GENERATE A SELECT WITHIN A PROCEDURE

Asked

Viewed 69 times

1

Good evening, everyone,

I’m trying to make a select within an ORACLE database, only I’m not getting it. Below is an example of code I’m trying to generate.

CREATE OR REPLACE PROCEDURE TRAZNOMES IS
BEGIN
    SELECT * FROM EMP;
END;
/

I’m very grateful, in case you could try to help me.

Sincerely yours.

sql oracle oracle11g

  • What do you want to do with this select? Take a specific value, loop it?

  • I want to read all the fields that exist in this table.

  • All fields and records?

  • The fields in the table are EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

  • That’s right, all fields and records.

  • There are many introductory tutorials about plsql, read one of them , it seems to me that vocêuma FUNCTION , tip a "select" in pl-sql needs to return one and only line, otherwise use cursors. This one sounds good https://www.docsity.com/pt/programando-prodedualmente-em-pl-sql/4733270/

Show 1 more comment

1 answer

0


When you need to make one select within a procedure and loop it, usually working with a cursor, and the same needs to be stated within your procedure, see an example using your procedure and select:

CREATE OR REPLACE PROCEDURE TRAZNOMES IS
    CURSOR C_EMP IS
        SELECT * FROM EMP;
BEGIN
   FOR XEMP in C_EMP LOOP
      DBMS_OUTPUT.PUT_LINE(XEMP.ENAME);
   END LOOP;
END;
/

This example the cursor opens with a syntax similar to a foreach, the variable XEMP becomes the object to access the cursor fields.

  • Very grateful for the explanation. I’ll take a closer look at how to work with a cursor.

Browser other questions tagged sql oracle oracle11g

You are not signed in. Login or sign up in order to post.