Reference article: https://www.cnblogs.com/huyong/archive/2011/05/04/2036377.html
In PL/SQL programs, cursors are often used to handle transactions with multiple rows of records.
There are four steps to use: define, open, extract, and close.
Example:
09:52:04 SCOTT@std1> DECLARE 09:52:07 2 CURSOR c_cursor 09:52:07 3 IS SELECT ename, sal 09:52:07 4 FROM emp 09:52:07 5 WHERE rownum<11; 09:52:07 6 v_ename emp.ename%TYPE; 09:52:07 7 v_sal emp.ename%TYPE; 09:52:07 8 BEGIN 09:52:07 9 OPEN c_cursor; 09:52:07 10 FETCH c_cursor INTO v_ename, v_sal; 09:52:07 11 WHILE c_cursor%FOUND LOOP 09:52:07 12 DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) ); 09:52:07 13 FETCH c_cursor INTO v_ename, v_sal; 09:52:07 14 END LOOP; 09:52:07 15 CLOSE c_cursor; 09:52:08 16 END; 09:52:09 17 / SMITH---800 ALLEN---1600 WARD---1250 JONES---2975 MARTIN---1250 BLAKE---2850 CLARK---2450 SCOTT---3000 TURNER---1500 ADAMS---1100 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00
10:01:43 SCOTT@std1> DECLARE 11:31:04 2 deptrec dept%Rowtype; 11:31:04 3 dept_name dept.dname%TYPE; 11:31:04 4 dept_loc dept.loc%TYPE; 11:31:04 5 CURSOR c1 IS SELECT dname,loc FROM dept WHERE deptno<=30; 11:31:04 6 CURSOR c2(dept_no NUMBER DEFAULT 10) IS SELECT dname,loc FROM dept WHERE deptno <= dept_no; 11:31:04 7 CURSOR c3(dept_no NUMBER DEFAULT 10) IS SELECT * FROM dept WHERE deptno <= dept_no; 11:31:04 8 11:31:04 9 BEGIN 11:31:04 10 OPEN c1; 11:31:04 11 LOOP 11:31:04 12 FETCH c1 INTO dept_name,dept_loc; 11:31:04 13 EXIT WHEN c1%NOTFOUND; 11:31:04 14 dbms_output.put_line(dept_name||'---'||dept_loc); 11:31:04 15 END LOOP; 11:31:04 16 CLOSE c1; 11:31:04 17 11:31:04 18 OPEN c2; 11:31:04 19 LOOP 11:31:04 20 FETCH c2 INTO dept_name,dept_loc; 11:31:04 21 EXIT WHEN c2%NOTFOUND; 11:31:04 22 dbms_output.put_line(dept_name||'---'||dept_loc); 11:31:04 23 END LOOP; 11:31:04 24 CLOSE c2; 11:31:04 25 11:31:04 26 OPEN c3(dept_no => 20); 11:31:04 27 LOOP 11:31:04 28 FETCH c3 INTO deptrec; 11:31:04 29 EXIT WHEN c3%NOTFOUND; 11:31:04 30 dbms_output.put_line(deptrec.deptno||'---'||deptrec.dname||'---'||deptrec.loc); 11:31:04 31 END LOOP; 11:31:04 32 CLOSE c3; 11:31:04 33 END; 11:31:06 34 / ACCOUNTING---NEW YORK RESEARCH---DALLAS SALES---CHICAGO ACCOUNTING---NEW YORK 10---ACCOUNTING---NEW YORK 20---RESEARCH---DALLAS PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 11:31:07 SCOTT@std1>
Cursor properties: Cursor_name%FOUND Boolean property is TRUE when the last cursor extraction operation FETCH succeeds, otherwise FALSE. Cursor_name%NOTFOUND Boolean property, contrary to%FOUND; Cursor_name%ISOPEN Boolean property, returns TRUE when the cursor is open; Cursor_name%ROWCOUNT numeric property that returns the number of records read from the cursor Example
15:04:04 SCOTT@std1> set serverout on
15:04:27 SCOTT@std1> DECLARE
15:04:40 2 v_empno emp.empno%TYPE;
15:04:40 3 v_sal emp.sal%TYPE;
15:04:40 4 CURSOR c_cursor IS SELECT empno,sal FROM emp;
15:04:40 5 BEGIN
15:04:40 6 OPEN c_cursor;
15:04:40 7 LOOP
15:04:40 8 FETCH c_cursor INTO v_empno,v_sal;
15:04:40 9 EXIT WHEN c_cursor%NOTFOUND;
15:04:40 10 IF v_sal<1200 THEN
15:04:40 11 UPDATE emp SET sal=sal+50 WHERE empno=v_empno;
15:04:41 12 dbms_output.put_line('Coded as'||v_empno||'Wages have been updated!');
15:04:41 13 END IF;
15:04:41 14 END LOOP;
15:04:41 15 CLOSE c_cursor;
15:04:41 16 END;
15:04:42 17 /
//Wage code 7369 has been updated!
//Wage code 7876 has been updated!
//The code 7900 salary has been updated!
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
15:04:43 SCOTT@std1>
15:04:43 SCOTT@std1> DECLARE
15:06:12 2 v_name emp.ename%TYPE;
15:06:12 3 v_job emp.job%TYPE;
15:06:12 4 CURSOR c1 IS SELECT ename,job FROM emp WHERE deptno=20;
15:06:12 5 BEGIN
15:06:12 6 OPEN c1;
15:06:12 7 LOOP
15:06:12 8 FETCH c1 INTO v_name,v_job;
15:06:12 9 IF c1%FOUND THEN
15:06:12 10 dbms_output.put_line(v_name||'The position is'||v_job);
15:06:12 11 ELSE
15:06:12 12 dbms_output.put_line('The results have been processed.');
15:06:12 13 EXIT;
15:06:12 14 END IF;
15:06:12 15 END LOOP;
15:06:12 16 CLOSE c1;
15:06:12 17 END;
15:06:14 18 /
SMITH The position is CLERK
JONES The position is MANAGER
SCOTT The position is ANALYST
ADAMS The position is CLERK
FORD The position is ANALYST
//The results have been processed.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
15:06:15 SCOTT@std1>
15:06:15 SCOTT@std1> DECLARE
15:38:26 2 v_ename emp.ename%TYPE;
15:38:26 3 v_hiredate emp.hiredate%TYPE;
15:38:26 4 CURSOR c1(dept_id NUMBER,jobid VARCHAR2) IS SELECT ename,hiredate FROM emp WHERE deptno=dept_id AND job=jobid;
15:38:26 5 BEGIN
15:38:26 6 OPEN c1(30,'SALESMAN');
15:38:26 7 LOOP
15:38:26 8 FETCH c1 INTO v_ename,v_hiredate;
15:38:26 9 IF c1%FOUND THEN
15:38:26 10 dbms_output.put_line(v_ename||'The date of employment is:'||v_hiredate);
15:38:26 11 ELSE
15:38:26 12 dbms_output.put_line('The result set has been processed.');
15:38:26 13 EXIT;
15:38:26 14 END IF;
15:38:26 15 END LOOP;
15:38:26 16 CLOSE c1;
15:38:26 17 END;
15:38:27 18 /
ALLEN The date of employment is 20:-FEB-81
WARD The date of employment is: 22-FEB-81
MARTIN The date of employment is 28:-SEP-81
TURNER The date of employment is 08.-SEP-81
//The result set has been processed.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
15:38:28 SCOTT@std1>
15:38:28 SCOTT@std1> DECLARE
16:22:36 2 TYPE emp_record_type IS RECORD(
16:22:36 3 v_ename emp.ename%TYPE,
16:22:36 4 v_hiredate emp.hiredate%TYPE);
16:22:36 5 v_emp_record emp_record_type;
16:22:36 6
16:22:36 7 CURSOR c1(dept_id NUMBER,jobid VARCHAR2) RETURN emp_record_type IS SELECT ename,hiredate FROM emp WHERE deptno=dept_id AND job=jobid;
16:22:36 8 BEGIN
16:22:36 9 OPEN c1(20,'CLERK');
16:22:36 10 LOOP
16:22:36 11 FETCH c1 INTO v_emp_record;
16:22:36 12 IF c1%FOUND THEN
16:22:36 13 dbms_output.put_line(v_emp_record.v_ename||'The date of employment is:'||v_emp_record.v_hiredate);
16:22:36 14 ELSE
16:22:36 15 dbms_output.put_line('The result set has been processed.');
16:22:36 16 EXIT;
16:22:36 17 END IF;
16:22:36 18 END LOOP;
16:22:36 19 CLOSE c1;
16:22:36 20 END;
16:22:37 21 /
SMITH The date of employment is 17:-DEC-80
ADAMS The date of employment is 23:-MAY-87
//The result set has been processed.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
16:22:38 SCOTT@std1>
16:22:38 SCOTT@std1> DECLARE
16:31:29 2 CURSOR c1(dept_id NUMBER,jobid VARCHAR2) IS SELECT ename,hiredate FROM emp WHERE deptno=dept_id AND job=jobid;
16:31:29 3 v_emp_record c1%ROWTYPE;
16:31:29 4 BEGIN
16:31:29 5 OPEN c1(20,'CLERK');
16:31:29 6 LOOP
16:31:29 7 FETCH c1 INTO v_emp_record;
16:31:29 8 IF c1%FOUND THEN
16:31:29 9 dbms_output.put_line(v_emp_record.ename||'The date of employment is:'||v_emp_record.hiredate);
16:31:29 10 ELSE
16:31:29 11 dbms_output.put_line('The result set has been processed.');
16:31:29 12 EXIT;
16:31:29 13 END IF;
16:31:29 14 END LOOP;
16:31:29 15 CLOSE c1;
16:31:29 16 END;
16:31:30 17 /
SMITH The date of employment is 17:-DEC-80
ADAMS The date of employment is 23:-MAY-87
//The result set has been processed.
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
16:31:31 SCOTT@std1>
Cursor FOR loops automatically execute cursor open, fetch, close and loops Example:
16:31:31 SCOTT@std1> DECLARE 16:43:57 2 CURSOR c1 IS SELECT deptno,ename,sal FROM emp; 16:43:57 3 BEGIN 16:43:57 4 FOR v1 IN c1 LOOP 16:43:57 5 dbms_output.put_line(v1.deptno||'---'||v1.ename||'---'||v1.sal); 16:43:57 6 END LOOP; 16:43:57 7 END; 16:43:58 8 / 20---SMITH---850 30---ALLEN---1600 30---WARD---1250 20---JONES---2975 30---MARTIN---1250 30---BLAKE---2850 10---CLARK---2450 20---SCOTT---3000 30---TURNER---1500 20---ADAMS---1150 30---JAMES---1000 20---FORD---3000 10---MILLER---1300 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 16:43:59 SCOTT@std1>
16:43:59 SCOTT@std1> DECLARE
16:58:03 2 CURSOR c1(dept_no NUMBER DEFAULT 10) IS SELECT deptno,dname,loc FROM dept WHERE deptno=dept_no;
16:58:03 3 BEGIN
16:58:03 4 dbms_output.put_line('When dept_no The parameter value is 30:');
16:58:03 5 FOR v1 IN c1(30) LOOP dbms_output.put_line(v1.deptno||'---'||v1.dname||'---'||v1.loc);
16:58:03 6 END LOOP;
16:58:03 7 dbms_output.put_line('When dept_no The parameter value is 10:');
16:58:03 8 FOR v1 IN c1 LOOP dbms_output.put_line(v1.deptno||'---'||v1.dname||'---'||v1.loc);
16:58:04 9 END LOOP;
16:58:04 10 END;
16:58:04 11 /
//When dept_no parameter value is 30:
30---SALES---CHICAGO
//When the dept_no parameter value is 10:
10---ACCOUNTING---NEW YORK
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
16:58:05 SCOTT@std1>
PL/SQL also allows the use of sub-queries in cursor FOR loop statements to achieve cursor functionality Example
16:58:05 SCOTT@std1> BEGIN 17:03:37 2 FOR v1 IN (SELECT dname,loc FROM dept) LOOP 17:03:37 3 dbms_output.put_line(v1.dname||'---'||v1.loc); 17:03:37 4 END LOOP; 17:03:37 5 END; 17:03:38 6 / ACCOUNTING---NEW YORK RESEARCH---DALLAS SALES---CHICAGO OPERATIONS---BOSTON PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 17:03:38 SCOTT@std1>
Implicit Cursor
Example: Delete all employees of a department in the EMPLOYEES table. If there are no employees in the department, delete the Department in the DEPARTMENT table.
17:36:32 SCOTT@std1> DECLARE 17:36:53 2 v_deptno emp.deptno%TYPE:=&p_deptno; 17:36:53 3 BEGIN 17:36:53 4 DELETE FROM emp WHERE deptno=v_deptno; 17:36:53 5 IF SQL%NOTFOUND THEN 17:36:53 6 DELETE FROM dept WHERE deptno=v_deptno; 17:36:53 7 END IF; 17:36:53 8 END; 17:36:54 9 / Enter value for p_deptno: 10 old 2: v_deptno emp.deptno%TYPE:=&p_deptno; new 2: v_deptno emp.deptno%TYPE:=10; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 17:36:58 SCOTT@std1>
Get the number of updated rows
08:48:43 SYS@std1> conn scott/tiger;
Connected.
08:48:49 SCOTT@std1> DECLARE
08:54:12 2 v_rows NUMBER;
08:54:12 3 BEGIN
08:54:12 4 UPDATE emp SET sal=30000 WHERE deptno=30;
08:54:12 5 v_rows:=SQL%ROWCOUNT;
08:54:12 6 dbms_output.put_line('Updated'||v_rows||'Wages of individual employees');
08:54:12 7 ROLLBACK;
08:54:12 8 END;
08:54:13 9 /
//Updated salaries for six employees
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
08:54:14 SCOTT@std1>
The difference between NO_DATA_FOUND and% NOTFOUND: SELECT... INTO statement triggers NO_DATA_FOUND Trigger% NOTFOUND when an explicit cursor WHERE clause is not found Trigger SQL%NOTFOUND when the WHERE clause of the UPDATE or DELETE statement is not found Use% NOTFOUND or% FOUND to determine the exit condition of the extraction loop, not NO_DATA_FOUND. Update and delete data using cursors Cursor modification and deletion operations refer to the modification or deletion of data rows specified in the table under cursor positioning. At this point, the FOR UPDATE option is required in the cursor query statement to lock all columns and partial columns of the corresponding data rows in the cursor result set in the table when the cursor is opened. In order not to change rows being processed (queried) by other users, ORACLE provides a FOR UPDATE clause to lock the selected rows. This requirement forces ORACLE to lock the rows of the cursor result set, preventing other transaction processes from updating or deleting the same rows until your transaction process commits or falls back. Grammar:
ORA-0054 : resource busy and acquire with nowait specified.
> If you use FOR UPDATE to declare cursors, you can use the WHERE CURRENT OF cursor_name clause in DELETE and UPDATE statements to modify or delete data rows in the database table corresponding to the current row of the cursor result set. Example
08:54:14 SCOTT@std1> DECLARE 09:21:38 2 v_deptno emp.deptno%TYPE:=&p_deptno; 09:21:38 3 CURSOR emp_cursor IS SELECT empno,sal FROM emp WHERE deptno=v_deptno FOR UPDATE NOWAIT; 09:21:38 4 BEGIN 09:21:38 5 FOR emp_record IN emp_cursor LOOP 09:21:38 6 IF emp_record.sal
09:36:06 SCOTT@std1> DECLARE 09:36:11 2 v_emp_record emp%ROWTYPE; 09:36:11 3 CURSOR c1 IS SELECT * FROM emp FOR UPDATE; 09:36:11 4 BEGIN 09:36:11 5 OPEN c1; 09:36:11 6 LOOP 09:36:11 7 FETCH c1 INTO v_emp_record; 09:36:12 8 EXIT WHEN c1%NOTFOUND; 09:36:12 9 IF v_emp_record.deptno=30 AND v_emp_record.job='SALESMAN' THEN 09:36:12 10 UPDATE emp SET sal=20000 WHERE CURRENT OF c1; 09:36:12 11 END IF; 09:36:12 12 END LOOP; 09:36:12 13 CLOSE c1; 09:36:12 14 END; 09:36:12 15 / PL/SQL procedure successfully completed. Elapsed: 00:00:04.39 09:36:17 SCOTT@std1>
Cursor variable Like a cursor, a cursor variable is a pointer to the current data row in a multiline query result set. But unlike cursors, cursor variables are dynamic and cursors are static. A cursor can only be connected to a specified query, that is, it can fixedly point to the memory processing area of a query, while a cursor variable can be connected to different query statements. It can point to the memory processing area of different query statements (but it can not point to multiple memory processing areas at the same time, and can only be connected to one query statement at a certain time). As long as the return types of these queries are compatible The cursor variable is a pointer, which belongs to the reference type, so the cursor variable type must be defined before the cursor variable type is declared. In PL/SQL, cursor variable types can be defined in the declaration areas of blocks, subroutines, and packages
TYPE ref_type_name IS REF CURSOR [ RETURN return_type];
Among them: ref_type_name is the newly defined cursor variable type name; return_type is the return value type of the cursor variable, which must be a record variable. When defining cursor variable type, we can use strong type definition and weak type definition. Strong type definitions must specify the return value type of cursor variables, while weak type definitions do not specify the return value type. Simply put: Strong type dynamic cursors refer to return statements, while weak type dynamic cursors refer to non-return statements (that is, weak type dynamic cursors can match any query statement, but strong type dynamic cursors can only match a specific query statement). Two steps to declare a cursor variable: Step 1: Define a REF CURSOU data type, such as: TYPE ref_cursor_type IS REF CURSOR; Step 2: Declare a cursor variable for the data type, such as: cv_ref REF_CURSOR_TYPE; Example: Create two strongly typed defined cursor variables and one weakly typed cursor variable
DECLARE TYPE deptrecord IS RECORD( deptno dept.deptno%TYPE, dname dept.dname%TYPE, loc dept.loc%TYPE); TYPE depttype1 IS REF CURSOR RETURN dept%ROWTYPE; TYPE depttype2 IS REF CURSOR RETURN deptrecord; TYPE curtype IS REF CURSOR; dept1 depttype1; dept2 depttype2; cr1 curtype;
Cursor variable operations are also open, extract, and close open
OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;
Among them: cursor_variable_name is a cursor variable, host_cursor_variable_name is a cursor variable declared in PL/SQL host environment (such as OCI: ORACLE Call Interface, Pro*c program, etc.). OPEN... The FOR statement can reopen the cursor variable before closing the current cursor variable without causing CURSOR_ALREAD_OPEN exceptional errors. When a cursor variable is newly opened, the memory processing area of the previous query is released extract
FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]...| record_variable};
Among them: cursor_variable_name and host_cursor_variable_name are cursor variable and host cursor variable_name respectively; variable and record_variable are common variable and record variable name respectively. Close
CLOSE {cursor_variable_name | :host_cursor_variable_name}
Cursor_variable_name and host_cursor_variable_name are cursor variables and host cursor variable_name respectively. If an application tries to close an open cursor variable, it will cause an INVALID_CURSOR exception error. Example:
09:36:17 SCOTT@std1> DECLARE 10:41:56 2 TYPE emp_job_rec IS RECORD( 10:41:56 3 empno emp.empno%TYPE, 10:41:56 4 ename emp.ename%TYPE, 10:41:56 5 job emp.job%TYPE 10:41:56 6 ); 10:41:57 7 TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec; 10:41:57 8 emp_refcur emp_job_refcur_type; 10:41:57 9 emp_job emp_job_rec; 10:41:57 10 BEGIN 10:41:57 11 OPEN emp_refcur FOR 10:41:57 12 SELECT empno,ename,job FROM emp ORDER BY deptno; 10:41:57 13 10:41:57 14 FETCH emp_refcur INTO emp_job; 10:41:57 15 WHILE emp_refcur%FOUND LOOP 10:41:57 16 dbms_output.put_line(emp_job.empno||':'||emp_job.ename||'is a '||emp_job.job); 10:41:57 17 FETCH emp_refcur INTO emp_job; 10:41:57 18 END LOOP; 10:41:57 19 10:41:57 20 CLOSE emp_refcur; 10:41:57 21 END; 10:41:58 22 / 7369:SMITHis a CLERK 7876:ADAMSis a CLERK 7566:JONESis a MANAGER 7788:SCOTTis a ANALYST 7902:FORDis a ANALYST 7900:JAMESis a CLERK 7844:TURNERis a SALESMAN 7654:MARTINis a SALESMAN 7521:WARDis a SALESMAN 7499:ALLENis a SALESMAN 7698:BLAKEis a MANAGER PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 10:41:58 SCOTT@std1>
10:41:58 SCOTT@std1> DECLARE
11:38:42 2 Type refcur_t IS REF CURSOR;
11:38:42 3 Refcur refcur_t;
11:38:42 4 TYPE sample_rec_type IS RECORD (
11:38:42 5 Id number,
11:38:42 6 Description VARCHAR2 (30)
11:38:42 7 );
11:38:42 8 sample sample_rec_type;
11:38:42 9 selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
11:38:42 10 BEGIN
11:38:42 11 IF selection='D' THEN
11:38:42 12 OPEN refcur FOR
11:38:42 13 SELECT deptno,dname FROM dept;
11:38:42 14 dbms_output.put_line('Department Data');
11:38:42 15 ELSE
11:38:42 16 OPEN refcur FOR
11:38:42 17 SELECT empno,ename FROM emp;
11:38:42 18 dbms_output.put_line('Employee Data');
11:38:42 19 RETURN;
11:38:42 20 END IF;
11:38:42 21
11:38:42 22 dbms_output.put_line('----------------------------');
11:38:42 23 FETCH refcur INTO sample;
11:38:42 24 WHILE refcur%FOUND LOOP
11:38:42 25 dbms_output.put_line(sample.id||':'||sample.DESCRIPTION);
11:38:42 26 FETCH refcur INTO sample;
11:38:43 27 END LOOP;
11:38:43 28 CLOSE refcur;
11:38:43 29 END;
11:38:43 30 /
Enter value for tab: D
old 9: selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
new 9: selection varchar2(1) := UPPER (SUBSTR ('D', 1, 1));
Department Data
----------------------------
20:RESEARCH
30:SALES
40:OPERATIONS
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
11:38:46 SCOTT@std1>
11:38:46 SCOTT@std1> DECLARE 12:04:38 2 TYPE emp_cursor_type IS REF CURSOR; 12:04:38 3 c1 emp_cursor_type; 12:04:38 4 v_emp_record emp%ROWTYPE; 12:04:38 5 v_reg_record dept%ROWTYPE; 12:04:38 6 BEGIN 12:04:38 7 OPEN c1 FOR SELECT * FROM emp WHERE deptno=20; 12:04:38 8 LOOP 12:04:38 9 FETCH c1 INTO v_emp_record; 12:04:38 10 EXIT WHEN c1%NOTFOUND; 12:04:38 11 dbms_output.put_line(v_emp_record.ename||'Date of employment:'||v_emp_record.hiredate); 12:04:38 12 END LOOP; 12:04:38 13 OPEN c1 FOR SELECT * FROM dept; 12:04:38 14 LOOP 12:04:38 15 FETCH c1 INTO v_reg_record; 12:04:38 16 EXIT WHEN c1%NOTFOUND; 12:04:38 17 dbms_output.put_line(v_reg_record.deptno||': '||v_reg_record.dname); 12:04:38 18 END LOOP; 12:04:38 19 CLOSE c1; 12:04:38 20 END; 12:04:38 21 / SMITH Date of employment: 17-DEC-80 JONES Date of employment: 02-APR-81 SCOTT Date of employment: 19-APR-87 ADAMS Date of employment: 23-MAY-87 FORD Date of employment: 03-DEC-81 20: RESEARCH 30: SALES 40: OPERATIONS PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 12:04:39 SCOTT@std1>