现在要动态创建表和索引,判断如果表不存在就创建,如果表存在就判断索引有无创建,没有就继续创建索引。因为oracle版本是10g的,只能用goto。过程没有报错,调用也没有报错,但是就是不能创建。过程在下面:
CREATE OR REPLACE PROCEDURE AP_MON_ACCT_BALANCE1(V_MON IN VARCHAR2) IS
V_N CHAR(1);
V_M CHAR(1);
V_P NUMBER :=0;
V_I NUMBER;
V_CRE_TAB_SQL VARCHAR2(4000);
V_CRE_IDX_SQL VARCHAR2(4000);
BEGIN
SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(V_MON,'YYYYMM')),'DD')) INTO V_I FROM DUAL;
loop
<<HERE>>
V_P :=V_P+1;
EXIT WHEN V_P>V_I; --判断是否有无超过这个月的最大天数
SELECT 'Y' INTO V_N FROM USER_TABLES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0');
IF V_N IS NOT NULL THEN
SELECT 'Y' INTO V_M FROM USER_INDEXES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0'); --判断索引是否存在
IF V_M IS NOT NULL THEN
GOTO HERE;
ELSE
V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || ' ON ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || '(ACCT_BALANCE_ID)';
EXECUTE IMMEDIATE V_CRE_IDX_SQL;
END IF;
ELSE
V_CRE_TAB_SQL := 'CREATE TABLE ACCT_BALANCE_' || V_MON ||LPAD(V_P, 2, '0') || ' (ACCT_BALANCE_ID NUMBER(9) NOT NULL,
BALANCE_TYPE_ID NUMBER(9) NOT NULL,
ACCT_ID VARCHAR2(30) NOT NULL,
EFF_DATE DATE NOT NULL,
EXP_DATE DATE,
BALANCE NUMBER(12) NOT NULL,
RESERVE_BALANCE NUMBER(12) NOT NULL,
CYCLE_UPPER NUMBER(12),
CYCLE_LOWER NUMBER(12),
CYCLE_UPPER_TYPE VARCHAR2(3) NOT NULL,
CYCLE_LOWER_TYPE VARCHAR2(3) NOT NULL,
BANK_ACCT VARCHAR2(30),
STATE VARCHAR2(3) NOT NULL,
STATE_DATE DATE NOT NULL,
REGION NUMBER(5) NOT NULL,
SUBS_ID VARCHAR2(30),
AUDITCYCLE NUMBER(8),
OWE_AMT NUMBER(12),
CYCLE_MATCH_TYPE NUMBER(1))';
EXECUTE IMMEDIATE V_CRE_TAB_SQL;
V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || ' ON ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || '(ACCT_BALANCE_ID)';
EXECUTE IMMEDIATE V_CRE_IDX_SQL;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
帮帮忙,看看。
CREATE OR REPLACE PROCEDURE AP_MON_ACCT_BALANCE1(V_MON IN VARCHAR2) IS
V_N CHAR(1);
V_M CHAR(1);
V_P NUMBER :=0;
V_I NUMBER;
V_CRE_TAB_SQL VARCHAR2(4000);
V_CRE_IDX_SQL VARCHAR2(4000);
BEGIN
SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(V_MON,'YYYYMM')),'DD')) INTO V_I FROM DUAL;
loop
<<HERE>>
V_P :=V_P+1;
EXIT WHEN V_P>V_I; --判断是否有无超过这个月的最大天数
SELECT 'Y' INTO V_N FROM USER_TABLES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0');
IF V_N IS NOT NULL THEN
SELECT 'Y' INTO V_M FROM USER_INDEXES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0'); --判断索引是否存在
IF V_M IS NOT NULL THEN
GOTO HERE;
ELSE
V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || ' ON ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || '(ACCT_BALANCE_ID)';
EXECUTE IMMEDIATE V_CRE_IDX_SQL;
END IF;
ELSE
V_CRE_TAB_SQL := 'CREATE TABLE ACCT_BALANCE_' || V_MON ||LPAD(V_P, 2, '0') || ' (ACCT_BALANCE_ID NUMBER(9) NOT NULL,
BALANCE_TYPE_ID NUMBER(9) NOT NULL,
ACCT_ID VARCHAR2(30) NOT NULL,
EFF_DATE DATE NOT NULL,
EXP_DATE DATE,
BALANCE NUMBER(12) NOT NULL,
RESERVE_BALANCE NUMBER(12) NOT NULL,
CYCLE_UPPER NUMBER(12),
CYCLE_LOWER NUMBER(12),
CYCLE_UPPER_TYPE VARCHAR2(3) NOT NULL,
CYCLE_LOWER_TYPE VARCHAR2(3) NOT NULL,
BANK_ACCT VARCHAR2(30),
STATE VARCHAR2(3) NOT NULL,
STATE_DATE DATE NOT NULL,
REGION NUMBER(5) NOT NULL,
SUBS_ID VARCHAR2(30),
AUDITCYCLE NUMBER(8),
OWE_AMT NUMBER(12),
CYCLE_MATCH_TYPE NUMBER(1))';
EXECUTE IMMEDIATE V_CRE_TAB_SQL;
V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || ' ON ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || '(ACCT_BALANCE_ID)';
EXECUTE IMMEDIATE V_CRE_IDX_SQL;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
帮帮忙,看看。
CREATE OR REPLACE PROCEDURE AP_MON_ACCT_BALANCE1(V_MON IN VARCHAR2) IS
V_N CHAR(1);
V_M CHAR(1);
V_P NUMBER :=0;
V_I NUMBER;
V_CRE_TAB_SQL VARCHAR2(4000);
V_CRE_IDX_SQL VARCHAR2(4000);
BEGIN
SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(V_MON,'YYYYMM')),'DD')) INTO V_I FROM DUAL;
loop
<<HERE>>
V_P :=V_P+1;
EXIT WHEN V_P>V_I; --判断是否有无超过这个月的最大天数
SELECT 'Y' INTO V_N FROM USER_TABLES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0');--当没有数据时会报错,no data found.
--上面这句改成判断count(*)是否为0 IF V_N IS NOT NULL THEN
SELECT 'Y' INTO V_M FROM USER_INDEXES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0'); --判断索引是否存在
--上举同上修改
IF V_M IS NOT NULL THEN
GOTO HERE;
ELSE
V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || ' ON ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || '(ACCT_BALANCE_ID)';
EXECUTE IMMEDIATE V_CRE_IDX_SQL;
END IF;
ELSE
V_CRE_TAB_SQL := 'CREATE TABLE ACCT_BALANCE_' || V_MON ||LPAD(V_P, 2, '0') || ' (ACCT_BALANCE_ID NUMBER(9) NOT NULL,
BALANCE_TYPE_ID NUMBER(9) NOT NULL,
ACCT_ID VARCHAR2(30) NOT NULL,
EFF_DATE DATE NOT NULL,
EXP_DATE DATE,
BALANCE NUMBER(12) NOT NULL,
RESERVE_BALANCE NUMBER(12) NOT NULL,
CYCLE_UPPER NUMBER(12),
CYCLE_LOWER NUMBER(12),
CYCLE_UPPER_TYPE VARCHAR2(3) NOT NULL,
CYCLE_LOWER_TYPE VARCHAR2(3) NOT NULL,
BANK_ACCT VARCHAR2(30),
STATE VARCHAR2(3) NOT NULL,
STATE_DATE DATE NOT NULL,
REGION NUMBER(5) NOT NULL,
SUBS_ID VARCHAR2(30),
AUDITCYCLE NUMBER(8),
OWE_AMT NUMBER(12),
CYCLE_MATCH_TYPE NUMBER(1))';
EXECUTE IMMEDIATE V_CRE_TAB_SQL;
V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || ' ON ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || '(ACCT_BALANCE_ID)';
EXECUTE IMMEDIATE V_CRE_IDX_SQL;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
IF V_N IS NOT NULL THEN
SELECT 'Y' INTO V_M FROM USER_INDEXES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0'); --判断索引是否存在
IF V_M IS NOT NULL THEN感觉你的这两个判断不太合理啊 如果没有找到表这样会直接到exception那
应该是定义两个number类型 然后select count(*) into 变量
if 变量>0 then ....
--用个例子来说明你的判断语句
--1.如果查询结果为null
SQL> declare
2 v_flag varchar2(2);
3 begin
4 select 'F' into v_flag from emp where 1=2;
5 dbms_output.put_line(v_flag);
6 end;
7 /
declare
*
第 1 行出现错误:
ORA-01403: 未找到数据
ORA-06512: 在 line 4
SQL> ed
已写入 file afiedt.buf 1 declare
2 v_flag varchar2(2);
3 begin
4 select 'F' into v_flag from emp where 1=2;
5 dbms_output.put_line(v_flag);
6 exception when others then --加了exception编译通过了
7 null;
8* end;
SQL> /PL/SQL 过程已成功完成。--查询结果为多行
SQL> ed
已写入 file afiedt.buf 1 declare
2 v_flag varchar2(2);
3 begin
4 select 'F' into v_flag from emp where 1=1;
5 dbms_output.put_line(v_flag);
6 --exception when others then
7 null;
8* end;
SQL> /
declare
*
第 1 行出现错误:
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在 line 4
SQL> ed
已写入 file afiedt.buf 1 declare
2 v_flag varchar2(2);
3 begin
4 select 'F' into v_flag from emp where 1=1;
5 dbms_output.put_line(v_flag);
6 exception when others then --同样加了exception通过了
7 null;
8* end;
SQL> /PL/SQL 过程已成功完成。SQL> ed
已写入 file afiedt.buf 1 declare
2 v_flag varchar2(2);
3 begin
4 select 'F' into v_flag from emp where empno=7369;
5 dbms_output.put_line(v_flag);
6 --exception when others then
7 --null;
8* end;
SQL> /
FPL/SQL 过程已成功完成。
2 V_N CHAR(1);
3 V_M CHAR(1);
4 V_P NUMBER :=0;
5 V_I NUMBER;
6 V_CRE_TAB_SQL VARCHAR2(4000);
7 V_CRE_IDX_SQL VARCHAR2(4000);
8 v_num number;
9 v_mess varchar2(4000);
10 BEGIN
11 SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(V_MON,'YYYYMM')),'DD')) INTO V_I FROM DUAL;
12 loop
13 <<HERE>>
14 V_P :=V_P+1;
15 EXIT WHEN V_P>V_I; --判断是否有无超过这个月的最大天数
16 SELECT count(*) INTO V_Num FROM USER_TABLES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0');
17
18 IF V_Num =1 THEN
19 SELECT count(*) INTO V_num FROM USER_INDEXES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0'); --判断索引是否存在
20 IF V_num=1 THEN
21 GOTO HERE;
22 ELSE
23 V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE_' || V_MON ||
24 LPAD(V_P, 2, '0') || ' ON ACCT_BALANCE_' || V_MON ||
25 LPAD(V_P, 2, '0') || '(ACCT_BALANCE_ID)';
26 EXECUTE IMMEDIATE V_CRE_IDX_SQL;
27 END IF;
28 ELSE
29 V_CRE_TAB_SQL := 'CREATE TABLE ACCT_BALANCE_' || V_MON ||LPAD(V_P, 2, '0') || ' (ACCT_BALANCE_ID NUMBER(9) NOT NULL,
30 BALANCE_TYPE_ID NUMBER(9) NOT NULL,
31 ACCT_ID VARCHAR2(30) NOT NULL,
32 EFF_DATE DATE NOT NULL,
33 EXP_DATE DATE,
34 BALANCE NUMBER(12) NOT NULL,
35 RESERVE_BALANCE NUMBER(12) NOT NULL,
36 CYCLE_UPPER NUMBER(12),
37 CYCLE_LOWER NUMBER(12),
38 CYCLE_UPPER_TYPE VARCHAR2(3) NOT NULL,
39 CYCLE_LOWER_TYPE VARCHAR2(3) NOT NULL,
40 BANK_ACCT VARCHAR2(30),
41 STATE VARCHAR2(3) NOT NULL,
42 STATE_DATE DATE NOT NULL,
43 REGION NUMBER(5) NOT NULL,
44 SUBS_ID VARCHAR2(30),
45 AUDITCYCLE NUMBER(8),
46 OWE_AMT NUMBER(12),
47 CYCLE_MATCH_TYPE NUMBER(1))';
48 EXECUTE IMMEDIATE V_CRE_TAB_SQL;
49 V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE_' || V_MON ||
50 LPAD(V_P, 2, '0') || ' ON ACCT_BALANCE_' || V_MON ||
51 LPAD(V_P, 2, '0') || '(ACCT_BALANCE_ID)';
52 EXECUTE IMMEDIATE V_CRE_IDX_SQL;
53 END IF;
54 END LOOP;
55 EXCEPTION
56 WHEN OTHERS THEN
57 v_mess:=sqlerrm;
58
59 END;
60 /
Procedure created
SQL>
TABLE_NAME
------------------------------
ACCT_BALANCE_20100101
ACCT_BALANCE_20100102
ACCT_BALANCE_20100103
ACCT_BALANCE_20100104
ACCT_BALANCE_20100105
ACCT_BALANCE_20100106
ACCT_BALANCE_20100107
ACCT_BALANCE_20100108
ACCT_BALANCE_20100109
ACCT_BALANCE_20100110
ACCT_BALANCE_20100111
ACCT_BALANCE_20100112
ACCT_BALANCE_20100113
ACCT_BALANCE_20100114
ACCT_BALANCE_20100115
ACCT_BALANCE_20100116
ACCT_BALANCE_20100117
ACCT_BALANCE_20100118
ACCT_BALANCE_20100119
ACCT_BALANCE_20100120
TABLE_NAME
------------------------------
ACCT_BALANCE_20100121
ACCT_BALANCE_20100122
ACCT_BALANCE_20100123
ACCT_BALANCE_20100124
ACCT_BALANCE_20100125
ACCT_BALANCE_20100126
ACCT_BALANCE_20100127
ACCT_BALANCE_20100128
ACCT_BALANCE_20100129
ACCT_BALANCE_20100130
ACCT_BALANCE_20100131
31 rows selected
SQL>
grant create any table to username;
grant create any index to username;
grant create any table to username;
grant create any index to username;
赋权限。
grant create any table to username和grant create table to username 有什么区别啊!
官方文档如下:
CREATE TABLE
Create tables in the grantee's schema.
CREATE ANY TABLE
Create tables in any schema. The owner of the schema containing the table must have space quota on the tablespace to contain the table.
WHEN OTHERS THEN NULL;-----运行不返回错误
END;
---用count(*) into 变量值的 来判断
CREATE OR REPLACE PROCEDURE AP_MON_ACCT_BALANCE1(V_MON IN VARCHAR2) IS
V_N number;
V_M number;
V_P NUMBER :=0;
V_I NUMBER;
V_CRE_TAB_SQL VARCHAR2(4000);
V_CRE_IDX_SQL VARCHAR2(4000);
BEGIN
SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(V_MON,'YYYYMM')),'DD')) INTO V_I FROM DUAL;
loop
<<HERE>>
V_P :=V_P+1;
EXIT WHEN V_P>V_I; --判断是否有无超过这个月的最大天数
SELECT count(*) INTO V_N FROM USER_TABLES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0');
--dbms_output.put_line('V_N:'||V_N);
IF V_N>0 THEN
SELECT count(*) INTO V_M FROM USER_INDEXES WHERE TABLE_NAME='ACCT_BALANCE_'||V_MON||LPAD(V_P, 2,'0'); --判断索引是否存在
--dbms_output.put_line('V_m:'||V_M);
IF V_M>0 THEN
GOTO HERE;
ELSE
V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || ' ON ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || '(ACCT_BALANCE_ID)';
EXECUTE IMMEDIATE V_CRE_IDX_SQL;
END IF;
ELSE
V_CRE_TAB_SQL := 'CREATE TABLE ACCT_BALANCE_' || V_MON ||LPAD(V_P, 2, '0') || ' (ACCT_BALANCE_ID NUMBER(9) NOT NULL,
BALANCE_TYPE_ID NUMBER(9) NOT NULL,
ACCT_ID VARCHAR2(30) NOT NULL,
EFF_DATE DATE NOT NULL,
EXP_DATE DATE,
BALANCE NUMBER(12) NOT NULL,
RESERVE_BALANCE NUMBER(12) NOT NULL,
CYCLE_UPPER NUMBER(12),
CYCLE_LOWER NUMBER(12),
CYCLE_UPPER_TYPE VARCHAR2(3) NOT NULL,
CYCLE_LOWER_TYPE VARCHAR2(3) NOT NULL,
BANK_ACCT VARCHAR2(30),
STATE VARCHAR2(3) NOT NULL,
STATE_DATE DATE NOT NULL,
REGION NUMBER(5) NOT NULL,
SUBS_ID VARCHAR2(30),
AUDITCYCLE NUMBER(8),
OWE_AMT NUMBER(12),
CYCLE_MATCH_TYPE NUMBER(1))';
EXECUTE IMMEDIATE V_CRE_TAB_SQL;
V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || ' ON ACCT_BALANCE_' || V_MON ||
LPAD(V_P, 2, '0') || '(ACCT_BALANCE_ID)';
EXECUTE IMMEDIATE V_CRE_IDX_SQL;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||' '||sqlerrm);
END;