Oracle--子程序之function、procedure实现及示例说明

一、说明

子程序

把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 ...）。