DBMS_UTILITY.EXPAND_SQL_TEXT
视图可以是一个有用的方法来隐藏开发人员的复杂性，但可能会导致问题。 使用视图很容易编写明显简单的语句，最终可能导致极其复杂的SQL被发送到服务器。 DBMS_UTILITY.EXPAND_SQL_TEXT过程展开对视图的引用，将它们转换为原始语句中的子查询。 一个简单的例子如下所示。
创建包含联接的视图：
CONN scott/tiger@pdb1
CREATE OR REPLACE VIEW emp_v AS
SELECT e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno,
d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno;该视图隐藏了连接的复杂性，允许我们使用一个非常简单的查询。
SELECT * FROM emp_v;我们可以看到服务器通过展开语句处理的真正的SQL语句。
SET SERVEROUTPUT ON
DECLARE
l_clob CLOB;
BEGIN
DBMS_UTILITY.expand_sql_text (
input_sql_text => 'SELECT * FROM emp_v',
output_sql_text => l_clob
);
DBMS_OUTPUT.put_line(l_clob);
END;
/
SELECT "A1"."EMPNO" "EMPNO",
"A1"."ENAME" "ENAME",
"A1"."JOB" "JOB","A1"."MGR" "MGR",
"A1"."HIREDATE" "HIREDATE",
"A1"."SAL" "SAL",
"A1"."COMM" "COMM",
"A1"."DEPTNO" "DEPTNO",
"A1"."DNAME" "DNAME"
FROM (SELECT "A2"."EMPNO_0" "EMPNO",
"A2"."ENAME_1" "ENAME",
"A2"."JOB_2" "JOB",
"A2"."MGR_3" "MGR",
"A2"."HIREDATE_4" "HIREDATE",
"A2"."SAL_5" "SAL",
"A2"."COMM_6" COMM",
"A2"."QCSJ_C000000000400000_7" "DEPTNO",
"A2"."DNAME_9" "DNAME"
FROM (SELECT "A4"."EMPNO" "EMPNO_0",
"A4"."ENAME" "ENAME_1",
"A4"."JOB" "JOB_2",
"A4"."MGR" "MGR_3",
"A4"."HIREDATE" "HIREDATE_4",
"A4"."SAL" "SAL_5",
"A4"."COMM" "COMM_6",
"A4"."DEPTNO" "QCSJ_C000000000400000_7",
"A3"."DEPTNO" "QCSJ_C000000000400001",
"A3"."DNAME" "DNAME_9"
FROM SCOTT."EMP" "A4",
SCOTT."DEPT" "A3"
WHERE "A4"."DEPTNO"="A3"."DEPTNO") "A2"
) "A1"
PL/SQL procedure successfully completed.
SQL>从下周开始，将以In-Memory为主要内容翻译一系列的文章。
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2016-12-11，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
