Oracle之PL/SQL学习笔记

Oracle之PL/SQL学习笔记

Oracle之PL/SQL学习笔记：

1. -- 使用匿名过程完成工资修改   
2. SET SERVEROUT ON ;  
3. DECLARE  
4.     l_salary NUMBER(5) ;  
5.     l_empno NUMBER(4) ;   
6. BEGIN  
7.     l_empno := 7369 ;   -- initialize must be execution section   
8.     SELECT sal INTO l_salary FROM emp WHERE empno=l_empno ;  
9.     IF l_salary > 500 THEN         
10.         UPDATE emp SET sal = l_salary+500 WHERE empno=l_empno ;  
11.     END IF;  
12.     EXCEPTION   -- Handle Exception   
13.         WHEN NO_DATA_FOUND THEN  
14.             DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;  
15. END ;  
16.   
17. -- 创建命名嵌套存储过程完成工资修改   
18. SET SERVEROUT ON ;  
19. CREATE OR REPLACE PROCEDURE change_salary IS  
20. -- DECLARE      -- 命名存储过程不需要DECLARE   
21.     l_salary NUMBER(5) ;  
22.     l_empno NUMBER(4) := 7369 ;   
23. BEGIN  
24.     -- l_empno := 7369 ;    -- initialize must be execution section   
25.     SELECT sal INTO l_salary FROM emp WHERE empno=l_empno ;  
26.     IF l_salary > 500 THEN         
27.         UPDATE emp SET sal = l_salary-500 WHERE empno=l_empno ;  
28.     END IF;  
29.         BEGIN  
30.             DBMS_OUTPUT.PUT_LINE('NESTED SECSION') ;  
31.         END ;  
32.     EXCEPTION   -- Handle Exception   
33.         WHEN NO_DATA_FOUND THEN  
34.             DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;  
35. END ;  
36.   
37. -- 定义变量   
38. identifier [CONSTANT] datatype [NOT NULL]  [:= | DEFAULT expr];  
39. v_hirdate DATE ;  
40. v_empno emp.empno%TYPE := 7369 ;  
41.   
42. DECLARE  
43.     emp_count NUMBER ;  
44. BEGIN  
45.     SELECT COUNT(*) INTO emp_count FROM employees WHERE department_id=&department_id;  
46.     DBMS_OUTPUT.PUT_LINE('count'||':'||emp_count) ;  
47. END ;  
48.   
49.   
50. -- Returning 子句   
51. DECLARE   
52.     myname employees.last_name%TYPE ;  
53.     mysal employees.salary%TYPE ;  
54. BEGIN  
55.     FOR rec IN (SELECT * FROM employees)   
56.     LOOP  
57.         UPDATE employees SET salary = rec.salary/1 WHERE rec.employee_id = employee_id -- 此处不可加分号   
58.         RETURNING last_name,salary INTO myname,mysal ;  -- 将修改后的值放入到变量中   
59.         DBMS_OUTPUT.PUT_LINE('myname:'||myname||',sal:'||mysal) ;  
60.     END LOOP ;  
61. END ;  
62.   
63. -- IF Statement   
64. DECLARE  
65.     v_start NUMBER:=1000 ;  
66. BEGIN  
67.     IF v_start>100 THEN  
68.         v_start:=2*v_start ;  
69.     ELSIF v_start>50 THEN  
70.         v_start:=5*v_start ;  
71.     ELSE  
72.         v_start:=0.1*v_start ;  
73.     END IF ;  
74.     DBMS_OUTPUT.PUT_LINE(v_start) ;  
75. END ;  
76.   
77. -- CASE Statement(Start with 'CASE' end by 'END')   
78. SET SERVEROUT ON ;  
79. DECLARE   
80.     -- no NUMBER :=100 ;   
81.     no2 NUMBER:= &i ;  
82. BEGIN  
83.     CASE no2  
84.     WHEN 100 THEN  
85.         DBMS_OUTPUT.PUT_LINE(100) ;  
86.     WHEN 200 THEN  
87.         DBMS_OUTPUT.PUT_LINE(200) ;  
88.     WHEN 300 THEN  
89.         DBMS_OUTPUT.PUT_LINE(300) ;  
90.     ELSE  
91.         DBMS_OUTPUT.PUT_LINE('DEFAULT') ;  
92.     END CASE ;  
93. END ;  
94.   
95. -- Select语句中使用Case块   
96. SELECT empno,CASE empno   
97.         WHEN 7369 THEN 'SMITH'  
98.         WHEN 7499 THEN 'ALLEN'  
99.         ELSE 'AKWOLF'  
100.         END FROM emp ;  
101.   
102. SELECT COUNT(CASE WHEN sal<1000 THEN 1 ELSE NULL END) count1,  
103.     COUNT(CASE WHEN sal>=1000 AND sal<2000 THEN 1 ELSE NULL END) count2  
104.     from emp ;  
105.   
106. -- Basic Loop   
107. DECLARE  
108.     v_orderid NUMBER:=1014 ;  
109.     v_counter NUMBER:=1 ;  
110. BEGIN  
111.     LOOP  
112.         INSERT INTO item VALUES(v_orderid,v_counter) ;  
113.         v_counter := v_counter+1 ;  
114.         EXIT WHEN v_counter>10 ;  
115.     END LOOP ;  
116. END ;  
117.   
118. -- For Loop   
119. DECLARE  
120.     v_orderid NUMBER :=199 ;  
121. BEGIN  
122.     FOR i IN 1..20 LOOP  
123.         INSERT INTO item VALUES(v_orderid,i) ;  
124.     END LOOP ;  
125. END;  
126.   
127. -- While Loop   
128. DECLARE  
129.     v_qty NUMBER :=1 ;  
130.     v_total NUMBER :=0 ;  
131. BEGIN  
132.     WHILE v_total<   &input LOOP  
133.         v_qty :=v_qty+1 ;  
134.         v_total := v_qty*&price ;  
135.         DBMS_OUTPUT.PUT_LINE(v_total) ;  
136.     END LOOP ;  
137. END ;  
138.   
139. -- CURSOR游标   
140. DECLARE  
141.     CURSOR emp_cur IS SELECT * FROM emp ;  
142. BEGIN  
143.     FOR emp_rec IN emp_cur LOOP  
144.         DBMS_OUTPUT.PUT_LINE('empno: '||emp_rec.empno||' ,ename: '||emp_rec.ename) ;  
145.     IF SQL%FOUND THEN  
146.         DBMS_OUTPUT.PUT_LINE('FOUND -->'||emp_rec.empno) ;  
147.     ELSIF SQL%NOTFOUND THEN  
148.         DBMS_OUTPUT.PUT_LINE('NOTFOUND -->') ;  
149.     ELSIF NOT SQL%ISOPEN THEN  
150.         DBMS_OUTPUT.PUT_LINE('NOT OPEN -->') ;     
151.     ELSE  
152.         DBMS_OUTPUT.PUT_LINE('THIS SESCTION HAS EXECTION'||SQL%ROWCOUNT) ;  
153.     END IF ;  
154.     END LOOP ;  
155. END ;