oracle 存储过程的基本语法
目前成都创新互联已为上1000+的企业提供了网站建设、域名、网页空间、网站改版维护、企业网站设计、石峰网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 存储过程名字
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;
8.用pl/sql developer debug
连接数据库后建立一个Test WINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
Oracle存储过程基本语法：
CREATE OR REPLACE PROCEDURE 存储过程名
IS
BEGIN
NULL;
END;
解释：
行1:
CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它;
行2:
IS关键词表明后面将跟随一个PL/SQL体。
行3:
BEGIN关键词表明PL/SQL体的开始。
行4:
NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;
行5:
END关键词表明PL/SQL体的结束。
给你示例你参考下:
----------------------------建立存储过程：
CREATE OR REPLACE PROCEDURE P_name--存储过程名字
(
i_var in int, --输入参数1
o_var OUT varchar2 --输出参数1
)
IS
v_STR VARCHAR2(200); --定义存储过程内部的局部变量
BEGIN
--下面是存储过程的主体实现部分
v_STR := i_var;
dbms_output.put_line(v_STR);
o_var := v_STR;
exception--错误异常处理部分
when others then
dbms_output.put_line(sqlerrm);--打印错误信息
END P_name;
-----------------------------------调用存储过程示例：
declare
v_in int;
v_out varchar2(50);
begin
v_in :=100;
P_name (v_in,v_out); --调用存储过程P_name
dbms_output.put_line('存储过程输入结果为：'||v_out);
exception--错误异常处理部分
when others then
dbms_output.put_line(sqlerrm);--打印错误信息
end;
Oracle存储过程写法实例
总结项目中写的存储过程例子：
Oracle存储过程基本语法 存储过程
1 CREATE OR REPLACE PROCEDURE 存储过程名
2 IS/AS
3 BEGIN
4 NULL;
5 EXCEIPTION;
6 END;
1、创建存储过程，后面可用is或者as：
create or replace procedure PRO_COMPLAIN_TEMPLATE as
2、定义变量，此处用到了%TYPE和%ROWTYPE，参考 /database/201211/168564.html ，另外定义了一个游标，TEM_INSTANCE TEMPLATE_CUR%ROWTYPE这个类型定义必须要在游标定义之后：
NEED_DO_FOR_ZL INTEGER;
CURRENT_MAX_ID MEMO_TEMPLET.TEMPLET_ID%TYPE;
CURSOR TEMPLATE_CUR IS SELECT TEMPLET_NAME, TEMPLET_CONTENT FROM SYS_COMPLAINT_TEMPLET;
TEM_INSTANCE TEMPLATE_CUR%ROWTYPE;
3、begin开始块：
begin
4、该插入语句使用了DBLINK，还有使用DBMS_OUTPUT.put_line('打印信息')进行信息输出：
insert into MEMO_TEMPLET (TEMPLET_ID, TEMPLET_CONTENT, TEMPLET_TYPE, TEMPLET_MEMO) (SELECT * FROM MEMO_TEMPLET@COMPANY);
SELECT COUNT(*) INTO NEED_DO_FOR_ZL FROM SYS_COMPLAINT_TEMPLET;
IF(NEED_DO_FOR_ZL 0) THEN
DBMS_OUTPUT.put_line('打印信息');
SELECT MAX(TEMPLET_ID)+1 INTO CURRENT_MAX_ID FROM MEMO_TEMPLET;
DBMS_OUTPUT.put_line('MEMO_TEMPLET最大ID' || CURRENT_MAX_ID);
FOR TEM_INSTANCE IN TEMPLATE_CUR LOOP
INSERT INTO MEMO_TEMPLET (TEMPLET_ID, TEMPLET_CONTENT,
TEMPLET_TYPE, TEMPLET_MEMO) VALUES (CURRENT_MAX_ID,
TEM_INSTANCE.TEMPLET_CONTENT, '7', NULL);
CURRENT_MAX_ID := CURRENT_MAX_ID + 1;
DBMS_OUTPUT.put_line(TEM_INSTANCE.TEMPLET_CONTENT);
END LOOP;
END IF;
COMMIT;
4、exception块，使用WHEN OTHERS THEN，其中用raise可显示错误信息：
exception
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('sqlcode : ' ||sqlcode);
raise;
ROLLBACK;
IF TEMPLATE_CUR%ISOPEN THEN
CLOSE TEMPLATE_CUR;
END IF;
DBMS_OUTPUT.put_line('打印信息');
end;
注：关于游标的使用，如果像本例中使用for循环去遍历游标的话则不需要显式的去用open/close
cursor打开和关闭游标，此处会自己处理，如果使用fetch
into的话就需要显式开关游标，另外任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况，也可以使用来判断游标状态，如IF
TEMPLATE_CUR%ISOPEN THEN。
---创建
create or replace procedure GenTestUserData(nRecordCount in number Default 50000) is
nCurrentCount number; -- 当前表中记录
nMaxLogID number;
nInsertCount number;
begin
select Count(LogID), Nvl(Max(LogID), 0)
into nCurrentCount, nMaxLogID
from MyUser;
nInsertCount:= nRecordCount - nCurrentCount;
if nInsertCount = 0 then
Return;
end if;
-- 插入数据
insert into MyUser(FirstName, LastName, LogID, Mail, Location)
select FirstName, LastName, LogID, 'MyUser@' || To_Char(LogID) || '.Com', '.' || To_Char(LogID) || '.Com'
from (select GetRandomHZ FirstName, GetRandomHZ LastName, nMaxLogID + Rownum LogID
from All_Tab_Columns
where Rownum = nInsertCount) TestData;
end;
/
--删掉
drop procedure GenTestUserData;
存储过程(procedure)：是一个命名了的语句块，可以有0个或多个参数
语法：
create or replace procedure HelloWorld
as
begin
dbms_output.put_line('HelloWorld');
end;
调用存储过程
* 命令调用 exec helloworld
* 语句块调用
begin
helloworld;
end;
/
DELETE:
create or replace procedure del_emp01
is
begin
delete from emp01 where empno=7369;
end;
exec del_emp01;
create or replace procedure del_emp01(v_empno in emp01.empno%type)
is
begin
delete from emp01 wherer empno=v_empno;
end;
exec del_emp01(7521);
INSERT
create or replace procedure ins_emp01
(v_empno emp01.empno%type,v_ename emp01.ename%type)
as
begin
insert into emp01(empno,ename) values(v_empno,v_ename);
end;
exec ins_emp01(1000,'李四');
UPDATE
create or replace procedure upd_emp01
(v_empno emp01.empno%type,v_ename emp01.ename%type)
as
begin
update emp01 set ename=v_ename where empno=v_empno;
end;
exec upd_emp01(1000,'张三');
SELECT
create or replace procedure sel_emp01
(v_empno emp01.empno%type,v_emp01_data out emp01%rowtype)
as
begin
select * into v_emp01_data from emp where empno=v_empno;
end;
带有输出参数的存储过程不能使用命令直接调用
只能由语句块或程序调用(JAVA) ****************************************************************
declare
v_emp_data emp01%rowtype;
begin
sel_emp01(7499,v_emp_data);
dbms_output.put_line(v_emp_data.ename||' '||v_emp_data.sal);
end;
调用：
declare
v_emp_data emp01%rowtype;
begin
sel_emp01(7499,v_emp_data);
dbms_output.put_line(v_emp_data.ename||' '||v_emp_data.sal);
end;
使用scott用户登录
统计某个部门的员工的工资总和，员工的人数，平均工资，创建存储过程
create or replace procedure deptcount
(v_deptno emp.deptno%type,v_sal_sum out number,v_recordes out number,v_avg_sal out number,errorMsg out varchar2)
is
begin
select sum(sal) into v_sal_sum from emp group by deptno having deptno=v_deptno;
select count(*) into v_recordes from emp group by deptno having deptno=v_deptno;
select avg(sal) into v_avg_sal from emp group by deptno having deptno=v_deptno;
exception
when no_data_found then
errorMsg:='没有该部门';
end;
调用：
declare
v_sal_sum number;
v_recordes number;
v_avg_sal number(8,2);
v_errormsg varchar2(20);
begin
deptcount(90,v_sal_sum,v_recordes,v_avg_sal,v_errormsg);
dbms_output.put_line(v_sal_sum||' '||v_recordes||' '||v_avg_sal);
dbms_output.put_line(v_errormsg);
end;
//输出参数
create or replace procedure my_pro(v_num in number,v_result out number)
is
v_temp number;
begin
v_temp:=0;
for i in 1..v_num
loop
v_temp:=v_temp+i;
end loop;
v_result:=v_temp;
end;
declare
v_recieve number;
begin
my_pro(100,v_recieve);
dbms_output.put_line(v_recieve);
end;
//既是输入参数又是输出参数
create or replace procedure my_pro1(v_i in out number)
is
v_j number;
begin
v_j:=30;
v_i:=v_i*v_j;
end;
declare
v_t number;
begin
v_t:=20;
my_pro1(v_t);
dbms_output.put_line(v_t);
end;