Oracle complex data types (1)

Oracle Data complex type (1) 1. pls_integer ------ this type is calculated by the cpu. Some overflow may cause errors, but the speed is fast. binary_integer --- It is executed by Oracle and will not overflow, but the execution speed is slow, because it is simulated by Oracle
Stable execution. The only difference between PLS_INTEGER and BINARY_INTENER is that when computing overflow occurs,
BINARY_INTENER type variables will be automatically assigned to a NUMBER type without errors, PLS_INTEGER type
. Www.2cto.com 2. use % type to reference the data type in a table. Java code declare v_sal emp. sal % type; v_ename emp. ename % type; begin select t. sal into v_sal from emp t where rownum = 1; select t. ename into v_ename from emp t where rownum = 1; dbms_output.put_line ('v _ sal ====' | v_sal ); dbms_output.put_line ('v _ ename = '| v_ename); end; 3. assign a value to multiple variables at a time: www.2cto.com Java code declare v_sal emp. sal % type; [list] [*] [/list] v_ename emp. ename % type; begin select t. sal, t. ename into v_sal, v_ename from emp t where rownum = 1; dbms_output.put_line ('v _ sal ==== '| v_sal ); dbms_output.put_line ('v _ ename = '| v_ename); end; 4. use % rowtype to reference the type of an existing table column. Java code declare v_sal emp. sal % type; v_ename emp. ename % type; v_emp emp % rowtype; ---- reference a column using rowtype begin select * into v_emp from emp t where rownum = 1; ---- Here only *, you cannot assign values to certain
Column (1) dbms_output.put_line ('v _ emp. ename = '| v_emp.ename); --- used variable. the column name can be set to end; 5. A data type set is defined through record (The problem in comment (1) can be solved) java code declare type emp_record is record (---- defines a type named emp_record, similar to the struct v_ename emp in C language. ename % type, v_sal emp. sal % type, v_empno emp. empno % type); v_emp_record emp_record; ---- defines a variable named v_emp_record whose type is emp_record. v_emp_record1 emp_record; begin ----- pays the value to the specified attribute select t of the variable. sal, t. empno, t. ename into v_emp_record.v_sal, v_emp_record.v_empno, v_emp _
Record. v_ename from emp t where rownum = 1; ----- pay the value to the variable (in the order defined in emp_record) select t. ename, t. empno, t. sal into v_emp_record1 from emp t where rownum = 1; dbms_output.put_line ('v _ emp_record.ename = '| v_emp_record.v_ename ); dbms_output.put_line ('v _ emp_record1.ename = '| v_emp_record1.v_ename); end; two collection containers (index table, nested table, varray) 1. index table Single Column multi-row problem Java code declare type num_array is table of number (5) index by binary_integer; --- define num_array
This type; v_num_array num_array; begin for I in 1 .. 10 loop v_num_array (I): = I; end loop; for k in reverse 1 .. 10 loop dbms_output.put_line ('v_num_array ('| k |') = '| v_num_array (k); end loop; end; 2. Multiple columns and multiple rows. Java code declare --- emp % rowtype indicates the element type in the Set (the above record type can also be used here), and binary_integer indicates
Index subscript type (varchar2 can also be used as the subscript) type emp_array is table of emp % rowtype index by binary_integer; v_emp_array emp_array; begin /************************************** **************************************** * ************************** binary_integer indicates that all data is put into v_emp_array at a time after being taken out, in this case, multiple records are equivalent
Therefore, a set is created, and v_emp_array is also of the set type. * therefore, the correct value can be assigned. Without binary_integer, the record is first retrieved and assigned to v_emp_array.
Different types, therefore, you cannot assign a value ************************************* **************************************** * ***************************/select * bulk collect into v_emp_array from emp t; for I in v_emp_array.first .. v_emp_array.last loop dbms_output.put_line (v_emp_array (I ). ename | ''| v_emp_array (I ). sal); end loop; end; www.2cto.com 3. using varchar2 as the underlying example (I personally think it is similar to HashMap in java) Java code declare type name_array is table of varchar2 (50) index by varchar2 (20); v_name_array name_array; begin v_name_array ('zhongguo'): = 'beijing'; v_name_array ('meigo'): = 'huashengdun'; v_name_array ('hangzhou'): = 'shou '; dbms_output.put_line (v_name_array ('hangout'); end;