PL/SQL编程（高级特点）

PL/SQL编程（高级特性）

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