从PHP调用Oracle存储过程 [英] Call Oracle stored procedure from PHP

问题描述

我正在尝试从过程中执行并获取数据:

I am trying to execute and get data from my procedure:

这是我的程序的定义方式:

Here is the way my procedure is defined:

create or replace PROCEDURE SP_GET_MY_DATA(
         IN_POP VARCHAR2,
         IN_SEG VARCHAR2,
         IN_DUR VARCHAR2, 
         IN_VIEW INTEGER, 
         IN_PAGE INTEGER, 
         VIEW_DATA_CUR OUT SYS_REFCURSOR) AS ...

这是我的PHP代码，用于执行过程并从过程中获取数据:

Here is my PHP code to execute and get data from procedure:

$db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = JXYX.com)(PORT = 1521)))(CONNECT_DATA=(SID=DHSJKS)))";
$conn = ocilogon("XXXXXX","XXXXXXXX",$db);          

$sql = 'BEGIN SP_GET_MY_DATA(:POP, :SEG, :DUR, :VIEW, :PAGE, :OUTPUT_CUR); END;';            

$stmt = oci_parse($conn,$sql);                     
oci_bind_by_name($stmt,':POP',$pop);           
oci_bind_by_name($stmt,':SEG',$seg);           
oci_bind_by_name($stmt,':DUR',$dur);           
oci_bind_by_name($stmt,':VIEW',$view);           
oci_bind_by_name($stmt,':PAGE',$page);           
$OUTPUT_CUR = oci_new_cursor($conn);
oci_bind_by_name($stmt,":OUTPUT_CUR", $OUTPUT_CUR, -1, OCI_B_CURSOR);                   
oci_execute($stmt, OCI_DEFAULT); 

while ($data = oci_fetch_assoc($OUTPUT_CUR)) {
    print_r($data);
}

但是这样做，我得到了这个错误:

But in doing so I am getting this error:

oci_fetch_assoc():ORA-24374:在获取或执行并获取之前未完成定义."

oci_fetch_assoc(): ORA-24374: define not done before fetch or execute and fetch".

我无法弄清我所缺少的.你能帮忙吗?

I am not able to figure out what I'm missing. Can you help?

推荐答案

要在PHP中使用游标，与直接从SELECT语句访问行相比，还需要三个额外的步骤.

To work with a cursor in PHP three additional steps are required, as compared to accessing rows directly from a SELECT statement.

• 第一步是使用oci_new_cursor()函数在PHP中准备游标资源，然后将其用于绑定到适当的参数.
• 第二步是在oci_bind_by_name()函数上添加参数
• 执行通常的SQL语句后，第三步是在游标资源上调用oci_execute().

• The first step is preparing a cursor resource in PHP using the oci_new_cursor() function, which you then use to bind to the appropriate parameter.
• The second step is to add a parameter on oci_bind_by_name() function
• The third step, after you have executed the usual SQL statement, is calling oci_execute() on the cursor resource.

代码:

//Connection does not change
$db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = JXYX.com)(PORT = 1521)))(CONNECT_DATA=(SID=DHSJKS)))";
$conn = ocilogon("XXXXXX","XXXXXXXX",$db);          

//Request does not change
$sql = 'BEGIN SP_GET_MY_DATA(:POP, :SEG, :DUR, :VIEW, :PAGE, :OUTPUT_CUR); END;';            

//Statement does not change
$stmt = oci_parse($conn,$sql);                     
oci_bind_by_name($stmt,':POP',$pop);           
oci_bind_by_name($stmt,':SEG',$seg);           
oci_bind_by_name($stmt,':DUR',$dur);           
oci_bind_by_name($stmt,':VIEW',$view);           
oci_bind_by_name($stmt,':PAGE',$page);    

//But BEFORE statement, Create your cursor
$cursor = oci_new_cursor($conn)

// On your code add the latest parameter to bind the cursor resource to the Oracle argument
oci_bind_by_name($stmt,":OUTPUT_CUR", $cursor,-1,OCI_B_CURSOR);

// Execute the statement as in your first try
oci_execute($stmt);

// and now, execute the cursor
oci_execute($cursor);

// Use OCIFetchinto in the same way as you would with SELECT
while ($data = oci_fetch_assoc($cursor, OCI_RETURN_LOBS )) {
    print_r($data}
}

我不太会Oracle(英语)，所以您应该阅读

I'm not very fluent with Oracle (an english), so you should read this tutorial. There is an interesting example, look at the Stored Procedures and Reference Cursors Chapter!

希望有帮助！

这篇关于从PHP调用Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！