Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

Oracle

COS Function in Oracle SQL – PLSQL

by

The COS function in Oracle SQL / PLSQL is used to calculate cosine of numeric value where numeric value is provided in radian format.

It’s mathematically equal to COS(N).

Syntax for the COS function in Oracle SQL / PLSQL is:

SELECT COS(radian)
FROM table_name;

Let’s take an example for understanding:
Suppose we want to get the cosine of 30 degree.

SELECT COS(30*22/(7*180))
FROM dual;

The output of the above statement will be:

COS(30*22/(7*180))
0.86592001044743

Notice that we have changed 30 degrees into radians while passing it in COS function.

Oracle PL/SQL Primary Keys

by

Oracle PL/SQL PRIMARY KEY is single or group of column(s)/field(s) that can uniquely identify each record in a table.

An Oracle PL/SQL COMPOSITE PRIMARY KEY is the one which is made up of more than one fields or columns.

Important points about Oracle PL/SQL PRIMARY KEYS are:

  • A table can have one and only one PRIMARY KEY.
  • In Oracle SQL a PRIMARY KEY can be created by using maximum of 32 columns / fields.
  • The columns / fields that are used to define a PRIMARY KEY on table cannot have NULL values.
  • A PRIMARY KEY can be defined using CREATE TABLE or ALTER TABLE Statement.

Oracle PL/SQL PRIMARY KEY using CREATE TABLE Statement Syntax

CREATE TABLE table_name
   (column_name1 datatype NULL/NOT NULL
   ,column_name2 datatype NULL/NOT NULL
   ,column_name3 datatype NULL/NOT NULL
.
column_nameN datatype null/notnull
,CONSTRAINT constraint_name PRIMARY KEY (column_name1, column_name2, . , column_nameN );

Oracle PL/SQL PRIMARY KEY using ALTER TABLE Statement Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name1,column_name2, . ,column_nameN);

Oracle PL/SQL PRIMARY KEY Examples

Oracle PL/SQL PRIMARY KEY – Using CREATE TABLE Statement

Suppose, we wish to create Oracle PL/SQL PRIMARY KEY constraint on ‘employee_id’ column of ‘employee’ table;

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Below CREATE TABLE Statement will define Oracle PL/SQL PRIMARY KEY on ‘employee’ table with “employee_id’ as PRIMARY KEY


CREATE TABLE employee
(employee_id        NUMBER(10)       NOT NULL
,employee_name      VARCHAR2(500)    NOT NULL
,salary             NUMBER(20)       NOT NULL
,department         VARCHAR2(300)    NOT NULL
,commission         NUMBER(20)
,constraint employee_pk PRIMARY KEY (employee_id);

Here with the help of the above SQL CREATE statement we have created a table named ‘employee’ that has 5 columns namely ‘employee_id’, ‘employee_name’, ‘department’, ‘salary’ and ‘commission’.  Also we are having the ‘employee_id’ column as the primary key for the ‘employee’ table.

In the above Oracle PL/SQL PRIMARY KEY example the SQL CREATE TABLE Statement will create a new table named ‘employee’ having a PRIMARY KEY on ‘employee_id’ column.

Let’s insert some data in the ‘employee’ table.


insert into employee
values (101,'Emp A',10000,'Sales',10);

insert into employee
values (102,'Emp B',20000,’IT’,20);

insert into employee
values (103,'Emp C',28000,'IT',20);

insert into employee
values (104,'Emp D',30000,'Support',NULL);

insert into employee
values (105,'Emp E',32000,'Sales',10);

The above SQL INSERT Statements will insert 5 rows in the ‘employee’ table.

Now, if we query the employee table as:

SELECT *
FROM employee;

We will get the following result:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we have successfully created ‘employee’ table with ‘employee_id’ as PRIMARY KEY and also inserted data in ‘employee’ table.

If we try to insert one more record of employee_id = 105 as:

insert into employee
values (105,'Emp F',40000,'Sales',30);

We get an error that unique constraint is getting violated and the data cannot be entered, this is because ‘employee_id’ is the primary key and for employee _id = 105 we already have a record in ‘employee’ table and hence one more record of the same key cannot be entered.

Oracle PL/SQL PRIMARY KEY – Using ALTER TABLE Statement

Suppose we have not created any Oracle PL/SQL PRIMARY KEY on ‘employee’ table earlier .

We can use the SQL ALTER TABLE Statement to define a new PRIMARY KEY on employee table.

For example, the below SQL ALTER TABLE Statement will create PRIMARY KEY on ‘employee_id’ column of employee table.

ALTER TABLE employee
ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);

We can always DROP, DISABLE and ENABLE an Oracle PL/SQL PRIMARY KEY.

SEQUENCE in Oracle SQL – PLSQL

by

In Oracle SQL /PLSQL whenever we want to create an auto-number field we use SEQUENCE. A SEQUENCE is used to generate number sequences in Oracle SQL.
Sequences are very useful whenever we want to create a unique number that can act as a primary key for a table.

Syntax for creation of a SEQUENCE in Oracle SQL / PLSQL is:

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

Let’s understand each line of the above SQL CREATE SEQUENCE command:

MINVALUE: is the minimum value that the sequence can have.
MAXVALUE: is the maximum value up to which the sequence will go. The maximum value allowed is 999999999999999999999999999. If we omit the MAXVALUE portion from the CREATE SEQUENCE command then the MAXVALUE is defaulted to 999999999999999999999999999
START WITH: is the value where the SEQUENCE starts from. The start value must be greater than or equal to MINVALUE.
INCREMENT BY: is the value by which the current value of the sequence is incremented to get the next value of the sequence.
CACHE: Represents the number of values that the sequence will have in cache for better performance.

Let’s take an example for understanding

Suppose have a table named ‘employee’ in the database as shown below, but we do not have any data in the table.

Employee_Id Employee_Name Salary Department Commission

Let’s create a SEQUENCE and the use it to enter data in ‘employee’ table.

Step 1: Creating a SEQUENCE:

CREATE SEQUENCE employee_seq
MINVALUE 100
MAXVALUE 999999999999999999999999999
START WITH 101
INCREMENT BY 1
CACHE 30;

Here with the help of the above SQL CREATE SEQUENCE command we have created a sequence named ‘employee_seq’ that starts with 101 and can go up to 999999999999999999999999999, it will increment with 1 value at a time and caching up to 30 values for performance.

Step 2: Inserting the data in the ‘employee’ table using the ‘employee_seq’ sequence in ‘employee_id’ field.

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp A',10000,'Sales',10);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp B',20000,'IT',20);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp C',28000,'IT',20);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp D',30000,'Support',NULL);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp E',32000,'Sales',10);

The above SQL INSERT statement will insert 5 rows in the ‘employee’ table.
Note the in all the above SQL INSERT statements we have used employee_seq.netxval to get the next value for the employee_id field.

Now, if we query the employee table as:

SELECT *
FROM employee;

We will get the following result:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we have successfully inserted data in ‘employee’ table using sequence ‘employee_seq’ for the ‘employee_id’ field.

VIEWS in Oracle SQL – PLSQL

by

A VIEW in Oracle SQL / PLSQL is like a virtual table, there is no physical existence of a view. A VIEW can be created using a SELECT statement by forming a valid SQL query.

Syntax for creation of a VIEW in Oracle SQL / PLSQL is :

CREATE VIEW view_name
AS
SELECT column(s)
FROM table_name(s)
WHERE condition(s);

Let’s take an example for understanding
Suppose have a table named ‘employee’ in the database as shown below.

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

Creating a VIEW

Suppose we want to create a view so that we can see the data only for those employees who are having ‘salary’ > 25000, we can achieve the same as:

CREATE VIEW employee_view
AS
SELECT *
FROM employee
WHERE salary > 25000;

The above query will create a view named as ‘employee_view’. If we query the ‘employee_view’ as:

SELECT *
FROM employee_view;

We will get the following result:

Employee_Id Employee_Name Salary Department Commission
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we can understand that we have created a view named ‘employee_view’ based on ‘employee’ table which shows us the records of employees having ‘salary’ > 25000 using CREATE VIEW and SELECT statement.

Scenario 2:

Updating an already existing view:

Syntax for updating a view is:

CREATE OR REPLACE VIEW view_name
AS
SELECT column(s)
FROM table(s)
WHERE condition(s);

Suppose we want to update the view named ‘employee_view’ created in the previous scenario so that the ‘employee_view’ can display the records of employees having ‘salary’ < 30000.

The same can be achieved as:

CREATE OR REPLACE VIEW employee_view
AS
SELECT *
FROM employee
WHERE salary < 30000;

The above statement will alter or change the definition of ‘employee_view’ so that it will show record of employees from ‘employee’ table having ‘salary’ < 30000.

If we query the ‘employee_view’ as:

SELECT *
FROM employee_view;

We will get the following result:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20

Here we can see that the view definition for ‘employee_view’ has been updated and the view is now displaying the records of employees having salary < 30000.

Scenario 3:

Deleting or dropping a view

The syntax for deleting or dropping view is

DROP VIEW view_name;

E.g.

DROP VIEW employee_view;

The above statement will delete or drop the definition of ‘employee_view’ from the database.

Important Points about VIEWS in SQL / PLSQL:

  1. The data in the VIEWS can be updated if the user has the privileges for doing the same.
  2. If the data in the VIEW is updated, it updates the data in the tables on which the VIEW  is based upon.
  3. If the table (on which the view is based) is dropped or deleted from the database the definition if the view still remains i.e. the VIEW continues to exist, but if we query the view we will get a message that VIEW has errors.
  4. If we create the base table again the VIEW become alright as it was before the deletion if the base table.

MINUS in Oracle SQL – PLSQL

by

A MINUS in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, a MINUS returns only those rows which are found in the first SELECT statement but are not found in the second SELECT statement.

Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for the MINUS to work.

Syntax for MINUS in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
MINUS
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

Suppose have a table named ‘employee’ in the database as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Also we have one more table ‘comm’ as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s take an example to understand MINUS, suppose we write our SQL query using MINUS as:

SELECT employee_id
FROM employee
MINUS
SELECT emp_id
FROM comm;

The result of the above query will be:

Employee_Id
101

Here we can see that the records for ‘employee_id’ = ‘102’,’103,’104’ and ‘105’ do not appear in the result set because they are present in both (‘employee’ and ‘comm’) tables.

The record for ‘employee_id’ = ‘101’ is present in employee table but is not present in ‘comm’ table and hence is placed in the result.

Suppose we change our SQL query for MINUS as:

SELECT emp_id
FROM comm
MINUS
SELECT employee_id
FROM employee;

The result of the above query will be:

Emp_Id
106
107

Here we can see that the records for ‘employee_id’ = ‘102’,’103,’104’ and ‘105’ do not appear in the result set because they are present in both (‘employee’ and ‘comm’) tables.
The record for ‘employee_id’ = ‘101’ is present in employee table(second table) but is not present in ‘comm’ table (first table)and hence is not placed in the result.