oracle子程序和程序包以及触发器

1. 子程序
   子程序的优点： 模块化， 可重用，可维护性，安全性
1.1 过程（存储过程）
-- 声明过程
       create or replace procedure pro_say_hello
is
          name varchar2(20); 
begin
          name := '&name';
          dbms_output.put_line('Hello ' || name);
end;

-- 在其他PL/SQL块中调用过程
begin
        pro_say_hello;
end;
-- 在SQL Plus中调用过程
      SQL> execute pro_say_hello;

/*
 过程的参数分为两种：入参，出参，出入参
 入参作为传入参数
 出参作为传出参数
 * 默认为入参
 */
-- 声明带参数的过程
       create or replace procedure pro_call_back_for_eat(name in varchar2, res_str out varchar2)
is
begin
          dbms_output.put_line('Hello ' || name);
          res_str := '你妈妈喊你回家吃饭。';
end;

-- 调用过程
       declare
         res_str varchar2(20);
begin
         pro_call_back_for_eat('贾君鹏', res_str);
         dbms_output.put_line('他说:' || res_str);
end;

-- 参数顺序 in out in out
       create or replace procedure pro_ding_ji(sal number, name in out varchar2)
is
begin
case 
when sal <= 800 then 
              name := name || '(屌丝)';
when sal <= 2000 then 
              name := name || '(贫民)';
when sal <= 5000 then 
              name := name || '(小资)';
when sal <= 10000 then 
              name := name || '(土豪)';
else
              name := name || '(大款)';
end case;
end;

-- 调用
       declare
          name varchar2(20) := '张三';
          sal number := 1500;
begin
-- 调用定级过程
          pro_ding_ji(sal,name);

          dbms_output.put_line('欢迎' || name);
end;

1.2 函数
       执行完后需要返回值的PL/SQL块，只能接受IN参数
       create or replace function fn_say_hello return varchar2
is
begin
return '吃了没？';
end;

-- 在PL/SQL块中执行函数
       declare
             ssss varchar2(20);
begin
             ssss := fn_say_hello();
             dbms_output.put_line(ssss);
end;

       直接在sql上使用
--select fn_say_hello() from dual;
       create or replace function fn_sum(s number,c number) return number
is
begin
return s + nvl(c,0);
end;

select ename,fn_sum(sal,comm) shouru from emp;

       ** 函数和过程的区别
       过程                                   函数
       只能作为PL/SQL语句执行                 可以在PL/SQL中调用，也可以在select语句上使用
       只能通过出参的形式来返回值               只能通过return的形式来返回值，只能接受入参，不能有出参
return只能作为结束当前代码块执行         必须有return来返回值
       可以接受任意类型的参数                  只能接受SQL类型的参数
       无需指明返回值类型                      必须指定返回值类型

2. 程序包
2.1 程序包分为包头和包体
2.2 包头
       create or replace package pkg_emp
is
-- 声明游标类型
type my_cursor_type is ref cursor return emp%rowtype;

-- 声明子程序规范
-- 添加员工信息
procedure pro_add_emp(empno number,ename varchar2,job varchar2,sal number,deptno number);
-- 声明子程序规范
-- 删除员工信息
function pro_delete_emp(in_empno number) return number ;

-- 声明一个查询所有的过程,出参为游标
procedure pro_list(emp_list out my_cursor_type);
end pkg_emp;
2.2 包体
       create or replace package body pkg_emp
is
-- 声明子程序规范
procedure pro_add_emp(empno number,ename varchar2,job varchar2,sal number,deptno number)
is
begin
                   insert into emp(empno,ename,job,sal,deptno) values(empno,ename,job,sal,deptno);
                   commit;
end pro_add_emp;
-- 声明子程序规范
function pro_delete_emp(in_empno number) return number 
is
                  rn number;
begin
                  delete from emp where empno = in_empno;

                  rn := SQL%ROWCOUNT;
                  commit;

return rn;
end pro_delete_emp; 

-- 返回游标
procedure pro_list(emp_list out my_cursor_type)
is
begin
open emp_list for select * from emp;
end pro_list;
end pkg_emp;

       使用程序包：
begin
           pkg_emp.pro_add_emp(1122, '李老板1', '包工头',10000, 20);
end;

begin
         dbms_output.put_line(pkg_emp.pro_delete_emp(1122));
end;

-- 调用带游标出参的过程
      declare
        emp_list pkg_emp.my_cursor_type;
        emp_row emp%rowtype;
begin
        pkg_emp.pro_list(emp_list);
        fetch emp_list into emp_row;
        dbms_output.put_line(emp_row.ename);
end;


3. 自主事务
    在子程序中加入 pragma autonomous_transaction;可以实现自主事务
    本子程序将会重新开启事务而不影响外部程序的事务.

    create or replace procedure pro_1
is
begin
           insert into dept(deptno, dname, loc) values(70,'国防部', '打仗的');
           insert into dept(deptno, dname, loc) values(80,'外交部', '电视');
           pro_2;
           rollback;
end;

    create or replace procedure pro_2
is
    pragma autonomous_transaction;
begin
           commit;
end;

begin
           pro_1;
end;
select * from dept;

1. 触发器
   create or replace trigger tg_autoincrement_user
    before -- 前置 after后置
    insert -- 只有添加之前才会触发[ or update [of sal] or delete]
on t_user
for each row -- 表示为行级,不写则为语句级
begin
      dbms_output.put_line('添加了一条数据...');
-- :new 表示当前操作数据的最新数据
-- :old 表示当前操作数据的原数据

-- 在数据添加到数据库之前,给当条数据添加id值
select seq_user_id.nextval into :new.id from dual;

end;

select * from t_user;

   insert into t_user(name) values('heihei');