declaring pl sql variable using query then using in subsequent query

Question

I’ve read some on declaring and using variables in PL/SQL. and I’ve only done so in other versions (TSQL) or code languages.

Below is what I’m trying to do, which is

to declare a variable
assign a value to that variable via query
use the subsequent result from #2 in another query.

I’ve tried other methods listed on the Internet but nothing I do seems to work when calling the variable in the final query.

The declaration and into statements work but the last select query does not.

    declare
      Degr_term varchar2(20);

    begin
      select max(z.term)
        INTO degr_term
        from dwh.rpt_ersd_vw d
        join dwh.dim_ers_term_vw z
          on d.DIM_DEGR_ERS_TERM_SKEY = z.dim_ers_term_skey;

      select * from ir_wip.stem_enr s where s.min_deg_term = degr_term;
    end;

does not work really doesn't help people to answer your question. A query in a PL/SQL code block must have an INTO or must be declared as a cursor. Does the code (in your question) compile? My guess is that it does not. So I think that you should edit your question and post the compilation error that you are getting. Did you try reading the Database PL/SQL Language Reference? — Abra
– Abra, Commented Dec 4, 2021 at 7:16
The approach should work fine, you just need to add code to process the output from the second query. — William Robertson
– William Robertson, Commented Dec 4, 2021 at 11:12

Littlefoot · Accepted Answer · 2021-12-07 06:12:52Z

Just like you selected INTO from the 1st select statement, you have to do it in the 2nd as well. It means that you'll have to declare a variable which will hold its contents.

Presuming - maybe not correctly - that 2nd select returns only one row - you could

SQL> declare
  2    degr_term varchar2(20);
  3    l_stem    stem_enr%rowtype;     --> this
  4  begin
  5    select max(z.term)
  6      into degr_term
  7      from rpt_ersd_vw d
  8      join dim_ers_term_vw z
  9        on d.DIM_DEGR_ERS_TERM_SKEY = z.dim_ers_term_skey;
 10
 11    select *
 12      into l_stem             --> this
 13      from stem_enr s
 14      where s.min_deg_term = degr_term;
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL>

(I removed schema names as I don't have them and didn't feel like creating ones.)

If the 2nd query returns more than a single row, then you'd e.g.

SQL> declare
  2    degr_term    varchar2(20);
  3    type         l_stem_typ is table of stem_enr%rowtype;
  4    l_stem_tab   l_stem_typ;
  5  begin
  6    select max(z.term)
  7      into degr_term
  8      from rpt_ersd_vw d
  9      join dim_ers_term_vw z
 10        on d.DIM_DEGR_ERS_TERM_SKEY = z.dim_ers_term_skey;
 11
 12    select *
 13      bulk collect
 14      into l_stem_tab
 15      from stem_enr s
 16       where s.min_deg_term = degr_term;
 17  end;
 18  /

PL/SQL procedure successfully completed.

SQL>

[EDIT: How to output the result? Using a loop]

Presume that table looks like this (I don't know really, you never posted any sample data):

SQL> select * from stem_enr;

MIN_DEG_TERM NAME   SURN
------------ ------ ----
           1 Little Foot
           1 Big    Foot

Then you'd (simplified example; I didn't feel like creating other tables as well):

SQL> declare
  2    type       l_stem_typ is table of stem_enr%rowtype;
  3    l_stem_tab l_stem_typ;
  4  begin
  5    select *
  6      bulk collect
  7      into l_stem_tab
  8      from stem_enr s
  9      where s.min_deg_term = 1;
 10
 11    for i in 1 .. l_stem_tab.count loop
 12      dbms_output.put_line(l_stem_tab(i).name ||' '|| l_stem_tab(i).surname);
 13    end loop;
 14  end;
 15  /
Little Foot
Big Foot

PL/SQL procedure successfully completed.

SQL>

Thank you. How do I output the results of l_stem_tab in that solution?
You're welcome. You'd use a loop; have a look at edited answer.

Collectives™ on Stack Overflow

declaring pl sql variable using query then using in subsequent query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related