动态查询，动态生成cursor或者动态执行SQL的DML操作

定义PROCEDURE P1 ( P_Asset_Type IN varchar2,P_Asset_SubType IN varchar2,P_OrderIds IN varchar2)。

需要根据传入参数动态查询，动态生成cursor或者动态执行SQL的DML操作。

1.简单的查询

SELECT   ABC from TableA where ORDER_ID IN ( P_OrderIds) ；

2. 动态生成cursor

定义游标和变量：

type   v_cursor   is   ref   cursor;  
C1 v_cursor;    v_command varchar2(2000);

v_command := 'SELECT    ABC from TableA  where ORDER_ID IN ( ' ||P_OrderIds|| ' ) ';

OPEN C1 for v_command;

3.动态DML操作

 v_command2 := 'UPDATE TableA  
               SET ASSET_TYPE_CD = '''||P_Asset_Type||''','
                ||'ASSET_SUBTYPE_CD = '''||P_Asset_SubType||''''
                ||'WHERE ORDER_ID IN ( '||P_OrderIds||' ) ';

execute immediate v_command2;（动态sql）

 

例子：

PROCEDURE BOP_MoveIssue(        P_Asset_Type IN varchar2,
                                    P_Asset_SubType IN varchar2,
                                    P_OrderIds IN varchar2)
        AS
             type   v_cursor   is   ref   cursor;
             C1 v_cursor;
             v_command varchar2(2000);
             V_ORDER_DETAIL BOP_ORDER_DET_TYPE;
             V_udf_str1  VARCHAR2(40);      --  28
             V_udf_str2  VARCHAR2(40);      --  29
             V_udf_str3  VARCHAR2(40);      --  30
             V_udf_str4  VARCHAR2(40);      --  31
             V_udf_str5  VARCHAR2(40);      --  32
             V_notional  VARCHAR2(20);      --  33
    V_orderId   Number(12);
             V_Iss_In_SM_Flg  Char(1) :='N';
             V_Cur_Amt_Outsd NUMBER(38,18);
             V_Status Varchar2(1);
             V_ERROR  Varchar2(100);
             V_Status_Msg Varchar2(250);
             v_deskId        bop_order_request.desk_id%type;
             v_fee           bop_order_detail.fee%type;
             v_priceSpreadCd bop_order_detail.price_spread_cd%type;

       BEGIN

       v_command := 'SELECT O.ORDER_ID,O.ASSET_ID,O.ASSET_SUBTYPE_CD,O.ASSET_TYPE_CD,O.AVG_LIFE,O.
                          BMK_DESC,O.BUY_SELL_CD,O.  COLLATERAL,O.COMMENTS,
                          O.COUPON,O.CURRENCY,O.DESCRIPTION, O.FITCH_RATING_CD,
                          O.INDEX_DESC,O.ISSUER_NAME,O. MARKET_CD,O.MATURITY_DT,
                          O.NOTIONAL,O.ORIG_FACE,O. PAR,O. POOL,
                          O.PRICE,O. REQ_CLIENT_ID,O.   REQ_USER_ID,
                          O.TRANCHE,O.TYPE,O. UDF_STR1,O. UDF_STR2,
                          O.UDF_STR3,O. UDF_STR4,O.UDF_STR5,O. YIELD ,O.ISSUE_IN_SEC_MSTR_FLAG,
                          O.SP_RATING_CD, O.MDY_RATING_CD, R.DESK_ID, O.STATUS, O.STATUS_MSG
                          from BOP_ORDER_DETAIL O, BOP_ORDER_REQUEST R
                          where ORDER_ID IN ( ' ||P_OrderIds|| ' )
                          and   R.ORDER_REQ_ID = O.ORDER_REQ_ID ';

          V_ORDER_DETAIL := BOP_ORDER_DET_TYPE(
                 null,null,null,null,null,null,null,null,null,null,
                 null,null,null,null,null,null,null,null,null,null,
                 null,null,null,null,null,null,null,null,null,null,
                 null,null,null);
          OPEN C1 for v_command;
          LOOP
             FETCH C1 into
                V_orderId,V_ORDER_DETAIL.asset_ID,V_ORDER_DETAIL.asset_SubType_Cd,V_ORDER_DETAIL.asset_Type_Cd  ,V_ORDER_DETAIL.avg_Life  ,
                            V_ORDER_DETAIL.bMk_Desc  ,V_ORDER_DETAIL.buy_Sell_Cd, V_ORDER_DETAIL.collateral  ,V_ORDER_DETAIL.comments  ,
                            V_ORDER_DETAIL.coupon  , V_ORDER_DETAIL.currency  ,V_ORDER_DETAIL.description  ,V_ORDER_DETAIL.fitch_Rating_cd,
                            V_ORDER_DETAIL.index_desc  ,V_ORDER_DETAIL.issuer_Name  , V_ORDER_DETAIL.market_Cd,V_ORDER_DETAIL.maturity_Dt  ,
                            V_ORDER_DETAIL.notional, V_ORDER_DETAIL.orig_Face,V_ORDER_DETAIL.par  ,V_ORDER_DETAIL.pool  ,
                            V_ORDER_DETAIL.price  ,V_ORDER_DETAIL.req_Client_Id, V_ORDER_DETAIL.req_User_Id  ,
                            V_ORDER_DETAIL.tranche  , V_ORDER_DETAIL.type  , V_ORDER_DETAIL.udf_str1,V_ORDER_DETAIL.udf_str2  ,
                            V_ORDER_DETAIL.udf_str3  , V_ORDER_DETAIL.udf_str4  , V_ORDER_DETAIL.udf_str5 ,V_ORDER_DETAIL.yield ,V_Iss_In_SM_Flg,
                            V_ORDER_DETAIL.sp_Rating_Cd, V_ORDER_DETAIL.mdy_Rating_Cd, v_deskId, v_status, v_status_msg;
            EXIT WHEN C1%NOTFOUND;

            V_ORDER_DETAIL.asset_ID := UPPER(V_ORDER_DETAIL.asset_ID);

            
   END LOOP;

            CLOSE C1;
            commit;

            RETURN;

     EXCEPTION WHEN others THEN
     CLOSE C1;
     END BOP_MoveIssue;