20CS07 Data Base Management Systems

UNIT II

Module 1I: Introduction to SQL:

Program & Semester: B.Tech & III SEM

Academic Year: 2022 - 23

• SQL means Structured Query Language.
• SQL was called SEQUEL (Structured English QUEry Language) and was designed and implemented at IBM Research
• SQL used to create, storing, manipulating and retrieving the data from the relational database.
• SQL is a comprehensive database language: It has statements for data definitions, queries, and updates.
• The SQL was implemented by Oracle Corporation

​

Characteristics of SQL

• SQL is an ANSI and ISO standard computer language for creating and manipulating databases.
• SQL allows the user to create, update, delete, and retrieve data from a database.
• SQL is very simple and easy to learn.
• SQL works with database programs like DB2, Oracle, MS Access, Sybase, My SQL Server etc.

Advantages of SQL:

• High Speed: SQL Queries can be used to retrieve large amounts of records from a database quickly and efficiently.
• Well Defined Standards Exist: SQL databases use long-established standard, which is being adopted by ANSI & ISO. Non-SQL databases do not adhere to any clear standard.
• No Coding Required: Using standard SQL it is easier to manage database systems without having to write substantial amount of code.
• Emergence of ORDBMS: Previously SQL databases were synonymous with relational database.
• With the emergence of Object-Oriented DBMS, object storage capabilities are extended to relational databases.

​

Disadvantages of SQL:

• Difficulty in Interfacing: Interfacing an SQL database is more complex than adding a few lines of code.
• More Features Implemented in Proprietary way: Although SQL databases conform to ANSI & ISO standards, some databases go for proprietary extensions to standard SQL to ensure vendor lockin.

SQL Data types & Literals:

• SQL Data Type is an attribute that specifies the type of data of any object.
• Each column, variable and expression has a related data type in SQL. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement.

SQL Server offers six categories of data types for your use which are listed below

• Exact Numeric Data Types
• Approximate Numeric Data Types
• Date and Time Data Types
• Character Strings Data Types
• Unicode Character Strings Data Types
• Binary Data Types

Exact Numeric Data Types

​

Approximate Numeric Data Types

​

Date and Time Data Types

Note − Here, date time has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.

Character Strings Data Types

​

Unicode Character Strings Data Types

Binary Data Types

​

​

​

​

​

SQL Literals

In SQL, a literal is the same as a constant. There are several types of literals

• String
• integer
• Decimal
• datetime literals.

String Literals

String literals are always surrounded by single quotes ('). These string literal

examples contain of strings enclosed in single quotes.

Example:

'TechOnTheNet.com'

'This is a literal'

'XYZ'

'123’

​

Integer Literals

Integer literals can be either positive numbers or negative numbers, but do not

contain decimals. If you do not specify a sign, then a positive number is assumed. Here are some

Example:

536

+536

-536

Decimal Literals

Decimal literals can be either positive numbers or negative numbers and contain

decimals. If you do not specify a sign, then a positive number is assumed.

Example:

24.7

+24.7

-24.7

Datetime Literals

Datetime literals are character representations of datetime values that are enclosed

in single quotes.

Examples:

April 30, 2015'

'2015/04/30'

'2015/04/30 08:34:25'

Character Literals

Character literals are character representations of Character values that are

enclosed in single quotes.

Example:

'A'

'2'

'%’

Constant in SQL

As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.

Example

If you want to write a program which will increase the salary of the employees by 25%, you can declare a constant and use it throughout the program.

Next time when you want to increase the salary again you can change the value of the constant which will be easier than changing the actual value throughout the program.

Syntax to declare a constant

constant_name CONSTANT datatype := VALUE;

salary_increase CONSTANT number (3) := 10;

SQL Commands:

• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• DQL (Data Query Language)
• DCL (Data Control Language)
• TCL(Transactional control commands)

​

DDL (Data Definition Language)

Data Definition Language (DDL), is the part of SQL that allows a database

user to create and restructure database objects, such as the creation or the deletion

of a table. Some of the most fundamental DDL commands include the following:

• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• CREATE INDEX
• ALTER INDEX
• DROP INDEX
• CREATE VIEW
• DROP VIEW

CREATE TABLE

Creating a basic table involves naming the table and defining its columns and each

column's data type. The SQL CREATE TABLE statement is used to create a new

table.

Basic syntax of CREATE TABLE statement is as follows:

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

.....

columnN datatype,

PRIMARY KEY (one or more columns) );

CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table.

The unique name or identifier for the table follows the CREATE TABLE statement. Then in brackets comes the list defining each column in the table and what sort of data type it is.

Example:

CREATE TABLE CUSTOMERS (

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25),

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID) );

ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

​

ALTER TABLE - ADD Column: To add a column in a table, use the following syntax:

Syntax: ALTER TABLE table_name ADD column_name datatype;

​

Example: The following SQL adds an "Email" column to the "Customers" table:

ALTER TABLE Customers ADD Email varchar(255);

ALTER TABLE - DROP COLUMN:

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

Syntax: ALTER TABLE table_name DROP COLUMN column_name;

​

Example: The following SQL deletes the "Email" column from the "Customers" table:

ALTER TABLE Customers DROP COLUMN Email;

​

ALTER TABLE - ALTER/MODIFY COLUMN:

To change the data type of a column in a table, use the following syntax:

​

Syntax: ALTER TABLE table_name MODIFY column_name datatype;

​

Example: ALTER TABLE Customers MODIFY Address varchar(40);

​

DROP TABLE

This command is used to drop the table along with the data and structure of the table.

Syntax: DROP TABLE table_name;

Example: DROP TABLE employee;

​

DML (Data Manipulation Language)

Data Manipulation Language (DML), is the part of SQL used to manipulate data

within objects of a relational database. There are three basic DML commands:

• INSERT
• UPDATE
• DELETE

INSERT INTO:

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 INSERT INTO statement as follows:

1. INSERT INTO TABLE_NAME (column1, column2, column3...columnN)] VALUES

(value1, value2, value3...valueN);

Example:

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00);

Here, column1, column2...columnN are the names of the columns in the table into which you want to insert data.

​

The SQL INSERT INTO syntax would be as follows:

​

INSERT INTO TABLE_NAME VALUES (value1, value2, value3...valueN);

Example:

INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32,'Ahmedabad',2000.00);

​

UPDATE TABLE

​

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

Syntax: The basic syntax of UPDATE query with WHERE clause is as follows:

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2...., columnN = valueN

WHERE [condition];

You can combine N number of conditions using AND or OR operators.

​

Example: update ADDRESS for a customer whose ID is 6:

SQL> UPDATE CUSTOMERS SET ADDRESS = 'Pune’ WHERE ID = 6;

​

If you want to modify all ADDRESS and SALARY column values in CUSTOMERS table, you do not need to use WHERE clause and UPDATE query would be as follows:

SQL> UPDATE CUSTOMERS SET ADDRESS = 'Pune', SALARY = 1000.00;

DELETE

The SQL DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.

​

Syntax:

DELETE FROM table_name

WHERE [condition];

You can combine N number of conditions using AND or OR operators.

​

Example: DELETE a customer, whose ID is 6:

SQL> DELETE FROM CUSTOMERS WHERE ID = 6;

​

If you want to DELETE all the records from CUSTOMERS table, you do not need to use WHERE clause and

DELETE query would be as follows:

​

SQL> DELETE FROM CUSTOMERS;

Now, CUSTOMERS table would not have any record.

DQL (Data Query Language)

​

Though comprised of only one command, Data Query Language (DQL) is the most

concentrated focus of SQL for modern relational database users. The base

command is as follows:

SELECT

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

​

Syntax:

SELECT column1, column2, columnN FROM table_name;

If you want to fetch all the fields available in the field, then you can use the following syntax:

SELECT * FROM table_name;

​

Example:

SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS;

​

If you want to fetch all the fields of CUSTOMERS table, then use the following query:

SQL> SELECT * FROM CUSTOMERS;

DCL (Data Control Language)

Data control commands in SQL allow you to control access to data within the

database. These DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands are as follows:

• GRANT
• REVOKE

​

GRANT

It is used to grant various privileges on tables to user. These privileges can be any combination of select, insert, update, delete, alter, or all.

​

Syntax: GRANT PRIVILEGES ON OBJECTS TO USER;

​

Here Privileges are select, insert, update, delete, all.

Objects are name of database object that you are granting privileges for.

User is name of the user.

​

Example: Grant all on Customers to Ramesh.

REVOKE

It is used to revoke various privileges on tables from the user.

​

Syntax: REVOKE PRIVILEGES ON OBJECTS FROM USER;

​

Here Privileges are select, insert, update, delete, all.

Objects are name of database object that you are granting privileges for.

User is name of the user.

​

Example: Revoke delete on Customers from Ramesh.

​

Transactional Control Commands

​

In addition to the previously introduced categories of commands, there are commands that allow the user to manage database transactions.

​

COMMIT: Saves database transactions

ROLLBACK: Undoes database transactions

SAVEPOINT: Creates points within groups of transactions in which to ROLLBACK

COMMIT

This command is used to end a transaction. By using this the transaction changes can be made permanent to the database. This command also removes all save points in the transaction.

​

When we use any DML command like INSERT, UPDATE or DELETE, the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back.

To avoid that, we use the COMMIT command to mark the changes as permanent.

​

Syntax: commit work; (or) commit;

​

SAVE POINT

Save points are used to divide a very lengthy transaction to smaller ones. These are used to identifies a point in a transaction to which we can roll back later.

​

Syntax: savepoint savepoint_name;

​

ROLLBACK

This command restores the database to last committed stte. It is also used with SAVEPOINT command to jump to a savepoint in an ongoing transaction.

​

If we have used the UPDATE command to make some changes into the database, and realise that those changes were not required, then we can use the ROLLBACK command to rollback those changes, if they were not committed using the COMMIT command.

​

Syntax: ROLLBACK TO savepoint_name;

​

Example

​

​

INSERT INTO class VALUES(5, 'Rahul');

COMMIT;

UPDATE class SET name = 'Abhijit' WHERE id = '5';

SAVEPOINT A;

INSERT INTO class VALUES(6, 'Chris');

SAVEPOINT B;

INSERT INTO class VALUES(7, 'Bravo');

SAVEPOINT C;

SELECT * FROM class;

​

ROLLBACK TO B;

SELECT * FROM class;

​

​

​

​

​

​

​

ROLLBACK TO A;

SELECT * FROM class;

​

​

SQL OPERATORS

An operator is a reserved word, or a character used primarily in an SQL statement's

WHERE clause to perform operations, such as comparisons and arithmetic

operations. Operators are used to specify conditions in an SQL statement and to

serve as conjunctions for multiple conditions in a statement.

• Arithmetic operators
• Comparison operators
• Logical operators
• Operators used to negate conditions

SQL Arithmetic Operators

Assume variable a holds 10 and variable b holds 20

​

SQL Comparison Operators

Assume variable a holds 10 and variable b holds 20

SQL Logical Operators

Here is a list of all the logical operators available in SQL.

​

SQL Aggregate Functions:

​

An aggregate function allows you to perform a calculation on a set of values to

return a single scalar value.

​

We often use aggregate functions with the GROUP BY and HAVING clauses of

the SELECT statement. The following are the most used SQL aggregate functions:

​

• AVG – calculates the average of a set of values.
• COUNT – counts rows in a specified table or view.
• MIN – gets the minimum value in a set of values.
• MAX – gets the maximum value in a set of values.
• SUM – calculates the sum of values.

​

syntax: aggregate_function (DISTINCT | ALL expression)

​

INDEXE’S:

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

​

For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers.

​

An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.

​

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.

​

​

Indexes can also be unique, like the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there is an index.

Syntax

CREATE INDEX index_name ON table_name;

​

Single-Column Indexes

A single-column index is created based on only one table column. The basic syntax is as follows.

CREATE INDEX index_name

ON table_name (column_name);

​

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows.

CREATE UNIQUE INDEX index_name

on table_name (column_name);

​

Composite Indexes

A composite index is an index on two or more columns of a table. Its basic syntax is as follows.

CREATE INDEX index_name

on table_name (column1, column2);

​

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

​

DROP INDEX Command

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because the performance may either slow down or improve.

The basic syntax is as follows

DROP INDEX index_name;

​

VIEW’S

A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.

​

A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.

​

Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables or another view.

​

To create a view, a user must have the appropriate system privilege according to the specific implementation.

​

The basic CREATE VIEW syntax is as follows

CREATE VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

You can include multiple tables in your SELECT statement in a similar way as you use them in a normal SQL SELECT query. Following is an example to create a view from the CUSTOMERS table.

​

This view would be used to have customer name and age from the CUSTOMERS table.

CREATE VIEW CUSTOMERS_VIEW AS

SELECT name, age

FROM CUSTOMERS;

​

Updating Views: To update the view we should use CREATE or REPLACE command.

Syntax:

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2......

FROM table_name

WHERE [condition];

Example:

CREATE OR REPLACE VIEW CUSTOMERS_VIEW1 AS

SELECT name, age, SALARY

FROM CUSTOMERS;

Dropping a VIEW: This command is used to drop a view.

Syntax: DROP VIEW View_name;

Example: DROP VIEW CUSTOMERS_VIEW1;

​

NESTED QUERIES

A Subquery or Inner query or a Nested query is a query within another SQL query

and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

​

There are a few rules that subqueries must follow −

• Subqueries must be enclosed within parentheses.
• A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
• An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY.
• The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery.

• Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.
• A subquery cannot be immediately enclosed in a set function.

​

Subqueries with the SELECT Statement

Subqueries are most frequently used with the SELECT statement.

SELECT column_name  

FROM table_name  

WHERE column_name  expression operator   

( SELECT column_name  from table_name WHERE ... ); 

​

​

​

Example

SELECT *   

    FROM EMPLOYEE  

    WHERE ID IN (SELECT ID   

    FROM EMPLOYEE   

    WHERE SALARY > 4500);  

Subqueries with the INSERT Statement

SQL subquery can also be used with the Insert statement. In the insert statement, data returned from the subquery is used to insert into another table.

​

In the subquery, the selected data can be modified with any of the character, date functions

INSERT INTO table_name (column1, column2, column3....)   

SELECT *   FROM table_name  WHERE VALUE OPERATOR

​

Consider a table EMPLOYEE_BKP with similar as EMPLOYEE.

​

We can use the following syntax to copy the complete EMPLOYEE table into the EMPLOYEE_BKP table.

INSERT INTO EMPLOYEE_BKP  SELECT * FROM EMPLOYEE   

WHERE ID IN (SELECT ID  FROM EMPLOYEE); 

Select * from EMPLOYEE_BKP  ;

​

​

​

Subqueries with the UPDATE Statement

​

The subquery of SQL can be used in conjunction with the Update statement. When a subquery is used with the Update statement, then either single or multiple columns in a table can be updated.

​

Syntax:

​

UPDATE table  SET column_name = new_value  WHERE VALUE OPERATOR  

   (SELECT COLUMN_NAME  FROM TABLE_NAME  WHERE condition);

​

Example

​

Let's assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE table. The given example updates the SALARY by .25 times in the EMPLOYEE table for all employee whose AGE is greater than or equal to 29.

​

UPDATE EMPLOYEE

SET SALARY = SALARY * 0.25

WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP

WHERE AGE >= 29);

Subqueries with the DELETE Statement

​

The subquery of SQL can be used in conjunction with the Delete statement just like any other statements mentioned above.

Syntax

​

DELETE FROM TABLE_NAME WHERE VALUE OPERATOR  

   (SELECT COLUMN_NAME FROM TABLE_NAME WHERE condition);

Example

Let's assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE table.

The given example deletes the records from the EMPLOYEE table for all EMPLOYEE whose AGE is greater than or equal to 29.

​

DELETE FROM EMPLOYEE  WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP  WHERE AGE >= 29 );

​

​

​

SET Operations in SQL

SQL supports few Set operations which can be performed on the table data. These are used to get meaningful results from data stored in the table, under different special conditions. Some of the Set operations are

1. UNION

2. UNION ALL

3. INTERSECT

4. MINUS

UNION

UNION is used to combine the results of two or more SELECT statements. However, it will eliminate duplicate rows from its resultset. In case of union, number of columns and datatype must be same in both the tables, on which UNION operation is being applied.

​

Syntax

​

SELECT column_name FROM table1  

UNION  

SELECT column_name FROM table2;

​

The First table

The Second table

Example

​

SELECT * FROM First   

UNION  

SELECT * FROM Second; 

 Union All

​

Union All operation is equal to the Union operation. It returns the set without removing duplication and sorting the data.

Syntax:

SELECT column_name FROM table1  

UNION ALL  

SELECT column_name FROM table2;  

Example: 

SELECT * FROM First   

UNION ALL  

SELECT * FROM Second;  

Intersect

It is used to combine two SELECT statements. The Intersect operation returns the common rows from both the SELECT statements.

​

In the Intersect operation, the number of datatype and columns must be the same.

It has no duplicates and it arranges the data in ascending order by default.

Syntax

​

SELECT column_name FROM table1  

INTERSECT  

SELECT column_name FROM table2; 

Minus

​

It combines the result of two SELECT statements. Minus operator is used to display the rows which are present in the first query but absent in the second query.

It has no duplicates and data arranged in ascending order by default.

​

Syntax:

SELECT column_name FROM table1  

MINUS  

SELECT column_name FROM table2; 

Example

​

SELECT * FROM First   

MINUS  

SELECT * FROM Second; 

CURSORS

Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement; for example, the number of rows processed, etc.

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. 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.

You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors

• Implicit cursors
• Explicit cursors

​

Implicit Cursors

​

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement.

Programmers cannot control the implicit cursors and the information in it.

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.

In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.

The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes

Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.

The following program will update the table and increase the salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected

DECLARE

total_rows number(2);

BEGIN

UPDATE customers

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;

​

Explicit Cursors:

Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

Syntax:

CURSOR cursor_name IS select_statement;

Working with an explicit cursor includes the following steps

• Declaring the cursor for initializing the memory
• Opening the cursor for allocating the memory
• Fetching the cursor for retrieving the data
• Closing the cursor to release the allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement.

CURSOR c_customers IS

SELECT id, name, address FROM customers;

Opening the Cursor

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows −

OPEN c_customers;

Fetching the Cursor

​

Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above opened cursor as follows

FETCH c_customers INTO c_id, c_name, c_addr;

​

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close the above-opened cursor as follows

CLOSE c_customers;

DECLARE

c_id customers.id%type;

c_name customers.name%type;

c_addr customers.address%type;

CURSOR c_customers is

SELECT id, name, address FROM customers;

BEGIN

OPEN c_customers;

LOOP

FETCH c_customers into c_id, c_name, c_addr;

EXIT WHEN c_customers%notfound;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

END LOOP;

CLOSE c_customers;

END;

1 Ramesh Ahmedabad

2 Khilan Delhi

3 kaushik Kota

4 Chaitali Mumbai

5 Hardik Bhopal

6 Komal MP

PL/SQL procedure successfully completed.

Trigger

A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs.

Triggers are written to be executed in response to any DML statement or DDL statement.

​

​

CREATE [OR REPLACE ] TRIGGER trigger_name   

{BEFORE | AFTER | INSTEAD OF }   

{INSERT [OR] | UPDATE [OR] | DELETE}   

[OF col_name]   

ON table_name   

[FOR EACH ROW]   

WHEN (condition)    

DECLARE  

   Declaration-statements  

BEGIN   

   Executable-statements  

EXCEPTION  

   Exception-handling-statements  

END;  

​

create or replace trigger trigger9 before insert or update or delete on departmnet

declare

day varchar2(20);

begin

day:=to_char(sysdate,'day');

if(day=’Tuesday’ and day='thursday') then

if inserting then

raise_application_error(-20002,'inserting restricted');

elsif updating then

raise_application_error(-20003,'updating restricted');

elsif deleting then

raise_application_error(-20004,'deleting restricted');

end if;

end if;

end;