• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6565
  • Last Modified:

How to create stored procedure in oracle for select statement?

How to create stored procedure in oracle for select statement?
0
nagaphanir
Asked:
nagaphanir
  • 2
1 Solution
 
sdstuberCommented:
what do you want to do with the select statement?

here's an example of how to iterate through the results of a query.

Note, if you are going to be doing more sql for each row, it will be more efficient to NOT iterate, but instead do your operations on the entire set in one step.
create or replace procedure my_procedure
is
begin
     for x in (select co1,col2 from your_table) loop
          dbms_output.put_line(x.col || ' ' || x.col2);
     end loop;
end;

Open in new window

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
did a small typo in the above :

create or replace procedure my_procedure
is
begin
     for x in (select col,col2 from your_table) loop     --> co1 is here so changed it here as col so
                                                                                   --> that the below dbms_output.put_line works
          dbms_output.put_line(x.col || ' ' || x.col2);
     end loop;
end;
0
 
sdstuberCommented:
oops, coding "freehand" bites me again.
thanks for the correction.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now