SQL> CREATE OR REPLACE FUNCTION get_col (tab VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 col_c2 VARCHAR2 (4000);
5 sqlstr VARCHAR2 (4000);
6 BEGIN
7 sqlstr := ' select ';
8
9 FOR cur IN (SELECT column_name
10 FROM user_col_comments
11 WHERE table_name = UPPER (tab))
12 LOOP
13 col_c2 := col_c2 || cur.column_name || ',';
14 END LOOP;
15
16 col_c2 := RTRIM (col_c2, 1);
17 col_c2 := SUBSTR (col_c2, 1, LENGTH (col_c2) - 1);
18 sqlstr := sqlstr || col_c2 || ' from ' || tab;
19 EXECUTE IMMEDIATE sqlstr;
20
21 RETURN sqlstr;
22 END;
23 /Function created.SQL> select get_col('a') from dual
2 /GET_COL('A')
-----------------------------------------------------------------
select RECEIVE_MONTH,DEFECT,DEFECT_TIMES,TYPE from aSQL>
SQL> select get_col('ABMFV_ACT_DRIVER_SOURCES_V') from dual
2 /GET_COL('ABMFV_ACT_DRIVER_SOURCES_V')
----------------------------------------------------------------------------------------------------
select DATA_SET_NAME,SOURCE,SOURCE_ACTIVITY,SOURCE_ACTIVITY_NAME,SOURCE_ACTIVITY_DEPT,SOURCE_ACTIVI
TY_DEPT_NAME,SOURCE_BEGIN_DATE,SOURCE_BEGIN_EFFECTIVE_DATE,SOURCE_END_DATE,SOURCE_ID,SOURCE_NAME,SOU
RCE_DEPT,SOURCE_DEPT_NAME,SOURCE_DEPT_ACTIVITY,SOURCE_DEPT_ACTIVITY_ID,SOURCE_DEPT_ACTIVITY_NAME,SOU
RCE_DEPT_ACTIVITY_TYPE_CODE,SOURCE_DEPT_ID,SOURCE_TYPE,SOURCE_TYPE_CODE,SOURCE_VALUE,ACTIVITY_DRIVER
,ACTIVITY_DRIVER_ID,ACTIVITY_DRIVER_NAME,ACTIVITY_DRIVER_DEPT,ACTIVITY_DRIVER_DEPT_NAME,ACTIVITY_DRI
VER_DEPT_ID,ACTIVITY_DRIVER_TYPE_CODE,MASTER_LIST,MASTER_LIST_ID,MASTER_LIST_NAME,MODEL,MODEL_ID,MOD
EL_NAME,DATA_SET,DATA_SET_ID from ABMFV_ACT_DRIVER_SOURCES_V
SQL> 这样子得到除了不要的其他字段可以吗?
2 RETURN VARCHAR2
3 IS
4 col_c2 VARCHAR2 (4000);
5 sqlstr VARCHAR2 (4000);
6 BEGIN
7 sqlstr := ' select ';
8
9 FOR cur IN (SELECT column_name
10 FROM user_col_comments
11 WHERE table_name = UPPER (tab))
12 LOOP
13 col_c2 := col_c2 || cur.column_name || ',';
14 END LOOP;
15
16 col_c2 := RTRIM (col_c2, 1);
17 col_c2 := SUBSTR (col_c2, 1, LENGTH (col_c2) - 1);
18 sqlstr := sqlstr || col_c2 || ' from ' || tab;
19 EXECUTE IMMEDIATE sqlstr;
20
21 RETURN sqlstr;
22 END;
23 /Function created.SQL> select get_col('a') from dual
2 /GET_COL('A')
-----------------------------------------------------------------
select RECEIVE_MONTH,DEFECT,DEFECT_TIMES,TYPE from aSQL>
SQL> select get_col('ABMFV_ACT_DRIVER_SOURCES_V') from dual
2 /GET_COL('ABMFV_ACT_DRIVER_SOURCES_V')
----------------------------------------------------------------------------------------------------
select DATA_SET_NAME,SOURCE,SOURCE_ACTIVITY,SOURCE_ACTIVITY_NAME,SOURCE_ACTIVITY_DEPT,SOURCE_ACTIVI
TY_DEPT_NAME,SOURCE_BEGIN_DATE,SOURCE_BEGIN_EFFECTIVE_DATE,SOURCE_END_DATE,SOURCE_ID,SOURCE_NAME,SOU
RCE_DEPT,SOURCE_DEPT_NAME,SOURCE_DEPT_ACTIVITY,SOURCE_DEPT_ACTIVITY_ID,SOURCE_DEPT_ACTIVITY_NAME,SOU
RCE_DEPT_ACTIVITY_TYPE_CODE,SOURCE_DEPT_ID,SOURCE_TYPE,SOURCE_TYPE_CODE,SOURCE_VALUE,ACTIVITY_DRIVER
,ACTIVITY_DRIVER_ID,ACTIVITY_DRIVER_NAME,ACTIVITY_DRIVER_DEPT,ACTIVITY_DRIVER_DEPT_NAME,ACTIVITY_DRI
VER_DEPT_ID,ACTIVITY_DRIVER_TYPE_CODE,MASTER_LIST,MASTER_LIST_ID,MASTER_LIST_NAME,MODEL,MODEL_ID,MOD
EL_NAME,DATA_SET,DATA_SET_ID from ABMFV_ACT_DRIVER_SOURCES_V
SQL> 这样子得到除了不要的其他字段可以吗?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货