Shulou(Shulou.com)06/01 Report--

There are three kinds of compound type variables that are often encountered in actual projects. Sort it out with some project examples, as follows:

Record type: a record type can contain one or more members, and each member can have a different type, and the member can be a scalar type. You can also reference other variable types. The characteristic of this type is that it is more suitable for dealing with situations where there are multiple columns in a query statement, such as when calling a row record in a table.

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

VARRAY variable length array: the number of elements in a variable length array needs to be limited. It is a collection of ordered elements. The subscript of the array starts at 1 and is suitable for use with less data.

Eg1: record type-declare type pro_x is record (v_id product_info.id%type, v_name varchar2 (10), v_price number (8Magne2)); the data type of v_pro pro_x;// declaration variable is 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 can constrain record members to be non-empty. You can use% rowtype instead to achieve the above functionality. 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)-× × 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 (20); cursor c is select zjbh from qlr_info where length (zjbh) = 18 and zjzl=' residents × × 'and regexp_like (substr (zjbh,1,17),' ^ [0-9] * $') / / regexp_like (substr (zjbh,1,17),'^ [0-9] * $') indicates that the first 17 strings intercepted by the lookup are numbers. ^: match start position; $: match end position; *: match zero or more times. Begin W:=TiArray (7, 9, 10, 5, 10, 5, 5, 5, and 4); A:=TcArray (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 (jieguo+1) Zuihyw2:=substr (shenfzh,18,1); if (zuihywzuihyw2) thendbms_output_line ('ID No.: 'emp.zjbh | |' error!') End if;end loop;end;eg2.1 uses an index table declaretype pro is table of number (8) index by varchar2 (20) with a string as a key; v_pro pro;beginv_pro ('test'): = 253 v_pro.first pro (' test1'): = 256 v_pro.first dbmsroomout.putSecretline (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 (10); / / defines the array length 10v_pro varr:=varr ('1'); / / initializes two elements (up to 10) beginv_pro (1): = '' V_pro (2): = 'ouou';dbms_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] May 11, 2016

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.