日期:2014-05-16 浏览次数:20786 次
--创建测试表
create table table1 (c1 nvarchar2(10),c2 nvarchar2(10));
insert into table1 values ('aa','bb');
insert into table1 values ('cc','dd');
commit;
--创建存储过程
CREATE OR REPLACE PROCEDURE TEST
IS
V_SQL NVARCHAR2(2000);
CURSOR CURSOR_1 IS
SELECT COLUMN_NAME CN
FROM USER_TAB_COLS
WHERE TABLE_NAME='TABLE1';
BEGIN
V_SQL := 'SELECT ';
FOR V_TB IN CURSOR_1
LOOP
V_SQL := V_SQL
------解决方案--------------------
V_TB.CN
------解决方案--------------------
'
------解决方案--------------------
''_''
------解决方案--------------------
';
END LOOP;
V_SQL := SUBSTR(V_SQL,1,LENGTH(V_SQL)-7);
V_SQL := V_SQL
------解决方案--------------------
' FROM TABLE1' ;
DBMS_OUTPUT.PUT_LINE(V_SQL);
--EXECUTE IMMEDIATE V_SQL;
END;
--执行存储过程 打印的sql
SELECT C1
------解决方案--------------------
'_'
------解决方案--------------------
C2 FROM TABLE1
DECLARE
V_SQL VARCHAR(300);
BEGIN
SELECT 'SELECT '
------解决方案--------------------
REPLACE(WMSYS.WM_CONCAT(T.COLUMN_NAME), ',', '
------解决方案--------------------
')
------解决方案--------------------
' FROM TABLE1 '
INTO V_SQL
FROM ALL_TAB_COLUMNS T
WHERE TABLE_NAME = 'TABLE1' --AND OWNER='用户'
--DBMS_OUTPUT.put_line(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;