一.存储过程
存储过程:是一个有名字得plsql 代码块,一般用来实现某个业务或功能,他没有返回值,但有输出参数(可以将数据传给外部程序),参数类型有三种类型输入参数,输出参数,输入输出参数,存储过程创建之后,会保存到数据库中,当数据库启动时,自动加载到内存中(一次创建多次使用)
存储过程创建语法
create [or replace] procedure 存储过程名[(参数名 in|out|in out)数据类型] is
—声明部分
begin
——代码块
——异常处理部分
end;
in : 输入参数,参数值只能使用,不能修改,它是默认得参数类型关键字可以省略可以以任意方式传参
out:输出参数,将程序处理结果传给外部程序,它只能以传变量得方式传参
in out:结合in 和 out 参数,它也只能以传变量得方式传参
1.存储过程得调用
(1)在代码块中调用
declare
begin
存储过程名[(参数..)];
end
2使用call(sql命令)命令调用
call 存储过程名(参数);--没有参数时不能省略
3使用exec(sqlplus)命令调用
exec 存储过程名(参数)–只能在sqlplus中使用
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(r);
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(c);
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;
二函数
函数
:函数也是一个有名字的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;