Hicasct.files.wordpress.com
UNIT III
INTRODUCTION TO ORACLE:
ORACLE is a fourth generation relational database management system. In general, a database management system (DBMS) must be able to reliably manage a large amount of data in a multi-user environment so that many users can concurrently access the same data. All this must be accomplished while delivering high performance to the users of the database. A DBMS must also be secure from unauthorized access and provide efficient solutions for failure recovery. The ORACLE Server provides efficient and effective solutions for the major database features.
ORACLE consists of many tools that allow you to create an application with ease and flexibility. You must determine how to implement your requirements using the features available in ORACLE, along with its tools. The features and tools that you choose to use to implement your application can significantly affect the performance of your application.
Several of the more useful features available to ORACLE application developers are integrity constraints, stored procedures and packages, database triggers, cost-based optimizer, shared SQL, locking and sequences.
This documentation will lead you through the main features and tools of ORACLE. It is intended to give you a partial view of what is available to you to use within the assignments.
This documentation will cover:
• ORACLE Architecture - provides a basic understanding of the ``Big Picture'' including the concepts and terminology of the ORACLE Server.
• Starting ORACLE And Other Important Information - provides the knowledge of how to set up your account and other system environment variables. It will also provide information about how ORACLE is currently setup, which you will require, and the steps you must take to report any problems.
• SQL*Plus (Terminal Monitor) - provides a summary of the commands that you will require in order to create tables and manipulate the database.
• SQL*Loader - provides a summary of the commands that you will require to allow you to load data from a file to the database.
• SQL Commands - provides the syntax of some of the SQL Commands in ORACLE to help you get started. This section will only shed light on the Data Definition Language commands and will not contain any information on querying the database (which should be covered in class).
TYPES OF DATABASE:
Database Management Systems
A Database is a collection of records. Database management systems are designed as the means of managing all the records. Database Management is a software system that uses a standard method and running queries with some of them designed for the oversight and proper control of databases.
There are four structural types of database management systems:
• Hierarchical databases.
• Network databases.
• Relational databases.
• Object-oriented databases.
Hierarchical Databases (DBMS)
In the Hierarchical Database Model we have to learn about the databases. It is very fast and simple. In a hierarchical database, records contain information about there groups of parent/child relationships, just like as a tree structure. The structure implies that a record can have also a repeating information. In this structure Data follows a series of records, It is a set of field values attached to it. It collects all records together as a record type. These record types are the equivalent of tables in the relational model, and with the individual records being the equivalent of rows. To create links between these record types, the hierarchical model uses these type Relationships.
[pic] [pic]
Advantage
Hierarchical database can be accessed and updated rapidly because in this model structure is like as a tree and the relationships between records are defined in advance. This feature is a two-edged.
Disadvantage
This type of database structure is that each child in the tree may have only one parent, and relationships or linkages between children are not permitted, even if they make sense from a logical standpoint. Hierarchical databases are so in their design. it can adding a new field or recordrequires that the entire database be redefined.
NetworkDatabase
A network databases are mainly used on a large digital computers. It more connections can be made between different types of data, network databases are considered more efficiency It contains limitations must be considered when we have to use this kind of database. It is Similar to the hierarchical databases, network databases .Network databases are similar to hierarchical databases by also having a hierarchical structure. A network database looks more like a cobweb or interconnected network of records.
In network databases, children are called members and parents are called occupier. The difference between each child or member can have more than one parent.
[pic]
The Approval of the network data model similar with the esteem of the hierarchical data model. Some data were more naturally modeled with more than one parent per child. The network model authorized the modeling of many-to-many relationships in data.
The network model is very similar to the hierarchical model really. Actually the hierarchical model is a subset of the network model. However, instead of using a single-parent tree hierarchy, the network model uses set theory to provide a tree-like hierarchy with the exception that child tables were allowed to have more than one parent. It supports many-to-many relationships.
RelationalDatabases
In relational databases, the relationship between data files is relational. Hierarchical and network databases require the user to pass a hierarchy in order to access needed data. These databases connect to the data in different files by using common data numbers or a key field. Data in relational databases is stored in different access control tables, each having a key field that mainly identifies each row. In the relational databases are more reliable than either the hierarchical or network database structures. In relational databases, tables or files filled up with data are called relations (tuples) designates a row or record, and columns are referred to as attributes or fields.
Relational databases work on each table has a key field that uniquely indicates each row, and that these key fields can be used to connect one table of data to another.
[pic]
The relational database has two major reasons
1. Relational databases can be used with little or no training.
2. Database entries can be modified without specify the entire body.
Properties of Relational Tables
In the relational database we have to follow some properties which are given below.
• It's Values are Atomic
• In Each Row is alone.
• Column Values are of the Same thing.
• Columns is undistinguished.
• Sequence of Rows is Insignificant.
• Each Column has a common Name.
Object-OrientedModel
In this Model we have to discuss the functionality of the object oriented Programming .It takes more than storage of programming language objects. Object DBMS's increase the semantics of the C++ and Java .It provides full-featured database programming capability, while containing native language compatibility. It adds the database functionality to object programming languages.This approach is the analogical of the application and database development into a constant data model and language environment. Applications require less code, use more natural data modeling, and code bases are easier to maintain. Object developers can write complete database applications with a decent amount of additional effort.
The object-oriented database derivation is the integrity of object-oriented programming language systems and consistent systems. The power of the object-oriented databases comes from the cyclical treatment of both consistent data, as found in databases, and transient data, as found in executing programs.
[pic]
Object-oriented databases use small, recyclable separated of software called objects. The objects themselves are stored in the object-oriented database. Each object contains of two elements:
1. Piece of data (e.g., sound, video, text, or graphics).
2. Instructions, or software programs called methods, for what to do with the data.
Disadvantage of Object-oriented databases
1. Object-oriented databases have these disadvantages.
2. Object-oriented database are more expensive to develop.
3. In the Most organizations are unwilling to abandon and convert from those databases.
They have already invested money in developing and implementing.
The benefits to object-oriented databases are compelling. The ability to mix and match reusable objects provides incredible multimedia capability.
RELATIONAL DATABSE PROPERTIES:
• A relational database is a collection of time-varying, normalized relations of assorted degrees.
• The following intiutitve correspondence can be made:
1. A relation is a file
2. Each file contains only one record type
3. The records have no particular order
4. Every field is single-valued
5. The records have a unique identifying field or composite field, called the primary key field.
[pic]
• A relational database consists of a collection of tables.
• All data values are atomic. No repeating groups are allowed.
• A relational database is a pointerless database, User does not see or is made aware of pointers.
[pic]
Relational Database Concepts
• Relation
• Tuple
• Attribute
• Cardinality
• Degree
• Primary key
• Domain
[pic]
Equivalent Database Concepts
• Relation Table
• Tuple Row or record
• Attribute Column or field
• Cardinality Number of rows
• Degree Number of columns
• Primary key Unique identifier
• Domain Pool of legal values
[pic]
DOMAINS
• A domain is a named set of scalar values, all of the same type.
Example 1: Domain of P# is the set of character strings of length 6.
Example 2: Domain of WEIGHT is the set of small integers less than 10,000.
Example 3: Domain of QTY is the set of integers less than one billion.
• Therefore, a "domain" is a "data type".
[pic]
DOMAINS
• Domains (or data types) can be used to impose semantic constraints.
Example 1:
SELECT P.*, SP.*
FROM P, SP
WHERE P.P# = SP.P# ;
The comparison in the conditional clause is sensible.
Example 2:
SELECT P.*, SP.*
FROM P, SP
WHERE P.WEIGHT = SP.QTY ;
The comparision involves two attributes of different types, and therefore should not be allowed.
[pic]
RELATIONS
• A relation R on a collection of domains D1, D2, ..., Dn, consists of two parts: a "heading" and a "body".
• The heading consists of a fixed set of attribute-domain pairs,
{ (A1:D1),(A2:D2), ... , (An,Dn) }
• The heading is also called the schema.
• The body consists of a time-varying set of tuples, where each tuple consists of a set of attribute-value pairs,
{ (A1:vi1), (A2:vi2),..., (An:vin) }
and i = 1, 2, ..., m.
• The body is also called the instance.
[pic]
RELATIONS
• In the above, n is the degree of the relation and m is the cardinality of the relation.
• A relation R can be considered as a variable.
• The heading of a relation is the "type" of the variable R.
• The type of R is (D1,D2,...,Dn).
• The body of a relation is the "value" of the variable R.
• The value of R is a subset of the Cartesian Product of D1, D2, ..., Dn.
[pic]
AN EXAMPLE OF A RELATION
• The heading of R is (S#, SNAME, STATUS, CITY )
• This is a shorthand notation for { (S#:S#), (SNAME:NAME), (STATUS:STATUS), (CITY,CITY) }
• The body of R is a subset of the product of the underlying domains.
• A typical tuple is an element of this product of domains.
( S1, Smith, 20, London )
• This is a shorthand notation for { (S#: S1), (SNAME: SMith), (STATUS: 20), (CITY: London) }
[pic]
CARTESIAN PRODUCT OF DOMAINS
• Suppose the domain of S# is {1,2,3} and the domain of NAME is {Smith, Doe}
• The Cartesian Product of the two domains is denoted by
S# x NAME
• The Cartesian Product is:
{(1,Smith), (1, Doe), (2,Smith), (2,Doe), (3,Smith), (3, Doe) }
• Later we will use the Cartesian Product to explain "equijoin" and "natural join".
[pic]
PROPERTIES OF RELATIONS
• P1: There are no duplicate tuples.
• P2: Tuples are unordered.
• P3: Attributes are unordered.
• P4: All attribute values are atomic.
[pic]
KINDS OF RELATIONS
• Base relations: The real relations. Called "base table" in SQL.
• Views: The virtual relations. A view is a named, derived relation.
• Snapshots: A snapshot is a real, not virtual, named derived relation.
• Query results: The final output relation from a specified query. It may not be named and has no permanent existence.
• Temporary relations: A nonpermanent named derived relation.
SQL: BASIC STUCTURE:
Basic Structure
A relational database is a collection of tables. Each table has its own unique name.
The basic structure of an SQL expression consists of three clauses:
• The select clause which corresponds to the projection operation. It is the list of attributes that will appear in the resulting table.
• The from clause which corresponds to the Cartesian-product operation. It is the list of tables that will be joined in the resulting table.
• The where clause which corresponds to the selection operation. It is the expression that controls the which rows appear in the resulting table.
A typical SQL query has the form of:
select A1, A2, ..., An
from r1, r2, ..., rn,
where P
The query is the equivalent to the relational algebra expression
π A1, A2, ..., An ( σP ( r1 Χ r2 Χ ... Χ rn ) )
The select Clause
Formal query languages are based on the mathematical notion of a relation being a set. Duplicate tuples never appear in relations. In practice, duplicate elimination is relatively time consuming. SQL allows duplicates in relations as well as the results of SQL expressions.
In those cases where we want to force the elimination of duplicates, we insert the keyword distinct after select. The default is to retain duplicates. This can be explicitly required with the keyword all.
The asterisk symbol "*" can be used in place of listing all the attributes.
The clause can also contain arithmetic expressions involving the operators +, -, *, and /.
A dot notation is used when explicitly identifying the table that the attribute comes from: borrower.loan-number
The from Clause
The from clause defines a Cartesian product of the tables in the clause.
The where Clause
SQL uses and, or and not (not symbols) and the comparison operators =, = , and . Also available is between:
where amount between 90000 and 100000
Additional, not between can be used.
SET OPERATIONS:
Set operators are used to join the results of two (or more) SELECT statements.The SET operators available in Oracle are UNION,UNION ALL,INTERSECT,and MINUS.
The UNION set operator returns the combined results of the two SELECT statements.Essentially,it removes duplicates from the results i.e. only one row will be listed for each duplicated result.To counter this behavior,use the UNION ALL set operator which retains the duplicates in the final result.INTERSECT lists only records that are common to both the SELECT queries; the MINUS set operator removes the second query's results from the output if they are also found in the first query's results. INTERSECT and MINUS set operations produce unduplicated results.
All the SET operators share the same degree of precedence among them.Instead,during query execution, Oracle starts evaluation from left to right or from top to bottom.If explicitly parentheses are used, then the order may differ as parentheses would be given priority over dangling operators.
Points to remember
• Same number of columns must be selected by all participating SELECT statements.Column names used in the display are taken from the first query.
• Data types of the column list must be compatible/implicitly convertible by oracle. Oracle will not perform implicit type conversion if corresponding columns in the component queries belong to different data type groups.For example, if a column in the first component query is of data type DATE, and the corresponding column in the second component query is of data type CHAR,Oracle will not perform implicit conversion, but raise ORA-01790 error.
• Positional ordering must be used to sort the result set. Individual result set ordering is not allowed with Set operators. ORDER BY can appear once at the end of the query. For example,
• UNION and INTERSECT operators are commutative, i.e. the order of queries is not important; it doesn't change the final result.
• Performance wise, UNION ALL shows better performance as compared to UNION because resources are not wasted in filtering duplicates and sorting the result set.
• Set operators can be the part of sub queries.
• Set operators can't be used in SELECT statements containing TABLE collection expressions.
• The LONG, BLOB, CLOB, BFILE, VARRAY,or nested table are not permitted for use in Set operators.For update clause is not allowed with the set operators.
UNION
When multiple SELECT queries are joined using UNION operator, Oracle displays the combined result from all the compounded SELECT queries,after removing all duplicates and in sorted order (ascending by default), without ignoring the NULL values.
Consider the below five queries joined using UNION operator.The final combined result set contains value from all the SQLs. Note the duplication removal and sorting of data.
SELECT 1 NUM FROM DUAL
UNION
SELECT 5 FROM DUAL
UNION
SELECT 3 FROM DUAL
UNION
SELECT 6 FROM DUAL
UNION
SELECT 3 FROM DUAL;
NUM
-------
1
3
5
6
To be noted, the columns selected in the SELECT queries must be of compatible data type. Oracle throws an error message when the rule is violated.
SELECT TO_DATE('12-OCT-03') FROM DUAL
UNION
SELECT '13-OCT-03' FROM DUAL;
SELECT TO_DATE('12-OCT-03') FROM DUAL
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
UNION ALL
UNION and UNION ALL are similar in their functioning with a slight difference. But UNION ALL gives the result set without removing duplication and sorting the data. For example,in above query UNION is replaced by UNION ALL to see the effect.
Consider the query demonstrated in UNION section. Note the difference in the output which is generated without sorting and deduplication.
SELECT 1 NUM FROM DUAL
UNION ALL
SELECT 5 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 6 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;
NUM
-------
1
5
3
6
3
INTERSECT
Using INTERSECT operator, Oracle displays the common rows from both the SELECT statements, with no duplicates and data arranged in sorted order (ascending by default).
For example,the below SELECT query retrieves the salary which are common in department 10 and 20.As per ISO SQL Standards, INTERSECT is above others in precedence of evaluation of set operators but this is not still incorporated by Oracle.
SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 10
INTRESECT
SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 20
SALARY
---------
1500
1200
2000
MINUS
Minus operator displays the rows which are present in the first query but absent in the second query, with no duplicates and data arranged in ascending order by default.
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 10
MINUS
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 20;
JOB_ID
-------------
HR
FIN
ADMIN
AGGREGATE FUNCTIONS:
An aggregate function allows you to perform a calculation on a set of values to return a single scalar value. We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.
The following are the most commonly used SQL aggregate functions:
• AVG – calculates the average of a set of values.
• COUNT – counts rows in a specified table or view.
• MIN – gets the minimum value in a set of values.
• MAX – gets the maximum value in a set of values.
• SUM – calculates the sum of values.
Notice that all aggregate functions above ignore NULL values except for the COUNT function.
SQL aggregate functions syntax
To call an aggregate function, you use the following syntax:
|1 |aggregate_function (DISTINCT | ALL expression) |
Let’s examine the syntax above in greater detail:
• First, specify an aggregate function that you want to use e.g., MIN, MAX, AVG, SUM or COUNT.
• Second, put DISTINCT or ALL modifier followed by an expression inside parentheses. If you explicitly use DISTINCT modifier, the aggregate function ignores duplicate values and only consider the unique values. If you use the ALL modifier, the aggregate function uses all values for calculation or evaluation. The ALL modifier is used by default if you do not specify any modifier explicitly.
SQL aggregate function examples
Let’s take a look some examples of using SQL aggregate functions.
COUNT function example
To get the number of the products in the products table, you use the COUNT function as follows:
SELECT COUNT(*) FROM products;
AVG function example
To calculate the average units in stock of the products, you use the AVG function as follows:
|SELECT AVG(unitsinstock) FROM products; |
[pic]
To calculate units in stock by product category, you use the AVG function with the GROUP BY clause as follows:
|SELECT categoryid, AVG(unitsinstock) FROM products GROUP BY categoryid; |
[pic]
SUM function example
To calculate the sum of units in stock by product category, you use the SUM function with the GROUP BY clause as the following query:
|SELECT categoryid, SUM(unitsinstock) FROM products GROUP BY categoryid; |
[pic]
MIN function example
To get the minimum units in stock of products in the products table, you use the MIN function as follows:
|SELECT MIN(unitsinstock) FROM products; |
[pic]
MAX function example
To get the maximum units in stock of products in the products table, you use the MAX function as shown in the following query:
|SELECT MAX(unitsinstock) FROM products; |
[pic]
DATE,NUMERIC AND CHARACTER FUNCTIONS:
Datetime Functions :
Datetime functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.
Some of the datetime functions were designed for the Oracle DATE datatype (ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY). If you provide a timestamp value as their argument, Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all.
The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types.
The datetime functions are:
ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET
Numeric Functions :
Numeric functions accept numeric input and return numeric values. Most numeric functions that return NUMBER values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. The numeric functions are:
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET
Character Functions:
Character Functions Returning Character Values
Character functions that return character values return values of the following datatypes unless otherwise documented:
• If the input argument is CHAR or VARCHAR2, then the value returned is VARCHAR2.
• If the input argument is NCHAR or NVARCHAR2, then the value returned is NVARCHAR2.
The length of the value returned by the function is limited by the maximum length of the datatype returned.
• For functions that return CHAR or VARCHAR2, if the length of the return value exceeds the limit, then Oracle Database truncates it and returns the result without an error message.
• For functions that return CLOB values, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data.
The character functions that return character values are:
CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER
NLS Character Functions
The NLS character functions return information about the character set. The NLS character functions are:
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
Character Functions Returning Number Values
Character functions that return number values can take as their argument any character datatype.
The character functions that return number values are:
ASCII
INSTR
LENGTH
REGEXP_INSTR
NESTED SUB QUERIES:
A subquery can be nested inside other subqueries. SQL has an ability to nest queries within one another. A subquery is a SELECT statement that is nested within another SELECT statement and which return intermediate results. SQL executes innermost subquery first, then next level. See the following examples:
Example -1 : Nested subqueries
If we want to retrieve that unique job_id and there average salary from the employees table which unique job_id have a salary is smaller than (the maximum of averages of min_salary of each unique job_id from the jobs table which job_id are in the list, picking from (the job_history table which is within the department_id 50 and 100)) the following SQL statement can be used :
Sample table: employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id
----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90
101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90
102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60
104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60
105 David Austin DAUSTIN 590.423.4569 6/22/1987
Sample table: jobs
|JOB_ID |JOB_TITLE |MIN_SALARY |MAX_SALARY |
|AD_PRES |President |20000 |40000 |
|AD_VP |Administration Vice President |15000 |30000 |
|AD_ASST |Administration Assistant |3000 |6000 |
|FI_MGR |Finance Manager |8200 |16000 |
|FI_ACCOUNT |Accountant |4200 |9000 |
|AC_MGR |Accounting Manager |8200 |16000 |
|AC_ACCOUNT |Public Accountant |4200 |9000 |
|SA_MAN |Sales Manager |10000 |20000 |
|SA_REP |Sales Representative |6000 |12000 |
|PU_MAN |Purchasing Manager |8000 |15000 |
|PU_CLERK |Purchasing Clerk |2500 |5500 |
|ST_MAN |Stock Manager |5500 |8500 |
|ST_CLERK |Stock Clerk |2000 |5000 |
|SH_CLERK |Shipping Clerk |2500 |5500 |
|IT_PROG |Programmer |4000 |10000 |
|MK_MAN |Marketing Manager |9000 |15000 |
|MK_REP |Marketing Representative |4000 |9000 |
|HR_REP |Human Resources Representative |4000 |9000 |
|PR_REP |Public Relations Representative |4500 |10500 |
SQL Code:
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary)<
(SELECT MAX(AVG(min_salary))
FROM jobs
WHERE job_id IN
(SELECT job_id FROM job_history
WHERE department_id
BETWEEN 50 AND 100)
GROUP BY job_id);
or
SELECT job_id,AVG(salary)
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary)<
(SELECT MAX(myavg) from (select job_id,AVG(min_salary) as myavg
FROM jobs
WHERE job_id IN
(SELECT job_id FROM job_history
WHERE department_id
BETWEEN 50 AND 100)
GROUP BY job_id) ss);
Output:
JOB_ID AVG(SALARY)
---------- -----------
IT_PROG 5760
AC_ACCOUNT 8300
ST_MAN 7280
AD_ASST 4400
SH_CLERK 3215
FI_ACCOUNT 7920
PU_CLERK 2780
SA_REP 8350
MK_REP 6000
ST_CLERK 2785
HR_REP 6500
Explanation:
This example contains three queries: a nested subquery, a subquery, and the outer query. These parts of queries are runs in that order.
Atfirst the nested subquery as follows:
SQL Code:
SELECT job_id FROM job_history
WHERE department_id
BETWEEN 50 AND 100;
Copy
This nested subquery retrieves the job_id(s) from job_history table which is within the department_id 50 and 100.
Output:
JOB_ID
----------
ST_CLERK
ST_CLERK
IT_PROG
SA_REP
SA_MAN
AD_ASST
AC_ACCOUNT
Here is the pictorial representation of how the above output comes.
[pic]
Now the subquery that receives output from the nested subquery stated above.
SELECT MAX(AVG(min_salary))
FROM jobs WHERE job_id
IN(.....output from the nested subquery......)
GROUP BY job_id
The subquery internally works as follows:
SQL Code:
SELECT MAX(AVG(min_salary))
FROM jobs
WHERE job_id
IN(
'ST_CLERK','ST_CLERK','IT_PROG',
'SA_REP','SA_MAN','AD_ASST', '
AC_ACCOUNT')
GROUP BY job_id;
Copy
The subquery returns the maximum of averages of min_salary for each unique job_id return ( i.e. 'ST_CLERK','ST_CLERK','IT_PROG', 'SA_REP','SA_MAN','AD_ASST', 'AC_ACCOUNT' ) by the previous subquery.
Here is the output:
Output:
MAX(AVG(MIN_SALARY))
--------------------
10000
Here is the pictorial representation of how the above output returns.
[pic][pic]
Now the outer query that receives output from the subquery and which also receives the output from the nested subquery stated above.
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary)<
(.....output from the subquery(
output from the nested subquery)......)
The outer query internally works as follows:
SQL Code:
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) |pno |
|descr | |descr |
|color | |color |
| | | |
|P1 | |P1 |
|Widget | |Widget |
|Blue | |Blue |
| | | |
|P2 | |P2 |
|Widget | |Widget |
|Red | |Red |
| | | |
|P3 | |P3 |
|Dongle | |Dongle |
|Green | |Green |
| | | |
| | |P4 |
| | |NULL |
| | |Brown |
| | | |
INSERT INTO sp
SELECT s.sno, p.pno, 500
FROM s, p
WHERE p.color='Green' AND s.city='London'
|Before | |After |
|sno |=> |sno |
|pno | |pno |
|qty | |qty |
| | | |
|S1 | |S1 |
|P1 | |P1 |
|NULL | |NULL |
| | | |
|S2 | |S2 |
|P1 | |P1 |
|200 | |200 |
| | | |
|S3 | |S3 |
|P1 | |P1 |
|1000 | |1000 |
| | | |
|S3 | |S3 |
|P2 | |P2 |
|200 | |200 |
| | | |
| | |S2 |
| | |P3 |
| | |500 |
| | | |
UPDATE Statement
The UPDATE statement modifies columns in selected table rows. It has the following general format:
UPDATE table-1 SET set-list [WHERE predicate]
The optional WHERE Clause has the same format as in the SELECT Statement. See WHERE Clause. The WHERE clause chooses which table rows to update. If it is missing, all rows are in table-1 are updated.
The set-list contains assignments of new values for selected columns. See SET Clause.
The SET Clause expressions and WHERE Clause predicate can contain subqueries, but the subqueries cannot reference table-1. This prevents situations where results are dependent on the order of processing.
SET Clause
The SET Clause in the UPDATE Statement updates (assigns new value to) columns in the selected table rows. It has the following general format:
SET column-1 = value-1 [, column-2 = value-2] ...
column-1 and column-2 are columns in the Update table. value-1 and value-2 are expressions that can reference columns from the update table. They also can be the keyword -- NULL, to set the column to null.
Since the assignment expressions can reference columns from the current row, the expressions are evaluated first. After the values of all Set expressions have been computed, they are then assigned to the referenced columns. This avoids results dependent on the order of processing.
UPDATE Examples
UPDATE sp SET qty = qty + 20
|Before | |After |
|sno |=> |sno |
|pno | |pno |
|qty | |qty |
| | | |
|S1 | |S1 |
|P1 | |P1 |
|NULL | |NULL |
| | | |
|S2 | |S2 |
|P1 | |P1 |
|200 | |220 |
| | | |
|S3 | |S3 |
|P1 | |P1 |
|1000 | |1020 |
| | | |
|S3 | |S3 |
|P2 | |P2 |
|200 | |220 |
| | | |
UPDATE s
SET name = 'Tony', city = 'Milan'
WHERE sno = 'S3'
|Before | |After |
|sno |=> |sno |
|name | |name |
|city | |city |
| | | |
|S1 | |S1 |
|Pierre | |Pierre |
|Paris | |Paris |
| | | |
|S2 | |S2 |
|John | |John |
|London | |London |
| | | |
|S3 | |S3 |
|Mario | |Tony |
|Rome | |Milan |
| | | |
DELETE Statement
The DELETE Statement removes selected rows from a table. It has the following general format:
DELETE FROM table-1 [WHERE predicate]
The optional WHERE Clause has the same format as in the SELECT Statement. See WHERE Clause. The WHERE clause chooses which table rows to delete. If it is missing, all rows are in table-1 are removed.
The WHERE Clause predicate can contain subqueries, but the subqueries cannot reference table-1. This prevents situations where results are dependent on the order of processing.
DELETE Examples
DELETE FROM sp WHERE pno = 'P1'
|Before | |After |
|sno |=> |sno |
|pno | |pno |
|qty | |qty |
| | | |
|S1 | |S3 |
|P1 | |P2 |
|NULL | |200 |
| | | |
|S2 | | |
|P1 | | |
|200 | | |
| | | |
|S3 | | |
|P1 | | |
|1000 | | |
| | | |
|S3 | | |
|P2 | | |
|200 | | |
| | | |
DELETE FROM p WHERE pno NOT IN (SELECT pno FROM sp)
|Before | |After |
|pno |=> |pno |
|descr | |descr |
|color | |color |
| | | |
|P1 | |P1 |
|Widget | |Widget |
|Blue | |Blue |
| | | |
|P2 | |P2 |
|Widget | |Widget |
|Red | |Red |
| | | |
|P3 | | |
|Dongle | | |
|Green | | |
| | | |
JOINED RELATIONS:
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Let's look at a selection from the "Orders" table:
|OrderID |CustomerID |OrderDate |
|10308 | 2 | 1996-09-18 |
|10309 | 37 | 1996-09-19 |
|10310 | 77 | 1996-09-20 |
|CustomerID | CustomerName |ContactName |Country |
|1 |Alfreds Futterkiste |Maria Anders |Germany |
|2 |Ana Trujillo Emparedados y helados |Ana Trujillo |Mexico |
|3 |Antonio Moreno Taquería |Antonio Moreno |Mexico |
Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.
Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
|OrderID |CustomerName |OrderDate |
|10308 |Ana Trujillo Emparedados y helados |9/18/1996 |
|10365 |Antonio Moreno Taquería |11/27/1996 |
|10383 |Around the Horn |12/16/1996 |
|10355 |Around the Horn |11/15/1996 |
|10278 |Berglunds snabbköp |8/12/1996 |
[pic]
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
[pic][pic][pic][pic]
SQL INNER JOIN
The INNER JOIN selects records that have matching values in both tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
[pic]
[pic]
ORDER TABLE:
|OrderID |CustomerID |EmployeeID |OrderDate |ShipperID |
|10308 | 2 | 7 | 1996-09-18 | 3 |
|10309 | 37 | 3 | 1996-09-19 | 1 |
|10310 | 77 | 8 | 1996-09-20 | 2 |
|CUSTOMER TABLE: | | | | | | |
|CustomerID |CustomerName |ContactName |Address |City |PostalCode |Country |
|1 |Alfreds Futterkiste |Maria Anders |Obere Str. 57 |Berlin |12209 |Germany |
|2 |Ana Trujillo Emparedados y helados |Ana Trujillo |Avda. de la |México |05021 |Mexico |
| | | |Constitución 2222 |D.F. | | |
|3 |Antonio Moreno Taquería |Antonio Moreno |Mataderos 2312 |México |05023 |Mexico |
| | | | |D.F. | | |
SQL INNER JOIN Example
The following SQL statement selects all orders with customer information:
Example
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Note: The INNER JOIN selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!
[pic]
JOIN Three Tables
The following SQL statement selects all orders with customer and shipper information:
Example
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
SQL LEFT JOIN
The LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
[pic]
Customers table:
|CustomerID |CustomerName |ContactName |Address |City |PostalCode |Country |
|1 |Alfreds Futterkiste |Maria Anders |Obere Str. 57 |Berlin |12209 |Germany |
|2 |Ana Trujillo Emparedados y |Ana Trujillo |Avda. de la Constitución|México D.F. |05021 |Mexico |
| |helados | |2222 | | | |
|3 |Antonio Moreno Taquería |Antonio Moreno |Mataderos 2312 |México D.F. |05023 |Mexico |
Orders table:
|OrderID |CustomerID |EmployeeID |OrderDate |ShipperID |
|10308 |2 |7 |1996-09-18 |3 |
|10309 |37 |3 |1996-09-19 |1 |
|10310 |77 |8 |1996-09-20 |2 |
[pic]
SQL LEFT JOIN Example
The following SQL statement will select all customers, and any orders they might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).
SQL RIGHT JOIN
The RIGHT JOIN returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
[pic]
Below is a selection from the "Orders" table:
|OrderID |CustomerID |EmployeeID |OrderDate |ShipperID |
|10308 |2 |7 |1996-09-18 |3 |
|10309 |37 |3 |1996-09-19 |1 |
|10310 |77 |8 |1996-09-20 |2 |
And a selection from the "Employees" table:
|EmployeeID |LastName |FirstName |BirthDate |Photo |
|1 |Davolio |Nancy |12/8/1968 |EmpID1.pic |
|2 |Fuller |Andrew |2/19/1952 |EmpID2.pic |
|3 |Leverling |Janet |8/30/1963 |EmpID3.pic |
SQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they might have have placed:
Example
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Note: The RIGHT JOIN returns all records from the right table (Employees), even if there are no matches in the left table (Orders).
SQL FULL OUTER JOIN
The FULL OUTER JOIN return all records when there is a match in either left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!
FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
[pic]
Below is a selection from the "Customers" table:
|CustomerID |CustomerName |ContactName | Address |City |PostalCode |Country |
|1 |Alfreds Futterkiste |Maria Anders |Obere Str. 57 |Berlin |12209 |Germany |
|2 |Ana Trujillo Emparedados y |Ana Trujillo |Avda. de la |México D.F. |05021 |Mexico |
| |helados | |Constitución 2222 | | | |
|3 |Antonio Moreno Taquería |Antonio Moreno |Mataderos 2312 |México D.F. |05023 |Mexico |
And a selection from the "Orders" table:
|OrderID |CustomerID |EmployeeID |OrderDate |ShipperID |
|10308 | 2 | 7 |1996-09-18 | 3 |
|10309 | 37 | 3 |1996-09-19 | 1 |
|10310 | 77 | 8 |1996-09-20 | 2 |
SQL FULL OUTER JOIN Example
The following SQL statement selects all customers, and all orders:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
A selection from the result set may look like this:
|CustomerName |OrderID |
|Alfreds Futterkiste | |
|Ana Trujillo Emparedados y helados |10308 |
|Antonio Moreno Taquería |10365 |
| |10382 |
| |10351 |
Note: The FULL OUTER JOIN returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.
SQL Self JOIN
A self JOIN is a regular join, but the table is joined with itself.
Self JOIN Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
[pic]
SQL Self JOIN
A self JOIN is a regular join, but the table is joined with itself.
Self JOIN Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
[pic]
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
|CustomerID |CustomerName |ContactName |Address |City |PostalCode |Country |
|1 |Alfreds Futterkiste |Maria Anders |Obere Str. 57 |Berlin |12209 |Germany |
|2 |Ana Trujillo Emparedados y |Ana Trujillo |Avda. de la |México D.F. |05021 |Mexico |
| |helados | |Constitución 2222 | | | |
|3 |Antonio Moreno Taquería |Antonio Moreno |Mataderos 2312 |México D.F. |05023 |Mexico |
[pic]
SQL Self JOIN Example
The following SQL statement matches customers that are from the same city:
Example
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID B.CustomerID
AND A.City = B.City
ORDER BY A.City;
DDL(DATA DEFINITION LANGUAGE):
DDL refers to "Data Definition Language", a subset of SQL statements that change the structure of the database schema in some way, typically by creating, deleting, or modifying schema objects such as databases, tables, and views.
data definition language (DDL) statements enable you to perform these tasks:
• Create, alter, and drop schema objects
• Grant and revoke privileges and roles
• Analyze information on a table, index, or cluster
• Establish auditing options
• Add comments to the data dictionary
The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.
The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
Oracle implicitly commits the current transaction before and after every DDL statement.
Many DDL statements may cause Oracle to recompile or reauthorize schema objects.
DDL Statements are
CREATE :Use to create objects like CREATE TABLE, CREATE FUNCTION,
CREATE SYNONYM, CREATE VIEW. Etc.
ALTER :Use to Alter Objects like ALTER TABLE, ALTER USER, ALTER
TABLESPACE, ALTER DATABASE. Etc.
DROP :Use to Drop Objects like DROP TABLE, DROP USER, DROP
TABLESPACE, DROP FUNCTION. Etc.
REPLACE :Use to Rename table names.
TRUNCATE :Use to truncate (delete all rows) a table.
Create
To create tables, views, synonyms, sequences, functions, procedures, packages etc.
Example
To create a table, you can give the following statement
create table emp (empno number(5) primary key,
name varchar2(20),
sal number(10,2),
job varchar2(20),
mgr number(5),
Hiredate date,
comm number(10,2));
Now Suppose you have emp table now you want to create a TAX table with the following structure and also insert rows of those employees whose salary is above 5000.
|Tax |
|Empno |Number(5) |
|Tax |Number(10,2) |
To do this we can first create TAX table by defining column names and datatypes and then use INSERT into EMP SELECT …. statement to insert rows from emp table. like given below.
create table tax (empno number(5), tax number(10,2));
insert into tax select empno,(sal-5000)*0.40
from emp where sal > 5000;
Instead of executing the above two statements the same result can be achieved by giving a single CREATE TABLE AS statement.
create table tax as select empno,(sal-5000)*0.4
as tax from emp where sal>5000
You can also use CREATE TABLE AS statement to create copies of tables. Like to create a copy EMP table as EMP2 you can give the following statement.
create table emp2 as select * from emp;
To copy tables without rows i.e. to just copy the structure give the following statement
create table emp2 as select * from emp where 1=2;
Temporary Tables (From Oracle Ver. 8i)
It is also possible to create a temporary table. The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table. You use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT keywords indicate if the data in the table is transaction-specific (the default) or session-specific:
• ON COMMIT DELETE ROWS specifies that the temporary table is transaction specific and Oracle truncates the table (delete all rows) after each commit.
• ON COMMIT PRESERVE ROWS specifies that the temporary table is session specific and Oracle truncates the table when you terminate the session.
This example creates a temporary table that is transaction specific:
CREATE GLOBAL TEMPORARY TABLE taxable_emp
(empno number(5),
ename varchar2(20),
sal number(10,2),
tax number(10,2))
ON COMMIT DELETE ROWS;
Indexes can also be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.
Alter
Use the ALTER TABLE statement to alter the structure of a table.
Examples:
To add new columns addr, city, pin, ph, fax to employee table you can give the following statement
alter table emp add (addr varchar2(20), city varchar2(20),
pin varchar2(10),ph varchar2(20));
To modify the datatype and width of a column. For example we you want to increase the length of the column ename from varchar2(20) to varchar2(30) then give the following command.
alter table emp modify (ename varchar2(30))
To decrease the width of a column the column can be decreased up to largest value it holds.
alter table emp modify (ename varchar2(15));
The above is possible only if you are using Oracle ver 8i and above. In Oracle 8.0 and 7.3 you cannot decrease the column width directly unless the column is empty.
To change the datatype the column must be empty in All Oracle Versions.
To drop columns.
From Oracle Ver. 8i you can drop columns directly it was not possible in previous versions.
For example to drop PIN, CITY columns from emp table.
alter table emp drop column (pin, city);
Remember you cannot drop the column if the table is having only one column.
If the column you want to drop is having primary key constraint on it then you have to give cascade constraint clause.
alter table emp2 drop column (empno) cascade constraints;
To drop columns in previous versions of Oracle8.0 and 7.3. and to change the column name in all Oracle versions do the following.
For example we want to drop pin and city columns and to change SAL column name to SALARY.
Step 1: Create a temporary table with desired columns using subquery.
create table temp as select empno, ename,
sal AS salary, addr, ph from emp;
Step 2: Drop the original table.
drop table emp;
Step 3: Rename the temporary table to the original table.
rename temp to emp;
Rename
Use the RENAME statement to rename a table, view, sequence, or private synonym for a table, view, or sequence.
• Oracle automatically transfers integrity constraints, indexes, and grants on the old object to the new object.
• Oracle invalidates all objects that depend on the renamed object, such as views, synonyms, and stored procedures and functions that refer to a renamed table.
Example
To rename table emp2 to employee2 you can give the following command.
rename emp2 to employee2
Drop
Use the drop statement to drop tables, functions, procedures, packages, views, synonym, sequences, tablespaces etc.
Example
The following command drops table emp2
drop table emp2;
If emp2 table is having primary key constraint, to which other tables refer to, then you have to first drop referential integrity constraint and then drop the table. Or if you want to drop table by dropping the referential constraints then give the following command
drop table emp2 cascade constraints;
Truncate
Use the Truncate statement to delete all the rows from table permanently . It is same as “DELETE FROM ” except
• Truncate does not generate any rollback data hence, it cannot be roll backed.
• If any delete triggers are defined on the table. Then the triggers are not fired
• It deallocates free extents from the table. So that the free space can be use by other tables.
Example
truncate table emp;
If you do not want free space and keep it with the table. Then specify the REUSE storage clause like this
truncate table emp reuse storage;
EMBEDDED SQL
Like we said above, embedded SQL is the one which combines the high level language with the DB language like SQL. It allows the application languages to communicate with DB and get requested result. The high level languages which supports embedding SQLs within it are also known as host language. There are different host languages which support embedding SQL within it like C, C++, ADA, Pascal, FORTRAN, Java etc. When SQL is embedded within C or C++, then it is known as Pro*C/C++ or simply Pro*C language. Pro*C is the most commonly used embedded SQL. Let us discuss below embedded SQL with respect to C language.
When SQL is embedded within C language, the compiler processes the compilation in two steps. It first extracts all the SQL codes from the program and the pre-compiler will compile the SQL code for its syntax, correctness, execution path etc. Once pre-compilation is done, these executable codes are embedded into the C code. Then the C compiler will compile the code and execute the code. Thus the compilation takes place in two steps – one for SQL and one for application language. Hence these types of compilation require all the query, data value etc to be known at the compilation time itself to generate the executable code. Otherwise C or any high level language cannot compile the code. Hence the SQL codes written are static and these embedded SQL is also known as static SQL. Thus SQLs know how to access DB, which queries to execute, which values to be inserted/ deleted/updated etc.
When SQL is embedded in C programming language, the C compiler will not understand which the syntax of C is and which syntax of SQL is. It needs to be clearly differentiated and compiler should know which is C and SQL. This is very important as pre-compiler will first extract all the SQLs embedded in it to compile it at DB level. Then it will be embedded in the C code which will be compiled by the C compiler to get executable code. All the embedded SQLs are preceded by ‘EXEC SQL’ and ends in semicolon (;). We can have these SQLs placed anywhere in the C code, provided it is placed in the correct order- declaration, execution and end. Let us see below how C code is differentiated from SQL code.
Structure of Embedded SQL
Structure of embedded SQL defines step by step process of establishing a connection with DB and executing the code in the DB within the high level language.
Connection to DB
This is the first step while writing a query in high level languages. First connection to the DB that we are accessing needs to be established. This can be done using the keyword CONNECT. But it has to precede with ‘EXEC SQL’ to indicate that it is a SQL statement.
EXEC SQL CONNECT db_name;
EXEC SQL CONNECT HR_USER; //connects to DB HR_USER
Declaration Section
Once connection is established with DB, we can perform DB transactions. Since these DB transactions are dependent on the values and variables of the host language. Depending on their values, query will be written and executed. Similarly, results of DB query will be returned to the host language which will be captured by the variables of host language. Hence we need to declare the variables to pass the value to the query and get the values from query. There are two types of variables used in the host language.
• Host variable : These are the variables of host language used to pass the value to the query as well as to capture the values returned by the query. Since SQL is dependent on host language we have to use variables of host language and such variables are known as host variable. But these host variables should be declared within the SQL area or within SQL code. That means compiler should be able to differentiate it from normal C variables. Hence we have to declare host variables within BEGIN DECLARE and END DECLARE section. Again, these declare block should be enclosed within EXEC SQL and ‘;’.
EXEC SQL BEGIN DECLARE SECTION;
int STD_ID;
char STD_NAME [15];
char ADDRESS[20];
EXEC SQL END DECLARE SECTION;
We can note here that variables are written inside begin and end block of the SQL, but they are declared using C code. It does not use SQL code to declare the variables. Why? This is because they are host variables – variables of C language. Hence we cannot use SQL syntax to declare them. Host language supports almost all the datatypes from int, char, long, float, double, pointer, array, string, structures etc.
When host variables are used in a SQL query, it should be preceded by colon – ‘:’ to indicate that it is a host variable. Hence when pre-compiler compiles SQL code, it substitutes the value of host variable and compiles.
EXEC SQL SELECT * FROM STUDENT WHERE STUDENT_ID =:STD_ID;
In above code, :STD_ID will be replaced by its value when pre-compiler compiles it.
Suppose we do not know what should be the datatype of host variables or what is the datatype in oracle for few of the columns. In such case we can allow the compiler to fetch the datatype of column and assign it to the host variable. It is done using ‘BASED ON’ clause. But format of declaration will be in host language.
EXEC SQL BEGIN DECLARE SECTION;
BASED ON STUDENT.STD_ID sid;
BASED ON STUDENT.STD_NAME sname;
BASED ON STUDENT.ADDRESS saddress;
EXEC SQL END DECLARE SECTION;
• Indicator Variable : These variables are also host variables but are of 2 byte short type always. These variables are used to capture the NULL values that a query returns or to INSERT/ UPDATE any NULL values to the tables. When it is used in a SELECT query, it captures any NULL value returned for any column. When used along with INSERT or UPDATE, it sets the column value as NULL, even though the host variable has value. If we have to capture the NULL values for each host variable in the code, then we have to declare indicator variables to each of the host variables. These indicator variables are placed immediately after the host variable in a query or separated by INDICATOR between host and indicator variable.
EXEC SQL SELECT STD_NAME INTO :SNAME :IND_SNAME
FROM STUDENT WHERE STUDENT_ID =:STD_ID;
Or
EXEC SQL SELECT STD_NAME INTO :SNAME INDICATOR :IND_SNAME
FROM STUDENT WHERE STUDENT_ID =:STD_ID;
INSERT INTO STUDENT (STD_ID, STD_NAME)
VALUES (:SID, :SNAME INDICATOR :IND_SNAME); --Sets NULL to STD_NAME
UPDATE STUDENT
SET ADDRESS = :STD_ADDR :IND_SADDR; --Sets NULL to ADDRESS
Though indicator variable sets/gets NULL values to the column, it passes/ gets different integer values. When SELECT query is executed, it gets 4 different integer values listed below :
[pic]
When insert / update statement is executed along with indicator variable, then it can pass two values to indicate to assign or not to assign NULL values.
[pic]
Execution Section
This is the execution section, and it contains all the SQL queries and statements prefixed by ‘EXEC SQL’.
EXEC SQL SELECT * FROM STUDENT WHERE STUDENT_ID =:STD_ID;
EXEC SQL SELECT STD_NAME INTO :SNAME :IND_SNAME
FROM STUDENT WHERE STUDENT_ID =:STD_ID;
INSERT INTO STUDENT (STD_ID, STD_NAME)
VALUES (:SID, :SNAME);
UPDATE STUDENT
SET ADDRESS = :STD_ADDR
WHERE STD_ID = :SID;
Above examples show simple SQL queries/statements. But we can have complex queries too.
In this embedded SQL, all the queries are dependent on the values of host variable and queries are static. That means, in above example of SELECT query, it always pulls student details for the student Id inserted. But suppose user enters student name instead of student ID. Then these SQLs are not flexible to modify the query to fetch details based on name. Suppose query is based on name and address of a student. Then code will not modify the query to fetch details based on name and address of a student. That means queries are static and it cannot be modified based on user input. Hence this kind of SQLs is known as static SQLs.
Error Handling
Like any other programming language, in embedded SQL also we need to handle errors. Error handling method would be based on the host language. Here we are using C language and we use labeling method, i.e.; when error occurs we stop the current sequence of execution and ask the compiler to jump to error handling section of the code to continue. In order to handle error, C programs require separate error handling structure which holds different variables to capture different set of errors. This structure is known as SQL Communication Area or SQLCA. Below is the structure of SQLCA.
struct sqlca {
/* ub1 */ char sqlcaid [8];
/* b4 */ long sqlabc;
/* b4 */ long sqlcode;
struct {
/* ub2 */ unsigned short sqlerrml;
/* ub1 */ char sqlerrmc[70];
} sqlerrm;
….
long sqlcode; //returns the error code
…
char sqlstate [6]; //returns predefined error statements
….
}
If we have to use this error handling structure, then we have to include sqlca.h header file in the program, using #include directives. In this structure mainly SQLCODE and SQLSTATE are used to see the type of error. SQLCODE returns different values for different types of errors.
[pic]
Whenever error occurs in the code, then we have to redirect the execution of code to handle the error rather than executing further. This is done using WHENEVER statement.
EXEC SQL WHENEVER condition action;
The condition in WHENEVER clause can be
• SQLWARNING – indicates SQL warning. It indicates the compiler that when SQL warning occurs perform action.
• SQLERROR – indicates SQL Error. The SQLCODE will have negative value.
• NOT FOUND - SQLCODE will have positive value indicating no records are fetched.
On receiving error or warning, action can be any one of the following:
• CONTINUE – indicates to continue with the normal execution of the code.
• DO – it calls a function and hence program will move to execute this error handling function.
• GOTO - Program will jump to the location to execute error handling.
• STOP – it immediately stops the execution of the program by calling exit (0) and all the incomplete transactions will be rolled back.
EXEC SQL WHENEVER SQLWARNING DO display_warning();
EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER NOT FOUND GOTO lbl_no_records;
Whenever we use ‘WHENEVER’ clause, first statement should be ‘EXEC SQL INCLUDE SQLCA;’ in the code. This is to indicate compiler that error handling needs to be done for the following code.
Consider a simple Pro*C program to illustrate embedded SQL. This program below accepts student name from the user and queries DB for his student id.
#include
#include
int main(){
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
BASED ON STUDENT.STD_ID SID; // host variable to store the value returned by query
char *STD_NAME; // host variable to pass the value to the query
short ind_sid;// indicator variable
EXEC SQL END DECLARE SECTION;
//Error handling
EXEC WHENEVER NOT FOUND GOTO error_msg1;
EXEC WHENEVER SQLERROR GOTO error_msg2;
printf("Enter the Student name:");
scanf("%s", STD_Name);
// Executes the query
EXEC SQL SELECT STD_ID INTO : SID INDICATOR ind_sid FROM STUDENT WHERE STD_NAME = : STD_NAME;
printf("STUDENT ID:%d", STD_ID); // prints the result from DB
exit(0);
// Error handling labels
error_msg1:
printf("Student Id %d is not found", STD_ID);
printf("ERROR:%ld", sqlca->sqlcode);
printf("ERROR State:%s", sqlca->sqlstate);
exit(0);
error_msg2:
printf("Error has occurred!");
printf("ERROR:%ld", sqlca->sqlcode);
printf("ERROR State:%s", sqlca->sqlstate);
exit(0);
}
UNIT IV
Introduction to Relational Database Design
Many people believe Access is such a simple product to use that database design is something they don't need to worry about. I couldn't disagree more! Just as a house without a foundation will fall over, a database with poorly designed tables and relationships will fail to meet the needs of its users.
The History of Relational Database Design
Dr. E. F. Codd first introduced formal relational database design in 1969 while he was at IBM. Relational theory, which is based on set theory, applies to both databases and database applications. Codd developed 12 rules that determine how well an application and its data adhere to the relational model. Since Codd first conceived these 12 rules, the number of rules has expanded into the hundreds. (Don't worry; you need to learn only a few of them!)
You should be happy to learn that, although not perfect as an application development environment, Microsoft Access measures up quite well as a relational database system.
Goals of Relational Database Design
The number one goal of relational database design is to, as closely as possible, develop a database that models some real-world system. This involves breaking the real-world system into tables and fields and determining how the tables relate to each other. Although on the surface this task might appear to be trivial, it can be an extremely cumbersome process to translate a real-world system into tables and fields.
A properly designed database has many benefits. The processes of adding, editing, deleting, and retrieving table data are greatly facilitated by a properly designed database. In addition, reports are easier to build. Most importantly, the database becomes easy to modify and maintain.
Rules of Relational Database Design
To adhere to the relational model, tables must follow certain rules. These rules determine what is stored in tables and how the tables are related.
The Rules of Tables
Each table in a system must store data about a single entity. An entity usually represents a real-life object or event. Examples of objects are customers, employees, and inventory items. Examples of events include orders, appointments, and doctor visits.
The Rules of Uniqueness and Keys
Tables are composed of rows and columns. To adhere to the relational model, each table must contain a unique identifier. Without a unique identifier, it becomes programmatically impossible to uniquely address a row. You guarantee uniqueness in a table by designating a primary key, which is a single column or a set of columns that uniquely identifies a row in a table.
Each column or set of columns in a table that contains unique values is considered a candidate key. One candidate key becomes the primary key. The remaining candidate keys become alternate keys. A primary key made up of one column is considered a simple key. A primary key comprising multiple columns is considered a composite key.
It is generally a good idea to pick a primary key that is
• Minimal (has as few columns as possible)
• Stable (rarely changes)
• Simple (is familiar to the user)
Following these rules greatly improves the performance and maintainability of your database application, particularly if you are dealing with large volumes of data.
Consider the example of an employee table. An employee table is generally composed of employee-related fields such as Social Security number, first name, last name, hire date, salary, and so on. The combination of the first name and the last name fields could be considered a primary key. This choice might work, until the company hires two employees with the same name. Although the first and last names could be combined with additional fields to constitute uniqueness (for example, hire date), this would violate the rule of keeping the primary key minimal. Furthermore, an employee might get married and change her last name. This violates the rule of keeping a primary key stable.
Using a name as the primary key violates the principle of stability. The Social Security number might be a valid choice, but a foreign employee might not have a Social Security number. This is a case in which a derived, rather than a natural, primary key is appropriate. A derived key is an artificial key that you create. A natural key is one that is already part of the database.
In examples such as this, I suggest adding EmployeeID as an AutoNumber field. Although the field would violate the rule of simplicity (because an employee number is meaningless to the user), it is both small and stable. Because it is numeric, it is also efficient to process. In fact, I use AutoNumber fields (an Identity field in SQL Server) as primary keys for most of the tables that I build.
The Rules of Foreign Keys and Domains
A foreign key in one table is the field that relates to the primary key in a second table. For example, the CustomerID is the primary key in the Customers table. It is the foreign key in the Orders table.
A domain is a pool of values from which columns are drawn. A simple example of a domain is the specific data range of employee hire dates. In the case of the Orders table, the domain of the CustomerID column is the range of values for the CustomerID in the Customers table.
PITFALLS:
A bad design may have several properties, including:
• Repetition of information.
• Inability to represent certain information.
• Loss of information.
The relation lending with the schema is an example of a bad design:
Lending-Schema=(branch-name, branch-city, assets, cutomer-name, loan-number, amount)
|branch-name |branch-city |assets |customer-name |loan-number |amount |
|Downtown |Brooklyn |9000000 |Jones |L-17 |1000 |
|Redwood |Palo Alto |2100000 |Smith |L-23 |2000 |
|Perryridge |Horseneck |1700000 |Hayes |L-15 |1500 |
|Downtown |Brooklyn |9000000 |Jackson |L-14 |1500 |
|Mianus |Horseneck |400000 |Jones |L-93 |500 |
|Round Hill |Horseneck |8000000 |Turner |L-11 |900 |
|Pownal |Bennington |300000 |Williams |L-29 |1200 |
|North Town |Rye |3700000 |Hayes |L-16 |1300 |
|Downtown |Brooklyn |9000000 |Johnson |L-23 |2000 |
|Perryridge |Horseneck |1700000 |Glenn |L-25 |2500 |
|Brighton |Brooklyn |7100000 |Brooks |L-10 |2200 |
Looking at the Downtown and Perryridge, when a new loan is added, the branch-city and assets must be repeated. That makes updating the table more difficult, because the update must guarantee that all tuples are updated. Additional problems come from having two people take out one loan (L-23). More complexity is involved when Jones took out a loan at a second branch (maybe one near home and the other near work.) Notice that there is no way to represent information on a branch unless there is a loan.
NORMALIZATION USING FUNCTIONAL DEPENDENCIES:
• Functional Dependency is a relationship that exists between multiple attributes of a relation.
• This concept is given by E. F. Codd.
• Functional dependency represents a formalism on the infrastructure of relation.
• It is a type of constraint existing between various attributes of a relation.
• It is used to define various normal forms.
• These dependencies are restrictions imposed on the data in database.
• If P is a relation with A and B attributes, a functional dependency between these two attributes is represented as {A → B}. It specifies that,
|A |It is a determinant set. |
|B |It is a dependent attribute. |
|{A → B} |A functionally determines B. |
| |B is a functionally dependent on A. |
• Each value of A is associated precisely with one B value. A functional dependency is trivial if B is a subset of A.
• 'A' Functionality determines 'B' {A → B} (Left hand side attributes determine the values of Right hand side attributes).
For example: Table
|EmpId | EmpName |
• employee> table, EmpName (employee name) is functionally dependent on EmpId (employee id) because the EmpId is unique for individual names.
• The EmpId identifies the employee specifically, but EmpName cannot distinguish the EmpId because more than one employee could have the same name.
• The functional dependency between attributes eliminates the repetition of information.
• It is related to a candidate key, which uniquely identifies a tuple and determines the value of all other attributes in the relation.
Advantages of Functional Dependency
• Functional Dependency avoids data redundancy where same data should not be repeated at multiple locations in same database.
• It maintains the quality of data in database.
• It allows clearly defined meanings and constraints of databases.
• It helps in identifying bad designs.
• It expresses the facts about the database design.
NORMALIZATION:
Normalization is a set of rules/guidelines/technique that is used while designing a database. These rules help to remove all the anomalies and distribute the data among different related tables and query them efficiently and effectively. It removes all the duplication issues and incorrect data issues, helping to have a well designed database. Normalization is divided into following normal forms:
1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce-Codd Normal Form (3.5NF)
5. Forth Normal Form (4NF)
First Normal Form (1NF)
A table is said to be in First Normal Form (1NF) if and only if each attribute of the relation is atomic. That is,
• Each row in a table should be identified by primary key (a unique column value or group of unique column values)
• No rows of data should have repeating group of column values.
Let's consider the STUDENT table with his ID, Name address and 2 subjects that he has opted for.
[pic]
• Look at Chris entry. He has only subject. Hence Subject2 for him is NULL. Here storage space for second entry is simply wasted.
• In the case of Joseph, he has two subjects, Mathematics and Physics, both the columns have values. Imagine if he opts for third subject? There is no column for his third entry. In this case, whole table needs to be altered, which is not good at this stage. Once database is designed, it should be a perfect one. We should not be modifying it as we start adding/updating data.
• One of the requirements of 1NF is, each table should have primary key. This key in the table makes each record unique. In our example we have it already- STUDENT_ID.
• Here, SUBJECT1 and SUBJECT2 are same set of columns, i.e.; it has same kind of information stored - Subject, which is a violation of first rule of 1NF. As it states, there should not be any repeating columns. We have to remove such columns. But think how?
In order to have STUDENT in 1NF, we have to remove multiple SUBJECT columns from STUDENT table. Instead, create only one SUBJECT column, and for each STUDENT enters as many rows as SUBJECT he has. After making this change, the above table will change as follows:
[pic]
Now STUDENT_ID alone cannot be a primary key, because it does not uniquely determines each record in the table. If we want to records for Joseph, and we query by his ID,100, gives us two records. Hence Student_ID is no more a primary key. When we observe the data in the table, all the four field uniquely determines each record. Hence all four fields together considered as primary key.
Second Normal Form (2NF)
A relation is said to be in a second normal form if and only if,
• it's in first normal form
• Every non-key attributes are identified by the use of primary key
• All subset of data, which applies to have multiple rows in a table must be removed and placed in a new table. And this new table and the parent table should be related by the use of foreign key.
In the 1NF STUDENT table above, Joseph and Allen have multiple rows because of their SUBJECTS. Although it is in 1NF form, it wastes storage space by repeating whole of their information - name and address in each row. In addition, Student ID alone is strong enough to be a primary key. If we make Student_ID as primary, all other attributes in the table cannot be uniquely identified. This is because of multiple rows exists for single ID. Hence it does not satisfy second condition of 2NF.
So what we can do here is, apply the third condition of 2NF. Remove Subject from the STUDENT table and create a separate table for it. So the two tables are - STUDENT and SUBJECT. Now the STUDENT table will have only STUDENT information - STUDENT_ID, STUDENT_NAME and ADDRESS. New SUBJECT table will have STUDENT_ID and SUBJECT_NAME.
[pic]
Now there is no repeating group of columns in STUDENT table and STUDENT_ID is the primary key of STUDENT table. It uniquely identifies the Student name and address which are non key attributes of this table. Hence it satisfies both 1NF and 2NF.
In the new SUBJECT table, Subject names opted by each student. Since same student cannot opt for same subject multiple times in academic year, there will not be any duplicity of data. But Student_ID alone is not unique; hence it cannot be a primary key. Both Student_ID and Subject_Name is unique in this table. Hence both of them together become a primary key. Hence SUBJECT table satisfies 1NF.
There is no non-key attributes in SUBJECT table. Hence we cannot verify second condition of 2NF. According to the third condition of 2NF, we have removed the data which is forming multiple rows and put those details in new table. It also states that there should be relationship between the original table and new table by using foreign key constraint. In the SUBJECT table, STUDENT_ID is derived from STUDENT table. In this table, STUDENT _ID is part of primary key as well as it is a foreign key. Hence we can easily relate both STUDENT and SUBJECT table by using STUDENT_ID. Hence satisfies the third condition of 2NF.
[pic]
If we want to know which all subjects Joseph has opted for, we would query as below:
SELECT std.STUDENT_ID,
std.STUDENT_NAME,
sb.SUBJECT
FROM STUDENT std, SUBJECT sb
WHERE std.STUDENT_ID = sb.STUDENT_ID
AND std.STUDENT_NAME = 'Joseph';
Third Normal Form (3NF)
For a relation to be in third normal form:
• it should meet all the requirements of both 1NF and 2NF
• If there is any columns which are not related to primary key, then remove them and put it in a separate table, relate both the table by means of foreign key i.e.; there should not be any transitive dependency.
Let’s add three more columns - STREET, CITY and ZIP to STUDENT table to explain 3NF. Below is the table satisfies conditions for 2NF - there is primary key, no repeating columns, no duplicate datas.
[pic]
Here, STREET and CITY have no relation with Student. It's not directly related to student. They fully depend upon zip code. Since Student stays in that area, through zip code, street and city are related to him. This kind of relationship is called transitive dependency. Since its second level of dependency, it is not necessary to store these details in STUDENT table.
Similarly, if there are multiple students staying in same area, STUDENT table is having huge amount of records and there is a change requested for street or city name, then whole STUDENT table needs to be searched and updated. Imagine, we have to update 'Fraser Village Drive' to Fraser Village Dr'. The Update statement would be
UPDATE STUDENT std
SET std.STREET = 'Fraser Village Dr'
WHERE std .STREET = 'Fraser Village Drive';
Above query will search whole student table for 'Fraser Village Drive' and then update it to 'Fraser Village Dr’. But searching a huge table and updating the single or multiple records will be a very time consuming, hence affecting the performance of the database.
Instead, if we have these details in a separate table ZIPCODE and is related to STUDENT table using zip? However ZIPCODE table will have comparatively less amount of records and we just have to update ZIPCODE table once. It will automatically reflect in the STUDENT table! Hence making the database and query simpler! And table is in 3NF.
UPDATE ZIPCODE z
SET z.STREET = 'Fraser Village Dr'
WHERE z .STREET = 'Fraser Village Drive';
[pic]
Now if we have to select the whole address of a student, Chris, we join both STUDENT and ZIPCODE table using ZIP and get the whole address.
SELECT std.STUDENT_NAME,
z.STREET,
z.CITY,
z.ZIP
FROM STUDENT std, ZIPCODE z
WHERE std.ZIP = z.ZIP
AND std.STUDENT_NAME = 'Chris';
Boyce-Codd Normal Form (3.5NF)
This normal form is also referred as 3.5 normal forms. This normal form
• Meets all the requirement of 3NF
• Any table is said to be in BCNF, if its candidate keys do not have any partial dependency on the other attributes. i.e.; in any table with (x, y, z) columns, if (x, y)->z and z->x then it's a violation of BCNF. If (x, y) are composite keys and (x, y)->z, then there should not be any reverse dependency, directly or partially.
In the above 3NF example, STUDENT_ID is the Primary key in STUDENT table and ZIP is the primary key in the ZIPCODE table. There is no other key column in each of the tables which determines the functional dependency. Hence it's in BCNF form. That is, with STUDENT_ID, we can retrieve STUDENT_NAME and ZIP from STUDENT table. Similarly, with ZIP value, we can retrieve STREET and CITY from ZIPCODE table
Let us consider another example - consider each student who has taken major subjects has different advisory lecturers. Each student will have different advisory lecturers for same Subjects. There exists following relationship, which is violation of BCNF.
(STUDENT_ID, MAJOR_SUBJECT) -> ADVISORY_LECTURER
ADVISORY_LECTURER -> MAJOR_SUBJECT
i.e. Major_Subject which is a part of composite candidate key is determined non-key attribute of the same table, which is against the rule.
Below table will have all the anomalies too. If we delete any student from below table, it deletes lecturer's information too. If we add any new lecturer/student to the database, it needs other related information also. Also, if we update subject for any student, his lecturer info also needs to be changed, else it will lead to inconsistency.[pic]
Hence we need to decompose the table so that eliminates so that it eliminates such relationship. Now in the new tables below, there are no inter-dependent composite keys (moreover, there is no composite key in both the tables). If we need to add/update/delete any lecturer, we can directly insert record into STUDENT_ADVISOR table, without affecting STUDENT_MAJOR table. If we need to insert/update/delete any subject for a student, then we can directly do it on STUDENT_MAJOR table, without affecting STUDENT_ADVISOR table. When we have both advisor as well as major subject information, then we can directly add/update both the tables. Hence we have eliminated all the anamolies in the database.
[pic]
Fourth Normal Form (4NF)
In the fourth normal form,
• It should meet all the requirement of 3NF
• Attribute of one or more rows in the table should not result in more than one rows of the same table leading to multi-valued dependencies
To understand it clearly, consider a table with Subject, Lecturer who teaches each subject and recommended Books for each subject.
If we observe the data in the table above it satisfies 3NF. But LECTURER and BOOKS are two independent entities here. There is no relationship between Lecturer and Books. In the above example, either Alex or Bosco can teach Mathematics. For Mathematics subject , student can refer either 'Maths Book1' or 'Maths Book2'. i.e.;
SUBJECT --> LECTURER
SUBJECT-->BOOKS
This is a multivalued dependency on SUBJECT. If we need to select both lecturer and books recommended for any of the subject, it will show up (lecturer, books) combination, which implies lecturer who recommends which book. This is not correct.
SELECT c.LECTURER, c.BOOKS FROM COURSE c WHERE SUBJECT = 'Mathematics';
To eliminate this dependency, we divide the table into two as below:
[pic]
Now if we want to know the lecturer names and books recommended for any of the subject, we will fire two independent queries. Hence it removes the multi-valued dependency and confusion around the data. Thus the table is in 4NF.
--Select the lecturer names
SELECT c.SUBJECT , c.LECTURER FROM COURSE c WHERE c.SUBJECT = 'Mathematics';
--Select the recommended book names
SELECT c.SUBJECT , c.BOOKS FROM COURSE c WHERE c.SUBJECT = 'Mathematics';
UNIT V
PL SQL basically stands for "Procedural Language extensions to SQL". This is the extension of Structured Query Language (SQL) that is used in Oracle. Unlike SQL, PL/SQL allows the programmer to write code in procedural format.
It combines the data manipulation power of SQL with the processing power of procedural language to create a super powerful SQL queries.
It allows the programmers to instruct the compiler 'what to do' through SQL and 'how to do' through its procedural way.
Similar to other database languages, it gives more control to the programmers by the use of loops, conditions and object oriented concepts.
Architecture of PL/SQL
The PL/SQL architecture mainly consists of following 3 components:
1. PL/SQL block
2. PL/SQL Engine
3. Database Server
PL/SQL block:
• This is the component which has the actual PL/SQL code.
• This consists of different sections to divide the code logically (declarative section for declaring purpose, execution section for processing statements, exception handling section for handling errors)
• It also contains the SQL instruction that used to interact with the database server.
• All the PL/SQL units are treated as PL/SQL blocks, and this is the starting stage of the architecture which serves as the primary input.
• Following are the different type of PL/SQL units.
o Anonymous Block
o Function
o Library
o Procedure
o Package Body
o Package Specification
o Trigger
o Type
o Type Body
PL/SQL Engine
• PL/SQL engine is the component where the actual processing of the codes takes place.
• PL/SQL engine separates PL/SQL units and SQL part in the input (as shown in the image below).
• The separated PL/SQL units will be handled with the PL/SQL engine itself.
• The SQL part will be sent to database server where the actual interaction with database takes place.
• It can be installed in both database server and in the application server.
Database Server:
• This is the most important component of Pl/SQL unit which stores the data.
• The PL/SQL engine uses the SQL from PL/SQL units to interact with the database server.
• It consists of SQL executor which actually parses the input SQL statements and execute the same.
Below is the pictorial representation of Architecture of PL/SQL.
[pic]
Advantage of Using PL/SQL
1. Better performance, as sql is executed in bulk rather than a single statement
2. High Productivity
3. Tight integration with SQL
4. Full Portability
5. Tight Security
6. Support Object Oriented Programming concepts.
Basic Difference between SQL and PL/SQL
In this section, we will discuss some differences between SQL and PL/SQL
|SQL |PL/SQL |
|SQL is a single query that is used to perform DML and DDL operations.|PL/SQL is a block of codes that used to write the entire program |
| |blocks/ procedure/ function, etc. |
|It is declarative, that defines what needs to be done, rather than |PL/SQL is procedural that defines how the things needs to be done. |
|how things need to be done. | |
|Execute as a single statement. |Execute as a whole block. |
|Mainly used to manipulate data. |Mainly used to create an application. |
|Interaction with Database server. |No interaction with the database server. |
|Cannot contain PL/SQL code in it. |It is an extension of SQL, so it can contain SQL inside it. |
DDL,DML,DCL COMMANDS:
[pic]
DDL:
DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.
• CREATE - to create database and its objects like (table, index, views, store procedure, function, and triggers)
• ALTER - alters the structure of the existing database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object
DML:
DML is short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE etc, and it is used to store, modify, retrieve, delete and update data in a database.
• SELECT - retrieve data from a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - Delete all records from a database table
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - interpretation of the data access path
• LOCK TABLE - concurrency Control
DCL:
DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.
• GRANT - allow users access privileges to database
• REVOKE - withdraw users access privileges given by using the GRANT command
TCL:
TCL is short name of Transaction Control Language which deals with a transaction within a database.
• COMMIT - commits a Transaction
• ROLLBACK - rollback a transaction in case of any error occurs
• SAVEPOINT - to rollback the transaction making points within groups
• SET TRANSACTION - specify characteristics of the transaction
INTEGRITY CONSTRAINTS:
Integrity Constraints
Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity. Many types of integrity constraints play a role in referential integrity (RI).
Primary Key Constraints
Primary key is the term used to identify one or more columns in a table that make a row of data unique. Although the primary key typically consists of one column in a table, more than one column can comprise the primary key. For example, either the employee's Social Security number or an assigned employee identification number is the logical primary key for an employee table. The objective is for every record to have a unique primary key or value for the employee's identification number. Because there is probably no need to have more than one record for each employee in an employee table, the employee identification number makes a logical primary key. The primary key is assigned at table creation.
The following example identifies the EMP_ID column as the PRIMARY KEY for the EMPLOYEES table:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR(40)NOT NULL,
EMP_ST_ADDR VARCHAR(20)NOT NULL,
EMP_CITY VARCHAR(15) NOT NULL,
EMP_ST CHAR(2)NOT NULL,
EMP_ZIP INTEGER(5)NOT NULL,
EMP_PHONE INTEGER(10)NULL,
EMP_PAGER INTEGER(10)NULL);
This method of defining a primary key is accomplished during table creation. The primary key in this case is an implied constraint. You can also specify a primary key explicitly as a constraint when setting up a table, as follows:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9)NOT NULL,
EMP_NAME VARCHAR(40)NOT NULL,
EMP_ST_ADDR VARCHAR(20)NOT NULL,
EMP_CITY VARCHAR (15)NOT NULL,
EMP_ST CHAR(2)NOT NULL,
EMP_ZIP NUMBER(5)NOT NULL,
EMP_PHONE NUMBER(10)NULL,
EMP_PAGER NUMBER(10)NULL,
PRIMARY KEY (EMP_ID));
The primary key constraint in this example is defined after the column comma list in the CREATE TABLE statement.
A primary key that consists of more than one column can be defined by either of the following methods:
CREATE TABLE PRODUCTS
(PROD_ID VARCHAR2(10)NOT NULL,
VEND_ID VARCHAR2(10)NOT NULL,
PRODUCT VARCHAR2(30)NOT NULL,
COST NUMBER(8,2)NOT NULL,
PRIMARY KEY (PROD_ID, VEND_ID));
ALTER TABLE PRODUCTS
ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (PROD_ID, VEND_ID);
Unique Constraints
A unique column constraint in a table is similar to a primary key in that the value in that column for every row of data in the table must have a unique value. Although a primary key constraint is placed on one column, you can place a unique constraint on another column even though it is not actually for use as the primary key.
Study the following example:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9)NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR (40)NOT NULL,
EMP_ST_ADDR VARCHAR (20)NOT NULL,
EMP_CITY VARCHAR (15)NOT NULL,
EMP_ST CHAR(2)NOT NULL,
EMP_ZIP NUMBER(5)NOT NULL,
EMP_PHONE NUMBER(10)NULL UNIQUE,
EMP_PAGER INTEGER(10)NULL);
The primary key in this example is EMP_ID, meaning that the employee identification number is the column that is used to ensure that every record in the table is unique. The primary key is a column that is normally referenced in queries, particularly to join tables. The column EMP_PHONE has been designated as a UNIQUE value, meaning that no two employees can have the same telephone number. There is not a lot of difference between the two, except that the primary key is used to provide an order to data in a table and, in the same respect, join related tables.
Foreign Key Constraints
A foreign key is a column in a child table that references a primary key in the parent table. A foreign key constraint is the main mechanism used to enforce referential integrity between tables in a relational database. A column defined as a foreign key is used to reference a column defined as a primary key in another table.
Study the creation of the foreign key in the following example:
CREATE TABLE EMPLOYEE_PAY_TBL
(EMP_ID CHAR(9)NOT NULL,
POSITION VARCHAR2(15)NOT NULL,
DATE_HIRE DATE NULL,
PAY_RATE NUMBER(4,2)NOT NULL,
DATE_LAST_RAISE DATE NULL,
CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID));
The EMP_ID column in this example has been designated as the foreign key for the EMPLOYEE_PAY_TBL table. This foreign key, as you can see, references the EMP_ID column in the EMPLOYEE_TBL table. This foreign key ensures that for every EMP_ID in the EMPLOYEE_PAY_TBL, there is a corresponding EMP_ID in the EMPLOYEE_TBL. This is called a parent/child relationship. The parent table is the EMPLOYEE_TBL table, and the child table is the EMPLOYEE_PAY_TBL table.
[pic]
The parent/child table relationship
n this figure, the EMP_ID column in the child table references the EMP_ID column in the parent table. For a value to be inserted for EMP_ID in the child table, a value for EMP_ID in the parent table must first exist. Likewise, for a value to be removed for EMP_ID in the parent table, all corresponding values for EMP_ID must first be removed from the child table. This is how referential integrity works.
A foreign key can be added to a table using the ALTER TABLE command, as shown in the following example:
alter table employee_pay_tbl
add constraint id_fk foreign key (emp_id)
references employee_tbl (emp_id);
NOT NULL Constraints
Previous examples use the keywords NULL and NOT NULL listed on the same line as each column and after the data type. NOT NULL is a constraint that you can place on a table's column. This constraint disallows the entrance of NULL values into a column; in other words, data is required in a NOT NULL column for each row of data in the table. NULL is generally the default for a column if NOT NULL is not specified, allowing NULL values in a column.
Check Constraints
Check (CHK) constraints can be utilized to check the validity of data entered into particular table columns. Check constraints are used to provide back-end database edits, although edits are commonly found in the front-end application as well. General edits restrict values that can be entered into columns or objects, whether within the database itself or on a front-end application. The check constraint is a way of providing another protective layer for the data.
The following example illustrates the use of a check constraint:
CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL,
EMP_NAME VARCHAR2(40) NOT NULL,
EMP_ST_ADDR VARCHAR2(20) NOT NULL,
EMP_CITY VARCHAR2(15) NOT NULL,
EMP_ST CHAR(2) NOT NULL,
EMP_ZIP NUMBER(5) NOT NULL,
EMP_PHONE NUMBER(10) NULL,
EMP_PAGER NUMBER(10) NULL),
PRIMARY KEY (EMP_ID),
CONSTRAINT CHK_EMP_ZIP CHECK ( EMP_ZIP = '46234');
The check constraint in this table has been placed on the EMP_ZIP column, ensuring that all employees entered into this table have a ZIP code of '46234'. Perhaps that is a little restricting. Nevertheless, you can see how it works.
If you wanted to use a check constraint to verify that the ZIP code is within a list of values, your constraint definition could look like the following:
CONSTRAINT CHK_EMP_ZIP CHECK ( EMP_ZIP in ('46234','46227','46745') );
If there is a minimum pay rate that can be designated for an employee, you could have a constraint that looks like the following:
CREATE TABLE EMPLOYEE_PAY_TBL
(EMP_ID CHAR(9) NOT NULL,
POSITION VARCHAR2(15) NOT NULL,
DATE_HIRE DATE NULL,
PAY_RATE NUMBER(4,2) NOT NULL,
DATE_LAST_RAISE DATE NULL,
CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID),
CONSTRAINT CHK_PAY CHECK ( PAY_RATE > 12.50 ) );
In this example, any employee entered in this table must be paid more than $12.50 an hour. You can use just about any condition in a check constraint, as you can with a SQL query. You learn more about these conditions in Hours 5 and 7.
Dropping Constraints
Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option. For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command:
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;
Table altered.
Some implementations might provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in MySQL, you can use the following command:
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;
Table altered.
PL/SQL Block:
Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.
PL/SQL Block consists of three sections:
• The Declaration section (optional).
• The Execution section (mandatory).
• The Exception Handling (or Error) 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;
PL/SQL Procedures
Stored Procedures
What is a Stored Procedure?
A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages.
A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block.
A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.
Procedures: Passing Parameters
We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
A procedure may or may not return any value.
General Syntax to create a procedure is:
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
IS - marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.
The syntax within the brackets [ ] indicate they are optional. By using CREATE OR REPLACE together the procedure is created if no other procedure with the same name exists or the existing procedure is replaced with the current code.
Procedures: Example
The below example creates a procedure ‘employer_details’ which gives the details of the employee.
1> CREATE OR REPLACE PROCEDURE employer_details
2> IS
3> CURSOR emp_cur IS
4> SELECT first_name, last_name, salary FROM emp_tbl;
5> emp_rec emp_cur%rowtype;
6> BEGIN
7> FOR emp_rec in sales_cur
8> LOOP
9> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
10> || ' ' ||emp_cur.salary);
11> END LOOP;
12>END;
13> /
How to execute a Stored Procedure?
There are two ways to execute a procedure.
1) From the SQL prompt.
EXECUTE [or EXEC] procedure_name;
2) Within another procedure – simply use the procedure name.
procedure_name;
|PL/SQL Functions | |
| | |
|What is a Function in PL/SQL? | |
|A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must| |
|always return a value, but a procedure may or may not return a value. | |
|General Syntax to create a function is | |
|CREATE [OR REPLACE] FUNCTION function_name [parameters] | |
|RETURN return_datatype; | |
|IS | |
|Declaration_section | |
|BEGIN | |
|Execution_section | |
|Return return_variable; | |
|EXCEPTION | |
|exception section | |
|Return return_variable; | |
|END; | |
1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.
For example, let’s create a frunction called ''employer_details_func' similar to the one created in stored proc
1> CREATE OR REPLACE FUNCTION employer_details_func
2> RETURN VARCHAR(20);
3> IS
5> emp_name VARCHAR(20);
6> BEGIN
7> SELECT first_name INTO emp_name
8> FROM emp_tbl WHERE empID = '100';
9> RETURN emp_name;
10> END;
11> /
In the example we are retrieving the ‘first_name’ of employee with empID 100 to variable ‘emp_name’.
The return type of the function is VARCHAR which is declared in line no 2.
The function returns the 'emp_name' which is of type VARCHAR as the return value in line no 9.
How to execute a PL/SQL Function?
A function can be executed in the following ways.
1) Since a function returns a value we can assign it to a variable.
employee_name := employer_details_func;
If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning the return type of the function to it.
2) As a part of a SELECT statement
SELECT employer_details_func FROM dual;
3) In a PL/SQL Statements like,
dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.
PL/SQL Cursors
What are Cursors?
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.
There are two types of cursors in PL/SQL:
|Implicit cursors | |
|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. | |
|Explicit cursors | |
|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. | |
|Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed. | |
Implicit Cursors: Application
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.
|Attributes |Return Value |Example |
|%FOUND |The return value is TRUE, if the DML statements like INSERT, DELETE and |SQL%FOUND |
| |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. | |
|%NOTFOUND |The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE|SQL%NOTFOUND |
| |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. | |
|%ROWCOUNT |Return the number of rows affected by the DML operations INSERT, DELETE, |SQL%ROWCOUNT |
| |UPDATE, SELECT | |
For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:
DECLARE var_rows number(5);
BEGIN
UPDATE employee
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.
Explicit Cursors
An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor.
General Syntax for creating a cursor is as given below:
CURSOR cursor_name IS select_statement;
• cursor_name – A suitable name for the cursor.
• select_statement – A select query which returns multiple rows.
How to use Explicit Cursor?
There are four steps in using an Explicit Cursor.
DECLARE the cursor in the declaration section.
OPEN the cursor in the Execution Section.
FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
1) Declaring a Cursor in the Declaration Section:
DECLARE
CURSOR emp_cur IS
SELECT *
FROM emp_tbl
WHERE salary > 5000;
In the above example we are creating a cursor ‘emp_cur’ on a query which returns the records of all the
employees with salary greater than 5000. Here ‘emp_tbl’ in the table which contains records of all the
employees.
2) Accessing the records in the cursor:
Once the cursor is created in the declaration section we can access the cursor in the execution
section of the PL/SQL program.
How to access an Explicit Cursor?
These are the three steps in accessing the cursor.
1) Open the cursor.
2) Fetch the records in the cursor one at a time.
3) Close the cursor.
General Syntax to open a cursor is:
OPEN cursor_name;
General Syntax to fetch records from a cursor is:
FETCH cursor_name INTO record_name;
OR
FETCH cursor_name INTO variable_list;
General Syntax to close a cursor is:
CLOSE cursor_name;
When a cursor is opened, the first row becomes the current row. When the data is fetched it is copied to the record or variables and the logical pointer moves to the next row and it becomes the current row. On every fetch statement, the pointer moves to the next row. If you want to fetch after the last row, the program will throw an error. When there is more than one row in a cursor we can use loops along with explicit cursor attributes to fetch all the records.
Points to remember while fetching a row:
We can fetch the rows in a cursor to a PL/SQL Record or a list of variables created in the PL/SQL Block.
· If you are fetching a cursor to a PL/SQL Record, the record should have the same structure as the cursor.
· If you are fetching a cursor to a list of variables, the variables should be listed in the same order in the fetch statement as the columns are present in the cursor.
General Form of using an explicit cursor is:
DECLARE
variables;
records;
create a cursor;
BEGIN
OPEN cursor;
FETCH cursor;
process the records;
CLOSE cursor;
END;
Explicit Cursor, Lets Look at the example below
Example 1:
1> DECLARE
2> emp_rec emp_tbl%rowtype;
3> CURSOR emp_cur IS
4> SELECT *
5> FROM
6> WHERE salary > 10;
7> BEGIN
8> OPEN emp_cur;
9> FETCH emp_cur INTO emp_rec;
10> dbms_output.put_line (emp_rec.first_name || ' ' || emp_rec.last_name);
11> CLOSE emp_cur;
12> END;
In the above example, first we are creating a record ‘emp_rec’ of the same structure as of table ‘emp_tbl’ in line no 2. We can also create a record with a cursor by replacing the table name with the cursor name. Second, we are declaring a cursor ‘emp_cur’ from a select query in line no 3 - 6. Third, we are opening the cursor in the execution section in line no 8. Fourth, we are fetching the cursor to the record in line no 9. Fifth, we are displaying the first_name and last_name of the employee in the record emp_rec in line no 10. Sixth, we are closing the cursor in line no 11.
What are Explicit Cursor Attributes?
Oracle provides some attributes known as Explicit Cursor Attributes to control the data processing while using cursors. We use these attributes to avoid errors while accessing cursors through OPEN, FETCH and CLOSE Statements.
When does an error occur while accessing an explicit cursor?
a) When we try to open a cursor which is not closed in the previous operation.
b) When we try to fetch a cursor after the last operation.
These are the attributes available to check the status of an explicit cursor.
|Attributes |Return values |Example |
|%FOUND |TRUE, if fetch statement returns at least one row. |Cursor_name%FOUND |
| |FALSE, if fetch statement doesn’t return a row. | |
|%NOTFOUND |TRUE, if fetch statement doesn’t return a row. |Cursor_name%NOTFOUND |
| |FALSE, if fetch statement returns at least one row. | |
|%ROWCOUNT |The number of rows fetched by the fetch statement |Cursor_name%ROWCOUNT |
| |If no row is returned, the PL/SQL statement returns an | |
| |error. | |
|%ISOPEN |TRUE, if the cursor is already open in the program |Cursor_name%ISNAME |
| |FALSE, if the cursor is not opened in the program. | |
Using Loops with Explicit Cursors:
Oracle provides three types of cursors namely SIMPLE LOOP, WHILE LOOP and FOR LOOP. These loops can be used to process multiple rows in the cursor. Here I will modify the same example for each loops to explain how to use loops with cursors.
Cursor with a Simple Loop:
1> DECLARE
2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN
6> IF NOT sales_cur%ISOPEN THEN
7> OPEN sales_cur;
8> END IF;
9> LOOP
10> FETCH emp_cur INTO emp_rec;
11> EXIT WHEN emp_cur%NOTFOUND;
12> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
13> || ' ' ||emp_cur.salary);
14> END LOOP;
15> END;
16> /
In the above example we are using two cursor attributes %ISOPEN and %NOTFOUND.
In line no 6, we are using the cursor attribute %ISOPEN to check if the cursor is open, if the condition is true the program does not open the cursor again, it directly moves to line no 9.
In line no 11, we are using the cursor attribute %NOTFOUND to check whether the fetch returned any row. If there is no rows found the program would exit, a condition which exists when you fetch the cursor after the last row, if there is a row found the program continues.
We can use %FOUND in place of %NOTFOUND and vice versa. If we do so, we need to reverse the logic of the program. So use these attributes in appropriate instances.
Cursor with a While Loop:
Lets modify the above program to use while loop.
1> DECLARE
2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN
6> IF NOT sales_cur%ISOPEN THEN
7> OPEN sales_cur;
8> END IF;
9> FETCH sales_cur INTO sales_rec;
10> WHILE sales_cur%FOUND THEN
11> LOOP
12> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
13> || ' ' ||emp_cur.salary);
15> FETCH sales_cur INTO sales_rec;
16> END LOOP;
17> END;
18> /
In the above example, in line no 10 we are using %FOUND to evaluate if the first fetch statement in line no 9 returned a row, if true the program moves into the while loop. In the loop we use fetch statement again (line no 15) to process the next row. If the fetch statement is not executed once before the while loop the while condition will return false in the first instance and the while loop is skipped. In the loop, before fetching the record again, always process the record retrieved by the first fetch statement, else you will skip the first row.
Cursor with a FOR Loop:
When using FOR LOOP you need not declare a record or variables to store the cursor values, need not open, fetch and close the cursor. These functions are accomplished by the FOR LOOP automatically.
General Syntax for using FOR LOOP:
FOR record_name IN cusror_name
LOOP
process the row...
END LOOP;
Let’s use the above example to learn how to use for loops in cursors.
1> DECLARE
2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN
6> FOR emp_rec in sales_cur
7> LOOP
8> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
9> || ' ' ||emp_cur.salary);
10> END LOOP;
11>END;
12> /
In the above example, when the FOR loop is processed a record ‘emp_rec’of structure ‘emp_cur’ gets created, the cursor is opened, the rows are fetched to the record ‘emp_rec’ and the cursor is closed after the last row is processed. By using FOR Loop in your program, you can reduce the number of lines in the program.
TRIGGERS:
Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events −
• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers can be defined on the table, view, schema, or database with which the event is associated.
Benefits of Triggers
Triggers can be written for the following purposes −
• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions
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 will 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 will 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, such as INSERT, UPDATE, and DELETE.
• [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will 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 the SQL prompt, it produces the following result −
Trigger created.
The following points need to be considered here −
• 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.
• The 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 the 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 the CUSTOMERS table, the 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, old salary is not available and the above result comes as null. Let us now perform one more DML operation on the CUSTOMERS table. The UPDATE statement will update an existing record in the table −
UPDATE customers
SET salary = salary + 500
WHERE id = 2;
When a record is updated in the CUSTOMERS table, the 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
PL/SQL – EXCEPTION HANDLING:
An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions −
• System-defined exceptions
• User-defined exceptions
Syntax for Exception Handling
The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN −
DECLARE
BEGIN
EXCEPTION
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;
Example
Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we had created and used in the previous chapters −
DECLARE
c_id customers.id%type := 8;
c_name customerS.Name%type;
c_addr customers.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
No such customer!
PL/SQL procedure successfully completed.
The above program displays the name and address of a customer whose ID is given. Since there is no customer with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured in the EXCEPTION block.
Raising Exceptions
Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax for raising an exception −
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
You can use the above syntax in raising the Oracle standard exception or any user-defined exception. In the next section, we will give you an example on raising a user-defined exception. You can raise the Oracle standard exceptions in a similar way.
User-defined Exceptions
PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
The syntax for declaring an exception is −
DECLARE
my-exception EXCEPTION;
Example
The following example illustrates the concept. This program asks for a customer ID, when the user enters an invalid ID, the exception invalid_id is raised.
DECLARE
c_id customers.id%type := &cc_id;
c_name customerS.Name%type;
c_addr customers.address%type;
-- user defined exception
ex_invalid_id EXCEPTION;
BEGIN
IF c_id ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- wordpress passing data between pages
- wordpress business templates
- wordpress rss feed not working
- wordpress jquery is not defined
- create wordpress blog
- wordpress roles editor
- wordpress full rss feed
- wordpress rss feed settings
- wordpress rss feed plugin
- wordpress display rss feed
- wordpress rss feed link
- wordpress rss feed to post