oracle-cursor type, syntax, attributes and use, No_data_found and%notfound differences __oracle

I. Classification of Cursors

The Oracle database provides two types of cursors, static and dynamic, while static cursors are divided into hermit cursors and explicit cursors, and dynamic cursors are classified as weak and strongly typed. Second, static cursors

1, an explicit cursor
1). Declaration of the travel slogan law:

cursor cursor name [(cursor input parameter 1[, cursor input parameter 2] ...)]
[return returned type] is query statement

cursor name: Refers to the defined cursor name, generally using the cursor_ name of the named format.
Cursor Input Parameters: Specify the input parameters for the cursor, note that when specifying the parameter type, the length cannot be constrained, such as number (10) is incorrect.
return type: Represents the type of a row of data extracted by a cursor
Query statement: The query statement used by the cursor
cursor parameters and return types are circled to denote a condition that can be omitted, and you can declare a cursor without these two arguments

2). To open the Tour slogan method:
open cursor name [all defined cursor input parameters]

3). Extract the travel slogan method:
FETCH cursor name into receive variable

4). To close the Tour banner method:
Close cursor name

Use examples:

---Retrieves all jobs in the EMP table for manager employee Information DECLARE/* declaration cursor, (cursor input parameter variable is var_job) optional * * CURSOR cur_emp (var_job in varchar2:= ' salesman ') I
  The query statement used by the S/* cursor is */SELECT EMPNO, ename, SAL from EMP WHERE JOB = var_job; /* Declare a record variable/type record_emp is Var_empno EMP. Empno%type, Var_ename EMP. Ename%type, Var_sal EMP.
  Sal%type);
Emp_row record_emp;
  BEGIN/* Opens the cursor, specifying the input parameter value of manager*/open cur_emp (' MANAGER ');
  /* The cursor is pointed to the first row of the result set and stored in the record variable * * FETCH cur_emp into Emp_row; /* If the cursor has data, loop/while Cur_emp%found loop dbms_output. Put_Line (' Employee ID: ' | | Emp_row. Var_empno | |
                         ' Employee Name: ' | | Emp_row. Var_ename | | ' Employee Salary: ' | | Emp_row.
    Var_sal);
  /* Point the cursor to the next data in the result set * * FETCH cur_emp into Emp_row;
  End LOOP;
/* Close cursor * * CUR_EMP;

End; Alternatively, use the%rowtype type DECLARE CURSOR cur_emp is/* To use the Fetch+%rowtype query field here must be consistent with the order and number of fields in the table/SELECT EMPNO, ename,
  JOB, MGR, HireDate, SAL, COMM, DEPTNO from EMP; /* Define a%ROWTYPE receive variable */Var_emp_type emp%rOwtype;
  BEGIN OPEN cur_emp;
  FETCH cur_emp into Var_emp_type; While Cur_emp%found Loop dbms_output. Put_Line (' Employee ID: ' | | Var_emp_type. EMPNO | |
                         ' Employee Name: ' | | Var_emp_type. ename | |
                         ' Employee Salary: ' | | Var_emp_type.
    SAL);
  FETCH cur_emp into Var_emp_type;
  End LOOP;
Close cur_emp; End;

The display cursor has the following properties:

2. Implicit cursors

Pl/sql declares a cursor for all SQL data manipulation statements (including a select that returns a row), and is called an implicit cursor because the user cannot directly name and control such a cursor. When a user uses data manipulation language (DML) in Pl/sql, Oracle defines a hermit cursor named SQL, which can be used to obtain information related to the most recently executed SQL statement by examining the properties of an implicit cursor.

The implicit cursor property sheet is as follows:

Use examples:

--Add 100 to the salesman in the EMP table, use the%rowcount property of the hermit cursor SQL to output the number of employees who increase wages

DECLARE

BEGIN
  UPDATE EMP SET sal = sal + 100 WHERE JOB = ' salesman ';
  IF sql%notfound THEN
    dbms_output. Put_Line (' no qualified employee ');
  ELSE
    Dbms_output. Put_Line (' Up: ' | | Sql%rowcount | | ' Wages of an employee ');
  End IF;
End;

third, dynamic cursors

When used, a static cursor uses a query statement that determines which query is dynamically executed at run time, using a REF CURSOR (dynamic cursor) and a cursor variable.

1). Declaring the syntax of a cursor
Declaring a REF CURSOR requires 2 steps: Declaring the cursor type of ref and declaring the cursor variable with this type.

Syntax for declaring cursor types:
Type cursor type name is REF CURSOR
[return cursor returned value type]

cursor type name: The type of a cursor variable that is defined, typically in the form of a ref_type_ type name.
cursor return value type: Optional, defines the return value type of a cursor variable, note that must be a record variable.

ref cursors are both strongly typed and weakly typed, and if you specify the return value type of a cursor variable when you define a cursor variable type, it is a strong type and, if not specified, a weak type. And once a return value type is specified, the returned set of the bound query result must be the type defined in return when the cursor is opened.

To declare the syntax of a cursor variable:
cursor variable name cursor type name

2). To open the syntax of a cursor:
Open cursor variable name for query statement

3). Extract the cursor, with an explicit cursor.
4. Close the cursor with an explicit cursor.

Use examples:

-Weak type-increases the salary of employees below 3000 by 500, up to a maximum of 3000 DECLARE type ref_cursor_type is REF CURSOR; --Declares a weak type of dynamic cursor type ref_cursor ref_cursor_type; --Define a cursor v_sal EMP for declared weakly typed cursors. sal%type;--declares that a variable is used to receive an employee's salary v_empno EMP. empno%type;--declares that a variable is used to receive an employee number BEGIN open ref_cursor for SELECT SAL, EMPNO from emp;--open a cursor and specify the SQL statement to use Loop FETCH Ref_cursor into V_sal, v_empno;--points the cursor to a row of data and assigns the variable exit when ref_cursor%notfound;--exits if v_sal < 300 when the cursor has no data
      0 then--pay less than 3000 enter if, this if the last will execute update SQL if v_sal >= 2500 then--salary less than 3000 and greater than 2500 enter this if v_sal: = 3000;
      ELSE v_sal: = v_sal + 500;  End if;--ends an IF, an if corresponds to an if; update EMP SET SAL = v_sal WHERE EMPNO = v_empno;--Update employee pay end if;--close outermost IF ending

loop;--ends the loop loop close ref_cursor;--closes the cursor end; --strong type--query all employee names DECLARE type emp_ref_cursor is REF CURSOR return emp%rowtype;--Declare a strong type (specifying the return type) of a dynamic cursor ref_cursor EMP_ ref_cursor;--defines a declared strongly typed cursor V_emp_record emp%rowtype;--defines a receive variable BEGIN OPEN ref_cursor for
    SELECT * from EMP;
    LOOP FETCH ref_cursor into V_emp_record;
    EXIT when Ref_cursor%notfound; Dbms_output. Put_Line (V_emp_record.
  ENAME);
  End LOOP;
Close ref_cursor; End;

looping cursors with A for statement

You can simplify the processing code of an explicit cursor by using the FOR Statement loop cursor. The For statement loops Leng open cursors, gets the rows automatically from the active set, and then closes the cursor when all rows are processed. A loop cursor automatically creates a variable of type%rowtype and uses this variable as a record index.

Grammar:
For record variable in cursor variable
Loop
//Action record variable
End Loop;

A record variable is a record variable declared by Pl/sql, and the property of this variable is declared as a%rowtype type, and the scope is within the for loop, and the variable cannot be accessed outside of the for loop.

For Statement loop cursor attributes:
1. Automatically terminates after all records have been fetched from the cursor.
2. Extract and process each record in the cursor.
3. If the%notfound property returns True after extraction, the loop is terminated. If no rows are returned, the loop is not entered.

Use examples:

--Query all employee names
DECLARE
  CURSOR emp_cursor is SELECT * from EMP;
BEGIN
  /* Use a For statement to loop a cursor without manually opening/closing the cursor/for
  V_emp_record in Emp_cursor loop
    dbms_output. Put_Line (V_emp_record. ENAME);
  End LOOP;
End;

v. The difference between No_data_found and%notfound

1, SELECT ... The INTO statement triggers a no_data_found when 0 and more records are returned.
2, the%notfound is triggered when the WHERE clause of the UPDATE or DELETE statement is not found.
3, in the extraction cycle to use%notfound or%found to determine the loop exit conditions, do not use the no_data_found.