QUICK -REVISION:DBMS BASIC TO CURSOR :

Data Definition Language : Introduction to SQL, Data Types, Creating Tables, Alter, Drop, Truncate, Rename

SQL is a database computer language designed for the retrieval and management of data in relational database. SQL stands for Structured Query Language

---

SQL tutorial gives unique learning on Structured Query Language and it helps to make practice on SQL commands which provides immediate results. SQL is a language of database, it includes database creation, deletion, fetching rows and modifying rows etc.

SQL is an ANSI (American National Standards Institute) standard but there are many different versions of the SQL language.

What is SQL?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.

SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.

Also, they are using different dialects, such as:

·        MS SQL Server using T-SQL,

·        Oracle using PL/SQL,

·        MS Access version of SQL is called JET SQL (native format) etc.

Why SQL?

·        Allows users to access data in relational database management systems.

·        Allows users to describe the data.

·        Allows users to define the data in database and manipulate that data.

·        Allows to embed within other languages using SQL modules, libraries & pre-compilers.

·        Allows users to create and drop databases and tables.

·        Allows users to create view, stored procedure, functions in a database.

·        Allows users to set permissions on tables, procedures, and views

SQL Commands:

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature:

DDL - Data Definition Language:

Command	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language:

Command	Description
SELECT	Retrieves certain records from one or more tables
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records

DCL - Data Control Language:

Command	Description
GRANT	Gives a privilege to user
REVOKE	Takes back privileges granted from user

SQL Constraints:

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

·        NOT NULL Constraint: Ensures that a column cannot have NULL value.

·        DEFAULT Constraint: Provides a default value for a column when none is specified.

·        UNIQUE Constraint: Ensures that all values in a column are different.

·        PRIMARY Key: Uniquely identified each rows/records in a database table.

·        FOREIGN Key: Uniquely identified a rows/records in any another database table.

·        CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.

·        INDEX: Use to create and retrieve data from the database very quickly.

·         All the examples given in this tutorial have been tested with MySQL server.

·                   SQL SELECT Statement:

·         SELECT column1, column2....columnN

·         FROM   table_name;

·                   SQL DISTINCT Clause:

·         SELECT DISTINCT column1, column2....columnN

·         FROM   table_name;

·                   SQL WHERE Clause:

·         SELECT column1, column2....columnN

·         FROM   table_name

·         WHERE  CONDITION;

·                   SQL AND/OR Clause:

·         SELECT column1, column2....columnN

·         FROM   table_name

·         WHERE  CONDITION-1 {AND|OR} CONDITION-2;

·                   SQL IN Clause:

·         SELECT column1, column2....columnN

·         FROM   table_name

·         WHERE  column_name IN (val-1, val-2,...val-N);

·                   SQL BETWEEN Clause:

·         SELECT column1, column2....columnN

·         FROM   table_name

·         WHERE  column_name BETWEEN val-1 AND val-2;

·                   SQL LIKE Clause:

·         SELECT column1, column2....columnN

·         FROM   table_name

·         WHERE  column_name LIKE { PATTERN };

·                   SQL ORDER BY Clause:

·         SELECT column1, column2....columnN

·         FROM   table_name

·         WHERE  CONDITION

·         ORDER BY column_name {ASC|DESC};

·                   SQL GROUP BY Clause:

·         SELECT SUM(column_name)

·         FROM   table_name

·         WHERE  CONDITION

·         GROUP BY column_name;

·                   SQL COUNT Clause:

·         SELECT COUNT(column_name)

·         FROM   table_name

·         WHERE  CONDITION;

·                   SQL HAVING Clause:

·         SELECT SUM(column_name)

·         FROM   table_name

·         WHERE  CONDITION

·         GROUP BY column_name

·         HAVING (arithematic function condition);

·                   SQL CREATE TABLE Statement:

·         CREATE TABLE table_name(

·         column1 datatype,

·         column2 datatype,

·         column3 datatype,

·         .....

·         columnN datatype,

·         PRIMARY KEY( one or more columns )

·         );

·                   SQL DROP TABLE Statement:

·         DROP TABLE table_name;

·                   SQL CREATE INDEX Statement :

·         CREATE UNIQUE INDEX index_name

·         ON table_name ( column1, column2,...columnN);

·                   SQL DROP INDEX Statement :

·         ALTER TABLE table_name

·         DROP INDEX index_name;

·                   SQL DESC Statement :

·         DESC table_name;

·                   SQL TRUNCATE TABLE Statement:

·         TRUNCATE TABLE table_name;

·                   SQL ALTER TABLE Statement:

·         ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};

·                   SQL ALTER TABLE Statement (Rename) :

·         ALTER TABLE table_name RENAME TO new_table_name;

·                   SQL INSERT INTO Statement:

·         INSERT INTO table_name( column1, column2....columnN)

·         VALUES ( value1, value2....valueN);

·                   SQL UPDATE Statement:

·         UPDATE table_name

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

·         [ WHERE  CONDITION ];

·                   SQL DELETE Statement:

·         DELETE FROM table_name

·         WHERE  {CONDITION};

·                   SQL CREATE DATABASE Statement:

·         CREATE DATABASE database_name;

·                   SQL DROP DATABASE Statement:

·         DROP DATABASE database_name;

·                   SQL USE Statement:

·         USE database_name;

·                   SQL COMMIT Statement:

·         COMMIT;

·                   SQL ROLLBACK Statement:

·         ROLLBACK;

Syntax:

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 )

);

syntax would be as follows:

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

Example:

Following statements would create six records in CUSTOMERS table:

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

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

Following is an example, which creates a CUSTOMERS table with ID as primary key and NOT NULL are the constraints showing that these fields can not be NULL while creating records in this table:

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

);

 if your table has been created successfully by looking at the message displayed by the SQL server, otherwise you can use DESC command as follows:

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

Syntax:

The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

SELECT column1, column2

FROM table_name

WHERE [ conditions ]

GROUP BY column1, column2

ORDER BY column1, column2

Example:

Consider the CUSTOMERS table is having the following records:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

+----+----------+-----+-----------+----------+

If you want to know the total amount of salary on each customer, then GROUP BY query would be as follows:

SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS

     GROUP BY NAME;

This would produce the following result:

+----------+-------------+

| NAME     | SUM(SALARY) |

+----------+-------------+

| Chaitali |     6500.00 |

| Hardik   |     8500.00 |

| kaushik  |     2000.00 |

| Khilan   |     1500.00 |

| Komal    |     4500.00 |

| Muffy    |    10000.00 |

| Ramesh   |     2000.00 |

+----------+-------------+

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default.

Syntax:

The basic syntax of ORDER BY clause is as follows:

SELECT column-list 

FROM table_name 

[WHERE condition] 

[ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be in column-list.

Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

+----+----------+-----+-----------+----------+

Following is an example, which would sort the result in ascending order by NAME and SALARY:

SQL> SELECT * FROM CUSTOMERS

     ORDER BY NAME, SALARY;

This would produce the following result:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

+----+----------+-----+-----------+----------+

Following is an example, which would sort the result in descending order by NAME:

SQL> SELECT * FROM CUSTOMERS

     ORDER BY NAME DESC;

This would produce the following result:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

+----+----------+-----+-----------+----------+

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

Consider the following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+--------+

|OID  | DATE                | CUSTOMER_ID | AMOUNT |

+-----+---------------------+-------------+--------+

| 102 | 2009-10-08 00:00:00 |           3 |   3000 |

| 100 | 2009-10-08 00:00:00 |           3 |   1500 |

| 101 | 2009-11-20 00:00:00 |           2 |   1560 |

| 103 | 2008-05-20 00:00:00 |           4 |   2060 |

+-----+---------------------+-------------+--------+

Now, let us join these two tables in our SELECT statement as follows:

SQL> SELECT ID, NAME, AGE, AMOUNT

        FROM CUSTOMERS, ORDERS

        WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

+----+----------+-----+--------+

| ID | NAME     | AGE | AMOUNT |

+----+----------+-----+--------+

|  3 | kaushik  |  23 |   3000 |

|  3 | kaushik  |  23 |   1500 |

|  2 | Khilan   |  25 |   1560 |

|  4 | Chaitali |  25 |   2060 |

+----+----------+-----+--------+

Here, it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.

SQL Join Types:

There are different types of joins available in SQL:

·        INNER JOIN: returns rows when there is a match in both tables.

·        LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

·        RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.

·        FULL JOIN: returns rows when there is a match in one of the tables.

·        SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

·        CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

·         he SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

·         To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length.

·                   Syntax:

·         The basic syntax of UNION is as follows:

·         SELECT column1 [, column2 ]

·         FROM table1 [, table2 ]

·         [WHERE condition]

·          

·         UNION

·          

·         SELECT column1 [, column2 ]

·         FROM table1 [, table2 ]

·         [WHERE condition]

·         Here given condition could be any given expression based on your requirement.

·                   Example:

·         Consider the following two tables, (a) CUSTOMERS table is as follows:

·         +----+----------+-----+-----------+----------+

·         | ID | NAME     | AGE | ADDRESS   | SALARY   |

·         +----+----------+-----+-----------+----------+

·         |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

·         |  2 | Khilan   |  25 | Delhi     |  1500.00 |

·         |  3 | kaushik  |  23 | Kota      |  2000.00 |

·         |  4 | Chaitali |  25 | Mumbai    |  6500.00 |

·         |  5 | Hardik   |  27 | Bhopal    |  8500.00 |

·         |  6 | Komal    |  22 | MP        |  4500.00 |

·         |  7 | Muffy    |  24 | Indore    | 10000.00 |

·         +----+----------+-----+-----------+----------+

·         (b) Another table is ORDERS as follows:

·         +-----+---------------------+-------------+--------+

·         |OID  | DATE                | CUSTOMER_ID | AMOUNT |

·         +-----+---------------------+-------------+--------+

·         | 102 | 2009-10-08 00:00:00 |           3 |   3000 |

·         | 100 | 2009-10-08 00:00:00 |           3 |   1500 |

·         | 101 | 2009-11-20 00:00:00 |           2 |   1560 |

·         | 103 | 2008-05-20 00:00:00 |           4 |   2060 |

·         +-----+---------------------+-------------+--------+

·         Now, let us join these two tables in our SELECT statement as follows:

·         SQL> SELECT  ID, NAME, AMOUNT, DATE

·              FROM CUSTOMERS

·              LEFT JOIN ORDERS

·              ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

·         UNION

·              SELECT  ID, NAME, AMOUNT, DATE

·              FROM CUSTOMERS

·              RIGHT JOIN ORDERS

·              ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

·         This would produce the following result:

·         +------+----------+--------+---------------------+

·         | ID   | NAME     | AMOUNT | DATE                |

·         +------+----------+--------+---------------------+

·         |    1 | Ramesh   |   NULL | NULL                |

·         |    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |

·         |    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |

·         |    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |

·         |    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |

·         |    5 | Hardik   |   NULL | NULL                |

·         |    6 | Komal    |   NULL | NULL                |

·         |    7 | Muffy    |   NULL | NULL                |

·         +------+----------+--------+---------------------+

·         he SQL TRUNCATE TABLE command is used to delete complete data from an existing table.

·         You can also use DROP TABLE command to delete complete table but it would remove complete table structure form the database and you would need to re-create this table once again if you wish you store some data.

·                   Syntax:

·         The basic syntax of TRUNCATE TABLE is as follows:

·         TRUNCATE TABLE  table_name;

·                   Example:

·         Consider the CUSTOMERS table having the following records:

·         +----+----------+-----+-----------+----------+

·         | ID | NAME     | AGE | ADDRESS   | SALARY   |

·         +----+----------+-----+-----------+----------+

·         |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

·         |  2 | Khilan   |  25 | Delhi     |  1500.00 |

·         |  3 | kaushik  |  23 | Kota      |  2000.00 |

·         |  4 | Chaitali |  25 | Mumbai    |  6500.00 |

·         |  5 | Hardik   |  27 | Bhopal    |  8500.00 |

·         |  6 | Komal    |  22 | MP        |  4500.00 |

·         |  7 | Muffy    |  24 | Indore    | 10000.00 |

·         +----+----------+-----+-----------+----------+

·         Following is the example to truncate:

·         SQL > TRUNCATE TABLE CUSTOMERS;

·         Now, CUSTOMERS table is truncated and following would be the output from SELECT statement:

What is PL/SQL? PL/SQL stands for Procedural Language extension of SQL. PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL. The PL/SQL Engine: Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL language code can be stored in the client system (client-side) or in the database (server-side). About This PL SQL Programming Tutorial This Oracle PL SQL tutorial teaches you the basics of database programming in PL/SQL with appropriate PL/SQL tutorials with coding examples. You can use these free online tutorials as your guide to practice, learn, for training, or reference while programming with PL SQL. I will be making more Oracle PL SQL programming tutorials as often as possible to share my knowledge in PL SQL and help you in learning PL SQL language and syntax better. Even though the programming concepts discussed in this tutorial are specific to Oracle PL SQL. The concepts like cursors, functions and stored procedures can be used in other database systems like Sybase , Microsoft SQL server etc, with some change in SQL syntax. This PL/SQL tutorial will be growing regularly; let us know if any topic related to PL SQL needs to be added or you can also share your knowledge on PL SQL with us. Lets share our knowledge about PL SQL with others.

A Simple PL/SQL Block:

Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

PL/SQL Block consists of three sections:

• The Declaration section (optional).
• The Execution section (mandatory).
• The Exception Handling (or Error) section (optional).

Declaration Section:

The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section. 

Execution Section:

The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section. 

Exception Section:

The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors. 

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:

·        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).

Triggers could be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

Triggers can be written for the following purposes:

·        Generating some derived column values automatically

·        Enforcing referential integrity

·        Event logging and storing information on table access

·        Auditing

·        Synchronous replication of tables

·        Imposing security authorizations

·        Preventing invalid transactions

Creating Triggers

The syntax for creating a trigger is:

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;

Where,

·        CREATE [OR REPLACE] TRIGGER trigger_name: Creates or replaces an existing trigger with the trigger_name.

·        {BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed. The INSTEAD OF clause is used for creating trigger on a view.

·        {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.

·        [OF col_name]: This specifies the column name that would be updated.

·        [ON table_name]: This specifies the name of the table associated with the trigger.

·        [REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE.

·        [FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.

·        WHEN (condition): This provides a condition for rows for which the trigger would fire. This clause is valid only for row level triggers.

Example:

To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters:

Select * from customers;

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

+----+----------+-----+-----------+----------+

The following program creates a row level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values:

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers

FOR EACH ROW

WHEN (NEW.ID > 0)

DECLARE

   sal_diff number;

BEGIN

   sal_diff := :NEW.salary  - :OLD.salary;

   dbms_output.put_line('Old salary: ' || :OLD.salary);

   dbms_output.put_line('New salary: ' || :NEW.salary);

   dbms_output.put_line('Salary difference: ' || sal_diff);

END;

/

When the above code is executed at SQL prompt, it produces the following result:

Trigger created.

Here following two points are important and should be noted carefully:

·        OLD and NEW references are not available for table level triggers, rather you can use them for record level triggers.

·        If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

·        Above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using DELETE operation on the table.

Triggering a Trigger

Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table:

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

VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

When a record is created in CUSTOMERS table, above create triggerdisplay_salary_changes will be fired and it will display the following result:

Old salary:

New salary: 7500

Salary difference:

Because this is a new record so old salary is not available and above result is coming as null. Now, let us perform one more DML operation on the CUSTOMERS table. Here is one UPDATE statement, which will update an existing record in the table:

UPDATE customers

SET salary = salary + 500

WHERE id = 2;

When a record is updated in CUSTOMERS table, above create triggerdisplay_salary_changes will be fired and it will display the following result:

Old salary: 1500

New salary: 2000

Salary difference: 500

Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, 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 the attributes like %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:

Attribute	Description
%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.
%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.
%ISOPEN	Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%ROWCOUNT	Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

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

Example:

We will be using the CUSTOMERS table we had created and used in the previous chapters.

Select * from customers;

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

+----+----------+-----+-----------+----------+

The following program would update the table and increase 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;

/

When the above code is executed at SQL prompt, it produces the following result:

6 customers selected

PL/SQL procedure successfully completed.

If you check the records in customers table, you will find that the rows have been updated:

Select * from customers;

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 |

|  2 | Khilan   |  25 | Delhi     |  2000.00 |

|  3 | kaushik  |  23 | Kota      |  2500.00 |

|  4 | Chaitali |  25 | Mumbai    |  7000.00 |

|  5 | Hardik   |  27 | Bhopal    |  9000.00 |

|  6 | Komal    |  22 | MP        |  5000.00 |

+----+----------+-----+-----------+----------+

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.

The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;

Working with an explicit cursor involves four steps:

·        Declaring the cursor for initializing in the memory

·        Opening the cursor for allocating memory

·        Fetching the cursor for retrieving data

·        Closing the cursor to release allocated memory

Declaring the Cursor

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

CURSOR c_customers IS

   SELECT id, name, address FROM customers;

Opening the Cursor

Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open 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 above-opened cursor as follows:

CLOSE c_customers;

Example:

Following is a complete example to illustrate the concepts of explicit cursors:

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;

/

When the above code is executed at SQL prompt, it produces the following result:

1 Ramesh Ahmedabad

2 Khilan Delhi

3 kaushik Kota   

4 Chaitali Mumbai

5 Hardik Bhopal 

6 Komal MP 

PL/SQL procedure successfully completed.

  PROGRAMS:

create table virus(

name varchar(10),

product varchar(10),

unitprice varchar(10)

);

insert into virus(name,product,unitprice)

values('A',200,20);

insert into virus(name,product,unitprice)

values('B',300,10);

insert into virus(name,product,unitprice)

values('C',100,50);

create table virushistory(

name varchar(10),

product varchar(10),

unitprice varchar(10)

);

create or replace trigger t2

before update of unitprice

on virus

for each row

begin

insert into virushistory

values(:old.name,:old.product,:old.unitprice);

end;

update virus

set unitprice=50

where unitprice=100;

=====================================================================================================================================

create table rome(

name varchar(10),

product varchar(10),

price varchar(10)

);

insert into rome(name,product,price)

values('a',300,200);

insert into rome(name,product,price)

values('b',300,200);

insert into rome(name,product,price)

values('c',300,200);

create or replace trigger t2015

before insert

on rome

for each row

begin

:new.name:=upper(:new.name);

end;

select * from rome

set ServerOutput ON

create or replace trigger t2019

after insert

on rome

for each row

begin

:new.name:=upper(:new.name)

DBMS_OUTPUT.PUT_LINE('CREATED')

end;

=====================================================================================================================================

set ServerOutput ON;

declare

a number;

b number;

c number;

procedure gravity(X number IN,Y number IN,Z number OUT) IS

begin

if X<Y then

Z:=X;

else

Z:=Y;

end if;

end;

begin

a:=25;

b:=30;

gravity(a,b,c)

DBMS_OUTPUT.PUT_LINE('Minimum value is ',||c)

end;

Declare

Begin

Dbms_output.putline("hello world")

END;

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

Declare

a number(10);

b number(10);

c number(10);

begin

a := 2;

b := 3;

c := a+b;

DBMS_OUTPUT.PUT_LINE('c:=a+b');

END;

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

Declare

a number(10);

b number(10);

c number(10);

begin

a := &a;

b := &b;

c := a+b;

DBMS_OUTPUT.PUT_LINE('Sum='||c);

END;

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

Declare

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

 nitin sir mbile number 9501440411

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

a number(10);

b number(10);

c number(10);

begin

a := &a;

b := &b;

c := a+b;

DBMS_OUTPUT.PUT_LINE('Sum='||c);

END;

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

create table abcdef

(

a int,

b int,

c int

)

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

Declare

a number(10);

b number(10);

c number (10);

begin

a :=&a;

b :=&b;

insert into abcdef(a,b,c)

values(a,b,c);

END;

------------------------------------------------------------armitag

select * from abcdef;

declare

n number;

i number;

f number:=1;

begin

n:=&n;

for i in 1..n

loop

f:=f*i;

end loop;

dbms_output.put_line(n||'! = '||f);

end;

EXAMPLE 3:

set ServerOutput ON

declare

begin

DBMS_OUTPUT.PUT_LINE('Hello World');

END;

declare

a number(10);

b number(10);

c number(10);

begin

a:=2;

b:=3;

c:=a+b;

DBMS_OUTPUT.PUT_LINE('Sum='||c);

END;

declare

a number(10);

b number(10);

c number(10);

begin

a:=&a;

b:=&b;

c:=a+b;

DBMS_OUTPUT.PUT_LINE('Sum='||c);

END;

create table abc000(

a int,

b int,

c int

)

declare

a number(10);

b number(10);

c number(10);

begin

a:=&a;

b:=&b;

c:=&c;

insert into abc000(a,b,c)

values(a,b,c);

END;

create table abc0000(

a int,

b int,

c int

)

declare

a number(10);

b number(10);

c number(10);

begin

a:=&a;

b:=&b;

insert into abc0000(a,b,c)

values(a,b,c);

update abc0000

set c=a+b;

DBMS_OUTPUT.PUT_LINE(c);

END;

select * from abc0000;

set ServerOutput ON;

declare

n number;

i number;

counter number;

begin

n:=&n;

i:=1;

counter:=0;

if n=1

then DBMS_OUTPUT.PUT_LINE('1 is a prime No.');

else if n=2

then DBMS_OUTPUT.PUT_LINE('2 is even prime');

else

for i in 1..n loop

if mod(n,i)=0

then counter:=counter+1;

end if;

end loop;

end if;

if counter=2

then

DBMS_OUTPUT.PUT_LINE(n||' is a prime No.');

else

DBMS_OUTPUT.PUT_LINE(n||' is a not prime No.');

end if;

end if;

end;