有一表TB_A
结构如下:id m1 m2 m3
--------------------------
001 100 200 300
002 12 22 32
003 22 35 56
...希望的结果: 001 002 003 .......
--------------------------
m1 100 12 22
m2 200 22 35
m3 300 32 56
...列数固定,行数不定,怎么写这个SQL,求教大神,谢谢
结构如下:id m1 m2 m3
--------------------------
001 100 200 300
002 12 22 32
003 22 35 56
...希望的结果: 001 002 003 .......
--------------------------
m1 100 12 22
m2 200 22 35
m3 300 32 56
...列数固定,行数不定,怎么写这个SQL,求教大神,谢谢
--创建测试表
CREATE TABLE TB_A(ID NVARCHAR2(10),M1 NUMBER,M2 NUMBER,M3 NUMBER);--插入测试数据
INSERT INTO TB_A VALUES ('001',100,200,300);
INSERT INTO TB_A VALUES ('002',12,22,32);
INSERT INTO TB_A VALUES ('003',22,35,56);
COMMIT;--创建存储过程
CREATE OR REPLACE PROCEDURE TEST
IS
V_SQL NVARCHAR2(2000);
CURSOR CURSOR_1 IS
select column_name cm
from user_tab_columns
where table_name = 'TB_A' and column_name <> 'ID';
CURSOR CURSOR_2 IS
SELECT ID FROM TB_A; BEGIN
FOR C_TB IN CURSOR_1
LOOP
V_SQL := V_SQL||' SELECT '''||C_TB.CM||''' AS " "';
FOR V_TB IN CURSOR_2
LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(A.ID,'''
|| V_TB.ID || ''', '||C_TB.CM||',0)) AS "'|| V_TB.ID ||'"';
END LOOP;
V_SQL := V_SQL || ' FROM TB_A A ' ;
V_SQL := V_SQL || ' UNION ALL';
END LOOP;
V_SQL := SUBSTR(V_SQL,1,LENGTH(V_SQL)-10);
DBMS_OUTPUT.PUT_LINE(V_SQL);
--EXECUTE IMMEDIATE V_SQL;
END;--执行存储过程 返回sql 查询数据 001 002 003
--------------------------------------
1 M1 100 12 22
2 M2 200 22 35
3 M3 300 32 56--继续插入2行测试数据
INSERT INTO TB_A VALUES ('004',33,44,55);
INSERT INTO TB_A VALUES ('005',35,45,65);
COMMIT;--执行查询 返回结果
001 002 003 004 005
-----------------------------------------------
1 M1 100 12 22 33 35
2 M2 200 22 35 44 45
3 M3 300 32 56 55 65
--删除测试表和存储过程
DROP TABLE TB_A;
DROP PROCEDURE TEST;
我的实际的表中有70多个字段,用您的方法执行到 DBMS_OUTPUT.PUT_LINE(V_SQL);这句时出现如下错误:ORA-20000:ORU-10027: buffer overflow, limit of 10000 bytes
这个问题也在网上查了一下,大都是用set serveroutput on size 100000的方法,我执行后错误依旧,再请指教,非常感谢
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,1,1000));
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,1000,1000));
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,2000,1000));
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,3000,1000));
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,4000,1000));
前提要判断 sql长度是多少 然后根据长度来打印sql 然后不能在关键位置截断 判断空格部分
CREATE OR REPLACE PROCEDURE TEST
IS
V_SQL NVARCHAR2(8000);
V_COUNT NUMBER(10);
V_BEGIN NUMBER(10);
V_END NUMBER(10);
CURSOR CURSOR_1 IS
select column_name cm
from user_tab_columns
where table_name = 'TB_A' and column_name <> 'ID';
CURSOR CURSOR_2 IS
SELECT ID FROM TB_A; BEGIN
FOR C_TB IN CURSOR_1
LOOP
V_SQL := V_SQL||' SELECT '''||C_TB.CM||''' AS " "';
FOR V_TB IN CURSOR_2
LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(A.ID,'''
|| V_TB.ID || ''', '||C_TB.CM||',0)) AS "'|| V_TB.ID ||'"';
END LOOP;
V_SQL := V_SQL || ' FROM TB_A A ' ;
V_SQL := V_SQL || ' UNION ALL';
END LOOP;
V_SQL := SUBSTR(V_SQL,1,LENGTH(V_SQL)-10);
V_COUNT := regexp_count(V_SQL,'UNION ALL');
V_BEGIN := 1;
FOR I IN 1..V_COUNT LOOP
V_END := INSTR(V_SQL,'UNION ALL',1,I);
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,V_BEGIN,V_END-V_BEGIN));
V_BEGIN := V_END;
END LOOP;
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL,V_END,LENGTH(V_SQL)-V_END));
END;
表中数据:
执行存储 返回sql 查询的数据
create table TB_V2_DBINFO
(
JG_ID NVARCHAR2(20) not null,
MUSER NVARCHAR2(60),
M1 NVARCHAR2(50),
M2 NVARCHAR2(50),
M3 NVARCHAR2(50),
M4 NVARCHAR2(50),
M5 NVARCHAR2(50),
M6 NVARCHAR2(50),
M7 NVARCHAR2(50),
M8 NVARCHAR2(50),
M9 NVARCHAR2(50),
M10 NVARCHAR2(50),
M11 NVARCHAR2(50),
M12 NVARCHAR2(50),
M13 NVARCHAR2(50),
M14 NVARCHAR2(50),
M15 NVARCHAR2(50),
M16 NVARCHAR2(50),
M17 NVARCHAR2(50),
M18 NVARCHAR2(50),
M19 NVARCHAR2(50),
M20 NVARCHAR2(50),
M21 NVARCHAR2(50),
M22 NVARCHAR2(50),
M23 NVARCHAR2(50),
M24 NVARCHAR2(50),
M25 NVARCHAR2(50),
M26 NVARCHAR2(50),
M27 NVARCHAR2(50),
M28 NVARCHAR2(50),
M29 NVARCHAR2(50),
M30 NVARCHAR2(50),
M31 NVARCHAR2(50),
M32 NVARCHAR2(50),
M33 NVARCHAR2(50),
M34 NVARCHAR2(50),
M35 NVARCHAR2(50),
M36 NVARCHAR2(50),
M37 NVARCHAR2(50),
M38 NVARCHAR2(50),
M39 NVARCHAR2(50),
M40 NVARCHAR2(50),
M41 NVARCHAR2(50),
M42 NVARCHAR2(50),
M43 NVARCHAR2(50),
M44 NVARCHAR2(50),
M45 NVARCHAR2(50),
M46 NVARCHAR2(50),
M47 NVARCHAR2(50),
M48 NVARCHAR2(50),
M49 NVARCHAR2(50),
M50 NVARCHAR2(50),
M51 NVARCHAR2(50),
M52 NVARCHAR2(50),
M53 NVARCHAR2(50),
M54 NVARCHAR2(50),
M55 NVARCHAR2(50),
M56 NVARCHAR2(50),
M57 NVARCHAR2(50),
M58 NVARCHAR2(50),
M59 NVARCHAR2(50),
M60 NVARCHAR2(50),
M61 NVARCHAR2(50),
M62 NVARCHAR2(50),
M63 NVARCHAR2(50),
M64 NVARCHAR2(50),
M65 NVARCHAR2(50),
M66 NVARCHAR2(50),
M67 NVARCHAR2(50),
M68 NVARCHAR2(50),
M69 NVARCHAR2(50),
M70 NVARCHAR2(50),
M71 NVARCHAR2(50),
M72 NVARCHAR2(50),
M73 NVARCHAR2(50),
M74 NVARCHAR2(50),
M75 NVARCHAR2(50),
M76 NVARCHAR2(50),
M77 NVARCHAR2(50),
M78 NVARCHAR2(50),
M79 NVARCHAR2(50),
M80 NVARCHAR2(50)
)
现在我改了下您的第29行为 V_COUNT := LENGTH(REPLACE(V_SQL, 'UNION ALL', ' '));
您的存储过程我改为如下: CREATE OR REPLACE PROCEDURE TEST3 IS
V_SQL NVARCHAR2(28000);
V_COUNT NUMBER(10);
V_BEGIN NUMBER(10);
V_END NUMBER(10); CURSOR CURSOR_1 IS
select column_name cm
from user_tab_columns
where table_name = 'TB_V2_DBINFO'
and column_name <> 'ZZJG_ID'
and column_name <> 'MUSER'
and column_name <> 'UPTIME'
ORDER BY to_number(substr(cm, 2)); CURSOR CURSOR_2 IS
SELECT ZZJG_ID FROM TB_V2_DBINFO;BEGIN
FOR C_TB IN CURSOR_1 LOOP
V_SQL := V_SQL || ' SELECT ''' || C_TB.CM || ''' AS " "';
FOR V_TB IN CURSOR_2 LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(A.ZZJG_ID,''' || V_TB.ZZJG_ID ||
''', ' || C_TB.CM || ',0)) AS "' || V_TB.ZZJG_ID || '"';
END LOOP;
V_SQL := V_SQL || ' FROM TB_V2_DBINFO A ';
V_SQL := V_SQL || ' UNION ALL';
END LOOP;
V_SQL := SUBSTR(V_SQL, 1, LENGTH(V_SQL) - 10);
V_COUNT := LENGTH(REPLACE(V_SQL, 'UNION ALL', ' '));
V_BEGIN := 1;
FOR I IN 1 .. V_COUNT LOOP
V_END := INSTR(V_SQL, 'UNION ALL', 1, I);
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL, V_BEGIN, V_END - V_BEGIN));
V_BEGIN := V_END;
END LOOP;
DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL, V_END, LENGTH(V_SQL) - V_END));
END;结果运行至倒数第五行DBMS_OUTPUT.PUT_LINE(SUBSTR(V_SQL, V_BEGIN, V_END - V_BEGIN));这句时还是出现上述错误
create table TB_V2_DBINFO
(
ZZJG_ID NVARCHAR2(20) not null,
MUSER NVARCHAR2(60),
M1 NVARCHAR2(50),
M2 NVARCHAR2(50),
M3 NVARCHAR2(50),
M4 NVARCHAR2(50),
M5 NVARCHAR2(50),
M6 NVARCHAR2(50),
M7 NVARCHAR2(50),
M8 NVARCHAR2(50),
M9 NVARCHAR2(50),
M10 NVARCHAR2(50),
M11 NVARCHAR2(50),
M12 NVARCHAR2(50),
M13 NVARCHAR2(50),
M14 NVARCHAR2(50),
M15 NVARCHAR2(50),
M16 NVARCHAR2(50),
M17 NVARCHAR2(50),
M18 NVARCHAR2(50),
M19 NVARCHAR2(50),
M20 NVARCHAR2(50),
M21 NVARCHAR2(50),
M22 NVARCHAR2(50),
M23 NVARCHAR2(50),
M24 NVARCHAR2(50),
M25 NVARCHAR2(50),
M26 NVARCHAR2(50),
M27 NVARCHAR2(50),
M28 NVARCHAR2(50),
M29 NVARCHAR2(50),
M30 NVARCHAR2(50),
M31 NVARCHAR2(50),
M32 NVARCHAR2(50),
M33 NVARCHAR2(50),
M34 NVARCHAR2(50),
M35 NVARCHAR2(50),
M36 NVARCHAR2(50),
M37 NVARCHAR2(50),
M38 NVARCHAR2(50),
M39 NVARCHAR2(50),
M40 NVARCHAR2(50),
M41 NVARCHAR2(50),
M42 NVARCHAR2(50),
M43 NVARCHAR2(50),
M44 NVARCHAR2(50),
M45 NVARCHAR2(50),
M46 NVARCHAR2(50),
M47 NVARCHAR2(50),
M48 NVARCHAR2(50),
M49 NVARCHAR2(50),
M50 NVARCHAR2(50),
M51 NVARCHAR2(50),
M52 NVARCHAR2(50),
M53 NVARCHAR2(50),
M54 NVARCHAR2(50),
M55 NVARCHAR2(50),
M56 NVARCHAR2(50),
M57 NVARCHAR2(50),
M58 NVARCHAR2(50),
M59 NVARCHAR2(50),
M60 NVARCHAR2(50),
M61 NVARCHAR2(50),
M62 NVARCHAR2(50),
M63 NVARCHAR2(50),
M64 NVARCHAR2(50),
M65 NVARCHAR2(50),
M66 NVARCHAR2(50),
M67 NVARCHAR2(50),
M68 NVARCHAR2(50),
M69 NVARCHAR2(50),
M70 NVARCHAR2(50),
M71 NVARCHAR2(50),
M72 NVARCHAR2(50),
M73 NVARCHAR2(50),
M74 NVARCHAR2(50),
M75 NVARCHAR2(50),
M76 NVARCHAR2(50),
M77 NVARCHAR2(50),
M78 NVARCHAR2(50),
M79 NVARCHAR2(50),
M80 NVARCHAR2(50),
UPTIME DATE default SYSDATE
)