php - How to call a function with parameters from plsql in php?

Question

I am trying to call a function with parameters from plsql in php and it's not working, but I don't know how.

My problem is it that I have a variable where i am trying to store the function $sqlGetGenre and i don't know how to call the function with $param parameter for example.

Also, another thing that I can't resolve is the call of a function in another function. I already Search On google, but I couldn’t Find something.

Create stored procedure:

$sqlGetGenre = "CREATE OR REPLACE FUNCTION getGenre(bookISBN IN VARCHAR2) RETURN VARCHAR2"
."AS"
."v_bookGenre VARCHAR2(100);"
."BEGIN"
."select genre into v_bookGenre from books where bookISBN=isbn;"
."return v_bookGenre;"

."EXCEPTION"
."when no_data_found then"
."return null;"
."END getGenre;";
$getGenre = oci_parse($conn, $sqlGetGenre);
oci_execute($getGenre);

Create stored procedure:

$sqlGetTitle ="CREATE OR REPLACE FUNCTION getTitle(bookISBN IN VARCHAR2) RETURN VARCHAR2"
."AS"
."v_bookTitle VARCHAR2(100);"
."BEGIN"
."select title into v_bookTitle from books where bookISBN=isbn;"

."return v_bookTitle;"

."EXCEPTION"
."when no_data_found then"
."return null;"
."END getTitle;";
$getTitle = oci_parse($conn, $sqlgetTitle);
   oci_execute($getTitle);

   $sqlCreateType1 = "CREATE OR REPLACE TYPE t IS TABLE OF varchar2(200);";
   $createType1 = oci_parse($conn, $sqlCreateType1);
   oci_execute($createType1);


   $sqlCreateType2 = "CREATE OR REPLACE TYPE typeRecom IS VARRAY(200) of VARCHAR2(200);";
   $createType2 = oci_parse($conn,$sqlCreateType2);
   oci_execute($createType2);

       $sqlTopRecommandations = "CREATE OR REPLACE FUNCTION topRecommandations(idUser IN VARCHAR2)" 
      ."RETURN typeRecom"
      ."AS"

     ."lastGenres t :=t();"
     ."readedBooks t:=t();"
     ."topTen typeRecom:=typeRecom();"

     ."v_i NUMBER := 1;"
     ."v_j NUMBER := 1;"

    ."verifDateBook NUMBER:=0;"
    ."verifDateLoan NUMBER:=0;"

    ."BEGIN"
    ."select count(*) into verifDateBook from books;"
   ."if(verifDateBook = 0) then"
   ."raise no_data_found;"
   ."end if;"
   ."select count(*) into verifDateLoan from loans;"
   ."if(verifDateLoan = 0) then"
   ."raise no_data_found;"
   ."end if;"

   ."lastGenres.extend();"
  ."for i in (select genre from books join loans on books.isbn=loans.bookId where loanDate>add_months(sysdate,-6) and idUser=loans.regNo order by genre desc) loop"
  ."lastGenres.extend();"
  ."lastGenres(lastGenres.count) := i.genre;"
  ."end loop;"
  ."readedBooks.extend();"
  ."for i in (select bookId from loans group by bookId order by count(bookId) desc) loop"
  ."readedBooks.extend();"
  ."readedBooks(readedBooks.count) := i.bookId;"
  ."end loop;"
  ."--select bookId into readedBooks from loan order by count(bookId) desc;"

  ."for v_i IN 1..readedBooks.count LOOP"
  ."for v_j in 1..lastGenres.count LOOP"

  ."if(lastGenres(v_j)=getGenre(readedBooks(v_i))) then"
  ."topTen.extend();"
  ."topTen(topTen.count) := getTitle(readedBooks(v_i));"
  ."end if;"
  ."EXIT WHEN topTen.count=10;"
  ."END LOOP;"
  ."EXIT WHEN topTen.count=10;"
  ."end LOOP;"
 ."return topTen;"
 ."EXCEPTION"
 ."WHEN no_data_found THEN"
 ."return null;"

."END topRecommandations;";

$topRecommandations = oci_parse($conn, $sqlTopRecommandations);
oci_execute($topRecommandations);

execute topRecommandations

$s = oci_parse($conn, "begin :ret :=topRecommandations(:param1); end;");
oci_bind_by_name($s, ':ret', $r, 200);
oci_bind_by_name($s, ':param1', $_SESSION['RollNo']);
oci_execute($s);
echo "Result is: ".$r;

标签： phpplsql

Answer 1

you can call a function e.g. on that way:

$s = oci_parse($c, "begin :ret :=getGenre(:param1); end;");
oci_bind_by_name($s, ':ret', $r, 200);
oci_bind_by_name($s, ':param1', $value1);
oci_execute($s);
echo "Result is: ".$r;

if your stored procedure doesn't have parameter, so you can call it on the same way, just remove the param value

$s = oci_parse($c, "begin :ret :=recommandation(); end;");
oci_bind_array_by_name($s,":ret",$arr,100,-1,SQLT_CHR);
oci_execute($s);