过程及程序块的技巧

1. --代码2.1 使用书序结构计算员工薪资  
2.   
3. DECLARE  
4.   v_sal1   NUMBER;  
5.   v_sal2   NUMBER;  
6.   v_sumsal NUMBER;  
7. BEGIN  
8.   SELECT sal  
9.   INTO   v_sal1  
10.   FROM   emp  
11.   WHERE  empno = &empno1;  
12.   SELECT sal  
13.   INTO   v_sal2  
14.   FROM   emp  
15.   WHERE  empno = &empno2;  
16.   v_sumsal := v_sal1 + v_sal2;  
17.   dbms_output.put_line('ID:' || &empno1 || ',' || &empno2 || ',工资之和：' ||  
18.                        v_sumsal);  
19. END;  
20.   
21.   
22. select * from emp order by sal desc;  
23.   
24. --代码2.2 使用分支结构为员工加薪  
25.   
26. DECLARE  
27.  --定义加薪比率常量  
28.  c_Manager CONSTANT NUMBER:=0.15;  
29.  c_SalesMan CONSTANT NUMBER:=0.12;  
30.  c_Clerk CONSTANT NUMBER:=0.10;  
31.  c_SR_CONT CONSTANT NUMBER:=0.88;  
32.  --定义工种变量  
33.  v_Job VARCHAR(100);  
34. BEGIN  
35.  --查询指定员工编码的员工信息  
36.  SELECT job INTO v_Job FROM scott.emp WHERE empno=&empNo1;  
37.  --执行分支判断  
38.  IF v_Job='CLERK' THEN  
39.    UPDATE scott.emp SET SAL=SAL*(1+c_Clerk) WHERE empno=&empNo1;  
40.  ELSIF v_Job='SALESMAN' THEN  
41.    UPDATE scott.emp SET SAL=SAL*(1+c_SalesMan) WHERE empno=&empNo1;     
42.  ELSIF v_Job='MANAGER' THEN  
43.    UPDATE scott.emp SET SAL=SAL*(1+c_Manager) WHERE empno=&empNo1;  
44.  ELSIF v_Job='SR.CONT' THEN  
45.    UPDATE scott.emp SET SAL=SAL*(1+c_SR_CONT) WHERE empno=&empNo1;        
46.  END IF;  
47.  --显示完成信息  
48.  DBMS_OUTPUT.PUT_LINE('已经为员工'||&empNo1||'成功加薪!');    
49.  EXCEPTION  
50.  --处理PL/SQL预定义异常  
51.  WHEN NO_DATA_FOUND THEN  
52.    DBMS_OUTPUT.PUT_LINE('没有找到员工数据');  
53. END;  
54.   
55.   
56. --代码2.3 使用循环结构为所有员工加薪（未使用自定义函数）  
57.   
58. DECLARE  
59.  --定义加薪比率常量  
60.  c_Manager CONSTANT NUMBER:=0.15;  
61.  c_SalesMan CONSTANT NUMBER:=0.12;  
62.  c_Clerk CONSTANT NUMBER:=0.10;  
63.  c_Cont CONSTANT NUMBER:=1.00;  
64.  v_Job VARCHAR(100);                         --定义职位变量  
65.  v_EmpNo VARCHAR(20);                        --定义员工编号变量  
66.  v_Ename VARCHAR(60);                        --定义员工名称变量  
67.  CURSOR c_Emp IS SELECT job,empno,ename from Scott.emp FOR UPDATE;  
68. BEGIN  
69.  OPEN c_Emp;   --打开游标  
70.  LOOP          --循环游标  
71.    FETCH c_Emp INTO v_Job,v_EmpNo,v_Ename;   --提取游标数据  
72.    EXIT WHEN c_Emp%NOTFOUND;                 --如果无数据可提取退出游标  
73.  IF v_Job='CLERK' THEN                       --如果为职员，加薪10%  
74.    UPDATE scott.emp SET SAL=SAL*(1+c_Clerk) WHERE CURRENT OF c_Emp;  
75.  ELSIF v_Job='SALESMAN' THEN                 --如果为销售职员，加薪12%  
76.    UPDATE scott.emp SET SAL=SAL*(1+c_SalesMan) WHERE CURRENT OF c_Emp;   
77.  ELSIF v_Job='MANAGER' THEN                  --如果为经理，加薪15%  
78.    UPDATE scott.emp SET SAL=SAL*(1+c_Manager) WHERE CURRENT OF c_Emp;    
79.  ELSIF v_Job='SR.CONT' THEN                  --如果为高级顾问，工资翻倍！  
80.    UPDATE scott.emp SET SAL=SAL*(1+c_Cont) WHERE CURRENT OF c_Emp;  
81.  END IF;  
82.  --显示完成信息  
83.  DBMS_OUTPUT.PUT_LINE('已经为员工'||v_EmpNo||':'||v_Ename||'成功加薪!');   
84.  END LOOP;  
85.  CLOSE c_Emp;                --关闭游标   
86.  EXCEPTION  
87.  WHEN NO_DATA_FOUND THEN     --处理PL/SQL预定义异常  
88.    DBMS_OUTPUT.PUT_LINE('没有找到员工数据');  
89. END;   
90.   
91.   
92.   
93. --执行动态SQL语句  
94.   
95. drop table books;  
96. DECLARE  
97.   v_SQLStr VARCHAR(200):=' CREATE TABLE BOOKS(ID int NOT NULL,BOOKNAME varchar2(100) NULL) ';  
98. BEGIN  
99.   EXECUTE IMMEDIATE v_SQLStr;  --执行DDL语句  
100. END;  
101.   
102. --尝试执行静态SQL语句（将发生编译错误）  
103.   
104. BEGIN  
105.   CREATE TABLE BOOKS(ID int NOT NULL,BOOKNAME varchar2(100) NULL;  
106. END;  
107.   
108. --代码2.4 创建员工对象  
109. CREATE OR REPLACE TYPE Emp_obj AS OBJECT  
110. (  
111.   empno NUMBER(4),     --员工编号属性  
112.   ename VARCHAR2(10),  --员工名称属性  
113.   job VARCHAR(9),      --员工职别属性  
114.   sal NUMBER(7,2),     --员工薪水属性  
115.   deptno NUMBER(2),    --部门编号属性  
116.   --加薪方法  
117.   MEMBER PROCEDURE AddSalary(radio NUMBER)  
118. );  
119. --定义对象类型体，实现对象方法  
120. CREATE OR REPLACE TYPE BODY Emp_obj AS  
121.   --实现对象方法  
122.   MEMBER PROCEDURE  AddSalary(radio NUMBER)  
123.   IS  
124.   BEGIN  
125.     sal:=sal*(1+radio);  --加上特定比例的薪水  
126.   END;   
127. END ;  
128.   
129. --代码2.5 创建函数封装业务逻辑  
130.   
131. CREATE OR REPLACE FUNCTION GetAddSalaryRatio(p_Job VARCHAR2)  
132. RETURN NUMBER AS  
133.   v_Result NUMBER(7,2);  
134. BEGIN  
135.  IF p_Job='CLERK' THEN                       --如果为职员，加薪10%  
136.    v_Result:=0.10;  
137.  ELSIF p_Job='SALESMAN' THEN                 --如果为销售职员，加薪12%  
138.    v_Result:=0.12;      
139.  ELSIF p_Job='MANAGER' THEN                  --如果为经理，加薪15%  
140.    v_Result:=0.15;  
141.  ELSIF p_Job='cont' THEN                  --如果为顾问，工资翻倍！  
142.    v_Result:=1;  
143.  END IF;  
144.  RETURN v_Result;     
145. END;  
146.   
147. --代码2.6 调用函数简化程序逻辑  
148. DECLARE  
149.  v_Job VARCHAR(100);                         --定义职位变量  
150.  v_EmpNo VARCHAR(20);                        --定义员工编号变量  
151.  v_Ename VARCHAR(60);                        --定义员工名称变量  
152.  v_Ratio NUMBER(7,2);  
153.  CURSOR c_Emp IS SELECT job,empno,ename from Scott.emp3 FOR UPDATE;  
154. BEGIN  
155.  OPEN c_Emp;   --打开游标  
156.  LOOP          --循环游标  
157.    FETCH c_Emp INTO v_Job,v_EmpNo,v_Ename;   --提取游标数据  
158.    EXIT WHEN c_Emp%NOTFOUND;                 --如果无数据可提取退出游标  
159.    v_Ratio:=GetAddSalaryRatio(v_Job);        --调用函数，得到加薪率  
160.    UPDATE scott.emp3 SET sal2=sal2*(1+v_Ratio) WHERE CURRENT OF c_Emp;          
161.  --显示完成信息  
162.  DBMS_OUTPUT.PUT_LINE('已经为员工'||v_EmpNo||':'||v_Ename||'成功加薪!');   
163.  END LOOP;  
164.  CLOSE c_Emp;                                --关闭游标   
165.  commit;  
166.  EXCEPTION  
167.  WHEN OTHERS THEN                            --处理PL/SQL预定义异常  
168.    DBMS_OUTPUT.PUT_LINE('没有找到员工数据');  
169.      
170. END;   
171.   
172. --代码2.7 最简单的PL/SQL块  
173. BEGIN  
174.   DBMS_OUTPUT.PUT_LINE('Oracle和Mysql差别挺大，PL/SQL是对SQL的增强。');  
175. END;  
176.   
177.   
178. --代码2.8 完整的PL/SQL语句块  
179. DECLARE  
180.   v_deptcount NUMBER(2);  
181.   v_deptno    NUMBER(2) := 60;  
182. BEGIN  
183.   SELECT COUNT(1)  
184.   INTO   v_deptcount  
185.   FROM   dept  
186.   WHERE  deptno = v_deptno;  
187.   IF v_deptcount = 0  
188.   THEN  
189.     INSERT INTO dept  
190.     VALUES  
191.       (v_deptno, '财务部', '深圳');  
192.     dbms_output.put_line('成功插入部门资料');  
193.   ELSIF v_deptcount = 1  
194.   THEN  
195.     dbms_output.put_line('该部门已存在！');  
196.   END IF;  
197. EXCEPTION  
198.   WHEN OTHERS THEN  
199.     dbms_output.put_line('部门资料插入失败！');  
200. END;  
201. commit;  
202. select * from dept;