动态创建表与索引

Question

CREATE OR REPLACE PROCEDURE P_CREATE_TAB(V_DATE IN char) AS
  --V_DATE格式:201012
  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_DATE, 'YYYYMM')), 'DD')) INTO V_I
      FROM DUAL;
  FOR I IN 1 .. V_I LOOP
    V_CRE_TAB_SQL := 'CREATE TABLE ACCT_BALANCE' || V_DATE ||
                     LPAD(I, 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;
  END LOOP;  FOR I IN 1 .. V_I LOOP
  V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE'||V_DATE ||
  LPAD(I, 2, '0') || ' ' || 'ON ACCT_BALANCE_' || V_DATE ||LPAD(I, 2, '0') || '(ACCT_BALANCE_ID)';
  EXECUTE IMMEDIATE V_CRE_IDX_SQL;
  END LOOP;
  END;会报这个错：
ORA-06550: 第 2 行, 第 4 列: 
PLS-00201: 必须声明标识符 'P_CREATE_TAB'
ORA-06550: 第 2 行, 第 4 列: 
PL/SQL: Statement ignored
请高手帮帮忙！

Answer 1

-- 完全复制你的
SQL> CREATE OR REPLACE PROCEDURE P_CREATE_TAB(V_DATE IN char) AS
  2    --V_DATE格式:201012
  3    V_I NUMBER;
  4    V_CRE_TAB_SQL VARCHAR2(4000);
  5    V_CRE_IDX_SQL VARCHAR2(4000);
  6  BEGIN
  7    SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(V_DATE, 'YYYYMM')), 'DD')) INTO V_I
  8    FROM DUAL;
  9    FOR I IN 1 .. V_I LOOP
 10    V_CRE_TAB_SQL := 'CREATE TABLE ACCT_BALANCE' || V_DATE ||
 11    LPAD(I, 2, '0') || ' ' ||
 12    '(ACCT_BALANCE_ID NUMBER(9) NOT NULL,
 13    BALANCE_TYPE_ID NUMBER(9) NOT NULL,
 14    ACCT_ID VARCHAR2(30) NOT NULL,
 15    EFF_DATE DATE NOT NULL,
 16    EXP_DATE DATE,
 17    BALANCE NUMBER(12) NOT NULL,
 18    RESERVE_BALANCE NUMBER(12) NOT NULL,
 19    CYCLE_UPPER NUMBER(12),
 20    CYCLE_LOWER NUMBER(12),
 21    CYCLE_UPPER_TYPE VARCHAR2(3) NOT NULL,
 22    CYCLE_LOWER_TYPE VARCHAR2(3) NOT NULL,
 23    BANK_ACCT VARCHAR2(30),
 24    STATE VARCHAR2(3) NOT NULL,
 25    STATE_DATE DATE NOT NULL,
 26    REGION NUMBER(5) NOT NULL,
 27    SUBS_ID VARCHAR2(30),
 28    AUDITCYCLE NUMBER(8),
 29    OWE_AMT NUMBER(12),
 30    CYCLE_MATCH_TYPE NUMBER(1))';
 31    EXECUTE IMMEDIATE V_CRE_TAB_SQL;
 32    END LOOP;
 33  
 34    FOR I IN 1 .. V_I LOOP
 35    V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE'||V_DATE ||
 36    LPAD(I, 2, '0') || ' ' || 'ON ACCT_BALANCE_' || V_DATE ||LPAD(I, 2, '0') || '(ACCT_BALANCE_ID
)';
 37    EXECUTE IMMEDIATE V_CRE_IDX_SQL;
 38    END LOOP;
 39    END;
 40  /过程已创建。

Answer 2

调用创建的时候会错误。我用这个简单的例子可以创建成功的：
create or replace procedure create_tab
as
 sql_t varchar2(4000);
 sql_v varchar2(4000);
  begin 
   for i in 1..2 loop
    sql_t :='create table table'||lpad(i,2,'0')||' '||'(id number,name char(10))';
    execute immediate sql_t;
   end loop;
   for i in 1..2 loop
    sql_v :='create index  table_ind'||lpad(i,2,'0')||' '||'on table'||lpad(i,2,'0')||'(id)';
    execute immediate sql_v;
    end loop;
 end;

Answer 3

begin
p_create_tab('201010');
end;

Answer 4

--原因：你过程中地表不一样，创建的表和味该表建唯一索引不一致，少了下划线！
CREATE OR REPLACE PROCEDURE P_CREATE_TAB(V_DATE IN char) AS
  --V_DATE格式:201012
  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_DATE, 'YYYYMM')), 'DD')) INTO V_I FROM DUAL;
  FOR I IN 1 .. V_I LOOP
  V_CRE_TAB_SQL := 'CREATE TABLE ACCT_BALANCE' || V_DATE ||
  LPAD(I, 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;
  END LOOP;
  Dbms_Output.PUT_LINE('  OK ');
  FOR I IN 1 .. V_I LOOP
  --原因：你这里给表创建唯一索引不对，表不是你上面的那些表，没有下划线！
  V_CRE_IDX_SQL := 'CREATE UNIQUE INDEX PK_ACCT_BALANCE'||V_DATE ||
  LPAD(I, 2, '0') || ' ' || 'ON ACCT_BALANCE' || V_DATE ||LPAD(I, 2, '0') || '(ACCT_BALANCE_ID)';
  
  Dbms_Output.PUT_LINE(V_CRE_IDX_SQL);
  EXECUTE IMMEDIATE V_CRE_IDX_SQL;
  END LOOP;
  END;

Answer 5

找到，建索引的动态多了一横。'ON ACCT_BALANCE_'