How to make temp table in PL-SQL procedure?

Question

I am new to PL-SQL. Can anyone answered me how to write Select statement(temp table) and apply if/else on the basis of that temp table and print output line? Sorry if you are asking my work/effort to see how far I have made it...I am looking a way to start.

Any help would be highly appreciable.

Show what you have tried so far. Also, what exactly you are trying to accomplish , with specific details. Otherwise, question could be voted as too broad. — Kaushik Nayak, Nov 07 '17 at 03:28
i am new to PLSQL . That's why I am asking if anyone know better in easy way. Sorry if it hurting you — Kate, Nov 07 '17 at 03:29
I am sorry if my question does not make any sense but I am trying to select multiple columns from different tables and apply if statement to see the result and print some message within procedure — Kate, Nov 07 '17 at 03:41

score 0 · Accepted Answer · answered Nov 07 '17 at 04:20

0

i didn't know about your exact requirement, below code is for my understating from your question. COde

  decalre
    a varchar(100);
    b varchar(100);
    begin
      select data1, data2 into a,b from temp_table where (condition); 
      if a= 1 then --- assume
        --your code
      elsif b=2 then
        --your code
      else
        --your code
      end if;
    end;

answered Nov 07 '17 at 04:20

Sabarish Mahalingam

145
10

Hi Sabarish , ya you are closer to my requirement . My select statement is complex and consist of join,union all,over partition. Do i need to give a,b,c,d, for every column for select statement? – Kate Nov 07 '17 at 04:31
yes, you should give variable to each column then only able to access the variable into your proc. Hope it will help to you.. kindly click up arrow which left top of this answer. your voting will helpful to others search. – Sabarish Mahalingam Nov 07 '17 at 05:23
Or you could use a [record variable](https://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS417). – William Robertson Nov 07 '17 at 08:02
Or you could create an [actual temporary table](https://oracle-base.com/articles/misc/temporary-tables), but note that it is a permanent structure that you don't create on the fly. – William Robertson Nov 07 '17 at 08:06
@WilliamRobertson : actually he is in initial stage of PLSQL.. so using record is quite difficult to him.. that why i preferred this simple way. – Sabarish Mahalingam Nov 07 '17 at 09:15
The trouble is I keep seeing code like this written by beginners because they don’t know it can be done more simply: https://stackoverflow.com/questions/47116220/pls-00394-wrong-number-of-values-in-the-into-list-of-a-fetch-statement. – William Robertson Nov 07 '17 at 09:32
Hi @Sabarish-mahalingam,so i created my simple procedure like this:declare – Kate Nov 08 '17 at 03:55
ok.. thats good. – Sabarish Mahalingam Nov 08 '17 at 04:00
Hi @Sabarish-mahalingam,so i created my simple procedure like this:create procedure ABC( p_name varchar2 p_serial_number varchar2) as name varchar2(20); serial_number varchar2 (20); quantity number; total_quantity varchar2(20) description varchhar(20); begin select ............. from aaa a,bbb b where a.name=p_name and a.serial_number=p_serial_number and b.name=p_name and b.description=a.description; if quantity>0 then DBMS_OUTPUT.PUT_LINE('abc done'); ELSE DBMS_OUTPUT.PUT_LINE('abd not done') ; END IF; END; – Kate Nov 08 '17 at 04:19
but when I tried to call my procedure it returns nothing. can you please tell me what is wrong with my code? – Kate Nov 08 '17 at 04:20

How to make temp table in PL-SQL procedure?

1 Answers1