Explicit Cursors
This is one of 5 important topics.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
declare cursor c_employee is select employee_id, first_name, last_name from employees order by employee_id; begin for r_employee in c_employee loop dbms_output.put_line(r_employee.employee_id||' '||r_employee.first_name||''||r_employee.last_name); end loop; end; 100 Steven King 101 EMRAH Kochhar 102 Lex De Haan 103 Alexander Hunold ..... |
A cursor can be declared as a variable containing more than one row. The row selected with the cursor should be declared to variable.
A row taken from a cursor needs to be assigned to a variable, here for r_employee in c_employee gets assigned to the cursor’s “employee_id, first_name, last_name” fields.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
declare cursor c_employee is select employee_id, first_name, last_name from employees order by employee_id; counter number; begin counter :=1; for r_employee in c_employee loop dbms_output.put_line(counter||' '||r_employee.employee_id||' '||r_employee.first_name||' '||r_employee.last_name); counter :=counter+1; end loop; end; 1 100 Steven King 2 101 EMRAH Kochhar 3 102 Lex De Haan |
To retrieve the 3rd record, change the counter variable to 3.
How to write a cursor that can find the number of records in the HR schema’s tables ? If possible, this would be very useful.
How many tables are there in the database ?
drop table emp purge
create table emp as select * from employees;
alter table emp modify (last_name null) ; – this changes the not null constraint to allow null entries in the last_name field
update emp set last_name=null; – sets all the last_name fields in all the emp records to null.
Example:
The values in the emp’s last_name field are empty, the same field in employees contains data. Is it possible to copy the values from employee’s last_name to the emp table ?
As the employee_id is unique, it can be used as an id to match.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
declare cursor c_emp is select employee_id, last_name from employees; begin for r_emp in c_emp loop update emp set last_name = r_emp.last_name where employee_id = r_emp.employee_id; commit; end loop; end; |
— If there are 10 million records to be changed, the commit should be placed in a loop. Otherwise, all the changes can be undone and this can increase the size of the database from 4Gb to 20-30Gb. If that amount of storage isn’t available, the database will stop.
A commit after every step would be also be inefficient. It is necessary to take action according to the number of record rows in the table. The cursor should be taken to the Employees table.
As we need someones data, the name table is added to the emp table:
alter table emp drop column name ;
alter table emp add (name varchar2 (100) );
This will be written in a first_name & last_name format.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
declare cursor c_emp is select employee_id, first_name, last_name from emp; begin for r_emp in c_emp loop update emp set name = r_emp.first_name ||' '|| r_emp.last_name where employee_id = r_emp.employee_id; commit; end loop; end; -- can be achieved in the following way: update emp set name = first_name||' ' || last_name select e.first_name, e.last_name, d.department_name department_name from emp e, departments d where e.department_id = d.department_id ; declare cursor c_DEPARTMENT is select department_id, DEPARTMENT_NAME from departments;cursor c_member (p_depno number) is select first_name, last_name from emp where department_id = p_depno;begin for r_department in c_department loop dbms_output.put_line(r_department.DEPARTMENT_NAME); for r_member in c_member(r_department.department_id) loop dbms_output.put_line(' '||r_member.first_name||' '|| r_member.last_name); end loop; end loop;end; |
The 2nd cursor gets the parameters. The first cursor retrieves members that have the specified department_id.
|
1 2 3 4 5 6 7 8 9 10 |
Administration Jennifer Whalen Marketing Michael Hartstein Pat Fay Purchasing Den Raphaely Alexander Khoo Shelli Baida .... |
Redo can’t be used in situations where a truncate : rollback isn’t achievable.
select count(*) from emp ;
|
1 2 3 |
begin truncate table emp ; end; |
This can’t work directly (DDL,create drop,alter truncate) and requires dynamic sql.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
begin execute immediate 'truncate table emp' ; end; declare table_name varchar2(30); sayi number;begin table_name := 'emp'; select count (*) into sayi from table_name; dbms_output.put_line(sayi); end; |
This won’t work because table and column names can’t be sent as parameters, however values can.
Example: select first_name, last_name from emp where employee_id = 45 ; <== Here only 45 can be sent as a parameter. Send parameters everywhere using execute immediate.
|
1 |
declare table_name varchar2(30); sayi number;begin table_name := 'emp';execute immediate ' select count(*) from '|| table_name into sayi; dbms_output.put_line(sayi); end; declare table_name varchar2(30); sayi number; kelime varchar2(10) := 'from';begin table_name := 'emp';execute immediate ' select count(*) '||kelime||' '|| table_name into sayi; dbms_output.put_line(sayi); end; |
The previous method would achieve the same results. Finding the database’s table record numbers Requirements: Table names and record numbers
|
1 |
declare cursor c_tablo is select table_name from user_tables; kayit_sayisi number;begin for r_tablo in c_tablo loop execute immediate 'select count(*) from ' ||r_tablo.table_name into kayit_sayisi; dbms_output.put_line(r_tablo.table_name|| '-' || kayit_sayisi); end loop; end; |
select table_name , num_rows from user_tables; The same can also be done in the following: analyze table emp compute statistics; This contains the table’s statistics. This is normally contained in the dynamic views and updated using the previous command. The DBMS’ accuracy in making decisions depends on how recently the information has been updated. For the DBMS to have the latest record count it needs to calculate the statistics. This can be done either by the DBMS or the user. This is done automatically every hour in 10g, 9i didn’t do this at all. An example of the effect of having a discrepancy in not calculating the latest statistics vs. having them is: When the DBMS still acts based on information of a record count of 100 records and performs a full scan, whereas the latest number of records is actually 1 million; if the DBMS knows that the latest record count is 1 million, it would use the index which would take 30 seconds instead of 10 minutes. There are 2 ways to analyze all the tables: 1- analyze table emp compute statistics; analyze table employees compute statistics; analyze table dept compute statistics; … Using this method a script file, that has all the tables saved, is run select ‘analyze table ‘ || table_name|| ‘ compute statistics;’ from user_tables; When run, the previous line carries out a test and saves the result as a delimited txt. To determine whether the statistics are current (have been updated), use the following line select table_name , num_rows from user_tables; To test, use select count(*) The second way to analyse all the tables: Using execute immediate (with dynamic sql), automatically collect the statistics of connected schemas.
|
1 |
declare cursor c_tablo is select table_name from tabs; begin for r_tablo in c_tablo loop execute immediate ' analyze table ' || r_tablo.table_name || ' compute statistics ' ; end loop; end; |
One cursor retrieve num_rows, and another cursor retrieves count(*) while running a side by side comparison using pl/sql. WHILE select trunc(dbms_random.value(1,50)) from dual;
|
1 2 3 |
declare sayi1 number; sayi2 number; sayi3 number; sayi4 number; sayi5 number; sayi6 number; begin sayi1 := trunc(dbms_random.value(1,50)); sayi2 := trunc(dbms_random.value(1,50)); while (sayi2 = sayi1) loop sayi2 := trunc(dbms_random.value(1,50)); -- wont exit the loop until sayi2 is unequal to sayi1 end loop ; sayi3 := trunc(dbms_random.value(1,50)); while (sayi3 = sayi1 OR sayi3 = sayi1) loop sayi3 := trunc(dbms_random.value(1,50)); end loop; dbms_output.put_line('Sayı1: ' || sayi1) ; dbms_output.put_line('Sayı2: ' || sayi2) ; dbms_output.put_line('Sayı3: ' || sayi3) ; end loop;end; declare cursor c_tablo is select table_name from tabs; kayit_sayisi number ; begin for r_tablo in c_tablo loop execute immediate ' select count(*) from ' || r_tablo.table_name into kayit_sayisi; dbms_output.put_line(rpad(r_tablo.table_name,25, ' ' ) || ' -> '||kayit_sayisi); end loop; end; |
he RPAD (Right Padding) function can structure the output into a more tabular layout.
A space ‘ ‘ or symbols such as * can be inserted after the 25th character.
Use the following codeblock to output (toad can cause the output to appear disarranged)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
REGIONS -> 4 LOCATIONS -> 23 JOBS -> 19 MLOG$_EMPLOYEES -> 0 declare cursor c_member is select e.first_name, e.last_name, d.department_name dept from employees e, departments d where e.department_id = d.department_id; r_member c_member%rowtype ; begin open c_member; loop fetch c_member into r_member; exit when c_member%notfound; dbms_output.put_line(r_member.first_name || ' ' || r_member.last_name || ' ' || r_member.dept ) ;end loop;close c_member; end; |
Using ‘for’ makes this task a lot easier. The naming convention for cursors is to start them with a c_.
If it isnt possible to count the amount of records entered with ‘insert into’ using:
|
1 2 3 |
dbms_output.put_line(SQL%COUNT) use dbms_output.put_line(SQL%ROWCOUNT) instead. |
UPDATEABLE CURSORS
During the insert update process, the user who first updates is the one who locks the row to maintain read consistency. This lock remains in effect until a commit and also prevents a rollback.
Once the 1st cursor has been committed the 2nd cursor automatically gets updated.
A Deadlock can be considered a double lock, i.e. different to a lock.
Example:
If while in one session User A tries to update a record that User B has locked and if, at the same time, in another session User B tries to update a record that User A has locked, the result would be a deadlock.
The DBMS outputs a deadlock error with the first session and waits for the first session to commit/rollback.
|
1 2 3 |
select * from employees for update of salary; |
This way only the salary column gets locked. Use this when you dont want to update before running a cursor.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
select * from employees for update of salary; declare cursor employees is select * from employee for update of salary, last_name ; Salary and last_name columns remain locked until after the cursor has shutdown. update table_name set salary = 5000 where current of c_member The above record has had its salary field updated to 5000. This can also be done with: update table_name set salary = 5000 where employee_id = r_member.employee_id |
t’s better to use a ‘for’ loop when including Cursor Parameters. This prevents endless loops and open cursors. Open cursors can negatively impact the DBMS’ performance, has the same impact as the cursor being run in the system.
RAM usage is proportional to the amount of cursors that remain open.
|
1 2 3 4 5 6 7 8 9 10 |
begin for r_member in (select first_name fn, last_name ln from employees) loop dbms_output.put_line(r_member.fn ||' ' || r_member.ln); end loop; end; Ellen Abel Sundar Ande Mozhe Atkinson Nested blocks |
This section shows that it isnt necessary to declare the cursor.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
declare salary number ; begin select salary -- Can the "dbms_output.put_line('complete');" line be run even if this select statement results in an error ? into salary -- This isnt possible in this structure because as soon as there is an error, it will trigger the exception and prevent anything else from running. from employees -- A second level block can be constructed internally, hence the term nested. where employee_id>100; dbms_output.put_line('complete'); delete from employees where employee_id = -198;exception when others then dbms_output.put_line(SQLERRM);end; declare salary number ; begin declare begin select salary into maas from employees where employee_id>100; exception when others then dbms_output.put_line('an error has been encountered'); end; dbms_output.put_line('complete'); delete from employees where employee_id = -198;exception when others then dbms_output.put_line(SQLERRM);end; begin func1() proc1() func2() func3() |
If an error occurs after running a proc and the exception catches the error, other functions and procedure won’t be affected.
Using RMAN for...
12 March 2019