存储过程由结构表生成表

结构表

CREATE TABLE JGTB5001
(
ZDM VARCHAR2(30 BYTE),
HZM VARCHAR2(100 BYTE),
LX VARCHAR2(50 BYTE),
JD VARCHAR2(20 BYTE),
WBKLX VARCHAR2(100 BYTE),
FUNCTIONNAME VARCHAR2(50 BYTE),
FUNCTIONPARAMETER VARCHAR2(50 BYTE)
);


生成的TB表
CREATE
OR REPLACE PROCEDURE PROC_CREATE_TB authid current_user as -----声明变量 sqlstr varchar2(4000);--插入语句 begin declare --- 定义 --类型定义 begin select 'create'||' table '||' TB5001 (' ||wmsys.wm_concat(zdm||' '||decode( lx,'number','number('||decode(jd,'0','20','20,'||jd)||')','date','date','varchar2('||jd||')'))||')' INTO sqlstr from JGTB5001; execute immediate sqlstr; select 'create'||' table '||' TB5002 (' ||wmsys.wm_concat(zdm||' '||decode( lx,'number','number('||decode(jd,'0','20','20,'||jd)||')','date','date','varchar2('||jd||')'))||')' INTO sqlstr from JGTB5002; execute immediate sqlstr; select 'create'||' table '||' TB5003 (' ||wmsys.wm_concat(zdm||' '||decode( lx,'number','number('||decode(jd,'0','20','20,'||jd)||')','date','date','varchar2('||jd||')'))||')' INTO sqlstr from JGTB5003; execute immediate sqlstr; select 'create'||' table '||' TB5004 (' ||wmsys.wm_concat(zdm||' '||decode( lx,'number','number('||decode(jd,'0','20','20,'||jd)||')','date','date','varchar2('||jd||')'))||')' INTO sqlstr from JGTB5004; execute immediate sqlstr; select 'create'||' table '||' TB5005 (' ||wmsys.wm_concat(zdm||' '||decode( lx,'number','number('||decode(jd,'0','20','20,'||jd)||')','date','date','varchar2('||jd||')'))||')' INTO sqlstr from JGTB5005; execute immediate sqlstr; select 'create'||' table '||' TB5006 (' ||wmsys.wm_concat(zdm||' '||decode( lx,'number','number('||decode(jd,'0','20','20,'||jd)||')','date','date','varchar2('||jd||')'))||')' INTO sqlstr from JGTB5006; execute immediate sqlstr; end; commit; end PROC_CREATE_TB; /

 

posted @ 2015-07-10 11:43  韩梦芫  阅读(414)  评论(0编辑  收藏  举报