oracle数据库对象--存储函数和存储过程

存储过程(PROCEDURE)，存储函数(FUNCTION)

通过之前两篇文章对pl/sql程序的简单介绍，现在就可以稳稳的学习重点了，存储过程和存储函数，这个才是我们项目开发中真正要用到的，前面所学的都是为了这个做铺垫的。

1.什么是存储过程和存储函数?

oracle允许将pl/sql程序块存储在数据库中，并在任何地方可以调用他，这样的程序块我们就成为存储过程或存储函数，过程和函数是pl/sql子程序，他们是被命名的程序块，均存储在数据库中，他们可以通过输入输出参数与调用者产生交互，过程和函数的唯一区别就是过程不向调用者返回数据而函数总是需要向调用者返回数据。
因为过程和函数是存储数据库中的，所以他们也是oracle数据库对象。

2.如何定义和维护一个存储函数？

(1)定义存储函数

//创建一个存储函数
CREATE [OR REPLACE] FUNCTION function_name[(param [in | out]type ,param1[in| out]type)] 
 //可能是有参数的
RETURN TYPE;//返回结果类型
IS | AS
<返回变量>
begin
方法执行体；
end；

其中OR REPLACE 代表是否替换之前的， 在参数名和类型之间我们有时候会添加 in，或者是out，或者是in out 关键字，
通过这些关键字修饰，我们来保证实参传进来是否会因为形参的改变而发生变化，如果使用out 和in out 则代表会发生变化
如果使用 in关键字则代表不会发生变化，默认是in，后面将通过实例来说明这个问题。

eg:创建一个返回当前时间的存储函数

create or replace function getSysdate   //无参函数
return Date;
is
v_date date;
begin
select sysdate into v_date from dual;
return v_date;
end;

//执行该函数
declare
v_date date;
begin
v_date := getSysdate()；
dbms_output.put_line(v_date);
end;

eg：获取某部门工资总和 和部门总人数

create or replace function get_salary(dep_id employees.department_id%type,emp_count out number)
//emp_count采用的 out修饰 即为输出函数，后文注释介绍为什么要这样。
return number;
is
v_sumsal number;
begin
select sum(salary),count(*) into v_sumsal ,emp_count from employees where department_id = dep_id;
 return v_sumsal;
end;

//执行实例二
declare
v_sum employees.salary%type;
v_count number(5);
begin
v_sum := get_salary(30,v_count);
dbms_output.put_line(v_sum||','|v_count|);
end;

总结：
（1）实例二是带有参数的存储函数，参数是分为输出（OUT修饰）参数和输入（IN 修饰）参数，pl/sql，我们把函数内自己
定义参数成为形式参数，而我们调用时传入的参数成为实际参数，oracle中，调用函数时，对于输出参数（out修饰），实际
参数会把自己的值复制一份给形式参数，然后形式参数发生操作，当函数顺利退出时又会将形式参数的值赋值给实际参数，
而对于输入（in修饰）参数就不行了，所以本题中，我们调用时传入了v_count实参,当函数结束后，发现实参的值改变了，
也是我们所需要的值。

所以一般当我们返回数据要求是多个数据类型时，我们都是通过定义输出参数，然后通过形式参数赋值给实际参数的方式，来
得到我们需要的实际参数，最终获得我们锁需要的结果。

（2）一般我们在传递参数的时候可以设置默认值，但是规定是只能给输入参数设置默认值，输出参数（out）或输入输出参数
不可以设置默认值
（3）一般参数不写修饰就是默认 in， 然后out 和 in out 表达可以理解成一个意思。
（4）我们在调用函数的时候，传入参数的时候，可以有多种方式
    第一种就是例题中那样，参数位置一一对应
    v_sum := get_salary(30,v_count);
    第二种就是使用名称表示法
    v_sum := get_salary(emp_count => v_count,dep_id =>80); //这样就与参数位置无关
    第三种就是混合表示法
    v_sum := get_salary(30,emp_count =>v_count);

（2）删除存储函数
格式：
DROP FUNCTION [user.] function_name;

---

3.如何定义和维护一个存储过程

之前已经介绍了，其实过程和函数本身区别并不大，过程是没有返回数据的。
在oracle server上建立存储过程，可以被多个应用程序调用，可以向存储过程中传递参数，也可以向存储过程中传回参数。

（1）定义存储过程

//创建一个存储函数
CREATE [OR REPLACE] PROCEDURE procedure_name [(param [in | out]type ,param1[in| out]type)]
//可能是有参数的
IS | AS
<类型 变量的说明>
begin
方法执行体；
exctption
捕获异常；
end；

//删除指定员工的信息
create procedure or replace del_pro (emp_id employees.employees_id%type)
is
no_data exception;
begin
delete from employees where employee_id =emp_id;
if sql%notfound then 
raise no_data;
end if;
dbms_output.put_line('编号为：'||emp_id||'的用户已经删除');
exception
when no_data then dbms_output.put_line('暂无此数据');
when others then dbms_output.put_line(sqlcode||'-----'||sqlerrm);
end;

//插入员工数据
create procedure insert_pro (eid employees.employees_id%type ,ename employees.last_name%type,
dep_id employees.department_id%type  )
is
dep_remain exception;
pragma exception_init(dep_remain,-1)
begin
insert into employees(employee_id,last_name,department_id) values(eid,ename,dep_id) ;
dbms_output.put_line('数据插入成功');
exception
when dep_remain then dbms_output.put_line('违反数据完整性约束，请检查dep_id参数');
when others then dbms_output.put_line(sqlcode||'----'||sqlerrm);
end;

以上两种都是我们传入参数，那么我们如何通过存储过程获得参数，首先过程是没有返回值的，所以肯定不同能通过返回值了，那么还剩的一个方法就是，通过形式参数给实参赋值，我们得到实参不就想到于得到想要的数据了，也就是将传入参数改为用输出参数或输入输出函数（out 或in out 修饰的）；示例如下：

//eg：获取某部门工资总和 和部门总人数

create or replace procedure get_salary(dep_id employees.department_id%type,
emp_count out number(4), v_sumsal out number(9))
//emp_count采用的 out修饰 即为输出参数。
is

begin
select sum(salary),count(*) into v_sumsal ,emp_count from employees where department_id = dep_id;
exception
when no_data_found then dbms_output.put_line('你输入的部门不存在');
when others then dbms_output.put_line(sqlcode||'---'||sqlerrm);
end;

（2）存储过程的调用
存储过程的调用可以使用execute 语句调用，但是对于有变量参与的，则不宜用这种方法，还是用和函数一样在程序块中调用。
格式如下：
EXECUTE procedure_name (parameter1,parameter2…);
如：调用删除指定员工信息的存储过程del_pro
EXECUTE del_pro(23);

对于我们需要有“返回”结果的存储过程，我们还是使用程序块调用：
如：调用获取某部门工资总和 和部门总人数get_salary存储过程

declare
v_ecount number(5);
v_esumsal number(10);
v_dep_id employees.department_id%type := &dep_id; 
begin
get_salary(v_dep_id,emp_count =>v_ecount,v_sumsal => v_esumsal );
dbms_output.put_line('部门编号为：||dep_id||'总工资为：'||v_esumsal||'总人数为：'||v_ecount);
end;

(3)删除存储过程
DROP PROCEDURE [user.]procedure_name;