7 Replies Latest reply: Dec 12, 2012 1:28 PM by Billy~Verreynne RSS

    Procedure

    979533
      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)

      thanks
        • 1. Re: Procedure
          LPS
          What do you want to do by the procedure....

          select * from car.....
          • 2. Re: Procedure
            Frank Kulash
            Hi,

            Welcome to the forum!
            976530 wrote:
            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)

            thanks
            Sure, you can do a query inside a procedure. You have to set up something to catch the results. Here's a simple example:
            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;
            You didn't post your cars table, so I used the scott.emp table (which most people have available) instead.
            Output:
            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
            Why are you considering a procedure? Why not just run the query in SQL? What is the business problem you're trying to solve?
            • 3. Re: Procedure
              6363
              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
              • 4. Re: Procedure
                979533
                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.
                • 5. Re: Procedure
                  rp0428
                  >
                  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.
                  • 6. Re: Procedure
                    979533
                    something like this:

                    select * from table_car

                    id name

                    1 ford
                    2 chevrolet
                    3 nissan
                    4 toyota
                    • 7. Re: Procedure
                      Billy~Verreynne
                      976530 wrote:
                      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.
                      3360's posting showed the correct way on how to use PL/SQL to construct a cursor on behalf of a client.

                      If that does not meet your requirements, then please explain the requirements.