ORACLE預存程序【轉載】

ORACLE預存程序【轉載】

http://blog.bossma.cn/archives/2008/01/80

1、插入資料：
向一張表裡插入若干條資料

1. /*
2. 建立表tb1
3. 包含兩個欄位col1、col2
4. */
5. create table tb1(
6. col1 char(20),
7. col2 char(20));
8.  
9. /*插入資料的預存程序*/
10. CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST
11. (
12. ST_NUM        IN     NUMBER,/*起始數值*/
13. ED_NUM        IN     NUMBER/*結束數值*/
14. )
15. IS
16. BEGIN
17. declare
18.        i   number;
19. begin
20. /*迴圈插入*/
21. FOR i IN ST_NUM..ED_NUM LOOP
22. INSERT INTO tb1 values(i,'test');
23. END LOOP;
24. end;
25. commit;
26. /*異常處理*/
27. EXCEPTION
28. WHEN OTHERS THEN
29. rollback;
30. END;
31.  
32. /*在CommondLine下執行*/
33. exec INSERTAMOUNTTEST(1,2000);

2、更新資料

1. CREATE OR REPLACE PROCEDURE UPDATEAMOUNTTEST
2. (
3. WHERE_NUM IN VARCHAR2/*SQL語句條件值*/
4. )
5. IS
6. BEGIN
7. UPDATE tb1 SET col2='test2' where col1 like WHERE_NUM||'%';
8. /*異常處理*/
9. EXCEPTION
10. WHEN OTHERS THEN
11. rollback;
12. END;
13.  
14. /*在CommondLine下執行*/
15. exec UPDATEAMOUNTTEST('11');

3、預存程序之間的調用
以一個查詢更新為例子

1. /*
2. 查詢更新預存程序，調用更新預存程序UPDATEAMOUNTTEST2
3. */
4. CREATE OR REPLACE PROCEDURE SELECTUPDATETEST
5. (
6. tJStr IN CHAR
7. )
8. is
9.   upStr CHAR(20);
10. begin
11. /*
12. 查詢出要更新的欄位值
13. */
14. select a.col2 into upStr from tb1 a where a.col1 = tJStr;
15. /*
16. 調用更新預存程序
17. 傳入用於更新的兩個參數
18. */
19. UPDATEAMOUNTTEST2('11',upStr);
20. end SELECTUPDATETEST;
21.  
22. /*
23. 更新用的預存程序，被SELECTUPDATETEST調用
24. */
25. CREATE OR REPLACE PROCEDURE UPDATEAMOUNTTEST2
26. (
27. WHERE_NUM IN VARCHAR2,/*更新條件*/
28. SET_NAME IN VARCHAR2/*更新後的欄位值*/
29. )
30. IS
31. BEGIN
32. UPDATE tb1 SET col2=SET_NAME where col1 like WHERE_NUM||'%';
33. EXCEPTION
34. WHEN OTHERS THEN
35. rollback;
36. END;

在測試的過程中遇到查詢不到值的問題，經確認是字串類型的問題，所以一定要考察欄位的字串類型與傳入的參數類型是否一致。
關於這個問題，下邊這篇文章給與了詳細說明：
[關於資料類型的比較規則(Datatype Comparison Rules)]http://sonic.itpub.net/post/17575/93972

另外ORACLE的關鍵字可在v$reserved_words視圖中查看
select * from v$reserved_words order by keyword