CREATE OR REPLACE TYPE emp_type
AS OBJECT (EMPNO NUMBER(4,0),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
HIREDATE DATE,
SAL NUMBER(7,2),
DEPTNO NUMBER(2,0));
/
CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type;
CREATE OR REPLACE FUNCTION get_emp_tab
RETURN emp_tab_type PIPELINED IS
BEGIN
FOR rec IN (SELECT * FROM emp) LOOP
PIPE ROW (emp_type(
rec.empno,
rec.ename,
rec.job,
rec.hiredate,
rec.sal,
rec.deptno)) ;
END LOOP;
END;
/
SELECT * FROM table(get_emp_tab);
|
CREATE OR REPLACE FUNCTION get_emp (p_deptno INT)
RETURNS TABLE (
empno scott.emp.empno%TYPE,
ename scott.emp.ename%TYPE,
job scott.emp.job%TYPE,
hiredate scott.emp.hiredate%TYPE,
sal scott.emp.sal%TYPE
deptno scott.emp.deptno%TYPE
)
AS $$
BEGIN
RETURN QUERY
SELECT e.empno,
e.ename,
e.job,
e.hiredate,
e.deptno
FROM scott.emp e
WHERE e.deptno = p_deptno ;
END; $$
LANGUAGE 'plpgsql';
select * from get_emp(10);
|