存储过程由结构表生成表

最新推荐文章于 2022-11-26 20:30:55 发布

dianjin3567

最新推荐文章于 2022-11-26 20:30:55 发布

阅读量72

点赞数

原文链接：http://www.cnblogs.com/siyunianhua/p/4635322.html

版权

结构表

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