Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL Declare and Define a variable
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. Received on Wed Sep 29 2004 - 10:18:26 CDT