CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Solijoli
Solijoli

Reputation: 474

Execute procedure for all rows

I have a code that takes employee_id as the parameter from the table1 and gets the name and nationality of the employees. The code is shown below

CREATE OR REPLACE PROCEDURE emp_info (
  e_id IN table1.employee_id%TYPE
)
IS
  e_name     table1.full_name%TYPE;
  e_nation   table1.nationality%TYPE;
BEGIN
  SELECT full_name, nationality
  INTO e_name, e_nation
  FROM table1
  WHERE employee_id = e_id and rownum = 1; 
  DBMS_OUTPUT.PUT_LINE ('Name=' || e_name || '  Country=' || e_nation);

  EXCEPTION
  WHEN OTHERS
  THEN
  DBMS_OUTPUT.PUT_LINE ('No data found for ' || e_id);
END;

The procedure works but the problem is at executing the procedure. I want all the values at the same time. How can i do that? I think it has to do something with Loop Cursor's but I could not manage it.

Also, you see the

   rownum = 1

line. I actually wanted it to skip the parts with multple values. How can i do that? Thanks.

Upvotes: 0

Views: 906

Answers (2)

StevieG
StevieG

Reputation: 8709

You should use a cursor for this. Also, you can extend it slightly to return either all employees OR just the one. If you want one specific record, then pass the id to the proc. If you want all records, pass NULL..

CREATE OR REPLACE PROCEDURE emp_info(e_id IN table1.employee_id%TYPE)
AS

  CURSOR c_employees(e_id IN table1.employee_id%TYPE) IS
    SELECT full_name, nationality
    FROM table1
    WHERE employee_id = e_id OR e_id IS NULL;

  r_employees c_employees%rowtype;

BEGIN

  OPEN c_employees(e_id);
    LOOP
    FETCH c_employees into r_employees;
    EXIT WHEN c_employees%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE ('Name=' || r_employees.full_name || '  Country=' || r_employees.nationality);      

    END LOOP;
  CLOSE c_employees;

END;

Then do:

EXEC emp_info(1);

to return just 1 record (for employee_id 1). Or

EXEC emp_info(NULL);

to return all records.

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

It appears that you want to use a cursor to read and process all the rows in the table which match the selection criteria. Something like the following may be of use:

CREATE OR REPLACE PROCEDURE emp_info (
  e_id IN table1.employee_id%TYPE
)
IS
BEGIN
  FOR aRow IN (SELECT full_name, nationality
                 FROM table1
                 WHERE employee_id = e_id)
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Name=' || aRow.FULL_NAME ||
                          '  Country=' || aRow.NATIONALITY);
  END LOOP;  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('No data found for ' || e_id);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' ' || SQLERRM);
END;

Share and enjoy.

Upvotes: 1

Related Questions