cursor的两种使用方法举例

最新推荐文章于 2024-09-25 09:51:24 发布

cuicu7273

最新推荐文章于 2024-09-25 09:51:24 发布

阅读量463

点赞数

文章标签：数据库

在一次培训中，我很"狂妄"的对参加的同事说，现在的研发不懂sql的，他只懂对象操作，你别让他改sql... 其实那一刻，我是深深的无奈。

这次翻出了很多尘封的笔记，包括我在技术成长蹒跚学步阶段的许许多多范例... 事实证明，优秀的程序员永远都是SI成长的良师，这是多年前我司一位优秀的程序员mm写的两段procedure，引领我进入了自主编写PLSQL代码的天地。曾经有很长一段时间，我都不敢宣称自己会写PLSQL代码，因为一直没耐心看语法看书，除了Oracle Online Document的E文文档外，我也没找到更好更合适的教材。每一次要用的时候，我就翻出这两段procedure参考，然后依葫芦画瓢...

今天贴出来，以纪念我司高手辈出的那个年代，纪念我从小小白成长为普通小白的那个年代...

DECLARE
    --Acct data for updatｅ cursor
CURSOR curAcct
IS
    selecｔ * FROM Acct where encodestr is null
    FOR UPDATE;

--get node parent 嵌套cursor
CURSOR curNode ( numNodeId NUMBER )
IS
    selecｔ *
      FROM Node
     START WITH nodeid = numNodeId CONNECT BY PRIOR parent_node = nodeid;

recAcct Acct%ROWTYPE;
recNode        Node%ROWTYPE;
varEncodeStr   Varchar(256);
BEGIN
--for方式双层嵌套引用cursor
FOR recAcct IN curAcct LOOP
varEncodeStr := '';
FOR recNode IN curNode(recAcct.NODE_ID) LOOP
      varEncodeStr := recNode.NODEID ||'-'|| varEncodeStr;

   EXIT WHEN recNode.PARENT_NODE = 0;
     END LOOP;

updatｅ Acct
     SET EncodeStr = varEncodeStr
   WHERE ID = recAcct.ID;
END LOOP;

COMMIT;
EXCEPTION
    WHEN OTHERS THEN
ROLLBACK;
END;
/
--set cstm anｄ enduser Encodestr
DECLARE
    v_adls_pa number(2);
    v_163_pa number(2);
    v_cash_pa number(2);
    v_prom_pa   number(2);
    v_cus_id cstm.Id%TYPE;
    v_user_id EndUser.Id%TYPE;
    v_encode Acct.ENCODESTR%TYPE;

    CURSOR cur_general_pa
    IS
      selecｔ cus.id,
             cus.enduserid,
             pa.encodestr
        FROM cstm cus, Acct pa, cstm2Acct c2p
       WHERE cus.id=c2p.cstmid
         anｄ pa.id=c2p.Acctid
         anｄ cus.encodestr is null
         anｄ pa.type = 1
       UNION
      selecｔ cus.id,
             cus.enduserid,
             pa.encodestr
        FROM cstm cus, Acct pa, cstm2Acct c2p
       WHERE cus.id=c2p.cstmid
         anｄ pa.id=c2p.Acctid
         anｄ cus.encodestr is null
         anｄ pa.type = 2
   ;

    CURSOR cur_cash_pa
    IS
      selecｔ cus.id,
             cus.enduserid,
             pa.encodestr
        FROM cstm cus, Acct pa, cstm2Acct c2p
       WHERE cus.id=c2p.cstmid
         anｄ pa.id=c2p.Acctid
         anｄ pa.type= 3
         anｄ cus.encodestr is null
          ;

BEGIN

--open方式引用cursor
OPEN cur_general_pa;
LOOP
    FETCH cur_general_pa INTO v_cus_id, v_user_id, v_encode;
IF (cur_general_pa%NOTFOUND) THEN
    EXIT;
    END IF;
    updatｅ cstm set encodestr=v_encode where id=v_cus_id;
    updatｅ enduser set encodestr=v_encode where id=v_user_id;
COMMIT;
END LOOP;
CLOSE cur_general_pa;

OPEN cur_cash_pa;
LOOP
    FETCH cur_cash_pa INTO v_cus_id, v_user_id, v_encode;
IF (cur_cash_pa%NOTFOUND) THEN
    EXIT;
    END IF;
    updatｅ cstm set encodestr=v_encode where id=v_cus_id;
    updatｅ enduser set encodestr=v_encode where id=v_user_id;
COMMIT;
END LOOP;
CLOSE cur_cash_pa;

EXCEPTION
    WHEN OTHERS THEN
ROLLBACK;
END;
/