This content has been marked as final. Show 7 replies
Welcome to the forum!
976530 wrote:Sure, you can do a query inside a procedure. You have to set up something to catch the results. Here's a simple example:
Hello, i would like to know how can i do to make a procedure that do this:
select * from car ( the table have two column id and name)
You didn't post your cars table, so I used the scott.emp table (which most people have available) instead.
SET SERVEROUTPUT ON CREATE OR REPLACE PROCEDURE show_dept AS BEGIN FOR r IN ( SELECT * FROM scott.dept ) LOOP dbms_output.put_line ( r.deptno || ' = deptno, ' || r.dname || ' = dname in show_dept' ); END LOOP; END show_dept; / SHOW ERRORS EXEC show_dept;
Why are you considering a procedure? Why not just run the query in SQL? What is the business problem you're trying to solve?
10 = deptno, ACCOUNTING = dname in show_dept 20 = deptno, RESEARCH = dname in show_dept 30 = deptno, SALES = dname in show_dept 40 = deptno, OPERATIONS = dname in show_dept
SQL> create or replace procedure p (c out sys_refcursor) as 2 begin 3 open c for 4 select object_id, object_name from all_objects 5 where object_name = 'DUAL'; 6 end; 7 / Procedure created. SQL> var c refcursor SQL> exec p(:c) PL/SQL procedure successfully completed. SQL> print c OBJECT_ID OBJECT_NAME ---------- ------------------------------ 116 DUAL 117 DUAL
I can do the query but i need to know how can i setup a procedure to do that query and manage the output
What output? You haven't said what output you want. Do you want a result set from the procedure or just one set of values?
Provide an example showing the input you will provide and the output you want from the procedure.
976530 wrote:3360's posting showed the correct way on how to use PL/SQL to construct a cursor on behalf of a client.
I can do the query but i need to know how can i setup a procedure to do that query and manage the output, thank for replying.
If that does not meet your requirements, then please explain the requirements.