写一个存储过程吧,试试看 .. /* Author: mantisXF Date: Sep 5, 2008 ENTER_COLUMN -- E.g: 1,2,4,5,7,3,2 ENTER_SCHEMA -- Schema name of your table ENTER_TABLE -- Table name OUT_CUR -- Return the result using cursor */CREATE OR REPLACE PROCEDURE GET_RESULT_NO_COLUMN_TEST(ENTER_COLUMN IN VARCHAR2, ENTER_SCHEMA IN VARCHAR2, ENTER_TABLE IN VARCHAR2, OUT_CUR OUT SYS_REFCURSOR) AS -- DECLARE VARIABLE V_STEP VARCHAR2(100); V_SQL VARCHAR2(2000); V_COLUMN_NAME VARCHAR2(100); V_COLUMNS VARCHAR2(500); CUR_GET_COLUMNS SYS_REFCURSOR;
BEGIN V_COLUMN_NAME := ''; V_COLUMNS := '';
V_STEP := 'OPEN CURSOR WHICH GETTING COLUMNS FROM DATA DICTIONARY'; V_SQL := 'SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('''||ENTER_SCHEMA||''') AND TABLE_NAME = UPPER('''||ENTER_TABLE||''') AND COLUMN_ID IN ('||ENTER_COLUMN||')';
OPEN CUR_GET_COLUMNS FOR V_SQL; LOOP FETCH CUR_GET_COLUMNS INTO V_COLUMN_NAME; EXIT WHEN CUR_GET_COLUMNS%NOTFOUND;
V_COLUMNS := V_COLUMNS || V_COLUMN_NAME || ','; END LOOP;
V_STEP := 'CLOSE THE CURSOR IF IT OPENED'; IF CUR_GET_COLUMNS%ISOPEN THEN CLOSE CUR_GET_COLUMNS; END IF;
V_STEP := 'RETURE THE CURSOR WHICH SELECT ALL REQUIRED RESULT'; V_SQL := 'SELECT '||RTRIM(V_COLUMNS,',')||' FROM '||ENTER_SCHEMA||'.'||ENTER_TABLE||'';
OPEN OUT_CUR FOR V_SQL;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error at :' || V_STEP); DBMS_OUTPUT.PUT_LINE(SQLCODE || ' : ' || SQLERRM);END GET_RESULT_NO_COLUMN_TEST;
/*
Author: mantisXF
Date: Sep 5, 2008
ENTER_COLUMN -- E.g: 1,2,4,5,7,3,2
ENTER_SCHEMA -- Schema name of your table
ENTER_TABLE -- Table name
OUT_CUR -- Return the result using cursor
*/CREATE OR REPLACE PROCEDURE GET_RESULT_NO_COLUMN_TEST(ENTER_COLUMN IN VARCHAR2,
ENTER_SCHEMA IN VARCHAR2,
ENTER_TABLE IN VARCHAR2,
OUT_CUR OUT SYS_REFCURSOR)
AS
-- DECLARE VARIABLE
V_STEP VARCHAR2(100);
V_SQL VARCHAR2(2000);
V_COLUMN_NAME VARCHAR2(100);
V_COLUMNS VARCHAR2(500);
CUR_GET_COLUMNS SYS_REFCURSOR;
BEGIN
V_COLUMN_NAME := '';
V_COLUMNS := '';
V_STEP := 'OPEN CURSOR WHICH GETTING COLUMNS FROM DATA DICTIONARY';
V_SQL := 'SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE OWNER = UPPER('''||ENTER_SCHEMA||''')
AND TABLE_NAME = UPPER('''||ENTER_TABLE||''')
AND COLUMN_ID IN ('||ENTER_COLUMN||')';
OPEN CUR_GET_COLUMNS FOR V_SQL;
LOOP
FETCH CUR_GET_COLUMNS INTO V_COLUMN_NAME;
EXIT WHEN CUR_GET_COLUMNS%NOTFOUND;
V_COLUMNS := V_COLUMNS || V_COLUMN_NAME || ',';
END LOOP;
V_STEP := 'CLOSE THE CURSOR IF IT OPENED';
IF CUR_GET_COLUMNS%ISOPEN THEN
CLOSE CUR_GET_COLUMNS;
END IF;
V_STEP := 'RETURE THE CURSOR WHICH SELECT ALL REQUIRED RESULT';
V_SQL := 'SELECT '||RTRIM(V_COLUMNS,',')||'
FROM '||ENTER_SCHEMA||'.'||ENTER_TABLE||'';
OPEN OUT_CUR FOR V_SQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error at :' || V_STEP);
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' : ' || SQLERRM);END GET_RESULT_NO_COLUMN_TEST;