Re: PL/SQL Declare and Define a variable

From: Oxnard <shankeyp_at_no-spam.comcast.net>
Date: Wed, 29 Sep 2004 18:28:00 -0500
Message-ID: <y4-dnfQS88SA2cbcRVn-pQ@comcast.com>

"Tony Andrews" <andrewst_at_onetel.com> wrote in message news:1096471106.801349.260410_at_k17g2000odb.googlegroups.com...
> Oxnard wrote:
> > I'm just wondering if there's anyway to do this, I guess it could
> help
> > readability.
> >
> > if I have a procedure:
> >
> > create or replace procedure test as
> > v_test NUMBER := 25; -- Works no big deal
> > v_test1 NUMBER := select my_valid_sequence.nextval from dual;
> -- does
> > not work
> > BEGIN
> > -- So I end up doing with v_test1 properly defined
> > select my_valid_sequence.nextval into v_test1 from dual;
> > END;
> > /
> >
> > The "select my_valid_sequence.nextval into v_test1 from dual;" is OK
> but it
> > seems to me the code would be a bit easier to read if I could Define
> the
> > variable in the declarative section. My question is without using a
> cursor
> > can a select be put into the declarative section?

>

> Not directly, no. But you could do this:

>

> SQL> create sequence test_seq;

>

> Sequence created.

>

> SQL> create or replace function get_test_seq return integer is
> 2 v_seqval integer;
> 3 begin
> 4 select test_seq.nextval into v_seqval from dual;
> 5 return v_seqval;
> 6 end;
> 7 /

>

> Function created.

>

> SQL> declare
> 2 v_test1 integer := get_test_seq;
> 3 begin
> 4 dbms_output.put_line(v_test1);
> 5 end;
> 6 /
> 1

>

> PL/SQL procedure successfully completed.
>

I was thinking about that ... Thanks for the advice Received on Wed Sep 29 2004 - 18:28:00 CDT