oracle 管道方式输出,Oracle 管道函数的一点巧妙利用

本帖最后由 caroline0803 于 2014-1-13 00:08 编辑

--根据楼主的启发，对LZ的函数进行了改动，让它生产动态的创建视图的子句

CREATE OR REPLACE FUNCTION GET_VIEW_CASE(I_TABLE_NAME VARCHAR2)

RETURN mesg_list

/*

* 根据指定表名返回对应的建立视图的语句：为了防止把数据库中已经存在的语句覆盖，在创建视图的语句中

* 未包含 OR REPLACE子句

*  使用方式: SELECT * FROM TABLE(GET_VIEW_CASE('YOUR_TABLE_NAME'));

*/

PIPELINED IS

V_MAX_COL_ID            NUMBER;

V_TABLE_NAME            VARCHAR2(100) := UPPER(I_TABLE_NAME); --表名字，需要进行大写转换

V_GEN_VIEW_NAME         VARCHAR2(100) := UPPER(I_TABLE_NAME) || '_V'; --视图名字：默认以_V结尾

V_ALIAS_TABLE_NAME      VARCHAR2(100);

V_COUNT_CR_ALI_TAB_NAME NUMBER; --计算表别名时使用的计数变量

V_TABLE_COMMENT         VARCHAR2(100); --表的注释

V_COMMENTS              VARCHAR2(100); --表字段的注释

V_SPACE_MARK            VARCHAR2(100); --字段名和字段描述之间的间隔符(--)

--表注释的游标-和表的列的顺序一致

CURSOR CUR_TAB_COLS IS

SELECT ACC.COLUMN_NAME, --列名字

ACC.COMMENTS, --列注释

ALC.COLUMN_ID --列编号

FROM ALL_COL_COMMENTS ACC

INNER JOIN ALL_TAB_COLS ALC

ON (ACC.COLUMN_NAME = ALC.COLUMN_NAME AND ACC.OWNER = ALC.OWNER AND

ACC.TABLE_NAME = ALC.TABLE_NAME)

WHERE ACC.OWNER = USER

AND ACC.TABLE_NAME = V_TABLE_NAME

ORDER BY ALC.COLUMN_ID;

BEGIN

SELECT MAX(ATC.COLUMN_ID)

INTO V_MAX_COL_ID

FROM USER_TAB_COLS ATC

WHERE ATC.TABLE_NAME = V_TABLE_NAME;

IF V_MAX_COL_ID IS NULL THEN

PIPE ROW('表不存在!!!');

RETURN;

END IF;

--获取表的备注字段信息

SELECT T.COMMENTS

INTO V_TABLE_COMMENT

FROM ALL_TAB_COMMENTS T

WHERE T.TABLE_NAME = V_TABLE_NAME;

--表的别名

IF V_ALIAS_TABLE_NAME IS NULL THEN

V_ALIAS_TABLE_NAME := SUBSTR(V_TABLE_NAME, 1, 1);

WHILE INSTR(V_TABLE_NAME, '_', 1, V_COUNT_CR_ALI_TAB_NAME) != 0 LOOP

V_ALIAS_TABLE_NAME      := V_ALIAS_TABLE_NAME ||

SUBSTR(V_TABLE_NAME,

INSTR(V_TABLE_NAME,

'_',

1,

V_COUNT_CR_ALI_TAB_NAME) + 1,

1);

V_COUNT_CR_ALI_TAB_NAME := V_COUNT_CR_ALI_TAB_NAME + 1;

END LOOP;

END IF;

--表的别名超过了3，则使用T作为别名

IF LENGTH(V_ALIAS_TABLE_NAME) > 3 THEN

V_ALIAS_TABLE_NAME := 'T';

END IF;

PIPE ROW('CREATE  VIEW ' || V_GEN_VIEW_NAME || ' AS ');

PIPE ROW('SELECT  ');

PIPE ROW('/********************************************************************************');

PIPE ROW('  功能描述：' || V_TABLE_COMMENT || '的视图');

PIPE ROW('  编 码 人：小明 ');

PIPE ROW('  编码日期：' || TO_CHAR(SYSDATE, 'YYYY-MM-DD'));

PIPE ROW('  引 用 表：' || UPPER(I_TABLE_NAME) || ':' || V_TABLE_COMMENT);

PIPE ROW('  修改记录：');

PIPE ROW('********************************************************************************/');

--打开游标循环处理列的注释信息

FOR V_REC IN CUR_TAB_COLS LOOP

--分隔符的初始化，用于拼接注释

V_COMMENTS := TRIM(V_REC.COMMENTS);

IF V_COMMENTS IS NULL THEN

V_SPACE_MARK := '';

ELSE

V_SPACE_MARK := ' --';

END IF;

--对于表中的最后一个字段，不能添加','分隔:

IF v_rec.COLUMN_ID != V_MAX_COL_ID THEN

V_SPACE_MARK := ',' || V_SPACE_MARK;

END IF;

PIPE ROW('       ' || V_ALIAS_TABLE_NAME || '.' || V_REC.COLUMN_NAME ||

V_SPACE_MARK || V_REC.COMMENTS);

END LOOP;

PIPE ROW(' FROM ' || V_TABLE_NAME || ' ' || V_ALIAS_TABLE_NAME);

RETURN;

END;

结果：

CREATE  VIEW EMP_V AS

SELECT

/********************************************************************************

功能描述：员工表的视图

编 码 人：小明

编码日期：2014-01-13

引 用 表：EMP:员工表

修改记录：

********************************************************************************/

E.EMPNO, --员工编号

E.ENAME, --姓名

E.JOB, --职位

E.MGR, --领导编号

E.HIREDATE, --雇佣日期

E.SAL, --薪资

E.COMM, --奖金

E.DEPTNO --部门编号

FROM EMP E