Oracle PL/SQL Composite data type, oraclepl

Oracle PL/SQL Composite data type, oraclepl

Composite data types can be roughly divided into two types. The first type is record type, which is suitable for processing single row and multiple columns of data, a bit similar to VO in java; the first type is set type, suitable for processing Single Column and multiple rows of data, similar to List in java, the following experiment is performed in version 11.2.0.1.0.

1. Record type

Drop table test purge;

Create table test
(
Id number (2 ),
Name varchar2 (60)
);
Insert into test values (1, 'aaa ');
Insert into test values (2, 'bbb ');
Insert into test values (3, 'ccc ');
Insert into test values (4, 'ddd ');
Insert into test values (5, 'Eee ');

Commit;

-- Explicitly define the record type
Declare
Type t_record is record
(
Id test. id % type,
Name test. name % type
);
Var_record t_record;
Coun number: = 0;
Begin
For c_row in (select id, name from test) loop
Coun: = coun + 1;
Dbms_output.put_line ('dide' | coun | 'cycle ');
Var_record.id: = c_row.id;
Var_record.name: = c_row.name;
Dbms_output.put_line ('record: '| var_record.id |' --- '| var_record.name );
Dbms_output.put_line ('cursor: '| c_row.id |' --- '| c_row.name );
End loop;
Exception when others then
Dbms_output.put_line (sqlcode | sqlerrm );
End;
/

Output result:
1st cycles
Record: 1 --- aaa
Cursor: 1 --- aaa
2nd cycles
Record: 2 --- bbb
Cursor: 2 --- bbb
3rd cycles
Record: 3 --- ccc
Cursor: 3 --- ccc
4th cycles
Record: 4 --- ddd
Cursor: 4 --- ddd
5th cycles
Record: 5 --- eee
Cursor: 5 --- eee

-- Implicitly defines the record type
Declare
T_record1 test % rowtype;
Cursor c_row (v_id in varchar2) is select id, name from test where id <= v_id;
T_record2 c_row % rowtype;
Begin
For row_test in c_row (3) loop
T_record1.id: = row_test.id;
T_record1.name: = row_test.name;
T_record2.id: = row_test.id;
T_record2.name: = row_test.name;
Dbms_output.put_line ('table rowtype: '| t_record1.id |' --- '| t_record1.name );
Dbms_output.put_line ('rowtype: '| t_record2.id |' --- '| t_record2.name );
Dbms_output.put_line ('cursor: '| row_test.id |' --- '| row_test.name );
End loop;
Exception when others then
Dbms_output.put_line (sqlcode | sqlerrm );
End;
/

Output result:

Table rowtype: 1 --- aaa
Rowtype of the cursor: 1 --- aaa
Cursor: 1 --- aaa
Rowtype of the Table: 2 --- bbb
Rowtype of the cursor: 2 --- bbb
Cursor: 2 --- bbb
Table rowtype: 3 --- ccc
Rowtype of the cursor: 3 --- ccc
Cursor: 3 --- ccc

If you select an explicit or implicit definition record, I prefer to select an explicit definition because it makes the logic clearer.

2. Set Type

-- Index table
Declare
Cursor cur_test is select id, name from test;
Type t_test1 is table of test % rowtype index by binary_integer;
Var_test1 t_test1;
Begin
SELECT id, name INTO var_test1 (0) FROM test WHERE id = 1;
Dbms_output.put_line ('var _ test1 (0): '| var_test1 (0). id |' --- '| var_test1 (0). name );
SELECT id, name INTO var_test1 (10) FROM test WHERE id = 2;
Dbms_output.put_line ('var _ test1 (10): '| var_test1 (10). id |' --- '| var_test1 (10). name );
End;

Var_test1 (0): 1 --- aaa
Var_test1 (10): 2 --- bbb

-- Nested table
DECLARE
TYPE t_test1 is table of test. id % TYPE;
Var_test1 t_test1;
Begin
Var_test1: = t_test1 (1, 2, 3 );
Dbms_output.put_line ('var _ test1: '| var_test1 (1) |', '| var_test1 (2) |', '| var_test1 (3 ));
End;

Var_test1: 1, 2, 3

-- Varray table
DECLARE
TYPE t_test1 is varray (10) OF test. id % TYPE;
Var_test1 t_test1;
Begin
Var_test1: = t_test1 (1, 2, 3 );
Dbms_output.put_line ('var _ test1: '| var_test1 (1) |', '| var_test1 (2) |', '| var_test1 (3 ));
End;
Var_test1: 1, 2, 3

There is no limit on the number of elements in the index table and nested table set, and there is no limit in the varray set.
Index tables cannot be stored in databases, and nested tables and varray can be stored in databases.

How to Use variables in oracle pl/SQL

Define and use variables

PL/SQL has four types: scalar type, composite type, reference type (reference), LOB (Large Obejct) Type

1. scalar type

The most common type is the scalar type. It refers to the variable that can only store a single value, including the numeric type, character type, date type, and boolean type. Each type also contains the corresponding child type.

Constant scalar type:

VARCHAR2 (n), CHAR (n), NUMBER (p, s), DATE, TIMESTAMP, LONG, long raw, BOOLEAN, BINARY_INTEGER (only PL/SQL ), BINARY_FLOAT and BINARY_DOUBLE (10 Gb new)

Define scalar:

Identifier [CONSTANT] datatype [not null] [: = | DEFAULT expr]

When using a scalar, note that the = sign is replaced by: =, which is the same as the value assignment symbol @ _ @ in delphi @_@

Example:

V_name VARCHAR2 (10 );
Vanderbilt Rate constants number (4, 2): = 3.04;

To prevent the defined variable TYPE from being inconsistent with the field TYPE in the table, you can use % TYPE to define it:

V_name employee. name % TYPE;

As shown above, the v_name type is the same as the name field type in the table "employee !!

2. Compound variables:

Variable used to store multiple values is called a composite variable, including PL/SQL records, PL/SQL tables, nested tables, and VARRAY.

1. PL/SQL records

Similar to the structure concept in C/C ++:

Declare
TYPE employee_record is RECORD (
Id employee. id % TYPE,
Name employee. name % TYPE,
Email employee. email % TYPE );
Em_record employee_record;
Begin
Select id, name, email into em_record from employee where name = & name;
Dbms_output.put_line ('employee name: '| em_record.name | 'employee ID:' | em_record.id );
End;

2. PL/SQL tables are similar to arrays. The difference is that PL/SQL tables allow negative subscripts without the upper and lower limits, for example:

Declare
TYPE employee_table is table of employee. name % TYPE index by BINaRY_INTEGER;
Em_table employee_table;
Begin
Select name into em_table (-1) from employee where name = & name;
Dbms_output.put_line ('employee name: '| em_table (-1 ));
End;

3. A nested table is similar to a PL/SQL table. The difference is that a nested table can be the Data Type of a table column, but a PL/SQL table cannot. When a nested table is used as a table column, you must specify a special storage table for it, such:

Create... the remaining full text>
 
In the oracle database, I used PL/SQL to create tables. I cannot understand the identifiers of some fields!

Binary_double double precision 64-bit
Binary_float dual-precision 32-bit
Blob, clob, and nclob are three large objects (LOB) used to save large image files or formatted text files, such as Miceosoft Word documents and non-text files such as audio and video files, the maximum length is 4 GB. There are several types of LOB, depending on the type of bytes you use. Oracle 8i actually stores the data in the database.
You can perform read, storage, write, and other special operations.
Interval day to second, interval year to month type storage time difference between two TIMESTAMP, second, month
Long variable long character column. The maximum length is 2 GB. It is used for long string data that does not need to be searched for strings. If you want to search for characters, you must use the varchar2 type.
Timestamp, more accurate than date
Timestamp with time zone, timestamp with local time zone timestamp (different time zones)
Number Type

Answer complete
Reference: dev.csdn.net/article/38/38823.shtm