源码-PL/SQL从入门到精通-第二章-PL/SQL基本概念-Part 3

接上篇。

代码如下：

--代码2.16 为emp表定义触发器代码
drop table raisesalarylog;

CREATE TABLE Scott.RaiseSalaryLog
(  
    --员工编号
   EmpNo NUMBER(10) NOT NULL PRIMARY KEY,   
 --加薪日期 
 RaisedDate DATE,                         
  --加薪前薪资 
  OriginalSal NUMBER(10,2),  
  --加薪后薪资             
   RaisedSal NUMBER(10,2)                  
    );

--定义触发器
CREATE OR REPLACE TRIGGER Scott.RaiseSalaryChange
--定义AFTER触发器，监测EMP表的SAL列的更新
AFTER UPDATE OF sal ON scott.emp
--定义的是行级别触发器
FOR EACH ROW
--声明区
DECLARE
  v_RecCount INT;  --定义记录数变量
BEGIN
 --查询更新EMP表的当前已被更新的员工是否在RAISESALARYLOG中存在
 SELECT COUNT(*) INTO v_RecCount FROM scott.RaiseSalaryLog WHERE EmpNo=:old.EmpNo;
 IF v_RecCount=0 THEN
    --如果不存在，则插入新的记录
    INSERT INTO scott.RaiseSalaryLog VALUES(:old.EmpNo,SYSDATE,:old.sal,:new.sal);  
 ELSE
   --如果存在则更新记录
    UPDATE scott.RaiseSalaryLog SET RaisedDate=SYSDATE,
    OriginalSal=:old.sal,RaisedSal=:new.sal WHERE EmpNo=:old.EmpNo;
 END IF;
  --如果出现错误，则显示错误消息
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/


SELECT *FROM EMP;

SELECT * FROM Scott.RaiseSalaryLog;

UPDATE Scott.emp SET sal=sal*1.2 WHERE empno=5093;

commit;


--代码2.17 触发PL/SQL预定义异常
DECLARE
  v_Ename VARCHAR2(30);       --定义员工名称保存变量
BEGIN
  --查询表中的员工名称
  SELECT ename INTO v_Ename FROM emp WHERE empno=&EmpNo;
  DBMS_OUTPUT.PUT_LINE('员工名称为：'||v_Ename);
  --异常处理块 
  EXCEPTION
  --异常筛选器
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有找到记录!');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('其他未处理异常!');
END;

--2.18 使用记录类型获取员工信息
DECLARE
  --定义记录类型
  TYPE Emp_Info_Type IS RECORD
  (
    EmpName VARCHAR2(10),
    Job VARCHAR(9),
    Sal NUMBER(11,2)
  );
  --声明记录类型的变量
  EmpInfo emp_Info_Type;
BEGIN
  --查询数据并保存到记录类型中
  SELECT ename,job,sal INTO EmpInfo FROM emp WHERE empno=&EmpNo;
  --输出记录类型变量中保存的员工消息
  DBMS_OUTPUT.PUT_LINE('员工信息为：员工姓名：'||EmpInfo.EmpName||
  ' 职位：'||EmpInfo.Job||
  ' 薪资：'||EmpInfo.Sal); 
END;


--代码2.19 使用游标和索引表显示员工名称
DECLARE 
  --定义员工名称索引表
  TYPE Emp_Table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
  EmpList Emp_Table;   --定义表类型的变量
  --定义游标类型
  CURSOR empcursor IS SELECT EName FROM emp;
BEGIN
  --如果游标没有打开则打开游标
  IF NOT empcursor%ISOPEN THEN
     OPEN empcursor;
  END IF;
  --从游标结果中提取所有的员工名称
  FETCH empcursor BULK COLLECT INTO EmpList;
  --使用FOR循环显示所有的员工名称
  FOR i IN 1..EmpList.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('员工名称：'||EmpList(i));
  END LOOP;
  CLOSE empcursor;   --关闭游标
END;

--代码2.20 使用动态SQL语句实现数据处理
DECLARE
  v_SQLStr VARCHAR2(200);  --保存SQL语句的变量
  v_Id INT;                --保存临时字段值的变量
  v_Name VARCHAR(100);
BEGIN
  --在嵌套块中先删除要创建的临时表
  BEGIN
  v_SQLStr:='DROP TABLE temptable';
  EXECUTE IMMEDIATE v_SQLStr;
  --如果产生异常不进行处理
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  --定义DDL语句来创建SQL
  v_SQLStr:='CREATE TABLE temptable (id INT NOT NULL PRIMARY KEY,tmpname VARCHAR2(100))';
  EXECUTE IMMEDIATE v_SQLStr;  --执行动态语句
  --向新创建的临时表中插入数据
  v_SQLStr:='INSERT INTO temptable VALUES(10,''临时名称1'')';
  EXECUTE IMMEDIATE v_SQLStr;  --执行动态语句
  --检索临时表数据，这里使用了动态SQL语句变量
  v_SQLStr:='SELECT * FROM temptable WHERE id=:tempId';
  --执行并获取动态语句查询结果
  EXECUTE IMMEDIATE v_SQLstr INTO v_Id,v_Name USING &1;
  --输出表中的信息
  DBMS_OUTPUT.PUT_LINE(v_Id||' '||v_Name);
END;

--代码2.21 使用3空格缩进进行代码格式化

--代码参考2.3 相同，可使用客户端代码美化器功能提高代码可读性