declare cursor a is SELECT 'CREATE TABLE '||YH||'.'||BM||'('||TO_CHAR(WMSYS.WM_CONCAT(ZD||' '||LX))||')'FROM 设置表 group by YH,BM; str varchar2(4000); begin open a; fetch a into str; while a %found loop EXECUTE IMMEDIATE STR; fetch a into str; end loop; close a; end; 没测试,自己调试下吧
declare cursor a is SELECT 'CREATE TABLE '||YH||'.'||BM||'('||TO_CHAR(WMSYS.WM_CONCAT(ZD||' '||LX))||')'FROM 设置表 group by YH,BM; str varchar2(4000); begin open a; fetch a into str; while a %found loop EXECUTE IMMEDIATE STR; fetch a into str; end loop; close a; end;第8行报错了 无效的数据类型 还有请问大神 列转行 是做什么用的
with t as ( select 'XXDM' ZD, 'varchar(200)' LX, 'XXJBSJXX' BM, 'icdc_xx' YH from dual union all select 'XXMC' ZD, 'varchar(200)' LX, 'XXJBSJXX' BM, 'icdc_xx' YH from dual union all select 'WYHBH' ZD, 'varchar(200)' LX, 'WYHXX' BM, 'icdc_aa' YH from dual union all select 'WYHMC' ZD, 'varchar(200)' LX, 'WYHXX' BM, 'icdc_aa' YH from dual union all select 'WYHJC' ZD, 'varchar(200)' LX, 'ZZMM' BM, 'icdc_bb' YH from dual union all select 'YWMC' ZD, 'varchar(200)' LX, 'ZZMM' BM, 'icdc_bb' YH from dual ) select zdlx BM, YH, 'create table '||YH||'.'||BM||' as ('||substr(sys_connect_by_path(t1.ZDlx, ','),2) ||')' from ( select ZD ||' '||LX zdlx, BM, YH, row_number()over(partition by BM, YH order by BM, YH ) rn from t)t1 where connect_by_isleaf = 1 start with rn=1 connect by rn = prior rn+1 and YH=prior YH
cursor a is SELECT 'CREATE TABLE '||YH||'.'||BM||'('||TO_CHAR(WMSYS.WM_CONCAT(ZD||' '||LX))||')'FROM 设置表 group by YH,BM;
str varchar2(4000);
begin
open a;
fetch a into str;
while a %found loop
EXECUTE IMMEDIATE STR;
fetch a into str;
end loop;
close a;
end;
没测试,自己调试下吧
cursor a is SELECT 'CREATE TABLE '||YH||'.'||BM||'('||TO_CHAR(WMSYS.WM_CONCAT(ZD||' '||LX))||')'FROM 设置表 group by YH,BM;
str varchar2(4000);
begin
open a;
fetch a into str;
while a %found loop
EXECUTE IMMEDIATE STR;
fetch a into str;
end loop;
close a;
end;第8行报错了 无效的数据类型 还有请问大神 列转行 是做什么用的
这行报错?这行不可能出错的啊
你不是在数据库上直接执行是吧?你用的开发工具是啥?
某些开发工具上不支持EXECUTE IMMEDIATE
这行报错?这行不可能出错的啊
你不是在数据库上直接执行是吧?你用的开发工具是啥?
某些开发工具上不支持EXECUTE IMMEDIATE用的是 pl/sql developer EXECUTE IMMEDIATE 执行这个需要权限的问题刚刚解决掉
本题主要用到了WM_CONCAT函数
报错的解决办法是要在EXECUTE IMMEDIATE STR;前后加上begin和end;就可以了.
顺便说一下 涅磐重生NPCS 的方法不错,呵
with t as
(
select 'XXDM' ZD, 'varchar(200)' LX, 'XXJBSJXX' BM, 'icdc_xx' YH from dual union all
select 'XXMC' ZD, 'varchar(200)' LX, 'XXJBSJXX' BM, 'icdc_xx' YH from dual union all
select 'WYHBH' ZD, 'varchar(200)' LX, 'WYHXX' BM, 'icdc_aa' YH from dual union all
select 'WYHMC' ZD, 'varchar(200)' LX, 'WYHXX' BM, 'icdc_aa' YH from dual union all
select 'WYHJC' ZD, 'varchar(200)' LX, 'ZZMM' BM, 'icdc_bb' YH from dual union all
select 'YWMC' ZD, 'varchar(200)' LX, 'ZZMM' BM, 'icdc_bb' YH from dual
)
select zdlx
BM,
YH,
'create table '||YH||'.'||BM||' as ('||substr(sys_connect_by_path(t1.ZDlx, ','),2) ||')'
from
(
select ZD ||' '||LX zdlx,
BM,
YH,
row_number()over(partition by BM, YH order by BM, YH ) rn
from t)t1
where connect_by_isleaf = 1
start with rn=1
connect by rn = prior rn+1
and YH=prior YH