游标用法询问。急急急

Question

create or replace procedures propertyScript
is
       cursor cur_propertyId is select a.propertyid
                                      from sysdb.lcmcc_t_gamepropertyinfo a
                                     where not exists (select 1
                                              from sysdb.lcmcc_t_temp_propertyinfo b
                                             where a.propertyid = b.propertyfeecode)
       cursor cur_propertyName is select a.propertyname
                                      from sysdb.lcmcc_t_gamepropertyinfo a
                                     where not exists (select 1
                                              from sysdb.lcmcc_t_temp_propertyinfo b
                                             where a.propertyid = b.propertyfeecode)
       cursor cur_description  is select a.description
                                      from sysdb.lcmcc_t_gamepropertyinfo a
                                     where not exists (select 1
                                              from sysdb.lcmcc_t_temp_propertyinfo b
                                             where a.propertyid = b.propertyfeecode)
       cursor cur_createTime   is select a.createtime
                                      from sysdb.lcmcc_t_gamepropertyinfo a
                                     where not exists (select 1
                                              from sysdb.lcmcc_t_temp_propertyinfo b
                                             where a.propertyid = b.propertyfeecode)
       cursor cur_gameContentCode is select a.gamecontentid
                                      from sysdb.lcmcc_t_gamepropertyinfo a
                                     where not exists (select 1
                                              from sysdb.lcmcc_t_temp_propertyinfo b
                                             where a.propertyid = b.propertyfeecode)
       sta_count             int  default 0;
       sta_propertyId        varchar(32);
       sta_propertyName        varchar(500);
begin
    begin;
    open cur_propertyId;
      LOOP
        FETCH cur_propertyId INTO sta_propertyId
              EXIT WHEN cur_propertyId%NOTFOUND;  
              //业务逻辑
        COMMIT;
      END LOOP;
    exception
      WHEN OTHERS THEN
      ROLLBACK WORK;
      DBMS_OUTPUT.PUT_LINE('ERROR: !');
      RETURN;
      close cur_propertyId;
    end;
    begin;
    
    open cur_propertyName;
      LOOP
        FETCH cur_propertyId INTO sta_propertyName
              EXIT WHEN cur_propertyId%NOTFOUND;  
              //业务逻辑
        COMMIT;
      END LOOP;
    exception
      WHEN OTHERS THEN
      ROLLBACK WORK;
      DBMS_OUTPUT.PUT_LINE('ERROR: !');
      RETURN;
      close cur_propertyName;
    end;
end propertyScript;
我想问设置游标的时候可以一次性设置多个吗？
取游标的时候，能把长度一样的游标放一个loop内吗？

Answer 1

游标可以一次设置多个，可以嵌套游标。但是你说的把长度一样的游标放在一个loop内没弄懂你的意思。

Answer 2

我想知道楼主想要干嘛，所有的游标都只是从同一张表里取一个字段，你大可以用一个游标从同一张表里取多个字段，fetch的时候把值存放到不同的变量里，要用哪个字段再引用哪个字段就OK啊！

Answer 3

 多个游标在一个loop遍历。

Answer 4

你5个游标的查询sql都是一样的，就是字段不一样，写成一个游标里就行

Answer 5

cursor cur_test is 
  select a.propertyid,a.propertyname,a.description,a.createtime,a.gamecontentid
  from sysdb.lcmcc_t_gamepropertyinfo a
  where not exists (select 1
                    from sysdb.lcmcc_t_temp_propertyinfo b
                    where a.propertyid = b.propertyfeecode);
begin
     OPEN cur_test;
     LOOP
         FETCH cur_test INTO v_propertyid,v_propertyname,v_description,v_createtime,v_gamecontentid;
         EXIT WHEN cur_test%NOTFOUNT;
         COMMIT;
     END LOOP;
   exception
     RETURN;
     close cur_test;
end;

Answer 6

晕，你不编译就敢写啊，procedures 居然写错
CREATE OR REPLACE PROCEDURE propertyScript IS
    CURSOR cur IS
        SELECT a.propertyid,
               a.propertyname,
               a.description,
               a.createtime,
               a.gamecontentid
          FROM sysdb.lcmcc_t_gamepropertyinfo a
         WHERE NOT EXISTS (SELECT 1
                  FROM sysdb.lcmcc_t_temp_propertyinfo b
                 WHERE a.propertyid = b.propertyfeecode);
    rec sysdb.lcmcc_t_gamepropertyinfo%ROWTYPE;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(rec.propertyid);
        DBMS_OUTPUT.PUT_LINE(rec.propertyname);
        DBMS_OUTPUT.PUT_LINE(rec.description);
        DBMS_OUTPUT.PUT_LINE(rec.createtime);
        DBMS_OUTPUT.PUT_LINE(rec.gamecontentid);
    END LOOP;
    CLOSE cur;
END;

Answer 7

楼主用如下方法，申明变量时最好用%type来定义变量的数据类型
如：
v_propertyid lcmcc_t_gamepropertyinf.propertyid%type;