CODEWITHSUNDEEP
oracle-databaseplsql
Sean
Sean

Reputation: 1

Writing a procedure with an array as out parameter

I can't create a PL/SQL stored procedure. For example returns all of the ename of employees in deptno 20.

  create table emp(
  empno    number(4,0),
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4,0),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2,0),
  constraint pk_emp primary key (empno),
  constraint fk_deptno foreign key (deptno) references dept (deptno)
);

This is what i manage to do but i can't seem to move/write it into a procedure.

declare
      TYPE name_array IS TABLE OF emp.ename %type
       INDEX BY BINARY_INTEGER;

      enames name_array;
    begin
      update emp set empno = empno where deptno = 20
      returning ename bulk collect into enames;
      for i in 1..enames.count loop
     dbms_output.put_line('ename : '||enames(i));
     end loop;

    end;
    /

Upvotes: 0

Views: 1972

Answers (2)

Brian Leach
Brian Leach

Reputation: 2101

When returning a single object (which includes a table of values), I prefer functions over out parameters. Here is code that implements both.

drop table emp purge;
drop table dept purge;
CREATE TABLE dept
(
    deptno    INTEGER PRIMARY KEY
);

CREATE TABLE emp
(
    empno NUMBER (4, 0)
  , ename VARCHAR2 (10)
  , job VARCHAR2 (9)
  , mgr NUMBER (4, 0)
  , hiredate DATE
  , sal NUMBER (7, 2)
  , comm NUMBER (7, 2)
  , deptno NUMBER (2, 0)
  , CONSTRAINT pk_emp PRIMARY KEY (empno)
  , CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
);

CREATE or replace PACKAGE emp_pkg
AS
    TYPE name_array_t IS TABLE OF emp.ename%TYPE
        INDEX BY BINARY_INTEGER;

    FUNCTION upd (p_empno IN emp.empno%TYPE)
        RETURN name_array_t;

    PROCEDURE upd (
        p_empno   IN     emp.empno%TYPE
      , p_names      OUT name_array_t
    );
END emp_pkg;

CREATE or replace PACKAGE BODY emp_pkg
AS
    FUNCTION upd (p_empno IN emp.empno%TYPE)
        RETURN name_array_t
    AS
        l_names   name_array_t;
    BEGIN
           UPDATE emp
              SET empno   = empno
            WHERE deptno = 20
        RETURNING ename
             BULK COLLECT INTO l_names;

        RETURN l_names;
    END upd;

    PROCEDURE upd (
        p_empno   IN     emp.empno%TYPE
      , p_names      OUT name_array_t
    )
    AS
    BEGIN
        p_names   := upd (p_empno);
    END upd;
END emp_pkg;

Upvotes: 1

nitin.sharma0180
nitin.sharma0180

Reputation: 471

This is a sample Stored Proc with return type as Array create 

procedure sample_proc(p_cust_id in number, p_customers out custarray)
as
my_cust custarray := custarray();

Upvotes: 0

Related Questions