삶은 예배다.

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:

References:

More Information:

N/A

Keywords:

PHP, OCI8, Extenstion, OCI, Oracle, database, call, stored procedure, stored function, single row, multi rows, package