存储过程概述
存储过程是子程序的一种类型，能够完成一些任务，作为schema对象存储于数据库。是一个有名字的pl/sql代码块，支持接收或不接受参数，同时也支持参数输出。一个存储过程通常包含定义部分，执行部分，exception部分，可以被其他子程序调用，也可以被重用。
过程定义
create ［or replace］procedure procedure_name
[(argument_name [in | out | in out] argument_type)]
as | is
begin
procedure_body;
end [procedure_name];
存储过程中参数的类型
in：表示是一个输入参数，可以指定缺省值。如省略参数类型，则缺省为in类型
out：表示是一个输出参数
in out：既可以作为一个输入参数，也可以作为一个输出参数来输出结果
过程调用
execute ｜call procedure_name [(argument_list)]
例：定义一个过程，以job为参数，查询该job的最高工资、最低工资、平均工资。
create or replace procedure query_sal(v_job in emp.job%type)
as
v_min_sal emp.sal%type;
v_max_sal emp.sal%type;
v_avg_sal emp.sal%type;
begin
select min(sal) into v_min_sal from emp where job = v_job;
select max(sal) into v_max_sal from emp where job = v_job;
select avg(sal) into v_avg_sal from emp where job = v_job;
dbms_output.put_line('this job is minimum salary is ' || v_min_sal);
dbms_output.put_line('this job is maximum salary is ' || v_max_sal);
dbms_output.put_line('this job is average salary is ' || v_avg_sal);
exception
when no_data_found then
dbms_output.put_line('not record found');
end;
sql> set serveroutput on
sql> exec query_sal('salesman');
this job is minimum salary is 1250
this job is maximum salary is 1600
this job is average salary is 1400
pl/sql procedure successfully completed.
参数及其传递方式
在 建立过程时，传递的参数为可选项，如果省略参数选项，则过程为无参过程（定义时不指定参数，调用时也不需要参数）。如果指定参数选项，则过程为有参过程 （定义时需要指定参数名字、模式、数据类型，调时时需要给出对应的参数值），定义时的参数，称为形参，调用时的参数称为实参。
无参过程
create or replace procedure display_systime
as
begin
dbms_output.put_line('current time is ' || sysdate);
end;
sql> exec display_systime;
current time is 24-feb-13
有参过程
定义时需要指定参数的名字、模式、数据类型
例：定义一个添加记录的过程（全部为输入参数）
create or replace procedure add_emp
(
v_no in emp.empno%type,
v_name in emp.ename%type,
v_dept in emp.deptno%type default 20 --缺省的部门号
)
as
begin
insert into emp (empno,ename,deptno) values(v_no,v_name,v_dept);
exception
when dup_val_on_index then
dbms_output.put_line('record exists');
end ;
sql> exec add_emp(7369,'test',20); --调用
record exists
例：定义一个输入员工编号，修改记录，再返回修改后的结果（姓名和工资）。
create or replace procedure ed_emp
(
v_no in emp.empno%type, --定义了一个in类型，二个out类型的参数
v_name out emp.ename%type,
v_sal out emp.sal%type)
as
begin
update emp set sal = sal + 100 where empno = v_no;
select ename,sal into v_name,v_sal from emp where empno = v_no;
exception
when no_data_found then
dbms_output.put_line('not data found');
end;
/
procedure created.
sql>variable t_name varchar2(20);
sql>variable t_sal number;
sql> exec ed_emp(7369,:t_name,:t_sal);
pl/sql procedure successfully completed.
sql> print t_name
t_name
--------------------------------------------------------------------------------
smith
sql> print t_sal
t_sal
----------
900
例：in out类型参数的使用
create or replace procedure comp
(num1 in out number,num2 in out number)
as
v1 number;
v2 number;
begin
v1 := num1 + num2;
v2 := num1 * num2;
num1 := v1;
num2 := v2;
end;
sql> var v1 number
sql> var v2 number
sql> exec :v1 := 3
pl/sql procedure successfully completed.
sql> exec :v2 := 5
pl/sql procedure successfully completed.
sql> exec comp(:v1,:v2);
sql> print v1 v2
v1
----------
8
v2
----------
15
sql> exec comp(:v1,:v2);
pl/sql procedure successfully completed.
sql> print v1 v2
v1
----------
23
v2
----------
120
可以看到in out类型的参数既作为输入参数又作为输出参数。
存储过程参数的传递方式：
按位置传递：
实参按顺序将值传给形参
execute ed_emp(7900,:t_name,:t_sal);
execute ed_emp(8000,'test2',20);
按名字传递
execute ed_emp(v_name=>'abcde',v_dept=>10,v_no=>8003);
混合传递
execute ed_emp(8005,v_dept=>20,v_name=>'test5');
过程管理
查看系统过程信息
dba_objects
dba_procedures
dba_source
使用desc procedure_name 查看存储过程的参数信息
sql>desc ed_emp;
procedure ed_emp
argument name type in/out default?
------------------------------ ----------------------- ------ --------
v_no number(4) in
v_name varchar2(10) out
v_sal number(7,2) out
从dba_objects获得存储过程的信息
sql>select owner,object_name,object_type,status from dba_objects where object_name = 'ed_emp';
owner object_name object_type status
------------------------------ -------------------- --------------- -------
scott ed_emp procedure valid
sql>select object_name,procedure_name,interface,authid from user_procedures;
object_name procedure_name int authid
-------------------- ------------------------------ --- ------------
display_sal no definer
ed_emp no definer
查看存储过程的源代码
sql>select line, text from user_source where name='ed_emp';
line text
---------- --------------------------------------------------------------------------------
1 procedure ed_emp
2 (
3 v_no in emp.empno%type,
4 v_name out emp.ename%type,
5 v_sal out emp.sal%type
6 )
7 as
8 begin
9 update emp set sal=sal+100 where empno=v_no;
10 select ename,sal into v_name,v_sal from emp where empno=v_no;
11 exception
12 when no_data_found then
13 dbms_output.put_line('not found record!');
14 end ed_emp;
查看错误信息
show errors
函数概述
函数通常用于返回特定的数据。其实质是一个有名字的pl/sql块，作为一个schema对象存储于数据库，可以被反复执行。函数通常被作为一个表达式来调用或存储过程的一个参数，具有返回值。
一、建立函数的语法
create [ or replace ] function function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datetype2,
...)
return datatype
is | as
[local_variable_declarations;...]
begin
--actions;
return expression;
end [function_name];
建立函数的几点注意事项
1.指定参数数据类型时(argument)，不能指定其长度
2.函数头部必须指定return子句，函数体内至少要包含一条return语句
3.可以指定in参数，也可以指定out参数，以及in out 参数
4.可以为参数指定缺省值。指定缺省值时使用default关键字。如arg1 varchar2 default 'scott'
使用函数的优点:
1.增加了代码的灵活性，可以完成一些较为复杂的任务，以及仅仅通过sql无法完成的任务
2.可以直接将函数使用到where子句中来过滤数据
3.可以作为存储过程的参数使用，是存储过程的一种补充
建立函数
建立不带参数的函数
create or replace function get_user
return varchar2
as
v_user varchar2(20);
begin
select username into v_user from user_users;
return v_user;
end;
使用全局变量接收函数的返回值
sql> var v1 varchar2(20);
sql> exec :v1 := get_user;
pl/sql procedure successfully completed.
sql> print v1
v1
--------------------------------------
scott
使用本地变量接收函数的返回值
sql> declare user_name varchar2(20);
2 begin
3 user_name := get_user();
4 dbms_output.put_line('current user: ' || user_name);
5 end;
6 /
current user: scott
pl/sql procedure successfully completed.
在sql语句中直接调用函数
sql> select get_user from dual;
get_user
------------------------------------
scott
使用dbms_output调用函数(此调用作为存储过程的一个参数来进行调用)
sql> set serveroutput on;
sql> exec dbms_output.put_line('current user: '||get_user);
current user: scott
建立带有in 参数的函数
create or replace function raise_sal(name in varchar2)
return number
as
new_sal emp.sal%type;
begin
select sal * 1.2 into new_sal from emp
where upper(ename) = upper(name);
return new_sal;
exception
when no_data_found then
raise_application_error(-20000,'current employee does not exists');
end;
sql> select sal,raise_sal('scott') from emp where ename='scott';
sal raise_sal('scott')
---------- ------------------
3000 3600
sql> select sal,raise_sal('scottt') from emp where ename='scott';
select sal,raise_sal('scottt') from emp where ename='scott'
*
error at line 1:
ora-20000: current employee does not exists
ora-06512: at scott.raise_sal, line 11
建立带有out参数的函数
create or replace function get_info
(name varchar2,titile out varchar2)
return varchar2
as
deptname dept.dname%type;
begin
select e.job,d.dname into titile,deptname
from emp e,dept d
where e.deptno = d.deptno
and upper(e.ename) = upper(name);
return deptname;
exception
when no_data_found then
raise_application_error(-20000,'current employee does not exists');
end;
/
注意对于使用out参数的函数，不能使用sql语句来调用。而必须定义变量接收out参数和函数的返回值。
调用如下
sql> var job varchar2(20);
sql> var dname varchar2(20);
sql> exec :dname := get_info('scott',:job);
pl/sql procedure successfully completed.
sql> print dname job
dname
-------------------------------------------------
research
job
-------------------------------------------------
analyst
sql> select get_info('scott') from dual
2 ;
select get_info('scott') from dual
*
error at line 1:
ora-06553: pls-306: wrong number or types of arguments in call to 'get_info'
建立带有in out参数的函数
create or replace function comp
(num1 number,num2 in out number)
return number
as
v_result number(6);
v_remainder number;
begin
v_result := num1 * num2;
v_remainder := mod(num1,num2);
num2 := v_remainder;
return v_result;
exception
when zero_divide then
raise_application_error(-20000,'divsion by zero');
end;
/
sql> var n1 number
sql> var n2 number
sql> exec :n2 := 10;
pl/sql procedure successfully completed.
sql> exec :n1 := comp(16,:n2);
pl/sql procedure successfully completed.
sql> print n1 n2
n1
----------
160
n2
----------
6
函数的调用及限制
1.函数的调用(其具体调用方法参照上面的演示)
a.使用全局变量接收函数的返回值
b.使用本地变量接受函数的返回值
c.在sql语句中直接调用函数
d.使用dbms_output调用函数
注：函数在调用的时候需要按位置指定参数，没有存储过程参数传递灵活必须具有execute 函数的权限
2.函数在sql中调用的主要场合
由于函数必须要返回数据，因此只能作为表达式的一部分调用。此外函数可以在sql语句的以下部分调用
a. select 命令的选择列表或子查询中
b. 条件表达式where, having子句中
c. connect by , start with ,order by 以及group by 子句中
d. insert 命令的values子句中
f. update 命令的set 子句中
3.函数在sql中调用的限制
a. sql语句中只能调用存储在服务器端的函数，而不能调用存储于客户端的函数
b. sql语句中调用的函数只能带有输入参数in，而不能带有输出参数out 以及输入输出参数in out
c. sql语句中调用的函数只能使用sql支持的标准数据类型，不能使用pl/sql特有的类型，如boolean,table,record等
d. sql语句中调用的函数不能包含insert ,update 和delete 语句
创建一张表tb_emp
sql> create table tb_emp as select * from emp;
table created.
创建一个函数，用于删除tb_emp表中指定的empno号的雇员信息，并返回其薪资
sql> create or replace function del_emp
2 (no number)
3 return number
4 as
5 v_sal emp.sal%type;
6 begin
7 select sal into v_sal from emp where empno = no;
8 delete tb_emp where empno = no;
9 return v_sal;
10 end;
11 /
function created.
使用sql语句调用时，收到了错误信息，在内部查询内不能完成dml操作
sql> select del_emp(7788) from dual;
select del_emp(7788) from dual
*
error at line 1:
ora-14551: cannot perform a dml operation inside a query
ora-06512: at scott.del_emp, line 8
使用exec执行时函数被成功执行
sql> var n1 number
sql> exec :n1 := del_emp(7788);
pl/sql procedure successfully completed.
sql> print n1
n1
----------
3000
函数的管理
函数使用了与存储过程相关的视图，可以从系统视图中获得函数的相关信息
dba_objects
dba_source
user_objects
user_source
查看函数的源码
sql>select line,text from user_source where name='del_emp' order by line
line text
---------- ------------------------------------------------------------
1 function del_emp
2 (no number)
3 return number
4 as
5 v_sal emp.sal%type;
6 begin
7 select sal into v_sal from emp where empno = no;
8 delete tb_emp where empno = no;
9 return v_sal;
10 end;
10 rows selected.
查看函数的参数信息
sql> desc del_emp;
function del_emp returns number
argument name type in/out default?
------------------------------ ----------------------- ------ --------
no number in
函数与存储过程的差异
存储过程 函数
---------------------------------- -------------------------------
不能被作为表达式调用 只能作为表达式被调用
声明头部关键字为procedure 声明头部关键字为function
声明头部不包含return关键字来描述返回类型 头部必须包含return关键字,且pl/sql块中至少包含一个有效的return语句
可以通过out,in out返回零个或多个值 通过return语句返回一个与头部声明中类型一致的值,也可使用in,in out返回值
sql语句中不可调用存储过程 sql语句可以调用函数
多用于数据库中完成特定的操作,如删除,更新,插入等dml操作 多用于特定的数据如选择等