ORACLE:在PL/SQL中使用CTE(公用表表达式) [英] ORACLE: Using CTEs (Common Table Expressions) with PL/SQL
问题描述
首先，我的背景是SQL Server.使用CTE(通用表表达式)很容易，将其转换为带有变量的存储过程不需要对SQL的结构进行任何更改，只需要用变量名替换输入的值即可.
但是，在Oracle PL/SQL中，这是完全不同的事情.我的CTE可以像直接SQL一样正常工作，但是一旦我尝试将它们包装为PL/SQL，就会遇到很多问题.据我了解，SELECT现在需要一个INTO，该INTO仅保存单个记录的结果.但是，我想要整个记录集包含多个值.
如果我在这里没有明显的道歉，我深表歉意.我认为我的问题中有99％是我需要进行的范式转换.
给出以下示例:
注意:我在这里大大简化了SQL.我确实知道以下示例可以在单个SQL语句中完成.实际的SQL要复杂得多.这是我在这里寻找的基础知识.
WITH A as (SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 200),
B as (SELECT * FROM A WHERE EMPLOYEE_START_DATE > date '2014-02-01'),
C as (SELECT * FROM B WHERE EMPLOYEE_TYPE = 'SALARY')
SELECT 'COUNTS' as Total,
(SELECT COUNT(*) FROM A) as 'DEPT_TOTAL',
(SELECT COUNT(*) FROM B) as 'NEW_EMPLOYEES',
(SELECT COUNT(*) FROM C) as 'NEW_SALARIED'
FROM A
WHERE rowcount = 1;
现在，如果我想使用顶部传入或预定义的变量将其放入PL/SQL，则声明变量，将值弹出到其中并将硬编码的值更改为变量并不是一件简单的事情.并运行它. 注意:我确实知道我可以简单地将硬编码值更改为:Department，:StartDate和:Type这样的变量，但是同样，我也简化了示例.
在这里我想解决的三个问题是我
1)用带声明变量的PL/SQL重写此方法的最佳方法是什么?现在，CTE必须投入一些精力.但是然后我一次只处理一行，而不是整个表.因此，CTE'A'一次只能显示一行，而CTE B只会看到该行，而不是A的所有数据结果，依此类推.我知道我很可能将不得不使用CURSORS来遍历记录，这似乎使它变得过于复杂.
2)现在，输出必须使用DBMS_OUTPUT.对于多条记录，我将不得不使用带有FETCH(或FOR ... LOOP)的CURSOR.是的?
3)在速度和资源使用方面，与直接SQL和直接SQL相比，是否会出现较大的性能问题?
一次又一次地感谢您，如果我错过这里确实很明显的内容，我深表歉意！
首先，这与CTE无关.对于简单的select * from table查询，此行为将是相同的.区别在于，使用T-SQL时，查询进入隐式游标，该游标将返回给调用方.从Management Studio执行SP时，这很方便.结果集显示在数据窗口中，就好像我们直接执行了查询一样.但这实际上是非标准行为. Oracle具有更标准的行为，可以这样表述:未定向到游标的任何查询的结果集都必须定向到变量".当直接输入变量时，查询必须仅返回一行.
要复制T-SQL的行为，只需显式声明并返回游标.然后，调用代码从游标中获取整个结果集，但一次只能获取一行.您无法获得Sql Developer或PL/SQL Developer将结果集转移到数据显示窗口的便利，但是您什么也做不了.
但是，由于我们通常不会编写仅从IDE调用的SP，因此使用Oracle的显式游标比使用SQL Server的隐式游标更容易.只需在Google上使用"oracle将ref游标返回给调用方"即可，以获取大量有用的资料.
First off, my background is in SQL Server. Using CTEs (Common Table Expressions) is a breeze and converting it to a stored procedure with variables doesn't require any changes to the structure of the SQL other than replacing entered values with variable names.
In Oracle PL/SQL however, it is a completely different matter. My CTEs work fine as straight SQL, but once I try to wrap them as PL/SQL I run into a host of issues. From my understanding, a SELECT now needs an INTO which will only hold the results of a single record. However, I am wanting the entire recordset of multiple values.
My apologies if I am missing the obvious here. I'm thinking that 99% of my problem is the paradigm shift I need to make.
Given the following example:
NOTE: I am greatly over simplifying the SQL here. I do know the below example can be done in a single SQL statement. The actual SQL is much more complex. It's the fundamentals I am looking for here.
WITH A as (SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 200),
B as (SELECT * FROM A WHERE EMPLOYEE_START_DATE > date '2014-02-01'),
C as (SELECT * FROM B WHERE EMPLOYEE_TYPE = 'SALARY')
SELECT 'COUNTS' as Total,
(SELECT COUNT(*) FROM A) as 'DEPT_TOTAL',
(SELECT COUNT(*) FROM B) as 'NEW_EMPLOYEES',
(SELECT COUNT(*) FROM C) as 'NEW_SALARIED'
FROM A
WHERE rowcount = 1;
Now if I want to make this into PL/SQL with variables that are passed in or predefined at the top, it's not a simple matter of declaring the variables, popping values into them, and changing my hard-coded values into variables and running it. NOTE: I do know that I can simply change the hard-coded values to variables like :Department, :StartDate, and :Type, but again, I am oversimplifying the example.
There are three issues I am facing here that I am trying to wrap my head around:
1) What would be the best way to rewrite this using PL/SQL with declared variables? The CTEs now have to go INTO something. But then I am dealing with one row at a time as opposed to the entire table. So CTE 'A' is a single row at a time, and CTE B will only see the single row as opposed to all of the data results of A, etc. I do know that I will most likely have to use CURSORS to traverse the records, which somehow seems to over complicate this.
2) The output now has to use DBMS_OUTPUT. For multiple records, I will have to use a CURSOR with FETCH (or a FOR...LOOP). Yes?
3) Is there going to a big performance issue with this vs. straight SQL in regards to speed and resources used?
Thanks in advance and again, my apologies if I am missing something really obvious here!
First, this has nothing to do with CTEs. This behavior would be the same with a simple select * from table query. The difference is that with T-SQL, the query goes into an implicit cursor which is returned to the caller. When executing the SP from Management Studio this is convenient. The result set appears in the data window as if we had executed the query directly. But this is actually non-standard behavior. Oracle has the more standard behavior which might be stated as "the result set of any query that isn't directed into a cursor must be directed to variables." When directed into variables, then the query must return only one row.
To duplicate the behavior of T-SQL, you just have to explicitly declare and return the cursor. Then the calling code fetches from the cursor the entire result set but one row at a time. You don't get the convenience of Sql Developer or PL/SQL Developer diverting the result set to the data display window, but you can't have everything.
However, as we don't generally write SPs just to be called from the IDE, it is easier to work with Oracle's explicit cursors than SQL Server's implicit ones. Just google "oracle return ref cursor to caller" to get a whole lot of good material.
这篇关于ORACLE:在PL/SQL中使用CTE(公用表表达式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
