sql - How to set an object as an out parameter? -
i have written procedure in pl/sql , want return emp type object. possible that? if yes, how can it?
here code:
create or replace procedure get_emp_rs (p_deptno in emp.deptno%type, p_recordset out emp_det) emp_details emp_det; begin open p_recordset select ename, empno emp deptno = p_deptno order ename; fetch p_recordset emp_details; --exit when p_recordset%notfound; --end loop; --for indx in p_recordset --loop emp_details.ename:= 'test'; --end loop; end get_emp_rs; / set serveroutput on size 1000000 declare l_cursor emp_det; --l_cur emp_det; --l_ename emp.ename%type; --l_empno emp.empno%type; l_deptno emp.deptno%type; begin l_cur:=get_emp_rs ('30', l_cursor); dbms_output.put_line('low'); /*loop fetch l_cursor l_ename, l_empno, l_deptno; exit when l_cursor%notfound;*/ dbms_output.put_line(l_cursor.ename || ' | ' || l_cursor.empno); end; / i want ename , empno after update in procedure. how can it? if there better way of doing please suggest me.
and please suggest me how can in way. cannot use functions here that's obligation. please let me know if there way of doing using.
in example (table emp) single emp record shouldn't selected department number. use empno instead.
the following quick solution , self-explainable:
set serveroutput on; set feedback off; clear; --define dataset object create type emp_det object ( empno number(4), ename varchar2(10) ); / --define collection of dataset objects create type emp_det_list table of emp_det; / --get single record out-variable identified emp pk empno create or replace procedure get_emp_rs(p_empno in emp.empno%type, p_recordset in out emp_det) begin --create return object inside sql select emp_det(empno, ename) p_recordset emp empno = p_empno; p_recordset.ename := 'test'; exception when no_data_found --return null if employee not found p_recordset := null; end get_emp_rs; / --get list of employees department create or replace procedure get_emp_list(p_deptno in emp.deptno%type, p_recordlist out emp_det_list) type c_cursor ref cursor; -- <-- explicit cursor solution c_emp_rs c_cursor; -- <-- explicit cursor solution v_rs emp_det; -- <-- explicit cursor solution begin --initialize out object p_recordlist := emp_det_list(); --create return object inside sql --via bulk collect /* select emp_det(empno,ename) bulk collect into p_recordlist emp deptno = p_deptno; */ --with manipulation of records --use for-loop implizit cursor /* l_rs in (select emp_det(empno, ename) emp_rs emp deptno = p_deptno) loop l_rs.emp_rs.ename := 'test'; p_recordlist.extend; p_recordlist(p_recordlist.last) := l_rs.emp_rs; null; end loop; */ --or define explicit cursor , loop-fetch open c_emp_rs select emp_det(empno, ename) emp_rs emp deptno = p_deptno; loop fetch c_emp_rs v_rs; exit when c_emp_rs%notfound; v_rs.ename := 'test'; p_recordlist.extend; p_recordlist(p_recordlist.last) := v_rs; end loop; close c_emp_rs; end get_emp_list; / --************************** -- test --************************** declare l_cursor emp_det; l_list emp_det_list; begin dbms_output.put_line('---- single emp ----'); get_emp_rs(7369, l_cursor); if (l_cursor not null) dbms_output.put_line(l_cursor.ename || ' | ' || l_cursor.empno); end if; dbms_output.put_line('---- emp list ----'); get_emp_list(30, l_list); if (l_list.count > 0 ) l_i in l_list.first .. l_list.last loop dbms_output.put_line(l_list(l_i).ename || ' | ' || l_list(l_i).empno); end loop; end if; end; / drop procedure get_emp_rs; drop procedure get_emp_list; drop type emp_det_list; drop type emp_det; output:
---- single emp ---- test | 7369 ---- emp list ---- test | 7499 test | 7521 test | 7654 test | 7698 test | 7844 test | 7900 sql> 
Comments
Post a Comment