Advance SQL Homework Help
Part4. ADVANCE SQL
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
o What is a database trigger?
o Types of Triggers
o Creating a database trigger
o Dropping a trigger
q Store Procedure
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.
The table on which the view is based is called as base table.
Creating views and using views
CREATE VIEW view-name AS
create view TEACHER_view
select Teacher_Name, Hire_Date,Salary from TEACHER;
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
SET Salary = 8000
Inserting Rows into a View:
The same rules apply for UPDATE and INSERT commands
Deleting Rows into a View:
DELETE FROM tble-VIEW
DELETE FROM TEACHER_view
WHERE Salary = 8000;
You can delete a view with the “DROP VIEW” command.
DROP VIEW view-name
DROP VIEW TEACHER_view;
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.
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.
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 .
drop trigger payments_bu_row;
BEFORE INSERT Trigger
The syntax to create a BEFORE INSERT Trigger.
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>
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.
CREATE [OR REPLACE] FUNCTION name
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
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
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.
The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row.
The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row.
The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row.
The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row.
Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT
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:
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:
Topics in database
- MYSQL Homework Help
- SQL Homework Help
- Advance SQL Homework Help
- SQL Functions Homework Help
- Big Data Homework Help
- Complex Query In Sql Help
- Basic SQL Query Help
- Attributes in Entity Homework Help
- String Related sql Query Help
- Join Related sql Query Help
- Small Tricky sql Query Help
- Authorization: SQL Recursion Help
- Database and data science techniques Help
- Database Languages Assignment Help
- Database Design Help
- Database System Architectures Design
- Entity Relationship Model Understanding
- Higher-Level Design: UML Diagram Help
- Implementation Of Atomicity And Durability
- Object-Based Databases Homework Help
- Oracle 10g/11g
- Parallel And Distributed Databases
- Query Optimization Technique
- Relational Databases Homework Help
- Serializability And Recoverability
- SQL Join
- SQL Queries And Updates
- XML And Relational Algebra Homework Help
- XML Queries And Transformations
- Data Mining
- Oracle Data warehouse
- Relational Model Online Help
- SQL And Advanced SQL Learning Help
Very affordable projects!! And that to submit before deadlines. Thanks for helping me in my database project and raising my grades. I have been able to secure good marks in my internal assessment only because of you. Read More