INTRODUCTION TO SQL
TATA CONSULTANCY SERVICES
Title Overview on Oracle Oracle’s relational database
What is a relational database?
A relational database is a collection of data items which can be accessed or reassembled in many different ways without having to reorganize the tables. Relational databases recognize the fact that data needs to be accessed several ways in order to be valuable. Rather than duplicating the information in several places, the data is broken down into sets of related information within tables. These tables can then be joined when needed to create the desired result.
TATA CONSULTANCY SERVICES
Title Overview on Oracle Character set & symbols
Character set
Type Character Letters A-Z, a-z Digits 0-9 Symbols ~!@#$%&*()-+=[]{}"‘,.?|:; Whitespace tab, space, carriage return SQL is not a case-sensitive language. Uppercase and lowercase letters are treated the same way with the exception of literal strings (alphanumeric characters surrounded by single quotes) and character variables (variables designated with the "&" prefix). Though SQL is case-insensitive, stick to using UPPERCASE for keywords and reserved words. It is much easier to read
TATA CONSULTANCY SERVICES
Title Overview on Oracle Simple and compound symbols
Symbol
Description
;
statement terminator
%
multibyte wild card symbol
_
singlebyte wild card symbol (underscore)
<> and !=
not equals
||
concatenation operator
<= and >=
relational operator
--
single line comment indicator (dash)
/* and */
multiline comment block delimiters
TATA CONSULTANCY SERVICES
Title Overview on Oracle Data Definition Using SQL
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 Add comments to the data dictionary Oracle commits the current transaction before and after every DDL command
TATA CONSULTANCY SERVICES
Title Overview on Oracle
CREATE TABLE
The create table statement is used to create a new table.
Syntax
create table "tablename" ("column1" "data type", "column2" "data type", "column3" "data type"); Format of create table if you were to use optional constraints: create table "tablename" ("column1" "data type" [constraint], "column2" "data type" [constraint], "column3" "data type" [constraint]); [ ] = optional TATA CONSULTANCY SERVICES
Title Overview on Oracle Example
create table employee (first varchar2(15), last varchar2(20), age number(3), address varchar2(30), city varchar2(20), state varchar2(20));
To create a new table, enter the keywords create table followed by the table name, followed by an open parenthesis, followed by the first column name, followed by the data type for that column, followed by any optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open parenthesis before the beginning table, and a closing parenthesis after the end of the last column definition. Make sure you separate each column definition with a comma. All SQL statements should end with a ";". The table and column names must start with a letter and can be followed by letters, numbers, or underscores - not to exceed a total of 30 characters in length. Do not use any SQL reserved keywords as names for tables or column names (such as "select", "create", "insert", etc).
TATA CONSULTANCY SERVICES
Title Overview on Oracle ALTER TABLE
Use Alter table command to change the definition of a table. Here is the format of a simple alter table statement: Syntax
ALTER TABLE [schema.] table { [ ADD ( { column_element | column_constraint) [ , column_element | column_constraint ) ] …) ] [ MODIFY (column_element [ , column_element]…) ] [ DROP drop_clause] … [ ENABLE enable_clause ] … [ DISABLE disable_clause ] ... Example Alter table employee add (town varchar2(20)); Alter table employee modify (town varchar2(40)); alter table employee modify(first not null); Alter table employee drop (town);
DROP TABLE
To remove a table from the database use the Drop table command . Here is the format of a simple alter table statement:
TATA CONSULTANCY SERVICES
Title Overview on Oracle
Syntax: DROP TABLE “tablename”;
EXAMPLE: Drop table employee;
Datatypes
Number Character Long Date-time
TATA CONSULTANCY SERVICES
Title Overview on Oracle Number(size,d) :- Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal. Number(size) :- Number value with a max number of column digits specified in parenthesis. Char(size) :-Fixed-length character string. Size is specified in parenthesis. Max 255 bytes. Varchar2(size) :- Variable-length character string. Max size of 2000. Date:-Date value from Jan 1 ,4712 BC to Dec 31,4712 AD. Timestamp :- Stores all the information that the DATE datatype stores, but also includes fractional seconds.(This is a new feature added in Oracle 9i). Long :- Variable length character values upto 2GB.
TATA CONSULTANCY SERVICES
Title Overview on Oracle Integrity Constraint Define the conditions under which data is valid Table Constraints Part of the table definition Column Constraints Part of the column definition
Constraint Types
Not Null Constraint Primary Key Constraint Unique Constraint Check Constraint Foreign Key (Referential Integrity Constraint)
TATA CONSULTANCY SERVICES
Title Overview on Oracle NOT NULL Constraint Specifies that a column cannot contain nulls If you do not specify this constraint, the default is NULL EXAMPLE:
ALTER TABLE hobbies (hobbies_type_code NOT NULL);
Primary Key Constraint
Designates a column or combination of columns to be the table’s primary key Both of these conditions must be true: No primary key value can appear in more than one row in the table No column that is part of the primary key can contain a NULL EXAMPLE: ALTER TABLE hobbies ADD CONSTRAINT PK_swvterm PRIMARY KEY (term_code);
TATA CONSULTANCY SERVICES
Title Overview on Oracle Unique Constraint Designate a column or combination of columns as a unique key No two rows in the table can have the same value for a unique key Cannot designate the same column or combination of columns as both a unique key and the primary key EXAMPLE: ALTER TABLE ABC ADD CONSTRAINT abc_code UNIQUE (detc_code);
Check Constraints Explicitly define a condition. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to NULL)
ALTER TABLE twraccd ADD CONSTRAINT check_trans_type CHECK (trans_type IN ('C', 'P')); ALTER TABLE twraccd ADD CONSTRAINT check_amount CHECK (amount > 0 );
TATA CONSULTANCY SERVICES
Title Overview on Oracle Data Manipulation Language (DML) Data Manipulation Language (DML) commands query and manipulate data in existing schema objects
SELECT INSERT UPDATE DELETE
These commands do not implicitly commit the current transaction.
SELECT
The purpose of a SELECT statement is to display columns and rows from one or more tables. This is also known as querying the database.
Syntax :
SELECT * FROM
;
Example :
SELECT deptno, ename, mgr FROM emp;
SELECT DISTINCT ename FROM emp;
SELECT ename, deptno FROM emp ORDER BY ename;
TATA CONSULTANCY SERVICES
Title Overview on Oracle INSERT
The INSERT command is used to add rows to a table. Syntax :
INSERT INTO table_name [ (column,column, …..) ] VALUES ( value,value, ….); Example :
INSERT INTO emp (first, last, age, address, city, state) values ('Luke', 'Duke', 45, '2130 Boars Nest', 'Hazard Co', 'Georgia');
UPDATE
The update statement is used to update or change records that match a specified criteria.
Syntax :
Update "tablename” set "columnname" = "newvalue” where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"];
Example :
Update phone_book set area_code = 623 where prefix = 979;
TATA CONSULTANCY SERVICES
Title Overview on Oracle DELETE
The delete statement is used to delete records or rows from the table. Syntax :
delete from "tablename” where "columnname" OPERATOR "value" [and|or "column” OPERATOR "value"]; Example :
delete from employee where lastname = 'May';
TATA CONSULTANCY SERVICES
Title Overview on Oracle Arithmetic Expressions and Functions
Objectives Use arithmetic expressions to perform calculations on data Use the following within your SQL statements:
Numeric functions Character functions Date functions Conversion functions Group functions Arithmetic Expressions Arithmetic expressions allow you to perform calculations on data: SELECT ENGLISH, ENGLISH+ 100 FROM TEST;
ENGLISH ---------550 530
ENGLISH+100 -------------650 630
TATA CONSULTANCY SERVICES
Title Overview on Oracle SELECT English, math, English+math "TOTAL" FROM test; ENGLISH MATHS TOTAL ---------- --------- -------- 550 480 1030 530 580 1110
Order Of Evaluation
The RDBMS evaluates each arithmetic expression in the following precedence: Division Multiplication Addition Subtraction To override the precedence, use parentheses
Example :
In this example, 100 is divided by 12, and then is added to the balance SELECT balance, balance + 100/12 from emp; BALANCE BALANCE+100/12 1500.5 1508.8333 TATA CONSULTANCY SERVICES
Title Overview on Oracle In the second example, 100 is added to the balance, and then the total is divided by 12 SELECT balance, (balance + 100)/12 FROM emp;
BALANCE -------------1500.5
(BALANCE+100)/12 --------------------------133.375
Numeric Functions ABS(n)
Returns the absolute value of n. SELECT ABS(-32) FROM DUAL; ◆
ABS(-32) --------32
CEIL(n)
Returns smallest integer greater than or equal to n
TATA CONSULTANCY SERVICES
Title Overview on Oracle SELECT CEIL(12.8) FROM DUAL; CEIL(12.8) ------------- 13 FLOOR(n) Returns largest integer equal or less than n SELECT FLOOR(12.8) FROM DUAL; FLOOR (12.8) ----------- 12 MOD(m,n) Returns the remainder of m divided by n SELECT MOD(5,2) FROM DUAL; MOD(5,2) ------- 1
TATA CONSULTANCY SERVICES
Title Overview on Oracle POWER(m,n) Returns m raised to the nth power SELECT POWER(10,2) FROM DUAL; POWER(10,2) ---------------- 100 ROUND(n [,m]) Returns n rounded to m places right of the decimal point SELECT ROUND(15.67,1) FROM DUAL; ROUND(15.67,1) ------------- 15.7 SIGN(n) If n < 0, the function returns -1; if n =0, the function returns 0; if n>0, then the function returns 1 SELECT SIGN(-15) FROM DUAL; SIGN(-15) -------- -1 TATA CONSULTANCY SERVICES
Title Overview on Oracle TRUNC(n [,m])
Returns n truncated to m decimal places SELECT TRUNC(16.99,1) FROM DUAL; TRUNC(16.99,1) ------------- 16.9
Character Functions
ASCII(char)
Returns the ASCII value for the given character SELECT ASCII('A') FROM DUAL; ASCII('A') --------- 65 CHR(n) Returns the character having ASCII value n SELECT CHR(65) FROM DUAL; C --- A TATA CONSULTANCY SERVICES
Title Overview on Oracle CONCAT(m,n) or ||' '||
Merges together two fields, specified by m and n SELECT CONCAT(first_name,last_name) "Name" FROM name_list; Name --------------- SatyaDash
INITCAP(char)
Returns char with the first letter of each word in uppercase and all others lowercase SELECT INITCAP('BASEBALL') FROM DUAL; INITCAP( -------------- Baseball
LOWER(char)
Returns char, with all letters forced to lowercase SELECT LOWER('BASEBALL') FROM DUAL; LOWER('B -------------- baseball
TATA CONSULTANCY SERVICES
Title Overview on Oracle UPPER(char)
Returns char, with all letters forced to uppercase SELECT UPPER('big letters') "Upper Case" FROM DUAL; Upper Case ---------- BIG LETTER
LPAD(char1, n [,char2]) Returns char1, left-padded to length n with the sequence of characters in char2
SELECT LPAD(pidm,8,0) "PIDM" FROM emp; PIDM ------------- 00012340
RPAD(char1, n [,char2])
Returns char1, right-padded to length n with sequence in char2 SELECT RPAD(last_name, 20, '. ')||id "ID Listing" FROM swriden WHERE change_ind IS NULL; ID Listing ----------------------------------Brown...........157834585 Smith...........3539543 TATA CONSULTANCY SERVICES
Title Overview on Oracle LTRIM(char [, set]) Removes characters from the left of char SELECT LTRIM('123ABC123','123') FROM DUAL; LTRIM( -----ABC123
RTRIM(char [, set])
Removes characters from the right of char SELECT RTRIM('123ABC123','123') FROM DUAL;
RTRIM( ----- 123ABC SUBSTR(char, m [,n]) Returns a portion of char, beginning at character m, n characters long (if n is omitted, to the end of char) SELECT SUBSTR('Systems and Computer Technology',1,25) "Substring" FROM DUAL; Substring ------------------------Systems and Computer Tech
TATA CONSULTANCY SERVICES
Title Overview on Oracle LENGTH(char)
Returns the length of char SELECT length('abc') FROM DUAL;
LENGTH('ABC') ------------ 3
Date Functions
ADD_MONTHS(d, n)
Returns the date d plus n months. Can be negative to subtract months SELECT ADD_MONTHS('10-DEC-97',2) FROM DUAL;
ADD_MONTH --------10-FEB-98 LAST_DAY(d) Returns the date of the last day of the month that contains d SELECT LAST_DAY('15-JAN-97') FROM DUAL; LAST_DAY( --------31-JAN-97 TATA CONSULTANCY SERVICES
Title Overview on Oracle TRUNC(d, [,fmt])
Returns d with the time portion on the day truncated the unit specified by the format model fmt SELECT TRUNC(TO_DATE('04-FEB-97'), 'YEAR') "Date" FROM DUAL;
Date -------- 01-JAN-97
Conversion Functions TRANSLATE (char, from, to)
Returns char with all occurrences of each character in from replaced by its corresponding character in to SELECT TRANSLATE ('PERRY','P','J') FROM DUAL;
TRANS --------------- JERRY SELECT TRANSLATE ('ABC123ABC123','B3','X0') "Translate" FROM DUAL; Translate -------------------AXC120AXC120 TATA CONSULTANCY SERVICES
Title Overview on Oracle NVL(expr1, expr2) NULLs in a fields can give unexpected results; it’s best to convert the NULL into another value SELECT NVL(sat_math,0), NVL(sat_verbal,0), NVL(sat_math,0) + NVL(sat_verbal,0) "Tot Score" FROM swrtest; NVL(SAT_MATH,0) NVL(SAT_VERBAL,0) -----------------------------------------------------480 550 580 530 0 520
Tot Score -----------1030 111 520
DECODE(expr, search1, result1, [search2, result2,]...[default]) Converts the retrieved value from the database (search value) to a value that you specify (result2) SELECT pidm,DECODE(sex,'F','Female', 'M','Male','Unknown') "Gender" FROM swbpers; PIDM -------12340 12341 12345
Gender ---------Female Male Unknown
SELECT DECODE(refund, 'Y', amount * -1, amount) FROM ... TATA CONSULTANCY SERVICES
Title Overview on Oracle
INSTR(char1, char2 [, n [ ,m]]) Searches char1 beginning with its nth character for the mth occurrence of char2 and returns the position of the character in char1 that is the first character of this occurrence SELECT INSTR('MISSISSIPPI','SS') FROM DUAL;
STRING --------3
REPLACE(char, search_string [, replace_string]) Returns char with every occurrence of search_string replaced with replacement_string SELECT REPLACE('100 Lakeshore Drive #3', '#','No. ') "Changes" FROM DUAL; Changes ------------------------100 Lakeshore Drive No. 3
TO_DATE(char [,fmt]) Converts a character value to a date value. If the fmt clause is omitted, then the character value must have the default format of 'DD-MON-YY' INSERT INTO swvterm VALUES (20008,'Fall Semester 2000', TO_DATE('010197','DDMMYY'));
TATA CONSULTANCY SERVICES
Title Overview on Oracle Group Functions Allow you to obtain summary information about groups of rows All group functions, except COUNT, ignore null values
AVG(n) Returns the average value of n SELECT AVG(gpa) FROM swrregs; AVG(GPA) -------------2.6052632
COUNT( { * | expr }) Returns the number of rows in a query. If specifying a count on a particular column, the null values are not included SELECT COUNT(*) FROM swrregs; COUNT(*) --------------26 SELECT COUNT(gpa) FROM swrregs; COUNT(GPA) -------------------19 TATA CONSULTANCY SERVICES
Title Overview on Oracle
MAX(expr) Returns the maximum value of expr SELECT MAX(gpa) FROM swrregs; MAX(GPA) --------4
MIN(expr) Returns the minimum value of expr SELECT MIN(gpa) FROM swrregs; MIN(GPA) --------0
SUM(n) Returns sum of values of n SELECT SUM(gpa) FROM SWRREGS; SUM(GPA) --------49.5
TATA CONSULTANCY SERVICES
Title Overview on Oracle Comparison operators Operators are used in WHERE clauses to compare values. Operator
Function
Examples
()
Overrides precedence
SELECT ( X + Y) / (X - Y)
=
Test for equality
...WHERE last_name = ‘SMITH'
!=, ^=, <>
Test for inequality
...WHERE last_name <>‘SMITH'
>
Greater than
...WHERE sat_verbal > 450
>=
Greater than or equal to
...WHERE sat_verbal >= 450
<
Less than
...WHERE sat_math < 450
<=
Less than or equal to
...WHERE sat_math <= 450
Example : SELECT ROWNUM, pidm, last_name FROM swriden WHERE ROWNUM < 5; SELECT * from employee where emp_id = 121245 ; TATA CONSULTANCY SERVICES
Title Overview on Oracle Pseudocolumns
SEQUENCES CURRVAL and NEXTVAL ROWID ROWNUM A pseudocolumns behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.
SEQUENCES
A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary keys.
CURRVAL and NEXTVAL CURRVAL returns the current value of a sequence NEXTVAL increments the sequence and returns the next value
TATA CONSULTANCY SERVICES
Title Overview on Oracle Example :
SELECT ename FROM emp WHERE empno = eno.CURRVAL;
UPDATE emp SET empno = eno.NEXTVAL WHERE empno = 7655 ;
ROWID For each row in the database, the ROWID pseudocolumn returns a row’s address. ROWID
values contain information necessary to locate a row.
Example : SELECT ROWID, ename FROM emp WHERE deptno = 20 ;
ROWNUM For each row returned by a query, the ROWNUM pseudocolumn returns a number.
Example : SELECT * FROM emp WHERE ROWNUM < 10 ; TATA CONSULTANCY SERVICES
Title Overview on Oracle Joins A join is a SELECT statement that combines rows from two or more tables and/or views. Oracle performs joins whenever multiple tables appear in the FROM clause of a SELECT statement.
EQUI-JOIN Returns rows from two or more tables based on an equality condition. SELECT select_list FROM table1 [,table2] [,table3] … WHERE [AND
table1.column = table2.column table1.column = table3.column] …
Example : SELECT last_name||', '||first_name||' '||mi NAME, street_line1||' '|| city||', '||stat_code||' '|| zip ADDRESS FROM swriden, swbaddr WHERE swriden.pidm = swbaddr.pidm AND change_ind IS NULL;
OUTER-JOINS An outer join returns all the rows returned by an equi-join as well as those rows from one table that do not match any rows from the other table. The table that might not contain the matching data is appended with a ‘(+)’ in the WHERE clause. TATA CONSULTANCY SERVICES
Title Overview on Oracle Example : SELECT last_name||', '||first_name||' '||mi NAME,street_line1||' '||city||', '||stat_code||' '|| zip ADDRESS FROM swriden, swbaddr WHERE swriden.pidm = swbaddr.pidm (+) AND change_ind IS NULL;
SELF-JOINS There may be cases when you will need to join a table to itself. This is especially helpful when finding duplicates. In order to join a table to itself you must use table aliases. Example : SELECT A.term_code, A.description, B.term_code, B.description FROM swvterm A, swvterm B WHERE A.term_code = B.term_code AND A.description <> B.description;
SET OPERATORS UNION
To return all distinct rows retrieved by either of the queries. Example : SELECT deptno FROM emp UNION SELECT deptno FROM dept ; TATA CONSULTANCY SERVICES
Title Overview on Oracle UNION ALL To return all rows retrieved by either of the queries Example : SELECT deptno FROM emp UNION ALL SELECT deptno FROM dept ;
INTERSECT To return only rows retrieved by both of the queries Example : SELECT deptno FROM emp INTERSECT SELECT deptno FROM dept ;
MINUS To return all rows retrieved by first query that are not in the second query Example : SELECT deptno FROM emp MINUS SELECT deptno FROM dept ;
TATA CONSULTANCY SERVICES
Title Overview on Oracle Data Transaction Language COMMIT In order for changes to become permanent they must be committed to the database. The COMMIT command makes database changes permanent. ROLLBACK allows us to discard or abandon changes. The change made to the database between two commit commands therefore make up a transaction. Until a transaction is committed none of its changes are visible to other users. ROLLBACK Uncommitted changes may be abandoned by typing ROLLBACK. ROLLBACK will return data to the state it was I immediately after your last COMMIT by discarding all changes made since the last commit. SAVEPOINT Save point allows you to arbitrarily “hold” your work at any point in time with the option of later committing that work or undoing all or a portion of it. Thus for a long transaction you can save parts of it as you proceed finally committing or rolling back. If you make an error you need not resubmit every statement. If you create a second save point with the same name as an earlier save point the earlier save point is deleted. The maximum number of save points per user process defaults to 5. This limit can be changed. TATA CONSULTANCY SERVICES
Title Overview on Oracle Data Control Language GRANT To grant privileges for a particular object to users and roles. To grant system privileges and roles, use the GRANT command. You must own the object or the owner of the object must grant you the object privileges with the GRANT option. This rule applies to users with the DBA role. Grants all the privileges for the object that you have been granted with the GRANT OPTION. GRANT object_priv [ALL] [(column)] ON [schema.] object TO user [PUBLIC] WITH GRANT OPTION; REVOKE To revoke object privileges from users and roles, use the REVOKE command. You must have been granted the system privilege or role with the ADMIN OPTION. Also, you can revoke any role if you have the GRANT ANY ROLE system privilege.
TATA CONSULTANCY SERVICES
Title Overview on Oracle Views A View is a logical table based on a query. Views themselves do not store data, but present data from other sources. Data can be queried, inserted, updated, and deleted from views. Views simplify the presentation of data by hiding the actual structure of the base table. CREATING VIEWS Views are used for these purposes : To provide an additional level of table security, by restricting access to a predetermined set of rows and/or columns of a base table To hide data complexity. A view may be used to act as one table when actually several tables are used to construct the results To present data from another perspective. For example, views provide a means of renaming columns without actually changing the base table’s definition CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.] view [ (alias [, alias] .. ) ] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] Example : CREATE OR REPLACE VIEW swvsusp AS SELECT * FROM swrstdn WHERE stdn_code = 'SS' WITH CHECK OPTION; TATA CONSULTANCY SERVICES
Title Overview on Oracle The WITH CHECK OPTION clause of the CREATE VIEW statement restricts users from inserting data violating the WHERE clause of the views query. CREATE OR REPLACE VIEW view_name SELECT attributes FROM table_name WHERE attribute IN (condition,condition..,….) WITH CHECK OPTION ; The name assigned to the CHECK OPTION constraint. If the constraint is omitted, then Oracle automatically assigns the constraint a name of the form, SYS_cn, where n is an integer that makes the constraint name unique within the database.
TATA CONSULTANCY SERVICES
INTRODUCTION TO PL/SQL
TATA CONSULTANCY SERVICES
Title Overview on Oracle OVERVIEW Oracle’s procedural language extension to Oracle SQL Block structured language Bridges the gap between database technology and procedural programming language Contains all SQL DML functionality Processed by it’s own PL/SQL engine The PL/SQL engine is incorporated into following products Oracle Forms SQL* Menu Oracle Reports Oracle Graphics
TATA CONSULTANCY SERVICES
Title Overview on Oracle PL/SQL ARCHITECTURE
PL/SQL is a technology and an integrated part of oracle This technology is an engine that executes PL/SQL blocks and subprograms PL/SQL resides in 2 environments (Oracle Server & Oracle Tool) Engine executes procedural statements but sends SQL statement to SQL statement executor
PL/SQL Block In Application
PL/SQL Engine SQL Statement Executor
ORACLE Server (PL/SQL Engine & Oracle Server) TATA CONSULTANCY SERVICES
Title Overview on Oracle PL/SQL ARCHITECTURE
ORACLE Forms
PL/SQL Block
PL/SQL Engine SQL Statement Executor PL/SQL Engine ORACLEServer
(PL/SQL Engine & Oracle TOOL) Advantages Of PL/SQL What PL/SQL can do for Programmers 1.Support for SQL 1. SQL data manipulation statement 2.Support for OOP 2. SQL transaction processing statement 3.Better Performance 3. SQL functions 4.High Productivity 4. SQL predicates 5.Full Portability 6.Tight Security & Integration with Oracle TATA CONSULTANCY SERVICES
Title Overview on Oracle PL/SQL BLOCK STRUCTURE A PL/SQL block has 3 parts Declaration Part (All objects of the block declared here) Execution Part (The objects are defined to manipulate data) Exception Part (Error-Handling logic is placed here) The structure of a PL/SQL block looks like DECLARE Declarations BEGIN Executable Statements EXCEPTION Error Handling Statements END; Notes: Blocks can either be simple or nested (Any block may contain sub-blocks) Statements end with a semicolon(;) Comments are preceded by -- for inline comments or surrounded by /*…..*/ Declared objects exists within a certain scope Two types of declarations a) CONSTANT and b)VARIABLE Data types available are NUMBER family , CHAR family , DATE, BOOLEAN and LOB family Variables can be declared with attributes %TYPE and %ROWTYPE Assignment to the variable is done with := Self Variables can also be declared with SUBTYPE keyword Syntax : IDENTIFIER [CONSTANT] datatype [NOT NULL] [:=PlSql Expression]; TATA CONSULTANCY SERVICES
Title Overview on Oracle SCOPE & VISIBILITY OF VARIABLE Scope of an identifier is the region of Program Unit (Block, Sub Program or Package) from which you can reference the identifier Visibility of an identifier is the region from which you can reference the identifier using an Unqualified name SCOPE VISIBILITY DECLARE X REAL; BEGIN ………… DECLARE X REAL; BEGIN ……….. END; …………. END;
DECLARE X REAL; BEGIN ………….. DECLARE
Solid Line Indicates OUTER X
X REAL;
Dotted Line Indicates INNER X
BEGIN ……….. END; …………… END;
Identifiers declared in a PL/SQL block are considered local to that block and global to all subblocks. Can not be declared twice in the same block. If a global identifier is re-declared in a sub-block, both identifiers remain in scope however within the sub-block only local identifier is visible because you must use a qualified name to reference the global variable A block can not reference identifiers declared in other blocks nested at same level. TATA CONSULTANCY SERVICES
Title Overview on Oracle SCOPE & VISIBILITY OF VARIABLE <> DECLARE a CHAR; b CHAR; BEGIN --------Identifiers available here are a (CHAR) & b <<subblock1>> DECLARE a INTEGER; c REAL; BEGIN -------Identifiers available here are a (INTEGER), b & c if a = To_Number(outer.a) THEN …. Legal as it references the variable of parent block END; <<subblock2>> DECLARE d REAL; BEGIN--------Identifiers available here are a (CHAR), b & d if d = subblock1.a THEN -- Illegal as it can not identifiers identifier declared in subblock1 -- which is declared in the same label as subblock2. END; -------Identifiers available here are a (CHAR) & b END; TATA CONSULTANCY SERVICES
Title Overview on Oracle CONTROL STRUCTURE
There are 3 control structures available in PL/SQL Conditional/ Selection (IF & CASE statements solves) Iterative (LOOP & EXIT statement solves) Sequential (GOTO & NULL statement solves)
T
F
F T
Condition
Iteration
Sequence
Conditional/Selection: To conditionally execute a statement or set of statements. IF & CASE statements are used for this purpose. There are 3 forms of IF statement. IF - THEN, IF - THEN ELSE & IF - THEN - ELSIF Anything compared with NULL(THREE VALUED LOGIC) results in a NULL value. LOOP executes a statement or sequence of statements multiple times. There are 3 forms of LOOP statement. 1) LOOP 2)WHILE - LOOP 3) FOR - LOOP TATA CONSULTANCY SERVICES
Title Overview on Oracle CONTROL STRUCTURE Example & Syntax Of Conditional/Selection Statements (IF & CASE) IF CASE IF THEN CASE <Expression> <Sequence Of Statements> WHEN THEN <Statement ELSIF THEN --WHEN May be Repeated <Sequence Of Statements> ELSE <Statement> END -ELSIF May be Repeated [ELSE * CASE Statements are put in SELECT Statements * CASE Statements uses a selector rather than multiple <Sequence Of Statements>] boolean exprn. to select seq. Of statements to execute END IF; Example IF grade=‘4’ THEN dbms_output.put_line(‘Excl ’); CASE grade ELSIF grade=‘3’ THEN dbms_output.put_line(‘Expt’); WHEN ‘4’ THEN dbms_output.put_line(‘Excl’); ELSIF grade=‘2’ THEN dbms_output.put_line(‘Expr’) WHEN ‘3’ THEN dbms_output.put_line(‘Expt’); ELSIF grade=‘1’ THEN dbms_output.put_line(‘Exps’); WHEN ‘2’ THEN dbms_output.put_line(‘Expr’); ELSE dbms_output.put_line(‘Training’); WHEN ‘1’ THEN dbms_output.put_line(‘Exps’); END IF; ELSE dbms_output.put_line(‘Training’) END; ITERATIVE STATEMENTS 1. (LOOP & EXIT) (Simple Loop) Encloses a sequence of statements between keyword LOOP and END LOOP. Exit statement exit any type of loop immediately and program control goes to the immediate statement after the loop. Syntax for Simple LOOP and EXIT statement is as follows. LOOP 1. EXIT; <Sequence Of Statements> 2. EXIT WHEN END LOOP;
TATA CONSULTANCY SERVICES
Title Overview on Oracle CONTROL STRUCTURE Examples Of LOOP & EXIT Statement LOOP LOOP count = count + 1 count = count + 1 IF count > 100 THEN EXIT WHEN count > 100; EXIT; --Exists the current Loop ………………….. END IF; END LOOP; ………………….. END LOOP; LOOP can also be labeled which sometimes helps in exiting of the outer LOOP also. Example:1 Example: 2 <<my_loop>> <> LOOP LOOP ………… …………….. END LOOP my_loop; <> LOOP Note: Label Name After Keyword EXIT outer WHEN ; --Exits Both Loop END LOOP is not mandatory. END LOOP; END LOOP outer; ITERATIVE STATEMENTS 2. WHILE LOOP WHILE LOOP repeats a sequence of statements until a specified condition is no longer true. Note: 1. The Term may be an PL/SQL Condition Syntax : WHILE LOOP (must return Boolean Value or NULL. <Sequence Of Statements> 2. The Sequence of Statements will be repeated as long END LOOP; evaluates to TRUE.
TATA CONSULTANCY SERVICES
Title Overview on Oracle CONTROL STRUCTURE ITERATIVE STATEMENTS 3. FOR LOOP Repeats a sequence of statements a fixed number of times. Syntax: FOR IN [REVERSE] lower_bound .. Higher_bound LOOP <Sequence Of Statements> END LOOP; Example Showing Use Of WHILE LOOP & FOR LOOP DECLARE FOR count IN 1..100 LOOP count Number(3) :=0; …………... BEGIN END LOOP; •The loop index take on each value in range, one at a time, either in forward WHILE count <= 100 LOOP direction or Reverse order. …….. •The loop index is implicitly of type NUMBER Count = count + 1; •The loop index is only defined within the loop. Can not reference it outside Loop. END LOOP; •The range of loop (lower_bound & higher_bound) must evaluate to numbers END; otherwise EXCEPTION (VALUE_ERROR) will be raised by Oracle. SEQUENTIAL CONTROL STATEMENTS 1. NULL It does nothing other than pass the control to the next statement. Syntax is NULL; NULL statement and Boolean Non-Value NULL are different You can use NULL in the IF condition for doing nothing in any branch of the condition. SEQUENTIAL CONTROL STATEMENTS 2. GOTO The GOTO statement branches to a label unconditionally. Syntax is GOTO The label name must be unique within its scope and must precede an executable statement or PL/SQL block. GOTO works in the sequence of statements that encloses the GOTO statement.
TATA CONSULTANCY SERVICES
Title Overview on Oracle CONTROL STRUCTURE Example Showing Use Of GOTO & NULL statement BEGIN DECLARE ……………… <> BEGIN BEGIN UPDATE emp SET …. END; ……………….. GOTO Update_Row; --legal END;
done BOOLEAN :=TRUE;
<> FOR I IN 1..50 LOOP IF done THEN GOTO End_Loop; END IF; …………… <<End_Loop>> --Illegal as does not precede END LOOP Outer; Executable statement Restrictions On GOTO END; From an inner block, LOOP or IF statement (1) From an IF clause to other IF clause(3) From an exception handler block into the current block (2) BEGIN BEGIN BEGIN GOTO abc; ………….. IF X > 3 THEN BEGIN <> GOTO abc; ………… Example1 ………….. ELSE Example2 Example3 <> EXCEPTION <> END; WHEN OTHERS THEN …………….. …………… GOTO abc; END IF; END; END; END;
TATA CONSULTANCY SERVICES
Title Overview on Oracle SUB PROGRAM
Named PL/SQL blocks that can take parameters and can be invoked. PL/SQL has 2 types of Sub Programs 1. PROCEDURE & 2.FUNCTION PROCEDURE Named PL/SQL block that can take parameters, perform an action and can be invoked. Generally used to perform an action and to pass values FUNCTION Named PL/SQL block that can take parameters, perform an action and return a value. Syntax For Creating PROCEDURE Syntax For Creating FUNCTION CREATE OR REPLACE PROCEDURE [schema.] procedureName --Same-(argument {IN, OUT, IN OUT} datatype [{:=,DEFAULT} initial value],…) {IS,AS} --As Procedure-Variable Declarations; --Except RETURN-Constant Declarations; --CLAUSE-BEGIN --Before {IS,AS} -PL/SQL Sub Program Body That means RETURN {IS,AS} EXCEPTION Exception PL/SQL Block END; Procedures/Functions are made up of a) Declarative Part b)Executable Part c)Optional Exception Handling Part There are 3 types of Argument Modes IN : Data values come in from the calling process and does is not changed (The default) OUT : No Data values comes. On Normal exit value of argument is passed back to caller. IN OUT: Data value comes in from calling process & another value is returned on normal exit. ADVANTAGES : 1. Security 2. Performance 3. Memory Allocation 4. Productivity 5. Integrity
TATA CONSULTANCY SERVICES
Title Overview on Oracle SUB PROGRAM
CREATE Any PROCEDURE/FUNCTION system privilege is required to create Pro/Fun. A PROCEDURE accepts one or more values as parameter (or None at all) and can return one or more values(or None at all) whereas a function accepts one or more values as parameters (or None at all) but always returns a result. Parameter data types can not be of type PLS_INTEGER. In Procedure/function it is illegal to constrain char/varchar/varchar2 parameters with a length and number parameters with a precision and/or scale. Names of Sub Programs reside in System View USER_OBJECTS, ALL_OBJECTS and DBA_OBJECTS and source code in USER_SOURCE, ALL_SOURCE, DBA_SOURCE. There are 3 ways of passing parameters 1. Positional 2.Named 3. Combination of these If a parameter has a default value then it does not have to be passed from calling envn. RETURN statement can also be used in PROCEDURE. But it should not contain any expression. The statement simply returns control to the caller before normal end of pro. A Sub Program Can contain several RETURN statements. Executing any of them completes the sub program immediately. The RETURN statement used in FUNCTION must contain an expression, which is evaluated, when the statement is executed. To drop a Sub Program Syntax is : DROP ; After dropping a sub program it marks dependent objects for re-compilation. Valid Statements inside a Sub Program body are SQL DML/PL-SQL, calls to other procedures/functions in the same database/remote database. To re-compile a Sub Program Syntax Is: ALTER <Sub Prog Type> COMPILE; To View errors during creation Syntax Is: SHO[W] Err[ors] OR From System Error Views. TATA CONSULTANCY SERVICES
Title Overview on Oracle SUB PROGRAM
Any Sub Program can raise an error and return a user-defined error message and error number using the syntax RAISE_APPLICATION_ERROR(Error_Number, Error_Message); Reasons for Re-Compilation could fail are Changing parameter list in called procedure Changing definition of or removing referenced columns from referenced table. Dropping referenced tables. If a Sub Program is called with Mixed Notation (POSITIONAL & NAMED) then first arguments must be specified by Position. Parameters are of 2 Types 1. ACTUAL and 2. FORMAL How ORACLE Creates a Stored PROCEDURE/FUNCTION Compiles the PROCEDURE/FUNCTION Stores the compiled code Stores the PROCEDURE/FUNCTION in the database. How ORACLE Executes a Stored PROCEDURE/FUNCTION Verifies the Use Access (Checks for EXECUTE ANY PROCEDURE/FUNCTION Privilege) Verifies the PROCEDURE Validity (Status In USER_OBJECTS System View) Executes the PROCEDURE/FUNCTION (EXEC[UTE] ) Example CREATE OR REPLACE PROCEDURE fire_employee(empId IN NUMBER,empName OUT CHAR) BEGIN To Call This Procedure From SQL*PLUS DELETE FROM empMst WHERE Id = empId AND Name=empName; EXEC fire_employee(ID,NAME); END; TATA CONSULTANCY SERVICES
Title Overview on Oracle PACKAGE A group of related PROCEDURES, FUNCTIONS, CURSOR definitions, VARIABLES, CONSTANTS and EEXCEPTION Definitions. Made up of 2 Components. 1.PACKAGE BODY & 2. PACKAGE SPECIFICATION. The specification is an INTERFACE to an application and has declarative statements. The PACKAGE BODY contains different PROCEDURES/FUNCTIONS. SYNTAX TO CREATE A PACKAGE 1. PACKAGE SPECIFICATION. CREATE [OR REPLACE] PACKAGE [schema.] Package_name IS/AS END [Package_Name]; SYNTAX TO CREATE A PACKAGE 2. PACKAGE BODY CREATE [OR REPLACE] PACKAGE BODY [schema.] Package_Body IS/AS END [Package_Body]; ADVANTAGES OF PACKAGE 1. Modularity 2. Easier Application Design 3.Information Hiding 4. Added Functionality 5. Better Performance CONSTRUCTS In PACKAGE Public Package Constructs : Declared In PACKAGE SPECIFICATION Private Package Constructs :Only declared in PACKAGE BODY TATA CONSULTANCY SERVICES
Title Overview on Oracle PACKAGE There are 3 types of variables in a Package Local Variables: Only Exists within the PROCEDURE/FUNCTION defined in a PACKAGE BODY. Local Static Variables: Defined within a Package BODY. Exists throughout the session Global Static Variables: Defined in the Package SPECIFICATION & can be accessed outside body. The Package BODY contains all the code bodies for all the procedures and functions named in the Package SPECIFICATION but the converse may not be TRUE. Before compiling the Package BODY, Package SPECIFICATION must be compiled. Package BODY has a dependency on the Package Specification but the converse is not true. Package may have ONE-TIME PROCEDURES, which is executed only once when you submit the package body to Oracle for Compilation. If you are trying to call a SUB-PROGRAM which is not defined yet then, FORWARD REFERENCE/DECLARATION is needed. You can OVERLOAD Procedures/Functions inside a PACKAGE as long as their formal parameters differ in Number, Order or Data-type Family. You can not Overload 2 Sub-Programs if their parameters differ only in NAME or MODE. You can not Overload 2 Sub-Programs based on their RETURN TYPE only. For OVERLOADED Sub-Programs, the PRAGMA RESTRICT_REFERENCES applied to the nearest definition prior to the PRAGMA. TATA CONSULTANCY SERVICES
Title Overview on Oracle PACKAGE Example CREATE OR REPLACE PACKAGE Journal_Entries IS g_s_var1 Number(2); --Global Static Variable PROCEDURE Journalize(amount REAL, trans_date VARCHAR2); PROCEDURE Journalize(amount REAL, trans_date INT); --Overload Journalize PRAGMA RESTRICT_REFERENCES(Journalize,RNDS) --Applies to Journalize(amount REAL, trans_date INT) END Journal_Entries; CREATE OR REPLACE PACKAGE BODY Journal_Entries IS l_s_var1 Number(2); --Local Static Variable. PROCEDURE Journalize(amount REAL, trans_date INT); --Forward Declaration PROCEDURE Journalize(amount REAL, trans_date VARCHAR2) IS l_var1 Number(2); --Local Variable. BEGIN INSERT INTO Journal VALUES (amount, TO_DATE(trans_date,’J’), l_s_var1); Journalize(12,2030); END Journalize; PROCEDURE Journalize(amount REAL, trans_date INT) IS BEGIN INSERT INTO Journal VALUES(amount,trans_date, g_s_var1); END Journalize; BEGIN --One-Time Procedure l_s_var1 :=2; g_s_var1 :=l_s_var1; END Journal_Entries;
--Assign value to local and global Static Variables.
TATA CONSULTANCY SERVICES
Title Overview on Oracle ERROR HANDLING IN PL/SQL
Error in PL/SQL is called EXCEPTION also. When an ERROR occurs an EXCEPTION is raised, then normal execution stops and control transfers to the Exception Handling part of PL/SQL SYNTAX : WHEN <Exception Name> [OR <Exception Name> …] THEN <Sequence Of Statements> Exceptions are of 2 types Pre-Defined Exception (System Defined Exceptions & Internal Exceptions (Raised Implicitly)) User-Defined Exception (Declare Exception & Raise Exception Explicitly) Main advantages of Using EXCEPTION are Eliminates needs to code multiple checks Improves readability System defined exceptions (NO_DATA_FOUND, DEVIDE_BY_ZERO etc..) are raised internally or can be caught using PRAGMA EXCEPTION_INIT. Exceptions ate caught inside Exception Handler Block. One Pre-Defined Exception called OTHERS is used to handle all errors not handled in the block, which must be the last exception handler inside the block. Sometimes it is necessary to know what error has exactly occurred. Oracle provides 2 functions SQLCODE (Numeric) and SQLERRM (Character) to solve it. Same exceptions can not be declared twice in the same block. Exceptions declared inside a block are considered local to that block and global to all it’s sub-blocks. Globally defined excns are referred inside a sub-block using the syntax as
TATA CONSULTANCY SERVICES
Title Overview on Oracle ERROR HANDLING IN PL/SQL
DECLARE myException Exception; --Declared User Defined Exception empNo VARCHAR2(5); BEGIN SELECT Emp_No INTO empNo FROM Emp WHERE ename = ‘BLAKE’; IF empNo = ‘99999’ THEN RAISE myException; --Raise User Defined Exception END IF; EXCEPTION WHEN myException THEN dbms_output.put_line(‘INVALID EMPLOYEE…’); WHEN NO_DATA_FOUND THEN dbms_output.put_line(‘No Employee Found’); WHEN OTHERS THEN dbms_output.put_line(‘Oracle Error ‘||SQLERRM||’ And Error Message ‘||SQLERRM); END;
Exception Propagation
Step #1. The Current block is searched for a handler. If not found then go to Step #2. Step #2. If an enclosing block is found then it is searched for a handler. Step #3. Step #1 and Step #2 are repeated until either there are no enclosing blocks or a handler is found. If there are no more enclosing blocks, the Exception is passed back to the calling environment. If a handler is found, it is executed. When done, the block in which the handler was found is terminated, and control is passed to the enclosing block (if one exist) or to the environment (If there is no enclosing block).
TATA CONSULTANCY SERVICES
Title Overview on Oracle ERROR HANDLING IN PL/SQL
An Example Showing How ERROR propagates BEGIN …………… BEGIN IF X = 1 THEN RAISE A; --Exception A is handled in the inner block and execution resumes in Outer block ELSIF X = 2 THEN RAISE B; --Propagates to the first outer block with appropriate handler. Then handled in outer block and control is passed back to the calling environment. ELSE RAISE C; --Has no handler and will result in a Runtime unhandled Exception. EXCEPTION WHEN A THEN ………………… END; ……………. Questions2 Questions3 EXCEPTION
WHEN B THEN ………………… END; Questions1. DECLARE Var1 Number(3) :=‘ABC’; BEGIN …… EXCEPTION WHEN OTHERS THEN ………. END;
DECLARE DECLARE myExc1 Exception myExc1 Exception myExc2 Exception BEGIN BEGIN RAISE myExc1; RAISE myExc1; EXCEPTION EXCEPTION WHEN myExc1 THEN WHEN myExc1 THEN ………… RAISE myExc2; RAISE; WHEN myExc2 THEN END; ….. END; Q. Can The blocks catch the Exception in above questions??
TATA CONSULTANCY SERVICES
Title Overview on Oracle PRAGMA
Is a Compiler Directive Conveys the Information To the compiler Processed at Compiled time not at the Run time. Do not affect meaning of a program There are 4 types of PRAGMAS EXCEPTION_INIT RESTRICT_REFERENCES AUTONOMOUS_TRANSACTION SERIALLY_REUSABLE EXCEPTION_INIT Associates an Exception Name with an Oracle Error Number. Lets you refer to any internal exception by name and write a specific handler for it instead of using OTHERS handler. EXAMPLE DECLARE myException EXCEPTION; h_Date DATE; PRAGMA EXCEPTION_INIT(myException, -01843) --Define The Exception BEGIN SELECT TO_DATE(‘14/14/2003’,’DD/MM/YYYY’) INTO h_Date FROM DUAL; EXCEPTION WHEN myException THEN dbms_output.put_line(‘Defined Exception Caught Here…’); WHEN OTHERE THEN dbms_output.put_line(‘Error ..’||SQLERRM||’ Error Message..’||SQLERRM); END;
TATA CONSULTANCY SERVICES
Title Overview on Oracle PRAGMA
RESTRICT_REFERENCES To be callable from SQL statements, a stored sub program must obey some “PURITY” rules. PURITY rules are meant to control side effects. RNDS :Read No Database Sate RNPS :Read No Package State WNDS :Write No Database State WNPS :Write No Package State EXAMPLE CREATE OR REPLACE PACKAGE TestPkg IS PROCEDURE PkgSelect; END; CREATE OR REPLACE PACKAGE BODY TestPkg IS PROCEDURE PkgSelect IS Count Number(2); BEGIN SELECT COUNT(*) INTO Count FROM TEST; dbms_output.put_line(‘Count Is…’||Count); END; END; If the user does not allow to select data from database then Mention PRAGMA in Package specification as CREATE OR REPLACE PACKAGE TestPkg IS PROCEDURE PkgSelect; PRAGMA RESTRICt_REFERENCES(PkgSelect,RNDS); END; --The Compilation of body will fail “PLS-00452:Subprogram “PKGSELECT” violates it’s ass. PRAGMA”
TATA CONSULTANCY SERVICES
Title Overview on Oracle PRAGMA AUTONOMOUS_TRANSACTION The AUTONOMOUS_TRANSACTION PRAGMA instructs the PL/SQL compiler to mark a routine as autonomous (Independent) Started by another transaction, the main transaction. Let you suspend the main transaction, so SQL operations, COMMIT or ROLLBACK these operations then resume the main transaction. Example: Let a table TEST(n NUMBER(2)) and 2 Procedures Proc1 and Proc2 are there. Proc2 is called from Proc1 PROCEDURE Proc1 IS PROCEDURE Proc2 IS BEGIN BEGIN INSERT INTO TEST VALUES (12); INSERT INTO TEST VALUES(13); Proc2; COMMIT; INSERT INTO TEST VALUES (143); --Error END; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; When the Proc1 is Executed then the table contains the values as 12,13. But suppose user wants to rollback the entire transaction in Proc1 if error comes but transaction in Proc2 to be committed I.e output should be 13 only , then define PRAGMA AUTONOMOUS_TRANSACTION in Proc2 as follows. PROCEDURE Proc2 IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN ……………. END;
TATA CONSULTANCY SERVICES
Title Overview on Oracle TRIGGER Application DATABASE INSERT INTO EMP VALUES ….
TRIGGERING EVENTS
Table EMP INSERT Trg.
UPDATE EMP SET …….
•INSERT •UPDATE •DELETE
UPDATE Trg.
TRIGGER TYPES •BEFORE/AFTER
DELETE FROM EMP WHERE ….
DELETE Trg.
•PER STATEMENT/ROW
WHAT A TRIGGER IS •A Trigger is a STORED PROCEDURE that fires implicitly when an INSERT/UPDATE/DELETE is issued against a Table. •A Trigger is made up of 3 Parts •A Triggering Event or Statement (INSERT/UPDATE/DELETE) •A Triggering Restriction (WHEN CLAUSE) •A Triggering Action (PL/SQL BODY) •Uses of Trigger •Can be used to prevent invalid transactions. •Enforce complex business rules •Derive column values automatically •Maintain replicate tables. •Gather statistics on table access
TATA CONSULTANCY SERVICES
Title Overview on Oracle TRIGGER
SYNTAX TO CREATE A TRIGGER CREATE [OR REPLACE ] TRIGGER [schema.]Trigger_name { BEFORE / AFTER } --TRIGGER TYPE { DELETE {OR} / INSERT {OR} / UPDATE [OF COLUMN1,COLUMN2,…] } --TRIGGER EVENT/STATEMENT ON [schema.]TABLE [ REFERENCING {OLD AS old,NEW AS new } ] [ FOR EACH ROW [WHEN ] ] --FOR EACH ROW is TRIGGER TYPE & WHEN is TRIGGER Restn. --Trigger Action. TYPES OF TRIGGER Row & Statement Trigger (Applicable to both TABLES and VIEWS) Before and After Trigger (Can not be used for Triggers created over VIEWS) INSTEAD-OF Trigger (Over Modifiable Views That can not be modified directly thru SQL DML stmnt) System Event & User Event Trigger (DATABASE Startup,Shutdown & USER Logon,Logoff) Notes Types of a Trigger determines The “TIME” when the trigger fires BEFORE TRIGGER (Before The Triggering Action) AFTER TRIGGER (After The Triggering Action) INSTEAD-OF TRIGGER (Instead Of The Triggering Action) The “ITEM” the trigger fires on ROW TRIGGER (Once For Each Row Affected By The Triggering Statement) STATEMENT TRIGGER (Once For The Trigg. Statement Regardless Of No. Of Rows Affected) A TRIGGER can not select from or change a MUTATING table ( Currently being modified by DML Oprn) Each TABLE Can have UPTO 12 TRIGGERS in all.
TATA CONSULTANCY SERVICES
Title Overview on Oracle TRIGGER
Notes The 12 Triggers that can be written over a Table are INSERT ROW BEFORE UPDATE AFTER STATEMENT DELETE BEFORE STATEMENT trigger is used to initialize global variables. BEFORE ROW triggers are used to compute derived fields in new ROW. Besides the above 12 TRIGGERS another 2 triggers are there which are written over views. They are INSTEADOF row Trigger and INSTEAD-OF statement Trigger. Except INSETAD-OF triggers, all triggers can be used only on TABLES. INSTEAD-OF triggers can be used only on VIEWS. Database triggers are not stored in compiled form. Instead of writing the entire trigger logic in Database trigger, write them in procedure(s). The procedure can then be called from Trigger using CALL statement which improved performance considerably. If a trigger can fire on more than one type of DML operation, use pre-defined PL/SQL Boolean variables to determine which caused the trigger to fire: 1. IF INSERTING 2. IF UPDATING [COLUMN_NAME,..] 3. IF DELETING If FOR EACH ROW Condition is there then it is ROW Type Trigger To refer to the old and new values of a column in row triggers, use the :OLD and :NEW prefixes: IF :NEW.sal < : OLD.sal ... Values available in ROW TRIGGER ONLY. NEW and OLD values available for UPDATE only. The OLD value in INSERT and NEW value in DELETE is NULL. You can replace :NEW and :OLD with other co-relation names if desired (Use REFERENCE clause) COMMIIT & ROLLBACK can not be placed in Trigger action (Except for AUTONOMOUS_TRANSACTION)
TATA CONSULTANCY SERVICES
Title Overview on Oracle TRIGGER EXECUTION HIERARCHY OF TRIGGER Execute all BEFORE STATEMENT TRIGGERS that apply to the statement LOOP for each ROW affected by SQL statement Execute all BEFORE ROW TRIGGER that apply to the statement Lock and change row and perform INTEGRITY CONSTRAINT checking Execute all AFTER ROW TRIGGER that apply to the statement Complete DEFERRED INTEGRITY CONSTRAINT checking Execute all AFTER STATEMENT TRIGGERS. CREATE OR REPLACE TRIGGER salary_check BEFORE INSERT OR UPDATE OF sal, job ON Employee FOR EACH ROW WHEN (NEW.job <> 'PRESIDENT') DECLARE minsal NUMBER; maxsal NUMBER; BEGIN IF INSERTING OR UPDATING THEN SELECT Min_Salary, Max_Salary INTO minsal, maxsal FROM sal_guide WHERE job = :NEW.JOB; IF (:NEW.sal < minsal OR :NEW.sal > maxsal ) THEN /* If salary below min or above max generate an error */ raise_application_error (-20500,'Salary' || :NEW.sal || 'out of range for job'|| :NEW.job || 'for ’||:NEW.emp); END IF; END IF; END; /* End Of Trigger*/ Difference between Triggers and Procedures is that Triggers can not take PARAMETERS whereas PROCEDURES can take. PROCEDURE is executed EXPLICITLY whereas TRIGGER is IMPLICITLY. Another difference is DECLARE keyword is required in TRIGGER to declare variables.
TATA CONSULTANCY SERVICES
Title Overview on Oracle CURSOR
A work area to execute SQL statements and store processing information. Every SQL DML statement processed by PL/SQL has an associated CURSOR. Two Types of CURSORS 1. EXPLICIT :Declared explicitly along with other identifiers to be used in the block. Multiple row SELECT Statements 2. IMPLICIT (SQL Cursor) :Declared by PL/SQL implicitly for all DML statement and single ROW queries. All INSERT, UPDATE, DELETE and SELECT ….INTO Statements EXPLICIT CURSOR USE Step1 : Declare the CURSOR DECLARE CURSOR <cursor name> IS ; The must NOT include the INTO clause. Declared cursors are scoped just like variables. Step2: Open the CURSOR OPEN OR Cursor_Variable. -- fetches values to the variable and increments the pointer to the next value. The first column gets assigned to Var1, the second assigned Var2, etc. Step4: Close The CURSOR. CLOSE <cursor name>; -- closes the cursor , and the result set becomes unidentified Once a cursor is closed ,it can be reopened . Any other operation on a closed cursor raises predefined exception INVALID_CURSOR. Note: There are TWO ways to Open ,Processing and Close a Cursor. They are 1. OPEN - FETCH - CLOSE 2. FOR LOOP
TATA CONSULTANCY SERVICES
Title Overview on Oracle CURSOR
Example (Using OPEN-FETCH-CLOSE) Using FOR LOOP DECLARE DECLARE V_E_No E_MST.E_No%TYPE; CURSOR C1 IS V_E_Name E_MST.E_Name%TYPE; SELECT E_No, E_Name CURSOR C1 IS FROM E_MST; SELECT E_No,E_Name BEGIN FROM E_MST; FOR I IN C1 BEGIN LOOP OPEN C1; INSERT INTO TEMP(No, Name) LOOP VALUES(I.E_No,I.E_Name); FETCH C1 INTO V_E_No,V_E_Name; END LOOP; EXIT WHEN C1%NOTFOUND; END; INSERT INTO TEMP(No, Name) VALUES(V_E_No,V_E_Name); Note. Here END LOOP automatically closes the END LOOP; CURSOR. CLOSE C1; END; EXPLICIT CURSOR ATTRIBUTES %NOTFOUND :Returns BOOLEAN Value. TRUE if there are no more rows to be fetched. %FOUND :Returns BOOLEAN Value. FALSE if there are no more rows to be fetched. %ISOPEN :Returns BOOLEAN Value. TRUE if the Cursor is already open. %ROWCOUNT :Returns NUMERIC Value. No. Of Rows returned by the cursor. IMPLICIT CURSOR ATTRIBUTES SQL%NOTFOUND,SQL%FOUND,SQL%ROWCOUNT and SQL%ISOPEN(Always Evaluate To False)
TATA CONSULTANCY SERVICES
Title Overview on Oracle
TATA CONSULTANCY SERVICES
Related Documents
