oracle从入门到精通(9)
------
游标,包,存储过程,触发器
select * from inf;
--静态游标---->显式游标
1 声明游标
2 打开游标
3 提取数据
4 关闭游标
declare
cursor my_cur is select * from inf;
r inf%rowtype;
begin
open my_cur;
fetch my_cur into r;
while(my_cur%found)
loop
dbms_output.put_line(r.id||' '||r.name||' '||r.age);
fetch my_cur into r;
end loop;
close my_cur;
end;
-----------------------------------------------------------------------------------------------
动态游标: 在声明的时候不需要关联到表,在打开的时候才指定,与哪一张表建立关联
---动态游标的语法:
declare
type my_cur is ref cursor; --声明一种动态游标类型,名称为 : my_cur
abc my_cur; --声明一个动态游标类型的变量,名称为: abc
r inf%rowtype; --声明一个变量r,它代表inf表中的一行数据
begin
open abc for select * from inf;
fetch abc into r;
while(abc%found)
loop
dbms_output.put_line(r.id||' '||r.name||' '||r.age);
fetch abc into r;
end loop;
close abc;
end;
---------------------------------------------------------------------------------------------
create or replace procedure my_pc(in_age in number)
as
begin
declare
type my_cur is ref cursor; --声明一种动态游标类型,名称为 : my_cur
abc my_cur; --声明一个动态游标类型的变量,名称为: abc
r inf%rowtype; --声明一个变量r,它代表inf表中的一行数据
begin
open abc for select * from inf where age >in_age ;
fetch abc into r;
while(abc%found)
loop
dbms_output.put_line(r.id||' '||r.name||' '||r.age);
fetch abc into r;
end loop;
close abc;
end;
end;
----------------------------------
begin
my_pc(in_age => 29);
end;
---包--package
作用:创建程序包以后,可以把定义好的游标类型,放到程序包中,要使用这种游标的时候,只需要从程序包中取得该游标
类型即可使用
-----------------------------------
创建程序包的语法:
--创建一个程序包,程序包中包含一个动态游标
create or replace package my_pk
as
type my_cur is ref cursor;
end;
----------------------------------------------------------------------
--创建一个存储过程,返回游标
create or replace procedure my_pc(out_cur out my_pk.my_cur)
as
begin
open out_cur for select * from inf;
end;
----------------------------------------------------------------
trigger ----触发器
什么是触发器?
答:存在于数据库中,当满足一定条件时,自动运行的语句块,就是触发器
触发器,用户不能调用,只有当满足一定条件时,系统会自动调用
触发器的分类:
-- 1 行级触发器 (当操作数据表中的某一行时,行级触发器就会自动运行)
-- 2 表级触发器 (当操作某一张表的时候,表级触发器就会自动运行)
-- 3 instead触发器 (当操作视图的时候,instead触发器就会自动运行)
-- 4 模式触发器 (当创建或删除对象的时候,它会自动运行)
***********触发器中的代码,都是自动运行.用户无法调用
触发器的基本语法:
create or replace trigger 触发器名称
在什么情况下 做什么事情;
---------------------------------------------------------
--行级触发器: 当向数据表中添加一行数据的时候,自动触发
create or replace trigger tr_insert_inf
before insert on inf for each row
begin
dbms_output.put_line('用户向数据表中添加了一行数据');
end;
----------------------------------------------------------------
代码分解:
create or replace trigger tr_insert_inf --创建一个触发器名称为: tr_insert_inf
before insert on inf --当向inf表中添加一行数据之前,先要运行触发器
for each row --表示当前触发器,是一个行级触器
begin --触发器要完成的操作
dbms_output.put_line('用户向数据表中添加了一行数据');
end;
------------
create table workInfo
(
id number primary key, --员工编号
name varchar2(20), --员工名称
work_years number --员工在本公司的工作年限
);
--新员工在公司的工作年限都应该是0
create or replace trigger tr_insert_workInfo
before insert on workInfo for each row
begin
:new.work_years:=0; --给work_years字段要添加的新值为0
end;
--------------------------------------------------
insert into workInfo values (1,'tom',0);
insert into workInfo values (2,'bruce',999);
select * from workInfo;
update workInfo set work_years =3 where id=2;
------------------------------
:new.work_years:=0; --要赋值给work_years字段的新值是0
:old.work_years ---取得work_years字段旧的数据
:new 与 :old只能用于行级触发器
-------------------------------------------------
利用触发器,自动删除表中的数据
------------------------------------------------
create or replace trigger tr_del_province
before delete on province for each row
begin
delete from city where pid=:old.pid;
end;
---------------------------------------------------
select * from province;
delete from province where pid=1;
create table province
(
pid number primary key,
pname varchar2(20)
)
insert into province values(1,'湖北省');
insert into province values(2,'广东省');
create table city
(
cid number primary key,
cname varchar2(20),
pid number references province(pid)
)
insert into city values(1,'武汉市',1);
insert into city values(2,'广州市',2);
-----------------------------------------------------------------------
表级触发器:
----判断用户执行了哪些操作,是否有权限对表做这些操作
create or replace trigger tr_insert_update_delete_inf
before insert or delete or update on inf
begin
if(user!='CINDY')
then raise_application_error(-20001,'错误,你无权限对表完成这些操作');
end if;
end;
--错误的编号必须是 : -20001------------ -20999 之间
select * from inf;
insert into inf values (2,'chris',34);
grant all on inf to crm;
select * from cindy.inf;
insert into cindy.inf values (3,'bruce',34);
select user from dual;--user 为当前用户
--------------------------------------------------------
利用表级触器做日志记录
create table inf_log
(
id number primary key,--日志编号
name varchar2(20),--哪个用户在操作
op_time date,---操作时间
op_desc varchar2(20) --做了哪些操作
)
create sequence inf_log_seq;--创建序列做为主键
---
create or replace trigger tr_insert_update_delete_inf
before insert or delete or update on inf
begin
if inserting
then insert into inf_log values(inf_log_seq.nextval,user,sysdate,'向inf表中添加了数据');
end if;
if updating
then insert into inf_log values(inf_log_seq.nextval,user,sysdate,'修改inf表的数据');
end if;
if deleting
then insert into inf_log values(inf_log_seq.nextval,user,sysdate,'删除inf表的数据');
end if;
end;
insert into inf values(10,'lee',22);
update cindy.inf set name='yyy' where id=9;
select * from inf_log;
grant all on inf to crm;