Re: Basic PL/SQL question involving a SELECT statement.

On 16 May 2003 03:25:33 -0700, mgf_rankin_at_yahoo.com (Martyn Rankin) wrote:

>Hi, I am new to PL/SQL and am wondering if it possible in PL/SQL to
>incorporate two select statements in a script? let me explain why I am
>asking this question.
>
>For example, I have a database table with a column containing all the
>divisions in a company and another table storing the financial data. I
>want to show the budget for each division. I want to incorporate the
>following SQL statements:
>
>(i) SELECT div FROM divisions;
>
>(ii) SELECT SUM(budget) bd FROM transactions
> WHERE unit like 'x' -- x is the string for the division name
>
>so far, I have declared my cursor and the script prints out all the
>divisions in the 'divisions' table. So where in my code (below) should
>I declare (and can the second SELECT statement to represent the SQL in
>(ii) - in the DECLARE section or the BEGIN? Am I correct in declaring
>the cursor for the divisions? If I create a table view to join the
>transactions & divisions table, would that make things easier?
>
>This is my code so far:
>
>DECLARE
> CURSOR divisions_cursor IS
> SELECT div FROM divisions;
>BEGIN
>/* open cursor */
>FOR divisions_record IN divisions_cursor LOOP
> DBMS_OUTPUT.PUT_LINE ('Division is ' || divisions_record.div);
> EXIT WHEN divisions_cursor%NOTFOUND;
> END LOOP;
>/* close cursor and end */
>
>
>I can't find any help on PL/SQL tutorials and I cannot find the answer
>to my question in my O'Reilly book & would gratefully appreciate any
>advice or tips.
>
>Thankyou for your time in advance,
>
>Martyn

DECLARE
  CURSOR divisions_cursor IS
    SELECT div FROM divisions;
cursor sumbudget (p_div in <whatever datatype?>) SELECT SUM(budget) bd FROM transactions

    WHERE unit like p_div||'%'
sum_budget number;
BEGIN
>/* cursor for loop, cursor is opened automatically */
>FOR divisions_record IN divisions_cursor LOOP
> DBMS_OUTPUT.PUT_LINE ('Division is ' || divisions_record.div);
open sumbudget;
fetch sumbuget into sum_budget;
close sumbudget;
/* redundant exit line removed*/    

> END LOOP;
>/*cursor closes automatically

of course this should rather be

select div, sum(budget)
from transactions
group by div;

Because that's what it is actually doing. A view is not needed.

Please read up on CURSOR FOR loop in the docs or the Feuerstein book and try to understand what he is saying. If you don't understand it get back with questions. Please also pay attention to the comment lines I have changed and added.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat May 17 2003 - 19:03:50 CEST