1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( <参数1>,[方式1]<数据类型1>, <参数2>,[方式2]<数据类型2>, ...)
) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN
END 存储过程名字 过程中有三种类型的参数:in/out/in out; 2.SELECT INTO STATEMENT 将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条 记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 例子: BEGIN SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ...
3.IF 判断 IF V_TEST=1 THEN BEGIN do something END; END IF;
4.while 循环 WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP; 5.变量赋值 V_TEST := 123; 6.用for in 使用cursor ... IS CURSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 END; END LOOP; END;
7.带参数的cursor CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP; CLOSE C_USER;
select af.keynode
into kn
from APPFOUNDATION af
where af.appid
=aid
and af.foundationid
=fid;
-- 有into,正确编译 select af.keynode
from APPFOUNDATION af
where af.appid
=aid
and af.foundationid
=fid;
-- 没有into,编译报错,提示:Compilation Error: PLS
-00428: an
INTO clause
is expected
in this
SELECT statement
3.在利用select...into...语法时,必须先确保数据库中有该条记录,否则会报出"no data found"异常。
可以在该语法之前,先利用
select count(*) from 查看数据库中是否存在该记录,如果存在,再利用select...into...
4.在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错
select keynode
into kn
from APPFOUNDATION
where appid
=aid
and foundationid
=fid;
-- 正确运行 select af.keynode
into kn
from APPFOUNDATION af
whereaf.appid=appidandaf.foundationid=foundationid;
-- 运行阶段报错,提示 ORA
-01422:exact
fetchreturns more than requested
numberof rows