Purpose: Show how to access Oracle stored procedure from PHP via OCI8 extension.
Attendees: Oracle Developer, PHP Developer
Prerequisites: 1. Oracle Database 8.1.7 or higher
2. PHP engine 4.2.x with OCI8 extension
Assumption: The reader should have basic knowledge on SQL command, PHP and OCI8 extension.
Steps to do: 1. Create table
2. Insert data
3. Create stored procedure/function (single row)
4. Check stored procedure status
5. Call stored procedure/function
6. Pack stored procedure/function to package (multi row)
7. Call package
Details: 1. Create table CREATE TABLE myfriends(
fid number(2),
fname varchar2(30),
ftel varchar2(20),
fbirthday date,
gid varchar2(3)
); CREATE TABLE mygroups(
gid number(2),
gname varchar2(30),
gdesc varchar2(50)
);
2. Insert data
insert into myfriends values(1,'teddy','111111',to_date('10-10-1970','DD-MM-YYYY'),1);
insert into myfriends values(2,'nicole','333333',to_date('10-10-1975','DD-MM-YYYY'),1);
insert into myfriends values(3,'tyranno','333333',to_date('10-12-1969','DD-MM-YYYY,'),2);
insert into myfriends values(4,'sefjl','444444',to_date('10-09-1970','DD-MM-YYYY'),2);
insert into myfriends values(5,'fuju','555555',to_date('10-10-1970','DD-MM-YYYY'),1);
insert into myfriends values(6,'kouprex','666666',to_date('10-10-1975','DD-MM-YYYY'),2);
insert into myfriends values(7,'cupid','777777',to_date('10-12-1975','DD-MM-YYYY'),1);
insert into myfriends values(8,'runma','888888',to_date('10-09-1975','DD-MM-YYYY'),1);
insert into myfriends values(9,'spidix','888888',to_date('10-09-1970','DD-MM-YYYY'),2); insert into mygroups values(1,'SALES','Sales group');
insert into mygroups values(2,'SUPPORTS','Supports group'); commit;
3. Create stored procedure/function (single row)
3.1 Stored procedure
CREATE OR REPLACE PROCEDURE get_single_row_details(var_fname in varchar2, var_ftel out varchar2)
IS
var_temp_tel varchar2(20);
BEGIN
SELECT ftel
INTO var_temp_tel
FROM myfriends
WHERE fname = var_fname; var_ftel := var_temp_tel;
return;
END;
3.2 Stored function CREATE OR REPLACE FUNCTION count_friends (in_group_id IN mygroups.gid%type)
RETURN number
IS
var_temp_count NUMBER(2);
BEGIN
select count(*) into var_temp_count from myfriends where gid = in_group_id;
return var_temp_count;
END;
4. Check stored procedure status
select object_name, status from user_objects where object_name like '%GET_%' or object_name like '%COUNT_%';
5. Call stored procedure/function
5.1 By SQL*Plus
COLUMN FID HEADING Number
COLUMN FNAME HEADING Name
COLUMN FTEL HEADING Telephone
COLUMN FBIRTHDAY HEADING Birthday
COLUMN GNAME HEADING Group
SET AUTOPRINT ON
-- call stored procedure
VARIABLE RETVAL VARCHAR2(20)
EXEC get_single_row_details('teddy',:RETVAL );
-- call stored function #1
variable myvar number
exec :myvar := count_friends(1);
print :myvar
-- call stored function #2
select count_friends(1) from dual;
5.2 By PHP
-- call stored procedure
<?php
:
$query = "begin get_single_row_details('$myFriendName', :var_ftel); end;"; echo "Connection is " . $con;
echo '<br>'; $stmt = OCIParse($con, $query) or die ('Can not parse query');
OCIBindByName($stmt,":var_ftel", &$myFriendTel, 20) or die ('Can not bind variable');
OCIExecute($stmt) or die ('Can not Execute statment');
:
?> -- call stored function
<?php
:
$query = "select count_friends(:group_id) MCOUNT from dual";
echo "Connection is " . $con;
echo '<br>'; $stmt = OCIParse($con, $query) or die ('Can not parse query');
OCIBindByName($stmt,":group_id", &$gid, 20) or die ('Can not bind variable'); $gid=1; // for gid =1 or 2
OCIExecute($stmt) or die ('Can not Execute statment'); while (ocifetch($stmt)){
echo '<br>'.$con." <".ociresult($stmt,"MCOUNT").">\n\n";
$_friends=ociresult($stmt,"MCOUNT");
echo '<br>'.$con."----done\n\n";
}
:
?>
6. Pack stored procedure/function to package (multi rows)
CREATE OR REPLACE PACKAGE myfriends_data AS
TYPE MyFriendsRecTyp IS RECORD (
fid myfriends.fid%type,
fname myfriends.fname%type,
ftel myfriends.ftel%type,
fbirthday myfriends.fbirthday%type,
gid myfriends.gid%type
); TYPE MyFriendsCurTyp IS REF CURSOR RETURN MyFriendsRecTyp; PROCEDURE get_single_row_details(var_fname in varchar2,var_ftel out varchar2);
PROCEDURE get_multi_rows_details(var_gid IN NUMBER,cv_myfriends IN OUT MyFriendsCurTyp);
FUNCTION count_friends(in_group_id IN mygroups.gid%type) Return Number; END myfriends_data;
CREATE OR REPLACE PACKAGE BODY myfriends_data AS
PROCEDURE get_single_row_details(var_fname in varchar2, var_ftel out varchar2)
IS
var_temp_tel varchar2(20);
BEGIN
SELECT ftel
INTO var_temp_tel
FROM myfriends
WHERE fname = var_fname;
var_ftel := var_temp_tel;
return;
END get_single_row_details; PROCEDURE get_multi_rows_details(var_gid IN NUMBER, cv_myfriends IN OUT MyFriendsCurTyp)
IS
BEGIN
OPEN cv_myfriends FOR
SELECT fid, fname, ftel, fbirthday,mf.gid FROM myfriends mf, mygroups mg
WHERE mf.gid = var_gid AND mf.gid = mg.gid
ORDER BY fid;
END get_multi_rows_details;
FUNCTION count_friends (in_group_id IN mygroups.gid%type)
RETURN number
IS
var_temp_count NUMBER(2);
BEGIN
select count(*) into var_temp_count from myfriends where gid = in_group_id;
return var_temp_count;
END count_friends; END myfriends_data;
7. Call package
7.1 By SQL*Plus
COLUMN FID HEADING Number
COLUMN FNAME HEADING Name
COLUMN FTEL HEADING Telephone
COLUMN FBIRTHDAY HEADING Birthday
COLUMN GNAME HEADING Group
SET AUTOPRINT ON -- call stored procedure
VARIABLE RETVAL VARCHAR2(20)
VARIABLE cv REFCURSOR EXEC myfriends_data.get_single_row_details('teddy',:RETVAL );
EXEC myfriends_data.get_multi_rows_details(1,:cv ); -- call stored function #1
variable myvar number
exec :myvar := myfriends_data.count_friends(1);
print :myvar -- call stored function #2
select myfriends_data.count_friends(1) from dual;
7.2 By PHP <?php
:
$stmt = OCIParse($conn,"begin myfriends_data.get_multi_rows_details(:group_id, :friend_cv); end;"); OCIBindByName($stmt,":group_id",&$gid,32);
OCIBindByName($stmt,":friend_cv",&$curs,-1,OCI_B_CURSOR); $gid=1; // for gid = 1 only
ociexecute($stmt);
ociexecute($curs); while (OCIFetchInto($curs,&$friend_cv )) {
echo "<pre>";
print_r($friend_cv );
echo "</pre>";
}
:
?>
Complete sample code:
1. Calling stored procedure (single row)
2. Calling stored function (single row)
3. Calling stored procedure (multi rows)
4. All completed command in this article
5. Sample output from excuting command
References:
1. PL/SQL User's Guide and Reference Release 2 (9.2) Part Number A96624-01 ,Oracle Corporation
2. " http://www.php.net/manual/en/function.ocinewcursor.php "
3. " http://www.php.net "
More Information:
N/A
Keywords:
PHP, OCI8, Extenstion, OCI, Oracle, database, call, stored procedure, stored function, single row, multi rows, package
| 
Rss Feed