The concept of cursor:
A cursor is a memory workspace of SQL, defined as a variable by the system or user. The function of cursors is to temporarily store blocks of data extracted from databases. In some cases, data needs to be transferred from tables stored on disk to computer memory for processing, and finally the processing results are displayed or written back to the database. In this way, the speed of data processing will be improved, otherwise frequent disk data exchange will reduce efficiency.  
There are two types of cursors: explicit cursor and implicit cursor. The SELECT...INTO... query statement used in the aforementioned program can only extract one row of data from the database at a time. For this form of query and DML operation, the system will use an implicit cursor. But if you want to extract multiple rows of data, it is up to the programmer to define an explicit cursor and process it through statements related to the cursor. An explicit cursor corresponds to a SELECT statement that returns multiple rows and columns.  
Once the cursor is opened, the data is transferred from the database to the cursor variable, and then the application program decomposes the required data from the cursor variable and processes it.  
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.  
* One-line query operation: SELECT... INTO....  
When an implicit cursor is used in the system, the state and result of the operation can be known by the properties of the implicit cursor, and then the flow of the program can be controlled. Implicit cursors can be accessed using the name SQL, but it is important to note that only the cursor properties of the previous DML operation or single-line SELECT operation can always be accessed through the SQL cursor name. So you usually use the SQL cursor name to access attributes immediately after the operation is completed. There are four types of cursor attributes, as shown below.  

[Training 1] Use the attribute of implicit cursor to judge whether the modification of employee's salary is successful.  
Step 1: Enter and run the following procedures:

1. SET SERVEROUTPUT ON    
2.         BEGIN  
3.         UPDATE emp SET sal=sal+100 WHERE empno=1234;   
4.          IF SQL%FOUND THEN    
5.         DBMS_OUTPUT.PUT_LINE('Successful modification of employee wages!');   
6.         COMMIT;    
7.         ELSE  
8.         DBMS_OUTPUT.PUT_LINE('Failure to revise employees'salaries!');   
9.          END IF;    
10.         END;  

The results are as follows:

Step 2: Change Employee Number 1234 to 7788 and re-execute the above procedure:
The results are as follows:

Description: In this case, the SQL%FOUND attribute is used to judge whether the modification is successful or not, and the corresponding information is given.  
Explicit cursor
Definition and operation of cursors
The use of cursors is divided into the following four steps.  
1. Declare cursors
Declare cursors in the DECLEAR section in the following format:
CURSOR cursor name [(parameter 1 data type [, parameter 2 data type]]
IS SELECT statement;
The parameter is an optional part, and the defined parameter can appear in the WHERE clause of the SELECT statement. If parameters are defined, the actual parameters must be passed when the cursor is opened.  
SELECT statements are queries for tables or views, or even joint queries. WHERE condition, ORDER BY or GROUP BY clauses can be used, but INTO clauses can not be used. Variables defined before cursors can be used in SELECT statements.  
2. Open the cursor
In the executable section, open the cursor in the following format:
OPEN cursor name [(actual parameter 1 [, actual parameter 2]]];
When the cursor is opened, the query result of the SELECT statement is transmitted to the cursor workspace.  
3. Data extraction
In the executable part, the data in the cursor workspace is fetched into variables in the following format. The extraction operation must be performed after the cursor is opened.  
FETCH cursor name INTO variable name 1 [, variable name 2];
Or
FETCH cursor name INTO records variables;
When the cursor is opened, a pointer points to the data area. The FETCH statement returns one row of data pointed to by the pointer at a time. To return multiple rows, it needs to be executed repeatedly, which can be achieved by using a loop statement. Control loops can be performed by judging the properties of cursors.  
The following two formats are explained:
The variable name in the first format is a variable used to receive data from the cursor, which needs to be defined in advance. The number and type of variables should be the same as the number and type of field variables in the SELECT statement.  
The second format takes a row of data into a record variable at a time. It needs to use% ROWTYPE to define the record variable beforehand. This format is more convenient to use and does not need to define and use multiple variables separately.  
The methods for defining recorded variables are as follows:
Variable name table name | cursor name% ROWTYPE;
The table must exist and the cursor name must be defined first.  
4. Close the cursor
CLOSE cursor name;
When an explicit cursor is opened, it must be explicitly closed. Once the cursor is closed, the resources occupied by the cursor are released, and the cursor becomes invalid. It must be reopened before it can be used.  
Here is a simple exercise using explicit cursors.  
[Training 1] The names and positions of 7788 employees in emp table were extracted with a cursor.  

1. SET SERVEROUTPUT ON  
2.         DECLARE    
3.          v_ename VARCHAR2(10);   
4.          v_job VARCHAR2(10);   
5.          CURSOR emp_cursor IS    
6.          SELECT ename,job FROM emp WHERE empno=7788;   
7.          BEGIN  
8.      OPEN emp_cursor;   
9.     FETCH emp_cursor INTO v_ename,v_job;   
10.         DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);   
11.         CLOSE emp_cursor;   
12.         END;  

The results are as follows:

1. SCOTT,ANALYST   
2.         PL/SQL The process has been successfully completed.   

Description: The program extracts and displays the name and position of Employee 7788 by defining the cursor emp_cursor.  
As an improvement of the above examples, the following training uses recorded variables.  
[Training 2] The name, position and salary of 7788 employees in emp table were extracted with a cursor.  

1. SET SERVEROUTPUT ON  
2.         DECLARE  
3.          CURSOR emp_cursor IS  SELECT ename,job,sal FROM emp WHERE empno=7788;   
4.          emp_record emp_cursor%ROWTYPE;   
5.         BEGIN  
6. OPEN emp_cursor;       
7.         FETCH emp_cursor INTO emp_record;   
8.            DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal);   
9.          CLOSE emp_cursor;   
10.         END;  

The results are as follows:

1. SCOTT,ANALYST,3000   
2.         PL/SQL The process has been successfully completed.   

Description: Record variables are used to receive data in an example. Record variables are defined by cursor variables and need to appear after the cursor definition.  
Note: The contents of recorded variables can be obtained in the following form:
Record variable name. Field name.  
[Training 3] Show the names and salaries of the top three employees with the highest salaries.  

1. SET SERVEROUTPUT ON  
2.         DECLARE  
3.          V_ename VARCHAR2(10);   
4.         V_sal NUMBER(5);   
5.         CURSOR emp_cursor IS  SELECT ename,sal FROM emp ORDER BY sal DESC;   
6.         BEGIN  
7.          OPEN emp_cursor;   
8.          FOR I IN 1..3 LOOP   
9.            FETCH emp_cursor INTO v_ename,v_sal;   
10.          DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);   
11.           END LOOP;   
12.          CLOSE emp_cursor;   
13.          END;  

The results are as follows:

1. KING,5000   
2.      SCOTT,3000   
3.      FORD,3000   
4.      PL/SQL The process has been successfully completed.  

Explanation: ORDER BY clause is used to sort cursor definitions, and loop statement is used to extract multi-line data.  
Cursor loop
[Training 1] Use a special FOR loop to display the numbers and names of all employees.  

1. SET SERVEROUTPUT ON  
2. DECLARE  
3.   CURSOR emp_cursor IS    
4.   SELECT empno, ename FROM emp;   
5. BEGIN  
6. FOR Emp_record IN emp_cursor LOOP      
7.     DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);   
8.     END LOOP;   
9.     END;  

The results are as follows:

Explanation: It can be seen that the circular form is very simple, implying the definition of recording variables, the opening, extraction and closing of cursors. Emp_record is an implicitly defined record variable, and the number of executions of the loop is consistent with the number of rows of data obtained by the cursor.  
[Training 2] Another form of cursor loop.  

1. SET SERVEROUTPUT ON    
2. BEGIN  
3.  FOR re IN (SELECT ename FROM EMP)  LOOP   
4.   DBMS_OUTPUT.PUT_LINE(re.ename)   
5.  END LOOP;   
6. END;  

The results are as follows:

1. SMITH   
2. ALLEN   
3. WARD   
4. JONES  

Explanation: This form is simpler, the definition of cursor is omitted, and the SELECT query statement of cursor appears directly in the loop.  
Explicit cursor properties
Although the cursor data can be obtained in the previous form, it is a more flexible method to use some of its attributes for structure control after the cursor is defined. The properties of an explicit cursor are shown below.  

The properties of the cursor can be obtained in the following form:
Cursor name% attribute
To determine whether the cursor emp_cursor is open, you can use the attribute emp_cursor%ISOPEN. If the cursor has been opened, the return value is "true" or "false". Specific reference can be made to the following training.  
[Training 1] Attribute exercises using cursors.  

1. SET SERVEROUTPUT ON  
2. DECLARE  
3.   V_ename VARCHAR2(10);   
4.   CURSOR emp_cursor IS    
5.   SELECT ename FROM emp;   
6. BEGIN  
7.  OPEN emp_cursor;   
8.  IF emp_cursor%ISOPEN THEN  
9. LOOP   
10.    FETCH emp_cursor INTO v_ename;   
11.    EXIT WHEN emp_cursor%NOTFOUND;   
12.    DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);   
13.   END LOOP;   
14.  ELSE  
15.   DBMS_OUTPUT.PUT_LINE('User Information: The cursor is not open!');   
16.  END IF;   
17.  CLOSE  emp_cursor;   
18. END;  

The results are as follows:

Explanation: This example uses emp_cursor%ISOPEN to judge whether the cursor is open or not; uses emp_cursor%ROWCOUNT to get the number of data rows returned by FETCH statements so far and output them; uses loop to get data and uses FETCH statements in the loop body; uses emp_cursor%NOTFOUND to judge whether the FETCH statements are successfully executed or not; when the FETCH statements fail, it indicates that the data has been taken out and exits to follow. Ring.  
[Exercise 1] Remove OPEN emp_cursor; statement and re-execute the above program.  
Transfer of cursor parameters
[Training 1] Cursor with parameters.  

1. SET SERVEROUTPUT ON  
2.         DECLARE  
3.             V_empno NUMBER(5);   
4.             V_ename VARCHAR2(10);   
5.             CURSOR  emp_cursor(p_deptno NUMBER,     p_job VARCHAR2) IS  
6.             SELECT  empno, ename FROM emp   
7.             WHERE   deptno = p_deptno AND job = p_job;   
8. BEGIN  
9.      OPEN emp_cursor(10, 'CLERK');   
10.     LOOP   
11.      FETCH emp_cursor INTO v_empno,v_ename;   
12.      EXIT WHEN emp_cursor%NOTFOUND;   
13.      DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);   
14.       END LOOP;   
15.     END;  

The results are as follows:

Explanation: The cursor emp_cursor defines two parameters: p_deptno for department number and p_job for position. The statement OPEN emp_cursor (10,'CLERK') passes two parameter values to the cursor, that is, the Department is 10 and the position is CLERK, so the content of the cursor query is the employees of the Department 10 whose position is CLERK. The loop section is used to display the contents of the query.  
[Exercise 1] Modify the parameters of Open statement: Department number is 20, post is AnalyYST, and re-execute.  
You can also pass parameters to the cursor through variables, but variables need to be defined before the cursor and assigned before the cursor is opened. Re-modify the above examples as follows:
[Training 2] Pass parameters to the cursor through variables.  

1. SET SERVEROUTPUT ON  
2.         DECLARE  
3.         v_empno NUMBER(5);   
4.         v_ename VARCHAR2(10);   
5.         v_deptno NUMBER(5);   
6. v_job VARCHAR2(10);   
7.          CURSOR emp_cursor IS  
8.             SELECT empno, ename FROM emp   
9.             WHERE   deptno = v_deptno AND job = v_job;   
10.         BEGIN  
11.          v_deptno:=10;   
12.          v_job:='CLERK';   
13.          OPEN emp_cursor;   
14.         LOOP   
15.          FETCH emp_cursor INTO v_empno,v_ename;   
16.            EXIT WHEN emp_cursor%NOTFOUND;   
17. DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);   
18.          END LOOP;   
19.         END;  

The results are as follows:

Description: The program and the previous program to achieve the same function.  
Use of Dynamic SELECT Statements and Dynamic Cursors
Oracle supports dynamic SELECT statements and dynamic cursors, and dynamic methods greatly expand the ability of programming.  
For SELECT statements with one row of query results, the query string can be generated dynamically and executed temporarily during the execution phase of the program. The grammar is as follows:
execute immediate query statement string into variable 1 [, variable 2];
The following is an example of dynamically generating SELECT statements.  
[Training 1] Dynamic SELECT query.  

1. SET SERVEROUTPUT ON    
2.         DECLARE    
3.         str varchar2(100);   
4.         v_ename varchar2(10);   
5.         begin  
6.         str:='select ename from scott.emp where empno=7788';   
7.         execute immediate str into v_ename;    
8.         dbms_output.put_line(v_ename);   
9.         END;   

The results are as follows:

1. SCOTT   
2.         PL/SQL The process has been successfully completed.  

Description: SELECT...INTO... Statements are stored in STR strings and executed through EXECUTE statements.  
The cursor defined in the variable declaration section is static and cannot be modified during the running of the program. Although different data can be obtained by parameter transfer, there are still many limitations. By using dynamic cursor, a query statement can be generated as a cursor at any time during the running stage of the program. To use dynamic cursors, you need to define a cursor type first, and then declare a cursor variable. The query statement corresponding to the cursor can be described dynamically during the execution of the program.  
The statement defining the cursor type is as follows:
TYPE cursor type name REF CURSOR;
The statement declaring cursor variables is as follows:
Cursor variable name cursor type name;
In the executable, you can open a dynamic cursor as follows:
OPEN cursor variable name FOR query statement string;
[Training 2] Show employee information in alphabetical order contained in the name.  
Enter and run the following program:

1. declare    
2.  type cur_type is ref cursor;   
3.  cur cur_type;   
4.  rec scott.emp%rowtype;   
5.  str varchar2(50);   
6.  letter char:= 'A';   
7. begin  
8.         loop           
9.          str:= 'select ename from emp where ename like ''%'||letter||'%''';   
10.          open cur for str;   
11.          dbms_output.put_line('Containing letters'||letter||'Name:');   
12.           loop   
13.          fetch cur into rec.ename;   
14.          exit when cur%notfound;   
15.         dbms_output.put_line(rec.ename);   
16. end loop;   
17.   exit when letter='Z';   
18.   letter:=chr(ascii(letter)+1);   
19.  end loop;   
20. end;  

The results are as follows:

1. Containing letters A Name:   
2. ALLEN   
3. WARD   
4. MARTIN   
5. BLAKE   
6. CLARK   
7. ADAMS   
8. JAMES   
9. Containing letters B Name:   
10. BLAKE   
11. Containing letters C Name:   
12. CLARK   
13. SCOTT  

Description: Double loops are used to dynamically generate the SELECT statement of cursor in the outer loop body, and then open it. The next letter in the alphabet can be obtained by the statement letter:=chr(ascii(letter)+1).  

Exception handling
Error handling
After the executable part of the program, the error handling part is composed of branches guided by WHEN statements. The grammar of error handling is as follows:
EXCEPTION 
WHEN Error 1[OR Error 2] THEN
Statement sequence 1;
WHEN Error 3[OR Error 4] THEN
Statement sequence 2;
WHEN OTHERS 
Statement sequence n;
END; 
Among them:
Errors are standard errors predefined by the system in the standard package, or errors customized by the user in the description section of the program. See the system predefined error types in the next section.  
Statement sequence is the error handling part of different branches.  
All errors that appear after WHEN are capturable errors. Others that are not captured will be processed in the WHEN OTHERS section. OTHENS must be the last branch of error handling in the EXCEPTION section. To further determine the type of error in this branch, the system error number and error information can be obtained by using the predefined functions SQLCODE() and SQLLERRM ().  
If an error occurs in a subblock of a program, but there is no error handling part in the subblock, the error is passed to the main program.  
The following is an example of a predefined exception caused by a query number error.  
[Training 1] Query the name of the employee numbered 1234.  

1. SET SERVEROUTPUT ON  
2. DECLARE  
3. v_name VARCHAR2(10);   
4. BEGIN  
5.    SELECT   ename   
6.    INTO     v_name   
7.    FROM     emp   
8.    WHERE    empno = 1234;   
9. DBMS_OUTPUT.PUT_LINE('The employee's name is:'|| v_name);   
10. EXCEPTION   
11.   WHEN NO_DATA_FOUND THEN  
12.     DBMS_OUTPUT.PUT_LINE('Number error, no corresponding employees found!');   
13.   WHEN OTHERS THEN  
14.     DBMS_OUTPUT.PUT_LINE('There were other mistakes!');   
15. END;  

The results are as follows:

Note: In the above query, because the employee numbered 1234 does not exist, the type of occurrence will be "NO_DATA"_
The exception of FOUND. "NO_DATA_FOUND" is the predefined error type of the system. The WHEN statement under EXCEPTION will catch the exception and execute the corresponding code part. In this case, the user-defined error message "Number error, no corresponding employee found!" is output. If other types of errors occur, the code part under OTHERS condition will be executed, showing "Other errors occur!".  
[Training 2] System errors are displayed by program code.  

1. SET SERVEROUTPUT ON  
2. DECLARE  
3. v_temp NUMBER(5):=1;   
4. BEGIN  
5. v_temp:=v_temp/0;   
6. EXCEPTION   
7.   WHEN OTHERS THEN  
8. DBMS_OUTPUT.PUT_LINE('A system error occurred!');   
9.     DBMS_OUTPUT.PUT_LINE('Error code:'|| SQLCODE( ));   
10.     DBMS_OUTPUT.PUT_LINE('Error message:' ||SQLERRM( ));   
11.         END;  

The results are as follows:

Explanation: A dividing error occurred in the running of the program, which was captured by WHEN OTHERS. The user's own output statement was executed to display the error information, and then ended normally. In the error handling part, the predefined functions SQLCODE() and SQLLERRM () are used to further obtain the error code and type information.  
Predefined error
Oracle has many system errors, but only a few common errors are defined in the standard package. Defined errors can be judged by standard error names in the EXCEPTION section and exception handling. Common system predefined exceptions are shown below.  

For example, if a program inserts duplicate values into the primary key column of a table, a DUP_VAL_ON_INDEX error will occur.  
If a system error is not defined in the standard package, it needs to be defined in the description section. The grammar is as follows:
Error name EXCEPTION;
After definition, PRAGMA EXCEPTION_INIT is used to associate a defined error with a particular Oracle error code, which can be used as the predefined error of the system. The grammar is as follows:
PRAGMA EXCEPTION_INIT (Error Name, - Error Code);
[Training 1] Define a new type of system error.  

1. SET SERVEROUTPUT ON  
2.         DECLARE  
3.         V_ENAME VARCHAR2(10);   
4.         NULL_INSERT_ERROR EXCEPTION;   
5.         PRAGMA EXCEPTION_INIT(NULL_INSERT_ERROR,-1400);   
6.         BEGIN  
7.         INSERT INTO EMP(EMPNO) VALUES(NULL);   
8. EXCEPTION   
9. WHEN NULL_INSERT_ERROR THEN  
10.     DBMS_OUTPUT.PUT_LINE('Unable to insert NULL Value!');   
11.   WHEN OTHERS  THEN  
12.     DBMS_OUTPUT.PUT_LINE('Other system errors occurred!');   
13. END;  

The results are as follows:

Note: NULL_INSERT_ERROR is a custom exception associated with system error 1400.  
Custom exception
Programmers can use the mechanism of causing exceptions to design programs and define their own types of exceptions. New exception types can be defined in the declaration section. The grammar of the definition is:
Error name EXCEPTION;
User-defined errors cannot be triggered by the system, but must be explicitly triggered by the program. The grammar of triggering is:
RAISE incorrect name;
RAISE can also be used to trigger simulation system errors, for example, RAISE ZERO_DIVIDE will trigger simulation dividing errors.  
Exceptions can also be thrown using the RAISE_APPLICATION_ERROR function. The function passes two parameters, the first is the user-defined error number, and the second is the user-defined error information. The number of exceptions thrown using this function should be chosen between 20,000 and 20,999.  
Customize exception handling errors in the same way as before.  
[Training 1] Insert new employees and limit the number of employees to 7000-8000.  

1. SET SERVEROUTPUT ON   
2. DECLARE   
3. new_no NUMBER(10);   
4. new_excp1 EXCEPTION;   
5. new_excp2 EXCEPTION;   
6. BEGIN   
7. new_no:=6789;   
8. INSERT INTO emp(empno,ename)   
9.   VALUES(new_no, 'Xiao Zheng');   
10.   IF new_no<7000 THEN   
11.     RAISE new_excp1;   
12.   END IF;   
13.   IF new_no>8000 THEN   
14.     RAISE new_excp2;   
15.   END IF;   
16.   COMMIT;   
17. EXCEPTION   
18. WHEN new_excp1  THEN   
19.     ROLLBACK;   
20.     DBMS_OUTPUT.PUT_LINE('The employee number is less than the lower limit of 7000!');   
21.     WHEN new_excp2  THEN   
22.     ROLLBACK;   
23.     DBMS_OUTPUT.PUT_LINE('Employee Number Over 8000 Upper Limit!');   
24.     END;  

The results are as follows:
The employee number is less than the lower limit of 7000!  
The PL/SQL process has been successfully completed.  
Description: In this example, two exceptions are defined: new_excp1 and new_excp2, representing errors with numbers less than 7000 and more than 8000, respectively. In the program, by judging the size of the number, the corresponding exception is generated, and the insertion operation is rolled back in the exception handling part, and the corresponding error information is displayed.  
[Training 2] Use RAISE_APPLICATION_ERROR function to cause system anomalies.  

1. SET SERVEROUTPUT ON  
2. DECLARE  
3. New_no NUMBER(10);   
4. BEGIN  
5.   New_no:=6789;   
6.  INSERT INTO    emp(empno,ename)   
7.   VALUES(new_no, 'JAMES');   
8. IF new_no<7000 THEN  
9.     ROLLBACK;   
10.     RAISE_APPLICATION_ERROR(-20001, 'Number less than 7000 lower limit!');   
11.   END IF;   
12.   IF new_no>8000 THEN  
13.     ROLLBACK;   
14.     RAISE_APPLICATION_ERROR (-20002, 'Number greater than the lower limit of 8000!');   
15.   END IF;   
16. END;  

The results are as follows:

1. DECLARE  
2.         *   
3.         ERROR Located at No. 1 That's ok:   
4.         ORA-20001: Number less than 7000 lower limit!   
5.         ORA-06512: stay line 9  

Description: In this training, RAISE_APPLICATION_ERROR is used to raise custom exceptions and display them in the wrong way. Error numbers are 2001 and 20002.  
Note: Compared with the previous training, this method does not need to define exceptions in advance, but can be triggered directly.  
You can refer to the following program fragments to record the error information into the table, where errors are the table for recording error information, SQLCODE is the error number for the occurrence of an exception, and SQLERRM is the error information for the occurrence of an exception.  
DECLARE 
  v_error_code      NUMBER; 
  v_error_message   VARCHAR2(255); 
BEGIN 
... 
EXCEPTION 
... 
WHEN OTHERS THEN 
    v_error_code := SQLCODE ; 
    v_error_message := SQLERRM ; 
    INSERT INTO errors 
    VALUES(v_error_code, v_error_message); 
END; 
[Exercise 1] Modify the employee's salary and control the range of modification by triggering anomalies between 600 and 6000.  
Stage training
[Training 1] Copy employees from one table to another.  
Step 1: Create a new table EMP1 with the same structure as the EMP table:
CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2; 
Step 2: Move the employee from the EMP table to the EMP1 table by specifying the employee number:

1. SET SERVEROUTPUT ON    
2. DECLARE  
3. v_empno NUMBER(5):=7788;   
4. emp_rec emp%ROWTYPE;   
5. BEGIN  
6.  SELECT * INTO emp_rec FROM emp WHERE empno=v_empno;   
7.  DELETE FROM emp WHERE empno=v_empno;   
8. INSERT INTO emp1 VALUES emp_rec;   
9.  IF SQL%FOUND THEN  
10.   COMMIT;   
11.   DBMS_OUTPUT.PUT_LINE('Employee replication success!');   
12.  ELSE    
13.   ROLLBACK;   
14.   DBMS_OUTPUT.PUT_LINE('Employee replication failed!');   
15.  END IF;   
16. END;  

The results are as follows:
Employee replication success!  
The PL/SQL process has been successfully completed.  
Step 2: Display the replication result:
SELECT empno,ename,job FROM emp1; 
The results are as follows:
 

1. EMPNO ENAME      JOB   
2. ------------- -------------- ----------------   
3.     7788  SCOTT      ANALYST  

Explanation: emp_rec variable is a record variable defined by EMP table, and the SELECT...INTO... statement passes the entire record to the variable. The INSERT statement inserts the entire record variable into the emp1 table, commits the transaction if the insertion is successful (SQL%FOUND is true), or rolls back the cancellation transaction. Try to modify employee number 7902 and re-execute the above procedure.  
[Training 2] Export employees'wages, which are expressed in different heights.  
Enter and execute the following procedures:

1. SET SERVEROUTPUT ON    
2. BEGIN  
3.  FOR re IN (SELECT ename,sal FROM EMP)  LOOP   
4.   DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*'));   
5.  END LOOP;   
6. END;  

The output is as follows:

1. SMITH       ********   
2. ALLEN           ****************   
3. WARD        *************   
4. JONES           ******************************   
5. MARTIN      *************   
6. BLAKE       *****************************   
7. CLARK           *****************************   
8. SCOTT           ******************************   
9. KING            **************************************************   
10. TURNER      ***************   
11. ADAMS       ***********   
12. JAMES           **********   
13. FORD            ******************************   
14. MILLER          *************   
15.          The results of implementation are as follows:   
16.         PL/SQL The process has been successfully completed.  

Explanation: The first rpad function produces alignment effect, and the second rpad function produces different numbers of * according to the amount of wages. The program adopts the form of implicit cursor loops.  
[Training 3] Write a program to format and output Department information.  
Enter and execute the following procedures:

1. SET SERVEROUTPUT ON    
2.         DECLARE  
3.          v_count number:=0;   
4.          CURSOR dept_cursor IS SELECT * FROM dept;   
5.         BEGIN  
6.           DBMS_OUTPUT.PUT_LINE('Department List');   
7. DBMS_OUTPUT.PUT_LINE('---------------------------------');   
8.          FOR Dept_record IN dept_cursor LOOP      
9.          DBMS_OUTPUT.PUT_LINE('Department number:'|| Dept_record.deptno);   
10.          DBMS_OUTPUT.PUT_LINE('Department name:'|| Dept_record.dname);   
11.             DBMS_OUTPUT.PUT_LINE('City:'|| Dept_record.loc);   
12. DBMS_OUTPUT.PUT_LINE('---------------------------------');   
13.       v_count:= v_count+1;   
14.         END LOOP;   
15.          DBMS_OUTPUT.PUT_LINE('Share'||to_char(v_count)||'Department!');   
16.         END;  

The output is as follows:

Description: In this program, field contents are arranged vertically. The V_count variable records the number of cycles, that is, the number of departments.  
[Training 4] It is known that each department has a manager, who compiles a program to count the name of the export department, the total number of departments, the total salary and the Department manager.  
Enter and execute the following procedures:

1. SET SERVEROUTPUT ON    
2. DECLARE  
3.  v_deptno number(8);   
4.  v_count number(3);   
5.  v_sumsal number(6);   
6.  v_dname  varchar2(15);   
7. v_manager  varchar2(15);   
8.  CURSOR list_cursor IS  
9.    SELECT deptno,count(*),sum(sal) FROM emp group by deptno;   
10. BEGIN  
11.   OPEN list_cursor;    
12.   DBMS_OUTPUT.PUT_LINE('----------- ministry door Unification meter surface -----------');   
13. DBMS_OUTPUT.PUT_LINE('Department name   Total number  Gross wage   Division Manager');   
14.   FETCH list_cursor INTO v_deptno,v_count,v_sumsal;    
15.   WHILE list_cursor%found LOOP     
16.  SELECT dname INTO v_dname FROM dept   
17.     WHERE deptno=v_deptno;   
18.     SELECT ename INTO v_manager FROM emp    
19.     WHERE deptno=v_deptno and job='MANAGER';   
20. DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_char(v_count),8)   
21.       ||rpad(to_char(v_sumsal),9)||v_manager);   
22.     FETCH list_cursor INTO v_deptno,v_count,v_sumsal;    
23.     END LOOP;   
24.         DBMS_OUTPUT.PUT_LINE('--------------------------------------');   
25.         CLOSE list_cursor;   
26.         END;  

The output is as follows:

Explanation: The SELECT statement with grouping function is used in the cursor to calculate the total number and total wages of each department. Then find the manager of the department according to the department number and position. The procedure assumes that each department has a manager.  
[Training 5] Increase wages for employees. Starting with low-wage employees, increase the original wage by 10% for each person. Limit the total wage increase to 800 yuan, showing the number and balance of wage increases.  
Enter and debug the following programs:

1. SET SERVEROUTPUT ON    
2. DECLARE    
3.   V_NAME CHAR(10);   
4.   V_EMPNO NUMBER(5);   
5.   V_SAL NUMBER(8);   
6.   V_SAL1 NUMBER(8);   
7.   V_TOTAL NUMBER(8) := 800;     --Total increase in wages   
8. V_NUM NUMBER(5):=0;     --Number of wage increases   
9.          CURSOR emp_cursor IS    
10.           SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC;   
11.         BEGIN  
12.          OPEN emp_cursor;   
13.         DBMS_OUTPUT.PUT_LINE('Full name      Original wage  New salary');    
14.         DBMS_OUTPUT.PUT_LINE('---------------------------');    
15.          LOOP   
16.             FETCH emp_cursor INTO V_EMPNO,V_NAME,V_SAL;   
17. EXIT WHEN emp_cursor%NOTFOUND;   
18.          V_SAL1:= V_SAL*0.1;   
19.             IF V_TOTAL>V_SAL1 THEN  
20.             V_TOTAL := V_TOTAL - V_SAL1;   
21.             V_NUM:=V_NUM+1;   
22.     DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||   
23.         TO_CHAR(V_SAL+V_SAL1,'99999'));   
24.              UPDATE EMP SET SAL=SAL+V_SAL1   
25.              WHERE EMPNO=V_EMPNO;   
26.          ELSE  
27. DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||TO_CHAR(V_SAL,'99999'));   
28.          END IF;   
29.         END LOOP;   
30.         DBMS_OUTPUT.PUT_LINE('---------------------------');   
31.         DBMS_OUTPUT.PUT_LINE('Increase in wages:'||V_NUM||' Surplus wages:'||V_TOTAL);     
32.          CLOSE emp_cursor;    
33.          COMMIT;   
34.          END;  

The output is as follows:

1. Full name        Original wage  New salary   
2.         ---------------------------------------------   
3. SMITH       1289   1418   
4. JAMES       1531   1684   
5. MARTIN      1664   1830   
6. MILLER          1730   1903   
7. ALLEN           1760   1936   
8. ADAMS       1771   1771   
9. TURNER      1815   1815   
10. WARD        1830   1830   
11. BLAKE       2850   2850   
12. CLARK       2850   2850   
13. JONES           2975   2975   
14. FORD            3000   3000   
15. KING            5000   5000   
16. -----------------------------------------------   
17. Increase in wages: 5 Surplus wage: 3   
18. PL/SQL The process has been successfully completed.  

[Exercise 1] Export the employee's name, salary and the difference between salary and average salary in the order of department number from small to large.  
[Exercise 2] Increase wages for all employees by 30% within 1000, 20% between 1000 and 2000, and 10% over 2000.