1. Composite data type-record type
Ø syntax format
Type name is record (
Field 1 Field 1 type [not null]: = expression 1;
Field 2 Field 2 type [not null]: = expression 2;
... )
Description: records must be of the same type when values are assigned to each other.
Ø case studies
Example 1
-- Create a table and insert records
Create table student (idnumber, name varchar2 (20), birthday date );
Insert into studentvalues (100, 'xiaoming', to_date ('192. 100', 'yyyy. mm. dd '));
Insert into studentvalues (200, 'xiaohua', to_date ('192. 100', 'yyyy. mm. dd '));
Insert into studentvalues (300, 'xiaoli', to_date ('192. 100', 'yyyy. mm. dd '));
-- Define the record type
Declare
Type t_studentRecord isrecord (
Id number,
Name varchar2 (20 ),
Birthday date );
Or:
Type t_StudentRecord isrecord (
Idstudent. id % type,
Name student. name % type,
Birthday student birthday % type );
V_students t_StudentRecord;
Begin
Select * into v_students from students whereid = 200;
Dbms_output.put_line (v_students.id | ''| v_students.name |'' | to_char (v_students.birthday, 'yyyy-mm-dd '));
End;
Example 2
Declare
Type t_StudentRecord is record (
Id number (4 ),
Name varchar2 (20 ),
Birthday date );
V_students t_StudentRecord;
V_students_copy t_StudentRecord;
Begin
V_students.id: = 400;
V_students.name: = 'zhao wei ';
V_students.birthday: = to_date ('1970. 100', 'yyyy. mm. dd ');
V_students_copy: = v_students;
Dbms_output.put_line (v_students_copy.id | ''| v_students_copy.name |'' | to_char (v_students_copy. birthday, 'yyyy-mm-dd '));
End;
Example 3
Declare
Type t_StudentRecord is record (
Id number (4 ),
Name varchar2 (20 ),
Birthday date );
Type t_StudentRecord2 is record (
Id number (4 ),
Namevarchar2 (20 ),
Birthday date );
V_students t_studentRecord;
V_students_copy t_studentRecord2;
Begin
V_students.id: = 400;
V_students.name: = 'zhao wei ';
V_students.birthday: = to_date ('1970. 100', 'yyyy. mm. dd ');;
V_students_copy: = v_students;
Dbms_output.put_line (v_students_copy.id | ''| v_students_copy.name |'' | to_char (v_students_copy. birthday, 'yyyy-mm-dd '));
End;
Error Description: if the two record types have different names but the content is identical, the two variables corresponding to the two types cannot be assigned values to each other.
2. index-by table of the Set Data Type
Ø Introduction: similar to the array concept in general programming languages.
Method for declaring the index-by table:
Type name is table of typeINDEX BY BINARY_INTEGER;
Note: type defines the types of each element in the index-by table. It can be a built-in type, a user-defined object type, or a % rowtype expression.
Ø single element in index-by table
After declaring the types and variables, you can use a single element in the table by using the variable name (index). index indicates the first element in the table.
Ø Index by table attribute functions
Attribute name |
Data Type |
Description |
Count |
Number |
Returns the number of rows in the table. |
Delete |
None |
Deletes a specified row of data (specified by the input parameter) from the table. |
Exists |
Boolean |
Returns true if the specified row exists. Otherwise, returns false. |
First |
Binary_integer |
Returns the subscript of the first row in the table. |
Last |
Binary_integer |
Returns the subscript of the last row in the table. |
Next |
Binary_integer |
Returns the subscript of the next row of the specified row (specified by the input parameter ). |
Prior |
Binary_integer |
Subscript of the previous row of the specified row (specified by the input parameter) |
Ø case studies
Example 1
Declare
Type t_StudentRecord isrecord (
Idstudent. id % type,
Name student. name % type,
Birthdaystudent birthday % type );
Type t_studentTable is table oft_StudentRecord index by binary_integer;
V_students t_studentTable;
Begin:
Select * into v_students (100) from student whereid = 100;
Dbms_output.put_line (v_students (100 ). id | ''| v_students (100 ). name | ''| to_char (v_students (100 ). birthday, 'yyyy-mm-dd '));
End;
Example 2
Declare
Type t_studentTable is table of student % rowtypeindex by binary_integer;
V_students t_studentTable;
Begin
Select * into v_students (1) from student whereid = 200;
Dbms_output.put_line (v_students (1). id | ''| v_students (1). name |'
'| To_char (v_students (1). birthday, 'yyyy-mm-dd '));
End;
Example 3
Declare
Type t_s is table of scott. emp % rowtype Indexby binary_integer;
V_s t_s;
V_index binary_integer; -- Index Number
V_loop binary_integer; -- number of cycles
Begin
Select * into v_s (10) from scott. emp whereempno = '123 ';
Select * into v_s (22) fromscott. emp where empno = '20140901 ';
Select * into v_s (-12) fromscott. emp where empno = '123 ';
V_index: = v_s.first;
V_loop: = v_s.count;
Loop
Dbms_output.put_line (v_s (v_index). empno );
V_index: = v_s.next (v_index );
V_loop: = v_loop-1;
Exit when v_loop <= 0;
End loop;
V_index: = v_s.last;
V_s.delete (v_index );
V_index: = v_s.first;
V_loop: = v_s.count;
Loop
Dbms_output.put_line (v_s (v_index). empno );
V_index: = v_s.next (v_index );
V_loop: = v_loop-1;
Exit when v_loop <= 0;
End loop;
End;
3. Set the Data Type-variable-length Array
Introduction: a variable-length array is a collection of ordered elements. Each element has an index, which corresponds to the position of the element in the array. The variable-length array has a size limit, but can be dynamically changed.
Create a variable-length array statement:
Ø case studies
-- Create a variable-length Array
Create Type varray_phone as varray (3) of varchar2 (50 );
-- Create a person-in-person table. In the table, the person has a column of phone numbers (which may include 1, 2, or 3 phone numbers ).
Create table person3
(
Id integer constraintperson3_pk primary key,
First_name varchar (20 ),
Last_name varchar (20 ),
Phone varray_phone
)
-- Fill in a variable-length Array
Insert into person3values (1, 'yuanyuan ', 'weixiang', varray_phone ('000000', '000000', '000000 '));
Select * from person3;
Insert into person3 values (2, 'hao', 'lihai ', varray_phone ());
Select * from person3;
-- Modify elements in a variable-length Array
Update person3 setphone = varray_phone ('20140901', '20160901') where id = 2;
Select * from person3;
-- Modify the length of elements in a variable-length array.
Alter type varray_phone modifyelement type varchar2 (49) cascade -- Error
Alter type varray_phone modify element type varchar2 (60) cascade
Note: The cascade option propagates changes to the dependent object Person3 in the database, which is the dependent object.
-- Modify the number of elements in the variable-length array.
Alter type varray_phonemodify limit 2 cascade -- an error is reported. The varray limit can only be increased.
Alter type varray_phonemodify limit 5 cascade
Insert into person3 values (3, 'yuanyuan ', 'weixiang', varray_phone ('000000', '000000', '000000', '000000', '000000 '));
4. cursor
Introduction: In the PL/SQL program design, you sometimes need to perform row-by-row processing on the returned result set of the query. Therefore, you need to cache the returned result set of the query to a memory zone, in order to perform row-by-row operations on the returned result set, the first address of the memory zone must be returned. This address is called a cursor.
Define the cursor syntax
Cursor name is select statement;
Note: The select statement in the cursor definition cannot contain the into clause.
Ø cursor attributes
Cursor property |
Description |
Cursor name % isopen |
Boolean value. If the cursor is enabled, the value is true. Otherwise, the value is false. |
Cursor name % notfound |
Boolean value. If no result is returned for the last fetch operation, the value is true. Otherwise, the value is false. |
Cursor name % found |
Boolean value. If no result is returned for the last fetch operation, the value is false. Otherwise, the value is true. |
Cursor name % rowcount |
Numeric value. The value is the number of rows returned so far. |
Ø case studies
Example 1
DECLARE
V_no scott. emp. empno % type;
V_name scott. emp. ename % type;
CURSORc_e is select empno, ename FROM scott. emp;
BEGIN
OPEN c_e;
LOOP
FETCH c_e INTO v_no, v_name;
Exit when c_e % NOTFOUND;
Dbms_output.put_lint (v_no | ''| v_name );
End loop;
CLOSE c_e;
END;
Example 2
DECLARE
CURSOR c_e is select empno, ename FROM scott. emp;
BEGIN
For c1 in c_e loop
Dbms_output.put_lint (c1.empno | ''| c1.ename );
End loop;
END;
5. updatable cursors
Introduction: extracts data from the cursor and can update and delete data in the database.
Ø Syntax: when defining a cursor, The for update of clause must be added; the where current of clause must be added to the update and delete statements.
Ø case studies
Example 1: for each employee in a department in the scott plan emp table, if his salary is less than 1600 yuan, his salary is set to 1600 yuan.
Accept p_deptno prompt 'Please enter the deptno ';
-- Accept is similar to scanf in C language, meaning to Accept input from the screen to the p_deptno variable.
Declare
V_deptno scott. emp. deptno % type: = & p_deptno;
Cursor emp_cursor is select empno, job, sal from scott. emp wheredeptno = v_deptno for update of sal;
Begin
For emp_record in emp_cursor loop
If emp_record.sal <1600 then
Update emp set sal = 1600 where currentof emp_cursor;
End if;
End loop;
End;
Example 2: Raise wages for employees. Employees are sorted in descending order of wages. Employees with lower salaries increase by 10%. However, to control the total salary of employees within 0.5 million, once the total amount exceeds 0.5 million, the salary increase for the remaining employees will be stopped.
6. A cursor with Parameters
Description: when defining a display cursor, you can add a parameter definition. When a cursor is used, data in the cursor cache also changes when different values are entered for the parameter.
Define the cursor Syntax:
Cursor name (parameter 1 data type ,....) Is select clause;
Enable the cursor Syntax:
Open cursor name (& Parameter 1 ,....) ;
Ø case: Query and print the employees of a department from the scott solution's emp table, where the Department number is interactively entered by the user.
Accept v_deptnoprompt 'Please enter the deptno;
Declare
V_ename scott. emp. ename % type;
V_sal scott. emp. sal % type;
Cursor emp_cursor (v_deptno number) is selectename, sal from scott. emp where deptno = v_deptno;
Begin
Open emp_cursor (& p_deptno );
Loop
Fetch emp_cursor into v_ename, v_sal;
Exit when emp_cursor % notfound;
Dbms_output.put_line (v_ename | ''| v_sal );
End loop;
Close emp_cursor;
End;