oracle-过程、函数、触发器、包

原创

武大保安 2022-09-19 15:58:14 博主文章分类：数据库 ©著作权

文章标签 触发器存储过程 sql 文章分类 运维

©著作权归作者所有：来自51CTO博客作者武大保安的原创作品，请联系作者获取转载授权，否则将追究法律责任

前面PL/SQL编程基础中的PL/SQL程序块都是匿名的，其中包含的代码无法保存到oracle数据库中。oracel提供了4种可以存储PL/SQL块:过程、函数、触发器、包

1.存储过程

一种命名的PL/SQL程序块，参数可有可无，通常无返回值，存储过程被保存在数据库中（已经编译好的），因此执行效率很高。

1.1创建存储过程

create [or replace] procedure 过程名 [(参数1[,参数2]...)] is|as
begin
   plsql语句;
[exception]
   [异常处理语句;]
end [过程名];
说明:没有declare,需要关键字create [or replace] procedure
输入参数其后要指定in关键字输出参数其后要指定out关键字

eg:创建一个存储过程，改存储过程实现向dept表中插入一条记录
set serveroutput on; create procedure pro_insertDept is begin    insert into dept values(77,'市场拓展部','JILIN');    commit;    dbms_output.put_line('插入记录成功！'); end pro_insertDept; /
如果已经存在名为pro_insertDept的存储过程，会报错
此时可以加上or replace 覆盖掉原来的过程，如下:
set serveroutput on; create or replace procedure pro_insertDept is begin insert into dept values(77,'市场拓展部','JILIN'); commit; dbms_output.put_line('插入记录成功！'); end pro_insertDept; /
注:
1.错误处理
若创建过程存在错误，默认的保错信息很简单，就一行:警告: 创建的过程带有编译错误。
此时可以接着执行一行show errors;输出详细的报错信息。
如上面的 dbms_output.put_line 写错了
2.执行
execute 过程名；
execut 过程名；
exec 过程名；
3.调用执行:PL/SQL程序块中调用某个过程 (不需要exec关键字了)
eg:在PL/SQL程序块中调用存储过程pro_insertDept，然后执行这个PL/SQL块。
set serveroutput on; begin    pro_insertDept; end; /

1.2存储过程的参数

1.in模式参数

输入参数，也是默认参数模式，执行存储过程时:in参数不能被修改,只能根据被传入的指定值（或者默认值）为存储过程提供数据

eg:创建一个存储过程，并定义3个in模式的变量，然后将这3个变量的值插入dept表中。
create or replace procedure insert_dept(    num_deptno in number,   --定义in模式的变量，它存储部门编号    var_ename in varchar2,   --定义in模式的变量，它存储部门名称    var_loc in varchar2) is begin    insert into dept    values(num_deptno,var_ename,var_loc);   --向dept表中插入记录    commit; end insert_dept; /
参数传递
1.指定名称传递
指定名称传递好处：顺序可打乱(和在表中插入数据指定字段名差不多)
格式:过程名(参数1=>值1,[参数2=>值2]...);
调用1:
exec insert_dept(var_ename=>'市场部',var_loc=>'成都',num_deptno=>90);
调用2:
begin    insert_dept(var_ename=>'市场部',var_loc=>'成都',num_deptno=>90); end; /
2.按位置传递(按顺序全插入)
exec insert_dept(80,'采购部','成都');
或者:
begin    insert_dept(80,'采购部','成都'); end; /
3.混合方式传递
eg:在PL/SQL块中调用存储过程insert_dept,然后使用"按位置传递"方式传入第1个参数值，接下来使用"指定名称传递"方式
传入剩下两个参数值，最后执行当前PL/SQL块，代码运行结果如下:
exec insert_dept(38,var_loc=>'济南',var_ename=>'测试部');
注:使用混合模式，只能是前面全部是"按位置传递"，后面"指定名称传递"，也即是在某个位置使用"指定名称传递"方式传递
参数值后，其后面的参数值也要使用"指定名称传递"(因为"指定名称传递"方式有可能已经破坏了参数原始的定义顺序)

2.out模式参数

参数值可以传递到存储过程以外的环境，在执行存储过程时：out参数只能等待被赋值，而不能像in参数那样为存储过程本身提供数据

eg:创建一个存储过程，要求定义两个out模式的字符类型的参数，一个输入参数deptno,然后将在dept表中根据部门号检索到的一行部门信息存储到两个输出参数中。
create or replace procedure select_dept(    num_deptno    in    number,               --输入参数    var_dname    out dept.dname%type,   --输出参数存储部门名称并输出    var_loc    out dept.loc%type) is begin    select dname,loc    into var_dname,var_loc    from dept    where deptno=num_deptno; exception    when no_data_found then               --若select语句无返回记录        dbms_output.put_line('该部门编号的不存在'); end select_dept; /
1.pl/sql块中调用out模式(有out参数)的存储过程
需要在declare部分声明和存储过程中out参数类型兼容的若干变量
eg:首先要在pl/sql块中声明若干变量，然后调用select_dept存储过程，并将定义的变量传入改存储过程，以便接收out参数的返回值。
set serveroutput on; declare    var_dname dept.dname%type;    var_loc dept.loc%type; begin    select_dept(90,var_dname,var_loc);    dbms_output.put_line(var_dname||'位于:'||var_loc); end; /
2.使用exec命令执行out模式的存储过程
使用exec命令需要在sql*plus环境中使用variable关键字声明两个变量，用以存储out参数的返回值。
eg:使用variable关键字声明两个变量，分别用来存储部门名称和位置信息，然后使用exec命令执行
存储过程，并传入声明的两个变量来接收out参数的返回值，代码及执行结果如下。
variable var_dname varchar2(50); variable var_loc varchar2(50); exec select_dept(90,:var_dname,:var_loc);
--想要查看结果还需要主动打印变量值
print var_dname var_loc;
col :VAR_LOC for a20; col :VAR_DNAME for a20; select :var_loc,:var_dname from dual;
3. in out模式参数
既可以做输入参数，也可以做输出参数，即既可以接收外界传递的参数值，也可以做为返回值传递给外界
create or replace procedure pro_square(    num in out number,       --计算它的平方或平方根，这是一个"in out"参数    flag in boolean) is     --计算平方或平方根的标识，这是一个"in"参数    i int:=2;               --存储过程没有declare 直接定义内部变量了 begin    if flag then        num:=power(num,i);    else        num:=sqrt(num);    end if; end; /
执行
eg:调用存储过程pro_square,计算某个数的平方根或平方。
declare    var_number number;    var_temp number;    bool_flag boolean; begin    var_temp :=3;    var_number :=var_temp;--此时角色是输入参数输出语句内的角色是输出参数    bool_flag :=false;--false表示计算平方根 true表示计算平方    pro_square(var_number,bool_flag);--调用存储过程    if bool_flag then        dbms_output.put_line(var_temp||'的平方是:'||var_number);    else        dbms_output.put_line(var_temp||'的平方根是:'||var_number);    end if; end; /
false改成true：

1.3 IN参数的默认值

实际上，oracle支持在声明in参数的同时给其初始化默认值，这样在存储过程调用时，如果没有向in参数传入值，则存储过程可以使用默认值进行操作。

eg:创建一个存储过程，定义3个IN参数，并将其中的两个参数设置初始默认值，然后将这个3个IN参数的值插入dept表中。
create or replace procedure insert_dept( num_deptno in number, --in参数需要传入值 var_dname in varchar2 default '综合部', --in参数可以不传入值,此时使用的就是默认值 var_loc in varchar2 default '北京') is begin insert into dept values(num_deptno,var_dname,var_loc); end; /
调用:
eg:在pl/sql块中调用insert_dept存储过程，并且只向该存储过程传入两个参数值
declare row_dept dept%rowtype; begin commit; insert_dept(91,var_loc=>'太原');--有默认参数时使用"指定名称传递"的方式最安全 var_dname使用默认值'综合部' select * into row_dept from dept where deptno=91; dbms_output.put_line('部门名称:《'||row_dept.dname||'》，地点在《'||row_dept.loc||'》'); end; /

2 函数

函数经常用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数

2.1创建函数和调用

和存储过程极其相似，唯一不同:函数有返回值，存储过程没有

格式:主要关键字为function 声明有"return 返回值类型" 调用有 "return(返回值类型的变量或值)"
create or replace function 函数名[(参数1[,参数2]...) return 数据类型 is --return 数据类型指定返回值类型
     [内部变量] --没有declare语句，直接在此处声明
begin
    plsql语句;
[exception]
    [异常处理语句;]
end [函数名]
eg:创建一个函数，用于计算emp表中某个指定部门的平均工资
create or replace function get_avg_pay(num_deptno number) return number is    num_avg_pay number; begin    select avg(sal) into num_avg_pay from emp where deptno=num_deptno;--某个部门的平均工资    return(round(num_avg_pay,2)); exception    when no_data_found then        dbms_output.put_line('该部门编号的不存在');        return(0);--返回平均工资为0 end; /
调用:和c语言一样，函数执行表达式就是返回值
set serveroutput on; declare    avg_pay number; begin    avg_pay:=get_avg_pay(10);    dbms_output.put_line('10号部门的平均工资是:'||avg_pay); end; /
调用2：
select get_avg_pay(&no) 平均工资 from dual;

2.2删除函数

drop function 函数名；

eg:
drop function get_avg_pay;

3.触发器

触发器可以看做是一种"特殊"的存储过程，它定义了一些与数据库相关事件(如insert,update,create等事件)发生时应执行
的"功能代码块"，通常用于管理复杂的完整性约束，或监控对表的修改，或通知其他程序，甚至可以实现对数据的审计功能。

3.1触发器简介

触发器是由"触发事件"触发而执行的，而不像存储过程需要编写代码主动调用才会执行

触发事件:能引起触发器执行的操作被称为"触发事件"
eg：DML(insert,update,delete) DDL(create,alter,drop) 引起数据库系统事件(如程序启动或者退出、产生异常错误等)；引发用户事件（如登录或退出数据库操作）
触发器可以看做是一种"特殊"的存储过程，它定义了一些与数据库相关事件(如insert,update,create等事件)发生时应执行
的"功能代码块"，通常用于管理复杂的完整性约束，或监控对表的修改，或通知其他程序，甚至可以实现对数据的审计功能。
定义触发器的语法格式:
create or replace trigger tri_name [before | after | instead of] tri_event on table_name | view_name | user_name | db_name [for each row [when tri_condition] [declare] --触发器允许有declare声明 [var_name var_type] begin plsql语句; end tri_name;

trigger：触发器关键字触发时机关键字: before：执行DML等操作之前触发 after:执行DML等操作之后触发 instead of：此触发器是替代触发器 on：触发器要操作的对象类型 tri_name:触发器名字 tri_event:触发事件，如常用的有:insert，update,delete,create,alter,drop等 table_name | view_name | user_name | db_name：数据表，视图、用户模式、数据库 when tri_condition:触发条件子句，触发事件事件执行了，且tri_condition表达式为true触发器才会执行触发事件事件执行了但tri_condition表达式为false，触发器也不会执行 plsql语句:触发器功能实现的主体

触发器事件5中类型：

行级触发器:当DML语句对每一行数据进行操作时都会引起该触发器的运行
语句级触发器:无论DML语句影响多少行数据，其所引起的触发器仅执行一次。
替换触发器:该触发器是定义在视图上的，而不是定义在表上的，它是用来替换所使用实际语句的触发器。
用户事件触发器:是指与DDL操作或用户登录、退出数据库等事件相关的触发器。如用户登录到数据库或使用alter语句修改表结构
系统事件触发器:是指在oracle数据库系统的事件中进行触发的触发器，如oracle实例的启动与关闭。

下面分别介绍这5种类型

3.2语句级触发器

针对一条DML语句而引起的触发器执行。
在语句级触发器中，不使用for each row子句，也即无论数据操作多少行，触发器都只会执行一次
eg:监控对dept表的操作，记录操作类型和日期
1) 准备工作，先建立一个dept_log数据表，并在其中定义两个字段，分别用来存储操作种类信息和操作日期
create table dept_log(    operate_tag varchar2(10),    operate_time varchar2(50) );
2) 创建一个触发器tri_dept,该触发器在insert、update和delete事件下都可以被触发，并且操作的数据对象是dept表。
然后要求在触发器执行时输出对dept表所做的具体操作。
create or replace trigger tri_dept    before insert or update or delete    on dept --创建触发器tri_dept 当dept表发生插入、修改、和删除操作时引起该触发器执行 declare    var_tag varchar2(10); begin    if inserting then        var_tag:='插入';    elsif updating then        var_tag:='修改';    elsif deleting then        var_tag:='删除';    end if;    insert into dept_log values(var_tag,sysdate); --向日志表中插入对dept表的操作信息 end tri_dept; /insert into dept values(94,'编码部','芜湖'); update dept set loc='安庆' where deptno=94; delete from dept where deptno=94; select * from dept_log;
inserting、updating、deleting为条件谓词
对于条件谓词，甚至可以在其中判断特定列是否被更新，例如要判断用户是否是对dept表的dname列进行了修改，可以使用下面的语句：
if updating(dname) then
   ...
end if;
如此，则即使用户修改了dept表中的数据，但没有对dname列的值进行修改，那么该条件谓词的值仍然为false,这样相应的逻辑语句不会执行。

3.3行级别触发器

不言而喻，行级触发器会针对DML操作所影响的每一行数据都执行一次触发器。创建这种触发器时，必须在语法中使用
for each row这个选项。使用行级触发器的一个典型应用就是给数据表生成主键值，下面就来讲解这个典型应用的实现过程。
1)准备工作，先建立一个有主键的数据表。
create table goods(    id int primary key,    good_name varchar2(50) );
2)给goods表的id列生成不能重复的有序值，这里需要创建一个序列(一种数据库对象，后面的内容)
eg:使用create sequence 语句创建一个序列，命名为seq_id,代码及运行结果如下
create sequence seq_id;
上面已经建立了序列seq_id，则可以在plsql程序中调用它的nextval属性来获取一系列有序的数值，这些数值就可以作为goods表的主键值了。
3)创建一个行级触发器，该触发器在数据表goods插入数据时被触发，并且在该触发器的主体中实现设置goods表的id列的值
注：
列标识符: 访问当前正在受到影响的数据行
   原值标识符:":old.列名" 通常用于update和delete 标识当前行指定列的原始值
   新值标识符:":new.列名"   通常用于update和insert 指向新行的指定列，可为其赋值
create or replace trigger tri_insert_good    before insert    on goods        --关联goods数据表，在向其插入新记录之前，引起该触发器的运行    for each row    --创建行级触发器 begin    select seq_id.nextval    into :new.id    from dual;       --从序列中生成一个新的数值，赋值给当前插入行的id列 end; /
触发执行：插入时不必插入主键了，由触发器自己维护
insert into goods values(null,'苹果'); insert into goods values(null,'梨子'); insert into goods values(null,'香蕉'); insert into goods values(10,'西瓜'); select * from goods;

3.4替换触发器

替换触发器(即:instead of触发器)，是专门定义在视图上的触发器。由于视图是多个基表连接组成的逻辑结构，
一般不允许用户直接进行DML(insert，update，delete等)操作(会报错，下面有演示)，当用户为视图编写"替换触发器"后，
用户对视图的DML操作实际上就变成了执行触发器的逻辑代码，此时便可以借助触发器的逻辑对构成视图的各个基表进行操作了，以达到对视图的DML操作。
1)准备工作，创建视图
在system模式下，给scott用户授予"create view"权限，然后在scott模式下创建一个检索雇员信息的视图，该视图的基表
包括dept和emp表。
--切换管理员身份 connect system/password@orcl --管理员身份下给scott用户授权"create view" grant create view to scott; --切换到scott模式 connect scott/tiger@orcl --建立视图雇员编号、姓名、部门号、部门名、工作、雇佣日期 create view view_emp_dept as select empno,ename,dept.deptno,dname,job,hiredate from emp,dept where emp.deptno=dept.deptno; --此时若直接对视图执行DML，会报错 insert into view_emp_dept values(8888,'东方',10,'ACCOUNTING','CASHIER',sysdate); --ORA-01776: 无法通过联接视图修改多个基表
2)编写一个关于view_emp_dept视图在insert事件中的触发器(替换触发器，在触发器的plsql语句块中逐表执行insert操作)
--创建一个关于view_emp_dept视图的替换触发器，在该触发器的主体中实现向emp表和dept表中插入两行相互关联的数据
create or replace trigger tri_insert_view instead of insert on view_emp_dept --创建一个关于view_emp_dept视图的替换触发器(触发器替换insert操作) for each row --是行级视图 declare row_dept dept%rowtype; begin select * into row_dept from dept where deptno=:new.deptno;--检索指定部门编号的记录行 if sql%notfound then--新插入的行的部门号为:new.deptno 若未检索到这个部门号说明是新部门，避免外键约束冲突，插入该部门 insert into dept(deptno,dname) values(:new.deptno,:new.dname); --像dept表中插入数据 end if; insert into emp(empno,ename,deptno,job,hiredate) --接着向emp表中插入数据 values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate); end tri_insert_view; /
3)此时对视图view_emp_dept执行insert操作便不会报错了，而是引起触发器tri_insert_view的执行，从而实现分别向
emp表和dept表插入一条记录行
insert into view_emp_dept values(8888,'东方',10,'ACCOUNTING','CASHIER',sysdate); select * from view_emp_dept;

改进：部门不存在，select有into。。就直接触发器异常，删去into也不行。。。似乎要捕获异常然后在异常逻辑里执行插入
create or replace trigger tri_insert_view instead of insert on view_emp_dept --创建一个关于view_emp_dept视图的替换触发器针对insert操作 for each row --行级别触发器 declare row_dept dept%rowtype; --dept表行类型 begin select * into row_dept from dept where deptno=:new.deptno; --检索指定部门(刚刚插入的那个部门)编号的记录行 insert into emp(empno,ename,deptno,job,hiredate) --触发器的逻辑里执行向各个基本表插入数据的逻辑 values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate); --以达到向逻辑视图插入数据的目的（不可以直接insert视图的，因为视图是虚表） exception when no_data_found then insert into dept(deptno,dname) values(:new.deptno,:new.dname); insert into emp(empno,ename,deptno,job,hiredate) values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate); end tri_insert_view; /

3.5用户事件触发器

用户事件触发器是因进行DDL操作或用户登录、退出等操作而引起运行的一种触发器，引起该类型触发器运行的常见用户事件包括:
create、alter、drop、analyze、comment、grant、revoke、rename、truncate、suspend、logon、logoff
等。
1)首先创建一个日志信息表，用于保存DDL操作的信息
--创建日志信息表，该表保存的日志信息包括:数据对象、数据对象类型、操作行为、操作用户和操作日期等。 create table ddl_oper_log(    db_obj_name varchar2(20),   --数据对象名称    db_obj_type varchar2(20),   --对象类型    oper_action varchar2(20),   --具体ddl行为    oper_user varchar2(20),       --操作用户    oper_date date                --操作日期 );
2)创建一个用户触发器，用于将当前模式下的DDL操作信息保存到上面所创建的ddl_oper_log日志信息表中
--关于scott用户的DDL操作(这里包括create,alter,drop)创建一个触发器，然后将DDL操作的相关信息插入到ddl_oper_log日志表中 create or replace trigger tri_ddl_oper    before create or alter or drop    on scott.schema--在scott模式下，在创建、修改、删除数据对象之前将引起该触发器运行                    --用户触发器的独特之处 "on scott.schema"<==>"on 用户模式" begin    insert into ddl_oper_log values(        ora_dict_obj_name,   --操作数据对象的名称        ora_dict_obj_type,   --对象类型        ora_sysevent,       --系统事件名称        ora_login_user,       --登录用户        sysdate); end; /
注:上面向ddl_oper_log表中插入数据时，使用了若干个事件属性，各自含义如下
   ora_dict_obj_name,   --获取DDL操作所对应的数据库对象
   ora_dict_obj_type,   --获取DDL操作所对应的数据库对象类型
   ora_sysevent,       --获取触发器的系统事件名
   ora_login_user,       --获取登录用户名
3)scott模式下执行DDL操作，观察触发器执行
--在scott模式下，创建一个数据表和一个视图，然后删除视图和修改表，最后查看ddl_oper_log日志表
create table tb_test(id number); create view view_test as select empno,ename from emp; alter table tb_test add(name varchar2(10)); drop view view_test; select * from ddl_oper_log;

4.程序包

程序包由PL/SQL程序元素(如变量、类型)和匿名PL/SQL块(如游标)、命名PL/SQL块(如存储过程和函数)组成。
程序包可以被整体加载到内存中，这样就可以大大加快程序包中任何一个组成部分的访问速度。
dbms_output.put_line 语句就是程序包的一个具体应用，其中，dbms_output是程序包，而put_line就是其中的
一个存储过程。程序包通常由规范和包主体组成。

4.1程序包的规范

"规范"用于规定在程序包中可以使用哪些变量、类型、游标和子程序(各种命名的PL/SQL块)，需要注意的是:
程序包一定要在"包主体"之前被创建
可见规范很像c++.h文件中对成员函数的声明，也像java里的接口的声明
规范格式:
create [or replace] package pack_name is    [declare_variable];    [declare_type];    [declare_cursor];    [declare_function];    [declare_procedure]; end [pack_name];
参数说明如下:
pack_name:程序包名称，如果数据库中已经存在了此名称，则可以指定"or replace"将原来的程序包名称覆盖
declare_variable:   规范内声明的变量。
declare_type:       规范内声明的类型。
declare_cursor:       规范内声明的游标。
declare_function:   规范内声明的函数，但仅仅定义参数和返回值类型，不能有函数体。
declare_procedure:   规范内声明的存储过程，但仅仅定义参数，不包括过程主体。
eg:创建一个程序包的"规范"，首先在该程序包中声明一个可以获取指定部门的平均工资的函数，然后再声明
一个可以实现按照指定比例上调指定职务的工资的存储过程。
create or replace package pack_emp is    function fun_avg_sal(num_deptno number) return number;--获取指定部门的平均工资    procedure pro_regulate_sal(var_job varchar2,num_proportion number);--按照指定比例上调指定职务的工资 end pack_emp; /

4.2程序包的主体

程序包主体包含规范中声明的具体实现，此外也可以在"程序包的主体"中声明一些内部变量。
格式:
create [or replace] package body pack_name is [inner_variable] [cursor_body] [function_title] {begin    fun_plsql; [exception]    [dowith_sentences;] end [function_name]} [procedure_title] {begin    pro_plsql; [exception]    [dowith_sentences;] end [pro_name]} ... end [pack_name];
参数说明如下:
pack_name:程序包名称，要求与对应"规范"的程序包名称相同
inner_variable:程序包主体的内部变量。
cursor_body:游标主体
function_title:从"规范"中引入的函数头部声明。
fun_plsql:PL/SQL语句，这里是函数主要功能的实现部分。从begin到end部分就是函数的body.
dowith_sentences:异常处理语句
fun_name:函数的名称
procedure_title:从"规范"中引入的存储过程头部声明
pro_plsql:PL/SQL语句，这里是存储过程主要功能的实现部分。从begin到end部分就是存储过程的body
pro_name:存储过程的名字
eg:创建程序包pack_emp的主体，在该主体中实现对应"规范"中声明的函数和存储过程。
create or replace package body pack_emp is    function fun_avg_sal(num_deptno number) return number is --引入规范中的函数        num_avg_sal number;   --定义内部变量    begin        select avg(sal)        into num_avg_sal        from emp        where deptno=num_deptno;        return(num_avg_sal);    exception        when no_data_found then            dbms_output.put_line('该部门编号的不存在雇员记录');        return 0;    end fun_avg_sal;    procedure pro_regulate_sal(var_job varchar2,num_proportion number) is --引入规范中的过程    begin        update emp        set sal=sal*(1+num_proportion)        where job=var_job;    end pro_regulate_sal; end pack_emp; /
调用:
eg:创建一个匿名的PL/SQL块，然后通过程序包pack_emp调用其中的函数fun_avg_sal和存储过程pro_regulate_sal,
并输出函数返回结果，代码如下。
set serveroutput on; declare    num_deptno emp.deptno%type;   --定义部门编号变量    var_job emp.job%type;--定义职务变量    num_avg_sal emp.sal%type;--定义工资变量    num_proportion number;--定义工资调整比例变量 begin    num_deptno:=10;   --设置部门编号10    num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);    dbms_output.put_line(num_deptno||'号部门的平均工资是:'||num_avg_sal);    var_job:='SALESMAN';--设置职务名称    num_proportion:=0.1;--设置调整比例    pack_emp.pro_regulate_sal(var_job,num_proportion);--调整指定部门的工资 end; /

总结:创建程序包规范->实现程序包主体->在PL/SQL块或sqlplus中调用程序包中的子程序--即函数或存储过程。

本次实验

--1.过程的使用
--1.1无参数的存储过程
create or replace procedure proc_execution as  --创建存储过程，过程名proc_execution，若过程名已存在，则覆盖
begin
  update emp set ename='yourname' where empno=9010;  --过程逻辑主体  9010号员工改名为'yourname'
end proc_execution;
/

--先插入一条记录 否则9010号员工不存在
insert into emp values(9010,'欣南','战士',7839,sysdate,4000,null,10);
--执行过程
exec proc_execution;


--1.2 带输入输出的存储过程
--1:查询emp表中给定职工号的姓名，工资和佣金
set serveroutput on;
create or replace procedure pro_queryEmpByNo(
  --参数和c语言函数相似，直接在过程名后加个()在()里写
  var_empno in emp.empno%type) as
  type emprow_type is record(
    var_ename emp.ename%type,
    var_sal emp.sal%type,
    var_comm emp.comm%type
  );
  emprow emprow_type;--记录类型定义好后要再实例化一个变量  注意变量名在前  类型在后
begin
  select ename,sal,comm into emprow from emp where empno=var_empno;
  dbms_output.put_line(var_empno||'号员工姓名:'||emprow.var_ename||' 工资:'||emprow.var_sal||' 佣金:'||emprow.var_comm);
exception  
  when no_data_found then
    dbms_output.put_line('不存在'||var_empno||'号员工!');
end pro_queryEmpByNo;
--最后的/不要忘记了
/

--执行过程
exec pro_queryEmpByNo(9010);


--2:创建一个存储过程，以员工编号为参数，返回该员工的姓名和工资(若工资超过10000，则报错，提示工资超过规定值)
set serveroutput on;
create or replace procedure pro_queryEmpByNo(
    var_empno emp.empno%type  --不写in 默认in
  ) as
  var_sal emp.sal%type;
  var_ename emp.ename%type;
begin
  select ename,sal into var_ename,var_sal from emp where empno=var_empno;
  if var_sal>10000 then
    dbms_output.put_line(var_empno||'号员工的工资超过规定值!');
  else
    dbms_output.put_line('工资正常!');
  end if;
exception
  when no_data_found then
    dbms_output.put_line('不存在'||var_empno||'号员工!');
end pro_queryEmpByNo;
/

--执行
exec pro_queryEmpByNo(9010);
--工资正常!
update emp set sal=20000 where empno=9010;
exec pro_queryEmpByNo(9010);
--9010号员工的工资超过规定值!



--3:利用存储过程查询给定部门号的部门里分别有哪些人(要求输出具体部门的人员名单)
set serveroutput on;
create or replace procedure pro_queryDeptByNo(
    var_deptno in dept.deptno%type
  ) as
begin
  dbms_output.put_line(var_deptno||'号部门人员如下:');
  for i in (select ename from emp where deptno=var_deptno) 
  loop
    dbms_output.put_line(i.ename);
  end loop;
end;
/

--执行
exec pro_queryDeptByNo(10);


--2.函数的使用
--题目:用function查询出emp中给定职工号的工资;并用两种方式调用函数
create or replace function getSalByEmpno(var_empno emp.empno%type) return emp.sal%type is
  var_sal emp.sal%type;
begin
  select sal into var_sal from emp where empno=var_empno;
  return var_sal;
exception
  when no_data_found then
    dbms_output.put_line('不存在'||var_empno||'号员工!');
  return 0;
end getSalByEmpno;
/

--调用1:
declare 
    var_empno number;
    var_sal number;
begin
  var_empno:=&no;
  var_sal:=getSalByEmpno(var_empno);
  if var_sal!=0 then
      dbms_output.put_line(var_empno||'号员工的工资是:'||var_sal);
    end if;
end;
/

--调用2:
select getSalByEmpno(&no) 工资 from dual;


--3.触发器的使用
--3.1语句级触发器 实现课本样例-22

--监控对dept表的操作，记录操作类型和日期

--1) 准备工作，先建立一个dept_log数据表，并在其中定义两个字段，分别用来存储操作种类信息和操作日期
create table dept_log(
  operate_tag varchar2(10),
  operate_time varchar2(50)
);

--2) 创建一个触发器tri_dept,该触发器在insert、update和delete事件下都可以被触发，并且操作的数据对象是dept表。
--然后要求在触发器执行时输出对dept表所做的具体操作。
create or replace trigger tri_dept
  before insert or update or delete
  on dept --创建触发器tri_dept 当dept表发生插入、修改、和删除操作时引起该触发器执行
declare
  var_tag varchar2(10);
begin
  if inserting then
    var_tag:='插入';
  elsif updating then
    var_tag:='修改';
  elsif deleting then
    var_tag:='删除';
  end if;
  insert into dept_log values(var_tag,sysdate); --向日志表中插入对dept表的操作信息
end tri_dept;
/

--调用
insert into dept values(94,'编码部','芜湖');
update dept set loc='安庆' where deptno=94;
delete from dept where deptno=94;
select * from dept_log;
-- 插入       2019-05-21 00:36:43
-- 修改       2019-05-21 00:36:48
-- 删除       2019-05-21 00:36:54
-- 插入       2019-05-23
-- 修改       2019-05-23
-- 删除       2019-05-23


--3.2行级别触发器
--1.当emp表中删除一条记录时，把删除的记录写到职工备份表emp_bak中(该表要自己建立)

--获取建表语句

SET SERVEROUTPUT ON 
SET LINESIZE 1000 
SET FEEDBACK OFF 
set long 99999           
set pagesize 4000   
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;


CREATE TABLE "SCOTT"."EMP"(
      "EMPNO" NUMBER(4,0),
      "ENAME" VARCHAR2(10),
      "JOB" VARCHAR2(9),
      "MGR" NUMBER(4,0),
      "HIREDATE" DATE,
      "SAL" NUMBER(7,2),
      "COMM" NUMBER(7,2),
      "DEPTNO" NUMBER(2,0),
       CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
 );
--仿照
create table emp_bak(
  "EMPNO" NUMBER(4,0),
  "ENAME" VARCHAR2(10),
  "JOB" VARCHAR2(9),
  "MGR" NUMBER(4,0),
  "HIREDATE" DATE,
  "SAL" NUMBER(7,2),
  "COMM" NUMBER(7,2),
  "DEPTNO" NUMBER(2,0)
);

--建表方法二，推荐   （数据也复制了 可以以此来备份表）
create table emp_bak as select * from emp;
--只是复制表结构 where后条件为假即可  
create table myemp as select * from emp where 1=2;

--删除前备份
set serveroutput on;
create or replace trigger tri_emp
  before delete
  on emp
  for each row
begin
  insert into emp_bak values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end;
/


--2.当用户向emp表中插入一条数据后，该触发器将统计emp表中新行的数目，并输出   用时间来判断吧 2019年及以后的都是新数据
set serveroutput on;
create or replace trigger tri_insertemp
  after insert on emp for each row
declare
  var_num number;
  pragma autonomous_transaction;--自动commit(自治事务) 否则insert事务尚未提交，触发器与insert两个事务会冲突  
begin
  select count(*) into var_num from emp where hiredate>'2019-01-01'; --2019年后的视为新的
  dbms_output.put_line('新纪录条数'||var_num);
end;
/


--3.3替换触发器
--管理员身份登录
conn sys/password@orcl as sysdba;
--授予scott用户建视图的权限
grant create view to scott;
--切换到scott用户
conn scott/tiger@orcl;

--先建立视图 视图由基本表emp,dept的字段联合组成逻辑表
create view view_emp_dept as
  select empno,ename,dept.deptno,dname,job,hiredate
  from emp,dept where emp.deptno=dept.deptno;

--建立触发器
create or replace trigger tri_insert_view
  instead of insert
  on view_emp_dept --创建一个关于view_emp_dept视图的替换触发器 针对insert操作
  for each row --行级别触发器
declare
  row_dept dept%rowtype; --dept表行类型  
begin
  select * into row_dept from dept
  where deptno=:new.deptno; --检索指定部门(刚刚插入的那个部门)编号的记录行
  if sql%notfound then --未检索到该部门编号记录
    insert into dept(deptno,dname) values(:new.deptno,:new.dname); --向视图insert的部门记录不存在，则向dept表中先插入对应，否则破坏外键约束
  end if;
  insert into emp(empno,ename,deptno,job,hiredate)          --触发器的逻辑里执行向各个基本表插入数据的逻辑 
  values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);  --以达到向逻辑视图插入数据的目的（不可以直接insert视图的，因为视图是虚表）
end tri_insert_view;
/


--测试
insert into view_emp_dept values(8888,'bob',80,'测试部','测试员',sysdate);
insert into view_emp_dept values(8889,'东方',10,'ACCOUNTING','CASHIER',sysdate);
select * from view_emp_dept;

--改进 个人感觉有问题 select * into row_dept from dept where deptno=:new.deptno; 本行用了into 一旦查不到数据(第一个用列)就触发异常 程序直接终止
--个人感觉 去掉into row_dept 后面根本没用到呀。。。（如此declare也多余了）  行不通。。。。
--若row_dept实在要用  则只有捕获no_data_found异常 在异常处理里进行接下来的逻辑了
--简单改法  不行 必须要into
--捕获异常改法
create or replace trigger tri_insert_view
  instead of insert
  on view_emp_dept --创建一个关于view_emp_dept视图的替换触发器 针对insert操作
  for each row --行级别触发器
declare
  row_dept dept%rowtype; --dept表行类型  
begin
  select * into row_dept from dept
  where deptno=:new.deptno; --检索指定部门(刚刚插入的那个部门)编号的记录行
  insert into emp(empno,ename,deptno,job,hiredate)          --触发器的逻辑里执行向各个基本表插入数据的逻辑 
  values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);  --以达到向逻辑视图插入数据的目的（不可以直接insert视图的，因为视图是虚表）
exception
  when no_data_found then
    insert into dept(deptno,dname) values(:new.deptno,:new.dname);
    insert into emp(empno,ename,deptno,job,hiredate) values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);
end tri_insert_view;
/


-- 4.包的使用
--创建包:create package
--规范
create or replace package pk is
  procedure setSalByEname(gname varchar2,gsal number);--声明包中有一个过程   根据员工姓名修改工资
  function getSumsalByEname(gname varchar2) return number;--根据根据员工姓名计算年总收入
end;
/

--包主体
create or replace package body pk is
  procedure setSalByEname(gname varchar2,gsal number) is
  begin
    update emp set sal=gsal where ename=gname;
  end;

  function getSumsalByEname(gname varchar2) return number is  
    annual_salary number;--function不准有declare 此行相当于declare的变量声明
  begin
    select sal*12+nvl(comm,0)*12 into annual_salary from emp where ename=gname;
    return annual_salary;
  end;
end;
/


--调用
exec 包名.过程或函数
--刚才建立的包体调用:
--修改scott的工资
exec pk.setSalByEname('SCOTT',6789);
--获取scott年收入
select pk.getSumsalByEname('SCOTT') from dual;


--思考题
1.传递参数的方式有哪些?
过程中有: in模式参数、out模式参数、in out模式参数
函数有:   传入参数和返回值

2.过程与函数的使用原则
需要经常执行的pl/sql代码块写成过程，可以重复多次执行，
  1) exec 过程名;
  2) plsql块里直接写过程名
    begin
        pro_insertDept;
    end;

函数:经常用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数
  1) select 函数名(参数) from dual;
  2)plsql块里直接写函数名



3.before,after,instead of触发器分别用在什么场合?
before：执行DML等操作之前触发
after:  执行DML等操作之后触发
instead of：此触发器是替代触发器,用于视图（逻辑虚表）

上一篇：java 动作与键盘事件的对应 ActionEvent InputMap KeyStroke

下一篇：java GUI 事件文本框焦点事件 FocusListener

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯