一、说明
子程序
把PL/SQL代码块编译后存储在数据库中。子程序包含三个部分,分别是声明部分(declare)、执行部分(begin)、异常处理部分(exception)。子程序有两种类型,分别是过程(procedure)和函数(function)。
二、函数(function)
大师说:talk is cheap, show me the code
2.1 无参function
create or replace function f_hello return varchar2 as
begin
return 'Hello,world!';
end;
declare
v_result varchar2(200);
begin
v_result := f_hello();
DBMS_OUTPUT.put_line(v_result);
end;
2.2 有参function
create or replace function f_hello(f_name varchar2) return varchar2 as
begin
return 'Hello,' || f_name;
end;
declare
v_result varchar2(200);
begin
v_result := f_hello('张三');
DBMS_OUTPUT.put_line(v_result);
end;
function实例
实例:有参function,获取年龄
create or replace function f_get_age(f_d1 date,f_d2 date) return number as
begin
return trunc(months_between(f_d1,f_d2)/12);
end;
declare
age number;
begin
age := f_get_age(sysdate,date'1994-01-01');
DBMS_OUTPUT.put_line(age);
end;
实例:有参function,根据ID获取学生信息
create or replace f_get_student(f_id number) return student%rowtype as
row student%rowtype;
begin
select * into row from student where id = f_id;
return row;
end;
declare
v_student student%rowtype;
begin
v_student := f_get_student(401);
DBMS_OUTPUT.put_line('ID:' || v_student.id || ',姓名:' || v_student.name);
end;
实例:有参function,根据BOOLEAN型返回'True'或'False'
create or replace function f_bln(f_a boolean) return varchar2 as
begin
if f_a=true then
return 'true';
else
return 'false';
end if;
end;
declare
a number;
b number;
c varchar2(200);
begin
a := 2;
b := 1;
c := f_bln(a > b);
DBMS_OUTPUT.put_line('a=' || a || ',b=' || b || ',a>b:' ||c);
end;
实例:无参function,编写一个函数,返回学生的平均年龄
create or replace function f_get_avg_age return number as
avg_age number;
begin
select round(avg(age)) into avg_age from student;
return avg_age;
end;
declare
avg_age number;
begin
avg_age := f_get_avg_age();
DBMS_OUTPUT.put_line('平均年龄是:' || avg_age);
end;
三、存储过程(procedure)
3.1 什么是存储过程?
存储过程(procedure)是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。
比如一个生成订单的过程,我需要记录订单表,日志表,更新客户信息表,更新客户积分等等的操作.那可以把这些操作都放在一个存储过程中实现。每次生成订单,只需要通过程序调用一下存储过程就可以了。而不需要通过程序去多次连接数据库,每次执行对一个表的操作。
3.2 存储过程如何授权?
//(1)授权给scott用户可以调用函数或存储过程(hello)
grant execute on hello to scott;
//(2)授权给所有用户都能调用函数或存储过程(hello)
grant execute on hello to public;
3.3 存储过程的优点有哪些?
(1)更好的重用:一次创建,任意使用。
(2)更快的执行效率:SQL语句大量执行或重复执行时,执行存储过程比SQL语句快。
(3)更少的网络流量:几百行的SQL语句,在网络中只需要发送一条存储过程执行的语句即可。
(4)更好的安全机制:可以授权存储过程给指定的用户,或者将存储过程设置为public。
procedure实例
实例:procedure交换参数
create or replace procedure p_swap(p_n1 in out number,p_n2 in out number) as
--声明临时变量
temp number;
begin
--交换参数
temp := p_n1;
p_n1 := p_n2;
p_n2 := temp;
end;
declare
a number;
b number;
begin
a := 1;
b := 2;
DBMS_OUTPUT.put_line('交换前:a=' || a || ',b=' || b);
--调用存储过程
p_swap(a,b);
DBMS_OUTPUT.put_line('交换后:a=' || a || ',b=' || b);
end;
实例:procedure,创建一个存储过程,求年龄?
create or replace procedure p_get_age(p_d1 date,p_d2 date,p_age out number) as
begin
p_age := trunc(months_between(p_d1,p_d2)/12);
end;
declare
age number;
begin
p_get_age(sysdate,date'1994-10-15',age);
DBMS_OUTPUT.put_line('生日为:' || age);
end;
实例:procedure,根据ID查询出学生的信息并直接在过程里打印出来
create or replace procedure p_get_student(p_id in number,row out student%rowtype) as
begin
select * into row from student where id = p_id;
end;
declare
row student%rowtype;
begin
p_get_student(2,row);
DBMS_OUTPUT.put_line('ID:' || row.id || ',姓名:' || row.name);
end;
四、function、procedure使用区别
1、procedure用于处理复杂的业务逻辑,function用于处理复杂的sql语句
2、procedure可以有out参数类型,function数没有;
3、procedure在声明中可以不包含return子句,且执行代码块中没有返回值;
4、procedure必须声明return并指明返回是什么类型,且执行代码块中有返回值(return ...)。