存储过程是一种命名的PL/SQL程序块,它既可以没有参数,也可以有若干个输入、输出参数,甚至可以有多个既作输入又作输出的参数,但它通常没有返回值。存储过程被保存在数据库中,它不可以被SQL语句直接执行或调用,只能通过EXECUT命令执行或在PL/SQL程序块内部被调用。由于存储过程是已经编译好的代码,所以其被调用或引用时,执行效率非常高。
1、创建存储过程
创建存储过程需要使用PROCEDURE关键字,其基本语法格式如下:
create [or replace] procedure pro_name [(parameter1[,parameter2]…)] is|as
begin
plsql_sentences;
[exception]
[dowith_sentences;]
end [pro_name];
pro_name:存储过程名称。
parameter1:存储过程的参数,若是输入参数,则需要在其后面指定“in”关键字;若是输出参数,则需要在其后面指定“out”关键字。在in或out关键字的后面是参数的数据类型,但不能指定该类型的长度。
plsql_sentences:PL/SQL语句,它是存储过程功能实现的主体。
dowith_sentences:异常处理语句,也是PL/SQL语句,这是一个可选项。
【实例】创建一个存储过程,定义1个in模式的变量,用于传人部门编号;定义2个out模式的字符类型的参数,分别用于返回部门名称和部门地址。
create or replace procedure pro_dept(num_deptno in number,var_dname out dept.dname%type,var_loc out dept.loc%type) is
/*这里定义存储过程的内部变量*/
var_msg varchar2(100);
begin
select dname,loc
into var_dname,var_loc
from dept
where deptno = num_deptno;
exception
when no_data_found then --若查询语句无返回记录
var_msg:='未找到部门编号为'||num_deptno||'的记录';
dbms_output.put_line(var_msg);
end;
2、调用存储过程
【实例】在SQL*Plus环境中,使用execute(exec)命令执行pro_dept存储过程。
SQL> variable var_dname varchar2(50);
SQL> variable var_loc varchar2(50);
SQL> exec pro_dept(10,var_dname,var_loc);
【实例】在PL/SQL块中调用pro_dept存储过程
declare
var_dname dept.dname%type;
var_loc dept.loc%type;
begin
pro_dept(10,var_dname,var_loc);
dbms_output.put_line('部门名称:'||var_dname||' 部门地址:'||var_loc);
end;
3、存储过程的参数
存储过程可以接受多个参数,参数迷失包括IN、OUT、IN OUT这3中。
IN模式参数:这是一种输入类型的参数,参数值由调用方传人,并且只能被存储过程读取。
OUT模式参数:这一个输出类型的参数,表示这个参数在存储过程中已经被赋值,并且这个参数值可以传递到当前存储过程以外的环境中。
IN OUT模式参数:可以兼顾其他两种参数的特点,在调用存储过程时,可以从外界向该类型的参数传入值;在执行完存储过程之后,可以将该参数返回值传给外界。
4、IN参数的默认值
Oracle支持在声明IN参数的同时给其初始化默认值,这样在存储过程调用时,如果没有向IN参数传入值,则存储过程可以使用默认值进行操作。
【实例】创建一个存储过程,定义1个带默认值得IN参数。
create or replace procedure pro_seldept(num_deptno in number default 10) is
var_dname dept.dname%type; --定义存储过程内部变量,保存部门名称
var_loc dept.loc%type; --定义存储过程内部变量,保存部门地址
begin
select dname,loc
into var_dname,var_loc
from dept
where deptno = num_deptno;
dbms_output.put_line('部门名称:'||var_dname||' 部门地址:'||var_loc);
end;
调用存储过程:
begin
pro_seldept(); --查询默认部门编号的信息
pro_seldept(20); --查询编号为20的部门信息
end;