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
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.