oracle存储过程与游标的使用

CREATE OR REPLACE PROCEDURE xjp_sp_autohold_greencard1

AS
   v_TARGETVALUE NUMBER;
   v_val      NVARCHAR2(40);

BEGIN
  
  SELECT TARGETVALUE INTO v_TARGETVALUE FROM characteristic c WHERE c.characteristic='Therehold_AutoHold_GreenCard' AND c.ACTIVE=1;

  UPDATE LOT_NO L SET L.OBJECTCLASS='N' WHERE l.expirationdate =trunc(SYSDATE)+v_TARGETVALUE AND l.expirationdate >SYSDATE ;
  
  INSERT INTO xjp_t_audit_admin(PERFORMEDBY,TYPE,PERFORMEDON,OLDVALUE,NEWVALUE,CREATEDON,CREATEDBY)
  SELECT 'SYSTEM','QUAL STATUS'||P.PRODUCTNO||L.LOTNO,sysdate,'release','hold',sysdate,'SYSTEM'
  FROM lot_no l,PRODUCT p
  WHERE l.expirationdate <=SYSDATE+v_TARGETVALUE AND l.expirationdate>SYSDATE AND l.productid=p.id;
 
END;


CREATE OR REPLACE PROCEDURE xjp_sp_autohold_greencard
AS
 v_TARGETVALUE NUMBER;

BEGIN
  SELECT TARGETVALUE INTO v_TARGETVALUE FROM characteristic c WHERE c.characteristic='Therehold_AutoHold_GreenCard' AND c.ACTIVE=1;
  
        DECLARE
         v_val      NVARCHAR2(40);
         v_lotno    NVARCHAR2(40);
         v_productid  NVARCHAR2(40);
         v_productno  NVARCHAR2(40);
         
       CURSOR c_cursor IS 
         select l.lotno, l.productid, l.expirationdate ,p.productno from lot_no l INNER JOIN  product p on  l.productid=p.id;   
               
        BEGIN
         OPEN c_cursor;
           LOOP
            FETCH c_cursor INTO  v_lotno,v_productid,v_val,v_productno;
            EXIT WHEN c_cursor%NOTFOUND; 
              IF v_val = trunc(sysdate) + v_TARGETVALUE THEN
                 UPDATE LOT_NO L SET L.OBJECTCLASS='N' where lotno=v_lotno and productid=v_productid;
                 INSERT INTO xjp_t_audit_admin(PERFORMEDBY,TYPE,PERFORMEDON,OLDVALUE,NEWVALUE,CREATEDON,CREATEDBY)values('SYSTEM','QUAL STATUS'||v_productno|| v_lotno,sysdate,'release','hold',sysdate,'SYSTEM' );
              END IF;
           END loop;
        CLOSE c_cursor;
        END; 
END;