oracle存储过程

简介

存储过程,它是一个有名字的plsql代码块
创建之后会保存到数据库中

当数据库启动时,会自动加载到数据库内存中,执行效率高
存储过程有参数,没有返回值,但是有输出参数

创建

存储过程的创建语法

create [or replace] procedure 存储过程名
[(形参 [in|out|in out] 数据类型,...)]
is|as
  声明部分
begin
  plsql代码块
  exception
    异常处理部分  
end;

创建一个存储过程,打印10部门员工信息

create or replace procedure p
is
begin
  for v in (select * from emp where deptno=10) loop
    dbms_output.put_line(v.empno||' , '||v.ename||' , '||v.job||' , '||v.deptno);
  end loop;
end;

调用

1在plsql代码块中调用

语法

存储过程名[(实参)];

begin
  p();
end;

输出

9123 , june ,  , 10
7782 , CLARK , MANAGER , 10
7839 , KING , PRESIDENT , 10
7934 , MILLER , CLERK , 10

2使用call命令调用(sql命令)

语法

call 存储过程名(实参)

call p();

输出

9123 , june ,  , 10
7782 , CLARK , MANAGER , 10
7839 , KING , PRESIDENT , 10
7934 , MILLER , CLERK , 10

3使用exec命令调用(sqlplus命令)

语法

exec 存储过程名(实参)

SQL> set serveroutput on
SQL> exec p();
9123 , june ,  , 10
7782 , CLARK , MANAGER , 10
7839 , KING , PRESIDENT , 10
7934 , MILLER , CLERK , 10

PL/SQL 过程已成功完成。

传参

存储过程的参数

• 输入参数(in)
• 输出参数(out)
• 输入输出参数(in out)

传值方式

• 1传值
• 2传变量
• 3形参=>值

1,in参数

in参数:表示输入参数,可以使用任意一种传参方式
in参数实参的值只能被使用,不能被修改

注意,通过in参数传的值,不能在程序中修改eno变量中的值

创建一个存储过程,传入员工编号,打印员工信息

create or replace procedure p1(eno in emp.empno%type)
is
  --声明一个变量,保存员工信息
  v emp%rowtype;
begin
  --根据员工编号查询员工信息
  select * into v from emp where empno=eno;
  --打印员工信息
  dbms_output.put_line(v.empno||' , '||v.ename||' , '||v.job||' , '||v.deptno);
  exception 
    when no_data_found then
      dbms_output.put_line('员工不存在');
end;

调用

传值的方式调用p1

begin
  p1(7369);
end;

输出

7369 , SMITH , CLERK , 20

传变量的方式调用

declare
  empno number(4):=7499;
begin
  p1(empno);
end;

输出

7499 , ALLEN , SALESMAN , 30

形参=>值的方式调用

begin
  p1(eno=>7521);
end;

输出

7521 , WARD , SALESMAN , 30

2,out参数

out参数: 输出参数,它只能以传变量的方式传值,
out参数的值是可以修改的
将存储过程的运行结果放到变量中,通过变量将结果传给外部程序

创建一个存储过程
输入一个员工编号,查询员工信息,并将查询结果传给调用程序

create or replace procedure p2(eno in emp.empno%type,
                               v out emp%rowtype)
is
begin
  select * into v from emp where empno=eno;
  exception
    when no_data_found then
      dbms_output.put_line('员工不存在');
end;

调用

declare
  v1 emp%rowtype;
begin
  p2(7369,v1);
  dbms_output.put_line(v1.empno||' , '||v1.ename||' , '||v1.job||' , '||v1.deptno);
end;

输出

7369 , SMITH , CLERK , 20

创建一个存储过程计算一个数字的阶乘

create or replace procedure p3(n in number,res out number)
is
begin
  --将res初始值设置为1
  res:=1;
  for i in 1..n loop
    res:=res*i;
  end loop;
end;

调用

declare
  n number(10);
begin
  p3(6,n);
  dbms_output.put_line(n);
end;  

输出

720

3,in out参数

in out参数:输入输出参数,它拥有in和out的所有特性
必须以传变量的方式传参,传入参数的初始值是有意义的,值是可以修改的

创建一个存储过程,传入员工编号,查询员工信息,传给外部程序

create or replace procedure p4(v in out emp%rowtype)
is
begin
  select * into v from emp where empno=v.empno;
end;

调用

declare
  v1 emp%rowtype;
begin
  v1.empno:=7369;
  p4(v1);
  dbms_output.put_line(v1.empno||' , '||v1.ename||' , '||v1.job||' , '||v1.deptno);
end;

输出

7369 , SMITH , CLERK , 20