1

Data Base Management

Systems(DBMS)
B LALITHA RAJESWARI
ASSISTANT PROFESSOR
CSE
 2
More Queries

 Retrieve the names of all employees who do not have supervisors.

 SELECT Fname, Lname FROM EMPLOYEE WHERE Super_ssn IS NULL;
 3
Nested Queries, Tuples, and
Set/Multiset Comparisons
 The first nested query selects the project numbers of projects that have an employee with last
name ‘Smith’ involved as manager, while the second nested query selects the project numbers of
projects that have an employee with last name ‘Smith’ involved as worker.

 In the outer query, we use the OR logical connective to retrieve a PROJECT tuple if the PNUMBER
value of that tuple is in the result of either nested query

 Some queries require that existing values in the database be fetched and then used in a
comparison condition

 Nested queries, which are complete select-from-where blocks within the WHERE clause of another
query .That other query is called the outer query.

 The comparison operator IN, which compares a value v with a set (or multiset) of values V and
evaluates to TRUE if v is one of the elements in V
 4
Cont..

 Note: If a nested query returns a single attribute and a single tuple, the query result will
be a single (scalar) value. In such cases, it is permissible to use = instead of IN
 In general, the nested query will return a table (relation), which is a set or multiset of
tuples.
 5
Cont..

 This query will select the Essns of all employees who work the same (project, hours)
combination on some project that employee ‘John Smith’ (whose Ssn = ‘123456789’) works on

 In addition to the IN operator, a number of other comparison operators can be used to

compare a single value v

 The = ANY (or = SOME) operator returns TRUE if the value v is equal to some value in the set V
and is hence equivalent to IN. The two keywords ANY and SOME have the same effect

 The keyword ALL can also be combined with each of these operators.
 6
Cont..

 The names of employees whose salary is greater than the salary of all the
employees in department 5

 Retrieve the name of each employee who has a dependent with the same first
name and is the same sex as the employee.
 7
Cont..

 A condition in the WHERE clause of a nested query references some attribute of a relation
declared in the outer query, the two queries are said to be correlated.
 The EXISTS and UNIQUE Functions in SQL
 The EXISTS function in SQL is used to check whether the result of a correlated nested query is
empty (contains no tuples) or not.
 The result of EXISTS is a Boolean value TRUE if the nested query result contains at least one
tuple, or FALSE if the nested query result contains no tuples.
 8
Cont..
Subqueries with the UPDATE & DELETE 9
Statements

 UPDATE CUSTOMERS SET SALARY = SALARY * 0.25 WHERE AGE IN (SELECT

AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 );

 DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM

CUSTOMERS_BKP WHERE AGE >= 27 );
 10
Explicit Sets and Renaming of
Attributes in SQL

 It is also possible to use an explicit set of values in the WHERE clause, rather than a nested
query.

 Retrieve the Social Security numbers of all employees who work on project numbers 1, 2,
or 3.

 SELECT DISTINCT Essn FROM WORKS_ON WHERE Pno IN (1, 2, 3);

 SELECT E.Lname AS Employee_name, S.Lname AS Supervisor_name FROM EMPLOYEE AS

E, EMPLOYEE AS S WHERE E.Super_ssn=S.Ssn;
 11
Introduction PL/SQL

 PL/SQL is a block-structured language.

 Each Object within a block has “Scope”
 Blocks are of two types
 Anonymous
 Block of code without a name. It can be used any where in a program and is sent to the server engine for
execution at runtime

 Named
 A block of code which has name. A “Sub Program” is a named block can be called and take arguments
 A Procedure is a subprogram that can perform an action
 A Function is a subprogram that returns a value
 A package is formed from a group procedures and functions
 A trigger is a block that is called implicitly by a DML Statements
 12
Block Structure in PL/SQL

 A PL/SQL block has three parts:

 A declarative part
 An executable part
 An exception-handling part
 13
Syntax

 [Declare]
 declaration of constants and variables , cursors and Exceptions]
 BEGIN
 Executable PL/SQL and SQL statements
 [Exception
 Actions for error conditions]
 END;
 Comments
 Single line Comment: -- this is single line Comment
 Multi Line Comment : /* This is a multiline Comment */
 14
Cont..

 Code block start with a declaration section, in which memory variables, constants,
cursors and other oracle objects can be declared and if required initialized.

 Begin section which describe processes that have to be applied to table data.
Actual data manipulation, retrieval, looping and branching constructs are specified
in this section.

 Exception section: This section deals with handling errors that arise during execution
data manipulation statements, which make up PL/SQL code block

 End section: This marks the end of a PL/SQL block.

 15
Reserved Words

 DECLARE,BEGIN,END,IF,WHILE,EXCEPTION,PROCEDURE,FUNCTION,PACKAGE AND TRIGGER

 User-defined Identifiers

 The name can be 1 to 30 characters

 The name must start with a letter

 Letters [A-Z , a-z],numbers, dollar sign($),number sign(#),the underscore(_) are allowed

 Spaces are not allowed ,Other special characters are not allowed

 Keywords can not be used as user-defined identifiers

 Names must be unique within a block

 Name should not be the same as the name of a column used in the block
 16
Data Types

 Four Types of Data Types  Composite

 Scalar: It is Atomic in nature  These are made up of elements or
components
 Character(CHAR,VARCHAR)  Records , Tables , Varrays
 Reference
 Number(integer , int , small int ,
 LOB
BINARY_INTEGER)
 BLOB
 Decimal(Number ,DEC ,DECIMAL ,FLOAT , REAL  CLOB
Double Precision)  BFILE
 NCLOB
 Boolean(TRUE,FALSE)

 Date
 17
Variable Declaration

 Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or any PL/SQL
datatype
 part_no NUMBER(4);
 in_stock BOOLEAN;
 Anchored Declaration
 It Uses %Type
 Variablename typeAttribute%Type [value assignment]
 Example :
 num1 number(3); num2 num1%Type
 Emp_sal Employee.salary%Type
 Dept_name Dept.Dname%Type
 18
Assigning values

 The first way uses the assignment operator (:=), a colon followed by an equal sign

 tax := price * tax_rate;

 valid_id := FALSE;

 bonus := current_salary * 0.10;

 wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;

 The second way to assign values to a variable is by selecting (or fetching) database
values into it

 SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;
 19
Cont..

 The third way to assign values to a variable is by passing it as an OUT or IN OUT parameter to a
subprogram.

 DECLARE

 my_sal REAL(7,2);

 PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ...

 BEGIN

 SELECT AVG(sal) INTO my_sal FROM emp;

 adjust_salary(7788, my_sal); -- assigns a new value to my_sal

 20
Example program

 set serveroutput ON  c:=b;

 DECLARE  :MAX:=50;
 -- declare variable a, b and c  dbms_output.put_line('The Value of a is'||a);
 -- and these three variables datatype are  dbms_output.put_line('The Value of b is'||b);
integer
 dbms_output.put_line('The Value of c is'||c);
 a number;
 dbms_output.put_line('The Value of pi is'||Pi);
 b number default 0;
 dbms_output.put_line('The Value of MAX
 c b%TYPE; -- Anchor type is’||:MAX);
 Pi constant number(3,2):=3.14;  END;
 BEGIN  /
 a:= 10;
 21
Constant and Bind Variables

 Declaring a constant is like declaring a variable except that you must add the
keyword CONSTANT and immediately assign a value to the constant.

 credit_limit CONSTANT REAL := 5000.00;

 Bind Variable

 These are also known as host variables

 These are declared at SQL* PLUS Environment and are accessed by a PL/SQL Block

 Anonymous blocks don’t take any arguments ,host variables are accessed by prefixed
with :

 These Variables are passed to procedures and functions as arguments

 22
Cont..

 Substitution Variables
 There are no Explicit Input/Output Statements , but Substitution variables
of SQL are available in PL/SQL
 A Standard Prompt for “variables” appears on the screen for users to type
in a value for it
 SET VERIFY OFF
 PRINT
 23
Cont..

 set serveroutput ON  c:=b;

 DECLARE  :num1:=c;
 -- declare variable a, b and c  dbms_output.put_line('The Value of substitution
variable a is'||a);
 -- and these three variables datatype are
integer  dbms_output.put_line('The Value of b is'||b);
 a number;  dbms_output.put_line('The Value of c is'||c);
 b number default 0;  dbms_output.put_line('The Value of Constant
pi is'||Pi);
 c b%TYPE;
 dbms_output.put_line('The Value of Bind
 Pi constant number(3,2):=3.14;
variable num1 is'||:num1);
 BEGIN
 END;
 a:=#
 /
 24
Printing in PL/SQL

 Oracle have built-in package “DBMS_OUTPUT” with a procedure “PUT_LINE” to

print

 An environment variable names SERVEROUTPUT must be toggled ON to view

output

 Example : DBMS_OUTPUT. PUT_LINE(‘This is the sample program’);

 DBMS_OUTPUT. PUT(‘This is the sample program’);

 25
Cont..

 set serveroutput ON  --find largest number

 DECLARE  --take it in c variable
 -- declare variable a, b and c  IF a > b THEN
 -- and these three variables datatype  c:= a;
are integer
 ELSE
 a number;
 c:= b;
 b number;
 END IF;
 c number;
 dbms_output.put_line(' Maximum
 BEGIN number in 10 and 100: ' || c);
 a:= &num1;  END;
 b:= &num2;  /
 26
Control Structures

 Selection Structure/Decision structure

 Simple If: IF condition THEN statements END IF;

 If-else : IF condition THEN statements ELSE else_statements END IF;

 If-else-else-if : IF condition_1 THEN statements_1 ELSIF condition_2 THEN

statements_2 [ ELSIF condition_3 THEN statements_3 ]... [ ELSE else_statements ] END
IF;

 Case stmt : CASE selector WHEN selector_value_1 THEN statements_1 WHEN

selector_value_2 THEN statements_2 ... WHEN selector_value_n THEN statements_n [
ELSE else_statements ] END CASE;]
 27
Loops

 Basic Loop : [ label ] LOOP statements END LOOP [ label ];

 For loop : [ label ] FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP statements

END LOOP [ label ];

 While Loop : [ label ] WHILE condition LOOP statements END LOOP [ label ];

 LOOP statements EXIT WHEN condition; END LOOP;

 The GOTO statement transfers control to a label unconditionally.

 The label must be unique in its scope and must precede an executable statement or a
PL/SQL block. When run, the GOTO statement transfers control to the labeled statement or
block

 The NULL statement only passes control to the next statement

 28
Cont..

 The EXIT statement exits the current iteration of a loop unconditionally and transfers
control to the end of either the current loop or an enclosing labeled loop.

 The EXIT WHEN statement exits the current iteration of a loop when the condition in
its WHEN clause is true, and transfers control to the end of either the current loop or an
enclosing labeled loop.

 The CONTINUE statement exits the current iteration of a loop unconditionally and transfers
control to the next iteration of either the current loop or an enclosing labeled loop.

 The CONTINUE WHEN statement exits the current iteration of a loop when the condition in
its WHEN clause is true, and transfers control to the next iteration of either the current loop
or an enclosing labeled loop.
 29
SQL in PL/SQL

 Simple IF:  temp := a;

 set serveroutput ON  a := b;
 DECLARE  b := temp;
 a NUMBER := 8;  END IF;
 b NUMBER := 5;
 temp NUMBER;  DBMS_OUTPUT.PUT_LINE ('a = '||a);
 BEGIN  DBMS_OUTPUT.PUT_LINE ('b = '||b);
 END;
 IF a > b THEN  /
 30
IF ELSE

 DECLARE
 n1 NUMBER := &num1;
 BEGIN
 -- test if the number provided by the user is even
 IF MOD(n1,2) = 0 THEN
 DBMS_OUTPUT.PUT_LINE ('The number. '||n1||' is even number');
 ELSE
 DBMS_OUTPUT.PUT_LINE ('The number '||n1||' is odd number.');
 END IF;
 DBMS_OUTPUT.PUT_LINE ('Done Successfully');
 END;
 /
 31

 DECLARE Good');
 grd CHAR(1);  ELSIF grd = 'D' THEN
 BEGIN  dbms_output. Put_line('Your Grade is:
Average');
 -- Accept value for grade
 ELSIF grd = 'F' THEN
 grd := '&new_grd';
 dbms_output.Put_line('Your Grade is: Poor');
 IF grd = 'A' THEN
 ELSE
 dbms_output.Put_line('Your Grade is:
Outstanding');  dbms_output.Put_line('No such grade in the
list.');
 ELSIF grd = 'B' THEN
 END IF;
 dbms_output.Put_line('Your Grade is:
Excellent');  END;
 ELSIF grd = 'C' THEN  /
 dbms_output.Put_line('Your Grade is: Very
 32
Searched Case

 DECLARE  WHEN grd = 'D' THEN

dbms_output.Put_line('Your Grade is:
 grd CHAR(1); Average');
 BEGIN  WHEN grd = 'F' THEN
 -- Accept value for grade dbms_output.Put_line('Your Grade is: Poor');
 grd := '&new_grd';  END CASE;
 CASE  EXCEPTION
 WHEN grd = 'A' THEN  WHEN CASE_NOT_FOUND THEN
dbms_output.Put_line('Your Grade is:  dbms_output.Put_line('No such grade in the
Outstanding'); list.');
 WHEN grd = 'B' THEN  END;
dbms_output.Put_line('Your Grade is:
Excellent');  /
 WHEN grd = 'C' THEN
dbms_output.Put_line('Your Grade is: Very
Good');
 33
Case

 DECLARE
 grd CHAR(1);
 BEGIN
 -- Accept value for grade
 grd := '&new_grd';
 CASE grd
 WHEN 'A' THEN dbms_output.Put_line('Your Grade is: Outstanding');
 WHEN 'B' THEN dbms_output.Put_line('Your Grade is: Excellent');
 WHEN 'C' THEN dbms_output.Put_line('Your Grade is: Very Good');
 WHEN 'D' THEN dbms_output. Put_line('Your Grade is: Average');
 WHEN 'F' THEN dbms_output.Put_line('Your Grade is: Poor');
 ELSE dbms_output.Put_line('No such grade in the list.');
 END CASE;
 END;
 /
 34
Loop

 DECLARE
 n NUMBER := 0;
 BEGIN
 LOOP
 DBMS_OUTPUT.PUT_LINE ('The value of n inside the loop is: ' || n);
 n := n + 1;
 IF n > 5 THEN
 EXIT;
 END IF;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('The value of n outside the loop is: ' || n);
 END;
 /
 35
EXIT WHEN

 DECLARE
 n NUMBER := 0;
 BEGIN
 LOOP
 DBMS_OUTPUT.PUT_LINE('The value of n inside the loop is: ' || TO_CHAR(n));
 n := n + 1;
 EXIT WHEN n > 5;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('The value of n after exit from the loop is: ' || TO_CHAR(n));
 END;
 /
 36
Continue when

 DECLARE
 n NUMBER := 0;
 BEGIN
 LOOP -- After CONTINUE statement, control resumes here
 DBMS_OUTPUT.PUT_LINE ('The value inside the loop: n = ' || TO_CHAR(n));
 n := n + 1;
 CONTINUE WHEN n < 4;
 DBMS_OUTPUT.PUT_LINE('The value inside loop, after CONTINUE: n = ' || TO_CHAR(n));
 EXIT WHEN n = 6;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE ('The value after exit from the loop: n = ' || TO_CHAR(n));
 END;
 /
 37
For loop

 DECLARE
 n number:= &first_n_number;
 BEGIN
 DBMS_OUTPUT.PUT_LINE ('The first '||n||' numbers are: ');
 for i in reverse 1..n loop
 dbms_output.put(i||' ');
 END LOOP;
 dbms_output.new_line;
 END;
 /
 38
Procedure

 A procedure is created with the CREATE OR  procedure-name specifies the name of the procedure.

REPLACE PROCEDURE statement.  [OR REPLACE] option allows the modification of an

existing procedure.
 The simplified syntax for the CREATE OR
REPLACE PROCEDURE statement is as  The optional parameter list contains name, mode and

follows-- types of the parameters.

 IN represents the value that will be passed from outside

CREATE [OR REPLACE] PROCEDURE procedure_name
and OUT represents the parameter that will be used to
[(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS |
return a value outside of the procedure.
AS}

BEGIN  procedure-body contains the executable part.

< procedure_body >  The AS keyword is used instead of the IS keyword for
END procedure_name; creating a standalone procedure.
 39
Parameter Modes in PL/SQL
Subprograms
 IN
 An IN parameter pass a value to the subprogram. It is a read-only parameter. Inside the
subprogram, an IN parameter acts like a constant. It cannot be assigned a value. It is the
default mode of parameter passing. Parameters are passed by reference.
 OUT
 An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT
parameter acts like a variable. The actual parameter must be variable and it is passed by
value.
 IN OUT
 An IN OUT parameter passes an initial value to a subprogram and returns an updated value
to the caller. It can be assigned a value and the value can be read.
 The actual parameter corresponding to an IN OUT formal parameter must be a variable, not
a constant or an expression. Formal parameter must be assigned a value. Actual parameter is
passed by value.
 40
Cont..

 CREATE OR REPLACE PROCEDURE greetings

 AS
 BEGIN
 dbms_output.put_line('Hello World!’);
 greetings;
 END;
 /
 41
Cont..

DECLARE z:= y;
a number; END IF;
b number; END;
c number; BEGIN
PROCEDURE findMin(x IN number, y IN number, z a:= 23;
OUT number)
b:= 45;
IS
findMin(a, b, c);
BEGIN
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
IF x < y THEN
END;
z:= x;
ELSE
 42
Cont..

A function is same as a procedure except that it returns a  function-name specifies the name of the function.
value..  [OR REPLACE] option allows the modification of an
existing function.
A function is created using the CREATE
 The optional parameter list contains name, mode and
FUNCTION statement.
types of the parameters. IN represents the value that
CREATE [OR REPLACE] FUNCTION function_name will be passed from outside and OUT represents the
parameter that will be used to return a value outside of
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
the procedure.
RETURN return_datatype  The function must contain a return statement.
{IS | AS}  The RETURN clause specifies the data type you are
going to return from the function.
BEGIN
 function-body contains the executable part.
< function_body > END [function_name];  The AS keyword is used instead of the IS keyword for
 43
Cont..

• To use a function, you will have to call that function to perform the defined task.

• When a program calls a function, the program control is transferred to the called function.

• A called function performs the defined task and when its return statement is executed or when the last
end statement is reached, it returns the program control back to the main program.

• To call a function, you simply need to pass the required parameters along with the function name and if
the function returns a value, then you can store the returned value. Following program calls the
function totalCustomers from an anonymous block
 44
Function Creation

DECLARE END;
a number; BEGIN
b number; a:= 23;
c number; b:= 45;
FUNCTION findMax(x IN number, y IN c := findMax(a, b);
number)RETURN number dbms_output.put_line(' Maximum of a and b is ' || c);
IS END;
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE z:= y;
END IF;
RETURN z;
 45
Recursive Functions

 When a subprogram calls itself, it is RETURN f;

referred to as a recursive call and the END;
process is known as recursion BEGIN
DECLARE num:= 6;
num number; factorial := fact(num);
factorial number; dbms_output.put_line(' Factorial '|| num || ' is ' ||
FUNCTION fact(x number) factorial);
RETURN number END;
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE f := x * fact(x-1);
END IF;
 46
Cursors

 A cursor holds the rows (one or more) returned by a SQL statement.

 The set of rows the cursor holds is referred to as the active set.

 PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries
that return only one row.

 For queries that return more than one row, you can explicitly declare a cursor to process the rows
individually.

 The set of rows returned by a multi-row query is called the result set.

 Its size is the number of rows that meet your search criteria.

 An explicit cursor "points" to the current row in the result set.

 47
Explicit Cursor

 set serveroutput on  LOOP

 DECLARE  FETCH c_pgm into p_name,p_prof1;
 p_name pgm.pname%type;  EXIT WHEN c_pgm%notfound;
 p_prof1 pgm.prof1%type;  dbms_output.put_line(p_name ||
' ' ||p_prof1);
 CURSOR c_pgm is
 END LOOP;
 SELECT pname,prof1 FROM pgm;
 CLOSE c_pgm;
 BEGIN
 END;
 OPEN c_pgm;
 /
 48
Implicit Cursor

 Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is
associated with this statement. For INSERT operations, the cursor holds the data that needs to
be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be
affected.
 DECLARE
 CURSOR c1 IS
 SELECT ename, sal, hiredate, deptno FROM emp;
 BEGIN
 FOR emp_rec IN c1 LOOP
 ... salary_total := salary_total + emp_rec.sal;
 END LOOP;
 49
Cursor FOR Loops

 You can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH,
and CLOSE statements

 A cursor FOR loop implicitly declares its loop index as a record that represents a row
fetched from the database.

 Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields
in the record, then closes the cursor when all rows have been processed.

 In the following example, the cursor FOR loop implicitly declares emp_rec as a
record:DECLARE
 50

 set serveroutput on  salary_total := salary_total +

emp_rec.salary;
 DECLARE
 END LOOP;
 salary_total pgm.salary%Type;
 dbms_output.put_line(salary_total);
 CURSOR c1 IS
 END;
 SELECT salary,pname FROM pgm;
 /
 BEGIN
 salary_total:=0;
 FOR emp_rec IN c1 LOOP
 51
Attributes

 1 %FOUND
 Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a
SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
 2 %NOTFOUND
 The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement
affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
 3 %ISOPEN
 Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor
automatically after executing its associated SQL statement.
 4 %ROWCOUNT
 Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or
returned by a SELECT INTO statement.
 52
Cont..

 DECLARE
 total_rows number(2);
 BEGIN
 UPDATE pgm SET salary = salary + 500;
 IF sql%notfound THEN
 dbms_output.put_line('no customers selected');
 ELSIF sql%found THEN
 total_rows := sql%rowcount;
 dbms_output.put_line( total_rows || ' customers selected ');
 END IF;
 END;
 /
 53
Exception

 DECLARE  exception2-handling-statements
 <declarations section>  WHEN exception3 THEN
 BEGIN  exception3-handling-statements
 <executable command(s)>  ........
 EXCEPTION  WHEN others THEN
 <exception handling goes here >  exception3-handling-statements
 WHEN exception1 THEN  END;
 exception1-handling-statements
 WHEN exception2 THEN
 54
Cont..

 DECLARE  DBMS_OUTPUT.PUT_LINE ('salary: ' ||

p_salary);
 p_prof1 pgm.Prof1%type:='&profession';
 EXCEPTION
 p_name pgm.pname%type;
 WHEN no_data_found THEN
 p_salary pgm.salary%type;
 dbms_output.put_line('No Records!');
 BEGIN
 WHEN others THEN
 SELECT pname,salary INTO p_name,
P_salary  dbms_output.put_line('Error!');
 FROM pgm  END;
 WHERE prof1=p_prof1;  /
 DBMS_OUTPUT.PUT_LINE ('Name: '||
p_name);
 55
User defined Exception

 DECLARE  DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

 c_id customers.id%type := &cc_id;  END IF;
 c_name customerS.Name%type;  EXCEPTION
 c_addr customers.address%type;  WHEN ex_invalid_id THEN
 -- user defined exception  dbms_output.put_line('ID must be greater than
zero!');
 ex_invalid_id EXCEPTION;
 WHEN no_data_found THEN
 BEGIN
 dbms_output.put_line('No such customer!');
 IF c_id <= 0 THEN
 WHEN others THEN
 RAISE ex_invalid_id;
 dbms_output.put_line('Error!');
 ELSE
 END;
 SELECT name, address INTO c_name, c_addr
 /
 FROM customers
 WHERE id = c_id;
 DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
 56
Triggers

 It is convenient to specify the type of action to be taken when certain events occur and when
certain conditions are satisfied.

 Other actions may be specified, such as executing a specific stored procedure or triggering other
updates.

 Suppose we want to check whenever an employee’s salary is greater than the salary of his or her
direct supervisor in the COMPANY database

 A typical trigger has three components:

 The event(s)

 The condition

 The action
 57
Cont..

 These are usually database update operations that are explicitly applied to the
database

 It may be necessary to write more than one trigger to cover all possible cases

 These events are specified after the keyword BEFORE - which means that the trigger
should be executed before the triggering operation is executed

 Use the keyword AFTER, which specifies that the trigger should be executed after the
operation specified in the event is completed.

 Once the triggering event has occurred, an optional condition may be evaluated.
 58
Cont..

 If no condition is specified, the action will be executed once the event occurs. If a
condition is specified, it is first evaluated, and only if it evaluates to true will the rule
action be executed. The condition is specified in the WHEN clause of the trigger

 The action is usually a sequence of SQL statements, but it could also be a database
transaction or an external program that will be automatically executed

 Triggers can be used in various applications, such as maintaining database

consistency, monitoring database updates, and updating derived data
automatically.
59
 60
Cont..

 CREATE [OR REPLACE ] TRIGGER trigger_name

 {BEFORE | AFTER | INSTEAD OF }
 {INSERT [OR] | UPDATE [OR] | DELETE}
 [OF col_name]
 ON table_name
 [REFERENCING OLD AS o NEW AS n]
 [FOR EACH ROW]
 WHEN (condition)
 DECLARE
 Declaration-statements
 BEGIN
 Executable-statements
 EXCEPTION
 Exception-handling-statements
 END;
 61
THANK YOU