Use PL/SQL monitoring procedure to perform time

Create a table

CREATE TABLE Process_timing_log
(
Process_name VARCHAR2 (+),
Execution_date DATE,
records_processed number,
ELAPSED_TIME_SEC number
)

Creative procedure

CREATE OR REPLACE PROCEDURE scott.update_salary
As
CURSOR Cur_employee
Is
SELECT empno, Sal, ROWID from Employee_test;

Lv_new_salary_num number;
Lv_count_num Pls_integer: = 0;
Lv_start_time_num Pls_integer;
Lv_total_time_num number;
BEGIN
Lv_start_time_num: = dbms_utility. Get_time;

For Cur_employee_rec in Cur_employee
LOOP
Lv_count_num: = Lv_count_num + 1;
Lv_new_salary_num: = Cur_employee_rec.sal;

UPDATE Employee_test
SET sal= Lv_new_salary_num
WHERE ROWID = Cur_employee_rec. ROWID;
END LOOP;

Lv_total_time_num: = (dbms_utility. get_time-lv_start_time_num)/100;

INSERT into Process_timing_log (Process_name,
Execution_date,
Records_processed,
ELAPSED_TIME_SEC)
VALUES (' Update_salary ',
Sysdate,
Lv_count_num,
Lv_total_time_num);

COMMIT;
END update_salary;
/
The results of the exercise

Process_name,execution_date,records_processed,elapsed_time_sec
UPDATE_SALARY,2016/8/1 pm 02:57:08,4257554,532.28

Use PL/SQL monitoring procedure to perform time