title: ORACLE: Using CTEs (Common Table Expressions) with PL/SQL
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Given the following example:</p>
  
  <p><em>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.</em></p>
  
  <pre><code>WITH A as (SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 200),
  
  B as (SELECT * FROM A WHERE EMPLOYEE_START_DATE &gt; 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;
  </code></pre>
  
  <p>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. <em>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.</em> </p>
  
  <p>There are three issues I am facing here that I am trying to wrap my head around:</p>
  
  <p>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.</p>
  
  <p>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? </p>
  
  <p>3) Is there going to a big performance issue with this vs. straight SQL in regards to speed and resources used?</p>
  
  <p>Thanks in advance and again, my apologies if I am missing something really obvious here!</p>