Oracle
1、游标
2、利用游标，读取所有数据
3、利用存储过程，读取所有数据
4、触发器
----------------------------------------------------
什么是存储过程?
答:存储过程是提前编写好,并且存储在数据库中的语句块,只要调用,即可马上运行
什么存储过程的好处?
答: 1、信息存储在数据库中,所以安全性会更好
2、由于语句块本就存在数据库中可以直接运行,所以,速度会更快
3、复用性会更好
--------------------------------------------------------
create or replace procedure my_pc(in_out_id in out number,out_name out varchar2,out_score out number)
as
begin
select * into in_out_id,out_name,out_score from inf where id=in_out_id;
end;
------------------------------------
declare
myid number(11):=1;
myname varchar2(20);
myscore number(11);
begin
my_pc(myid,myname,myscore);
dbms_output.put_line('编号:'||myid);
dbms_output.put_line('姓名:'||myname);
dbms_output.put_line('成绩:'||myscore);
end;
--------------------------------------------------------------------------------------------------------------------
cursor---游标
游标: 它是指向数据表中,某一条数据的一个指针,游标指向哪一行,这一行数据才可以读取
游标的分类: 静态游标 与 动态游标
静态游标:--在创建游标时,就必须指定该游标关联的是哪一张表. (并且关联以后,就不能再改变)
动态游标:--在创建的时候,不必指定关联的是哪一张表,用的时候再指定(并且可以改变)
游标
静态游标
隐式游标: --不需要用户创建,系统内置一些静态游标
*****隐式游标主要有三种:
sql%found ---语句执行成功,返回true,执行失败返回false
sql%notfound---语句执行成功,返回false,执行失败返回true
sql%rowcount --返回成功执行的语句的行数
显示游标: --需要用户自己创建的静态游标
动态游标
select * from inf ORDER BY ID;
----------------------------------------------------------
--静态游标--->隐式游标
declare
myid number(11);
begin
myid:=&请输入你要删除的学生编号;
delete from inf where id>myid;
if(sql%found)
then
dbms_output.put_line('删除成功');
dbms_output.put_line('被删除的行数为:'||sql%rowcount);
else
dbms_output.put_line('删除失败');
end if;
end;
-------------------------------------------------------
静态游标--->显示游标
--静态游标: 在声明游标的时候,就必须指定关联的是哪一张表
使用步骤:
1、声明游标
2、打开游标
3、利用游标提取数据
4、关闭游标
--------------------------------------------------------------
declare
cursor my_cr is select * from inf order by id; --声明游标读取inf表的数据
r inf%rowtype;
begin
open my_cr; --打开游标
fetch my_cr into r; --让游标向下移动一位,并且把游标指向的这一行数据,赋值给r
dbms_output.put_line(r.id||' '||r.name||' '||r.score);
close my_cr;--关闭游标
end;
------------------------------------------------------------ ************静态游标-----显示游标
declare
cursor my_cr is select * from inf order by id; --声明游标
r inf%rowtype;
begin
open my_cr; --打开游标
fetch my_cr into r; --游标向下移动一位,并且把这一行数据赋值给变量r
while(my_cr%found) --my_cr%found 如果游标指向的这一行存在数据,返回true,没有数据,返回false
loop
dbms_output.put_line(r.id||' '||r.name||' '||r.score);
fetch my_cr into r;
end loop;
close my_cr; --关闭游标
end;
----------------------------------------------------------------------------------------------------------------
动态游标 : --这种游标,在声明游标的时候,不需要指定关联的是哪一张表,在打开游标的时候,再指定
declare
type mycr is ref cursor; --声明一种类型,这种类型为游标类型
c_r mycr; --声明一个变量,变量为c_r,它是一种游标类型的变量
r inf%rowtype; --声明行变量,用于保存读取的数据
begin
open c_r for select * from inf order by id; --打开游标,并且指定关联的表
fetch c_r into r; --利用游标提取数据
while(c_r%found)
loop
dbms_output.put_line(r.id||' '||r.name||' '||r.score);
fetch c_r into r;--指向下一行
end loop;
close c_r;
end;
---------------------------------------------------------
oracle的 PL/SQL语句块中,声明的变量\游标这些对象,使用完成以后,就会马上销毁,它们自身无法保存
----
如果将这些变量\游标存放一个对象中,它们就可以永久保存下来-------------------该对象称为: 程序包 package
--创建程序包
create or replace package my_pk
as
type my_cr is ref cursor;
end;
----
--创建存储过程,返回游标
create or replace procedure my_pc(in_score in number,out_cr out my_pk.my_cr)
as
begin
open out_cr for select * from inf where score>=in_score order by score desc;
end;
select user from dual;
-----------------------------------------------------------------------------------------------------------------------
*******************************************************
触发器 trigger
什么是触发器?它有什么用?
答:触发器是存储在数据库中代码块,当用户的操作满足某一条件时,系统将自动调用触发器的中代码;
*********注意:其实触发器只能被系统调用,不能被用户调用
--触发器的分类:
1、行级触发器
2、表级触发器
3、instead of 触发器(视图触发器)
4、模式触发器
------------------------------------------------------------------------------------------------------
行级触发器: 当用户对表中的某一行数据进行操作时,触发了设置的触发条件时,触发器中的代码就会自动运行
--行级触发器语法:
create or replace trigger 触发器的名称
before 动作名称 on 表 for each row
begin
触发器要执行的代码;
end;
----------------------------------------------
示例: --对表添加的数据时候,触发
create or replace trigger tr_inf_insert
before insert on inf for each row
begin
dbms_output.put_line('用户向数据表中添加到了一行数据');
end;
--如果对表进行了修改,删除,添加,都触发
create or replace trigger tr_inf_insert
before insert or update or delete on inf for each row
begin
dbms_output.put_line('用户操作了表中的数据');
end;
--------------------------------------------------------------------
利用行级触发器,限制新员工在本公司的工作年限都为0
create table empInfo
(
eid number(11) primary key,
ename varchar2(20) not null,
work_years number(11) default 0 --所有新入职的员工在本公司的工作年限都应该为0
);
--------------------------------------
-- :new.work_years 表示:即将赋给inf表中work_years字段的新值
create or replace trigger tr_empInfo_insert
before insert on empInfo for each row
begin
if(:new.work_years>0) --如果要添加的工作年限大于0,就把它改为0
then
:new.work_years:=0;
end if;
end;
insert into empInfo values(1,'tom',10);
update empInfo set work_years=5 where eid=1;
select * from empInfo;
--------------------------------
create table province
(
pid number primary key,
pname varchar2(20)
);
insert into province values(1,'湖北省');
create table city
(
cid number primary key,
cname varchar2(20),
pid number,
foreign key (pid) references province(pid)
);
insert into city values(1,'武汉市',1);
---------------------------------------------------------
select * from province;
select * from city;
delete from province where pid=1;
-------------------------------------------------------------
** 通过触发器,在删除主键表数据的时候,由触发器提前把关联的外键数据删除
create or replace trigger tr_province_delete
before delete on province for each row
begin
delete from city where pid=:old.pid; --如果城市表中的pid字段等于要删除的省份表中pid字段,就这对应城市信息删除
end;
------------------------------------------------------------
********注意:
:new.xxx
:old.xxx
这两个属性,只有在行级触发中才可以使用
-------------------------------------------------------------------------------------------------------------------------
*************表级触发器
--不包含 for each row 的触发器就是表级触发器
select user,sysdate from dual; -- user 获得当前用户 sysdate 获得系统时间
--------------------****************表级触发器
******创建表,实现日志记录
create table inf_log
(
log_id number primary key,
op_name varchar2(20),--用户名
op_time date,--操作时间,
op_details varchar2(100) --做了什么操作
)
create sequence inf_log_seq; --创建序列
--如果执行的是insert操作, 系统的inserting属性会返回true
--如果执行的是update操作, 系统的updating属性会返回true
--如果执行的是delete操作, 系统的deleting属性会返回true
create or replace trigger tr_inf_operation
before insert or update or delete on inf
begin
if inserting
then insert into inf_log values(inf_log_seq.nextval,user,sysdate,'向数据表添加了数据');
elsif updating
then insert into inf_log values(inf_log_seq.nextval,user,sysdate,'修改了数据表中的数据');
elsif deleting
then insert into inf_log values(inf_log_seq.nextval,user,sysdate,'删除了数据表中的数据');
end if;
end;
select * from inf_log;
delete from inf where id=13;
update inf set name='other' where id=3;
insert into inf values(13,'aaa',99);
