php連oracle stored procedure

• sql
  
  1. 建立連線
     $conn = oci_connect('YOUR_ACCOUNT', 'YOUR_PASSWORD','//IP_ADDRESS:PORT/SERVICE_NAME');
  2. 下sql
     $sql = "SELECT ID, NAME as \"name\"
     FROM CATEGORY
     WHERE PARENT_ID = 0 AND STATUS = 1
     ORDER BY CATEGORY_ID ASC";
  3. 取資料
     $stid = oci_parse($conn, $sql);
     oci_execute($stid);
     $nrows = oci_fetch_all($stid, $res,0, -1, OCI_FETCHSTATEMENT_BY_ROW);
     
     //echo "$nrows rows fetched
     print_r( $res);*/
     
• stored procedure
  
  1. 建立連線
     $conn = oci_connect('YOUR_ACCOUNT', 'YOUR_PASSWORD','//IP_ADDRESS:PORT/SERVICE_NAME');
  2. 連stored procedure
     假設sp名為myStoredProcedured，目的是將取到的時間轉為unit timestamp
     $sql ="begin
     :result := myStoredProcedured(:result => to_date(:myDate,'yyyy-mm-dd'));
     end; ";
     $stmt = oci_parse($conn,$sql) or die('could not parse statement.');
     
     //bind參數
     oci_bind_by_name($stmt,':result',$result,10); //回傳值需靠參數回傳
     oci_bind_by_name($stmt,':myDate',$myDate,10);
     
     //給值
     $myDate = '2012-06-12';
     
     //取值
     oci_execute($stmt) or die('execute');
     print_r($result);
     
  3. 給參數