What are the categories in Divisions of PL SQL ?

There are 3 Different Division Categories in PL-SQL

1. Data definition language (DDL): This is used a set of commands that defines data base objects. (Create, alter, drop and Rename).

(i) Create table Command : The Create Table Command defines each column of the table uniquely. Each column has a minimum of three attributes, a name, data type and size (i.e. column width).

Syntax :

Create table <table name> (<column1> <Data Type> [Size],

<column2> <Data Type> [Size],------------------------------<column N> <Data Type> [Size]);

Eg., Create Table Sample ( sno number(3), sname varchar2(10));

(ii) Alter Table :

It is used to change the structure of the Table i.e. adding new column, changing the data type and size. The alter command can have 3 types of sub commands. They are:

1)ADD: By using this command we can add new columns to the existing table.

Syntax:

Alter Table <Table_Name> add (Column1 <data_type>[<size>],

Column2 <data_type>[<size>],- - - - -,Column(n) <data_type>[<size>])

Modify: It is used to change the Data Type and size of the existing columns. If you can change the Data Type and Size you must satisfy the following rules.

By using Modify command in alter we cannot change the column name.

We can not Change the Positions of the existing or new columns

We cannot decrease the length (Size) of an existing column, if that column is having values. But we can increase the size of the existing column even if the data is present

Syntax:

Alter Table <Table_Name> Modify (Column1 <data_type>[<size>],Column2 <data_type>[<size>],- - - - -,Column(n) <data_type>[<size>])

2. Drop This command is introduced in Oracle 8i. It is used to remove the column of a table.

Syntax:

Alter Table <table_name> Drop column <column_name>

Adding single column :

Alter table students Add (total number(4));

Adding Multiple column :

Alter table students Add (average number(6,2),result char(4));

 

 3. Drop Command

This command is used to drop or delete any table from the database.

 

Syntax: Drop table <table_name>

Example: Drop table students;

2.Data Manipulating Language (DML): These are used to view, update, adding record, and removing records (select, update, insert, delete).

(i) Select Statement :

The select statement is used to display the details of a table

Syntax :

Select [ * | Distinct | <column list>] from <table name>[ Where <condition > ][ Group by <column name(s) > ][ Having <Condition> ][ Order by <Expression > ]

Examples :

1. To display all Tables List

select * from tab;

2. To display a particular table details

select * from emp;

3. to display particular columns in a table

select empno, ename, job from emp;

(ii) Update Command :

This command is used to update or modify all or specified column values with new values.

Syntax:

Update < Table Name > set <column1> = <value1>, <column2>=<value2>, -------<column N>=<value N> where <Condition>