Oracle function return table Type

Oracle function return table Type 1. Use a custom type to implement 1. Create a table object type. To return a table object in Oracle, you must define a table type as follows: SQL code create or replace type type_table is table of number, when using a function to return a table with one column available, we will introduce how to return multiple columns later. 2. Create a function in the function definition. You can use a canonicalized table function and a common method, the following code provides two usage methods: 1) pipeline Table function Method: SQL code create or replace function f_pipe (s number) return type_table pipelined as begin for I in 1 .. s loop pipe row (I); end loop; return; end f_pipe; Note: Empty return must be used to end the pipeline. the method for calling a function is as follows: 2) Common method: SQL code create o R replace function f_normal (s number) return type_table as rs type_table: = type_table (); begin for I in 1 .. s loop rs. extend; rs (rs. count): = I; end loop; return rs; end f_normal; call method: SQL code select * from table (f_normal (5); If multiple columns are required, you must first define an object type. You can replace the object type with the number in the preceding statement. Define the object type: SQL code create or replace type type_row as object (id int, name varchar2 (50 )) the Definition Statement for modifying the table object type is as follows: SQL code create or replace type type_table is table of type_row; 1), pipeline table function Method: SQL code create or replace function f_pipe (s number) return type_table pipelined as v_type_row type_row; begin for I in 1 .. s loop v_type_row: = type_row (I, to_char (I * I); pipe row (v_type_row); end loop; return; end f_pipe; test: select * from table (f_pipe (5); 2) normal method: SQL code create or replace function f_normal (s number) return type_table as rs type_table: = type_table (); begin for I in 1 .. s loop rs. extend; rs (rs. count): = type_row (rs. count, 'name' | to_char (rs. count); -- Result (Result. count): = type_row (NULL, NULL); -- rs (rs. count ). name: = rs (rs. count ). name | 'xxxx'; end loop; return rs; end f_normal; test: select * from table (f_normal (5); Other code segments grant permissions (and) SQL code create or replace type type_table_number is table of number; create or replace function f_right_table (rights number) -- custom table type -- pipelined pipe keyword return type_table_number pipelined as begin -- call method: select column_value as right from table (f_right_table (power () * 2-2 )); for I in 1 .. 15 loop IF bitand (rights, power (2, I) = power (2, I) THEN pipe row (power (2, I); -- specific method of pipe row, output record end if; end loop; return; end f_right_table; a method for Traversing data, just to demonstrate myrow, myrow is equivalent to a temporary variable Java code for myrow in (select 2 as right from dual where bitand (rights, 2) = 2 union select 4 as right from dual where bitand (rights, 4) = 4) loop rs. extend; rs (rs. count): = myrow. right; end loop; 2. other Implementation Packages use a stored procedure and return the cursor.> package Definition 1) Baotou SQL code create or replace package mypk as type t_cursor is ref cursor; procedure proc (name varchar2, c out t_cursor, a number); end; 2) package SQL code create or replace package body mypk as procedure proc (name varchar2, c out t_cursor, a number) as begin open c for select * from test where id = a and name = name; end proc; end; this solution has too many limitations and cannot implement select * from function () required call: SQL code declare cur_out_arg mypk. t_cursor; rec_arg test % rowtype; begin mypk. proc ('abc', cur_out_arg, 3); -- open cur_out_arg; loop -- extract a row of data to rec_arg fetch cur_out_arg into rec_arg; -- determine whether to extract the value, exit if no value is obtained. The value cur_out_arg % notfound is false. The value cur_out_arg % notfound is true exit when cur_out_arg % notfound; dbms_output.put_line (rec_arg.id | '-' | rec_arg.name); end loop; -- close the cursor close cur_out_arg; end;