Advance SQL Homework Help

Part4. ADVANCE SQL

q Views

o What is a view?

o Creating and using a view

o Updating a View

o Inserting Rows into a View

o Deleting Rows into a View

o Dropping Views

q Trigger

o What is a database trigger?

o Types of Triggers

o Creating a database trigger

o Dropping a trigger

q Store Procedure

q Cursor

q Others

What is Views?

A view is SQL statement and virtual table. It is stored in the database with a relevant name.

View is structure of table that is predefined SQL statement or query. A view can select or contain all row of the table. View created one or many tables which depend on SQL query to create a view.

Example:

The table on which the view is based is called as base table.

Creating views and using views

Syntax:

CREATE VIEW view-name AS
SELECT colm-name(s)
FROM tble-name
WHERE [condition];

For Example:

create view TEACHER_view

as

select Teacher_Name, Hire_Date,Salary from TEACHER;

---------

View created.

Updating a View:

Updating a View under certain conditions:

• The “SELECT” clause
  • Might not contain the keyword DISTINCT,ORDER BY clause, set functions and operators.
• The FROM clause
  • Might not contain multiple tables.
• The WHERE clause
  • Might not contain subqueries.
• The query may not contain GROUP BY or HAVING.

CREATE OR REPLACE VIEW view-name AS
SELECT colm-name(s)
FROM tble-name
WHERE [condition];

For Example:

UPDATE TEACHER_view

SET Salary = 8000

WHERE Teacher_Name='Sini';

Inserting Rows into a View:

The same rules apply for UPDATE and INSERT commands

Deleting Rows into a View:

Syntax:

DELETE FROM tble-VIEW

WHERE [condition];

For Example:

DELETE FROM TEACHER_view

WHERE Salary = 8000;

Dropping Views:

You can delete a view with the “DROP VIEW” command.

Syntax:

DROP VIEW view-name

For Example:

DROP VIEW TEACHER_view;

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

View Delete.

What isTrigger?

Triggers or database trigger is a stored program or PL/SQL block that is automatically executed on an event in the database. The event is related to,

A database manipulation (DML) statement such as DELETE or INSERT or UPDATE.

A database definition (DDL) statement such as CREATE or ALTER or DROP.

A database operation such as SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN.

Triggers might be described about the table or view or schema or database with which the event is associated.

Forms of Triggers

Trigger may be classified as:

q Statement level

q Row level

q Before triggers

q After triggers

Creating a Database Trigger

The following details are to be given at the time of creating a trigger.

q Name of the trigger

q When trigger is to be fired - before or after

q Command - UPDATE, DELETE, or INSERT

q Whether row-level trigger or not

q Condition to filter rows.

q Table to be associated with

q PL/SQL block that is to be executed when trigger is fired.

Syntax:

Figure: Execution sequence of database-triggers.

If FOR EACH ROW option can be used after that this gets the row level trigger or else it's a statement level trigger.

WHEN can be used to fire the actual trigger only if the actual statement is satisfied. This kind of "clause" can be used only with row-triggers.

For example:

The following trigger is fired only when AMOUNT is more than 1000.

create or replace trigger

before insert on payments

for each row

when :new.amount > 1000

Dropping a Trigger

It can be dropped using DROP TRIGGER command .

Syntax:

drop trigger payments_bu_row;

BEFORE INSERT Trigger

The syntax to create a BEFORE INSERT Trigger.

Syntax

What is a stored procedure??

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sp or SP) is actually stored in the database data dictionary.

Store procedure is a name collection of SQL statements and procedural logic i.e., compiled verified and stored in the server database. A stored procedure is typically treated like other database object and controlled through server security mechanism.

The invocation of a stored procedure is treated as a regular external call. The application waits for the stored procedure to terminate, and parameters can be passed back and forth. Stored procedures can be called locally and remotely on a different system. However, stored procedures are particularly useful in a distributed environment since they may considerably improve the performance of distributed applications by reducing the traffic of information across the communication network.

Stored procedures can be used for many different application purposes such as:

Distributing the logic between a client and a server

Performing a sequence of operations at a remote site

Combining results of query functions at a remote site

• Controlling access to database objects
• Performing non-database functions

In SQL we are having different types of stored procedures are there

a) System Stored Procedures

b) User Defined Stored procedures

c) Extended Stored Procedures

System Stored Procedures:

System stored procedures are stored in the master database and these are starts with a sp_ prefix. These procedures can be used to perform variety of tasks to support sql server functions for external application calls in the system tables

Ex: sp_helptext [StoredProcedure_Name]

User Defined Stored Procedures:

User Defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use sp_ prefix because if we use the sp_ prefix first it will check master database then it comes to user defined database

Extended Stored Procedures:

Extended stored procedures are the procedures that call functions from DLL files. Now a day’s extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures.

Stored procedure types:

There are two categories into which stored procedures can be divided:

1) SQL stored procedures

2) External stored procedures

SQL stored procedures

SQL stored procedures are written in the SQL language. This makes it easier to port stored

procedures from other database management systems (DBMS) to the iSeries server and

from the iSeries server to other DBMS. Implementation of the SQL stored procedures is

based on procedural SQL standardized in SQL99.

External stored procedure

An external stored procedure is written by the user in one of the programming languages on

the iSeries server. You can compile the host language programs to create *PGM objects or

Service Program. To create an external stored procedure, the source code for the host

language must be compiled so that a program object is created. Then the CREATE

PROCEDURE statement is used to tell the system where to find the program object that

implements this stored procedure. The stored procedure registered in the following example

returns the name of the supplier with the highest sales in a given month and year.

Store Procedure is mainly used to perform certain task on database. for example

Get DB result sets from some business logic on data.

Execute multiple DB operation at single call.

Used to migrate data from one table to another table.

Can be called for other programming language like java.

Creating a stored procedures

A stored procedure is created using CREATE PROCEDURE command.

OR REPLACE is used to create a procedure even a procedure with the same name is already existing.

To create a stored procedure the syntax is fairly simple:

CREATE PROCEDURE <owner>.<procedure name>

<Param> <datatype>

AS

<Body>

Creating a stored function

A stored function is same as a procedure, except that it returns a value. CREATE FUNCTION command is used to create a stored function.

OR REPLACE is used to create a function even though a function with the same name already exists

Advantages of using stored procedures

1) Stored procedure allows modular programming.

2) Stored Procedure allows faster execution.

3) Stored Procedure can reduce network traffic.

4) Stored procedures provide better security to your data

5) Easy Maintenance

6) Security

What is a cursor?

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.

This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Oracle uses a work area to execute SQL commands and store processing information. PL/SQL allows you to access this area through a name using a cursor.

Cursors that you use in PL/SQL are of two types:

q Implicit cursor

q Explicit cursor

Implicit Cursor

PL/SQL declares an implicit cursor for every DML command, and queries that return a single row. The name of the implicit cursor is SQL. You can directly use this cursor without any declaration.

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.

Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.

For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected.
When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.

The status of the cursor for each of these attributes are defined in the below table.

Explicit Cursor

PL/SQL’s implicit cursor can handle only single-row queries. If you ever need to select more than one row using SELECT in PL/SQL then you have to use explicit cursor.

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;

Working with an explicit cursor involves four steps:

1) Declaring the cursor for initializing in the memory

2) Opening the cursor for allocating memory

3) Fetching the cursor for retrieving data

4) Closing the cursor to release allocated memory

1) 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;

2) 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;

3) 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;

4) Closing the Cursor

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

CLOSE c_customers;