CODEWITHSUNDEEP
oracle-databaseplsql
Lo Mc
Lo Mc

Reputation: 11

declaring pl sql variable using query then using in subsequent query

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

  1. to declare a variable
  2. assign a value to that variable via query
  3. 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;

Upvotes: 0

Views: 165

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

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>

Upvotes: 1

Related Questions