SQL CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as
if the data were coming from one single table.
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Note: A view always shows up-to-date data! The database engine recreates the data, using
the view's SQL statement, every time a user queries a view.
SQL CREATE VIEW Examples
If you have the Northwind database you can see that it has several views installed by
default.
The view "Current Product List" lists all active products (products that are not discontinued)
from the "Products" table. The view is created with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
We can query the view above as follows:
SELECT * FROM [Current Product List]
Another view in the Northwind sample database selects every product in the "Products"
table with a unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
We can query the view above as follows:
SELECT * FROM [Products Above Average Price]
Another view in the Northwind database calculates the total sale for each category in 1997.
Note that this view selects its data from another view called "Product Sales for 1997":
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
We can query the view above as follows:
SELECT * FROM [Category Sales For 1997]
We can also add a condition to the query. Now we want to see the total sale only for the
category "Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'
SQL Updating a View
You can update a view by using the following syntax:
SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to add the "Category" column to the "Current Product List" view. We will
update the view with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
SQL Dropping a View
You can delete a view with the DROP VIEW command.
SQL DROP VIEW Syntax
DROP VIEW view_name
PL/SQL Block consists of three sections:
The Declaration section (optional).
The Execution section (mandatory).
The Exception (or Error) Handling 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.
Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested
within other PL/SQL blocks. Comments can be used to document code.
How a Sample PL/SQL Block Looks
DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
General Syntax to declare a variable is
variable_name datatype [NOT NULL := value ];
variable_name is the name of the variable.
datatype is a valid PL/SQL datatype.
NOT NULL is an optional specification on the variable.
value or DEFAULT valueis also an optional specification, where you can initialize a variable.
Each variable declaration is a separate statement and must be terminated by a semicolon.
For example, if you want to store the current salary of an employee, you can use a variable.
DECLARE
salary number (6);
Example: The below program will get the salary of an employee with id '1116' and display it on the
screen.
DECLARE
var_salary number(6);
var_emp_id number(6) = 1116;
BEGIN
SELECT salary
INTO var_salary
FROM employee
WHERE emp_id = var_emp_id;
dbms_output.put_line(var_salary);
dbms_output.put_line('The employee '
|| var_emp_id || ' has salary ' || var_salary);
END;
/
General Syntax to declare a constant is:
constant_name CONSTANT datatype := VALUE;
constant_name is the name of the constant i.e. similar to a variable name.
The word CONSTANT is a reserved word and ensures that the value does not change.
VALUE - It is a value which must be assigned to a constant when it is declared. You cannot
assign a value later.
For example, to declare salary_increase, you can write code as follows:
DECLARE
salary_increase CONSTANT number (3) := 10;
You must assign a value to a constant at the time you declare it. If you do not assign a value to
a constant while declaring it and try to assign a value in the execution section, you will get a
error. If you execute the below Pl/SQL block you will get error.
DECLARE
salary_increase CONSTANT number(3);
BEGIN
salary_increase := 100;
dbms_output.put_line (salary_increase);
END;
IF condition 1
THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF;
General Syntax to write a Simple Loop is
:
LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;
These are the important steps to be followed while using Simple Loop.
1) Initialise a variable before the loop body.
2) Increment the variable in the loop.
3) Use a EXIT WHEN statement to exit from the Loop. If you use a EXIT statement without WHEN
condition, the statements in the loop is executed only once.
2) While Loop
A WHILE LOOP is used when a set of statements has to be executed as long as a condition is true. The
condition is evaluated at the beginning of each iteration. The iteration continues until the condition
becomes false.
The General Syntax to write a WHILE LOOP is:
WHILE <condition>
LOOP statements;
END LOOP;
Important steps to follow when executing a while loop:
1) Initialise a variable before the loop body.
2) Increment the variable in the loop.
3) EXIT WHEN statement and EXIT statements can be used in while loops but it's not done oftenly.
3) FOR Loop
A FOR LOOP is used to execute a set of statements for a predetermined number of times. Iteration
occurs between the start and end integer values given. The counter is always incremented by 1. The
loop exits when the counter reachs the value of the end integer.
The General Syntax to write a FOR LOOP is:
FOR counter IN val1..val2
LOOP statements;
END LOOP;
val1 - Start integer value.
val2 - End integer value.
Important steps to follow when executing a while loop:
1) The counter variable is implicitly declared in the declaration section, so it's not necessary to declare
it explicity.
2) The counter variable is incremented by 1 and does not need to be incremented explicitly.
3) EXIT WHEN statement and EXIT statements can be used in FOR loops but it's not done oftenly.
DECLARE
i number(1);
j number(1);
BEGIN
<< outer_loop >>
FOR i IN 1..3 LOOP
<< inner_loop >>
FOR j IN 1..3 LOOP
dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
END loop inner_loop;
END loop outer_loop;
END;
/
Creating a Function
A standalone function is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE
OR REPLACE PROCEDURE statement is as follows:
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
Where,
function-name specifies the name of the function.
[OR REPLACE] option allows modifying an existing function.
The optional parameter list contains name, mode and types of the parameters. IN represents that value will be
passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure.
The function must contain a return statement.
RETURN clause specifies that data type you are going to return from the function.
function-body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone function.
Example:
The following example illustrates creating and calling a standalone function. This function returns the total number of
CUSTOMERS in the customers table. We will use the CUSTOMERS table, which we had created in PL/SQL
Variables chapter:
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 |
+----+----------+-----+-----------+----------+
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;
/
When above code is executed using SQL prompt, it will produce the following result:
Function created.
Calling a Function
While creating a function, you give a definition of what the function has to do. To use a function, you will have to call
that function to perform the defined task. When a program calls a function, program control is transferred to the called
function.
A called function performs defined task and when its return statement is executed or when it last end statement is
reached, it returns program control back to the main program.
To call a function you simply need to pass the required parameters along with function name and if function returns a
value then you can store returned value. Following program calls the function totalCustomers from an anonymous
block:
DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Total no. of Customers: 6
PL/SQL procedure successfully completed.
Example:
The following is one more example which demonstrates Declaring, Defining, and Invoking a Simple PL/SQL Function
that computes and returns the maximum of two values.
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Maximum of (23,45): 45
PL/SQL procedure successfully completed.
Creating a Procedure
A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the
CREATE OR REPLACE PROCEDURE statement is as follows:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
Where,
procedure-name specifies the name of the procedure.
[OR REPLACE] option allows modifying an existing procedure.
The optional parameter list contains name, mode and types of the parameters. IN represents that value will be
passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure.
procedure-body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone procedure.
Example:
The following example creates a simple procedure that displays the string 'Hello World!' on the screen when
executed.
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/
When above code is executed using SQL prompt, it will produce the following result:
Procedure created.
Executing a Standalone Procedure
A standalone procedure can be called in two ways:
Using the EXECUTE keyword
Calling the name of the procedure from a PL/SQL block
The above procedure named 'greetings' can be called with the EXECUTE keyword as:
EXECUTE greetings;
The above call would display:
Hello World
PL/SQL procedure successfully completed.
The procedure can also be called from another PL/SQL block:
BEGIN
greetings;
END;
/
The above call would display:
Hello World
PL/SQL procedure successfully completed.
Deleting a Standalone Procedure
A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for deleting a procedure is:
DROP PROCEDURE procedure-name;
So you can drop greetings procedure by using the following statement:
BEGIN
DROP PROCEDURE greetings;
END;
/
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 trigger display_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 trigger display_salary_changes will be fired and it
will display the following result:
Old salary: 1500
New salary: 2000
Salary difference: 500
