Use the table Type of pl SQL as the return value

SQL server has been used before oracle is used. What impressed me most during the use of SQL server was its strong select statement and record concentration. The results of a select statement can be used as both a record set and a set. While the select statement acts as a query statement, it can also be used as a value assignment and computing statement. This makes it easy to write SQL statements in analyzer. You do not have to write a stored procedure, define variables, and then write a series of SQL statements to complete complex functions between tables. When the statement needs to be reused, the statement can be defined as a stored procedure with the declaration, which is convenient and flexible.

After oracle is used, the situation changes a bit. The role of the select statement is subject to many restrictions.
Select @ a = @ a + fchar form table1;
This statement is correct in SQL server. @ a returns the result of connecting the values of the fchar column of all records in Table 1, but it is not allowed in oracle. To get the same result, you can only use a cursor.

In addition, if SQL server Functions and stored procedures contain select statements, they will directly return the record set. However, the select statement of the record set cannot be returned in oracle functions and stored procedures. Therefore, to return the record set, you must use a dynamic cursor or set type.

If a cursor is returned by a function or stored procedure, on the one hand, it is prone to type security issues. On the other hand, it is also troublesome to observe the results after the SQL window of PL/SQL Developer is upgraded. Therefore, returning a set is a good choice. The pl SQL table type is well-used in the collection.

The following is a function example that uses the table type as the return value:

1 -- create a global type
2 --------------- create object type objrow
3 create type objrow as object (fid number, title varchar2 (400 ));
4 ------------------------------------------------ create a table type based on the objrow type
5 create or replace type ty_List as table of objrow;
6 -------------------------------------------------- create a table for the test
7 create table t_L (
8fid number,
9fT varchar2 (400)
10 );
11 ------------------------- insert some records as test data
12 declare
13v_ I number;
14 begin
15v_ I: = 0;
16
17 while (v_ I <10)
18 loop
19 insert into t_L (Fid, Ft) values (v_ I, 'ttt' | v_ I );
20v_ I: = V_ I + 1;
21end loop;
22
23end;
24 -------------------------------------------------- create a function that returns the ty_List table type.
25 create or replace function get_TList
26 return ty_List
27 -- declare the ty_List table type as the return value
28is
29 -- declare the ty_List table type variable and initialize it
30 v_list ty_list: = ty_List ();
31
32 begin
33
34 -- query the test table and put the results in the table type. Pay attention to the bulk collect keyword.
35 select new objrow (fid, fT) bulk collect into v_list from t_L;
36
37 return v_list;
38end;
39 -------------------------------------------------- query the records of the test table
40 select * from t_L;
41 ---------------------------------------- call the function get_TList
42 ---------- use the table type returned by get_TList as the table Type through the table () function
43 select * from table (get_tlist ());
44 -- execute this statement. You can see that the return value of the function is a set.
45 select get_tlist () from dual;

As you can see from the code, using the table () function in combination with the function or stored procedure that returns the table type can flexibly nest the computing result as a record set into the select statement, to associate with a real table or combine multiple computing results. In this way, the select statement can be used to obtain more complex computing results.

If you want to obtain the results of a select statement when running batch SQL using declare in the SQL window, you can use the following method: 1 declare
2 TYPE TYPE_STATION is record (
3fid number,
4 title varchar2 (400)
5 );
6 type v_ty_List is table of TYPE_STATION;
7 v_list v_ty_List: = v_ty_List ();
8 v_ I number;
9 begin
10
11 select fid, fT bulk collect into v_list from t_L;
12
13 for v_ I in 1 .. v_list.count loop
14 dbms_output.put_line (v_list (v_ I). fid | v_list (v_ I). title );
15 end loop;
16end;

The principle is similar to the principle used by the above functions. Let alone too much.

So far, I still have no way to understand that the select statement with variables as the condition cannot be called on the SQL window of PL/SQL Developer .....
On SQL server, the following result sets are queried through variables:
Declare @ I int;
Set @ I = 1;
Select * from table1 where id = @ I;
In this way, you can get different query results by changing the value of the variable. But in oracle,
Declare
V_ I number;
Begin
V_ I: = 1;
Select * from table1 where id = v_ I;
End;
Obviously, this statement is not feasible. in oracle, how can we call the select statement with variables and return results?
In fact, this is what I want to solve most. The result is not resolved. Instead, it takes one night for the returned table type and select * from table. Below are some articles I have read. Thank you for your selfless dedication! Xi

Use of the combination of a nested table of the record type and bulk bind
Http://flysky0814.itpub.net/post/35477/419397

Summary of oracle pl/SQL record

Http://bigboar.itpub.net/post/8411/52348

PL/SQL table --- table () function usage ----------- I am a little depressed in this article. It writes something the same as what I wrote here, but I have never copied it, because I found it only when I wrote about half of the materials. However, the author is really amazing. The article solved the problem of creating global table variables. In the past, the declaration of global table variables was not followed by index declaration such as index by binary_integer... 5555

Http://www.itpub.net/617298,1.html

Using PL/SQL table to construct a continuous data set table ------ this article is very enlightening for using table Variables

Http://blog.itpub.net/post/6/30895

Application of ORACLE array in the process

Http://itpub120.itpub.net/post/25009/234635

Oracle user manual (1) --- declare Variables

Http://www.cnblogs.com/furenjun/archive/2006/07/04/442482.html