firstthree (my_seqid in varchar2) as cursor c1 is select sname, grade from student s join taken
//stored procedure
create or replace procedure firstthree (my_seqid in varchar2) as
cursor c1 is
select sname, grade from student s join taken t on s.sid=t.sid
where seqid=my_seqid
order by grade desc;
-- my_sname varchar2(40);
my_sname student.sname%TYPE;
-- my_grade number(2,1);
my_grade taken.grade%TYPE;
begin
/* clear the my_tmp table */
delete from my_tmp;
commit;
open c1;
for i in 1..3 loop
fetch c1 into my_sname, my_grade;
exit when c1%notfound; /* in case the number requested */
/* is more than the total */
/* number of enrolled students */
/* display the result */
dbms_output.put_line('name: ' || my_sname || ' ' || 'grade: ' || my_grade);
/* put into temporary table */
insert into my_tmp values(my_sname, my_grade);
commit;
end loop;
close c1;
end;
/
--------------
//php
<?
$connection = oci_connect ("test", "test", "test");
if ($connection == false){
$e = oci_error();
die($e['message']);
}
// the statement to call the stored procedure
$query = "begin " .
" firstThree(:my_seqid); " .
"end;";
$cursor = oci_parse($connection, $query);
if ($cursor == false){
$e = oci_error($connection);
die($e['message']);
}
// after parsing, bind the variable with Oracle variable.
oci_bind_by_name($cursor, ":my_seqid", $my_seqid, 5);
$my_seqid = '00001';
$result = oci_execute($cursor, OCI_NO_AUTO_COMMIT);
if ($result == false){
$e = oci_error($cursor);
die($e['message']);
}
oci_commit($connection);
oci_close($connection);
echo("Operation completed successfully.");
?>
----------------------------
Modify the stored procedure and the above PHP code to output (echo) the average of the top three grades in the Web page.
You need to modify the header of procedure firstThree() to
create or replace procedure firstThree(my_seqid in varchar2, my_average out number)
to include an output parameter my_average which outputs the average grade. Note the keyword OUT is used to indicate an output parameter. In the PHP code, you also need to bind an output variable. For example, if you use :my_average as the actual output parameter in the PL/SQL code, you can use the follow PHP code to bind it with a PHP variable $my_average:
oci_bind_by_name($cursor, ":my_average", $my_average, 40);
Note that 40 is used as the fourth argument of oci_bind_by_name() since the maximum length of a number in Oracle is 40 digits. The size of the PHP output variable must be large enough to accommodate the output parameter from the stored procedure. oci_bind_by_name resizes the PHP output variable my_average based on the length argument, which is 40 in this case.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
