Re: PL/SQL Problem

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Mon, 10 May 1999 21:50:42 +0200
Message-ID: <7h7dkq$esq$1@weber.a2000.nl>

Sydney wrote
> Due to the fact this is a function i can't use the declare key word.

No, you do not have to use it. Just put anything you want to declare between the AS (or IS) and BEGIN:

    create or replace function (pnumcom numcom%TYPE) return tcmd as

      type tcmd is record
        ( tnumtable numtable%TYPE,
          tnumserv numserv%TYPE,
          tnomserv nomserv%TYPE,
          tdatecom DATE,
          tmontant montant%TYPE);
      rcmd tcmd

    begin
    ...
    end;

> 2) Here is a simpla PL/SQL sample.
> create or replace procedure
> l_name char(20);
> begin
> l_name := &name;
> dbms_output.put_line(l_name || ' is a good PL/SQL programmer :)');
> end;

Hmmm, remember that SQL*Plus will replace the &name PRIOR to having Oracle actualy create the procedure. So, &name is only prompted for once. To remove the spaces, use varchar2(20) instead of char(20).

> 3)Cursor question

<snip>
> Is the "for in c1 loop" read all data in the cursor?

Yes.

> If there is no data in the cursor, where do i put the
> exception in order to display a message.

If there is no data, the loop is simply not executed. Alternatively, you could use OPEN and FETCH explicitly.

> 4) Another cursor question
> cursor c2 is select sum(montant) from commande where
> to_char(datecom,'MM')=pmois group by to_char(datecom,'DD');
<snip>
> How to access to the sum(montant)?
> Which one is correct(hope there is one ;))
> rec.montant or rec.sum(montant)

None... You need to give the sum(..) an alias, like

    cursor c2 is

        select sum(montant) my_sum
        from commande
        where to_char(datecom,'MM')=pmois
        group by to_char(datecom,'DD');

and use

    rec.my_sum

Arjan. Received on Mon May 10 1999 - 14:50:42 CDT