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

随书光盘中的源码没有序号，部分找不到或者有bug，调试过程中一并更正。

其中，还有两个彩蛋：“双十九”乘法口诀表和“双九九”加法口诀表的输出（PL/SQL实现），以及一个附加（加薪函数的调用）

代码如下：

--代码2.9 使用嵌套块更新和插入部门表
/*说明：
1.随书源代码中v_deptno前缺少&符号，导致异常处理模块报错“ORA-01400: 无法将 NULL 插入”。
本人调试了半天，才发现问题所在，现已修复。
2.此段代码为手工录入，因为在随书光盘中未找到*/
/*以下为调试过程中使用的sql语句：
create table dept2 as select * from dept;
select * from dept2;
drop table dept2;*/

DECLARE
  v_deptno    NUMBER(2);
  v_deptname  VARCHAR2(14);
BEGIN
  --内部嵌套块
  BEGIN
    SELECT dname
    INTO   v_deptname
    FROM   dept2
    WHERE  deptno = &v_deptno;
    DBMS_OUTPUT.PUT_LINE('您查询的部门名称为：' || v_deptname);
  END;
  --内部嵌套块
  DECLARE
    v_loc VARCHAR2(13) := '上海浦东';
  BEGIN
    UPDATE dept2
    SET    loc = v_loc
    WHERE  deptno = v_deptno;
    DBMS_OUTPUT.PUT_LINE('在内部嵌套块中成功更新部门资料');
    COMMIT;
  END;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    BEGIN
      INSERT INTO dept2
      VALUES (&v_deptno,'IT开发','全球');
      DBMS_OUTPUT.PUT_LINE('在异常处理嵌套块成功新增部门资料');
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
END;

--代码2.10 命名嵌套块（在代码2.9中增加了<<查询与员工名称块>>等三个标签）
DECLARE
  v_deptno    NUMBER(2);
  v_deptname  VARCHAR2(14);
BEGIN
  --内部嵌套块
  <<查询员工名称块>>
  BEGIN
    SELECT dname
    INTO   v_deptname
    FROM   dept2
    WHERE  deptno = &v_deptno;
    DBMS_OUTPUT.PUT_LINE('您查询的部门名称为：' || v_deptname);
  END;
  --内部嵌套块
  <<更新员工记录块>>
  DECLARE
    v_loc VARCHAR2(13) := '上海浦东';
  BEGIN
    UPDATE dept2
    SET    loc = v_loc
    WHERE  deptno = v_deptno;
    DBMS_OUTPUT.PUT_LINE('在内部嵌套块中成功更新部门资料');
    COMMIT;
  END;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    <<插入员工记录块>>
    BEGIN
      INSERT INTO dept2
      VALUES (&v_deptno,'IT开发','全球');
      DBMS_OUTPUT.PUT_LINE('在异常处理嵌套块成功新增部门资料');
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
END;


--代码2.11 PL/SQL变量定义示例
DECLARE
  v_DeptName VARCHAR2(10);        --定义标量变量
  v_LoopCounter BINARY_INTEGER;   --使用PL/SQL类型定义标量变量
  --定义记录类型
  TYPE t_Employee IS RECORD (EmpName VARCHAR2(20),EmpNo NUMBER(7),Job VARCHAR2(20));
  v_Employee t_Employee;          --定义记录类型变量
  TYPE csor IS REF CURSOR;        --定义游标变量
  v_date DATE NOT NULL DEFAULT SYSDATE;--定义变量并指定默认值
BEGIN
 NULL;
END;
/

--代码2.12 使用CASE进行条件判断
CREATE OR REPLACE FUNCTION GetAddSalaryRatioCASE(p_Job VARCHAR2)
RETURN NUMBER AS
  v_Result NUMBER(7,2);
BEGIN  
  CASE p_Job                     --使用CASE WHEN语句进行条件判断
  WHEN 'CLERK' THEN              --职员
    v_Result:=0.10;
  WHEN 'SALESMAN' THEN           --销售
    v_Result:=0.12;
  WHEN 'MANAGER' THEN            --经理
    v_Result:=0.15;
  WHEN 'cont' THEN            --consultant，翻倍
    v_Result:=1;
  END CASE; 
 RETURN v_Result;                --返回值
END;

--代码2.13 使用FOR循环打印九九乘法口诀表
DECLARE
  v_Number1 NUMBER(3);  --外层循环变量
  v_Number2 NUMBER(3);  --内存循环变量
BEGIN  
  FOR v_Number1 IN 1..9 --开始外层循环
  LOOP
    --进行内存循环
    FOR v_Number2 IN 1..v_Number1
    LOOP
      --打印口决内容
      DBMS_OUTPUT.PUT(v_Number1||'*'||v_Number2||'='||v_Number1*v_Number2||'   ');
    END LOOP; 
    --输出换行  
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END;

--彩蛋1 使用FOR循环打印“双十九”乘法口诀表（印度乘法口诀表）
DECLARE
  v_Number1 NUMBER(3);  --外层循环变量
  v_Number2 NUMBER(3);  --内存循环变量
BEGIN  
  FOR v_Number1 IN 1..19 --开始外层循环
  LOOP
    --进行内存循环
    FOR v_Number2 IN 1..v_Number1
    LOOP
      --打印口决内容
      --第十行缩进，使得输出更美观
      DBMS_OUTPUT.PUT  (v_Number1||'*'||v_Number2||'='||v_Number1*v_Number2||'    ');
    END LOOP; 
    --输出换行  
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END;


--彩蛋2 使用FOR循环打印“双九九”加法口诀表
DECLARE
  v_Number1 NUMBER(3);  --外层循环变量
  v_Number2 NUMBER(3);  --内存循环变量
BEGIN  
  FOR v_Number1 IN 1..19 --开始外层循环
  LOOP
    --进行内存循环
    FOR v_Number2 IN 1..v_Number1
    LOOP
      --打印口决内容
      --第十行缩进，使得输出更美观
      DBMS_OUTPUT.PUT  (v_Number1||'+'||v_Number2||'='||(v_Number1+v_Number2)||'    ');
    END LOOP; 
    --输出换行  
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END;

--创建为员工加薪的过程
CREATE OR REPLACE PROCEDURE AddEmpSalary(p_Ratio NUMBER,p_EmpNo NUMBER)
AS
BEGIN
  IF p_Ratio>0 THEN         --判断传入的参数是否大于0
    --如果大于0,则更新Emp表中的数据
    UPDATE scott.emp3 SET sal2=sal2*(1+p_Ratio) WHERE EMPNO=p_EmpNo;
  END IF;
   --提示加薪成功。
  DBMS_OUTPUT.PUT_LINE('加薪成功!');
END;



--附加：调用加薪函数
DECLARE
  v_empno NUMBER(4);
  v_sal2  VARCHAR2(14);
  v_ratio  VARCHAR2(14);
BEGIN
  SELECT sal2
  INTO   v_sal2
  FROM   emp3
  WHERE  empno = &v_empno;
  addempsalary(&v_ratio, &v_empno);
  DBMS_OUTPUT.PUT_LINE('员工'||&v_empno||'已成功加薪百分之'||&v_ratio*100);
  commit;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE('未找到该员工！');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
END;

update emp3 set sal2=5120000 where empno=7509;

--代码2.15 员工加薪管理保代码
/*包规范定义，不包括实现代码*/
CREATE OR REPLACE PACKAGE EmpSalary 
AS
--执行实际的加薪动作
PROCEDURE AddEmpSalary(p_Ratio NUMBER,p_EmpNo NUMBER);
--使用IF-ELSIF语句得到加薪比率
FUNCTION GetAddSalaryRatio(p_Job VARCHAR2) RETURN NUMBER;
--使用CASE语句得到加薪比率
FUNCTION GetAddSalaryRatioCASE(p_Job VARCHAR2) RETURN NUMBER;
END EmpSalary;
/
/*包体定义*/
CREATE OR REPLACE PACKAGE BODY EmpSalary
AS
   PROCEDURE AddEmpSalary(p_Ratio NUMBER,p_EmpNo NUMBER)
    AS
    BEGIN
      IF p_Ratio>0 THEN         --判断传入的参数是否大于0
        --如果大于0,则更新Emp表中的数据
        UPDATE scott.emp SET sal=sal*(1+p_Ratio) WHERE EMPNO=p_EmpNo;
      END IF;
       --提示加薪成功。
      DBMS_OUTPUT.PUT_LINE('加薪成功!');
    END;
    
    FUNCTION GetAddSalaryRatio(p_Job VARCHAR2)
    RETURN NUMBER AS
      v_Result NUMBER(7,2);
    BEGIN
     IF p_Job='CLERK' THEN                       --如果为职员，加薪10%
       v_Result:=0.10;
     ELSIF p_Job='SALESMAN' THEN                 --如果为销售职员，加薪12%
       v_Result:=0.12;    
     ELSIF p_Job='MANAGER' THEN                  --如果为经理，加薪15%
       v_Result:=0.15;
     END IF;
     RETURN v_Result;   
    END; 
    
    FUNCTION GetAddSalaryRatioCASE(p_Job VARCHAR2)
    RETURN NUMBER AS
      v_Result NUMBER(7,2);
    BEGIN  
      CASE p_Job                     --使用CASE WHEN语句进行条件判断
      WHEN 'CLERK' THEN              --职员
        v_Result:=0.10;
      WHEN 'SALESMAN' THEN           --销售
        v_Result:=0.12;
      WHEN 'MANAGER' THEN            --经理
        v_Result:=0.15;
      END CASE; 
     RETURN v_Result;                --返回值
    END;      
END EmpSalary;