oracle 函数，过程，程序包

--子程序和程序包

--author:shine
--一。子程序：指的是已经命名的pl/sql块。包括函数和存储过程。
--1.过程
--1）初识
--exp:1.1.1
create or replace procedure
  pro_find_emp(emp_no emp.empno%type) --传入或传出的参数，和外界联系
as
  empname emp.ename%type;  --local局部参数，只在内部使用
begin
  select ename into empname from emp where empno = emp_no;
  dbms_output.put_line(empname);
  exception
    when no_data_found then
      dbms_output.put_line('没找到');
end;

--测试
begin
  pro_find_emp(7369);
end;

--2）过程的参数模式(有in,out,in out)
--exp:1.1.2:in参数
create or replace procedure
  pro_find_emp(e_name in emp.ename%type) --带in的参数（不写时默认为in）
as
  emp_row emp%rowtype;
  type emp_cur_type is ref cursor;
  emp_cur emp_cur_type;
begin
  open emp_cur for 'select * from emp where ename like :1'
  using e_name;
    loop
      fetch emp_cur into emp_row;
      exit when emp_cur%notfound;
      dbms_output.put_line('编号：'||emp_row.empno||'姓名：'||emp_row.ename);
    end loop;
  close emp_cur;
end;

--测试
begin
  pro_find_emp('S%');
end;

--exp:1.1.3:out参数
create or replace procedure
  pro_emp_sal(emp_no in emp.empno%type,emp_test out varchar2) --传入或传出的参数类型不能带长度如：varchar2(10)
as
  emp_sal number(5); --局部参数类型必须带长度
begin
  select sal into emp_sal from emp where empno = emp_no;
  case
    when emp_sal >= 2000 then emp_test := '高工资';
    when emp_sal >=1000 and emp_sal < 2000 then emp_test := '还可以';
    else emp_test :='没戏';
  end case;
end

--测试
declare
  emp_test varchar2(1000);
begin
  pro_emp_sal(7566,emp_test);
  dbms_output.put_line(emp_test);
end;

--exp:1.1.4:in out 参数
create or replace procedure
  pro_emp_transform(num1 in out int,num2 in out int)
as
  temp int;
begin
  temp := num1;
  num1 := num2;
  num2 := temp;
end;

--测试
declare
  num1 int := &num1;
  num2 int := &num2;
begin
  dbms_output.put_line('测试前:num1='||num1||' num2='||num2);
  pro_emp_transform(num1,num2);
  dbms_output.put_line('测试后:num1='||num1||' num2='||num2);
end;

--3）使用过程的原始权限是system和创建过程的用户，要让别人使用就得分权限
grant execute on pro_find_emp to shine;--把执行pro_find_emp的权限分给shine用户

--4）删除过程
drop procedure pro_find_emp;

--2.函数：必须要有返回值，且只能有一个。
--a.函数只能带in参数，不能带in out ，out参数
--b.函数形式参数和返回值类型只能是数据库类型，不能是pl/sql类型如：boolean..
--1)初识：
--exp:1.2.1
create or replace function fun_hello
return varchar2 as
begin
  return 'helloword';
end;

--测试
select fun_hello from dual;

--2）带参数的函数
--exp:1.2.2
create or replace function
  total(num1 in number,num2 in number)
return number as  --从样子上看就比procedure多了个return;
  t_otal number(4) := 10;
begin
  t_otal := num1 + num2;
  if t_otal > 10 then
    dbms_output.put_line(num1||'加'||num2||'的和大于10');
  end if;
  return t_otal;
end;

--测试
select total(&num1,&num2) from dual; --函数不能通过exeucte单独执行，只能通过sql块，和表达式运用。

--3）函数授权
grant execute on total to shine;

--4)删除函数
drop function total;

--稍微总结哈：
--1)过程是重量级的，主要用于执行复杂的更新操作，作为pl/sql块单独执行。
--2)函数是轻量级的，主要用于执行查询操作或计算，转换..，作为表达式的一部分到处用。

--3.自主事务处理
--exp 1.3.1:
create or replace procedure p2
as
  a varchar2(50);
begin
  select ename into a
  from emp where empno = 7369;
  dbms_output.put_line(a);
  rollback;
end;
 
create or replace procedure p1
as
  b varchar2(50);
begin
  update emp set ename='shine'
  where empno = 7369;
  p2();
  select ename into b
  from emp where empno=7369;
  dbms_output.put_line(b);
end;

--测试
begin
  p1();
end;

--结果为:shine smith
--这说明p2中的rollback把p1中的update 回滚了
--上例中，p2的操作影响到p1，为了使每一个过程都能自主处理.有了自主事务处理,pragma autonomous_transaction;
--exp 1.3.2:
create or replace procedure p2
as
  a varchar2(50);
  pragma autonomous_transaction;  --加了自主事务处理
begin
  select ename into a
  from emp where empno = 7369;
  dbms_output.put_line(a);
  rollback;
end;
 
create or replace procedure p1
as
  b varchar2(50);
begin
  update emp set ename='shine'
  where empno = 7369;
  p2();
  select ename into b
  from emp where empno=7369;
  dbms_output.put_line(b);
end;
--结果：smith shine
--说明p2中的rollback 没有回滚p1中的update;

--二、程序包：顾名思义把很多子程序（函数，过程）包在一起、(感觉就像java类一样)
--程序包包括两部分：包规范，包主题
--1、包规范：（就像java中的接口一样，只定义方法不实现，并且在其中定义的方法或属性都是public static）
--exp:2.1.1
create or replace package pack_test as
  procedure pro_emp_findname (emp_no emp.empno%type);
  function fun_emp_findsal(emp_no int) return number;
  username constant varchar(10):='shine';
end pack_test;

--2. 包主体：（要实现接口啦）
create or replace package body pack_test as
  procedure pro_emp_findname (emp_no emp.empno%type) as
  e_name emp.ename%type;
  begin
    select ename into e_name from emp where empno = emp_no;
    dbms_output.put_line('编号：'||emp_no||'名字：'||e_name);
  end;
   
  function fun_emp_findsal(emp_no int) return number as
  e_sal number(4);
  begin
    select sal into e_sal from emp where empno = emp_no;
    return e_sal;
  end;
 
end pack_test;

--测试
select pack_test.fun_emp_findsal(7369) from dual;

begin
  pack_test.pro_emp_findname(7369);
  dbms_output.put_line(pack_test.username);
end;

--3.程序包中的游标(必须要有return)
create or replace package pack_test2 as
  cursor emp_cur(e_name emp.ename%type) return emp%rowtype;
  procedure show_emp(e_name emp.ename%type);
end pack_test2;

create or replace package body pack_test2 as
    cursor emp_cur(e_name emp.ename%type) return emp%rowtype is
    select * from emp where ename like '%'||e_name||'%';
   
    procedure show_emp(e_name emp.ename%type) as
    emp_row emp%rowtype;
    begin
      open emp_cur(e_name);
        loop
          fetch emp_cur into emp_row;
          exit when emp_cur%notfound;
          dbms_output.put_line('编号：'||emp_row.empno||'名字：'||emp_row.ename);
        end loop;
      close emp_cur;
    end;
end pack_test2;

--测试
begin
pack_test2.show_emp('S');
end;

--4.查看包代码
select line,text from user_source
where name='PACK_TEST2';
--5.查看包规范
desc pack_test2;