存储过程生成表

/* Formatted on 2007/11/07 11:33 (Formatter Plus v4.8.6) */
CREATE OR REPLACE PROCEDURE p_kalon_create_table (tableno IN VARCHAR2)
IS
   tmpvar      NUMBER;
   createsql   VARCHAR2 (4000);
   flag        NUMBER (1);
   tmpsql      VARCHAR2 (3000);
   tablename   VARCHAR2 (30);
BEGIN
   tablename := UPPER ('kalon_' || SUBSTR (tableno, 1, 6));
   createsql :=
         'create table '
      || tablename
      || '(id number,name varchar2(10)) '
      || 'TABLESPACE TEST_AREA
					PCTUSED    0
					PCTFREE    10
					INITRANS   1
					MAXTRANS   255
					STORAGE    (
					            INITIAL          1M
					            NEXT             1M
					            MINEXTENTS       1
					            MAXEXTENTS       2147483645
					            PCTINCREASE      0
					            BUFFER_POOL      DEFAULT
					           )
					LOGGING 
					NOCOMPRESS 
					NOCACHE
					NOPARALLEL
					NOMONITORING';
   flag := 0;
   tmpsql :=
         'select count(*) from dual '
      || 'where exists(select 1 from user_objects '
      || 'where object_name='''
      || tablename
      --|| 'where object_name=''' || 'upper(' || tablename || ')'''
      || ''')';

   EXECUTE IMMEDIATE tmpsql
                INTO flag;

   COMMIT;

   IF flag = 0
   THEN
      EXECUTE IMMEDIATE createsql;
	  COMMIT;
   END IF;

   --/*下面的这条语句当不存在查询的表时将抛出 NO_DATA_FOUND异常--
   --tmpsql := 'select 1 from dual where exists (select 1 from user_objects where object_name='''
     --        || 'KALON'')';
   --flag := 0;
   --execute immediate tmpsql into flag;
   --if flag <> 1 then
        -- execute immediate createSQL;
    --else
      --DBMS_OUTPUT.put_line ('...exist...');
    --end if;
   
--/*测试脚本，呵呵~~-------------------------------------------------------------------------
 --exec p_kalon_create_table('20070606');

   --create table kalon(idno number);

   --drop table KALON_200706;

   --select * from kalon;

   --select * from user_objects;

   --select 1 from dual where exists (select 1 from user_objects where object_name='KALON_200706');
--/*-----------------------------------------------------------------------------------------
   tmpvar := 0;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('...xxx....');
      ROLLBACK;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('....yyy...');
      RAISE;
-- Consider logging the error and then re-raise
END p_kalon_create_table;
/