在存储过程中怎么用动态游标？

Question

create or replace ...
as
  type cur_type is ref cursor;
  c cur_type;
  sql string(100);
begin
  sql:='select ...';
  open c for sql;    ...

Answer 1

我这里有一个实例，楼主可以看一下：CREATE OR REPLACE Procedure USP_UPSTOCKIMPORT
   ( LNO      IN VARCHAR2 ,
     OPERATER IN VARCHAR2
   )
   IS
   ACTID        STOCKIMPORTHEAD.ACCOUNTID%TYPE;
   STOCKID      STOCKIMPORTHEAD.STOCKLOCID%TYPE;
   BDT          STOCKIMPORTHEAD.BILLDT%TYPE;
   BNO          STOCKIMPORTHEAD.BILLNO%TYPE;
   CID          STOCKIMPORTHEAD.CUSTOMERID%TYPE;
   FTOTALQTY    STOCKIMPORTBODY.TOTALQTY%TYPE;
   FAMOUNT      STOCKIMPORTBODY.AMOUNT%TYPE;
   FUNITPRICE   STOCKIMPORTBODY.UNITPRICE%TYPE;
   GINID        STOCKIMPORTBODY.GDSINID%TYPE;
   LROWNO       STOCKIMPORTBODY.ROWNO%TYPE;
   FBINAMOUNT   STOCKIMPORTBODY.AMOUNT%TYPE;
   LCOUNT       NUMBER;
   LISTNO       VARCHAR2(10);
   OTYPE        STOCKIMPORTHEAD.STOCKTYPE%TYPE;   CURSOR c_GetBody IS
    SELECT  GDSINID,TOTALQTY,UNITPRICE,AMOUNT,ROWNO
    FROM    STOCKIMPORTBODY
    WHERE   LISTNO = LNO
    ORDER BY ROWNO;BEGIN
    SELECT  STOCKLOCID , ACCOUNTID , BILLDT , CUSTOMERID , STOCKTYPE , BILLNO
    INTO    STOCKID    , ACTID     , BDT    , CID        , OTYPE     , BNO
    FROM    STOCKIMPORTHEAD
    WHERE   LISTNO = LNO;    OPEN    c_GetBody;
    FETCH   c_GetBody   INTO  GINID,FTOTALQTY,FUNITPRICE,FAMOUNT,LROWNO;    WHILE   c_GetBody%FOUND LOOP
        -- 库存帐
        UPDATE  /*+ INDEX (STOCK PK_STOCK) */ STOCK
        SET     CLOSEQTY     = CLOSEQTY     + FTOTALQTY ,
                CLOSEAMOUNT  = CLOSEAMOUNT  + FAMOUNT   ,
                BILLDT       = BDT
        WHERE   GDSINID    = GINID   AND
                STOCKLOCID = STOCKID AND
                CUSTOMERID = CID     AND
                ACCOUNTID  = ACTID   ;
        IF SQL%ROWCOUNT = 0 THEN
            INSERT
            INTO    STOCK(  ACCOUNTID , STOCKLOCID   , GDSINID ,
                            OPENQTY   , OPENAMOUNT   ,
                            IMPORTQTY , IMPORTAMOUNT ,
                            EXPORTQTY , EXPORTAMOUNT ,
                            PROFQTY   , PROFAMOUNT   ,
                            CLOSEQTY  , CLOSEAMOUNT  ,
                            PREQTY    , PREAMOUNT    ,
                            ONWAYQTY  , ONWAYAMOUNT  ,
                            BILLDT    , CUSTOMERID   )
            VALUES( ACTID     , STOCKID , GINID ,
                    0         , 0       ,
                    0         , 0       ,
                    0         , 0       ,
                    0         , 0       ,
                    FTOTALQTY , FAMOUNT ,
                    0         , 0       ,
                    0         , 0       ,
                    BDT       , CID     );
        END IF;
    FETCH   c_GetBody   INTO    GINID,FTOTALQTY,FUNITPRICE,FAMOUNT,LROWNO;
    END LOOP;
    CLOSE   c_GetBody;
END ;