oracle可重复执行脚本写法---总结

工作中经常要写oracle脚本，而且需要重复执行，因此在此总结一下常用的可重复性脚本的写法，以下都是通过举例子说明。

[sql] view plain copy

1. --1、创建表  
2. DECLARE  
3.   VC_STR           VARCHAR2(5000);  
4.   VN_COUNT         NUMBER;  
5. BEGIN  
6.   SELECT COUNT(*) INTO VN_COUNT  
7.     FROM USER_TABLES  
8.    WHERE TABLE_NAME = 'TB_BALANCE';  
9.   --如果没有则新增表，如果有就不处理  
10.   IF VN_COUNT < 1 THEN  
11.     VC_STR := 'create table TB_BALANCE (  
12.           ID              NUMBER not null,  
13.           BILL_TYPE       CHAR(1),  
14.           CORP_SCALE_CODE CHAR(4),  
15.           INDUSTRY_CODE   VARCHAR2(8),       
16.           BALANCE         NUMBER(18,2),  
17.           END_DATE        DATE  
18.         )';  
19.     EXECUTE IMMEDIATE VC_STR;  
20.   END IF;  
21. END;  
22. /  
23.   
24. -- 2增加表字段  
25.   
26. DECLARE  
27.   VN_COUNT     NUMBER;  
28.   V_STR        VARCHAR2(1000);  
29. BEGIN  
30. select COUNT(*)   
31.   INTO VN_COUNT  
32.   from USER_TAB_COLUMNS   
33.  where table_name = 'TB_BALANCE' AND COLUMN_NAME = 'CUR_CODE';  
34. IF VN_COUNT < 1 THEN  
35.   V_STR := ' alter table TB_BALANCEadd(CUR_CODE VARCHAR2(50) default ''01'' not null)';  
36.   EXECUTE IMMEDIATE V_STR;  
37. END IF;  
38. END;  
39. /  
40.   
41. --3、判断表bis_acc_his_bal是否已经建有名为PK_BIS_ACC_HIS_BAL的主键，没有则新增  
42. DECLARE  
43.   VN_COUNT    NUMBER;  
44. BEGIN  
45.   SELECT COUNT(*)  
46.     INTO VN_COUNT  
47.     FROM user_constraints WHERE constraint_type = 'P' and constraint_name = 'PK_BIS_ACC_HIS_BAL' and table_name = 'BIS_ACC_HIS_BAL';  
48.   IF VN_COUNT < 1 THEN  
49.    EXECUTE IMMEDIATE 'alter table bis_acc_his_bal add constraint PK_BIS_ACC_HIS_BAL primary key(BANK_ACC, BAL_DATE)';  
50.   END IF;  
51.   COMMIT;   
52. END;  
53. /  
54. --4、插入数据  
55. DECLARE  
56. VN_COUNT   NUMBER;  
57. BEGIN  
58. SELECT COUNT(*) INTO VN_COUNT FROM TB_BALANCE WHERE  FORMULA_CODE = 'GETTOPDKYE';  
59. IF VN_COUNT <1 THEN  
60. INSERT INTO TB_BALANCE(ID, FORMULA_NAME, HELP, FORMULA_CODE)VALUES  
61. (NVL((SELECT MAX(ID) + 1 FROM RPT1104_REPORT_FORMULA),1),  
62.    'GETTOPDKYE（****）',  
63.    'GETTOPDKYE（*****）:<br>  
64.     参数1:贷款科目的代码<br>  
65.     参数2:数据项，1-10<br>  
66.     参数3:第几列；包括：客户名称，客户代码，最高风险额，贷款余额<br>  
67.     示例:GETTOPDKYE(1303,1,1)<br*********<br>',  
68.    'GETTOPDKYE'  
69.  );  
70. END IF;  
71.    COMMIT;  
72. END;  
73. /  
74. --5、判断表bis_acc_his_bal是否已经建有名为PK_BIS_ACC_HIS_BAL的索引，有则删除  
75. DECLARE  
76.   VN_COUNT    NUMBER;  
77. BEGIN  
78.   SELECT COUNT(*)  
79.     INTO VN_COUNT  
80.     FROM user_indexes WHERE index_name = 'PK_BIS_ACC_HIS_BAL' and table_name = 'BIS_ACC_HIS_BAL';  
81.   IF VN_COUNT >= 1 THEN  
82.    EXECUTE IMMEDIATE 'drop index PK_BIS_ACC_HIS_BAL';  
83.   END IF;  
84.   COMMIT;   
85. END;  
86. /