--以下为显示游标
--隐式游标
--游标 pS:注意 游标的申明!类型在前! 当OPEN游标的时候,上下文区是没数据的!!!!!
declare
cursor firstCursor is select sal from emp where emp.empno=&empId;
vsalary number;
begin
open firstCursor;
fetch firstCursor into vsalary;
close firstCursor;
dbms_output.put_line(vsalary);
end;
select * from emp
--游标属性
declare
cursor firstCursor is select sal from emp where emp.deptno=&deptID;
vsalary number;
begin
if not firstCursor%isopen then dbms_output.put_line('游标未打开!'); end if;--如何游标未打开输出信息
open firstCursor;
loop
fetch firstCursor into vsalary;
dbms_output.put_line(firstCursor%rowcount);
dbms_output.put_line(vsalary);
exit when firstCursor%notfound;
end loop;
close firstCursor;
end;
select * from emp where emp.deptno = 30
--FETCH BULK COLLECT INTO 语句
--将游标指向的当前行的数据一次性插入到ROWTYPE类型中去
declare
type empRecord is record(
empName emp.ename%type,
empSalary emp.sal%type
);
type empTable is table of empRecord index by binary_integer;
emptab empTable;
cursor myCursor is select emp.ename,emp.sal from emp where emp.deptno=&deptID;
begin
open myCursor;
fetch myCursor bulk collect into emptab;
close myCursor;
for i in 1..emptab.count loop
dbms_output.put_line(emptab(i).empName||' '||emptab(i).empSalary);
end loop;
end;
--用ROWTYPE类型类保存游标数据
declare
empRow emp%rowtype;
cursor empCursor is select * from emp where emp.deptno=&deptID;
begin
open empCursor;
loop
fetch empCursor into empRow;
exit when empCursor%notfound;
dbms_output.put_line(empRow.ename||' '||empRow.sal);
end loop;
end;
/*参数游标.PS在定义游标参数时,无需为游标参数设置长度.
而且记得在游标SELECT语句后加上该参数的WHERE 条件,否则参数游标无意义*/
declare
empRow emp%rowtype;
cursor empCursor(a emp.deptno%type) is select * from emp where emp.deptno=a;
begin
open empCursor(&deptNo);
loop
fetch empCursor into empRow;
dbms_output.put_line(empRow.ename||' '||empRow.sal||' '||empRow.deptNo);
exit when empCursor%notfound;
end loop;
end;
select * from emp where emp.deptno=30
--UPDATE 游标
declare
cursor empCursor(a emp.deptno%type) is select * from emp where emp.deptno=a for update;
empRow emp%rowtype;
ecount number:=0;
begin
open empCursor(&deptNo);
loop
fetch empCursor into empRow;
exit when empCursor%notfound ;
if empRow.sal<5000 then
update emp set sal =sal+1 where current of empCursor ;
ecount:=ecount+1;
end if;
end loop;
close empCursor;
dbms_output.put_line(ecount);
end;
select * from emp where emp.deptno = 30
--使用FOR循环的游标遍历,非常简单.ps无需打开和关闭游标!!!!
--FOR循环里的循环变量直接是游标的ROWTYPE类型
declare
cursor empCursor is select * from emp where emp.deptno=&deptNo;
empRow emp%rowtype;
begin
for empRow in empCursor loop
dbms_output.put_line(empRow.ename);
end loop;
end;
--在游标FOR循环中直接使用子查询
declare
empRow emp%rowtype;
begin
for empRow in (select * from emp where emp.deptno=&deptNo) loop
dbms_output.put_line(empRow.ename);
end loop;
end;
--自定义游标变量
declare
type empCursor is ref Cursor;
empCur empCursor;
empRow emp%rowtype;
begin
open empCur for select * from emp where emp.deptno=&deptNo;
loop
fetch empCur into empRow;
exit when empCur%notfound;
dbms_output.put_line(empRow.ename);
end loop;
close empCur;
end;
select * from emp where emp.deptno = 30
--创建返回结果集的存储过程,注意SYS_CURSOR的使用.
create or replace procedure proc_getAllEmpByDeptId(
empCursor out sys_refcursor,
a number
)as
begin
open empCursor for select * from emp where emp.deptno=a;
end;
--注意SYS_REFCURSOR类型游标不支持FOR.但可以作为参数传递,
--而CURSOR类型游标不能作为参数传递
declare
empRow emp%rowtype;
empCursor sys_refcursor;
begin
proc_getAllEmpByDeptId(empCursor,&deptNo);
loop
fetch empCursor into empRow;
exit when empCursor%notfound;
dbms_output.put_line(empRow.ename);
end loop;
end;
-- 使用函数来返回系统游标
create or replace function fun_getAllEmpsByDeptId(a emp.deptno%type) return sys_refcursor
as
empCursor sys_refcursor;
begin
open empCursor for select * from emp where emp.deptno=a;
return empCursor;
end;
--调用刚刚创建的函数
declare
deptNo number:=&deptNo;
empCursor sys_refcursor;
empRow emp%rowtype;
begin
empCursor:=fun_getAllEmpsByDeptId(deptNo);
loop
fetch empCursor into empRow;
exit when empCursor%notfound;
dbms_output.put_line(empRow.ename);
end loop;
end;
--触发器
create or replace trigger emp_insertTrigger before insert or delete or update on emp
begin
RAISE_APPLICATION_ERROR(-20000,'暂时不能操作此表');
end;
-- 测试触发器
delete from emp where emp.empno=108
--删除
drop trigger emp_insertTrigger
--条件谓词
create or replace trigger emp_insertTrigger before insert or delete or update on emp
begin
case
when INSERTING then
RAISE_APPLICATION_ERROR(-20000,'暂时不能插入记录');
when UPDATING then
RAISE_APPLICATION_ERROR(-20000,'暂时不能更新记录');
when DELETING then
RAISE_APPLICATION_ERROR(-20000,'暂时不能删除记录');
else
RAISE_APPLICATION_ERROR(-20000,'反正就是不让你操作此表');
end case;
end;
--使用UPDATE(列名)判断是否更新此列
create or replace trigger emp_insertTrigger before insert or delete or update on emp
begin
if updating('sal') then
RAISE_APPLICATION_ERROR(-20000,'你休想修改工资!');
end if;
end;
--测试
update emp set sal=sal+1 where emp.empno=108;
drop trigger emp_insertTrigger
--创建审计表
create table audit_table(
AID number primary key,
tb_name varchar2(20) not null,
ins number not null,
del number not null,
upd number not null,
starttime date,
endtime date
);
drop table audit_table
--为审计表创建序列
create sequence seq_audit start with 1 increment by 1;
--创建关于审计表的触发器
create or replace trigger tri_audit after insert or update or delete on emp
declare Acount number;
begin
select count(*) into Acount from audit_table where tb_name='EMP';
if acount = 0 then
insert into audit_table values(seq_audit.nextval,'EMP',0,0,0,sysdate,null);
end if;
case
--判断当前触发的是什么事件
when inserting then update audit_table set ins=ins+1,endtime=sysdate where tb_name='EMP';
when deleting then update audit_table set del=del+1,endtime=sysdate where tb_name='EMP';
when updating then update audit_table set upd=upd+1,endtime=sysdate where tb_name='EMP';
end case;
end;
--测试
update emp set sal =sal+1 where emp.empno=108;
select * from audit_table
drop table audit_table
drop trigger tri_audit
--BEFORE行级触发器 PS:注意,行级触发器实际是作用在某表的某个字段的
--而且,在语法上是先写OF字段再ON表明
create or replace trigger tri_emp_row_before before update of sal on emp for each row
begin
if :NEW.sal < :OLD.sal THEN
raise_application_error(-20000,'不能降低员工工资!');
end if;
end;
--测试EMP表的BEFORE行级触发器
update emp set sal=sal-100 where emp.empno=7499;
drop trigger tri_emp_row_before
create table test(
id number,
sal number
)
--AFTER行级触发器
create or replace trigger tri_emp_salary_after after update of sal on emp for each row
begin
if :NEW.sal < :OLD.sal THEN
insert into test values(1,:NEW.sal);
end if;
end;
--测试AFTER行级触发器
update emp set sal=sal-100 where emp.empno=7499;
select * from test;
drop trigger tri_emp_salary_after;
drop table test;
--创建BEFORE行级带有WHEN子句的触发器 PS:注意,此处的WHEN子句里用到旧表时应为OLD;
--此触发器会在修改EMP表里员工ID为108的工资时触发!
create or replace trigger tri_emp_salary_before_when before update of sal on emp for each row when (old.empno=7499)
begin
raise_application_error(-20000,'请不要修改Allen的工资');
end;
--测试BEFORE行级带有WHEN子句的触发器
update emp set sal=100 where empno=7499;
drop trigger tri_emp_salary_before_when
--创建用户登陆触发器PS:普通用户无此权限,使用SYS用户
create or replace trigger tri_login after logon on database when(ora_login_user not in('SYS','SYSTEM'))
begin
insert into loginInfo values(seq_loginInfo.nextval,ora_login_user);
end;
--在SYS用户下创建此表
create table loginInfo(
LID number primary key,
LoginName varchar2(10)
);
--创建此表的自动增长列
create sequence seq_loginInfo start with 1 increment by 1;
--给SCOTT用户授权
grant select on loginInfo to scott
select * from sys.loginInfo
--创建DDL触发器 需SYS或SYSTEM用户
create or replace trigger tri_create after create on database
begin
dbms_output.put_line('创建了表');
end;