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: The mutual assignment of records requires two record types to be identical.

? Case

Example 1

--Create a table and insert a record

CREATE TABLE student (IDNumber, name Varchar2 (a), birthday date);

Insert into studentvalues (+, ' xiaoming ', to_date (' 2000.12.17 ', ' yyyy.mm.dd '));

INSERT into Studentvalues ($, ' Xiaohua ', to_date (' 2001.12.17 ', ' yyyy.mm.dd '));

Insert into studentvalues (' Xiaoli ', to_date (' 2002.12.17 ', ' yyyy.mm.dd '));

--Defining record types

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 (' 2003.12.17 ', ' 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 (' 2003.12.17 ', ' 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 type type names are different, but the contents are identical, the two variables corresponding to the two types cannot be assigned to each other.

2. Collection Data type--index-by table

? Description: Similar to the concept of arrays in common programming languages.

? Methods for declaring index-by tables:

Type name is TABLE of Typeindex by Binary_integer;

Description: Where type defines the type of elements in the Index-by table, which can be a built-in type, a user-defined object type, or an expression that uses%rowtype.

? Index-by a single element in a table

After declaring the type and variable, you can use a single element in the table by using the variable name (index), where Index refers to the first element in the table.

? Property function for Index by table

Property name	Data type	Description
Count	Number	Returns the number of rows in a table
Delete	No	Used to remove a row of data from a table specified (specified by the passed-in parameter)
Exists	Boolean	Returns true if the specified row exists, otherwise false
First	Binary_integer	Returns the subscript of the first row in the table
Last	Binary_integer	Returns the subscript for the last row in the table
Next	Binary_integer	Returns the subscript for the next row of the specified row (specified by the passed-in parameter)
Prior	Binary_integer	Subscript for the previous row in the specified row (specified by the passed in parameter)

? Case

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 (+) from student whereid=100;

Dbms_output.put_line (v_students) id| | | | V_students (+). name| | ' ' | | To_char (v_students (+), 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;--Cycle times

Begin

Select * into v_s (Ten) from Scott.emp whereempno= ' 7788 ';

Select * into v_s (fromscott.emp) where empno= ' 7902 ';

Select * into v_s ( -12) fromscott.emp where empno= ' 7934 ';

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. Collection data Type-variable-length array

? Description: Variable-length array: is a collection of stored ordered elements, each with an index that corresponds to the position of the element in the array. The variable-length array has a size limit, but can be changed dynamically.

? To create a variable-length array statement:

? Case

--Create a variable-length array

Create Type Varray_phone as Varray (3) of VARCHAR2 (50);

--Create a people table in which people have a list of phones (1, 2, or 3 phone numbers may be available).

CREATE TABLE Person3

(

ID integer CONSTRAINTPERSON3_PK primary key,

first_name varchar (20),

last_name varchar (20),

Phone Varray_phone

)

--Fill variable length array

INSERT into Person3values (1, ' Yuan ', ' Weixiang ', Varray_phone (' 12345 ', ' 34567 ', ' 56789 '));

SELECT * from Person3;

Insert Intoperson3 VALUES (2, ' Hao ', ' Lihai ', Varray_phone ());

SELECT * from Person3;

--modifying elements in a variable-length array

Update Person3 setphone= varray_phone (' 12345 ', ' 34567 ') where id = 2;

SELECT * from Person3;

--Modify the length size of the elements of the variable-length array

Alter type Varray_phone modifyelement type VARCHAR2 ($) Cascade--Error

Alter type Varray_phone Modify element type VARCHAR2 Cascade

Description: The CASCADE option propagates changes to dependent objects in the database Person3 is the dependent object

--Modify the number of variable-length array elements

Alter type VARRAY_PHONEMODIFY LIMIT 2 cascade--Error, Varray limit only increases

Alter type varray_phonemodify LIMIT 5 cascade

INSERT into Person3 values (3, ' Yuan ', ' Weixiang ', Varray_phone (' 12345 ', ' 34567 ', ' 56789 ', ' 34567 ', ' 56789 '));

4. Cursors

? Description: In PL/SQL programming, it is sometimes necessary to do a row-by-line processing of the query return result set, which requires that the query return the result set to a memory area, in order to be able to perform a row-by-line operation on the returned result set, this address is called a cursor.

? Define the travel banner method

Cursor cursor name is a SELECT statement;

Note: The SELECT statement in the cursor definition cannot contain an into clause.

? Cursor Properties

Cursor Properties	Description
Cursor name%isopen	A Boolean value that evaluates to TRUE if the cursor is open, otherwise false.
Cursor name%notfound	A Boolean value that evaluates to TRUE if the most recent fetch operation did not return a result, otherwise false.
Cursor name%found	A Boolean value that evaluates to False if the most recent fetch operation did not return a result, or true.
Cursor name%rowcount	A numeric value that is the number of rows returned to the current date.

? Case

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 a 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

? Description: Extracting data from a cursor allows you to update and delete data in the database.

? Syntax: When you define a cursor, you must add a for update of clause, plus the WHERE CURRENT OF clause in the UPDATE and DELETE statements.

? Case

Example 1: For each employee in a department in the Scott Program EMP table, if its salary is less than $1600, set its salary to $1600.

Accept p_deptno Prompt ' Please enter the Deptno ';

--accept is similar to the scanf in C, meaning to accept input from the screen into the P_DEPTNO variable.

Declare

V_deptno scott.emp.deptno%type:=&p_deptno;

Cursor Emp_cursor is a 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: For workers to raise wages, the staff by salary from low to high ranking, from the low wages of employees began to rise, each employee rose 10%. But to control the employee's total wages within 500,000, once the total is more than 500,000, stop the wages of the remaining employees.

6. Cursors with parameters

? Description: When defining a display cursor, you can include the definition of the parameter. When you use cursors, the data in the cursor cache changes as you enter different values for the parameters.

? Define the Tour banner method:

cursor cursor name (parameter 1 data type, ...) is a SELECT clause;

? To open the Tour banner method:

Open cursor name (& parameter 1, ...);

? Case: Query and print the employee situation for a department from the EMP Table of the Scott program, where the department number is entered interactively 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