ลองเขียน php เรียก procedure oracle แบบมที parameter out ปัญหาคือ php เรียกแล้ว ข้อมูลใน database ไม่อัพเดต และไม่คืนค่าให้อ่ะครับ เหมือนมันไม่ทำงาน ลองเทสเรียกผ่าน sqldevelope ใช้ได้ปกติ
อันนี้ procedure ใน oracle ครับ
ORACLE PROCEDURE
create or replace PROCEDURE kimtest_UPDATESALARYIWITHINOUT(P_EMPLOYEEID IN NUMBER,P_SALARYPERCENT IN NUMBER, P_SALARY OUT NUMBER)
IS
-- declare local variable
-- get data and put into variable--
SP_SALARY number;
Begin
if P_SALARYPERCENT > 0 then
-- get salary from table put into variable
select employee_salary into SP_SALARY
from kimtest_employee
where employee_id = P_EMPLOYEEID;
--check value parameter out --
P_SALARY := SP_SALARY;
DBMS_OUTPUT.PUT_LINE('=====IF STATEMENT SALARY UPDATED===='|| P_SALARY);
-- update salary from table and multiply with increament percent
SP_SALARY := SP_SALARY + (SP_SALARY*P_SALARYPERCENT);
--put data to parameter OUT
P_SALARY := SP_SALARY;
-- show output of parameter out that should be update
DBMS_OUTPUT.PUT_LINE('=====IF STATEMENT SALARY UPDATED===='|| P_SALARY);
--updata salary back to database
update kimtest_employee
set employee_salary = SP_SALARY
where employee_id = P_EMPLOYEEID;
else
DBMS_OUTPUT.PUT_LINE('===ELSE STATEMENT====');
end if;
END;
อันน้ php อ่ะครับ
PHP
<?php
$dbstr = "oracleconnector";
$objConnect = oci_connect("kim","Ioncube03",$dbstr);
if($objConnect)
{
print 'Successfully connected to Oracle Database!'."<br/>";;
//call procedure
$strSQL = 'BEGIN kimtest_UPDATESALARYIWITHINOUT(:PHP_EMPLOYEEID, :PHP_SALARYPERCENT, :PHP_SALARY); END;';
//$strSQL = 'BEGIN "kimtest_UPDATESALARYIWITHINOUT"(:PHP_EMPLOYEEID, :PHP_SALARYPERCENT, :PHP_SALARY); END;';
$objParse = oci_parse($objConnect, $strSQL);
// Declare cursor for OUT SYS_REFCURSOR
//$objCursor = oci_new_cursor($objConnect);
// Bind input parameters
oci_bind_by_name($objParse, ':PHP_EMPLOYEEID', $S_EMPLOYEEID) ;
oci_bind_by_name($objParse, ':PHP_SALARYPERCENT', $S_SALARYPERCENT) ;
// Bind output parameters
oci_bind_by_name($objParse, ':PHP_SALARY', $S_SALARY);
//assign value for input
$S_EMPLOYEEID = 1;
$S_SALARYPERCENT = 0.1;
//execute procedure
oci_execute ($objParse);
// Display OUT : pRowFound
echo "data of employee id : ".$S_EMPLOYEEID."<br/>";
echo "data of salary percent : ".$S_SALARYPERCENT."<br/>";
echo "data of salary after procedure : ".$S_SALARY."<br/>";
}
else
{
$errmsg = oci_error();
print 'Oracle connection failed' . $errmsg['message'];
}
//error_reporting(E_ALL);
//ini_set('display_errors', true);
oci_close($objConnect);
?>
Tag : PHP, Oracle
Load balance : Server 02
