Oracle PL/SQL Advanced Programming

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;