如何将数组传递给plsql块并将结果集返回给php？

Question

我试图将$ PRODUCT_NUM数组传递给pl-sql块，然后为数组中的两个产品编号的每一个执行存储函数，并将结果集发送回php。我对pl-sql并不熟悉，并希望得到一些关于如何实现这一点的指导？我的意图是构建一个基于REST的Web svc，在清理完成后调用下面的PHP代码。不知道我是否想为下面的sql块创建一个存储过程，因为我预计每分钟下面的代码会有1000个命中。

<?php 
$conn = oci_connect("username","password","localdb"); 

$CONFIG_NAME='DirectType'; 
$BUS_SEG_CODE=''; 
$PRODUCT_NUM=array('130342','270179'); 
$MEMBER_NAME='87307-3'; 
$EFFECTIVE_DATE=strtotime('2016-12-01'); 
$MODEL_DATE=time(); 
$CURRENCY='USD'; 
$REALM_NUM=1024; 
$RESOLVED_PRICE=111; 
$RESOLVED_CURRENCY='USD'; 

if ($conn) { 
    echo "Connection Successful"."\n"; 
    echo "System time is ".time(); 

    $sql_proc = " 
    DECLARE 
     v_MEMBER_ID NUMBER; 
     v_PRODUCT_ID NUMBER; 
     PRODUCTNUMBER NUMBER; 
     BASEPRICE NUMBER; 
     RESOLVEDPRICE NUMBER; 
     UPCHARGEAMOUNT NUMBER; 
     CURRENCY VARCHAR2; 
     PRICINGDOCNAME VARCHAR2(100); 
     TIER_INDEX NUMBER; 
     DOC_ID VARCAHR2(100); 
    BEGIN 
      SELECT cat_map_id INTO v_PRODUCT_ID 
      FROM CAT_MAP WHERE product_num IN :PRODUCT_NUM_ARR and catalog_type = 'INT'; 

      SELECT member_id INTO v_MEMBER_ID 
      FROM MEMBER_TABLE WHERE member_name = :MEMBER_NAME; 

      v_PMLI_PK := PACKAGE.function(:CONFIG_NAME,:BUS_SEG_CODE,v_MEMBER_ID,v_PRODUCT_ID,TO_TIMESTAMP(TO_DATE(:EFFECTIVE_DATE,'YYYY-MM-DD')),TO_TIMESTAMP(TO_DATE(:MODEL_DATE,'YYYY-MM-DD')), 
                  :CURRENCY_CODE,:REALM_NUM,:RESOLVED_PRICE,:RESOLVED_CURRENCY); 

     SELECT 
      PMLI.PRODUCT_NUM INTO PRODUCTNUMBER, 
      PMLI.BASE_PRICE INTO BASEPRICE, 
      PMLI.PRICE INTO RESOLVEDPRICE, 
      PMLI.UP_CHARGE INTO UPCHARGEAMOUNT, 
      PMLI.PRICE_CURR INTO CURRENCY, 
      NULL, 
      DOC.STRUCT_DOC_NAME INTO PRICINGDOCNAME, 
      PMLI.TIER_INDEX INTO TIER_INDEX, 
      NULL, 
      PMLI.DOC_ID INTO DOC_ID, 
      NULL 
      FROM P_MASTER PMLI 
      INNER JOIN DOC_TABLE DOC 
      ON (PMLI.STRUCT_DOC_ID = DOC.STRUCT_DOC_ID) 
      WHERE PMLI.PMLI_ID = v_PMLI_PK; 

    END; 


    $stmt = oci_parse($conn,$sql_proc); 
    // Bind the input parameter 

oci_bind_by_name($stmt,':CONFIG_NAME',$CONFIG_NAME); 
oci_bind_by_name($stmt,':BUS_SEG_CODE',$BUS_SEG_CODE); 
oci_bind_by_name($stmt,':EFFECTIVE_DATE',$EFFECTIVE_DATE); 
oci_bind_by_name($stmt,':MODEL_DATE',$MODEL_DATE); 
oci_bind_by_name($stmt,':CURRENCY_CODE',$CURRENCY); 
oci_bind_by_name($stmt,':REALM_NUM',$REALM_NUM); 
oci_bind_by_name($stmt,':RESOLVED_PRICE',$RESOLVED_PRICE); 
oci_bind_by_name($stmt,':RESOLVED_CURRENCY',$RESOLVED_CURRENCY); 

// Parse the statement. Note there is no final semi-colon in the SQL statement 
$result=oci_execute($stmt); 

if (!$result){ 
    $e = oci_error($stmt); // For oci_execute errors pass the statement handle 
    print htmlentities($e['message']); 
    print "\n<pre>\n"; 
    print htmlentities($e['sqltext']); 
    printf("\n%".($e['offset']+1)."s", "^"); 
    print "\n</pre>\n"; 

} 
else { 
    echo "Execute STMT returns True or False : ".$result; 
    echo "Resolved Price is : ".$RESOLVED_PRICE. "\n"; 
} 
echo "</pre>"; 
oci_free_statement($stmt); 
oci_close($conn); 
} 
else { 
$e = oci_error(); 
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); 
} 
    ?> 

Answer 1

我的建议是，你创建你的SQL块作为数据库中的存储过程。

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm

让你的程序接受一个参数，一个集合（表型要准确）。 在数据库中创建一个表类型：

CREATE TYPE myRec AS OBJECT (NUMBER(10)); 
    CREATE TYPE myTab AS TABLE OF myRec; 

你的程序必须接受型MYTAB的PARAM：

PROCEDURE p_myProc(param1 myTab) IS... 

在PHP代码

然后，只需调用这个过程：

$sql_proc = "p_myproc(yourPHPArrayVariable)"; 

请注意，你必须确保数据类型是同步的..我的意思是不要传递字符，如果你的数据库中的基础记录是数字，如我的例子。