存储过程
-- 定义:
在大型数据库中,存储过程是一组为了完成特定功能的SQL语句集。
存储在数据库中,经过第一次编译后再次调用,不需要再次编译,用户通过指定存储过程的名字并给出参数来执行它(如果该存储过程有参数)。
存储过程大大的提高了SQL语句的功能和灵活性。
-- 创建存储过程:
create procedure 过程名(参数 参数数据类型) is/as
声明变量
begin
过程体
end 过程名;
-- 修改存储过程:
create or replace procedure 过程名 ……
在 create 后面加上 or replace
-- 删除存储过程:
drop procedure 过程名;
-- 不带参数的存储过程:
Ex1:创建一个简单的存储过程pro_1,调用该过程时,将emp表中的empno为7369的员工的ename修改为CANDY。
create procedure pro_1 as
begin
update emp set ename = 'CANDY' where empno = 7369;
end pro_1;
-- 过程创建好后,过程体中的内容并没有执行,仅仅只是被编译,需要调用该过程。
-- 调用过程的两种形式:execute / call
exec pro_1;
或者:
call pro_1();
-- 带参数的存储过程:
-- 过程的参数分为 in参数 和 out参数 和 in out参数
-- in参数,输入参数。在调用的时候要传入一个值。
如果在定义的时候没有说明参数类型,则默认为in参数
-- out参数,输出参数。调用完之后输出结果。
-- in out参数,同时拥有in参数和out参数的特性,既能接受用户的传值,又允许在过程体中修改其值,并可以将值返回。
但是它不接受常量值,只能使用变量为其传值。
-- in参数:
Ex2:创建带in参数的存储过程pro_2,为该过程设置两个in参数,分别用于接受用户提供的empno和ename。
并且调用pro_2,通过该过程将empno为7369的员工的ename修改为ELLA。
create procedure pro_2(emp_no in number,emp_name in VARCHAR2) is
begin
update emp set ename = emp_name where empno = emp_no;
end pro_2;
-- 赋值的形式主要有以下两种:
-- 不指定参数名:
exec pro_2(6500,'ELLA');
-- 指定参数名:
exec pro_2(emp_name => 'ELLA',emp_no => 7369);
-- out参数:
Ex3:创建存储过程pro_3,为该过程设置一个in参数和一个out参数,其中in参数接受用户提供的empno,然后在该过程体中将empno对应的ename值传递给out参数。
调用存储过程pro_3,为其in参数赋值为7369,并声明变量emp_name接受与输出其out参数的返回值。
create procedure pro_3(emp_no in number,emp_name out VARCHAR2)is
begin
select ename into emp_name from emp where empno = emp_no;
end pro_3;
调用存储过程pro_3
VARIABLE emp_name VARCHAR2(10);
exec pro_3(7369,:emp_name);
print emp_name;
-- 注: 调用存储过程,如果需要显示该过程中out参数的返回值,还需要实现使用variable语句声明对应的变量接受返回值,并在调用过程中绑定该变量。
-- 注:在execute语句中绑定变量时,需要在变量名前添加冒号(:)
-- in out参数:
Ex4:创建存储过程pro_4,通过该过程交换两个变量(n1 / n2)中的值。
调用pro_4过程,调用前声明为in out参数赋值的变量,调用后使用select语句输出交换值后的结果。
create procedure pro_4(n1 in out number,n2 in out number)is
temp1 number;
temp2 number;
begin
temp1 := n1;
temp2 := n2;
n1 := temp2;
n2 := temp1;
end pro_4;
调用pro_4过程:
variable n1 number;
variable n2 number;
exec :n1 :=1111;
exec :n2 :=9999;
exec pro_4(:n1,:n2);
-- 注:使用execute命令可以为变量赋值,要在变量名前添加冒号(:)