oracle存储过程精讲

1、存储过程（过程）

a、概念：

是一个命名的程序块(命名块比匿名块更好用)

这种有名字的PL/SQL块称之为PL/SQL存储程序单元或子程序，是被存储到数据库中的。

子程序又分为4种：存储过程、函数、包和触发器。

过程主要用于在数据库中完成特定的操作或者任务。

命名块又称为存储程序单元或子程序。

b、存储过程优点

简化客户端应用程序的开发和维护

提高应用程序的运行性能

无需通过网络传输源代码，降低了网络通信的负担，运行速度快

自动完成需要预先执行的任务：

可以在系统启动时自动执行，而不必再进行手工操作，大大方便了用户的使用。

2、创建过程

语法：

create or replace procedure name [parameter,…] –-----头部
IS｜AS. ---------------------------------------------两者都可，选其一
变量，常量声明; ---------------------------------------即使没有变量、常量，is|as也不能省略
BEGIN
	Sql语句和PL/SQL语句
[EXCEPTION]
	异常处理语句
END;

说明：create or replace在创建视图的时候学习过。

a、创建简单无参的存储过程

例：创建一个过程，能输出“hello world”

create or replace procedure sayhello as     -- as换成is也是可以的
BEGIN
  dbms_output.put_line('hello world');
END ;

提示：procedure sayhello 已编译

(1)、过程的调用（exec、call、匿名块3种方式）

过程的调用

1、使用execute命令
exec sayhello(); --括号必须有

2、使用call命令
call sayhello();

3、在匿名的程序块中直接以过程名调用
BEGIN
  sayhello();
END;

上面三种调用过程的方式都可以输出hello world语句。

b、创建有参数的存储过程

在建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)。

通过在过程中使用输入参数，可以将数据传递到执行部分；

通过使用输出参数，可以将执行部分的数据传递到应用环境。

过程参数的语法：
parameter_name in|out|in out|datatype [:=|default expr]
In：表示参数是输入给过程的
out：表示参数在过程中将被赋值，可以传给过程体的外部

说明：默认情况下是输入参数in，in是可以省略的。

例： 创建一个过程，用于给指定的员工涨100元钱。并且输出涨前和涨后的工资。

create or replace procedure raisesal(eno in emp.empno%type)
As
	p_sal emp.sal% TYPE;
BEGIN
　	  --涨前工资
 	  select sal into p_sal from emp where empno=eno;
	  --给员工涨工资
	  update emp set sal=sal+100 where empno=eno;
	　--输出结果
 	  dbms_output.put_line('涨前：'|| p_sal||'，涨后：'||(p_sal+100));
end;

exec raisesal(7369);

call  raisesal(7369);

begin 
	raisesal(7369);
end;

注：eno是参数变量，p_sal也是声明的一个变量。

定义参数的类型的时候可以使用%type，也可以自定义类型varchar2、number等，但是和游标一样，是不能加精度的，比如varchar2(2)、number(2)是不可以的。

注意：(p_sal+100)这个地方一定是有括号的，才能够进行相加

另一种想法💡：可以改变begin中的语句进行输出，其结果输出相同，如下代码：

create or replace procedure raisesal(eno in emp.empno%type)
As
	p_sal emp.sal% TYPE;
	p_sal2 emp.sal% TYPE;
BEGIN
 	select sal into p_sal from emp where empno=eno;
	update emp set sal=sal+100 where empno=eno;
    select sal into p_sal2 from emp where empno=eno;
 	dbms_output.put_line('涨前：' || p_sal ||  '，涨后：'||p_sal2);
end;

(1)、带in参数的过程的调用

1、按位置调用
exec raisesal(7369);

2、按名称调用
在调用存储过程的参数列表中不仅提供参数名，还指定给它传递的参数值两部分。
exec raisesal(eno=>7369)

注意：按名称调用参数的赋值可以不用按照顺序，但是如果是按照位置调用过程的话，那么就必须使给变量赋值的顺序和声明变量的顺序一致。

例： 创建一个过程，用于给指定的员工涨100元钱。并且输出涨前和涨后的工资。

create or replace procedure raisesal(eno in emp.empno%type,e_name emp.ename%type)
As
p_sal emp.sal% TYPE;
BEGIN
	　--涨前工资
 	 select sal into p_sal from emp where empno=eno;
	  --给员工涨工资
	  update emp set sal=sal+100 where empno=eno;
	　--输出结果
	  dbms_output.put_line('涨前：'|| p_sal||'，涨后：'||(p_sal+100));
end;

exec raisesal(7369,'SMITH');

exec raisesal(e_name=>'SCOTT',eno=>7369);

exec raisesal(7369,‘SMITH’);
exec raisesal(e_name=>‘SCOTT’,eno=>7369);
上面这两种调用方式我们可以看出：按位置调用的时候必须按照变量声明的顺序进行赋值，而按名称调用的使用，即通过“=>”这种方式调用的方法，可以不用按照声明变量的顺序进行赋值。

---

可以设置参数的默认值，如果调用时不指定值，则为默认值

格式1:

create or replace PROCEDURE raisesal(eno in emp.empno% TYPE default 7369)
exec raisesal();

格式2:

create or replace procedure raisesal(eno in emp.empno%type:=7788)
exec raisesal();

例： 创建一个过程，用于给指定的员工涨100元钱。并且输出涨前和涨后的工资。

create or replace procedure raisesal(eno in emp.empno%type default 7788)
As
p_sal emp.sal% TYPE;
BEGIN
　--涨前工资
  select sal into p_sal from emp where empno=eno;
  --给员工涨工资
  update emp set sal=sal+100 where empno=eno;
　--输出结果
  dbms_output.put_line('涨前：'|| p_sal||'，涨后：'||(p_sal+100));
end;

exec raisesal();  --不传参数就相当于使用了默认值

注意：上面调用过程的时候exec raisesal();没有给有默认值的参数赋值，这就使得eno这个变量使用默认值7788.

如果没有给读入参数设置默认值，而且调用过程的时候也不给参数赋值，那么就出错了，只有设置了默认值的参数变量，在调用过程的时候才不需要赋值(当然可以进行赋值而不使用默认值)。

(2)、带out参数的存储过程

创建：查询指定员工的姓名和薪水，并利用out模式将参数值传给调用者。

create or replace procedure show(eno emp.empno%TYPE,v_name out emp.ename%TYPE,v_sal out emp.sal%TYPE)
IS
BEGIN
    select ename,sal into v_name, v_sal from emp where empno=eno;
EXCEPTION　
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('没有这个员工');
END;

declare
	eno emp.empno%TYPE;
	p_name  emp.ename%TYPE;
	p_sal emp.sal%TYPE
begin
	show(7788,p_name,p_sal);
	--show(v_name=>p_name,v_sal=>p_sal,eno=>7788);
	dbms_output.put_line(p_name || '          ' || p_sal);
end;

说明：过程名为show的过程，前一个参数是输入参数，in是可以省略的，后两个参数是输出参数，out是不能省略的。

上面将out输出变量是在匿名块中完成调用输出的。

在匿名块中调用，在匿名块中把in参数和out参数都设置为变量，然后输出out参数变量

说明：上面在匿名块中输出out参数变量的过程也可以使用下面的代码实现：

set serveroutput on
DECLARE
 	 eno emp.empno% TYPE:=&no;
 	 V_NAME emp.ename% TYPE;
	  v_sal emp.sal% TYPE; 
BEGIN
	  show(eno,v_name,v_sal);
	--show(p_name=> v_name, p_sal=> v_sal, eno=> eno);
	  dbms_output.put_line('编号为'||eno||'的员工姓名是：'||v_name||'，其薪水是：'||v_sal);
END;

---

(3)、在命令窗口通过绑定变量调用

创建：查询指定员工的姓名和薪水，并在命令窗口通过绑定变量调用

1、创建过程：

create or replace procedure show(eno emp.empno%TYPE,v_name out emp.ename%TYPE,v_sal out emp.sal%TYPE)
IS
BEGIN
    select ename,sal into v_name, v_sal from emp where empno=eno;
EXCEPTION　
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('没有这个员工');
END;

2、在sqlplus下输入下面的命令，在命令窗口通过绑定变量调用：

var v_name varchar2(10); --不能用字段类型
var v_sal number;　      --number类型不能加长度
exec show(7369,:v_name,:v_sal); --变量绑定（普通变量前面加上冒号 :   ），调用存储过程（这种写法的赋值顺序必须和声明变量时的顺序保持一致）
print v_name v_sal;    --打印变量，此处变量名前面没有冒号“:”
select :v_name,:v_sal from dual;   --查询变量输出，此处变量名前面应该有冒号“:”

(4)、带in out参数的存储过程

in out这样的既是输入参数又是输出参数（既可以读入，又可以输出），in out是不能省略的。

例：输入两个数值，然后将两个数值的数据交换，再输出两个数值的值：

create or replace procedure swap(n1 in out number,n2 in out number)
as 
ptemp number;     --用于交换时的临时变量
begin 
	ptemp :=n1;
	n1 := n2;
	n2 := ptemp;
end;

declare 
	num1 number(5) := &first;
	num2 number(5) := &second;
begin
	dbms_output.put_line('num1=' || num1 || '              num2=' || num2);
	swap(num1,num2);
	dbms_output.put_line('num1=' || num1 || '              num2=' || num2);
end;

3、修改与删除过程

修改：
or replace

删除：
当某个过程不再需要时，应将其从内存中删除，以释放它占用的内存资源。
语法格式：
DROP PROCEDURE 　过程名
例：
drop procedure raisesal

4、查看存储过程

存储过程的信息都存储在数据字典中
（一）user_objects显示有关对象的信息。
（二）user_source显示源代码文本。

select object_name,object_type,status from user_objects where object_name='SHOW';

select line,text from user_source where name='SHOW';

5、练习：

a、创建一个存储过程，根据提供的雇员姓名，查询该雇员的上级领导人的姓名，并返回。

方案1：

create or replace procedure fanhui(sname in emp.ename%type,v_name out emp.ename%type)
as 
begin
    select ename into v_name from emp where empno=(select mgr from emp where ename = sname);
end;

declare
  sname emp.ename%type;
  v_name emp.ename%type;
begin
  fanhui(sname=>'SCOTT',v_name=>v_name);
  dbms_output.put_line(v_name);
  end;

select * from emp;

方案2：

create or replace procedure fanhui(sname in emp.ename%type,v_name out emp.ename%type)
as 
pmgr emp.mgr%type;
begin
    select mgr into pmgr from emp where ename=sname;
    select ename into v_name from emp where empno=pmgr;
end;

declare
    s_name emp.ename%type;
    m_name emp.ename%type;
begin
    fanhui(sname=>'SCOTT',v_name=>m_name);
    dbms_output.put_line(m_name);
end;