Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'd like to caveat this by saying that I haven't had a lot of time to work on this so there may be a few errors but it should give you the gist of what i'm trying to tell you:</p> <pre><code>CREATE or REPLACE PROCEDURE TEST( activationStartDate IN DATE, activationEndDate IN DATE, deActivationStartDate IN DATE, deActivationEndDate IN DATE ) AS CURSOR main_cur IS WITH include_rec AS (SELECT first_name, start_date, COUNT(1) OVER (PARTITION BY first_name) name_count FROM Employee WHERE start_date BETWEEN activationStartDate AND activationEndDate) SELECT DISTINCT first_name FROM include_rec WHERE start_date NOT BETWEEN deActivationStartDate AND deActivationEndDate AND name_count &gt; 2; -- FirstNameListTable dbms_sql.varchar2_table; BEGIN OPEN main_cur; FETCH main_cur BULK COLLECT INTO FirstNameListTable; CLOSE main_cur; FOR i IN FirstNameListTable.FIRST .. FirstNameListTable.LAST LOOP ---business logic END LOOP; etc... </code></pre> <p>I'd also say that if you are expecting a large resultset then put the BULK COLLECT into a loop too to reduce your memory requirements.</p> <p>In an ideal world, you'd pass the variables activationStartDate, activationEndDate, deActivationStartDate and deActivationEndDate into the cursor as cursor parameters to keep the cursor modular but that's up to you. ;-)</p> <p>Hope this helps... Ollie.</p> <p>EDIT: In response to your question about using records to select * from the table, you can declare an associative array based on the cursor columns, for the example above if you wanted to select more than one column from EMPLOYEE then after the WITH clause you would select the named columns from EMPLOYEE you want and instead of:</p> <pre><code>FirstNameListTable dbms_sql.varchar2_table; </code></pre> <p>declare the associative array type and variable as:</p> <pre><code>TYPE main_cur_tabtype IS TABLE OF main_cur%ROWTYPE INDEX BY PLS_INTEGER; main_cur_tab main_cur_tabtype; </code></pre> <p>This gives you a flexible array that will automatically hold the columns selected in your cursor (main_cur).</p> <p>You would collect the records into this array with the BULK COLLECT:</p> <pre><code>OPEN main_cur; FETCH main_cur BULK COLLECT INTO main_cur_tab; CLOSE main_cur; </code></pre> <p>and loop through them with:</p> <pre><code>FOR i IN main_cur_tab.FIRST .. main_cur_tab.LAST LOOP etc. </code></pre> <p>as for returning your resultset to Java, well I'm no java expert and you'll need to ask that in another forum or tag this question with the Java tag and hope a Java person picks it up and answers it for you.</p>
    singulars
    1. This table or related slice is empty.
    1. POImprove Performance of pl/sql,want to perform Q2 on result of Q1
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USOllie
    1. USOllie
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POImprove Performance of pl/sql,want to perform Q2 on result of Q1
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COIf you are performing a full table scan in your main query, perhaps you should talk to your DBA's and ask them if an index on start_date would be possible/beneficial.
      singulars
      1. PO
      1. USOllie
    2. CO@Ollie--Please see original question, i edited, as suggested by u...but getting some error.....waiting for ur reply...I am new to pl/sql
      singulars
      1. PO
      1. USuser595014
    3. COYou have NOT copied what I gave you, I can see a MINUS in your code which I have totally eliminated from the code I wrote. If you are going to use the code I give you, at least use it as intended and not some hybrid of your original code and the answer I give you. Try the code I gave you as it is and see if you get an error.
      singulars
      1. PO
      1. USOllie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload