Oracle Stored Procedure exercise seven array topics

Nonsense written in front

Since the work needs to be frequently used in Oracle stored procedures, arrays are an inevitable element, so let's take a look at how Oracle stored procedures use arrays to a low level ....

The array is a data type. You need to create and declare the following statements.

 
Create Or ReplaceType myvarray_listAsVarray (10)Of Varchar2(50)
 

1. The array is used as the input parameter of the process.

 

 Create   Or   Replace   Procedure  Show_list_p ( /*  Used to display Arrays  */  P_varlist  In  Myvarray_list)  Is  Begin   For X In   1 ... P_varlist. Count  Loop dbms_output.put_line (  '  P_varlist (  ' | X |  '  ) =  '  |  P_varlist (x ));  End  Loop;  End ;

 --CallCall show_list_p (myvarray_list ('Oracle','DB2','SQL Server','MySQL','DBA'));
 

 

2 array as function return value

 Create   Or   Replace  Function Output_list_p Return  Myvarray_list  --  Variable arrays are called as output parameters in PL/SQL stored procedures:  As  O_data myvarray_list:  =  Myvarray_list ();  Begin    For V_count In   1 .. 5 Loop o_data.extend; o_data (v_count ):  =  V_count;  --  Dbms_output.put_line ('list ('| v_count |') = '| o_data (v_count ));      End  Loop;  Return  O_data;  End Output_list_p;

 --  Call  Declare  V_list myvarray_list; v_count  Number ;  Begin  Select Output_list_p () Into V_list From  Dual;  For V_count In   1 ... V_list. Count  Loop dbms_output.put_line (  '  V_list (  '  | V_count|  '  ) =  '  |  V_list (v_count ));  End  Loop;  End ;