Oracle three composite type variable analysis

The three complex type variables that are frequently encountered in the actual project. Combine some of the project examples to do a collation, the specific following:

1. Record type: A record type can contain one or more members, and each member can have a different type, and the member may be a scalar type. Other variable types can also be referenced. This type of feature is more appropriate for handling multiple columns in a query statement, and the most common scenario is to call a row of records in a table.

2. Index table type (associative array): Index table type and arrays similar, he uses the key value to find the corresponding value, where the key value is different from the real array subscript, the index table subscript allows the use of strings. The length of the array is not a fixed value and can grow automatically as needed. Where the key value is an integer or a string, where the value is either a normal scalar type or a record type. (Subscript starting from 1)

3. Varray variable length array: The number of elements in a variable-length array needs to be limited, he is a set of stored ordered elements, the index of the array starting from 1, suitable for less data when used.
   

EG1: Record type-declare type pro_x is record  (v_id product_info.id%type, v_name  varchar2 (Ten),  v_price number (8,2))   v_pro pro_x;//declaring variable  v_pro,v_ The data type of Pro is the pro_x type.   begin   select id,name,price into v_pro from product_info  where id= ' 11011211410086 ';    dbms_output.put_line (' ID: ' | | v_pro.v_id| | ', ' | | ' Name: ' | | v_pro.v_name| | ', ' | | ' Price: ' | | V_pro.v_price);   end;       record type declaration method: Type type_name is  record (field_name datatype[not null]{:=|default}expression)   Description:not null  You can constrain a record member to be non-empty.     can be replaced with%rowtype to achieve the above functions. namely: declarev_pro product_info%rowtype; begin   select id,name,price  Into v_pro from product_info where id= ' 11011211410086 ';    dbms_ Output.put_line (' ID: ' | | v_pro.id| | ', ' | | ' Name: ' | | V_pro.name| | ', ' | | ' Price: ' | | V_pro.price);   END;  EG2: Index Table type (associative array)---ID check   declare  type  tiarray is table of integer;  type tcarray is table of  VARCHAR2 (1);  type id_emp is table of qlr_info%type  index  by binary_integer;  rst id_emp;  w tiarray;  a tcarray;   S INTEGER;  TAB VARCHAR2 ($);   zuihyw varchar2 (1);   JIEGUO NUMBER;  SHENFZH VARCHAR2 (;  cursor c is   ) Select zjbh from qlr_info where length (ZJBH) =18 and zjzl= ' identity card '  and  regexp_like (substr (zjbh,1,17), ' ^[0-9]*$ '),//regexp_like (substr (zjbh,1,17), ' ^[0-9]*$ ')   Indicates that the first 17 of a lookup intercept is a string number. ^: match start position; $: match end position; *: match 0 or more times.  begin w:=tiarray (7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2);  a:=tcarray (' 1 ', ' 0 ', ' X ', ' 9 ', ' 8 ', ' 7 ', ' 6 ', ' 5 ', ' 4 ', ' 3 ', ' 2 '); for emc in c loops:=0;shenfzh:=emp.zjbh;for  i in 1..17 loops:=s+to_number (substr (shenfzh,i,1) *w (i)); End loop;jieguo:=s mod  11;zuihyw:=a (jieguo+1); Zuihyw2:=substr (shenfzh,18,1);if  (ZUIHYW<>ZUIHYW2)  thendbms_ Output_line (' ID number: ' emp.zjbh| | ' Error! ') end if;end loop;end;eg2.1 use string as the key value of the Index table Declaretype pro is table of number (8  INDEX BY VARCHAR2, V_pro pro;beginv_pro (' Test '): =253;v_pro (' test1 '): =256;dbms_out.put _line (v_pro.first | | ', ' | | V_pro (V_pro.first)); end; Index Table type declaration: Type type_name is table of {column_type|variable_name %TYPE|TABLE_NAME%ROWTYPE}[NOT NULL] INDEX BY {PLS_INTEGER|BINARY_INTEGER|VARCHAR2 (V_size)} EG3, variable-length array declaretype varr is  varray (10)  of varchar2 (+//) defines the length of the array 10v_pro  Varr:=varr (' 1 ', ' 2 ');//Initializes two elements (up to 10 can be initialized) Beginv_pro (1): = ' haha '; v_prO (2): = ' Ouou ';d bms_output.put_line (V_pro (1) | | | ', ' | | V_pro (2)); end; variable-length array declaration: Type type_name is {varray|varying array} (Size_limit) Of element_ type[not null]                                                                        2016.05.11

This article from the "MST" blog, reproduced please contact the author!

Oracle three composite type variable analysis