Select statement inside a Procedure gives me a error, why?-CodePudding

I'm new to pl/SQL, and I'm trying to create a procedure that displays the values from the table 'BUS'. Please help me out here. I'm using SQL*Plus as a command-line interface tool for Oracle Database 10g Enterprise Edition Release 10.2.0.1.0.

SQL> create or replace procedure get_bus_table
  2  in
  3  begin
  4  select * from bus;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

CodePudding user response：

Use:

select * from bus;

and do not use a procedure.

If you want to use a procedure then you will need to SELECT into something; that could be variable(s), collection(s) or a cursor. For example:

CREATE PROCEDURE get_bus_table(
  o_cur OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN o_cur FOR
    select * from bus;
END get_bus_table;
/

Then, when you call the procedure, you can process the returned cursor.

db<>fiddle here

CodePudding user response：

There are various errors (there's no in but is or as; PL/SQL requires an INTO clause, but that probably wouldn't help here; you didn't say how would you want to display data contained in the table).

Sample data:

SQL> select * from bus;

   ROUTENO SOURCE  DESTINATION
---------- ------- -----------
         1 chennai bangalore
       101 PUNE    CHENNAI
       102 PUNE    BANGALORE
       103 CHENNAI PUNE

Here's one option: a procedure that loops through the table and uses dbms_output.put_line:

SQL> create or replace procedure get_bus_table is
  2  begin
  3    for cur_r in (select routeno, source, destination
  4                  from bus)
  5    loop
  6      dbms_output.put_line(cur_r.routeno ||' - '|| cur_r.source ||' - '||
  7                           cur_r.destination);
  8    end loop;
  9  end;
 10  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> begin
  2    get_bus_table;
  3  end;
  4  /
1 - chennai - bangalore
101 - PUNE - CHENNAI
102 - PUNE - BANGALORE
103 - CHENNAI - PUNE

PL/SQL procedure successfully completed.

SQL>

Or, maybe you wanted to use a function (instead of a procedure):

SQL> create or replace type t_row as object
  2    (routeno     number,
  3     source      varchar2(20),
  4     destination varchar2(20));
  5  /

Type created.

SQL> create or replace type t_tab as table of t_row;
  2  /

Type created.

SQL> create or replace function f_get_bus_table
  2    return t_tab
  3  is
  4    l_tab t_tab;
  5  begin
  6    select t_row(routeno, source, destination)
  7      bulk collect into l_tab
  8      from bus;
  9    return l_tab;
 10  end;
 11  /

Function created.

Let's try it:

SQL> select * from table(f_get_bus_table);

   ROUTENO SOURCE               DESTINATION
---------- -------------------- --------------------
         1 chennai              bangalore
       101 PUNE                 CHENNAI
       102 PUNE                 BANGALORE
       103 CHENNAI              PUNE

SQL>

There's also a ref cursor procedure in another answer to your question; I suggest you check it.