F-LBM-23-22K III [Link].

CS Lab Manual for RDBMS & ORACLE Lab

[Link]. Computer Science

CORE LAB - 6 PROGRAMMING LAB- VB & ORACLE

ORACLE LAB MANUAL

Programme Objective
To inculcate the knowledge of RDBMS concepts and Programming with Oracle as a backend solution.

Programme Outcome
PO-1 Students become knowledgeable academically to implement the principles and for rapid growth
of the IT industry.

PO-2 The programme offers face to face teaching with project-based learning, ensuring the graduate
with technical fluency.
PO - 1 This programme helps to develop academically competent and professionally motivated
personnel equipped with critical thinking that helps to improve the scientific temper with a sense of
social responsibility.
PO-2 This programme imbibes development of Software quality practices the limited
[Link] an understanding of the limitations.
PO - 2 To demonstrate the knowledge gained by understanding scientific and mathematical skills to
apply these in their own work.
PO - 3 They will be able to comprehend, write effective reports , design documentation and make
effective presentations. Students will be able to change their perspectives to the new developments in
the field of Computer Science.

List of Programs

1. Create a table for Employee details with Employee Number as primary key and following fields:
Name, Designation, Gender, Age, Date of Joining and Salary. Insert at least ten rows and perform
various queries using any one Comparison, Logical, Set, Sorting and Grouping operators.

2. Write a PL/SQL to update the rate field by 20% more than the current rate in inventory table which
has the following fields: Prono, ProName and Rate. After updating the table a new field (Alter) called
for Number of item and place for values for the new field without using PL/SQL block.

3. Write a PL/SQL program to implement the concept of Triggers

4. Write a PL/SQL program to implement the concept “Procedures”.

F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

Course Objective
SQL forms the cornerstone of all relational database operations. The ability to write the SQL language
is essential for those who develop database applications. This course provides a solid foundation of the
SQL programming language that enables students to build, query and manipulate databases. Working
in Oracle database throughout this course, students compare the ANSI/ISO standard with the SQL
implementation of this database product

Prerequisite : Basic commands in SQL

Operating System: Windows
Environment: Oracle 10g SQL * Plus

Connecting to Oracle Database from SQL*Plus

On Windows platform: Click Start, point to Programs (or All Programs), point to Oracle
Database 10g Express Edition, and then select Run SQL Command [Link]*Plus is a client
program with which you can access Oracle Database. This section shows how to start SQL*Plus
and connect to Oracle Database.

1. At the command prompt, type sqlplus and press the key Enter.

SQL*Plus starts and prompts you for your user name. Eg. Scott

2. Type your user name and press the key Enter.

SQL*Plus prompts you for your password. Eg. Tiger

3. Type your password and press the key Enter.

SQL*Plus prompts you to enter the host string Eg. Cas

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database
installation. It has a command-line user interface, a Windows Graphical User Interface (GUI)
and the iSQL*Plus web-based user interface.

There is also the SQL*Plus Instant Client which is a stand-alone command-line interface
available on platforms that support the OCI Instant Client. SQL*Plus Instant Client connects to
any available Oracle database, but does not require its own Oracle database installation. See the
Oracle Call Interface Programmer's Guide for more information on the OCI Instant Client.

SQL*Plus has its own commands and environment, and it provides access to the Oracle
Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system
commands to perform the following:

 Format, perform calculations on, store, and print from query results
 Examine table and object definitions
 Develop and run batch scripts
 Perform database administration
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

You can use SQL*Plus to generate reports interactively, to generate reports as batch processes,
and to output the results to text file, to screen, or to HTML file for browsing on the Internet.
You can generate reports dynamically using the HTML output facility of SQL*Plus, or using
the dynamic reporting capability of iSQL*Plus to run a script from a web page.

Course Outcome

Students will learn how to:

• Understand the basics of Relational Databases

• Write SQL code based on ANSI/ISO standards to build and maintain databasestructures
• Update database content with SQL and transaction handling
• Retrieve data from single or multiple tables
• Process data with row and aggregate functions
• Manipulate data with correlated and non-correlated sub-queries.
• Apply views to break down problems and enhance security

SQL:

SQL is a standard language for storing, manipulating and retrieving data in databases

What is SQL?

SQL stands for Structured Query Language

SQL lets you access and manipulate databases

SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the
International Organization for Standardization (ISO) in 1987

What can SQL do?

 SQL can execute queries against a database

 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views

PROGRAM 1: EMPLOYEE DATABASE

AIM: Create a table for Employee details with Employee Number as primary key and following
fields: Name, Designation, Gender, Age, Date of Joining and Salary. Insert at least ten rows and
perform various queries using any one Comparison, Logical, Set, Sorting and Grouping operators.

1) TABLE CREATION WITH PRIMARY KEY

The CREATE TABLE statement is used to create a new SQL database.

F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

Table Create without Primary Key:

Syntax

SQL>CREATE TABLE table_name( column1 datatype [ NULL | NOT NULL ], column2 datatype [
NULL | NOT NULL ],);

Table Create withPrimary Key:

Syntax

The syntax to create a primary key using the CREATE TABLE statement in SQL is:

SQL>CREATE TABLE table_name( column1 datatype CONSTRAINT constraint_name PRIMARY

KEY, column2datatype [ NULL | NOT NULL ],);

 The PRIMARY KEY constraint uniquely identifies each record in a table.

 Primary keys must contain UNIQUE values, and cannot contain NULL values.

 A table can have only ONE primary key; and in the table, this primary key can consist of
single or multiple columns (fields).

2) DESCRIBE COMMAND

The MySQL’s DESCRIBE or DESC both are equivalent. The DESC is the short form of DESCRIBE
command and used to dipslay the information about a table like column names and constraints on
column [Link] DESCRIBE command is equivalent to the following command

SQL>DESC TABLE NAME:

3) INSERT COMMAND

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

Syntax

There are two basic syntaxes of the INSERT INTO statement which are shown below.
SQL>INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN); (OR)
SQL>)INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

4) SELECT COMMAND
The SQL SELECT statement is used to fetch the data from a database table which returns this data in
the form of a result table. These result tables are called result-sets.

Syntax

SQL>SELECT column1, column2, columnN FROM table_name; (OR)

F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

SQL>SELECT * from TABLENAME;

5) SELECT statement with the WHERE clause

 The SQL WHERE clause is used to specify a condition while fetching the data from a
single table or by joining with multiple tables.
 If the given condition is satisfied, then only it returns a specific value from thetable.
 The WHERE clause is not only used in the SELECT statement, but it is also used in the
UPDATE, DELETE statement, etc.
 You can specify a condition using the comparison or logical operators like >, <, =, LIKE,
NOT, etc.
SQL >SELECT column1, column2, columnNFROMtable_nameWHERE [condition];

PROGRAM
1. CREATE A TABLE FOR EMPLOYEE DETAILS:
create table employee(empno number primary key,empname varchar2(20),designation
varchar2(30),gender varchar2(6),age number,dojdate,salary number);

Table created.

2. DESCRIBE A TABLE:

SQL>desc employee;

Name Null? Type

EMPNO NOT NULL NUMBER

EMPNAME VARCHAR2(20)

DESIGNATION VARCHAR2(30)

GENDER VARCHAR2(6)

AGE NUMBER

DOJ DATE
SALARY NUMBER

3. INSERT A VALUES FOR A EMPLOYEE TABLE:

SQL> insert into employee
values(&empno,'&empname','&designation','&gender',&age,'&doj',&salary);

4. SELECT ALL THE ROWS FROM THE EMPLOYEE TABLE:

SQL>select * from employee;

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

103 manoj clerk male 26 28-MAR-15 28000

102 john hr male 32 17-APR-15 30000

105 peter marketing manager male 35 01-FEB-15 35000

104 pooja project developer female 27 14-APR-15 25000

107 aishu tester female 25 20-JUL-15 24000

108 yamuna clerk female 30 11-JAN-15 31000

7 rows selected.

5. COMPARISON:
(i) SQL> select * from employee where salary>30000;

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

105 peter marketing manager male 35 01-FEB-15 35000

108 yamuna clerk female 30 11-JAN-15 31000

(ii) SQL> select * from employee where age between 25 and 30;

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

103 manoj clerk male 26 28-MAR-15 28000

104 pooja project developer female 27 14-APR-15 25000

107 aishu tester female 25 20-JUL-15 24000

108 yamuna clerk female 30 11-JAN-15 31000

(iii) SQL> select * from employee where empname like '%a';

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

------- --------------- ------------------------- -------------- ------- ------------- ------------

104 pooja project developer female 27 14-APR-15 25000

F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

108 yamuna clerk female 30 11-JAN-15 31000

(iv) SQL> select * from employee where salary in(35000,30000,28000);

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

103 manoj clerk male 26 28-MAR-15 28000

102 john hr male 32 17-APR-15 30000

105 peter marketing manager male 35 01-FEB-15 35000

(v) SQL>select * from employee where empno=103;

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

103 manoj clerk male 26 28-MAR-15 28000

5. LOGICAL:

(i) SQL> select * from employee where salary<50000 and salary>30000;

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

105 peter marketing manager male 35 01-FEB-15 35000
108 yamuna clerk female 30 11-JAN-15 31000

(ii) SQL> select * from employee where designation='manager' or designation='admin';

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

(iii) SQL> select * from employee where not salary<30000;

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

102 john hr male 32 17-APR-15 30000

105 peter marketing manager male 35 01-FEB-15 35000

108 yamuna clerk female 30 11-JAN-15 31000

4 rows selected.

6. SORTING:

(i) SQL> select * from employee order by empno;

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

102 john hr male 32 17-APR-15 30000

103 manoj clerk male 26 28-MAR-15 28000

104 pooja project developer female 27 14-APR-15 25000

105 peter marketing manager male 35 01-FEB-15 35000

107 aishu tester female 25 20-JUL-15 24000

108 yamuna clerk female 30 11-JAN-15 31000

7 rows selected.

(ii) SQL> select * from employee order by empnodesc;

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

108 yamuna clerk female 30 11-JAN-15 31000

107 aishu tester female 25 20-JUL-15 24000

105 peter marketing manager male 35 01-FEB-15 35000

104 pooja project developer female 27 14-APR-15 25000

103 manoj clerk male 26 28-MAR-15 28000

102 john hr male 32 17-APR-15 30000

101 arjun manager male 30 12-JAN-14 35000
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

7 rows selected.

7. SET OPERATION:

(i) SQL> select * from employee where salary>30000 union select * from employee where age
between 25 and 30;
EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

103 manoj clerk male 26 28-MAR-15 28000

104 pooja project developer female 27 14-APR-15 25000

105 peter marketing manager male 35 01-FEB-15 35000

107 aishu tester female 25 20-JUL-15 24000

108 yamuna clerk female 30 11-JAN-15 31000

6 rows selected.

(ii) SQL> select * from employee where salary>30000 union all select * from employee where age
between 25 and 30;

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

105 peter marketing manager male 35 01-FEB-15 35000

108 yamuna clerk female 30 11-JAN-15 31000

101 arjun manager male 30 12-JAN-14 35000

103 manoj clerk male 26 28-MAR-15 28000

104 pooja project developer female 27 14-APR-15 25000

107 aishu tester female 25 20-JUL-15 24000

108 yamuna clerk female 30 11-JAN-15 31000

8 rows selected.

(iii) SQL> select * from employee where salary>30000 intersect select * from employee where age
between 25 and 30;
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

101 arjun manager male 30 12-JAN-14 35000

108 yamuna clerk female 30 11-JAN-15 31000

(iv) SQL> select * from employee where salary>30000 minus select * from employee where age
between 25 and 30;

EMPNO EMPNAME DESIGNATION GENDER AGE DOJ SALARY

105 peter marketing manager male 35 01-FEB-15 35000

8. GROUP FUNCTION;

(i) SQL> select count(*) from employee where gender='female';

COUNT(*)

(ii) SQL> select sum(salary) from employee;

SUM(SALARY)

208000

(iii) SQL> select avg(salary) from employee;

AVG(SALARY)

29714.2857

(iv) SQL> select max(salary) from employee;

MAX(SALARY)

35000

(v) SQL> select min(salary) fromemployee;

MIN(SALARY)

24000
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

PROGRAM 2-INVENTORY TABLE

AIM :Write a PL/SQL to update the rate field by 20% more than the current rate in inventory table
which has the following fields: Prono, ProName and Rate. After updating the table a new field (Alter)
called for Number of item and place for values for the new field without using PL/SQL block.

1) PL/SQL
In PL/SQL, one needs to compose the “PLSQL Set serveroutput ON” command. The server output
checks whether PL/SQL will create the output or not. It prints the output created by the
DBMS_OUTPUT package from PL/SQL methods. PL/SQL program executes in oracle engine so
it is required to get server output result and display on the screen, else, the results will not be
displayed.
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

2) UPDATE COMMAND

he SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE
clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.

Syntax

UPDATE table_nameSET column1 = value1, column2 = value2... , columnN = valueNWHERE

[condition];

PROGRAM 2 INVENTORY MANAGEMENT QUERY

1. CREATE A TABLE AS INVENTORY:

SQL> create table inventory(prono number primary key,
2 pronamevarchar(20),
3 rate number);

Table created.

2. INSERT VALUES INTO INVENTORY TABLE:

SQL> insert into inventory values(&prono,'&proname',&rate);
Enter value for prono: 1001
Enter value for proname: soap
Enter value for rate: 30
old 1: insert into inventory values(&prono,'&proname',&rate)
new 1: insert into inventory values(1001,'soap',30)
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

1 row created.
SQL> /
Enter value for prono: 1002
Enter value for proname: horlicks
16

Enter value for rate: 100

old 1: insert into inventory values(&prono,'&proname',&rate)
new 1: insert into inventory values(1002,'horlicks',100)

1 row created.
SQL> /
Enter value for prono: 1003
Enter value for proname: milkybar
Enter value for rate: 50
old 1: insert into inventory values(&prono,'&proname',&rate)
new 1: insert into inventory values(1003,'milkybar',50)

1 row created.
SQL> /
Enter value for prono: 1004
Enter value for proname: chocobar
Enter value for rate: 40
old 1: insert into inventory values(&prono,'&proname',&rate)
new 1: insert into inventory values(1004,'chocobar',40)

1 row created.

3. SELECT ALL THE ROWS IN INVENTORY TABLE:

SQL> select * from inventory;

PRONO PRONAME RATE

1001 soap 30
1002 horlicks 100
1003 milkybar 50
1004 chocobar 40

4. UPDATE THE COLUMN AS RATE:

SQL> getinventory_update.sql
1 begin
2 update inventory set rate=rate+(rate*20/100);
3 commit;
4* end;
SQL> /
PL/SQL procedure successfully completed.

5. SELECT ALL THE ROWS IN INVENTORY TABLE:

F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

SQL> select * from inventory;

PRONO PRONAME RATE

1001 soap 36
1002 horlicks 120
1003 milkybar 60
1004 chocobar 48

6. ADDING ANEW COLUMN AS NO_OF_ITEMS IN INVENTORY TABLE:

SQL> alter table inventory add no_of_items number;

Table altered.

7. DESCRIBEA INVENTORY TABLE:

SQL>desc inventory;
Name Null? Type

PRONO NOT NULL NUMBER

PRONAME VARCHAR2(20)
RATE NUMBER
NO_OF_ITEMS NUMBER

8. UPDATE AN EXISTING COLUMNS IN INVENTORY TABLE:

SQL> update inventory set no_of_items=case prono
2 when 1001 then 10
3 when 1002 then 8
4 when 1003 then 6
5 when 1004 then 4
6 end;

4 rows updated.
9. SELECT ALL THE ROWS IN INVENTORY TABLE:
SQL> select * from inventory;

PRONO PRONAME RATE NO_OF_ITEMS

1001 soap 36 10
1002 horlicks 120 8
1003 milkybar 60 6
1004 chocobar 48 4

***************************************************************************
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

PROGRAM 3-INVENTORY MANAGEMENT SYSTEM USING DATABASE TRIGGERS

AIM: Write a PL/SQL program to implement the concept of Triggers

WHAT IS A TRIGGER?

A trigger is a named PL/SQL unit that is stored in the database and executed (fired) in
response to a specified event that occurs in the database.

Overview of Triggers

A trigger is a named program unit that is stored in the database and fired (executed) in
response to a specified event. The specified event is associated with either a table, a view, a
schema, or the database, and it is one of the following:

 A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

 A database definition (DDL) statement (CREATE, ALTER, or DROP)
 A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP,
or SHUTDOWN)

The trigger is said to be defined on the table, view, schema, or database.

TRIGGER TYPES:

A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement,
a DELETE trigger is fired by a DELETE statement, and so on.

An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The
database fires the INSTEAD OF trigger instead of executing the triggering DML statement.
For more information, see Modifying Complex Views (INSTEAD OF Triggers).

A system trigger is defined on a schema or the database. A trigger defined on a schema fires
for each event associated with the owner of the schema (the current user). A trigger defined
on a database fires for each event associated with all users.

A simple trigger can fire at exactly one of the following timing points:

 Before the triggering statement executes

 After the triggering statement executes
 Before each row that the triggering statement affects
 After each row that the triggering statement affects
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

A compound trigger can fire at more than one timing point. Compound triggers make it
easier to program an approach where you want the actions you implement for the various
timing points to share common data. For more information, see Compound Triggers.

TRIGGER STATES

A trigger can be in either of two states:

Enabled. An enabled trigger executes its trigger body if a triggering statement is entered and
the trigger restriction (if any) evaluates to TRUE.

Disabled. A disabled trigger does not execute its trigger body, even if a triggering statement
is entered and the trigger restriction (if any) evaluates to TRUE.

By default, a trigger is created in enabled state. To create a trigger in disabled state, use
the DISABLE clause of the CREATE TRIGGER statement.

DATA ACCESS FOR TRIGGERS

When a trigger is fired, the tables referenced in the trigger action might be currently
undergoing changes by SQL statements in other users' transactions. In all cases, the SQL
statements running within triggers follow the common rules used for standalone SQL
statements. In particular, if an uncommitted transaction has modified values that a trigger
being fired either must read (query) or write (update), then the SQL statements in the body of
the trigger being fired use the following guidelines:

 Queries see the current read-consistent materialized view of referenced tables and any
data changed within the same transaction.
 Updates wait for existing data locks to be released before proceeding.

USES OF TRIGGERS

Triggers supplement the standard capabilities of your database to provide a highly

customized database management system. For example, you can use triggers to:

 Automatically generate derived column values

 Enforce referential integrity across nodes in a distributed database
 Enforce complex business rules
 Provide transparent event logging
 Provide auditing
 Maintain synchronous table replicates
 Gather statistics on table access
 Modify table data when DML statements are issued against views
 Publish information about database events, user events, and SQL statements to
subscribing applications
 Restrict DML operations against a table to those issued during regular business hours
 Enforce security authorizations
 Prevent invalid transactions
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

GUIDELINES FOR DESIGNING TRIGGERS

Use the following guidelines when designing triggers:

 Use triggers to guarantee that when a specific operation is performed, related actions
are performed.
 Do not define triggers that duplicate database features.

For example, do not define triggers to reject bad data if you can do the same checking
through constraints.

Although you can use both triggers and integrity constraints to define and enforce any
type of integrity rule, Oracle strongly recommends that you use triggers to constrain
data input only in the following situations:

o To enforce referential integrity when child and parent tables are on different
nodes of a distributed database
o To enforce complex business rules not definable using integrity constraints
o When a required referential integrity rule cannot be enforced using the
following integrity constraints:
 NOT NULL, UNIQUE
 PRIMARY KEY
 FOREIGN KEY
 CHECK
 DELETE CASCADE
 DELETE SET NULL
 Limit the size of triggers.

If the logic for your trigger requires much more than 60 lines of PL/SQL code, put
most of the code in a stored subprogram and invoke the subprogram from the trigger.

The size of the trigger cannot exceed 32K.

 Use triggers only for centralized, global operations that must fire for the triggering
statement, regardless of which user or database application issues the statement.
 Do not create recursive triggers.

For example, if you create an AFTER UPDATE statement trigger on

the employees table, and the trigger itself issues an UPDATE statement on
the employees table, the trigger fires recursively until it runs out of memory.

 Use triggers on DATABASE judiciously. They are executed for every user every time
the event occurs on which the trigger is created.
 If you use a LOGON trigger to monitor logons by users, include an exception-
handling part in the trigger, and include a WHEN OTHERS exception in the
exception-handling part. Otherwise, an unhandled exception might block all
connections to the database.
 If you use a LOGON trigger only to execute a package (for example, an application
context-setting package), put the exception-handling part in the package instead of in
the trigger.
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

PRIVILEGES REQUIRED TO USE TRIGGERS

To create a trigger in your schema:

 You must have the CREATE TRIGGER system privilege

 One of the following must be true:
o You own the table specified in the triggering statement
o You have the ALTER privilege for the table specified in the triggering
statement
o You have the ALTER ANY TABLE system privilege

To create a trigger in another schema, or to reference a table in another schema from a trigger
in your schema:

 You must have the CREATE ANY TRIGGER system privilege.

 You must have the EXECUTE privilege on the referenced subprograms or packages.

To create a trigger on the database, you must have

the ADMINISTER DATABASE TRIGGER privilege. If this privilege is later revoked, you
can drop the trigger but not alter it.

The object privileges to the schema objects referenced in the trigger body must be granted to
the trigger owner explicitly (not through a role). The statements in the trigger body operate
under the privilege domain of the trigger owner, not the privilege domain of the user issuing
the triggering statement (this is similar to the privilege model for stored subprograms).

PROGRAM:

i) CREATE INVENTORY MASTER TABLE:-

SQL>create table inventory_master1(orderid number primary key,custid number,orderdate
date,amount number);

Table created

ii) CREATE INVENTORY TRANSACTION TABLE:-

SQL>create table inventory_trans1(orderid number references inventory_master1,productid

number,productname varchar2(30),quantity number,unitprice number);

Table created

iii) CREATE TRIGGER ON INVENTORY MASTER:-

SQL>set serveroutput on
create or replace trigger
check1 before insert on inventory_master1 for each row
declare
F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

begin
if(:[Link]<0)then
raise_application_error(-20040,'invalid amount');
end if;
end;

Trigger created.

iv) INSERT INTO INVENTORY TABLES:-

SQL>insert into inventory_master1 values(&orderid,&custid,'&orderdate',&amount);

Enter value for orderid: 5001

Enter value for custid: 101
Enter value for orderdate: 12-JUL-20
Enter value for amount:2000
old 1: insert into inventory values(&orderid,&custid,’&orderdate’,&amount)
new 1: insert into inventory values(5001,101,12-JUL-20,2000)

1 row inserted

SQL>insert into inventory_master1 values(&orderid,&custid,'&orderdate',&amount);

Enter value for orderid: 5002

Enter value for custid: 102
Enter value for orderdate: 13-JUL-20
Enter value for amount:0
old 1: insert into inventory values(&orderid,&custid,’&orderdate’,&amount)
new 1: insert into inventory values(5002,102,13-JUL-20,0)
ORA-20202: INVALID AMOUNT
ORA-06512: AT "SURESH.CHECK1", LINE 16
ORA-04088: ERROR DURING EXECUTION OF TRIGGER 'SURESH.CHECK1'

v) CREATE TRIGGER ON INVENTORY TRANSACTION:-

SQL>create or replace trigger

check2 before insert on inventory_trans1 for each row
declare
begin
if(:[Link]<0)then
raise_application_error(-20040,'invalid quantity');
end if;
end;

Trigger created.

vi) INSERT INTO INVENTORY TRANSACTION:-

F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

SQL>insert into inventory_trans1 values

(&orderid,&productid,'&productname',&quantity,&unitprice);

Enter value for orderid: 5001

Enter value for productid: 88
Enter value for productname: Television
Enter value for quantity:10
Ener value for unitprice: 32000
old 1: insert into inventory values(&orderid,&productid,’&productname’,&quantity,&unitprice)
new 1: insert into inventory values(5001,88,Television,10,32000)

1 row inserted

SQL>insert into inventory_trans1 values

(&orderid,&productid,'&productname',&quantity,&unitprice);

Enter value for orderid: 5002

Enter value for productid: 89
Enter value for productname: Airconditioner
Enter value for quantity:-10
Ener value for unitprice: 40000
old 1: insert into inventory values(&orderid,&productid,’&productname’,&quantity,&unitprice)
new 1: insert into inventory values(5002,89,’Airconditioner’,-10,40000)
ORA-20040: INVALID QUANTITY
ORA-06512: AT "SURESH.CHECK2", LINE 16
ORA-04088: ERROR DURING EXECUTION OF TRIGGER 'SURESH.CHECK2'

*************************************************************************
 F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

PROGRAM 4 : STORED PROCEDURE

Aim : Write a PL/SQL program to implement the concept “Procedures”.

PROGRAM:
Step 1 : Creating table
> create table tbmarks
(rno number(3),name varchar(15),mark1 number(3),mark2 number(3),
total number(3),AVERAGE number(5,2),result varchar(10));

Step 2 : Creating procedure for Inserting records in tbmarks table

>create or replace procedure prmarksins(prno in number,pname in varchar,pmark1 in number,pmark2 in

number)
is
vtotal number;
vaverage number;
vresult varchar(10);
begin
vtotal:=pmark1+pmark2;
vaverage:=vtotal/2;
if(pmark1>=40 and pmark2>=40) then
vresult:='Pass';
else
vresult:='Fail';
end if;
insert into tbmarks values(prno,pname,pmark1,pmark2,vtotal,vaverage,vresult);
commit;
end;

Step 3: To Run the Procedure

SQL> exec prmarksins(1,'KING',89,90);

PL/SQL procedure successfully completed.
SQL> exec prmarksins(2,'SCOTT',39,56);
PL/SQL procedure successfully completed.

SQL> select * from tbmarks;

RNO NAME MARK1 MARK2 TOTAL AVERAGE RESULT

1 KING 89 90 179 89.5 Pass

2 SCOTT 39 56 95 47.5 Fail

Step 4 : Creating Procedure for Modifying records

>create or replace procedure prmarksupd(prno in number,

pmark1 in number,pmark2 in number)
is
vtotal number;
vaverage number;
vresult varchar(10);
begin
vtotal:=pmark1+pmark2;
 F-LBM-23-22K III [Link]. CS Lab Manual for RDBMS & ORACLE Lab

vaverage:=vtotal/2;
if(pmark1>=40 and pmark2>=40) then
vresult:='Pass';
else
vresult:='Fail';
end if;
update tbmarks set mark1=pmark1,mark2=pmark2,total=vtotal,average=vaverage,
result=vresult where rno=prno;
commit;
end;

Step 5 :
SQL> select * from tbmarks;

RNO NAME MARK1 MARK2 TOTAL AVERAGE RESULT

1 KING 89 90 179 89.5 Pass

2 SCOTT 39 56 95 47.5 Fail

SQL> exec prmarksupd(2,55,56);

PL/SQL procedure successfully completed.

SQL> select * from tbmarks;

RNO NAME MARK1 MARK2 TOTAL AVERAGE RESULT

1 KING 89 90 179 89.5 Pass

2 SCOTT 55 56 111 55.5 Pass

Step 6: Creating Procedure for deleting the record in TBMARKS table

>create or replace procedure prmarksdel(prno in number)

is
begin
delete from tbmarks where rno=prno;
commit;
end;

SQL> exec prmarksdel(2);

PL/SQL procedure successfully completed.

SQL> select * from tbmarks;

RNO NAME MARK1 MARK2 TOTAL AVERAGE RESULT

1 KING 89 90 179 89.5 Pass

******************************************************************************