PL/SQL BASIC structure---plsql composite type---table type variable

Table type variable table
The syntax is as follows:
Type table types are table of type index by Binary_integer;
Table variable name table type;
The type can be the preceding type definition, and the index by Binary_integer clause represents the index of the signed integer so that the table is accessed
The data method in a type variable is "table variable name (index symbol integer)". Table type, equivalent to the map container in Java,
is a variable-length array, key (the symbol integer index) must be an integer, can be a negative number, and value (type) can be
scalar, or it can be a record type. You can assign a value out of order, but you must first assign it to use it.
1. Defining a one-dimensional table type variable
―――――――――――――――――――――――――――――――――――――
Declare
Type T_TB is Table of VARCHAR2 (a) index by Binary_integer;
V_TB T_TB;
Begin
V_TB (+): = ' Hello ';
V_TB (98): = ' world ';
Dbms_output.put_line (V_TB (100));
Dbms_output.put_line (V_TB (98));
End
Table-type variables of type record
Declare
Type T_RD is record (ID number,name varchar2 (20));
Type T_TB is table of T_RD index by Binary_integer;
V_TB2 T_TB;
Begin
V_TB2 (+). Id:=1;
V_TB2 (+). name:= ' Hello ';
--dbms_output.put_line (V_TB2 (). ID);
--dbms_output.put_line (V_TB2 (+). Name);
Dbms_output.put_line (V_TB2) id| | ' ' | | V_TB2 (+). Name);
End
―――――――――――――――――――――――――――――――――――――

2. Defining multidimensional Table type variables
The program defines a multidimensional table type named Tabletype1, which is equivalent to a multidimensional array, Table1 is a multidimensional table type variable, and the number
According to table Tempuser.testtable, the records of RecordNumber 60 are extracted.
stored in the Table1 and displayed.
―――――――――――――――――――――――――――――――――――――
Declare
Type Tabletype1 is table of Testtable%rowtype index by Binary_integer;
Table1 Tabletype1;
Begin
SELECT * into table1 from tempuser.testtable where recordnumber=60;
Dbms_output.put_line (table1) recordnumber| | Table1 (currentdate);
End

Note: In a defined table type variable, you can use count, delete, first, last, next, exists, and prior
and other properties, using the method for "table variable name. Property", which returns a number.

Set Serveroutput on
Declare
Type Tabletype1 is Table of VARCHAR2 (9) index by Binary_integer;
Table1 Tabletype1;
Begin
Table1 (1): = ' Chengdu city ';
Table1 (2): = ' Beijing ';
Table1 (3): = ' Qingdao ';
Dbms_output.put_line (' Total number of records: ' | | To_char (Table1.count));
Dbms_output.put_line (' first record: ' | | Table1.first);
Dbms_output.put_line (' last record: ' | | Table1.last);
Dbms_output.put_line (' The previous record of the second article: ' | | Table1.prior (2));
Dbms_output.put_line (' The next record in the second article: ' | | Table1.next (2));
End
―――――――――――――――――――――――――――――――――――――
*****************************************
%type and%rowtype
*****************************************
Define variables using%type, in order for the types of variables in PL/SQL to match the data types of the fields in the data table, Oracle
The 9i provides a%type definition method. This way, when the field type of the data table is modified, the type of the corresponding variable in the PL/SQL program
Automatically modified.
―――――――――――――――――――――――――――――――――――――
CREATE TABLE Student (
ID number,
Name VARCHAR2 (20),
Age Number (3,0)
);
INSERT into student (Id,name,age) VALUES (1, ' Susu ', 23);
--Find a variable for a field
Declare
V_name varchar2 (20);
V_name2 Student.name%type;
Begin
Select name into v_name2 from student where rownum=1;
Dbms_output.put_line (v_name2);
End
--Find variables for multiple fields
Declare
V_ID Student.id%type;
V_name Student.name%type;
V_age Student.age%type;
Begin
Select Id,name,age into V_id,v_name,v_age from student where rownum=1;
Dbms_output.put_line (v_id| | ' ' | |   v_name| | ' ' | | V_age);
End
--Find a variable of type, recommended *
Declare
V_student Student%rowtype;
Begin
SELECT * into V_student from student where rownum=1;
Dbms_output.put_line (v_student.id| | ' ' | |   v_student.name| | ' ' | | V_student.age);
End
--can also be found by field, but the field order must be the same, not recommended
Declare
V_student Student%rowtype;
Begin
Select Id,name,age into v_student from student where rownum=1;
Dbms_output.put_line (v_student.id| | ' ' | |   v_student.name| | ' ' | | V_student.age);
End
Declare
V_student Student%rowtype;
Begin
Select Id,name,age to v_student.id,v_student.name,v_student.age from student where
id=1;
--select * into V_student.id,v_student.name,v_student.age from student where id=1;
Dbms_output.put_line ();
End
―――――――――――――――――――――――――――――――――――――
Note: Insert,update,delete,select all can, create Table,drop table not. DPL,DML,
and process Control statements can be used in PL/SQL, but DDL statements are not.

Declare
V_name student.name%type:= ' Wang ';
Begin
INSERT into student (Id,name,age) values (2,v_name,26);
End

Begin
INSERT into student (Id,name,age) VALUES (5, ' hehe ', 25);
End
Declare
V_name student.name%type:= ' Hexian ';
Begin
Update student set name=v_name where id=1;
End
Begin
Update student set name= ' Qinaide ' where id=2;
End
―――――――――――――――――――――――――――――――――――――

PL/SQL BASIC structure---plsql composite type---table type variable