Oracle第十一课
一、课后作业讲解
-
给一个字符串,'7788,5566,2233'员工编号,传入存储过程,删除这些员工 记录,并给出这些员工删除的结果'true,false,true'删除成功true,否则false,以字符串形式返回。 比如5566删除失败(原因可能是不存在该记录,判断依据用系统隐式游标的rowcount属性) 注:要拆分传入的员工编号字符串为单个的员工编号
--1.删除的存储过程
CREATE OR REPLACE PROCEDURE p_delete(ids VARCHAR,-- 要删除的ids
rs OUT VARCHAR) -- 删除的结果
IS
v_ids VARCHAR(50) :=ids;-- 赋值给变量,因为入参无法改变
v_id VARCHAR(5);-- 截取出的id
n NUMBER;-- 逗号的位置
BEGIN
LOOP
EXIT WHEN v_ids IS NULL;-- ids为null时退出循环
n:=INSTR(v_ids,',');-- 找逗号的位置
IF n=0 THEN -- 没有逗号
v_id:=v_ids;
v_ids:=NULL;-- ids变为null,标识着准备退出循环
ELSE
v_id:=SUBSTR(v_ids,1,n-1);-- 找逗号前的id
v_ids:=SUBSTR(v_ids,n+1);-- ids更改为剩下的id信息
END IF;
DELETE FROM emp WHERE empno=v_id;-- 删除记录
IF SQL%ROWCOUNT=1 THEN -- 判断结果
IF rs IS NULL THEN-- 拼接结果
rs:='true';
ELSE
rs:=rs||',true';
END IF;
ELSE
IF rs IS NULL THEN
rs:='false';
ELSE
rs:=rs||',false';
END IF;
END IF;
END LOOP;
END;
--2.调用存储过程
-- Created on 2021/1/26 by DELL
declare
v_result VARCHAR2(50);
BEGIN
-- 删除id=1 id=2 id=3 结果为result1,result2,result3
p_delete('1,2,3',v_result);
COMMIT;
dbms_output.put_line('execute result:'||v_result);
end;
二、触发器
-
定义
触发器是附加在数据库对象上的一段代码,它指定了触发的时机和事件,触发器一般建立在表或视图上,也可以建立在用户或数据库层面上。触发器不能主动调用,只有在对相应对象操作时自动触发。
自动触发(指定触发的事件,增,删,改,登录,登出,重启,关闭)
可以在某个事件前,后触发(还有替代)
触发器比较隐蔽,发生问题不好定问,可以考虑是否用存储过程或其他技术来替代
-
根据触发器作用的对象分为三种:DML触发器,替代触发器,系统触发器
-
DML触发器(触发器的粒度)
-
语句级触发器(对sql操作只做一次触发)
-
建一个日志表,给emp表创建一个语句级DML触发器,操作数据时在日志表中记日志
-- 创建日志表
CREATE TABLE t_log(ID NUMBER PRIMARY KEY,
log_user VARCHAR2(20),
log_date DATE,
log_text VARCHAR2(100));
-- 创建一个触发器
CREATE OR REPLACE TRIGGER trg_emp1
BEFORE INSERT OR UPDATE OR DELETE
ON emp
DECLARE
v_event VARCHAR(10);
BEGIN
IF inserting THEN
v_event:='insert';
ELSIF updating THEN
v_event:='update';
ELSE
v_event:='delete';
END IF;
INSERT INTO t_log VALUES(seq1.nextval,USER,SYSDATE,v_event||'on emp');
END;
-- 触发器状态
alter trigger trg_emp1 disable;
alter trigger trg_emp1 enable;
-- 测试
INSERT INTO emp(empno) VALUES(2);
INSERT INTO emp(empno) VALUES(1);
DELETE FROM emp WHERE empno IN(1,2);
SELECT * FROM t_log;
-
-
行级触发器(每行都会进行触发):for each row
-- 创建新表,用原表数据填充
CREATE TABLE emp2 AS SELECT * FROM emp;
-- 创建新表,只要原表的结构
CREATE TABLE emp3 AS SELECT * FROM emp WHERE 1=2;
-- 创建行级触发器
CREATE OR REPLACE TRIGGER trg_emp2
BEFORE INSERT OR UPDATE OR DELETE
ON emp
FOR EACH ROW -- 行级触发器
DECLARE
BEGIN
IF inserting THEN
INSERT INTO emp2(empno,ename) VALUES(:new.empno,:new.ename);
ELSIF updating THEN
UPDATE emp2 SET ename=:new.ename WHERE empno=:old.empno;
ELSE
DELETE FROM emp2 WHERE empno=:old.empno;
END IF;
END;
-- 测试数据
INSERT INTO emp(empno) VALUES(2);
INSERT INTO emp(empno) VALUES(1);
DELETE FROM emp WHERE empno IN (1,2);
-
-
DDL触发器
-- 创建DDL触发器
CREATE OR REPLACE TRIGGER trg_ddl
AFTER DDL
ON scott.schema
DECLARE
BEGIN
INSERT INTO t_log(ID,log_user,log_date,log_text)
VALUES(seq1.nextval,USER,SYSDATE,ora_sysevent||'-'||ora_dict_obj_name||'-'||ora_dict_obj_type);
END; -
替代触发器:解决复杂视图不能插入数据的问题
-- 创建替代触发器
CREATE OR REPLACE TRIGGER trg_vemp
INSTEAD OF INSERT
ON v_emp
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO emp(empno,sal,comm) VALUES(:new.empno,:new.sal,0);
END;
-- 测试数据
INSERT INTO v_emp(empno,sal) VALUES(3,100);
三、事务
-
定义
为了一个业务操作,有时需要操作多条sql,只有这些sql都完成后,一个业务操作才算完成,这个过程就叫事务。
如果中间有一条sql出错,则全部回滚
如果所有sql都完成,则需要提交
-
事务的四大特点:ACID
-
原子性:Atomicity,要么都做,要么都不做
-
一致性:Consistency,事务操作前和操作后都是平衡的
-
隔离性:Isolation,多个事务并发对同一数据进行操作时,会有序操作,互不影响
-
持久性:Durability,事务一旦提交,数据会永久保存起来,断电或重启都不会影响
-
四、锁
-
锁的分类
-
从限制程序角度
-
排他锁
-
共享锁
-
-
从产生时机角度
-
自动锁
-
显示锁
-
-
从产生的操作角度
-
DML锁
-
DDL锁
-
-
悲观锁和乐观锁
-