存储过程特点
存储过程是指被命名的PL/SQL块,这种块可以带有参数,可以在不同应用中多次调用。PL/SQL有两种类型的存储过程:过程和函数。其中,过程用于执行特定操作,而函数则用于返回特定数据。通过将商业逻辑和企业规则集成到PL/SQL子程序中,可以简化客户端应用的开发和维护,提高应用的性能。
创建存储过程语法
过程一般用于执行一个指定的操作,可以将常用的特定操作封装成过程。
语法:
CREATE [OR REPLACE] PROCEDURE procedure_name
(argument1 [mode1] datatype1, argument2 [mode2] datatype2,...)
IS [AS]
声明部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;
上述语法中,procedure_name 用于指定过程名称,argument1 、argument2 等则用于指定过程的参数,IS 或 AS 用于开始一个 PL/SQL 块。当指定参数数据类型时,不能指定其长度。另外,创建存储过程时,既可以指定输入参数(IN),又可以指定输出参数(OUT)及输入输出参数(IN OUT)。通过在过程中使用输入参数,可以将应用环境的数据传递到执行部分。通过使用输出参数,可以将执行部分的数据传递到应用环境。定义子程序参数时,如果不指定参数模式,则默认为输入参数;如果需要定义输出参数,则必须指定OUT关键字;如果定义输入输出参数,则必须指定 IN OUT关键字。
不带参数存储过程
创建不带参数的存储过程
现有表tb_test的数据如下:
A | B |
---|---|
aa | bb |
aa | cc |
bb | cc |
aa | bb |
aa | cc |
bb | cc |
创建一个无参存储过程,用于删除tb_test表中重复记录的操作。代码如下:
CREATE OR REPLACE PROCEDURE proc_del_dup_rec
AS
BEGIN
DELETE tb_test a
WHERE a_ROWID=
<
SELECT MAX<ROWID> FROM tb_test b
WHERE
a.a=b.a AND
a.b=b.b
>;
END;
执行不带参数的存储过程
在SQL*Plus环境中,调用过程有两种方法:使用exec命令和使用call命令。使用exec命令调用过程proc_del_dup_rec的代码如下:
exec proc_del_dup_rec;
经验:在调用过程时,也可以使用PL/SQL块的方式,以下代码将以PL/SQL块的方式来调用proc_del_dup_rec过程:
begin
proc_del_dup_rec
end;
调用过程中,不需要添加exec或call关键字。
带输入参数的存储过程
创建过程中,可以使用输入参数将应用程序的数据传递到过程中。当为过程定义参数时,如果不指定参数模式,将默认为输入参数。另外,可以使用IN关键字显示地定义输入参数。
以下代码将示例在emp_copy中添加一条记录,empno为已有empno的最大值+1,ename不能为空却长度必须大于0,depton为60。
create or replace procedure insert_emp(emp_name in varchar2,dept_no in number) as
begin
declare max_empno number;
begin
if(emp_name is null or length(ema_name) = 0) then
return;
end if
if(dept_no != 60) then
return;
end if;
select max(empno) into max_empno from emp_copy
insert into emp_copy(empno,ename,deptno) values(max_empno+1,emp_name,dept_no);
end;
end insert_emp;
调用存储过程:
execute insert_emp('Li Si',60);
带输出参数的存储过程
在带输入参数的存储过程insert_emp的需求之上增加需求:要分别统计表emp_copy插入数据前后的记录数。代码如下:
create or replace procedure insert_emp(emp_name in varchar2,dept_no in number,original_count ont number,current_count out number) as
begin
declare max_empno number;
begin
if(emp_name is null or length(ema_name) = 0) then
return;
end if
if(dept_no != 60) then
return;
end if;
select count(1) into original_count from emp_copy;
select max(empno) into max_empno from emp_copy
insert into emp_copy(empno,ename,deptno) values(max_empno+1,emp_name,dept_no);
select count(1) into current_count from emp_copy;
end;
end insert_emp;
调用存储过程:
declare count1 number;
declare count2 number;
begin
insert_emp('Wang Wu',60,count1,count2);
dbms_output.put_line('Original count of table emp_copy is' || count1);
dbms_output.put_line('Current count of table emp_copy is' || count2);
end;