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;
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博客 ” ,链接:http://blog.itpub.net/26770925/viewspace-1450745/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26770925/viewspace-1450745/