现在要动态创建表和索引,判断如果表不存在就创建,如果表存在就判断索引有无创建,没有就继续创建索引。因为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;
帮帮忙,看看。

解决方案 »

  1.   

    判断表和index是否存在的语句有问题.
    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;
      

  2.   

    没用,这个试过,也是没用的一开始就是用count(1) 去判断的。。还是不能创建!
      

  3.   

    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感觉你的这两个判断不太合理啊 如果没有找到表这样会直接到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 过程已成功完成。
      

  4.   

    SQL> CREATE OR REPLACE PROCEDURE AP_MON_ACCT_BALANCE1(V_MON IN VARCHAR2) IS
      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> 
      

  5.   

    SQL> select table_name from user_tables t where t.table_name like 'ACCT%';
     
    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> 
      

  6.   

    记得用sys给用户赋予权限
    grant create any table to username;
    grant create any index to username;
      

  7.   

    嗯,但是使用count(1)去判断也是不能创建成功,不知道什么原因啊
      

  8.   

    晕死啊果然是没有
    grant create any table to username;
    grant create any index to username;
    赋权限。
    grant create any table to username和grant create table to username 有什么区别啊!
      

  9.   

    区别还是有的,create table权限只能在本schema内建表,而any table是可以跨schema的。
    官方文档如下:
    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.
      

  10.   

    关键是错在这里EXCEPTION
      WHEN OTHERS THEN NULL;-----运行不返回错误
     END;
      

  11.   


    ---用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;