Procedure
Procedures can be used for Insert, Update and Delete operations. As their structures aren’t suitable, they aren’t used that often for Select operations.
It is possible to specify the action to apply such as a drop or purge, on a user basis, when they want to delete a table by saving it into a procedure and enforcing it.
This is decided upon by the database administrator.
1 2 3 4 5 6 7 8 |
create or replace procedure drop_table (p_tablename varchar2) is begin execute immediate 'drop table ' || p_tablename || ' purge' ; -- has to be coded as ' purge'. Its necessary to have a single space between the ' and purge. dbms_output.put_line(p_tablename || ' table has been dropped. '); end; begin drop_table ('employees_2') ; end; |
Can alternatively be run as:
1 2 3 4 5 6 7 8 9 10 |
exec drop_table('employees_3'); create or replace procedure add_dept (p_deptid number, p_deptname varchar2) is begin insert into dept(department_id, department_name) -- As this is a DDL command, it can be written directly without an exec values (p_deptid, p_deptname); commit; end; exec add_dept(800, 'eighthundred') select upper('ibrahimİBRAHİM') from dual; -----> IBRAHIMİBRAHİM select lower('IĞDIRığdırİBRAHİM') from dual; ---> iğdirığdırıbrahım |
This is a bug in Oracle.
1 2 |
select 'ismail' from dual; select replace('ismail','i','İ') from dual; |
A workaround is to replace the lowercase i’s
1 2 3 4 5 6 7 |
select upper('ibrahimİBRAHİM') from dual; select lower('IĞDIRığdırİBRAHİM') from dual; select 'ismail' from dual; select replace('ismail','i','İ') from dual; from personel where upper(lower(ad))=upper(lower('omer')) where increase(ad)=increase('omer') |
The following calculates the statistics of the tables and the indexes of the users who run it.
1 2 3 4 5 6 7 8 9 10 11 |
create or replace procedure calculate_table_statistics is cursor c_table is select table_name from user_tables; begin for r_table in c_table loop execute immediate 'analyze table ' || r_table.table_name || 'compute statistics'; dbms_output.put_line(r_table.table_name || ' has been analyzed.'); end loop; end; |
The code can also be run with: exec calculate_table_statistics
Alternatively, the procedure can be scheduled in the toad application’s jobs tab. This is shown how in a command line environment with the following code:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE jobno NUMBER; BEGIN DBMS_JOB.submit (job => jobno, what => 'HR.CALCULATE_TABLE_STATISTICS;', next_date => trunc(sysdate)+23/24, interval => 'SYSDATE + 1', no_parse => TRUE ); DBMS_OUTPUT.put_line ('Created Job - the job number is:' || TO_CHAR (jobno)); COMMIT; END; / |
Note: If a table hs been created using “create table xxxxx as select * from yyy”, then its constraints cant be migrated.
The indexes get the table record count from user_tables.
Insert Stress
1 2 3 4 5 6 7 8 |
begin for i in 1..5 loop insert into emp2 select * from emp2 ; commit; end loop ; end; |
This procedure is to compare the row counts between the tables in a schema.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Create or replace procedure compare_row_count is cursor c_table is select table_name, num_rows from user_tables; oracle_count number; actual_count number; begin dbms_output.put_line('ACTUAL ORACLE'); dbms_output.put_line('------ ------'); for r_table in c_table loop oracle_count := r_table.num_rows; execute immediate 'select count(*) from ' ||r_table.table_name into actual_count; if (oracle_count = actual_count) then null; -- dbms_output.put_line(r_table.table_name||'-'||actual_count|| -- '*****'||r_table.table_name||'-'||oracle_count); else dbms_output.put_line('Attention : '|| r_table.table_name||'-'||actual_count|| '********'||r_table.table_name||'-'||oracle_count); end if ; end loop; end; |
Note: ‘is’ and ‘as’ are both supported. Writing ‘end procedure’ is also optional.
in & out
1 2 3 4 5 6 7 8 |
create or replace procedure get_emp_name_surname (p_employee_id number, name out varchar2, surname out varchar2, salary out number) is -- (p_employee_id in number, ) not having 'in' is interpreted the same as having 'in'. i.e. optional begin select first_name, last_name, salary into name, surname, salary from employees where employee_id = p_employee_id ; end ; |
The syntax below can also be run with SQLPlus
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
variable name varchar2(20) variable surname varchar2(20) -- both of these are in SQLPlus and probably won't work in toad exec easg(206, :name, :surname); -- easg retrieves both name and surname print name print surname create synonym easg for get_emp_name_surname declare name varchar2(20); surname varchar2(20); salary number; begin easg(206, name, surname, salary); -- Colons are used to retrieve variables declared in SQLPlus which is why they havent been used here. dbms_output.put_line(name||' '||surname||' '||salary); end; |
The advantage of using a Procedure over a Function: A function can only retrieve a single value, e.g. only name, only surname, single token name_surname but not two separate values.
A function can’t, for example, retrieve a name and assign it to a variable. However this is possible with a procedure.
Functions can either be a varchar2 or a number type. Procedures are able to simultaneously output multiple variables and types.
Both functions and procedures are considered to be program units. Only execute privileges are granted to these program units.
1 |
grant execute on raise_salary to student01; |
Errors that could be caused by creating functions and procedures.
1 2 3 |
show errors -- to view errors select * from user_errors -- an alternative way of viewing errors show errors procedure raise_salary -- if there are many errors |
Compile/Recompile
1 2 |
alter procedure raise_salary compile ; alter function raise_salary compile ; |
Note: With 10G, the dbms attempts to recompile called program routines that have become invalid.
1 2 3 4 5 6 7 8 |
select object_name, status from user_objects drop procedure raise_salary ; drop function salary_valid ; drop type name ; select * from from user_objects where object_type='PROCEDURE' |
This code can be viewed in toad. If toad hasnt been installed, it can be viewed using user_source view/table.
Flashback Recovery
13 March 2019