一、取函數的返回值
1.取函數的系統類型數據
2.取函數的自定義類型數據
3.取函數的自定義類型數據集合
二、取存儲過程的返回值
1.取存過的系統類型數據
2.取存過的自定義類型數據
3.取存過的自定義類型數據集合
--自定義類型 create or replace type mytype as object ( str1 number, str2 varchar2(10) ); --自定義類型集合 create or replace type mytype_list as table of mytype;
--1函數 返回字符串 create or replace function fun_ceshi1(num1 number, num2 number) return varchar2 as str1 varchar2(111); begin str1 := '測試時。。。'; return str1; end; --取值 select fun_ceshi1(1,1) from dual;
--2函數 返回自定義類型 create or replace function fun_ceshi2(num1 number, num2 number) return mytype as my mytype; begin select mytype(ename1, ename2) into my from (select t.empno ename1, t.ename ename2 from emp t where rownum=1); return my; end; -- select fun_ceshi2(1,1) from dual;
--3函數 返回自定義類型集合 create or replace function fun_ceshi3(num1 number, num2 number) return mytype_list as my mytype_list; begin select mytype(ename1, ename2) BULK COLLECT into my from (select t.empno ename1, t.ename ename2 from emp t); return my; end; -- select * from table(fun_ceshi3(1,1));
--1存過 返回字符串 create or replace procedure pro_ceshi1(num1 number, num2 number, num3 out varchar2) as begin select '字符串num' into num3 from dual; end; --調用 declare num3 varchar2(111); begin pro_ceshi1(1, 1, num3); dbms_output.put_line(num3); end;
--2存過 返回自定義類型 create or replace procedure pro_ceshi2(num1 number, num2 number, strType out mytype) as begin select mytype(ename1, ename2) into strType from (select t.empno ename1, t.ename ename2 from emp t where rownum=1); end; --調用 declare strType mytype; begin pro_ceshi2(1, 1, strType); dbms_output.put_line(strType.str1 || ' ' || strType.str2); end;
--3存過 返回自定義類型集合 create or replace procedure pro_ceshi3(num1 number, num2 number, strType out mytype_list) as begin select mytype(ename1, ename2) BULK COLLECT into strType from (select t.empno ename1, t.ename ename2 from emp t); end; --調用 declare strType mytype_list; cursor c_c is select * from table(strType); begin pro_ceshi3(1, 1, strType); for v_r in c_c loop dbms_output.put_line(v_r.str1 || ' ' || v_r.str2); end loop; end;