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

"Martyn Rankin" <mgf_rankin_at_yahoo.com> wrote in message news:c4147d77.0305160225.48d425a3_at_posting.google.com...
> 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?

You *could* just add a second cursor and repeat the same logic for the second cursor.

> 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?

I would probably do it in straight SQL with

select d.div,sum(t.budget)
from divisions d,transactions t
where t.unit=d.div
group by d.div;

but you could easily turn this into a cursor loop as you do in your example if you *need* to do it in PL/SQL.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK