oracle plsql 存储过程常用例子

1.   Author:qinyangzhao                                  
2.    describe:PL/SQL记录记录 (type is record)                             
3.                                                       
4. *********************************************************/   
5. set serveroutput on--打开显示模式   
6. declare  
7.     type cust_record_type is record(--定义记录类型   
8.         name customer.name%type,--声明标量变量   
9.         total ord.total%type--声明记录变量   
10.     );   
11.     cust_record cust_record_type;   
12. begin  
13.     select a.name ,b.total ,into cust_record   
14.     from customer a ,ord b   
15.     where a.customer_id=b.customer_id and b.ord_id=&id;   
16.     dbms_output.put_line('客户名'||cust_record.name);   
17.     dbms_output.put_line('订单总额:'||cust_record.total);   
18. end;     
19. /*********************************************************   
20.                                                       
21.    Author:qinyangzhao                                  
22.    describe:%rowtype属性(rowtype)                              
23.                                                        
24. *********************************************************/   
25. delcare   
26.     product_record product%rowtype;   
27. begin  
28.     product_record.product_id:=&id;   
29.     product_record.description:='&description';   
30.     insert into product values product_record;   
31. end;     
32. /*********************************************************   
33.                                                        
34.    Author:qinyangzhao                                  
35.    describe:索引表(table)                               
36.                                                        
37. *********************************************************/       
38. declare  
39.     type item_table_type is table of item.ename%type   
40.     index by pls_integer;   
41.     item_table item_table_type;   
42. begin  
43.     select * bulk collect into item_table(-1)   
44.     from item where ord_id=&id;      
45.     dbms_output.put_line('条款编号:'||item_table(-1));   
46.        
47. end;           
48.   
49. /*********************************************************   
50.                                                        
51.    Author:qinyangzhao                                  
52.    describe:嵌套表(table)                               
53.                                                        
54. *********************************************************/       
55. declare  
56.     type item_table_type is table of item.ename%type;      
57.     item_table item_table_type;   
58. begin  
59.     item_table:=item_table_type('mary','mary','mary');   
60.     select ename into item_table(2) from item   
61.     where empno=&no;   
62.     dbms_output.put_line('雇员名:'||item_table(2));   
63. end;     
64. /*********************************************************   
65.                                                       
66.    Author:qinyangzhao                                  
67.    describe:变长数组(array)                                
68.                                                       
69. *********************************************************/   
70. declare  
71.   
72.     type name_array_type is varray(20) of varchar2(30);   
73.     type city_array_type is varray(20) of varchar2(30);   
74.     name_array name_array_type;   
75.     city_array city_array_type;   
76. begin           
77.     select name ,city bulk collect   
78.         into name_array,city_array from customer;   
79.     for i in 1..name_array.count loop   
80.         dbms_output.put_line('客户名:'||name_array(i)||',所在城市:'||city_array(i));   
81.     end loop;       
82. end;   
83. /*********************************************************   
84.                                                        
85.    Author:qinyangzhao                                  
86.    describe:记录表(table)                               
87.                                                        
88. *********************************************************/       
89. declare  
90.     type item_table_type is table of item%rowtype   
91.     index by pls_integer;   
92.     item_table item_table_type;   
93. begin  
94.     select * bulk collect into item_table   
95.     from item where ord_id=&id;   
96.     for i in 1..item_table.count loop   
97.         dbms_output.put_line('条款编号:'||item_table(i).item_id||',总价:'||   
98.             item_table(i).total);   
99.      end loop;   
100. end;        
101. /*********************************************************   
102.                                                        
103.    Author:qinyangzhao                                  
104.    describe:多级(varray)                               
105.                                                        
106. *********************************************************/     
107. declare  
108.     type al_varray_type is varray(10) of int;--定义一维varray   
109.     type nal_varray_type is varray(10) of al_varray_type;--定义二维varrary集合   
110.     --初始化二维集合变量   
111.     nvl nal_varrary_type:=nal_varrary_type(   
112.         al_varray_type(58,100,102),   
113.         al_varray_type(55,6,73),   
114.         al_arrary_type(2,4));   
115. begin  
116.     dbms_output.put_line('显示二维数组所有元素');   
117.     for i in 1..nvl.count loop   
118.         for j in 1..nvl(i).count loop   
119.             dbms_output.put_line('nvl('||i||','||j||')='||nvl(i,j));   
120.         end loop;   
121.     end loop;   
122. end;                       
123. /*********************************************************   
124.                                                        
125.    Author:qinyangzhao                                  
126.    describe:多级(嵌套)                               
127.                                                        
128. *********************************************************/     
129. declare  
130.     type al_table_type is table of int;--定义一维嵌套表   
131.     type nal_table_type is table of al_table_type;--定义二维嵌套表集合   
132.     --初始化二维集合变量   
133.     nvl nal_varrary_type:=nal_varrary_type(   
134.         al_varray_type(58,100,102),   
135.         al_varray_type(55,6,73),   
136.         al_arrary_type(2,4));   
137. begin  
138.     dbms_output.put_line('显示二维数组所有元素');   
139.     for i in 1..nvl.count loop   
140.         for j in 1..nvl(i).count loop   
141.             dbms_output.put_line('nvl('||i||','||j||')='||nvl(i,j));   
142.         end loop;   
143.     end loop;   
144. end;     
145. /*********************************************************   
146.                                                        
147.    Author:qinyangzhao                                  
148.    describe:多级(索引表)                               
149.                                                        
150. *********************************************************/     
151. declare  
152.     type al_table_type is table of int  
153.     index by binary_integer;--定义一维table   
154.     type nal_table_type is table of al_table_type   
155.     index by binary_integer;--定义二维table集合       
156.     nvl nal_varrary_type;   
157. begin  
158.     --初始化二维集合变量   
159.     nvl(1)(1):=10;   
160.     nvl(1)(2):=5;   
161.     nvl(2)(1):=32;   
162.     nvl(2)(2):=88;   
163.     dbms_output.put_line('显示二维数组所有元素');   
164.     for i in 1..nvl.count loop   
165.         for j in 1..nvl(i).count loop   
166.             dbms_output.put_line('nvl('||i||','||j||')='||nvl(i,j));   
167.         end loop;   
168.     end loop;   
169. end;                 
170. /*********************************************************   
171.                                                        
172.    Author:qinyangzhao                                  
173.    describe:处理多行查询语句                              
174.                                                        
175. *********************************************************/                     
176. declare  
177.    type empcurtyp is ref cursor;   
178.    emp_cv empcurtyp;   
179.    emp_record emp%rowtype;   
180.    sql_stat varchar2(100);   
181. begin  
182.    sql_stat:='select * from emp where deptno:=dno';   
183.    open emp_cv for sql_stat using &dno;   
184.    loop   
185.        fetch emp_cv into emp_record;   
186.        exit when emp_cv%notfound ;   
187.        dbms_output.put_line('雇员名:'||emp_record.ename||',工资:'||emp_record.sal);   
188.    end loop;   
189.    close emp_cv;   
190. end;   
191. /*********************************************************   
192.                                                        
193.    Author:qinyangzhao                                  
194.    describe:使用bulk子句处理dml语句返回子句                             
195.                                                        
196. *********************************************************/       
197. declare      
198.     type ename_table_type is table of emp.ename%type   
199.       index by binary_integer;   
200.     type sal_table_type is table of emp.sal%type   
201.       index by binary_integer;   
202.       ename_table ename_table_type;   
203.       sal_table sal_table_type;   
204.       sql_stat varchar2(100);   
205. begin  
206.     sql_stat:='update emp set sal=sal*(1+:percent/100)'   
207.            ||'where deptno=:dno'   
208.            ||'returning ename,sal into :name,:salary';   
209.     execute immediate sql_stat using &percen ,&dno returning bulk collect into ename_table,sal_table;   
210.     for i in 1..ename_table.count loop   
211.        dbms_output.put_line('雇员:'||ename_table(i)   
212.              ||',的新工资为'|| sal_table(i));   
213.     end loop;                            
214. end;     
215. /*********************************************************   
216.                                                        
217.    Author:qinyangzhao                                  
218.    describe:使用bulk子句处理多行查询                             
219.                                                        
220. *********************************************************/         
221. declare    
222.     type ename_table_type is table of emp.ename%type   
223.        index by binary_integer;   
224.     ename_table ename_table_type;   
225.     sql_stat varchar2(100);   
226. begin  
227.     sql_stat:='select ename from emp where deptno+:dno';   
228.     execute immediate sql_stat bulk collect into ename_table using &dno;   
229.     for i in 1..ename_table.count loop   
230.         dbms_output.put_line(ename_table(i));   
231.     end loop;       
232. end;   
233. /*********************************************************   
234.                                                        
235.    Author:qinyangzhao                                  
236.    describe:在fetch语句中使用bulk子句                           
237.                                                        
238. *********************************************************/       
239. declare     
240.     type empcurtyp is ref cursor;   
241.     emp_cv empcurtyp ;   
242.     type ename_table_type is table of emp.ename%type   
243.        index by binary_integer;   
244.     ename_table ename_table_type;   
245.     sql_stat varchar2(100);   
246. begin  
247.     sql_stat:='select ename from emp where job:title';   
248.     open emp_cv for sql_stat using '&job';   
249.     fetch emp_cv bulk collect into ename_table;   
250.     for i in 1..ename_table.count loop   
251.       dbms_output.put_line(ename_table(i));   
252.     end loop;   
253.     close emp_cv;   
254. end;               
255.  /*********************************************************   
256.                                                        
257.    Author:qinyangzhao                                  
258.    describe:在forall语句中使用bulk子句                           
259.                                                        
260. *********************************************************/        
261. declare  
262.     type ename_table_type is table of emp.ename%type;   
263.     type sal_table_type is table of emp.sal%type;   
264.     ename_table ename_table_type;   
265.     sal_table sal_table_type;   
266.     sql_stat varchar2(100);   
267. begin  
268.     ename_table:=name_table_type('scott','smith','clark');   
269.     sql_stat:='update emp set sal=sal*1.1 where ename=:1'   
270.            ||'returning sal into :2';   
271.     forall i in 1..ename_talbe.count  
272.         execute immediate sql_stat using ename_table(i)   
273.             returing bulk collect into sal_table ;   
274.     for j in 1..ename_table.count loop   
275.         dbms_output.put_line('雇员'||ename_table(j)          
276.             ||'的新工资为'||sal_table(j));   
277.     end loop;   
278. end;