create or replace package pak_kingsql1 is
procedure pro_kingsql(p_one in varchar2,p_two out varchar2,p_three in out varchar2);
function f_grade(v_sal in number) return number;
end;
/
create or replace package body pak_kingsql1 is
procedure pro_kingsql(p_one in varchar2,p_two out varchar2,p_three in out varchar2)
is
begin
dbms_output.put_line('参数:'||'p_one:'||p_one||',p_two:'||',p_three:'||p_three);
end pro_kingsql;
function f_grade(v_sal in number)
return number
is
v_sal2 emp.sal%type:=0;
yisi varchar2(100);
begin
case when v_sal<2000 and v_sal>0 then yisi:='xys';
when v_sal>2000 and v_sal<5000 then yisi:='zdys';
when v_sal>5000 then yisi:='ksyysl';
else yisi:='zjmysl';
end case;
return yisi;
end f_grade;
end pak_kingsql1;
/
BEGIN
DBMS_OUTPUT.enable ; -- 开启缓冲区
DBMS_OUTPUT.put('www.') ; -- 向缓冲增加内容
DBMS_OUTPUT.put('vdata.com.cn') ; -- 向缓冲增加内容
DBMS_OUTPUT.new_line ; -- 换行,输出之前缓冲区内容
DBMS_OUTPUT.put('www.vdedu.com.cn') ; -- 向缓冲增加内容
DBMS_OUTPUT.new_line ; -- 换行,输出之前缓冲区内容
DBMS_OUTPUT.put('bbs.vdedu.com.cn') ; -- 向缓冲增加内容,之后没有换行,此内容不输出
--DBMS_OUTPUT.put_line('猜猜上一行打印不打印?') ;
END ;
/
declare
v_line1 varchar2(200);--保存第1行数据
v_line2 varchar2(200);--保存第2行数据
v_line3 varchar2(200);--保存第3行数据
v_status number;--保存状态
begin
dbms_output.enable;--开启缓冲区
dbms_output.put('www.vdata.com.cn');--向缓冲增加内容
dbms_output.new_line;--换行
dbms_output.put('www.oracle.com.cn');--向缓冲增加内容
dbms_output.new_line;--换行
dbms_output.put('bbs.vdata.cn');--向缓冲增加内容
dbms_output.new_line;--换行
dbms_output.get_line(v_line1,v_status);--读取缓冲区一行数据
dbms_output.get_line(v_line2,v_status);--读取缓冲区一行数据
dbms_output.get_line(v_line3,v_status);--读取缓冲区一行数据
dbms_output.put_line('取得数据:'||v_line1);
dbms_output.put_line('取得数据:'||v_line2);
dbms_output.put_line('取得数据:'||v_line3);
end;
/
declare
begin
update scott.emp set emp.sal=emp.sal*1.3 where emp.job='hehe';
if sql%found then
dbms_output.put_line('0 updated');
end if;
end;
/
declare
begin
update scott.emp set emp.set=emp.sal*1.3 where emp.job='salesman';
if sql%found then
dbms_output.put_line(sql%rowcount|| ' rows has been updated');
elsif sql%notfound then
dbms_output.put_line(sql%rowcount|| ' rows hsa been updated');
end if;
end;
/
declare
cursor cur_emp is select*from emp;
v_emprow emp%rowtype;
begin
if cur_emp%isopen then --游标已经打开
null;
else --游标未打开
open cur_emp; --打开游标
end if;
fetch cur_emp into v_emprow; --取出游标当前数据
while cur_emp%found loop --判断是否有数据
dbms_output.put_line(cur_emp%rowcount||'$ 雇员姓名:'||v_emprow.ename||',职位:'||
v_emprow.job||',工资:'||v_emprow.sal);
fetch cur_emp into v_emprow; --把游标指向下一行
end loop;
close cur_emp; --关闭游标
end;
/
declare
cursor cur_emp is select*from emp;
v_emprow emp%rowtype;
begin
if cur_emp%isopen then --游标打开
null;
else --游标未打开
open cur_emp; --游标打开
end if;
loop
fetch cur_emp into v_emprow; --取出游标当前数据
exit when cur_emp%notfound; --如果没有找到数据则退出循环
dbms_output.put_line(cur_emp%rowcount||'$雇员姓名:'||v_emprow.ename||',职位:'||
v_emprow.job||',工资:'||v_emprow.sal);
end loop;
close cur_emp; --关闭游标
end;
/
declare
cursor cur_emp is select*from emp;
begin
for v_emprow in cur_emp loop
dbms_output.put_line(cur_emp%rowcount||'$雇员姓名:'||v_emprow.ename||'.职位:'||v_emprow.job||',工资:'||v_emprow.sal);
end loop;
end;
/
触发器
create table emp_x as select*from emp;
create or replace trigger forbid_emp_x_trigger
before insert or delete
on emp_x
DECLARE
v_currentdate varchar(20);
begin
select to_char(sysdate,'dd')into v_currentdate from dual;
if trim(v_currentdate)!='10'then
raise_application_error(-20008,'在每个月的10号才允许办理入职手续!');
end if;
end;
/
知识点的梳理:
Oracle中的触发器分为DML触发器,instead-of(替代)触发器,DDL触发器,系统触发器和数据库事件触发器;
DML触发器中分为以下两类:
表级触发器:所有更新操作只在之前或之后触发一次;
行级触发器:针对更新的每一行分别进行之前或之后触发;
行级触发器中可以使用":old"取得更新前的数据,使用":new"取得更新后的数据;
复合触发器是在Oracle11G之后增加的新功能,可以进行4个触发事件操作;
如果要对视图进行更新操作,则应该使用替代触发器来完成,在替代触发器中,可以对视图中包含的多个数据表进行更新操作;
当需要对发生的DDL操作进行触发时,可以采用DDL触发器。DDL触发器可以针对一个用户或整个数据库,如果针对数据库级应该具备管理员权限;
每一个触发器只能编写最多32KB的代码,当程序复杂时,可通过过程或函数进行功能切割;
触发器简介
触发器类似于过程和函数;
例:当对某一张表执行更新操作(insert,update,delete)时,都可能引发触发器执行;
触发器依靠事件执行;
采用隐式调用,不能接收参数
基本语法与分类
Oracle中的触发器分类:DML,instead-of(替代),DDL,系统或数据库事件触发器
所有触发器都支持的基本创建语法:
CREATE [OR REPLACE] TRIGGER 触发器名称
[BEFORE | AFTER] --触发时间
[INSTEAD OF]
[INSERT | UPDATE |UPDATE OF 列名称 [,列名称,...]|DELETE] --触发事件
ON [表名称 | 视图 |DATABASE | SCHEMA] --触发对象
[REFERENCING [OLD AS 标记] [NEW AS 标记] [PARENT AS 标记]]
[FOR EACH ROW] --触发频率
[FOLLOWS 触发器名称]
[DISABLE]
[WHEN 触发条件] --触发条件
[DECLARE] --触发操作(程序主体)
[程序声明部分;]
[PRAGMA AUTONOMOUS_TRANSACTION;]
BEGIN
程序代码部分;
END [触发器名称];
/
语法作用:
CREATE [OR REPLACE] TRIGGER 触发器名称:创建一个触发器,设置名称,如果选择了OP REPLACE选项,则表示替换已有的触发器;
[BEFORE | AFTER] :该触发器的触发时间,是在操作之前(BEFORE)还是操作之后(AFTER)触发;
[INSTEAD OF]:替代触发器,对于视图操作所定义的触发器类型;
[INSERT | UPDATE |UPDATE OF 列名称 [,列名称,...]|DELETE]:触发的事件,可以是数据表的增加,修改,删除,或者部分字段的更新;
ON [表名称 | 视图 |DATABASE | SCHEMA] :触发器的触发对象,可以是数据表,视图,数据库,模式(用户);
[REFERENCING [OLD AS 标记] [NEW AS 标记] [PARENT AS 标记]]:对于":old",":new",":parent"这3个标识符定义别名;
[FOR EACH ROW]:定义行级触发,没有此语句就是表级触发器;
[FOLLOWS 触发器名称]:配置多个触发器执行的先后顺序;
[DISABLE]:触发器建立之后默认是启用状态,通过此选项,可将其定义为禁用状态;
[WHEN 触发条件]:当满足指定条件时才执行触发器操作;
[DECLARE]:触发器主体程序声明部分,定义变量或游标;
[PRAGMA AUTONOMOUS_TRANSACTION;]:自治事务声明,编写此语句后会在触发器中启动一个子事务处理,并且可以使用COMMIT提交事务;
BEGIN:程序主体部分;
END:触发器结束标记;
/:完结标记
触发器注意事项
触发器不接受任何参数,且只能是在产生了某一个触发事件之后才会自动调用;
对于一张数据表的触发器,最多只有12个,同一种类型的触发器,只能定义一次;
一个触发器最大为32KB,所以如果需要编写的代码较多,可以通过过程或函数调用来完成;
默认情况下,触发器中不能使用事务处理操作,或采用自治事务进行处理;
在一张数据表中,定义过多的触发器,会造成DML性能下降;
触发器相关权限
CREATE ANY TRIGGER
为任意用户创建触发器的权限
ALTER ANY TRIGGER
修改任意触发器的权限
DROP ANY TRIGGER
删除任意触发器的权限
create or replace trigger forbid_emp_x_trigger2
before insert or delete or update
on emp_x
declare
v_currentweak varchar(20);
v_currenthour varchar(20);
begin
select to_char(sysdate,'day'),to_char(sysdate,'hh24') into v_currentweak,v_currenthour
from dual;
if trim(v_currentweak)='monday' or trim(v_currentweak)='saturday' or trim(v_currentweak)='sunday'
then
raise_application_error(-20008,'A');
elsif trim(v_currenthour)<9 or trim(v_currenthour)>18 then
raise_application_error(-20009,'B');
end if;
end;
/
create or replace trigger forbid_emp_trigger3
before update of sal,comm
on emp
declare
currenthour varchar(20);
begin
select to_char(sysdate,'hh24')into currenthour from dual;
if trim(currenthour)>'12'then
raise_application_error(-20009,'meitian12dianyihoubuyunxugengxinguyuangongzi、yongjin');
end if;
end;
create or replace trigger forbid_emp_x_trigger4
before insert
on emp_x
for each row
declare
v_jobcount number;
begin
select count(empno)into v_jobcount from emp where:new.job in(
select distinct job from emp);
if v_jobcount = 0 then --没有此职位信息
raise_application_error(-20008,'zengjiaguyuandezhiweixinximingchengcuowu!');
else
if:new.sal>5000 then
raise_application_error(-20008,'zengjiaguyuandegongzibudechaoguo5000!');
end if;
end if;
end;
/
create or replace trigger emp_update_trigger5
before update of sal
on emp_x
for each row
begin
if abs((:new.sal-:old.sal)/:old.sal)>0.1 then
raise_application_error(-20008,'guyuanxiugaifudutaida!');
end if;
end;
/
create or replace trigger dept_update_trigger10
before insert or update or delete
on dept
for each row
begin
if inserting then
insert into dept log(logid,type,logdate,deptno,dname,loc)
values
(dept_log_seq.nextval,'insert',sysdate,:new.deptno,:new.dname,:new.loc);
elsif updating then
insert into dept_log(logid,type,logdate,deptno,dname,loc)
values
(dept_log_se.nextval,'update'.sysdate,:new.deptno,:
create or replace package pak_kingsql1 is
procedure pro_kingsql(p_one in varchar2,p_two out varchar2,p_three in out varchar2);
function f_grade(v_sal in number) return number;
end;
/
create or replace package body pak_kingsql1 is
procedure pro_kingsql(p_one in varchar2,p_two out varchar2,p_three in out varchar2)
is
begin
dbms_output.put_line('参数:'||'p_one:'||p_one||',p_two:'||',p_three:'||p_three);
end pro_kingsql;
function f_grade(v_sal in number)
return number
is
v_sal2 emp.sal%type:=0;
yisi varchar2(100);
begin
case when v_sal<2000 and v_sal>0 then yisi:='xys';
when v_sal>2000 and v_sal<5000 then yisi:='zdys';
when v_sal>5000 then yisi:='ksyysl';
else yisi:='zjmysl';
end case;
return yisi;
end f_grade;
end pak_kingsql1;
/
BEGIN
DBMS_OUTPUT.enable ;-- 开启缓冲区
DBMS_OUTPUT.put('www.') ;-- 向缓冲增加内容
DBMS_OUTPUT.put('vdata.com.cn') ;-- 向缓冲增加内容
DBMS_OUTPUT.new_line ;-- 换行,输出之前缓冲区内容
DBMS_OUTPUT.put('www.vdedu.com.cn') ;-- 向缓冲增加内容
DBMS_OUTPUT.new_line ;-- 换行,输出之前缓冲区内容
DBMS_OUTPUT.put('bbs.vdedu.com.cn') ;-- 向缓冲增加内容,之后没有换行,此内容不输出
--DBMS_OUTPUT.put_line('猜猜上一行打印不打印?');
END ;
/
declare
v_line1 varchar2(200);--保存第1行数据
v_line2 varchar2(200);--保存第2行数据
v_line3 varchar2(200);--保存第3行数据
v_status number;--保存状态
begin
dbms_output.enable;--开启缓冲区
dbms_output.put('www.vdata.com.cn');--向缓冲增加内容
dbms_output.new_line;--换行
dbms_output.put('www.oracle.com.cn');--向缓冲增加内容
dbms_output.new_line;--换行
dbms_output.put('bbs.vdata.cn');--向缓冲增加内容
dbms_output.new_line;--换行
dbms_output.get_line(v_line1,v_status);--读取缓冲区一行数据
dbms_output.get_line(v_line2,v_status);--读取缓冲区一行数据
dbms_output.get_line(v_line3,v_status);--读取缓冲区一行数据
dbms_output.put_line('取得数据:'||v_line1);
dbms_output.put_line('取得数据:'||v_line2);
dbms_output.put_line('取得数据:'||v_line3);
end;
/
declare
begin
update scott.emp set emp.sal=emp.sal*1.3 where emp.job='hehe';
if sql%found then
dbms_output.put_line('0 updated');
end if;
end;
/
declare
begin
update scott.emp set emp.set=emp.sal*1.3 where emp.job='salesman';
if sql%found then
dbms_output.put_line(sql%rowcount|| ' rows has been updated');
elsif sql%notfound then
dbms_output.put_line(sql%rowcount|| ' rows hsa been updated');
end if;
end;
/
declare
cursor cur_emp is select*from emp;
v_emprow emp%rowtype;
begin
if cur_emp%isopen then --游标已经打开
null;
else --游标未打开
open cur_emp; --打开游标
end if;
fetch cur_emp into v_emprow; --取出游标当前数据
while cur_emp%found loop --判断是否有数据
dbms_output.put_line(cur_emp%rowcount||'$ 雇员姓名:'||v_emprow.ename||',职位:'||
v_emprow.job||',工资:'||v_emprow.sal);
fetch cur_emp into v_emprow; --把游标指向下一行
end loop;
close cur_emp; --关闭游标
end;
/
declare
cursor cur_emp is select*from emp;
v_emprow emp%rowtype;
begin
if cur_emp%isopen then --游标打开
null;
else --游标未打开
open cur_emp; --游标打开
end if;
loop
fetch cur_emp into v_emprow; --取出游标当前数据
exit when cur_emp%notfound; --如果没有找到数据则退出循环
dbms_output.put_line(cur_emp%rowcount||'$雇员姓名:'||v_emprow.ename||',职位:'||
v_emprow.job||',工资:'||v_emprow.sal);
end loop;
close cur_emp; --关闭游标
end;
/
declare
cursor cur_emp is select*from emp;
begin
for v_emprow in cur_emp loop
dbms_output.put_line(cur_emp%rowcount||'$雇员姓名:'||v_emprow.ename||'.职位:'||v_emprow.job||',工资:'||v_emprow.sal);
end loop;
end;
/
触发器
create table emp_x as select*from emp;
create or replace trigger forbid_emp_x_trigger
before insert or delete
on emp_x
DECLARE
v_currentdate varchar(20);
begin
select to_char(sysdate,'dd')into v_currentdate from dual;
if trim(v_currentdate)!='10'then
raise_application_error(-20008,'在每个月的10号才允许办理入职手续!');
end if;
end;
/
知识点的梳理:
Oracle中的触发器分为DML触发器,instead-of(替代)触发器,DDL触发器,系统触发器和数据库事件触发器;
DML触发器中分为以下两类:
表级触发器:所有更新操作只在之前或之后触发一次;
行级触发器:针对更新的每一行分别进行之前或之后触发;
行级触发器中可以使用":old"取得更新前的数据,使用":new"取得更新后的数据;
复合触发器是在Oracle11G之后增加的新功能,可以进行4个触发事件操作;
如果要对视图进行更新操作,则应该使用替代触发器来完成,在替代触发器中,可以对视图中包含的多个数据表进行更新操作;
当需要对发生的DDL操作进行触发时,可以采用DDL触发器。DDL触发器可以针对一个用户或整个数据库,如果针对数据库级应该具备管理员权限;
每一个触发器只能编写最多32KB的代码,当程序复杂时,可通过过程或函数进行功能切割;
触发器简介
触发器类似于过程和函数;
例:当对某一张表执行更新操作(insert,update,delete)时,都可能引发触发器执行;
触发器依靠事件执行;
采用隐式调用,不能接收参数
基本语法与分类
Oracle中的触发器分类:DML,instead-of(替代),DDL,系统或数据库事件触发器
所有触发器都支持的基本创建语法:
CREATE [OR REPLACE] TRIGGER 触发器名称
[BEFORE | AFTER] --触发时间
[INSTEAD OF]
[INSERT | UPDATE |UPDATE OF 列名称 [,列名称,...]|DELETE] --触发事件
ON [表名称 | 视图 |DATABASE | SCHEMA] --触发对象
[REFERENCING [OLD AS 标记] [NEW AS 标记] [PARENT AS 标记]]
[FOR EACH ROW] --触发频率
[FOLLOWS 触发器名称]
[DISABLE]
[WHEN 触发条件] --触发条件
[DECLARE] --触发操作(程序主体)
[程序声明部分;]
[PRAGMA AUTONOMOUS_TRANSACTION;]
BEGIN
程序代码部分;
END [触发器名称];
/
语法作用:
CREATE [OR REPLACE] TRIGGER 触发器名称:创建一个触发器,设置名称,如果选择了OP REPLACE选项,则表示替换已有的触发器;
[BEFORE | AFTER] :该触发器的触发时间,是在操作之前(BEFORE)还是操作之后(AFTER)触发;
[INSTEAD OF]:替代触发器,对于视图操作所定义的触发器类型;
[INSERT | UPDATE |UPDATE OF 列名称 [,列名称,...]|DELETE]:触发的事件,可以是数据表的增加,修改,删除,或者部分字段的更新;
ON [表名称 | 视图 |DATABASE | SCHEMA] :触发器的触发对象,可以是数据表,视图,数据库,模式(用户);
[REFERENCING [OLD AS 标记] [NEW AS 标记] [PARENT AS 标记]]:对于":old",":new",":parent"这3个标识符定义别名;
[FOR EACH ROW]:定义行级触发,没有此语句就是表级触发器;
[FOLLOWS 触发器名称]:配置多个触发器执行的先后顺序;
[DISABLE]:触发器建立之后默认是启用状态,通过此选项,可将其定义为禁用状态;
[WHEN 触发条件]:当满足指定条件时才执行触发器操作;
[DECLARE]:触发器主体程序声明部分,定义变量或游标;
[PRAGMA AUTONOMOUS_TRANSACTION;]:自治事务声明,编写此语句后会在触发器中启动一个子事务处理,并且可以使用COMMIT提交事务;
BEGIN:程序主体部分;
END:触发器结束标记;
/:完结标记
触发器注意事项
触发器不接受任何参数,且只能是在产生了某一个触发事件之后才会自动调用;
对于一张数据表的触发器,最多只有12个,同一种类型的触发器,只能定义一次;
一个触发器最大为32KB,所以如果需要编写的代码较多,可以通过过程或函数调用来完成;
默认情况下,触发器中不能使用事务处理操作,或采用自治事务进行处理;
在一张数据表中,定义过多的触发器,会造成DML性能下降;
触发器相关权限
CREATE ANY TRIGGER
为任意用户创建触发器的权限
ALTER ANY TRIGGER
修改任意触发器的权限
DROP ANY TRIGGER
删除任意触发器的权限
create or replace trigger forbid_emp_x_trigger2
before insert or delete or update
on emp_x
declare
v_currentweak varchar(20);
v_currenthour varchar(20);
begin
select to_char(sysdate,'day'),to_char(sysdate,'hh24') into v_currentweak,v_currenthour
from dual;
if trim(v_currentweak)='monday' or trim(v_currentweak)='saturday' or trim(v_currentweak)='sunday'
then
raise_application_error(-20008,'A');
elsif trim(v_currenthour)<9 or trim(v_currenthour)>18 then
raise_application_error(-20009,'B');
end if;
end;
/
create or replace trigger forbid_emp_trigger3
before update of sal,comm
on emp
declare
currenthour varchar(20);
begin
select to_char(sysdate,'hh24')into currenthour from dual;
if trim(currenthour)>'12'then
raise_application_error(-20009,'meitian12dianyihoubuyunxugengxinguyuangongzi、yongjin');
end if;
end;
create or replace trigger forbid_emp_x_trigger4
before insert
on emp_x
for each row
declare
v_jobcount number;
begin
select count(empno)into v_jobcount from emp where:new.job in(
select distinct job from emp);
if v_jobcount = 0 then --没有此职位信息
raise_application_error(-20008,'zengjiaguyuandezhiweixinximingchengcuowu!');
else
if:new.sal>5000 then
raise_application_error(-20008,'zengjiaguyuandegongzibudechaoguo5000!');
end if;
end if;
end;
/
create or replace trigger emp_update_trigger5
before update of sal
on emp_x
for each row
begin
if abs((:new.sal-:old.sal)/:old.sal)>0.1 then
raise_application_error(-20008,'guyuanxiugaifudutaida!');
end if;
end;
/
create or replace trigger dept_update_trigger10
before insert or update or delete
on dept
for each row
begin
if inserting then
insert into dept log(logid,type,logdate,deptno,dname,loc)
values
(dept_log_seq.nextval,'insert',sysdate,:new.deptno,:new.dname,:new.loc);
elsif updating then
insert into dept_log(logid,type,logdate,deptno,dname,loc)
values
(dept_log_se.nextval,'update'.sysdate,:new.deptno,:
create or replace package pak_kingsql1 isprocedure pro_kingsql(p_one in varchar2,p_two out varchar2,p_three in out varchar2);function f_grade(v_sal in number) return number;end;/
create or replace package body pak_kingsql1 isprocedure pro_kingsql(p_one in varchar2,p_two out varchar2,p_three in out varchar2)isbegindbms_output.put_line('参数:'||'p_one:'||p_one||',p_two:'||',p_three:'||p_three);end pro_kingsql;function f_grade(v_sal in number)return numberisv_sal2 emp.sal%type:=0;yisi varchar2(100);begincase when v_sal<2000 and v_sal>0 then yisi:='xys';when v_sal>2000 and v_sal<5000 then yisi:='zdys';when v_sal>5000 then yisi:='ksyysl';else yisi:='zjmysl'; end case;return yisi;end f_grade;end pak_kingsql1;/
BEGINDBMS_OUTPUT.enable ;-- 开启缓冲区DBMS_OUTPUT.put('www.') ;-- 向缓冲增加内容DBMS_OUTPUT.put('vdata.com.cn') ;-- 向缓冲增加内容DBMS_OUTPUT.new_line ;-- 换行,输出之前缓冲区内容DBMS_OUTPUT.put('www.vdedu.com.cn') ;-- 向缓冲增加内容DBMS_OUTPUT.new_line ;-- 换行,输出之前缓冲区内容DBMS_OUTPUT.put('bbs.vdedu.com.cn') ;-- 向缓冲增加内容,之后没有换行,此内容不输出--DBMS_OUTPUT.put_line('猜猜上一行打印不打印?');END ;/
declarev_line1 varchar2(200);--保存第1行数据v_line2 varchar2(200);--保存第2行数据v_line3 varchar2(200);--保存第3行数据v_status number;--保存状态begindbms_output.enable;--开启缓冲区dbms_output.put('www.vdata.com.cn');--向缓冲增加内容dbms_output.new_line;--换行dbms_output.put('www.oracle.com.cn');--向缓冲增加内容dbms_output.new_line;--换行dbms_output.put('bbs.vdata.cn');--向缓冲增加内容dbms_output.new_line;--换行dbms_output.get_line(v_line1,v_status);--读取缓冲区一行数据dbms_output.get_line(v_line2,v_status);--读取缓冲区一行数据dbms_output.get_line(v_line3,v_status);--读取缓冲区一行数据dbms_output.put_line('取得数据:'||v_line1);dbms_output.put_line('取得数据:'||v_line2);dbms_output.put_line('取得数据:'||v_line3);end;/
declarebeginupdate scott.emp set emp.sal=emp.sal*1.3 where emp.job='hehe';if sql%found thendbms_output.put_line('0 updated');end if;end;/
declare beginupdate scott.emp set emp.set=emp.sal*1.3 where emp.job='salesman';if sql%found thendbms_output.put_line(sql%rowcount|| ' rows has been updated');elsif sql%notfound thendbms_output.put_line(sql%rowcount|| ' rows hsa been updated');end if;end;/
declarecursor cur_emp is select*from emp;v_emprow emp%rowtype;beginif cur_emp%isopen then --游标已经打开null;else --游标未打开open cur_emp; --打开游标end if;fetch cur_emp into v_emprow; --取出游标当前数据while cur_emp%found loop --判断是否有数据dbms_output.put_line(cur_emp%rowcount||'$ 雇员姓名:'||v_emprow.ename||',职位:'||v_emprow.job||',工资:'||v_emprow.sal);fetch cur_emp into v_emprow; --把游标指向下一行end loop;close cur_emp; --关闭游标end;/
declarecursor cur_emp is select*from emp;v_emprow emp%rowtype;beginif cur_emp%isopen then --游标打开null;else --游标未打开open cur_emp; --游标打开end if;loopfetch cur_emp into v_emprow; --取出游标当前数据exit when cur_emp%notfound; --如果没有找到数据则退出循环dbms_output.put_line(cur_emp%rowcount||'$雇员姓名:'||v_emprow.ename||',职位:'||v_emprow.job||',工资:'||v_emprow.sal);end loop;close cur_emp; --关闭游标end;/
declarecursor cur_emp is select*from emp;beginfor v_emprow in cur_emp loopdbms_output.put_line(cur_emp%rowcount||'$雇员姓名:'||v_emprow.ename||'.职位:'||v_emprow.job||',工资:'||v_emprow.sal);end loop;end;/ 触发器create table emp_x as select*from emp;create or replace trigger forbid_emp_x_triggerbefore insert or deleteon emp_xDECLAREv_currentdate varchar(20);beginselect to_char(sysdate,'dd')into v_currentdate from dual;if trim(v_currentdate)!='10'thenraise_application_error(-20008,'在每个月的10号才允许办理入职手续!');end if;end;/知识点的梳理:
Oracle中的触发器分为DML触发器,instead-of(替代)触发器,DDL触发器,系统触发器和数据库事件触发器;DML触发器中分为以下两类:表级触发器:所有更新操作只在之前或之后触发一次;行级触发器:针对更新的每一行分别进行之前或之后触发;行级触发器中可以使用":old"取得更新前的数据,使用":new"取得更新后的数据;复合触发器是在Oracle11G之后增加的新功能,可以进行4个触发事件操作;如果要对视图进行更新操作,则应该使用替代触发器来完成,在替代触发器中,可以对视图中包含的多个数据表进行更新操作;当需要对发生的DDL操作进行触发时,可以采用DDL触发器。DDL触发器可以针对一个用户或整个数据库,如果针对数据库级应该具备管理员权限;每一个触发器只能编写最多32KB的代码,当程序复杂时,可通过过程或函数进行功能切割;
触发器简介触发器类似于过程和函数;例:当对某一张表执行更新操作(insert,update,delete)时,都可能引发触发器执行;触发器依靠事件执行;采用隐式调用,不能接收参数基本语法与分类Oracle中的触发器分类:DML,instead-of(替代),DDL,系统或数据库事件触发器所有触发器都支持的基本创建语法:CREATE [OR REPLACE] TRIGGER 触发器名称[BEFORE | AFTER] --触发时间[INSTEAD OF] [INSERT | UPDATE |UPDATE OF 列名称 [,列名称,...]|DELETE] --触发事件ON [表名称 | 视图 |DATABASE | SCHEMA] --触发对象[REFERENCING [OLD AS 标记] [NEW AS 标记] [PARENT AS 标记]][FOR EACH ROW] --触发频率[FOLLOWS 触发器名称][DISABLE][WHEN 触发条件] --触发条件[DECLARE] --触发操作(程序主体)[程序声明部分;][PRAGMA AUTONOMOUS_TRANSACTION;]BEGIN程序代码部分;END [触发器名称];/语法作用:CREATE [OR REPLACE] TRIGGER 触发器名称:创建一个触发器,设置名称,如果选择了OP REPLACE选项,则表示替换已有的触发器;[BEFORE | AFTER] :该触发器的触发时间,是在操作之前(BEFORE)还是操作之后(AFTER)触发;[INSTEAD OF]:替代触发器,对于视图操作所定义的触发器类型;[INSERT | UPDATE |UPDATE OF 列名称 [,列名称,...]|DELETE]:触发的事件,可以是数据表的增加,修改,删除,或者部分字段的更新;ON [表名称 | 视图 |DATABASE | SCHEMA] :触发器的触发对象,可以是数据表,视图,数据库,模式(用户);[REFERENCING [OLD AS 标记] [NEW AS 标记] [PARENT AS 标记]]:对于":old",":new",":parent"这3个标识符定义别名;[FOR EACH ROW]:定义行级触发,没有此语句就是表级触发器;[FOLLOWS 触发器名称]:配置多个触发器执行的先后顺序;[DISABLE]:触发器建立之后默认是启用状态,通过此选项,可将其定义为禁用状态;[WHEN 触发条件]:当满足指定条件时才执行触发器操作;[DECLARE]:触发器主体程序声明部分,定义变量或游标;[PRAGMA AUTONOMOUS_TRANSACTION;]:自治事务声明,编写此语句后会在触发器中启动一个子事务处理,并且可以使用COMMIT提交事务;BEGIN:程序主体部分;END:触发器结束标记;/:完结标记触发器注意事项触发器不接受任何参数,且只能是在产生了某一个触发事件之后才会自动调用;对于一张数据表的触发器,最多只有12个,同一种类型的触发器,只能定义一次;一个触发器最大为32KB,所以如果需要编写的代码较多,可以通过过程或函数调用来完成;默认情况下,触发器中不能使用事务处理操作,或采用自治事务进行处理;在一张数据表中,定义过多的触发器,会造成DML性能下降;触发器相关权限CREATE ANY TRIGGER为任意用户创建触发器的权限ALTER ANY TRIGGER修改任意触发器的权限DROP ANY TRIGGER删除任意触发器的权限
create or replace trigger forbid_emp_x_trigger2before insert or delete or updateon emp_xdeclare v_currentweak varchar(20);v_currenthour varchar(20); begin select to_char(sysdate,'day'),to_char(sysdate,'hh24') into v_currentweak,v_currenthour from dual; if trim(v_currentweak)='monday' or trim(v_currentweak)='saturday' or trim(v_currentweak)='sunday' then raise_application_error(-20008,'A'); elsif trim(v_currenthour)<9 or trim(v_currenthour)>18 then raise_application_error(-20009,'B'); end if; end; /create or replace trigger forbid_emp_trigger3before update of sal,common empdeclarecurrenthour varchar(20);beginselect to_char(sysdate,'hh24')into currenthour from dual;if trim(currenthour)>'12'thenraise_application_error(-20009,'meitian12dianyihoubuyunxugengxinguyuangongzi、yongjin');end if;end;
create or replace trigger forbid_emp_x_trigger4before inserton emp_xfor each rowdeclarev_jobcount number;beginselect count(empno)into v_jobcount from emp where:new.job in(select distinct job from emp);if v_jobcount = 0 then --没有此职位信息raise_application_error(-20008,'zengjiaguyuandezhiweixinximingchengcuowu!');elseif:new.sal>5000 thenraise_application_error(-20008,'zengjiaguyuandegongzibudechaoguo5000!');end if;end if;end;/
create or replace trigger emp_update_trigger5before update of salon emp_xfor each rowbeginif abs((:new.sal-:old.sal)/:old.sal)>0.1 thenraise_application_error(-20008,'guyuanxiugaifudutaida!');end if;end;/
create or replace trigger dept_update_trigger10before insert or update or deleteon deptfor each rowbeginif inserting theninsert into dept log(logid,type,logdate,deptno,dname,loc)values(dept_log_seq.nextval,'insert',sysdate,:new.deptno,:new.dname,:new.loc);elsif updating theninsert into dept_log(logid,type,logdate,deptno,dname,loc)values(dept_log_se.nextval,'update'.sysdate,:new.deptno,: