select stored proc plsql

Question

I'm a bit confused by the stored procedure syntax in Oracle.

I started with a simple:

select * from test_table;

It works, then I put it in a proc:

CREATE OR REPLACE PROCEDURE example
IS
BEGIN
   select * from test_table;
END;

Doesn't work. Expected "INTO" is the error message I get. Now, I've seen syntax examples of SQL Server code that just shoves a select statement into a proc and it works instantly, but that doesn't seem to be the case here.

Answer 1

T-SQL and PL/SQL are completely different languages. In particular, for PL/SQL you have to select the result into some variable or cursor. Depending on what you plan to do with the record data - process in the procedure - or return to the caller, will drive what you have to do.

In your example, if you want to return the record set, you would do something like this:

CREATE OR REPLACE PROCEDURE example (
                      p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    select * from test_table;
END example ;

See this link for examples.