1、创建存储过程：
CREATE OR REPLACE PROCEDURE procedure_name(var_name_1 IN varchar2,var_name_2 OUT varchar2)
AS
----此处声明变量、游标----
BEGIN
----此处是存储过程的执行体----
END procedure_name;

2、变量赋值：
1) 变量名 := 值;
2) SELECT col1,col2 INTO var_name1,var_name2 FROM ...;

3、判断语句：
IF 条件判断式 THEN
----执行语句----
ELSE
----执行语句----
END IF;

4、FOR循环：
FOR 变量 IN CURSOR_NAME LOOP
----执行语句----
END LOOP;

5、WHILE循环：
WHILE 条件判断式 LOOP
----执行语句----
END LOOP;

6、游标CURSOR：
--声明
CURSOR cursor_name IS SELECT ... FROM ...;
--打开
OPEN cursor_name;
LOOP
--放入游标内容
EXIT WHEN cursor_name%NOTFOUND;
FETCH cursor_name INTO ...;
----执行语句----
END LOOP;
--关闭
CLOSE cursor_name;

例：
CREATE OR REPLACE PROCEDURE dealwithAccDate
AS
V_BA_BS NUMBER;--备案标示
v_countOne NUMBER;

--游标声明
CURSOR cursor6 is select id from REG_EntRecord;

BEGIN
OPEN cursor6;
LOOP
FETCH cursor6 INTO V_BA_BS;
EXIT WHEN CURSOR6%NOTFOUND;
select count(*) into v_countOne from REG_EntRecord where ID = V_BA_BS;
IF(v_countOne > 0) THEN
update REG_EntRecord set AccDate=(select RegDate from REG_EntRecord where ID = V_BA_BS) where ID = V_BA_BS;
commit;
END IF;
END LOOP;
CLOSE cursor6;

END dealwithAccDate;

ITPub博客

ORACLE 存储过程小记

最新文章