Whoever Resembles a People is One of Them. He is not one of us who resembles other than us,
nor who resembles the Jews nor the Christians.(jami-at-tirmidhi-vol-5-ahadith-2695)
O you who believe (who wish to reach ALLAH C.C. before death)! Do not take my and
your enemies for friends!
And although they have denied what has come to you
from the Truth, you still love them (treating them as your friends).
If you go forth to struggle hard in My Way, seeking My Pleasure, (then why) you manifest
love to them and share with them your secrets.
And I know what you conceal and what you manifest. And whoever of you does that, then he
indeed has gone astray from the right Way. 60/Al-Mumtahanah-1
They are driving out the Messenger and yourselves from your land because you
believe in ALLAH C.C., your Lord.
Recite (read and explain to others) that which has been revealed to you of the Book
and keep up the Prayer.
Surely the Prayer keeps away from Al-Fahsh (to do what ALLAH C.C. prohibits) and Al-Munkar
(to deny what ALLAH C.C. commands).
Whoever obeys me, obeys ALLAH C.C., and whoever disobeys me, disobeys ALLAH C.C.
Whoever obeys the ruler, obeys me, and whoever disobeys the ruler, disobeys me. " (Sahih)

set serveroutput on
declare
dept_name varchar2(30);
begin
select department_name
into dept_name
from departments
where department_id=120 ;
dbms_output.put_line('Department Name:'|| department_name);
end ;

The output:

Department Name: Treasury

declare: Variables are declare between this and the [b]begin[/b] keywords.

into: The value fetched using SQL is assigned to the variable.

dbms_output.put_line: Displays the variable's value on screen and moves on to the next line.

Entering dbms_output.put, without a _line suffix may not produce an output.

This isn't a procedure or a function but an anonymous block, if script had been unsaved it would have lost.

If it was a function it would have been saved in the database.

The dbms_output.put_line can be considered a form of debugging to check if the code is working properly. By continuously printing out the variable's values to the screen, the code can be verified for correctness.

Example: To retrieve data for 5 units of a product, a Java program would have to perform 5 round trips to the database. With PL/SQL this can be achieved in only one go.

With some projects, where data isn't being retrieved from a database quickly enough, code may need to be transferred from a Java program to PL/SQL.

In terms of performance, stored procedures are the fastest blocks of code in databases.

SGA - library cache (System Global Area) stores compiled SQL & PL/SQL code that has already been run, if the DBMS needs to run this code again it is fetched from the library cache without needing to be parsed again.

Parsing is a demanding operation on the database and takes time (as it checks whether there is authorization to use a database, if the needed columns are present within the table, etc.).

Procedures can be called using any programming language or by SQL. They can be run from any database that can be queried.

PL/SQL Program Block

The structure of a PL/SQL Program Block

declare

begin (required)

program logic (required)

exception

end (required)

{code}
set serveroutput on -- no need to use toad (however toad is necessary if wanting to: execute as script)
begin
dbms_output.put_line('Hello World');
end ;
-- a single line of comment
/*
This is how paragraphs of comments should be written
*/

All commands should end with a semicolon ;.

Example: The following code outputs "Hello" on to the screen

SQL> begin
dbms_output.put_line('Hello');
end;
/
Hello
PL/SQL procedure successfully completed

Example: Outputs Hello(1 to 5).

SQL> beginfor i in 1..5
loop
dbms_output.put_line('Merhaba'||i);
end loop;
end;
/ -- There may be no need for the forward slash character here in toad, but it's necessary in SQLPlus

Output of the previous code block

Hello1
Hello2
Hello3
Hello4
Hello5
PL/SQL procedure successfully completed

There is no need to use a declare here. The loop carries out an insert from 1 to 1000.

(NOTE: 'loop' and 'end loop' could have been written here instead of begin and end.)

As the insert and commit statements are enclosed in the loop, there is a commit operation following every insert operation. There isn't much need for a rollback as if there hasn't been an insert the statement is drawn back anyway. If the commit operation is done outside of a loop then it would be more useful to have a rollback.

This example isn't one that really shows exception handling.

The PL/SQL assignment operator is :=

declare
section_no number;
section_name varchar2(50);
begin
section_no :=45;
select department_name
into section_name
from departments
where department_id=section_name;
dbms_output.put_line('Section Name:'||section_name);
end;
The following error is due to there not being a department_id 45
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8

The code has been changed to catch errors messages as shown in the following lines.

declare
section_no number;
section_name varchar2(50);
begin
section_no :=45;
select department_name
into section_name
from departments
where department_id=section_no;
dbms_output.put_line('Section Name: '||section_name);
exception
when no_data_found then
dbms_output.put_line('Unable to find Section Number: '||section_no);
end;

 

Output

Unable to find Section Number: 45
PL/SQL procedure successfully completed.

DDL commands can't be written in SQL directly but DML commands such as insert, update and delete can.

Dynamic SQL commands are required to run DDL commands such as alter, delete, drop and truncate.

Example: "execute immediate 'drop table members"

begin
drop table members; -- This command shouldn't be used here.
end;

Dynamic SQL is required to run a command as shown on line 2. The same is necessary to dynamically retrieve a table name. However dynamic SQL isn't required to retrieve values from a table.

This is a general requirement for outputting the record counts of tables, e.g. for tables containing more than a million records.

A forward slash '/' re-runs the previous statement. run does the same and in addition it outputs the statement on to the screen.

The dot operator '.' keeps the syntax in the buffer and places it in the command line, as previously mentioned, it requires a forward slash to run it.

employee.salary%TYPE ---> as it is structured in the employee table's salary column

As an exception handler for catching errors, "WHEN others THEN" should be written last. This is because general errors that haven't been assigned, or specified, get processed at this point.

set serveroutput on
declare
registryno employees.employee_id%type;
name employees.first_name%type;
surname employees.last_name%type;
begin
registryno := 199;
select first_name, last_name
into name, surname
from employees
where employee_id = registryno;
dbms_output.put_line(name||' '||surname);
end;
Douglas Grant
PL/SQL procedure successfully completed.

If select has 3 values 'into' should also have 3 values, 'into' statements should only be a single line. For example if there are 2 people to be entered, then an 'into' shouldn't used.

Declare
EmpLname VARCHAR2(20)
EmpSalary NUMBER(6)
EmpBDate DATE

Unlike SQL, PL/SQL also allows the assignment of Boolean variables, where variable naming conventions apply.

*max 30 characters
*must begin with a letter
*can contain letters, numbers and symbols.
*the symbols that are allowed: $, #, _

These naming conventions are applicable to tables, columns and variables in PL/SQL.
Data Types

binary_integer: a datatype used to store signed (both positive and negative numbers) the same as number datatype
PLS_integer: a newly entered PLS_integer is processed faster that binary_integer. Also faster than number in PL/SQL
binary_float, binary_double: in terms of accuracy they come after decimal
natural: natural numbers 0-2G (+)
positive: 1-2G
data type synonyms: synonyms used to run code originating from other databases
synonyms used for numbers: dec, decimal, double, precision, float, integer, int, numeric, real, smalllint
char character, string
varchar2 varchar
varchar written in mysql scripts are changed to varchar2
Declare
NewEmpSalary Number(6) :=25000;
NewEmpSal Number(6) DEFAULT 25000;
"default" is the same as ":=". Generally ":=" gets used.
Declare
EndTheLoop := FALSE;

BEGIN

A boolean can be assigned a value of true or false

In toad: view ---> toad options

Select PL/SQL from languages

Click on code templates

Enter the preferred abbreviations in the opened window (i.e. declare begin end > dbe)

These can be entered while coding using ctrl+space

declare
count number;
begin
count :=67;
if (count < 100) then
dbms_output.put_line('small count');
else
dbms_output.put_line('large count');
end if;
end;
declare
count number;
begin
count :=67;
if (count < 100) then
dbms_output.put_line('small count');
dbms_output.put_line('still too small');
else
dbms_output.put_line('large count');
end if;
end;
declare
is_male boolean;
begin
is_male :=false;
if (is_male) then
dbms_output.put_line('male');
else
dbms_output.put_line('female');
end if ;
end;

-- This outputs female

-- if the false is changed to true then the output is male

Note: Booleans can't be used in columns.

select dbms_random.value(1,5) from dual;

Remove accidental numbers between 0 and 49.

iAs with programming languages, if a constant is declared in the declare section, it's assigned value cannot later be changed

declare
date_ date ;
name varchar2(20);
begin
date_ := sysdate;
name := upper('mehmet');
dbms_output.put_line('date:'||date_);
dbms_output.put_line('name:'|| name);end;
declare
date_ date ;
name varchar2(20);
begin
date_ := sysdate;
name := user;
dbms_output.put_line('date:'||date_);
dbms_output.put_line('name:'|| name);end;

This example shows that functions can be used without statements such as 'select user from dual:'.
Comparison Operators

declare
count1 number;
count2 number;
begin
count1 := 14;
count2 := 30;
IF (count1>10 and count2>10) THEN
dbms_output.put_line('Both are greater than 10');
END IF;
end;

-- counts less than 10 will not get output onto the screen.

declare
count1 number;
count2 number;
begin
count1 := 4;
count2 := 30;
IF (count1>10 OR count2>10) THEN
dbms_output.put_line('One of the counts are greater than 10');
END IF;
end;
declare
count number :=7;
begin
if(count=1) then
dbms_output.put_line('count 1');
elsif (count=2) then
dbms_output.put_line('count 2');
elsif (count=3) then
dbms_output.put_line('count 3');
else
dbms_output.put_line('The count is greater than 3');
end if;
end;
declare
max_salary number;
begin
select max(salary)
into max_salary
from hr.employees;
dbms_output.put_line('The highest salary is '||max_salary);
end;
declare
max_salary number;
min_salary number;
avg_salary number;
begin
select max(salary)
into max_salary
from employees;select min(salary)
into min_salary
from employees;
select round(avg(salary),2)
into avg_salary
from employees;
dbms_output.put_line('The highest salary: '||max_salary);
dbms_output.put_line('The lowest salary: '||min_salary);
dbms_output.put_line('The average mean salary: '||avg_salary);
end;

This can be achieved with a single query:

create table copy_dept as
select * from departmenst
where 1=2 ;

-- The copy of departments remains empty if the rows aren't retrieved this way.

-- A condition of 1=1 being enforced results in rows being created together.

begin
insert into copy_dept (department_id, department_name)
values (10, 'test') ;
commit;
end;
begin
insert into copy_dept (department_id, department_name)
values (10, 'test') ;
commit;
end;

-- commit can also be placed after end.

Should the commit be placed in or outside of the procedure ? If placed within the procedure, the user that runs the procedure would also have performed a commit.
Example:

A money transfer transaction scenario of where 50TL is sent from account no. 24 to account no. 30.

begin
update accounts
set amount = amount-50
where accountNo=24;
update accounts
set amount = amount+50
where accountNo=30;
commit;
exception
when others then
rollback;
end;

Example:

There was an error while the money was being withdrawn from account no. 20 and deposited into account no. 30.

If there was an upper bound constraint of 100TL and the amount to be transferred is 110TL, in such a scenario the amount won't be transferred to account no. 30 and there would be a rollback.

When the procedure works, both transactions (of withdraw and deposit) should work together, if it doesn't then both transactions should be rolled back.

In the example scenario of transferring 50TL from account no. 24 to account no. 30; if there is a commit after the first update, i.e. withdrawal, and due to an arbitrary constraint the money can't be transferred to the receiving account the money would have been lost.

Running an update frequently is demanding on the dbms' resources. Using the procedure enter an account number as well as the balance increase amount; if entering -50 for example, then 50TL should be deducted. An entry of +50 would deposit 50TL into the account. A commit done immediately following the amount deposit would result in an inconsistency, for this scenario.

Changes would still appear even if there isn't a commit in the session. They wouldn't appear in the other sessions.

"novalidate" in the commit insertions results in only validating the new data and not the old data. There is no alteration of data that doesn't meet the conditions of the constraints.

The default setting is "enablevalidate".

1 alter table employee
2 add EmpID NUMBER(4) CONTSTRAINT employeePK PRIMARY KEY NOVALIDATE ;

create sequence EmpIDsequence:

As this is the Primary Key in the table it can't insert the same numbers, instead it inserts the values from the sequence.

DECLARE
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO employee (EmpID, lname, fname, ano, salary)
VALUES (EmpIDsequence.NEXTVAL, 'Doe', 'John', 1, 30000 + i) ;
END LOOP ;
END ;
/

GO TO

This isn't used often in corporate projects and has been removed from most programming languages.

Label

Can be considered a type of bookmark for executable statements within the code which the GOTO statement transfers control to.

LOOP

Using a 'for' statement isnt compulsory to run loops, 'loop' keywords are independent of 'for' statements.

DECLARE
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO employee (EmpID, lname, fname, ano, salary)
VALUES (EmpIDsequence.NEXTVAL, 'Doe', 'John', 1, 30000 + i) ;
END LOOP ;
END ;
/
declare
count number;
begin
count := 1;
loop
dbms_output.put_line(count);end loop;
end ;

Printing from 1 to 10 using Loop

declare
count number;
begin
count := 1;
loop
dbms_output.put_line(count);end loop;
end ;

If the count was omitted, the loop would have entered an infinite loop. Placing "exit when count> 100;" at the beginning is good practice.

It's easier to achieve results, as shown in the following example using a 'for'.

declare
count number;
begin
for i in 1..10
loop
dbmps_output.put_line(count);
end loop
end ;
begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop ;
end ;

Use a 'for' loop if the final count is known.

BEGIN
FOR i in reverse 1..10 LOOP -- runs from 10 to 1

WHILE

Used whenever the final count is unknown. Checks the condition at the beginning of the loop.

declare
count number;
begin
count := 1;
while (count<=10)
loop
dbms_output.put_line(count);
count := count+1;
end loop ;
end ;

Note: This example didn't work as it was supposed to.

declare
total_students number ;
student_grade number;
total_grade number :=0 ;
total_averages number :=0 ;
begin
total_students :=0;
student_grade := &ogr_not ;
while (student_grade>0)
loop
total_grade := total_grade + student_grade ;
dbms_output.put_line('Grade Total: ' || total_grade) ;
total_students := total_students + 1 ;
dbms_output.put_line('Student no: ' || total_students) ;
student_grade := &ogr_ not
dbms_output.put_line('Not: ' || student_grade) ;
end loop;
ort_not := total_grade / total_students ;
dbms_output.put_line('Ortalama: ' || total_averages) ;
end;

CASE

The difference between decode and case: Case can be used by itself, however Decode can't be.

select decode (........
into
by using select decode a gender can be entered
with case
:= decode (gender. 'M', 'Male', 'F', 'Female', 'No selection has been entered');
declare
birth_month CHAR(3)
message VARCHAR2(40);
BEGIN
x_ssn := &prompt_for_ssn;
select bdate
into x bdate
from employee
where ssn = x_ssn;
birth_month := to_char (x_bdate, 'mon');
case birth_month
when 'JAN' THEN message := 'start of the year'
when 'FEB' THEN message := 'short month'
ELSE message := 'No comment'
end case;
DBMS_OUTPUT.PUT_LINE (message);
END;
/

Exception

As good practice, errors starting with ora- shouldn't be displayed to users, who may be unfamiliar with the error codes.

These errors should be caught with an exception to display them as messages such as "Unable to find record", etc.

declare
dept_name varchar2 (50);
begin
select department_name
into dept_name
from departments
where department_id=&team;
dbms_output.put_line('Department: '|| dept_name );
end;

If team doesn't contain any data, by default the error message would be ORA-0143 no data found. By having exception handling code in every procedure, a less technical and easier to understand error message can be displayed to users.
'others' is an Oracle operator that catches all error messages and can be used to output a generic error message.

declare
dept_name varchar2 (50);
begin
select department_name
into dept_name
from departments
where department_id=&takim;
dbms_output.put_line('Departman: '|| dept_name );
exception
when others then
dbms_output.put_line('There has been an error.');
end;
declare
dept_name varchar2 (50);
begin
select department_name
into dept_name
from departments
where department_id=&team;
dbms_output.put_line('Department: '|| dept_name );
exception
when no_data_found then
dbms_output.put_line('Unable to find the entered record number.');end;
declare
dept_name varchar2 (50);
begin
select department_name
into dept_name
from departments
where department_id=&department;
dbms_output.put_line('Department: '|| dept_name );
exception
when no_data_found then
dbms_output.put_line('Unable to find record no. '||&department);end;
Error message: "Unable to find record no. 400"

An example of a Divide by Zero error in Oracle:

declare
dept_name varchar2 (50);
count number;
begin
count :=7/0;
select department_name
into dept_name
from departments
where department_id=&department;
dbms_output.put_line('Department: '|| dept_name );
exception
when no_data_found then
dbms_output.put_line('Unable to find record no. '||&department);
when others then
dbms_output.put_line('There has been an error.');
end;

The previous code would output: "There has been an error."

declare
dept_name varchar2 (50);
count number;
begin
count :=7/0;
select department_name
into dept_name
from departments
where department_id=&department;
dbms_output.put_line('Department: '|| dept_name );exception
when no_data_found then
dbms_output.put_line('Unable to find record no. '||&department);
when zero_divide then
dbms_output.put_line('Divide by zero error.');
--when others then
--dbms_output.put_line('There has been an error.');
end;

The error output: "Divide by zero error."

alter table emp drop primary key cascade; -- Can be done without entering a table name
alter table emp drop constraint pk_teams_tid cascade; -- If it is another table's foreign key then it would drop the table without outputting an error

A test scenario where the same Dept (department_id) is entered consecutively.

As a result the dbms is unable to find 2 different records with the same department_id:

declare
dept_name varchar2 (50);
sayi number;
begin
-- count :=7/0;
select department_name
into dept_name
from dept
where department_id=&department;
dbms_output.put_line('Department: '|| dept_name );
exception
when no_data_found then
dbms_output.put_line('Unable to find record no. '||&department);
when zero_divide then
dbms_output.put_line('Divide by zero error.');
when others then
dbms_output.put_line('There has been an error.');
end;
"There has been an error."
exception when others then null;

The previous line can be understood as: don't display messages, its better to use an exception. When there is an actual error it overrides others.

"There has been an error."
declare
dept_name varchar2 (50);
count number;
begin
--count :=7/0;
select department_name
into dept_name
from dept
where department_id=&department;
dbms_output.put_line('Department: '|| dept_name );
exception
when no_data_found then
dbms_output.put_line('Unable to find record no. '||&department);
when zero_divide then
dbms_output.put_line('Divide by zero error.');
--when others then
-- dbms_output.put_line('There has been an error.');
end;
declare
dept_name varchar2 (50);
count number;
begin
--count :=7/0;
select department_name
into dept_name
from dept
where department_id=&department;
dbms_output.put_line('Department: '|| dept_name );
exception
when no_data_found then
dbms_output.put_line('Unable to find record no. '||&department);
when zero_divide then
dbms_output.put_line('Divide by zero error.');
--when too_many_rows then
-- dbms_output.put_line('There have been more results than expected');
when others then
dbms_output.put_line('There has been an error.');
end;
Error output "There have been more results than expected"
declare
dept_name varchar2 (50);
count number;
begin
--count :=7/0;
select department_name
into dept_name
from dept
where department_id=&department;
dbms_output.put_line('Department: '|| dept_name );
exception
when no_data_found then
dbms_output.put_line('Unable to find record no. '||&department);
when zero_divide then
dbms_output.put_line('Divide by zero error.');
--when too_many_rows then
--dbms_output.put_line('There have been more results than expected');when others then
dbms_output.put_line('There has been an error.'||SQLERRM);
end;
Error message: "ORA-01422: exact fetch returns more than requested number of rows"

SQLERRM is an important DBMS feature as it can at least output the type of error that has been encountered.

declare
dept_name varchar2 (50);
count number;
begin
--count :=7/0;select department_name
into dept_name
from dept
where department_id=&departman;
dbms_output.put_line('Department: '|| dept_name );
exception
when no_data_found then
dbms_output.put_line('Unable to find record no. '||&department);
when zero_divide then
dbms_output.put_line('Divide by zero error.');
--when too_many_rows then
--dbms_output.put_line('There have been more results than expected');when others then
dbms_output.put_line('There has been an error. ' ||SQLCODE|| ' ' ||SQLERRM);
end;
Error message: "ORA-01422: exact fetch returns more than requested number of rows"

An example of SQLERRM being more useful is that it can output an error message in a local language.

declare
dept_name varchar2 (50);
count number;
begin
--count :=7/0;select department_name
into dept_name
from dept
where department_id=&department;
dbms_output.put_line('department: '|| dept_name );
exception
when no_data_found then
dbms_output.put_line('Unable to find record no. '||&department);
when zero_divide then
dbms_output.put_line('Divide by zero error.');
--when too_many_rows then
--dbms_output.put_line('There have been more results than expected');when others then
dbms_output.put_line('There has been an error. ' ||SQLCODE|| ' ' ||SQLERRM);
end;
The resulting error message: "ORA-01422: exact fetch returns more than requested number of rows"

SQLERRM is a better procedure as it can output the ORA-01422 error dialog in a local language.

exception
when others then
dbms_output.put_line(SQLERRM);

At least it can do something like:

Pragma Exception_INIT

alter table hr.dept modify DEPARTMENT_name not null novalidate;
insert into dept (department_id)
values (700);
Outputs an error such as: " ORA-01400 " : cannot insert null into....

As this is an undefined error, it can be declared using 'pragma', which is a useful way of catching undeclared errors.

declare
not_null_not_allowed exception ;
pragma exception_init(not_null_not_allowed , -1400);begin
insert into dept (department_id)
values (700);
exception
when not_null_not_allowed then
dbms_output.put_line('Error: Can't leave a not null field !');
end;

Error: Can't leave a not null field !
Implicit Cursors

begin
delete from copy_dept
where department_id < 30 ;
dbms_output.put_line(SQL%ROWCOUNT||'records have been deleted.');
end;
With an output like "3 records have been deleted", it is possible to see the number of records that have been affected.
begin
delete from copy_dept
where department_id > 1000 ;
if (SQL%FOUND) then
dbms_output.put_line('Deletion completed.');
else
dbms_output.put_line('Unable to delete.');
end if ;
end;
Returns "unable to delete" message.

SQL%FOUND: If the SQL changes any records, SQL%FOUND becomes FALSE as all of the values in the example become less than 1000. SQL%NOTFOUND becomes TRUE, as SQL%NOTFOUND is the reverse of SQL%FOUND.

To delete values larger than 30, SQL%FOUND becomes TRUE. This can be considered a confirmation of running the SQL query.

User-Defined Events

Example:

A condition where a salary shouldn't be less than the average salary has been defined.

This is to be applied to new users so that their entered salary amount is checked against the average and a higher salary should be entered.

Oracle doesn't have error handling if a lower salary has been entered. Error handling due to the business logic should be defined by the software developer.

In an example condition of not allowing more than 10 people to be on leave during a working day. If the human resources department wants to grant leave to 11 people, the dbms should catch and output a defined business logic error.

declare
salary number;
avg_salary number;
higher_than_avg_salary exception;
begin
salary :=3000;
select round(avg(salary))
into avg_salary
from employees;
if (salary < avg_salary) then
raise higher_than_avg_salary;
end if;dbms_output.put_line('Average Salary: ' ||avg_salary);
exception
when higher_than_avg_salary then
dbms_output.put_line(' The entered amount of (' ||salary||') should not be less that ('||avg_salary||'));
end;
The output "The entered amount of 3000 should not be less than 6462"

Note: A range of 20000 - 20999 has been reserved for users.

begin
salary :=3000;
select round(avg(salary))
into avg_salary
from employees;
if (salary < avg_salary) then
raise_application_error(-20100,'The entered salary should not be less than the average salary.');
end if;dbms_output.put_line('Average Salary: ' ||avg_salary);
end;
Error Output "ORA-20100: The entered salary should not be less than the average salary."

The examples shown here should not be placed in Procedures or Functions as any errors would be sent to the application server instead of being displayed to the user.


Explicit Cursors

This is one of 5 important topics.

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.

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.

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.

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.

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 ;

begin
truncate table emp ;
end;

This can't work directly (DDL,create drop,alter truncate) and requires dynamic sql.

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.

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

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.

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;
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;

The 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)

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:

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.

select *
from employees
for update ;

All record locks get transferred to the user who runs this statement. The locks can be undone with:

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.

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

It'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.

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.

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.


Function

Sysdate is an example of a function triggered by the sysdate system date.

dbms_output.put_line

A function retrieves a value. (SELECT) generally carries out select operations within functions. It requests a set of ids and returns the corresponding names.

Example, return the Turkish date:

Create or Replace Function: Represented as a c or an r in the view. Privileges are removed if this is dropped.

This is why create or replace is preferred, as the code can change but the privileges remain.

create or replace function get_turkish_date return varchar2 is
date varchar2 (15);
begin
select to_char(sysdate, 'DD/MM/YYYY')
into date
from dual;
return date;
end;

Can be run in toad's editor with "run as script"

select get_turkish_date from dual;

This function works on every table, it returns as many results as there are rows in a table

select get_turkish_date from emp;

Returns a single result from dual. Alternatively, create a table called single_row so that there would only be a single line entry.

Example: Change to upper case

create or replace function change_upper_case (p_word varchar2) return varchar2 is
upper_case_word varchar2(100);
begin
upper_case_word := upper(p_word);
return upper(p_word);
end;
select change_upper_case('mehmet') from dual;
select first_name, change_upper_case(first_name) from emp; -- creates a column next to the functions and changes them to upper case.
select change_upper_case(first_name) from emp ; -- function changes to upper case.
applying a function to first_name.
create public synonym bhc for change_upper_case; a hr user may not have the necessary privileges. grant create public synonym to hr;
create synonym bhc for change_upper_case; creating a synonym specifically for or granting privileges to hr users

Where the functions are used: The set name, so that a user can give an id to get its name.

create or replace function get_dept_name(p_deptno number) return varchar2 is
dept_name varchar2(30);
begin
select department_name
into dept_name
from departments
where department_id = p_deptno;
return dept_name;
exception
when others then
return SQLERRM;
end;
select get_dept_name (100) from dual;
select get_dept_name (108) from dual; -- as it doesn't contain any exception handling, it will return an error when there isnt any data
create or replace function get_dept_name(p_deptno number) return varchar2 is
dept_name varchar2(30);
begin
select department_name
into dept_name
from departments
where department_id = p_deptno;
return dept_name;
exception
when no_data_found then
return p_deptno || ' no set with entered number ' ;
end;
Outputs an error such as "There is no set number 108"a


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.

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:

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.

select 'ismail' from dual;
select replace('ismail','i','İ') from dual;

A workaround is to replace the lowercase i's

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.

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:

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

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.

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

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

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.

grant execute on raise_salary to student01;

Errors that could be caused by creating functions and procedures.

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

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.

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.


Packages

There could be a high number of functions and procedures within a project. It is helpful to summarize the functions used in the production machine.

What functions and procedures are used for will be answered in this section.

A package collects the procedures and functions together. It consists of 2 parts:

Spec: Where the definitions are placed

Body: Contains the code.

CREATE OR REPLACE PACKAGE date_time is -- 'as' could also be used in this statement
function convert_date return varchar2;
function convert_time return varchar2;
procedure print_date_time ; -- the procedure doesn't have a return type
end ;
CREATE OR REPLACE PACKAGE BODY datetime is
function convert_date return varchar2 is
date varchar2(20);

begin
select to_char(sysdate, 'DD/MM/YYYY')
into date
from dual;
return date ;
end;
function get_time return varchar2 is
time varchar2(20);
begin
select to_char(sysdate, 'HH24:MI:SS')
into time
from dual;
return time;
end;
procedure print_time_date is
begin
dbms_output.put_line(sysdate);
end;
end;

 

All definitions in the spec must have corresponding code in the body. Code that isnt going to be run elsewhere can be placed in the package's spec section.

It isn't possible to close functions and procedures from outside access, but this can be done in packages.

Package

select DATETIME.GET_TIME_DATE from dual ;

This procedure can't be run as it is, it needs to between a begin - end or with an exec.

Anything called from the package loads the entire contents of the package into memory. There shouldn't be any standalone functions or packages within projects, they should all be contained within packages. When working on professional projects, it's recommended preparing and placing all code within packages.

Example:

CREATE OR REPLACE PACKAGE dept_ADMINISTRATION is
procedure add_dept(p_deptno number, p_deptname varchar2);
procedure remove_dept(p_deptno number);
--procedure printout_dept;
--function get_dept_name (p_deptno number) return varchar2;
--function get_dept_count return number;
end;
CREATE OR REPLACE PACKAGE BODY dept_dept_ADMINISTRATION is
procedure add_dept(p_deptno number, p_deptname varchar2) is
begin
insert into dept(department_id, department_name)
values (p_deptno, p_deptname);
end;
procedure remove_dept(p_deptno number) is
begin
delete from dept
where department_id = p_deptno;
if (SQL%NOTFOUND) then
raise_application_error(-20100, 'No such department');
end if;
exception
when others then
raise_application_error(-20100, SQLERRM);
end;
end;

UTL_FILE

UTL_FILEs are used by PL/SQL programs to read and write physical (operating system) files on Oracle servers.

(CSV etc.)

utl_mail - to send mail

utl_file - to read and write files

Example: To read employee records and write them to a text file utl_file cannot write to the root of "c:\" but can write to another child directory.

create directory Reports, as '/u02/UTL_Directory' - gives permission to users to create a directory.

grant create any directory to user_name;

alternatively

Create a directory as a system user and give read/write permissions to a user.

grant read,write on directory Reports to user_name;

Users aren't allowed to own directories, who ever makes a directory they appear as belonging to the sys.

They can be queried from "dba_directories".

UTL_FILE parameters:

fopen ; Opens the file
is_open : Is the file open or closed
put : to write something
new_line : to move to the next line
put_line : to write to the next line
get_line : to read
to write puts and to read gets
fseek : to find a file
fclose : to close a file

The directory names must be written in upper case letters.

declare
textfile utl_file.file_type;
cursor c_employees is
select employee_id, first_name, last_name
from employees ;
row varchar2(500);
begin
textfile := utl_file.fopen('REPORTS','employees_records.txt','w',32767);
for r_employees in c_employees
loop
row := r_employees.employee_id||','||r_employees.first_name||','||r_employees.last_name;
utl_file.PUT_LINE(textfile,row);
end loop;

utl_file.fclose(textfile);
end;

declare
row varchar2(500); -- A maximum of 4000
textfile utl_file.file_type;
begin
textfile := utl_file.FOPEN('REPORTS','employees_records.txt','r',32767);
if not utl_file.IS_OPEN(textfile) then
dbms_output.put_line('Unable to open file');
else
loop
utl_file.GET_LINE(textfile,row);
dbms_output.put_line(row);
end loop;
end if ;
utl_file.fclose(textfile);
exception
when no_data_found then
utl_file.fclose(textfile);
when others then
dbms_output.put_line(SQLERRM);
utl_file.FCLOSE_ALL;

end ;


Trigger
Both tables and databases can have triggers.

Table triggers are mechanisms that run before or after insert, update or delete operations.

Example: When there is a link between 2 databases, in a situation where one had an insert on table X a trigger can be used so that the same data can automatically be inserted on table Y (insert trigger - before insert, after insert).

A trigger can also be used in a situations such as preventing making changes outside of working hours, by configuring the trigger to check the working hours.

Types of triggers

Statement Level Trigger: Triggered whenever a particular statement is run. (example: blocking inserts during specified hours).

Row Level Trigger: Works on every row in a table. Used in scenarios where changes made are to be mirrored in another location.

Instead of Trigger: Can update xx fields, while using a view. Doesn't have a wide variety of usage.

Example: To prevent delete/update/insert operations to the employees table outside of working hours

create or replace trigger security_time_check
before insert or update or delete on emp -- can also be declared as: before update of salary on emp
declare
day varchar2(3);
hour number(2);
begin
day := to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH');
hour := to_number(to_char(sysdate, 'fmHH24'));
if (day = 'SATURDAY' OR day = 'SUNDAY') OR (hour not between 8 and 17) then
raise_application_error(-20100, 'Changes outside of working hours are not allowed');
end if ;

end;

To disable the trigger:

alter trigger security_time_check disable ;
create or replace trigger security_time_check
before insert or update or delete on emp
declare
day varchar2(3);
hour number(2);
begin
day := to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH');
hour := to_number(to_char(sysdate, 'fmHH24'));
if (day = 'SATURDAY' OR day = 'SUNDAY') OR (hour not between 8 and 17) then
if inserting then
raise_application_error(-20100, 'Insert operations are not allowed outside of working hours');
elsif updating then
raise_application_error(-20100, 'Update operations are not allowed outside of working hours');
else
raise_application_error(-20100, 'Delete operations are not allowed outside of working hours');
end if;
end if ;
end;up

Row Level Triggers

for each row: applies to every row in the table

Example: when updating the salary, it shouldn't be below the average

create or replace trigger bigger_than_av_salary
before insert or update or delete of salary on emp
for each row
declare
av_salary number ;
begin
select round(avg(salary))
into av_salary
from emp ;

if :new.salary < av_salary then -- new salary
raise_application_error(-20100, 'The new value for salary cannot be less than the average salary of ('||av_salary||')');
end if ;
end;

As this codeblock works on a row level, it's unable to find the average salary

The log table

create table emp_log_table
(
user_name varchar2(20),
date_time date,
table_name varchar2(30),
transaction varchar2(10),
employees_id number,
old_salary number,
new_salary number
)
create or replace trigger trg_emp_salary_log
after update of salary on emp
for each row
declare
begin
INSERT INTO HR.EMP_LOG_TABLE (
USER_NAME, DATE_TIME, TABLE_NAME,
TRANSACTION, EMPLOYEES_ID, OLD_SALARY,
NEW_SALARY)
VALUES (user ,sysdate ,'employees' ,'update',:new.employee_id , :old.salary , :new.salary );
end;

Create a table

create table customer
(
customer_id number primary key,
name varchar2(20),
surname varchar2(20)
)

Create a sequence

create table customer
(
customer_id number primary key,
name varchar2(20),
surname varchar2(20)
)

Create a trigger

create or replace trigger trg_bef_ins_customer
before insert on customer
for each row
declare
begin
select seq_customer.nextval
into :new.no
from dual;
end;

To check

insert into customer(name, surname)
values ('Testing', 'Mistake')

The 'new' and 'old' keywords can only be used for each row, i.e. on a row level and not on a statement level.

The best way to add a field to an Auto increment is by using both a sequence and a trigger.

Example: To log the users and dates of transactions.

create table customer_log
(
user_name varchar2(20),
transaction varchar2(10),
date_time date
)

Create a procedure

create or replace procedure proc_log_customer
(user_name varchar2, transaction varchar2) as
begin
insert into customer_log
values(user_name, transaction, sysdate);

end;

Create a trigger

create or replace trigger trg_after_ins_customer
after insert on customer
for each row
declare
begin
proc_log_customer(user, 'insert');
end;

Alternatively

create or replace trigger trg_after_ins_customer_1
after insert on customer
call proc_log_customer(user,'insert')

Note: If the previous 3 lines of code are placed between a begin and an end, it won't be called.

To check

alter trigger trg_after_ins_customer compile
alter trigger trg_after_ins_customer disable/enable

In newer DBs, fields that used to store data in long type, now store them as CLOBs (character large objects).

The Long data type can hold upto 2GBs of data, whereas CLOB can hold upto 4GB. Another advantage of using CLOB over Long is that is isn't possibie to search for text within a Long field type.

Example:

create table textbody1
(
id number,
textbody long
);
create table textbody2
(
id number,
textbody clob
);
insert into textbody1 values(1,'test test')
insert into textbody2 values(1,'test test')
select * from textbody1 where textbody like '%test%'

This previous query returns a result for textbody2 but gives an error for textbody1.

type... table

Declared as 'index by table'. Choosing between Binary integer or PLS_integer, PLS is preferred as it is faster.

declare
type count_list is table of number
index by pls_integer;
numbers count_list;

begin
FOR i IN 1 .. 6 LOOP
numbers(i) := i + 5;

END LOOP;
FOR i IN numbers.first..numbers.last LOOP
dbms_output.put_line(numbers(i));
END LOOP;

end;

declare
type count_list is table of number
index by pls_integer;
numbers count_list;

begin
FOR i IN 1 .. 6 LOOP
numbers(i) := i + 5;

END LOOP;
FOR i IN count_list.first..count_list.last LOOP
dbms_output.put_line(count_list(i));
END LOOP;
dbms_output.put_line('number:'||count_list.count);
end;

declare
type count_list is table of number
index by pls_integer;
numbers count_list;

begin
FOR i IN 1 .. 6 LOOP
numbers(i) := i + 5;

END LOOP;
FOR i IN numbers.first..numbers.last LOOP
dbms_output.put_line(numbers(i));
END LOOP;
dbms_output.put_line('number:'||numbers.count);
if (numbers.exists(6)) then
dbms_output.put_line('There are 6 employees.');
end if;
end;

Checking to see whether there are 6 employees:

output
6
7
8
9
10
11
count:6
There are 6 employees

numbers.first numbers, takes it to 1.

numbers.last numbers, takes it to 6.

numbers.count numbers, shows the total number of employees.

numbers.exists, whether specified numbers exist, i.e. is there 0, or 6, or 7, etc.

declare
type count_list is table of number
index by pls_integer;
numbers count_list;

begin
FOR i IN 1 .. 6 LOOP
numbers(i) := i + 5;

END LOOP;
FOR i IN numbers.first..numbers.last LOOP
dbms_output.put_line(numbers(i));
END LOOP;
dbms_output.put_line('number:'||numbers.count);
if (numbers.exists(6)) then
dbms_output.put_line('There are 6 employees.');
end if;
end;

Copyright © 2015 Sysdba All Rights Reserved Web Designer