Oracle implicit cursors, display cursors, cursor loops, dynamic SELECT statements and dynamic cursors, exception handling, custom exceptions ____oracle

The concept of cursors:
A cursor is a memory workspace of SQL that is defined by the system or user as a variable. The role of a cursor is to temporarily store blocks of data that are extracted from the database. In some cases, the data needs to be transferred from the table stored in the disk to the computer memory for processing, and the processing results are displayed or eventually written back to the database. This will increase the speed of data processing, otherwise frequent disk data exchange will reduce efficiency.

There are two types of cursors: an explicit cursor and an implicit cursor. The Select ... that is used in the aforementioned program. Into ... A query statement that extracts only one row of data from a database at a time, using an implicit cursor for this form of query and DML operations. However, if you want to extract more than one row of data, the programmer defines an explicit cursor and processes it through the statements related to the cursor. An explicit cursor corresponds to a SELECT statement that returns multiple rows of columns with the result.

Once the cursor is opened, the data is transferred from the database to the cursor variable, and the application then decomposes the required data from the cursor variable and processes it.

An implicit cursor
As mentioned earlier, DML operations and Single-line SELECT statements use implicit cursors, which are:
* Insert operation: INSERT.
* Update operation: Update.
* Delete operation: delete.
* Single-line query operation: SELECT ... Into ....
When a system uses an implicit cursor, it is possible to understand the state and result of the operation through the properties of the implicit cursor, and then the process of the control program. Implicit cursors can be accessed using the first name SQL, but note that the SQL cursor name always accesses only the cursor properties of the previous DML operation or the single row select operation. Therefore, you typically use the SQL cursor name to access the property immediately after the operation has just been done. There are four properties of the cursor, as shown below.

Implicit cursor property return value type the    literal  
sql%rowcount    integer  represents the number of data rows that a DML statement successfully executed  
Sql%found a   boolean value of True to represent inserts, deletes, Update or Single-line query operation successful  
Sql%notfound    Boolean and Sql%found property return value  
Sql%isopen  boolean DML is true during execution, false after end  

Training 1 uses the attributes of an implicit cursor to determine whether the modification of an employee's salary is successful.
Step 1: Enter and run the following programs:

Set serveroutput on   
        BEGIN  
        UPDATE emp SET sal=sal+100 WHERE empno=1234;  
         IF sql%found THEN   
        dbms_output. Put_Line (' Successful revision of employee wages. ');  
        COMMIT;   
        ELSE  
        Dbms_output. Put_Line (' Modify employee's salary failed. ');  
         End IF;   
        End;  

The results of the operation are:

Failed to modify employee pay.  
        

Step 2: Change the employee number 1234 to 7788, and then repeat the above program:
The results of the operation are:

The employee's salary was successfully modified. The  
        Pl/sql process has completed successfully.

Note: In this case, the Sql%found property is used to determine the success of the modification and give the appropriate information.  

The use of the

Explicit cursor  
cursor definition and operation &NBSP
Cursor is divided into the following 4 steps. &NBSP,
1. Declares a cursor  
in the Declear section declares a cursor in the following format:  
CURSOR cursor name [(parameter 1 data type [, Parameter 2 data type ...])] The &NBSP
is SELECT statement; the  
parameter is an optional part, and the defined parameter can appear in the WHERE clause of the SELECT statement. If a parameter is defined, the corresponding actual parameters must be passed when the cursor is opened. &NBSP
The SELECT statement is a query statement to a table or view, or even a federated query. You can take a where condition, an order by, or a GROUP BY clause, but you cannot use an into clause. You can use variables defined before you define a cursor in a SELECT statement. &NBSP,
2. Open the cursor  
in the executable section, open the cursor in the following format: &NBSP
Open cursor name [(actual parameter 1[, actual parameter 2 ...])]; &NBSP
When the cursor is opened, the query result of the SELECT statement is routed to the cursor workspace. &NBSP,
3. Extracts data  
in the executable section, the data in the cursor workspace is taken to the variable in the following format. The fetch operation must be performed after the cursor is opened. &NBSP
FETCH cursor name into variable name 1[, variable name 2 ...]; &NBSP
or  
FETCH cursor into a record variable; a  
cursor opens with a pointer to the data area, and the FETCH statement returns a row of data for the pointer at a time, and multiple rows need to be repeated, and can be implemented using a loop statement. The control loop can be performed by judging the properties of the cursor. &NBSP
The following two formats are described:  
the variable name in the first format is the variable used to receive data from the cursor, which needs to be defined beforehand. The number and type of variables should be the same as the number and type of field variables in the SELECT statement. &NBSP
The second format takes one row of data to a record variable at a time, and you need to use%rowtype to define a record variable in advance, which is easy to use, and you don't have to define and use multiple variables separately. &NBSP
defines a record variable as follows: &NBSP
variable Name Table name | cursor name%ROWTYPE;&NBSP
where the table must exist and the cursor name must be defined first.

4. Close cursor

The close cursor name;
When an explicit cursor is opened, it must be explicitly closed. Once the cursor is closed, the resource occupied by the cursor is freed and the cursor becomes invalid and must be reopened for use.
The following is a simple exercise to use an explicit cursor.
"Training 1" uses cursors to extract the names and titles of 7788 employees in the EMP table.

SET serveroutput on  
        DECLARE   
         v_ename VARCHAR2 (a);  
         V_job VARCHAR2 (a);  
         CURSOR Emp_cursor is   
         SELECT ename,job from emp WHERE empno=7788;  
         BEGIN  
     OPEN emp_cursor;  
    FETCH emp_cursor into V_ename,v_job;  
        Dbms_output. Put_Line (v_ename| | ', ' | | V_job);  
        Close emp_cursor;  
        End;  

The results of the execution are:

The Scott,analyst  
        Pl/sql process has completed successfully.   

Description: This program extracts and displays the name and title of employee 7788 by defining the cursor emp_cursor.
As an improvement to the above examples, record variables are used in the following training.
"Training 2" uses cursors to extract the names, titles, and salaries of 7788 employees in the EMP table.

SET serveroutput on  
        DECLARE  
         CURSOR emp_cursor are  SELECT ename,job,sal from emp WHERE empno=7788;  
         Emp_record Emp_cursor%rowtype;  
        BEGIN  
OPEN emp_cursor;      
        FETCH emp_cursor into Emp_record;  
           Dbms_output. Put_Line (emp_record.ename| |, ' | | | emp_record.job| | ', ' | | | emp_record.sal);  
         Close emp_cursor;  
        End;  

The results of the execution are:

The scott,analyst,3000  
        Pl/sql process has completed successfully.   

Note: In an instance, a record variable is used to receive data, and a record variable is defined by a cursor variable that needs to appear after the cursor definition.
Note: You can obtain the contents of a record variable in the following form:
Record variable name. Field name.
"Training 3" shows the names and wages of the top 3 employees with the highest salary.

SET serveroutput on  
        DECLARE  
         v_ename VARCHAR2 (a);  
        V_sal number (5);  
        CURSOR Emp_cursor is  SELECT ename,sal from emp order by Sal DESC;  
        BEGIN  
         OPEN emp_cursor;  
         For I in 1..3 LOOP to  
           FETCH emp_cursor into v_ename,v_sal;  
         Dbms_output. Put_Line (v_ename| | ', ' | | V_sal);  
          End LOOP;  
         Close emp_cursor;  
         End;  

The results of the execution are:

The king,5000  
     scott,3000  
     ford,3000  
     pl/sql process has completed successfully.  

Description: The program uses the ORDER BY clause in the cursor definition to sort, and uses the Loop statement to extract the multiline data.

Cursor Loops
Training 1 Displays the number and name of all employees in a special for loop format.

SET serveroutput  
on DECLARE  
  CURSOR emp_cursor   
  are SELECT empno, ename from EMP;  
BEGIN for  
Emp_record in Emp_cursor LOOP     
    dbms_output. Put_Line (emp_record.empno| | Emp_record.ename);  
    End LOOP;  
    End;  

The results of the execution are:

The 7369SMITH  
7499ALLEN  
7521WARD  
7566JONES  
         pl/sql process has been successfully completed.  

Note: You can see that the loop form is very simple, which implies the definition of the record variable, the opening, extraction, and closing of the cursor. Emp_record for implicitly defined record variables, the number of times the loop executes is consistent with the number of rows obtained by the cursor.
"Training 2" is another form of cursor looping.

SET serveroutput on   
"BEGIN for  
 Re in" (SELECT ename from EMP)  LOOP  
  dbms_output. Put_Line (re.ename) end  
 LOOP;  
End;  

The results of the execution are:

SMITH  
ALLEN  
WARD  
JONES  

Note: This form is simpler, omitting the definition of the cursor, and the cursor's SELECT query statement appears directly in the loop.

Explicit cursor Properties
Although you can use the previous form to obtain cursor data, it is a more flexible method to use some of its properties for structural control after the cursor definition. The properties of an explicit cursor are shown below.

Cursor Property   return value type   semantic  
%rowcount   integer  Gets the number of rows returned by the FETCH statement  
%found The  most recent FETCH statement returns a row of data is true. Otherwise, the False%notfound Boolean is the opposite of the   %found property return value  
%isopen The boolean cursor is open, or false  

You can obtain the properties of the cursor in the following form:
Cursor Name% property
To determine whether the cursor Emp_cursor is open, you can use the property emp_cursor%isopen. If the cursor is already open, the return value is true, otherwise false. Specific reference to the following training.
Training 1 uses the properties of cursors to practice.

SET serveroutput on  
DECLARE  
  v_ename VARCHAR2 (a);  
  CURSOR Emp_cursor is   
  SELECT ename from EMP;  
BEGIN  
 OPEN emp_cursor;  
 IF emp_cursor%isopen THEN  
LOOP  
   FETCH emp_cursor into V_ename;  
   EXIT when Emp_cursor%notfound;  
   Dbms_output. Put_Line (To_char (emp_cursor%rowcount) | | -'|| V_ename);  
  End LOOP;  
 ELSE  
  Dbms_output. Put_Line (' User info: Cursor not open. ');  
 End IF;  
 Close  emp_cursor;  
End;  

The results of the execution are:

The 1-smith  
2-allen  
3-ward  
 pl/sql process has completed successfully.  

Note: This example uses Emp_cursor%isopen to determine whether a cursor is open, use Emp_cursor%rowcount to obtain the number of rows returned by the FETCH statement and output, use a loop to fetch the data, use the FETCH statement in the loop body, and use the EMP_ Cursor%notfound determines whether the fetch statement succeeds and exits the loop when the FETCH statement fails to indicate that the data has been taken out.
Exercise 1 Removes the Open Emp_cursor statement and executes the above program again.
Passing of cursor parameters
"Training 1" with parameters of the cursor.

SET serveroutput on  
        DECLARE  
            v_empno number (5);  
            V_ename VARCHAR2 (a);  
            CURSOR  Emp_cursor (p_deptno number,     p_job VARCHAR2)  
            is SELECT  empno, ename from emp  
            WHERE   Deptno = p_deptno and job = P_job;  
BEGIN  
     OPEN emp_cursor (' clerk ');  
    LOOP  
     FETCH emp_cursor into V_empno,v_ename;  
     EXIT when Emp_cursor%notfound;  
     Dbms_output. Put_Line (v_empno| | ', ' | | V_ename);  
      End LOOP;  
    End;  

The results of the execution are:

The 7934,miller  
        Pl/sql process has completed successfully.  

Description: The cursor emp_cursor defines two parameters: The P_DEPTNO represents the department number, and the p_job represents the position. The Statement Open emp_cursor (' clerk ') passes two parameter values to the cursor, that is, the department is 10, and the title is clerk, so the cursor query is for a department 10 employee with a job of clerk. The loop section is used to display the contents of the query.
Exercise 1 modifies the parameters of the Open statement: The department number is 20, the title is analyst, and it is executed again.
You can also pass parameters to a cursor through a variable, but the variable needs to be defined before the cursor and assigned before the cursor is opened. The above examples are revised as follows:
"Training 2" passes arguments to the cursor through a variable.

SET serveroutput on  
        DECLARE  
        v_empno number (5);  
        V_ename VARCHAR2 (a);  
        V_deptno number (5);  
V_job VARCHAR2 (a);  
         CURSOR emp_cursor  
            is SELECT empno, ename from emp  
            WHERE   deptno = v_deptno and job = V_job;  
        BEGIN  
         v_deptno:=10;  
         v_job:= ' clerk ';  
         OPEN Emp_cursor;  
        LOOP  
         FETCH emp_cursor into V_empno,v_ename;  
           EXIT when Emp_cursor%notfound;  
Dbms_output. Put_Line (v_empno| | ', ' | | V_ename);  
         End LOOP;  
        End;  

The results of the execution are:

The 7934,miller  
        Pl/sql process has completed successfully.  

Description: This program implements the same functionality as the previous program.

Use of dynamic SELECT statements and dynamic cursors
Oracle supports dynamic SELECT statements and dynamic cursors, and dynamic methods greatly extend the ability of programming.
For SELECT statements that have a row of query results, you can generate and execute them temporarily in the execution phase of the program by dynamically generating a query statement string, which is:
Execute IMMEDIATE query statement string into variable 1[, variable 2 ...];
The following is an example of dynamically generating a SELECT statement.
"Training 1" Dynamic select query.

SET serveroutput on   
        DECLARE   
        str varchar2 (MB);  
        V_ename VARCHAR2 (a);  
        Begin  
        str:= ' select ename from scott.emp where empno=7788 ';  
        Execute immediate str into v_ename;   
        Dbms_output.put_line (v_ename);  
        End;   

The results of the execution are:

The SCOTT  
        Pl/sql process has been successfully completed.  

Description: SELECT ... Into ... Statement is stored in the STR string and executed through the EXECUTE statement.
The cursor defined in the variable declaration section is static and cannot be modified while the program is running. Although it is possible to get different data through parameter passing, there are still a lot of limitations. By adopting dynamic cursors, you can generate a query statement as a cursor at any time during the runtime of the program. To use a dynamic cursor, you need to define a cursor type and then declare a cursor variable, which can be described dynamically during the execution of the program.

The statements that define the cursor type are as follows:
Type cursor name REF CURSOR;
The statement declaring a cursor variable is as follows:
Cursor variable name cursor type name;
In the executable section, you can open a dynamic cursor in the following form:
OPEN cursor variable name for query statement string;
Training 2 displays employee information in alphabetical order included in the name.
Enter and run the following program:

Declare   
 type cur_type is REF CURSOR;  
 Cur cur_type;  
 Rec Scott.emp%rowtype;  
 Str varchar2 (m);  
 Letter char:= ' A ';  
Begin  
        Loop          
         str:= ' select ename from emp where ename like '% ' | | letter| | ' %''';  
         Open cur for str;  
         Dbms_output.put_line (' Inclusive Letter ' | | letter| | ' 's name: ');  
          Loop  
         fetch cur into rec.ename;  
         Exit when Cur%notfound;  
        Dbms_output.put_line (rec.ename);  
End Loop;  
  Exit when letter= ' Z ';  
  LETTER:=CHR (ASCII (letter) +1);  
 End Loop;  
End  

The results of the operation are:

Contains the name of the letter A:  
ALLEN  
WARD  
MARTIN  
BLAKE  
CLARK  
ADAMS  
contains the name of the letter B:  
BLAKE  
contains the name of the letter C:  
CLARK  
SCOTT.  

Note: Use a double loop, in the outer loop body, dynamically generate the cursor's SELECT statement, and then open. The next letter in the alphabet can be obtained by using the statement LETTER:=CHR (ASCII (letter) +1).

Exception handling