存储过程详解

CREATE OR REPLACE PROCEDURE XXX (XX1 in VARCHAR,
                                 XX2 in INT,
                                 XX3 in INT,
                                 XX4 in INT,
                                 XX5 in INT,
                                 XX6 out sys_refcursor) IS 
-- 创建一个类型变量ref_cursor,它引用游标
TYPE ref_cursor IS REF CURSOR;
myCursor ref_cursor;

-- 创建一个字符串类型的数组（下表自增长）
TYPE t_list IS table OF VARCHAR(32) INDEX BY BINARY_INTEGER;
id_list t_list;

v_id             VARCHAR(32);
v_temp_id        VARCHAR(32);
i_row            NUMBER(5) := 0;

-- 处理条件

sql_where VARCHAR(2000) :=' AND ((xxx IS NULL AND UPDATE_TIME < SYSDATE- NUMTODSINTERVAL('||xx1||',''second''))'
||'or (xxx is not null and UPDATE_TIME < SYSDATE - NUMTODSINTERVAL('||xx2||',''minute'')))'
|| 'and xx3 <'||xx3|| ' and xx4 !=''value2'' and xx4=''value3'' ';

sql_select_for_update VARCHAR(2000);
sql_update VARCHAR(2000);
sql_select VARCHAR(2000);
sql_result VARCHAR(2000) :='SELECT xx1,xx2，...,xxN '
  || ' FROM xxx WHERE xx1 =''' || value4 || ''''
  || ' ORDER BY CREATE_DATE ';

BEGIN

sql_select := 'SELECT xx1 FROM xxx WHERE 1=1 ' || sql_where || ' ORDER BY CREATE_DATE ';

OPEN myCursor for sql_select;

LOOP
    FETCH myCursor INTO xx1;

    EXIT WHEN myCursor%notfound;

-- 尝试在加锁之前判断记录是否已经被其他用户锁定，如果已经锁定，则跳过已锁定记录，如果没有被锁定，就锁定这条未被锁定的记录

-- :1 指的是USING的第一个参数v_id
sql_select_for_update := 'SELECT xx1 FROM xxx WHERE xx1 =:1'
   || sql_where || ' FOR UPDATE SKIP LOCKED'；
dbms.output.put_line(sql_select_for_update);
    EXECUTE IMMEDIATE sql_select_for_update INTO v_temp_id USING v_id;
    IF SQL%ROWCOUNT >0 THEN
      i_row := i_row + 1;
      id_list(i_row) := v_id;
    END IF;

    EXIT WHEN i_row >= returnRows;
END LOOP;

sql_update :='UPDATE xxx '
|| 'SET xx1=value1，xx2=:1,...,xxN=valueN '
|| 'WHERE xx1 =:2 ' ||sql_where;
-- 修改符合条件的记录
FORALL i IN id_list.FIRST..id_list.LAST
    EXECUTE IMMEDIATE sql_update USING xx1,id_list(i);
CLOSE my cursor;
COMMIT;

OPEN xxx FOR sql_result;

-- SUBSTR(SQLERRM,1,200)返回指定错误代码的错误信息，并截取前200个字节
Exception
    WHEN OTHERS THEN
        dbms.output.put_line(SUBSTR(SQLERRM,1,200));
    ROLLBACK;
END XXX;

 

SQL%ROWCOUNT:默认值为0  返回执行成功的数量（insert update delete select）

SQL%FOUND:默认值为null 布尔型 有记录被修改，返回true

SQL%NOTFOUND:默认值为null 布尔型 没有记录被修改，返回true

SQL%ISOPEN:布尔型 如果游标打开，返回true，游标未打开，返回false

 

FORALL循环：FORALL i IN 下限..上限  

SQLERRM：返回指定错误代码的错误信息（适当进行截取，最多512字节）

：1，：2    ：USING后的参数赋值给：1，：2

INDEX BY BINRY_INTEGER. 下标自增长

FOR UPDATE SKIP LOCKED:尝试在加锁之前判断记录是否已经被其他用户锁定，如果已经锁定，则跳过已锁定记录，如果没有被锁定，就锁定这条未被锁定的记录