Detailed explanation of PHP access to Oracle stored procedures

This article mainly introduces the relevant information of php accessing oracle Stored procedureExample details, here is the example code to help you realize such a function, friends in need can refer to it

Detailed explanation of php accessing oracle stored procedure instances

For example, my local Oracle database has a package with a stored procedure in it:

create or replace package PKG_TRANS_REL is

 -- Author : test
 -- Created : 
 -- Purpose : test

 -- Public type declarations
 PKG_NAME varchar2(20) := 'PKG_TRANS_REL';
 --存储过程，测试用
 procedure pro_GC_withdraw(in_merch_no   in varchar2,
              in_withdraw_amt in number,
              out_result   out number,
              out_errmsg   out varchar2);
end PKG_TRANS_REL;

The package name is PKG_TRANS_REL, and the stored procedure is pro_GC_withdraw. This stored procedure has four parameters, two input parameters and two output parameters.

Example of calling via pdo in PHP:

  $this->_pdo = new PDO(PDO_DB_DNS, PDO_DB_USER, PDO_DB_PASSWORD);
  $call = "CALL PKG_TRANS_REL.pro_GC_withdraw(?,?,?,?)";

  try{
      $stmt = $this->_pdo->prepare($call);

      $stmt->bindParam(1, $merch_no);
      $stmt->bindParam(2, $amount, PDO::PARAM_INT);

      $stmt->bindParam(3, $result, PDO::PARAM_INT, 4);
      $stmt->bindParam(4, $error_msg, PDO::PARAM_STR, 64);

      $stmt->execute();

    }catch (PDOException $e)
    {
      $msg = 'SQL:'.$e->getMessage();
      $msg = iconv('GBK','UTF-8',$msg);
      user_dump('SQL:'.$msg);
      return false;
    }

    ...

bindParam The third parameter defaults to PDO::PARAM_STR, if it is For other types, you need to specify

Input parameter Passing value is relatively simple, while outgoing parameter is slightly more complicated, and the length must be specified.

php program can access the database by using stored procedures. Some people think that using stored procedures is easier to maintain. However, benevolent people have different opinions. On this issue, I think that using stored procedures means that DBA and developers must cooperate more closely. If one of them changes, it will obviously be difficult to maintain.

But using stored procedures has at least two of the most obvious advantages: speed and efficiency. Using stored procedures is obviously faster. In terms of efficiency, if an application needs to perform a series of SQL operations at one time, it needs to go back and forth between PHP and Oracle. It is better to put the application directly into the database to reduce the number of round trips and increase efficiency. But in Internet applications, speed is extremely important, so it is necessary to use stored procedures. I also used PHP to call stored procedures not long ago, and did the following example.

code

//建立一个test表 
　　create table test ( 
　　id number(16) not null, 
　　name varchar2(30) not null, 
　　primary key (id) 
　　); 
　　//插入一条数据 
　　insert into test values (5, 'php_book'); 
　　//建立一个存储过程 
　　create or replace procedure proc_test ( 
　　p_id in out number, 
　　p_name out varchar2 
　　) as 
　　begin 
　　select name into p_name 
　　from test 
　　where id = 5; 
　　end proc_test;

php code　

<?php 
　　//建立数据库连接 
　　$user = "scott"; //数据库用户名 
　　$password = "tiger"; //密码 
　　$conn_str = "tnsname"; //连接串(cstr : connection_string) 
　　$remote = true //是否远程连接 
　　if ($remote) { 
　　$conn = ocilogon($user, $password, $conn_str); 
　　} 
　　else { 
　　$conn = ocilogon($user, $password); 
　　} 
　　//设定绑定 
　　$id = 5; //准备用以绑定的php变量 id 
　　$name = ""; //准备用以绑定的php变量 name 
　　/** 调用存储过程的sql语句(sql_sp : sql_storeprocedure) 
　　* 语法： 
　　* begin 存储过程名([[:]参数]); end; 
　　* 加上冒号表示该参数是一个位置 
　　**/ 
　　$sql_sp = "begin proc_test(:id, :name); end;"; 
　　//parse 
　　$stmt = ociparse($conn, $sql_sp); 
　　//执行绑定 
　　ocibindbyname($stmt, ":id", $id, 16); //参数说明：绑定php变量$id到位置:id，并设定绑定长度16位 
　　ocibindbyname($stmt, ":name", $name, 30); 
　　//execute 
　　ociexecute($stmt); 
　　//结果 
　　echo "name is : $name<br>"; 
　　?>

The above is the detailed content of Detailed explanation of PHP access to Oracle stored procedures. For more information, please follow other related articles on the PHP Chinese website!