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

随书附带的源码没有序号，部分有bug，调试过程中一并更正。

--代码2.1 使用书序结构计算员工薪资

DECLARE
  v_sal1   NUMBER;
  v_sal2   NUMBER;
  v_sumsal NUMBER;
BEGIN
  SELECT sal
  INTO   v_sal1
  FROM   emp
  WHERE  empno = &empno1;
  SELECT sal
  INTO   v_sal2
  FROM   emp
  WHERE  empno = &empno2;
  v_sumsal := v_sal1 + v_sal2;
  dbms_output.put_line('ID:' || &empno1 || ',' || &empno2 || ',工资之和：' ||
                       v_sumsal);
END;


select * from emp order by sal desc;

--代码2.2 使用分支结构为员工加薪

DECLARE
 --定义加薪比率常量
 c_Manager CONSTANT NUMBER:=0.15;
 c_SalesMan CONSTANT NUMBER:=0.12;
 c_Clerk CONSTANT NUMBER:=0.10;
 c_SR_CONT CONSTANT NUMBER:=0.88;
 --定义工种变量
 v_Job VARCHAR(100);
BEGIN
 --查询指定员工编码的员工信息
 SELECT job INTO v_Job FROM scott.emp WHERE empno=&empNo1;
 --执行分支判断
 IF v_Job='CLERK' THEN
   UPDATE scott.emp SET SAL=SAL*(1+c_Clerk) WHERE empno=&empNo1;
 ELSIF v_Job='SALESMAN' THEN
   UPDATE scott.emp SET SAL=SAL*(1+c_SalesMan) WHERE empno=&empNo1;   
 ELSIF v_Job='MANAGER' THEN
   UPDATE scott.emp SET SAL=SAL*(1+c_Manager) WHERE empno=&empNo1;
 ELSIF v_Job='SR.CONT' THEN
   UPDATE scott.emp SET SAL=SAL*(1+c_SR_CONT) WHERE empno=&empNo1;      
 END IF;
 --显示完成信息
 DBMS_OUTPUT.PUT_LINE('已经为员工'||&empNo1||'成功加薪!');  
 EXCEPTION
 --处理PL/SQL预定义异常
 WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('没有找到员工数据');
END;


--代码2.3 使用循环结构为所有员工加薪（未使用自定义函数）

DECLARE
 --定义加薪比率常量
 c_Manager CONSTANT NUMBER:=0.15;
 c_SalesMan CONSTANT NUMBER:=0.12;
 c_Clerk CONSTANT NUMBER:=0.10;
 c_Cont CONSTANT NUMBER:=1.00;
 v_Job VARCHAR(100);                         --定义职位变量
 v_EmpNo VARCHAR(20);                        --定义员工编号变量
 v_Ename VARCHAR(60);                        --定义员工名称变量
 CURSOR c_Emp IS SELECT job,empno,ename from Scott.emp FOR UPDATE;
BEGIN
 OPEN c_Emp;   --打开游标
 LOOP          --循环游标
   FETCH c_Emp INTO v_Job,v_EmpNo,v_Ename;   --提取游标数据
   EXIT WHEN c_Emp%NOTFOUND;                 --如果无数据可提取退出游标
 IF v_Job='CLERK' THEN                       --如果为职员，加薪10%
   UPDATE scott.emp SET SAL=SAL*(1+c_Clerk) WHERE CURRENT OF c_Emp;
 ELSIF v_Job='SALESMAN' THEN                 --如果为销售职员，加薪12%
   UPDATE scott.emp SET SAL=SAL*(1+c_SalesMan) WHERE CURRENT OF c_Emp; 
 ELSIF v_Job='MANAGER' THEN                  --如果为经理，加薪15%
   UPDATE scott.emp SET SAL=SAL*(1+c_Manager) WHERE CURRENT OF c_Emp;  
 ELSIF v_Job='SR.CONT' THEN                  --如果为高级顾问，工资翻倍！
   UPDATE scott.emp SET SAL=SAL*(1+c_Cont) WHERE CURRENT OF c_Emp;
 END IF;
 --显示完成信息
 DBMS_OUTPUT.PUT_LINE('已经为员工'||v_EmpNo||':'||v_Ename||'成功加薪!'); 
 END LOOP;
 CLOSE c_Emp;                --关闭游标 
 EXCEPTION
 WHEN NO_DATA_FOUND THEN     --处理PL/SQL预定义异常
   DBMS_OUTPUT.PUT_LINE('没有找到员工数据');
END; 



--执行动态SQL语句

drop table books;
DECLARE
  v_SQLStr VARCHAR(200):=' CREATE TABLE BOOKS(ID int NOT NULL,BOOKNAME varchar2(100) NULL) ';
BEGIN
  EXECUTE IMMEDIATE v_SQLStr;  --执行DDL语句
END;

--尝试执行静态SQL语句（将发生编译错误）

BEGIN
  CREATE TABLE BOOKS(ID int NOT NULL,BOOKNAME varchar2(100) NULL;
END;

--代码2.4 创建员工对象
CREATE OR REPLACE TYPE Emp_obj AS OBJECT
(
  empno NUMBER(4),     --员工编号属性
  ename VARCHAR2(10),  --员工名称属性
  job VARCHAR(9),      --员工职别属性
  sal NUMBER(7,2),     --员工薪水属性
  deptno NUMBER(2),    --部门编号属性
  --加薪方法
  MEMBER PROCEDURE AddSalary(radio NUMBER)
);
--定义对象类型体，实现对象方法
CREATE OR REPLACE TYPE BODY Emp_obj AS
  --实现对象方法
  MEMBER PROCEDURE  AddSalary(radio NUMBER)
  IS
  BEGIN
    sal:=sal*(1+radio);  --加上特定比例的薪水
  END; 
END ;

--代码2.5 创建函数封装业务逻辑

CREATE OR REPLACE 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;
 ELSIF p_Job='cont' THEN                  --如果为顾问，工资翻倍！
   v_Result:=1;
 END IF;
 RETURN v_Result;   
END;

--代码2.6 调用函数简化程序逻辑
DECLARE
 v_Job VARCHAR(100);                         --定义职位变量
 v_EmpNo VARCHAR(20);                        --定义员工编号变量
 v_Ename VARCHAR(60);                        --定义员工名称变量
 v_Ratio NUMBER(7,2);
 CURSOR c_Emp IS SELECT job,empno,ename from Scott.emp3 FOR UPDATE;
BEGIN
 OPEN c_Emp;   --打开游标
 LOOP          --循环游标
   FETCH c_Emp INTO v_Job,v_EmpNo,v_Ename;   --提取游标数据
   EXIT WHEN c_Emp%NOTFOUND;                 --如果无数据可提取退出游标
   v_Ratio:=GetAddSalaryRatio(v_Job);        --调用函数，得到加薪率
   UPDATE scott.emp3 SET sal2=sal2*(1+v_Ratio) WHERE CURRENT OF c_Emp;        
 --显示完成信息
 DBMS_OUTPUT.PUT_LINE('已经为员工'||v_EmpNo||':'||v_Ename||'成功加薪!'); 
 END LOOP;
 CLOSE c_Emp;                                --关闭游标 
 commit;
 EXCEPTION
 WHEN OTHERS THEN                            --处理PL/SQL预定义异常
   DBMS_OUTPUT.PUT_LINE('没有找到员工数据');
   
END; 

--代码2.7 最简单的PL/SQL块
BEGIN
  DBMS_OUTPUT.PUT_LINE('Oracle和Mysql差别挺大，PL/SQL是对SQL的增强。');
END;


--代码2.8 完整的PL/SQL语句块
DECLARE
  v_deptcount NUMBER(2);
  v_deptno    NUMBER(2) := 60;
BEGIN
  SELECT COUNT(1)
  INTO   v_deptcount
  FROM   dept
  WHERE  deptno = v_deptno;
  IF v_deptcount = 0
  THEN
    INSERT INTO dept
    VALUES
      (v_deptno, '财务部', '深圳');
    dbms_output.put_line('成功插入部门资料');
  ELSIF v_deptcount = 1
  THEN
    dbms_output.put_line('该部门已存在！');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('部门资料插入失败！');
END;
commit;
select * from dept;