函数与存储过程、程序包

一、自定义函数

• 创建自定义函数

create or replace function 函数名 return 返回值的类型 as
begin
函数定义
end 函数;

• 在数据字典中查看自定义函数的信息
  与之相关的视图主要有两个：user_objects与user_source
• 调用函数

函数名（）

• 函数的参数

create or replace function 函数名（数据类型1 参数1，数据类型2 参数2...） return 返回值的类型 as
begin
函数定义
end 函数;

• 返回值的类型后面+deterministic ——设置函数的确定性；保证传入参数相同时，不执行函数，从缓存中获得正确结果

2、典型函数 (row2column )——行转列

二、存储过程

• 格式

create or replace procedure 存储过程名称 as
begin
存储过程定义
end 存储过程名称

其中存储过程定义是一组PL/SQL代码。

• 创建存储过程

create or replace procedure updateStatus  as
begin
 update students set status='ACT';
 commit
 end updateStatus;
 /

• 查看存储过程信息
  数据字典：user_object、user_source
• 执行存储过程

--PL/SQL段中
begin
  存储过程名称
  end；
  /
 -- 命令行中
SQL> execute 存储过程名称;

• 存储过程参数
  1、in参数：只进不出，内部不能赋值，即内部不能修改
  2、out参数：只出不进
  3、in out 参数：可进可出

SQL> create or replace procedure insertStudent(p_name in varchar2, p_age in number)
  2  as                                                                            
  3  begin                                                                         
  4  declare v_max_id number;                                                       
  5  begin           ---begin后面:p_age :=10会出错                                                              
  6  if (p_name is null or length(p_name)=0) then                                  
  7  return;                                                                       
  8  end if;                                                                       
  9  if (p_age<15 or p_age>25) then                                                
 10  return;                                                                       
 11  end if;                                                                       
 12                                                                                
 13  select max(student_id) into v_max_id from students;                           
 14  insert into students(student_id, student_name, student_age, status)           
 15  values(v_max_id+1, p_name, p_age, 'ACT');                                     
 16  end;                                                                          
 17                                                                                
 18  end insertStudent;                                                            

三、程序包

程序包：由规范和主体构成
规范：类似于面向对象编程中的接口
主体：必须实现函数和存储过程的集合

• 创建规范

create or replace package 包名 as
变量名称1 数据类型1；
变量名称2 数据类型2；
变量名称3 数据类型3；
...
function 函数名称1（参数列表） return 数据类型1；
....
procedure 存储过程1（参数列表）；
.....
end 包名;

• 查看规范信息
  数据字典：user_objects

 select object_name,object_type,status from user_objects;

• 创建主体

create or replace package body 包名 as
实现部分
end 包名

• 调用程序包的函数/存储过程

select 包名.函数名 from dual;