Re: PL/SQL Problem

1. I must declare a type before using it as a return variable of a function. Due to the fact this is a function i can't use the declare key word. Here is my PL/SQL code declare type tcmd is record ( tnumtable numtable%TYPE, tnumserv numserv%TYPE, tnomserv nomserv%TYPE, tdatecom DATE, tmontant montant%TYPE); create or replace function (pnumcom numcom%TYPE) return tcmd as rcmd tcmd begin ... end; There is an error(Don't like create) . Is there a solution?

>> If you are going to use the %type you need to tell it which table the
column is coming from for >>the %type.

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;
If you enter for example 'my dog', it will write my dog(14 blanks) ' is a good PL/SQL programmer ;) How to remove the 14 blanks and have this my dog is a good PL/SQL programmer :)

>> CHAR is a fixed length datatype, if you only enter 7 characters it will
pad with 14 blanks. Try >>using VARCHAR2 instead and you won't get the blanks.

3)Cursor question
create or replace procedure aff_tableservie (pnumserv serveur.numserv%TYPE, pdate DATE) as
cursor c1 is select numtable from affecte where numserv = pnumserv and datejour = pdate;
begin
  for rec in c1 loop
    dbms_output.put_line('Le serveur ' || pnumserv || 'a servi la table ' || rec.numtable || 'le ' ||

    to_char(rec.datejour,'DD/MM/YYYY');   end loop;
end;
Is the "for in c1 loop" read all data in the cursor? If there is no data in the cursor, where do i put the exception in order to display a message.

>> You don't have to put an exception for an explicit cursor, but if you
want one you can use:
for rec in c1 loop
begin
.....
....
exception
when ......
end;
end loop;

4) Another cursor question
Here is a cursor
cursor c2 is select sum(montant) from commande where to_char(datecom,'MM')=pmois group by to_char(datecom,'DD'); To read the entire cursor
for rec in c2 loop
end loop;
How to access to the sum(montant)?
Which one is correct(hope there is one ;)) rec.montant or rec.sum(montant)

>> Use an alias in the select statement:
cursor c2 is select sum(montant) as sum_montant from commande where to_char(datecom,'MM')=pmois group by to_char(datecom,'DD'); Then access it with rec.sum_montant.

Kenny Gump
OCP 7.3 DBA Sydney wrote in message <7h74uf$rka$2_at_wanadoo.fr>...
>Hi
>
>I'm a french student, i must create some PL/SQL procedure and function. I'm
>using SQL Server 8.0 on a UNIX system (HP-UNIX).
>
>1) I must declare a type before using it as a return variable of a
function.
>Due to the fact this is a function i can't use the declare key word.
>Here is my PL/SQL code
>declare type tcmd is record
>( tnumtable numtable%TYPE,
> tnumserv numserv%TYPE,
> tnomserv nomserv%TYPE,
> tdatecom DATE,
> tmontant montant%TYPE);
>create or replace function (pnumcom numcom%TYPE) return tcmd as
>rcmd tcmd
>begin
>...
>end;
>There is an error(Don't like create) . Is there a solution?
>
>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;
>
>If you enter for example 'my dog', it will write
>my dog(14 blanks) ' is a good PL/SQL programmer ;)
>How to remove the 14 blanks and have this
>my dog is a good PL/SQL programmer :)
>
>3)Cursor question
>
>create or replace procedure aff_tableservie (pnumserv serveur.numserv%TYPE,
>pdate DATE) as
>cursor c1 is select numtable from affecte where numserv = pnumserv and
>datejour = pdate;
>begin
> for rec in c1 loop
> dbms_output.put_line('Le serveur ' || pnumserv || 'a servi la table '
||
>rec.numtable || 'le ' ||
> to_char(rec.datejour,'DD/MM/YYYY');
> end loop;
>end;
>
>Is the "for in c1 loop" read all data in the cursor?
>If there is no data in the cursor, where do i put the exception in order to
>display a message.
>
>4) Another cursor question
>
>Here is a cursor
>cursor c2 is select sum(montant) from commande where
>to_char(datecom,'MM')=pmois group by to_char(datecom,'DD');
>
>To read the entire cursor
>for rec in c2 loop
>end loop;
>How to access to the sum(montant)?
>Which one is correct(hope there is one ;))
>rec.montant or rec.sum(montant)
>
>Thanks a lot.
>Sydney
>
>
>
Received on Tue May 11 1999 - 06:51:53 CDT