存储过程

1.存储过程
存储过程：是一个有名字的plsql代码块，一般用来实现某个业务或功能 。
它没有返回值，但有输出参数（可以将数据传给外部程序），
参数类型有三种类型输入参数，输出参数，输入输出参数，
存储过程创建之后，会保存到数据库中，当数据库启动时，
自动加载到内存中（一次创建多次使用）

存储过程的创建语法：
create [or replace] procedure 存储过程名[(参数名 in|out|in out 数据类型,参数 …)]
is|as
–声名部分
begin
–代码块
–异常处理部分
end;

in：输入参数，参数值只能使用，不能修改，它是默认的参数类型关键字可以省略
可以以任意方式传参
out：输出参数，将程序处理结果传给外部程序，它只能以传变量的方式传参
in out：结合in和out参数，它也只能以传变量的方式传参

select ‘drop procedure ‘||object_name||’;’ from user_objects where object_type=‘PROCEDURE’;

–写一个带参数的存储过程，打印99乘法表
create or replace procedure p1
is
begin
for i in 1…9 loop
for j in 1…i loop
dbms_output.put(j||’’||i||’=’||(ij)||’ ');
if ij < 10 then
dbms_output.put(’ ');
end if;
end loop;
dbms_output.new_line();
end loop;
end;

存储过程的调用：
(1)在代码块中调用
declare

begin
存储过程名[(参数,…)];
end;

begin
p1();
end;

begin
p1;
end;
(2)使用call（sql命令）命令调用
call 存储过程名(参数); --没有参数时不能省略()

call p1();

(3)使用exec（sqlplus命令）命令调用
exec 存储过程名(参数); --只能在sqlplus中使用

exec p1();
SQL> set serveroutput on
SQL> exec p1();
11=1
12=2 22=4
13=3 23=6 33=9
14=4 24=8 34=12 44=16
15=5 25=10 35=15 45=20 55=25
16=6 26=12 36=18 46=24 56=30 66=36
17=7 27=14 37=21 47=28 57=35 67=42 77=49
18=8 28=16 38=24 48=32 58=40 68=48 78=56 88=64
19=9 29=18 39=27 49=36 59=45 69=54 79=63 89=72 9*9=81
PL/SQL procedure successfully completed

（1）in：输入参数，参数值只能使用，不能修改，它是默认的参数类型关键字可以省略
可以以任意方式传参
–写一个存储过程，传入一个数字类型参数，计算这个数的阶乘，并打印结果
create or replace procedure p2(n in number)–默认参数类型为输入参数 in可以省略
is
–声名一个变量保存阶乘结果，初始值为1
r number:=1;
begin
–n:=5; --不可以在存储过程代码中修改in类型参数的值
for i in 1…n loop
r:=r*i;
end loop;
dbms_output.put_line®;
end;

call p2(6);

declare
m number:=&m;
begin
p2(m);
end;

begin
p2(n=>3);
end;

(2)out：输出参数，将程序处理结果传给外部程序，它只能以传变量的方式传参
–写一个存储过程，传入两个参数，一个参数表示部门编号，一个参数数字类型参数
–根据部门编号，查询出部门下的员工人数，并将员工人数传给外部程序使用
create or replace procedure p3(dno number,n out number)
is
begin
select count(1) into n from emp where deptno=dno;
end;

declare
–声名一个变量保存员工的人数
c number;
begin
–调用存储过程p3
p3(20,c);
–打印变量c值（20号部门的员工人数）
dbms_output.put_line©;
end;
3.结合in和out参数，它也只能以传变量的方式传参
create or replace procedure p4(n in out number)
is
begin
select count(1) into n from emp where deptno=n;
end;
–调用存储过程p4
declare
–声名一个变量保存员工人数和部门编号
v number:=&部门编号;
begin
–调用
p4(v);
–打印员工人数
dbms_output.put_line(v);
end;

–写一个存储过程，传入一个部门编号，将部门下的员工信息返回
create or replace procedure p5(dno number,c out sys_refcursor)
is
begin
–打开游标
open c for select * from emp where deptno=dno;
end;

–调用存储过程p5
declare
–定义一个游标变量
cur sys_refcursor;
–声名一个变量保存游标的一条记录
v emp%rowtype;
begin
–调用存储过程
p5(20,cur); --相当于打开游标
–遍历游标
loop
–fetch into 语句
fetch cur into v;
–退出循环语句
exit when cur%notfound;
–循环体语句
–打印员工信息
dbms_output.put_line(v.empno||’,’||v.ename||’,’||v.job||’,’||v.mgr||’,’||v.hiredate||’,’||v.sal||’,’||v.comm||’,’||v.deptno);

end loop;
–关闭游标
close cur;
end;

2.函数
函数：函数也是一个有名字的plsql代码块，一般用来完成一个功能或者数学计算
函数有返回值，并且在调用时必须使用到返回值，参数类型也分输入，输出
和输入输出参数，函数中不允许使用临时表，在一些情况下不能使用dml语句
函数可以在代码块中调用，也可以在sql语句中使用

语法：
create [or replace] function 函数名[(参数 in|out|in out 数据类型,…)]
return 返回值类型
is
–声名部分
begin
–代码块
return语句;
–异常处理
end;
–写一个函数计算一个数的阶乘，并返回阶乘结果
create or replace function fn1(n number)
return number
is
–声名一个变量保存阶乘结果
r number:=1;
begin
–计算阶乘
for i in 1…n loop
r:=r*i;
end loop;
–return 将阶乘结果返回
return r;
end;

函数的调用：必须使用它的返回值
1.在代码块中调用
declare
begin
fn1(5); --不允许，因为没有使用函数的返回值
end;

begin
dbms_output.put_line(fn1(5));
end;
declare
–声名一个变量保存阶乘结果
v number;
begin
v:=fn1(5);
dbms_output.put_line(v);
end;

begin
if fn1(5)>1000 then
dbms_output.put_line(‘haha’);
end if;
end;
(2)在sql语句中调用
select fn1(5) from dual;

–函数的递归调用（自己调用自己）
–写一个函数使用递归调用的方式实现求一个数的阶乘
create or replace function fn2(n number)
return number
is
begin
/***
fn2(5) n=5 判断n<=2不成立 else nfn2(n-1)=5fn2(4)
fn2(4) n=4 …n<=2… else nfn2(n-1)=4fn2(3)
fn2(3) n=3 … else nfn2(n-1)=3fn2(2)
fn2(2) n=2 …n<=2成立 return n 2 fn2(2)=2
5fn2(4)=54fn2(3)=543fn2(2)=543*2=5!

  n<=2是临界条件（类似退出循环条件）

/
if n <=2 then
return n;
else
return nfn2(n-1);
end if;
end;

select fn2(5) from dual;

select fn2(4) from dual;

/**
集团
公司1
部门1
大组1
小组1
员工1
员工2
员工3
小组2
大组2
部门2
部门3
公司2
公司3

集团–》公司1–》部门1–》大组–》--》小组

ID,部门名称，上级部门ID --最上级的上级部门ID是空

–写一个函数实现，输入一个员工编号，查询出员工所有上级
老板,经理,组长,姓名1