Oracle user manual (I)-declare Variables

/**//*
-- Create a table
Create table student (
Recordid number (38 ),
Sid int not null,
Sname varchar2 (50 ),
Sdate date,
Sage number (3)
);
*/
-- Delete a table
-- Drop table student;


-- Insert data
/**//*
Set serveroutput on -- allow server output
Declare
Maxrecords constant int: = 100;
I int: = 1;
Begin
For I in 1 .. maxrecords
Loop
Insert into student (sid, sdate) values (I, sysdate );
End loop
-- Dbms_output.put_line ('data is input successfully! ');
Commit;
End;
*/
-- Select * from student;
-- Declare a variable
/**//*
Declare
Piconstant number (9): = 3.1415926;
Begin
Commit;
End;
*/
-- Composite data types (five common types)
-- 1. Use % type to define variables
-- To make the variable type in PL/SQL consistent with the data type of the field in the data table, Oracle 9i provides the % type definition method.
-- When the field type of the data table is modified, the type of the corresponding variable in the PL/SQL program is also automatically modified.
/**//*
Declare
Mydate student. sdate % type;
Begin
Commit;
End;
*/
-- 2. Define record type variables
-- Record Data Types that bind multiple basic data types.

/**//*
Set serveroutput on
Declare
Type myrecord is record (
Sid int,
Sdate date );
Srecord myrecord; -- declare an instance with a custom record type variable
Begin
Select sid, sdate into srecord from student where sid = 68;
Dbms_output.put_line ('Id: '| srecord. sid | 'date:' | srecord. sdate); -- '|': it is a string connector.
End;
*/

-- 3. Use the % rowtype variable
-- Use % type to get the data type of the field for the variable, and use % rowtype to get the data type of the entire record for the variable.
-- Compare the definitions of the two: variable name data table. Column name % type, variable name data table % rowtype.
/**//*
Set serveroutput on
Declare
MytableRow student % rowtype;
Begin
Select * into mytableRow
From student
Where sid = 88;
Dbms_output.put_line (mytableRow. sid | mytableRow. sdate );
End;
*/
-- 4. Define one-dimensional table type variables
-- Table type variables are different from data tables. The syntax for defining table type variables is as follows:
--―――――――――――――――――――――――――――――――――――――
-- Type table type is table of type index by binary_integer;
-- Table variable name table type;
--―――――――――――――――――――――――――――――――――――――
-- The type can be the previous type definition. The index by binary_integer clause indicates that the index is composed of symbol integers,
-- In this way, the data method used to access table-type variables is "Table variable name (index symbol integer )".
/**//*
Declare
Type tabletype1 is table of varchar2 (4) index by binary_integer; -- defines a struct-type one-dimensional array.
Type tabletype2 is table of student. sid % type index by binary_integer; -- defines an integer Array
Table1 tabletype1; -- Instance Declaration
Table2 tabletype2; -- Instance Declaration
Begin
Table1 (1): = 'student ';
Table1 (2): = 'employee ';
Table2 (1): = 88;
Table2 (2): = 89;
Dbms_output.put_line (table1 (1) | table2 (1 ));
Dbms_output.put_line (table1 (2) | table2 (2 ));
End;
*/
-- 5. Define multi-dimensional table Variables
-- Defines multi-dimensional arrays.
-- Note that data must be inserted into the database before running the following statements.
Declare
Type tabletype1 is table of student % rowtype index by binary_integer;
Table1 tabletype1;
Begin
Select * into table1 (60)
From student
Where sid = 60;
Dbms_output.put_line (table1 (60). sid | table1 (60). sdate );
End;