Oralce-pl/sql Programming-Cursors

PL/SQL (procedural language/sql) is a procedural programming language that Oracle introduces in the database.

PL/SQL block structure

Declares part of the execution part (required) Exception handling section

[DECLARE]

--declaration section, optional

BEGIN

-The execution part must be

[Exception]

--Exception handling section, optional

END

Single-line Comment:--

Multiline Comment:/* */

Basic data type:

Number VARCHAR2 char long nchar nvarchar2 Date Boolean

Special data types:

%type, declares a data type that is the same as the specified column name.

DECLARE var_job Emp.job%type;

Record: Records type data

Type Emp_type is record

(

Var_ename varchar2 (20),

Var_job varchar2 (20),

Var_sal number

);

%rowtype: Combined with the%type and record features, you can define a special data type based on the row structure in the data table to store a row of data retrieved from the data table

Rowvar_emp emp%rowtype; Defines a variable that can store a row of data in an EMP table rowvar_emp

Define variables: Var_countryname varchar2 (50): = ' China ';

Define constants: Con_day constant integer:=365;

PL/SQL cursors

To display cursors and implicit cursors:

Display Cursors : Example

1. Declare a cursor that retrieves employee information from the EMP table, and then open the cursor and specify that the job is to retrieve employee information for "MANAGER", then use the Fetch...into statement and the while loop
Reads all employee information in the cursor, and finally outputs the employee information read

Declare
Cursor Cur_emp (var_job in varchar2:= ' salesman ')
is select Empno,ename,sal
From EMP
where Job=var_job;
Type record_emp is record
(
Var_empno Emp.empno%type,
Var_ename Emp.ename%type,
Var_sal Emp.sal%type
);
Emp_row record_emp;
Begin
Open cur_emp (' MANAGER ');
fetch cur_emp into Emp_row; --Reads the contents of the cursor, moves the cursor pointer to the first row in the result set
While Cur_emp%found loop
Dbms_output.put_line (emp_row.var_ename| | ' The number is ' | | emp_row.var_empno| | ', wages are ' | | Emp_row.var_sal);
Fetch cur_emp into Emp_row;
End Loop;
Close cur_emp;
End
/

Cursor Properties:

%found: If the SQL statement affects at least one row of data, this property is true, otherwise false

%notfound: Contrary to the above function

%rowcount: Returns the number of rows affected

%isopen: When the cursor is open, returns True when closed, false

An implicit cursor

When executing an SQL statement, Oracle automatically creates an implicit cursor. This cursor is the working area in memory in which the statement is processed.

Example:

In the Scott mode, increase the salary of the salesperson in the EMP table by 20% and then use the%rowcount property output of the implicit cursor SQL to raise the
Number of employees in wages

Begin
Update emp
Set sal = sal* (1+0.2)
Where job= ' salesman ';
If sql%notfound Then
Dbms_output.put_line (' No Employee adjusted wages ');
Else
Dbms_output.put_line (' There ' | | sql%rowcount| | ' 20% ' of employees ' salary increases);
End If;
End
/

For Statement loop cursor:

Example:

Use an implicit cursor and a for statement to retrieve employee information for a job that is a salesperson and output

Begin
For Emp_record in (select Empno,ename,sal from emp where job= ' salesman ')
Loop
Dbms_output.put_line (' Employee number: ' | | EMP_RECORD.EMPNO);
Dbms_output.put_line ('; Employee Name: ' | | Emp_record.ename);
Dbms_output.put_line ('; Employee wage: ' | | Emp_record.sal);
End Loop;
End
/

Use the display cursor and the FOR statement to retrieve employee information for department number 30 and output

Declare
Cursor Cur_emp is
SELECT * FROM emp
where deptno=30;
Begin
For Emp_record in Cur_emp
Loop
Dbms_output.put (' Employee number: ' | | EMP_RECORD.EMPNO);
Dbms_output.put (' Employee Name: ' | | Emp_record.ename);
Dbms_output.put_line (' Employee position: ' | | Emp_record.job);
End Loop;
End
/

In summary, cursors can be declared in a for loop that uses cursors (implicit, display). Instead of opening cursors, reading cursors, closing cursors, and so on, these are automatically done internally by the Oracle system

PL/SQL Exception handling

In the [exception] code block

Depending on the mechanism and principle of the exception, Oracle system exceptions can be categorized into the following two main categories:

1. Pre-defined exceptions

Defined in Oracle's core PL/SQL library, users can identify them using their name in their own PL/SQL exception handling section. The handling of such anomalies does not require the user to define them in the program, which is automatically raised by Oracle.

System pre-defined exceptions	Description
Zero_divide	Exception thrown when Divisor is zero
Access_into_null	Attempt to assign a value to a property of an uninitialized object
Collection_is_null	Attempting to use an uninitialized collection element
Cursor_already_open	An attempt was made to open a cursor that has been opened again, but the cursor is not closed until it is reopened
Invalid_cursor	Performs an illegal cursor operation, such as closing an open cursor
Invalid_number	Attempt to convert a string to an invalid number and fail
Login_denied	Attempting to connect to the database with an invalid user name or password
No_data_found	The SELECT into statement does not return data
Rowtype_mismatch	The primary cursor variable is incompatible with the return type of the PL/SQL cursor variable
Self_is_null	Use an empty object to invoke its method when using an object type
Subscript_beyond_count	Element The following table exceeds the elements in a nested table or Varry
Subscript_outside_limit	Attempting to reference an element in a nested table or varry using an illegal index number
Sys_invalid_rowid	Error when the string is converted to rowID because the string is not a valid ROWID value
Timeout_on_resource	Oracle timed out while waiting for resources
Too_many_rows	When you execute a SELECT INTO statement, the result set exceeds the exception thrown by one row

2. Custom Exceptions

There are two kinds

A-Error encoding exception

Define an exception variable with an error code exception number of "00001", and then want to insert a record in the Dept table that can "violate uniqueness constraints".

Finally, output exception hint information in exception code

Declare
Primary_iterant exception;
pragma exception_init (primary_iterant,-00001);-- associated error number and exception variable name
Begin
INSERT INTO Dept values (10, ' Software Development Department ', ' Shenzhen ');
exception
When primary_iterant then
Dbms_output.put_line (' primary key not allowed to repeat! ');
End
/

B-business logic exceptions

Customize an exception variable that, when inserting data into a dept table, throws an exception using the Raise statement if the LOC field is determined to be null.

and transfer the program execution process to the exception section for processing

Declare
null_exception exception;
Dept_row Dept%rowtype;
Begin
dept_row.deptno:=66;
dept_row.dname:= ' Public Ministry ';
INSERT INTO Dept
VALUES (Dept_row.deptno,dept_row.dname,dept_row.loc);
If Dept_row.loc is null then
Raise Null_exception;
End If;
exception
When Null_exception Then
Dbms_output.put_line (' The value of the LOC field is not allowed to be null ');
Rollback
End
/

　　

Oralce-pl/sql Programming-Cursors