Oracle 11g 学习笔记-6（触发器、函数、存储过程、程序包）

程序包

1-pack_body_emp 、exec_package

SQL> CREATE OR REPLACE PACKAGE pack_emp IS
  2    FUNCTION fun_avg_sal(num_deptno NUMBER) RETURN NUMBER;
  3
  4    PROCEDURE pro_regulate_sal(var_job VARCHAR2,num_proportion NUMBER);
  5
  6  END pack_emp;
  7  /

程序包已创建。

SQL> CREATE OR REPLACE PACKAGE BODY pack_emp IS
  2         FUNCTION fun_avg_sal(num_deptno NUMBER) RETURN NUMBER IS
  3           num_avg_sal NUMBER;
  4           BEGIN
  5             SELECT AVG(sal) INTO num_avg_sal
  6             FROM emp
  7             WHERE deptno=num_deptno;
  8             RETURN (num_avg_sal);
  9           EXCEPTION
 10             WHEN no_data_found THEN
 11               dbms_output.put_line('该部门不存在雇员记录');
 12               RETURN 0;
 13           END fun_avg_sal;
 14
 15           PROCEDURE pro_regulate_sal(var_job VARCHAR2,num_proportion NUMBER) IS
 16           BEGIN
 17             UPDATE emp SET sal=sal*(1+num_proportion)
 18             WHERE job=var_job;
 19           END pro_regulate_sal;
 20  END pack_emp;
 21  /

程序包体已创建。

//执行程序包
SQL>  declare
  2      num_deptno emp.deptno%type;
  3      var_job emp.job%type;
  4      num_avg_sal emp.sal%type;
  5      num_proportion number;
  6    begin
  7      num_deptno:=10;
  8      num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);
  9      dbms_output.put_line(num_deptno||'号部门的平均工资是：'||num_avg_sal);
 10
 11      var_job:='SALESMAN';
 12      num_proportion:=0.1;
 13      pack_emp.pro_regulate_sal(var_job,num_proportion);
 14    end;
 15    /

触发器

2-[触发器] 语句级触发器：

SQL> create table dept_log(operate_tag varchar2(10),operate_time date);

表已创建。

SQL> create or replace trigger tri_dept
  2    before insert or update or delete
  3    on dept
  4  declare
  5    var_tag varchar2(10);
  6  begin
  7    if inserting then
  8      var_tag:='插入';
  9    elsif updating then
 10      var_tag:='修改';
 11    elsif deleting then
 12      var_tag:='删除';
 13    end if;
 14    insert into dept_log values(var_tag,sysdate);
 15  end tri_dept;
 16  /

触发器已创建

SQL> insert into dept values(66,'咨询'，'长春');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from dept_log;

OPERATE_TA OPERATE_TIME
---------- --------------
插入       09-7月 -20

3-[触发器]tri_insert_good 行级触发器，自动创建主键值.

SQL> create table goods
  2  (
  3    id int primary key,
  4    good_name varchar2(50)
  5  );

表已创建。

SQL> create sequence seq_id;

序列已创建。

SQL> create or replace trigger tri_insert_good
  2    before insert
  3    on goods
  4    for each row
  5  begin
  6    select seq_id.nextval
  7    into :new.id
  8    from dual;
  9  end;
 10  /

触发器已创建

SQL> insert into goods(good_name)values('苹果');

已创建 1 行。

SQL> insert into goods(good_name)values('葡萄');

已创建 1 行。

SQL> select * from goods;

        ID GOOD_NAME
---------- --------------------------------------------------
         1 苹果
         2 葡萄

函数

4-[函数]create_func 代码手敲并调用函数 get_agv_pay…最后删除函数

SQL> create or replace function get_avg_pay(num_deptno number) return number is   --创建一个函数，计算某个部门的平均工资
  2    num_avg_pay number; --定义临时变量，保存某部门平均工资
  3  begin
  4    select avg(sal) into num_avg_pay from emp where deptno=num_deptno;--获取某部门平均工资
  5    return(round(num_avg_pay,2));--返回平均工资
  6  exception
  7    when no_data_found then
  8      dbms_output.put_line('该部门编号不存在！');
  9      return 0;
 10  end;
 11  /

函数已创建。

//调用函数
SQL> set serveroutput on
SQL> declare
  2  avg_pay number;
  3  begin
  4  avg_pay:=get_avg_pay(10);--调用get_avg_pay()函数
  5  dbms_output.put_line(avg_pay);
  6  end;
  7  /
2916.67

PL/SQL 过程已成功完成。

//删除函数
SQL> drop function get_avg_pay;

函数已删除。

存储过程

5-[存储过程]—无参存储过程 create_procedure…pro_insertDept

SQL> create or replace procedure pro_insertDept is
  2  begin
  3    insert into dept values(77,'市场拓展部','jilin');
  4    commit;
  5    dbms_output.put_line('插入新纪录成功！');
  6  end pro_insertDept;
  7  /

过程已创建。

6-show error 在sqlplus中可以显示编译错误么？
可以

SQL> create or replace procedure pro_insertDep1 is
  2  begin
  3    insert into dept values(77,'市场拓展部','jilin');
  4    commit;
  5    dbms_output.put_line('插入新纪录成功！');
  6  end pro_insertDept;
  7  /

警告: 创建的过程带有编译错误。

SQL> show error
PROCEDURE PRO_INSERTDEP1 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PLS-00113: END 标识符 'PRO_INSERTDEPT' 必须同 'PRO_INSERTDEP1'
         匹配 (在第 1 行, 第 11 列)

// 调用存储过程
SQL> set serveroutput on
SQL> begin
  2  pro_insertDept;
  3  end;
  4  /
插入新纪录成功！

PL/SQL 过程已成功完成。

7-[存储过程] in_pro…insert_dept代码调用.带入参存储过程.

SQL> create or replace procedure insert_dept(
  2    num_deptno in number,
  3    var_ename in varchar2,
  4    var_loc in varchar2
  5    ) is
  6  begin
  7    insert into dept
  8    values(num_deptno,var_ename,var_loc);
  9    commit;
 10  end insert_dept;
 11  /

过程已创建。

SQL> begin
  2  insert_dept(var_ename=>'采购部',var_loc=>'成都',num_deptno=>15);
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> select *from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        66 咨询           长春
        77 市场拓展部     jilin
        15 采购部         成都
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        88 design         beijing

已选择8行。

8-[存储过程]pro_out…select_dept代码手敲并调用.带入参+返参存储过程.

SQL> create or replace procedure select_dept(
  2    num_deptno in number,
  3    var_dname out dept.dname%type,
  4    var_loc out dept.loc%type
  5    ) is
  6  begin
  7    select dname,loc
  8    into var_dname,var_loc
  9    from dept
 10    where deptno=num_deptno;
 11  exception
 12    when no_data_found then
 13     dbms_output.put_line('该部门编号不存在！');
 14  end select_dept;
 15  /

过程已创建。

SQL> set serveroutput on
SQL>  declare
  2   var_dname dept.dname%type;
  3   var_loc dept.loc%type;
  4   begin
  5     select_dept(50,var_dname,var_loc);
  6     dbms_output.put_line(var_dname||'位于:'||var_loc);
  7   end;
  8  /
IT位于:HEFEI

PL/SQL 过程已成功完成。

9-[存储过程]default_value… insert_dept 带默认值入参存储过程,插入数据。

SQL> create or replace procedure insert_dept(
  2    num_deptno in number,
  3    var_dname  in varchar2 default '综合部',
  4    var_loc in varchar2 default '北京')is
  5  begin
  6    insert into dept values(num_deptno,var_dname,var_loc);
  7  end;
  8   /

过程已创建。

SQL> begin
  2    insert_dept(90);
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        66 咨询           长春
        77 市场拓展部     jilin
        15 采购部         成都
        50 IT             HEFEI
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        88 design         beijing
        90 综合部         北京

已选择10行。