CODEWITHSUNDEEP
oracle-databaseplsql
B. Aly
B. Aly

Reputation: 3

How to create a procedure that shows a table (select * from emp)

I want to create a procedure on PL/SQL that only shows the (select * from emp) from my SQL table.

I am learning about procedures and I can basically change and update the table, but I want to show the modifications I made.

Upvotes: 0

Views: 865

Answers (4)

ᎮᏒᏗᏉᏋᏋᏁ
ᎮᏒᏗᏉᏋᏋᏁ

Reputation: 29

**Try This **

CREATE OR REPLACE FUNCTION DBFUN_RETURN_TABDATA RETURN SYS_REFCURSOR IS
REC_TAB SYS_REFCURSOR;
BEGIN
    OPEN REC_TAB FOR SELECT * FROM SCOTT.EMP;
    RETURN REC_TAB;
END;

Upvotes: 0

thatjeffsmith
thatjeffsmith

Reputation: 22467

Or just use SQL.

select * from emp

Adding PL/SQL to the mix doesn't really get you anything. If anything, it could make things harder/slower.

In SQL Server it's quite common to use T-SQL procedures to nothing but selects and return query results. This is not so common in Oracle.

You most definitely CAN use PL/SQL to execute SQL - but then you would generally do something WITH those results, with the logic in your PL/SQL. And then you would do something with those results - perhaps put them in a new object/table, or return a subset of them with a message to the caller of your PL/SQL program.

But if you are truly ONLY running a SQL and you want to get the results, then see Littlefoot's answer. Just be aware that going from PL/SQL to SQL, there's often a context switch at the Oracle optimizer/engine level, and there could be a performance hit.

To your question - how to see JUST the changes you made. See the RETURNING clause. There are some good examples here that show how to make changes AND get back said changes all in one code block..with plsql. In particular see statement/example 7.

Upvotes: 4

Connor McDonald
Connor McDonald

Reputation: 11616

If you're just getting started, then DBMS_OUTPUT is your friend to output anything from the database to your client program.

So you might just to just loop around the rows in your table, eg

SQL> set serverout on
SQL> begin
  2    for i in ( select * from emp )
  3    loop
  4      dbms_output.put_line(i.empno||'-'||i.ename);
  5    end loop;
  6  end;
  7  /
7369-SMITH
7499-ALLEN
7521-WARD
7566-JONES
7654-MARTIN
7698-BLAKE
7782-CLARK
7788-SCOTT
7839-KING
7844-TURNER
7876-ADAMS
7900-JAMES
7902-FORD
7934-MILLER

PL/SQL procedure successfully completed.

But, if you're starting out on the PL/SQL journey its time to grab a book on it, or head on over to devgym.oracle.com and livesql.oracle.com for assistance and tutorials.

Welcome to the PL/SQL club!

As Jeff said, when you get into some "real" programming, it can an easy temptation to use PL/SQL code where simply a SQL statement would do. So once you've learned some more PLSQL, ensure you don't use it to "reinvent the wheel" so to speak. PL/SQL is used to extend what SQL can, not replace it.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143073

Here's one option - ref cursor returned as an OUT parameter.

SQL> create or replace procedure p_test (par_out out sys_refcursor) as
  2  begin
  3    open par_out for select * From dept;
  4  end;
  5  /

Procedure created.

SQL> var rc refcursor
SQL>
SQL> exec p_test(:rc);

PL/SQL procedure successfully completed.

SQL> print rc

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON

SQL>

Upvotes: 3

Related Questions