ORACLE PL SQL

Oracle 9i A Presentation By: Kamlesh C

Session Objectives Introduction of RDBMS - Oracle 9i What is RDBMS? Normalization Oracle9i Built-In Data types Structured Query Language Oracle Built-In Functions String Numbers Date Transformation Grouping Data Together Other Database Objects Oracle Text

Session Objectives PL/SQL What is PL\SQL Introduction of Cursors Subprograms Procedures Functions Packages Triggers

Introduction of RDBMS – Oracle 9i

What is RDBMS? What is Database Management System ? Data is just "Data" until it is organized in a meaningful way, at which points it becomes "information". Oracle is also a means of easily turning data into information. A relational database management system gives a way of doing these tasks in an understandable and reasonably uncomplicated way. It basically does three things:  Lets you put data into it.  Keeps the data  Lets you to get the data out and work with it. Note: An object relational database management system extends the capabilities of the RDBMS to support object-oriented concepts.

Normalization Normalization is process of identifying the functional dependencies between different business entities. Data is split into a number of related tables in order to minimize duplication of information. 1st Rule of normalization: Each cell should have 1 value only. 2nd Rule of normalization: Partial dependencies 3rd Rule of normalization: Transitive dependency - No non PK should transitively dependent upon PK. 4th Rule of normalization: Multi Value dependencies 5th Rule of normalization: Loss less join

Oracle Built-in Data Types A datatype associates a fixed set of properties with the values that can be used in a column of a table. These properties cause Oracle to treat values of one datatype differently from values of another datatype. For example, Oracle can add values of NUMBER datatype, but not values of RAW datatype. Oracle supplies the following built-in datatypes: Character datatypes CHAR NCHAR VARCHAR2 and VARCHAR NVARCHAR2 CLOB NCLOB LONG NUMBER datatype

Oracle Built-in Data Types (cont.) Time and date datatypes: DATE INTERVAL DAY TO SECOND INTERVAL YEAR TO MONTH TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE Binary datatypes: BLOB BFILE RAW LONG RAW Another datatype, ROWID, is used for values in the ROWID pseudo column, which represents the unique address of each row in a table . More information on Oracle Datatypes

SQL - Structured Query Language Types of SQL statements Structured Query Language (SQL) Statements Data Definition Language (DDL) Statements Data Manipulation Language (DML) Statements Data Control Language (DCL) Transaction Control (TCL) Statements Session Control Statements System Control Statements

SQL - Structured Query Language SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. The standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. SELECTING DATA SELECT [DISTINCT] "COLUMN1" [,"COLUMN2",ETC] FROM "TABLENAME" [WHERE "CONDITION"] [GROUP BY “COLUMN1,COLUMN2”, ETC] [HAVING “ARITHMATIC EXPRESSION”] [ORDER BY “COLUMN1, COLUMN2”, ETC ASC | DESC NULLS FIRST | NULLS LAST] [FOR UPDATE OF “COLUMN1” NOWAIT | WAIT [DURATION]]; [] = optional INSERTING INTO A TABLE INSERT INTO "TABLENAME" (FIRST_COLUMN,...LAST_COLUMN) VALUES (FIRST_VALUE, ...LAST_VALUE); INSERT INTO “TABLENAME1” (“COLUMN1”, “COLUMN2”, ETC.) SELECT “COLUMN1”,”COLUMN2”, ETC FROM “TABLENAME2”; Note: Corresponding column datatype should be compatible

SQL - Structured Query Language Inserting into a Table (cont.) UN-CONDITIONAL INSERT ALL : INSERT ALL INTO "TABLENAME1" VALUES ("COLUMN1","COLUMN2", ETC) INTO "TABLENAME2" VALUES ("COLUMN3","COLUMN4",ETC) SELECT "COLUMN1","COLUMN2",COLUMN3","COLUMN4" FROM "TABLENAME3" WHERE [CONDITION] CONDITIONAL INSERT ALL: INSERT ALL WHEN “COLUMN3” = <EXPRESSION1> THEN INTO "TABLENAME1" VALUES ("COLUMN1","COLUMN2", ETC) ELSE INTO "TABLENAME2" VALUES ("COLUMN3","COLUMN4",ETC) SELECT "COLUMN1","COLUMN2",COLUMN3","COLUMN4" FROM "TABLENAME3" WHERE [CONDITION]

SQL - Structured Query Language Inserting into a Table (cont.) UN-CONDITIONAL INSERT FIRST : INSERT FIRST INTO "TABLENAME1" VALUES ("COLUMN1","COLUMN2", ETC) INTO "TABLENAME2" VALUES ("COLUMN3","COLUMN4",ETC) SELECT "COLUMN1","COLUMN2",COLUMN3","COLUMN4" FROM "TABLENAME3" WHERE [CONDITION] CONDITIONAL INSERT FIRST : INSERT FIRST WHEN “COLUMN3” = <EXPRESSION1> THEN INTO "TABLENAME1" VALUES ("COLUMN1","COLUMN2", ETC) WHEN “COLUMN4” = <EXPRESSION2> THEN INTO "TABLENAME2" VALUES ("COLUMN3","COLUMN4",ETC) SELECT "COLUMN1","COLUMN2",COLUMN3","COLUMN4" FROM "TABLENAME3" WHERE [CONDITION] INSERT WITH SUBQUERY: INSERT /*+ APPEND */ INTO (SELECT “COLUMN1” FROM “TABLENAME1” WHERE “CONDITION”) VALUES (“VALUE1”) | SELECT “COLUMN2” FROM “TABLENAME2”

SQL - Structured Query Language UPDATING INTO A TABLE: UPDATE “TABLENAME | SUBQUERY” [WITH {READ ONLY | CHECK OPTION CONSTRAINT “CONSTRAINTNAME”] SET ( “ COLUMNNAME1”, ” COLUMNNAME2 ”, ETC ) = (“VALUE | EXPRESSION |SUBQUERY”) [WHERE CONDITION] [ RETURNING (COLUMN1, EXPRESSION, ETC.) INTO :VAR1, :VAR2, ETC] UPDATE WITH SET VALUE: UPDATE “TABLE1 P” SET VALUE(P) = (SELECT VALUE(Q) FROM “TABLE2 Q” WHERE P.COLUMN1 = Q.COLUMN2) WHERE P.COLUMN1 = “VALUE | EXPRESSION | SUBQUERY”; DELETING FROM A TABLE DELETE [FROM] “TABLENAME | SUBQUERY [WITH CHECK OPTION]” [WHERE “ CONDITION”] [RETURNING (COLUMN1, EXPRESSION, ETC.) INTO :VAR1, :VAR2, ETC] TRUNCATE TABLE “TABLENAME”; DROP TABLE “TABLENAME” [CASCADE CONSTRAINTS] Note: These are DDL Statements.

SQL – Data Definition Language CREATE TABLE CREATE TABLE “TABLENAME” (“COLUMN1” “DATATYPE1” [“CONSTRAINT CLAUSE”], “COLUMN2” “DATATYPE2” [“CONSTRAINT CLAUSE”], ETC .., “ TABLE_CONSTRAINT_CLAUSE”); CREATE TABLE “TABLE1” AS SELECT “COLUMN1, ..” FROM “TABLE2” [WHERE “CONDITION”]; ALTER TABLE ALTER TABLE “TABLENAME” MODIFY “COLUMN1 DATATYPE”; ALTER TABLE “TABLENAME” ADD (“COLUMN1 DATATYPE” “CONSTRAINT CLAUSE”, “COLUMN1 DATATYPE” “CONSTRAINT CLAUSE”, ETC); ALTER TABLE “TABLENAME” DROP (“COLUMN1”, “COLUMN2”, ETC); ALTER TABLE “TABLENAME” SET UNUSED (“COLUMN1”, “COLUMN2”, ETC); ALTER TABLE “TABLENAME” SET UNUSED COLUMN “COLUMN1”; ALTER TABLE “TABLENAME” DROP UNUSED COLUMNS; ALTER TABLE “TABLENAME” DROP PRIMARY KEY [CASCADE]; ALTER TABLE “TABLENAME” ADD CONSTRAINT “CONSTRAINT_NAME” “CONSTRAINT_CLAUSE” [ENABLE | DISABLE] [VALIDATE | NOVALIDATE]; ALTER TABLE “TABLENAME” DROP CONSTRAINT “CONSTRAINT_NAME”; ALTER TABLE “TABLENAME” MODIFY CONSTRAINT “CONSTRAINT_NAME” [ENABLE | DISABLE] [VALIDATE | NOVALIDATE];

SQL – Constraints CONSTRAINTS Oracle uses integrity constraints to prevent invalid data entry into the base tables of the database. You can define integrity constraints to enforce the business rules you want to associate with the information in a database. If any of the results of a DML statement execution violate an integrity constraint, then oracle rolls back the statement and returns an error. TYPES OF INTEGRITY CONSTRAINTS NOT NULL Integrity Constraints UNIQUE Key Integrity Constraints PRIMARY KEY Integrity Constraints REFERENTIAL Integrity Constraints CHECK Integrity Constraints

SQL – Indexes INDEXES The impetus to index data arises because of the need to locate specific info and then to retrieve it as efficiently as possible since it is not possible to keep the entire set of data into the memory, and since disk access times are much slower than main memory access times, indexing plays important role. TYPES OF INDEXES B-TREE INDEX UNIQUE / NON-UNIQUE INDEX REVERSE KEY FUNCTION BASED INDEX BITMAP INDEX REVERSE KEY CREATE [UNIQUE|BITMAP] INDEX “INDEXNAME” ON TABLE (“COLUMN1” | “ EXPRESSION” [ASC | DESC]) [NOSORT|REVERSE]; ALTER INDEX “INDEXNAME” RENAME TO “NEW_INDEX_NAME”; DROP INDEX “INDEXNAME”;

SQL - Structured Query Language (cont) Types of OPERATORS SET : UNION, UNION ALL, INTERSECT, MINUS EQUALITY: <,>,<=,>=,!=,<>,^= LIKE with % and _ IN ANY ALL EXISTS BETWEEN Aggregate Functions - MIN MAX SUM AVG COUNT(“COLUMNNAME”) COUNT(*)

Oracle Built-In Functions || or CONCAT ('STRING1','STRING2') - Concatenate two strings together. ASCII('CHR | STRING') - Returns the decimal representation in the database characterset of the fist character of the string CHR(NUMBER) - Returns the character having the binary equivalent to the string in either database character set or national character set INITCAP('STRING1') - Initial Capital INSTR('STRING1','CHR | STRING2', FROMLOCATION1, OCCURENCE) - finds the location of of a character in a string LENGTH('STRING1') - Returns the length of a string LOWER('STRING1') - Converts every letter in a string to lowercase. LPAD('STRING1',LENGTH,'CHR || STRING2') - Left pad. LTRIM('STRING1','SET1') - Left Trim RPAD('STRING1',LENGTH,'CHR || STRING2') - Right PAD RTRIM('STRING1','SET1') - Right Trim SOUNDEX('STRING1') - Finds words that sound like the example specified SUBSTR('STRING1',FROM1,COUNT) - Substring. TRIM('STRING1','SET1') - All trim UPPER('STRING1') - Converts every letter in a string to uppercase. String Function:

Oracle Built-In Functions Single Value Function- ABS(NUMBER1) - Returns ABSolute value of the number. CEIL(NUMBER1) - Produces smallest integer greater than specified. FLOOR(NUMBER1) - Produces smallest integer lesser than specified. MOD(NUMBER1,DIVISOR1) - Returns modulus of the number. NVL(NUMBER1,SUBSTITUTE) - Returns Sub value if specified value is null POWER(NUMBER1,EXPONENT) - Number raised to exponent power ROUND(NUMBER1,PRECISION) - Rounds to the next value (if greater than 0.49) SIGN(NUMBER1) - Returns 1, -1 or 0 depending upon the sign. SQRT(NUMBER1) - Returns square root of the value TRUNC(NUMBER1,PRECISION) - Rounds to lower value VSIZE(NUMBER1 | CHAR | BLOB) - Returns storage size in oracle List Functions - COALESCE(VALUE1,VALUE2,...) - Returns first not-NULL value in list GREATEST(VALUE1,VALUE2,...) - Functions like MAX in list LEAST(VALUE1,VALUE2,...) - Functions like MIN in list Number Functions:

Oracle Built-In Functions ADD_MONTH(DATE, N) - Adds N months in date specified. CURRENT_TIMESTAMP - Returns current timestamp with active timezone DBTIMEZONE - Returns current database zone EXTRACT(timeunit FROM datatime) - Extract a portion of date GREATEST(VALUE1,VALUE2,...) - Functions like MAX in list LEAST(VALUE1,VALUE2,...) - Functions like MIN in list LAST_DAY(date) - Gives last day of the month MONTHS_BETWEEN(date2,date1) - Gives date2 - date 1 in months (fractional) NEXT_DAY(Date,‘Day') - Gives date of next day. TO_DATE('STRING1','FORMAT') - Converts string to date in given valid format. Date Functions:

Oracle Built-In Functions DECODE(VALUE,IF,THEN,IF, THEN,… ELSE) – Decodes a CHAR, VARCHAR2 or NUMBER into any of several different character strings or Numbers based on value. TO_CHAR(NUMBER1) – Converts Number to character TO_NUMBER(‘STRING1’) – Converts string to number TRANSLATE(‘STRING1’,IF,THEN) – Does an orderly char-to-char substitution in a string. Conversion and Transformation Functions:

Grouping Data Together Types of Joins INNER JOIN : An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition. OUTER JOIN: An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. NATURAL JOIN : A natural join is based on all columns in the two tables that have the same name LEFT OUTER JOIN – Taking all records from the left table RIGHT OUTER JOIN - Taking all records from the right table What is Cartesian Product ?

Grouping Data Together (contd.) CORRELATED SUBQUERIES - Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. GROUP BY EXTENSION - ROLLUP CUBE GROUPING

Other Database Objects VIEWS – View is a filtered information from table(s). Simple View Read-only Updateable With Check Option Without Check Option Complex View Read-only CREATE VIEW “NAME” AS “QUERY” [WITH READ ONLY | WITH CHECK OPTION]; DATABASE LINK - A named schema object that describes a path from one database to another. Database links are implicitly used when a reference is made to a global object name in a distributed database. CREATE [SHARED][PUBLIC] DATABASE LINK “LINK_NAME” [CONNECT TO CURRENT_USER] [USING 'CONNECT_STRING'] CREATE [SHARED][PUBLIC] DATABASE LINK “LINK_NAME” [CONNECT TO “ USER” IDENTIFIED BY “PASSWORD”] [AUTHENTICATED BY “USER” IDENTIFIED BY “PASSWORD”] [USING 'CONNECT_STRING'] .

Other Database Objects SEQUENCE – A database object from which multiple users may generate unique integers. CREATE SEQUENCE “SEQUENCE_NAME” [INCREMENT BY int ] [START WITH int] [MAXVALUE int | NOMAXVALUE] [MINVALUE int | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE int | NOCACHE] [ORDER | NOORDER ] SYNONYM – A synonym is an alias for a table, view, sequence, or program unit. A synonym is not actually a schema object itself, but instead is a direct reference to a schema object. CREATE [PUBLIC] SYNONYM “SYNONYM_NAME” FOR “OBJECT” [@DBLINK]

Oracle Text What is Oracle Text? Oracle Text is a tool that enables you to build text query applications. Oracle Text provides indexing, word and theme searching, and viewing capabilities for text. Text Query Applications The purpose of a text query application is to enable users to find text that contains one or more search terms. The text is usually a collection of documents. A good application can index and search common document formats such as HTML, XML, plain text, or Microsoft Word. For example, an application with a browser interface might enable users to query a company website consisting of HTML files, returning those files that match a query. CREATE TABLE “TABLE_NAME” ("COLUMN_NAME" BLOB NOT NULL, .. ETC) LOB("COLUMN_NAME“, …. ) STORE AS “LOB_NAME” ( STORAGE ( MAXEXTENTS UNLIMITED) ENABLE | DISABLE STORAGE IN ROW CHUNK 2K PCTVERSION 10 [CACHE READS | NOCACHE LOGGING | NOLOGGING]) TABLESPACE “TABLESPACE_NAME” Loading Your Text Table Text information (can be documents or text fragments) Path names of documents in your file system URLs that specify World Wide Web documents

Oracle Text (CONTD.) Indexing documents To query your document collection, you must first index the text column of your text table. Indexing breaks your text into tokens, which are usually words separated by spaces. Type of Index Oracle Text supports the creation of three types of indexes depending on your application and text source. CONTEXT CTXCAT CTXRULE CREATE INDEX “INDEX” ON “TABLE”(“BLOB_COLUMN”) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('DATASTORE CTXSYS.DEFAULT_DATASTORE FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP'); CREATE INDEX AUCTION_TITLEX ON AUCTION(TITLE) INDEXTYPE IS CTXCAT PARAMETERS ('INDEX SET AUCTION_ISET'); CREATE INDEX ON MYQUERIES(QUERY) INDEXTYPE IS CTXRULE PARAMETERS('LEXER LEXER_PREF STORAGE STORAGE_PREF SECTION GROUP SECTION_PREF WORDLIST WORDLIST_PREF');

PL/SQL What is PL/SQL ? PL/SQL is a procedural language that Oracle developed as an extension to standard SQL to provide a way to execute procedural logic on the database. Why Learn PL/SQL? Regardless of the front-end tool that you are using, you can use PL/SQL to perform processing on the server rather than the client. You can use PL/SQL to encapsulate business rules and other complicated logic. It provides for modularity and abstraction. You can use it in database triggers to code complex constraints, which enforce database integrity; to log changes; and to replicate data. PL/SQL can also be used with stored procedures and functions to provide enhanced database security. Finally, it provides you with a level of platform independence. Oracle is implemented on many hardware platforms, but PL/SQL is the same on all of them. It makes no difference whether you are running Personal Oracle on a laptop or Oracle8i Enterprise on UNIX.

PL/SQL (contd.) PL/SQL Is Block Structured DECLARE variable_declarations BEGIN program_code EXCEPTION exception_handlers END; What About Some Output?

PL/SQL (contd.) Variable declarations Variable_name [CONSTANT] data_type [NOT NULL] [:= (or DEFAULT) value]; All declarations must be made in the declaration section of the block. Variable Scopes: Block structure rule %TYPE <var-name> <obj>%type [not null][:= <init-val>]; E.g. name Books.title%type; /* name is defined as the same type as column 'title' of table Books*/ comm number(5,2) := 12.5; x comm%type; -- x is defined as the same type as variable comm User-defined subtypes SUBTYPE new_type IS original_type ; Example: SUBTYPE num IS NUMBER ; -- NUMBER(4) not allowed (constraint) mynum num; -- num is a user-defined type name (number) SUBTYPE nametype IS customer.fname%TYPE;

PL/SQL Control Structure CONDITIONAL IF-THEN_ELSE STATEMENT: IF condition1 THEN sequence_of_statements1 ELSIF condition2 THEN sequence_of_statements2 ELSE sequence_of_statements3 END IF;

PL/SQL Control Structure (contd.) CASE STATEMENT: CASE SELECTOR WHEN EXPRESSION1 THEN SEQUENCE_OF_STATEMENTS1; WHEN EXPRESSION2 THEN SEQUENCE_OF_STATEMENTS2; ... WHEN EXPRESSIONN THEN SEQUENCE_OF_STATEMENTSN; [ELSE SEQUENCE_OF_STATEMENTSN+1;] END CASE; SELECT CASE “COLUMN_NAME” WHEN “VALUE1” THEN “VALUE2” ELSE “VALUE3” END FROM “TABLE_NAME”;

PL/SQL Control Structure (contd.) ITERATIVE CONTROL: LOOP AND EXIT STATEMENTS: LOOP ... IF CONDITIONS1 THEN ... EXIT; -- exit loop immediately -- EXIT WHEN CONDITION2; END IF; END LOOP; <<outer>> LOOP ... LOOP ... EXIT outer WHEN ... -- exit both loops END LOOP; ... END LOOP outer;

PL/SQL Control Structure (contd.) ITERATIVE CONTROL: LOOP AND EXIT STATEMENTS: While Loop: WHILE condition LOOP sequence_of_statements END LOOP; FOR counter IN [REVERSE] lower_bound..higher_bound LOOP sequence_of_statements END LOOP; SEQUENTIAL CONTROL: GOTO AND NULL STATEMENTS

Introduction of Cursors PL/SQL uses two types of cursors: implicit and explicit . PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. However, for queries that return more than one row, you must declare an explicit cursor, use a cursor FOR loop, or use the BULK COLLECT clause. CURSOR CURSOR_NAME [(PARAMETER[, PARAMETER]...)] [RETURN RETURN_TYPE] IS SELECT_STATEMENT; PARAMETER = cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression] OPENING A CURSOR: OPEN CURSOR_NAME [(parameter1, parameter2…)]; FETCHING WITH A CURSOR: LOOP FETCH CURSOR_NAME INTO VARIABLE1; EXIT WHEN CURSOR_NAME%NOTFOUND; -- process data record END LOOP;

Introduction of Cursors (contd.) USING CURSOR FOR LOOPS: DECLARE RESULT TEMP.COL1%TYPE; CURSOR C1 IS SELECT N1, N2, FROM DATA_TABLE WHERE EXPER_NUM = 1; BEGIN FOR C1_REC IN C1 LOOP /* CALCULATE AND STORE THE RESULTS */ RESULT := C1_REC.N2 / (C1_REC.N1 + C1_REC.N1); INSERT INTO TEMP VALUES (RESULT, NULL, NULL); END LOOP; COMMIT; END; WHAT IS REF CURSORS?

Subprograms What Are Subprograms? Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called procedures and functions. Subprograms have a declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These objects are local and cease to exist when you exit the subprogram. The executable part contains statements that assign values, control execution, and manipulate ORACLE data. The exception-handling part contains exception handlers, which deal with exceptions raised during execution.

Subprograms - Procedures A procedure is a subprogram that computes a value, and returns one or more values. [CREATE [OR REPLACE]] PROCEDURE PROCEDURE_NAME[(PARAMETER DATATYPE [NOCOPY] [IN|OUT |INOUT][, PARAMETER DATATYPE [IN|OUT |INOUT]]...)] [AUTHID CURRENT_USER | AUTHID DEFINER ] {IS | AS} [PRAGMA AUTONOMOUS_TRANSACTION;] [LOCAL DECLARATIONS] BEGIN EXECUTABLE STATEMENTS [EXCEPTION EXCEPTION HANDLERS] END [PROCEDURE_NAME];

Subprograms - Functions A function is a subprogram that computes a value. Inside the function body, the RETURN statement is used to return control to the caller with a value. [CREATE [OR REPLACE]] FUNCTION FUNCTION_NAME[(PARAMETER DATATYPE[, PARAMETER DATATYPE]...)] RETURN DATATYPE} {IS | AS} [PRAGMA AUTONOMOUS_TRANSACTION;] [LOCAL DECLARATIONS] BEGIN EXECUTABLE STATEMENTS [EXCEPTION EXCEPTION HANDLERS] END [FUNCTION_NAME];

Packages What are Packages? Packages are PL/SQL constructs that allow related objects to be stored together. What are the advantages? Enforced information hiding, Object-Oriented design, Object persistence, Performance improvement, Less restrictive on dependency A package has two separate parts : specification and body. Each of them is stored separately. A package can only be stored Package Specification It contains information about the contents of the package, NOT the code itself. CREATE [OR REPLACE] PACKAGE PACKAGE_NAME {IS|AS} -- NO PARAMETERS PROCEDURE_SPECIFICATION | FUNCTION_SPECIFICATION | VARIABLE_DECLARATION | TYPE_DEFINITION | EXCEPTION_DECLARATION | CURSOR_DECLARATION END [PACKAGE_NAME];

Packages (contd.) Package Body It contains the actual code for the forward subprogram declarations in the package header -- so it can not be compiled without the header. Package body is optional (if no procedure or function defined in the header) The specification for the procedure or function must be the same in both. CREATE OR REPLACE PACKAGE BODY PACKAGE_NAME {IS|AS} ... [BEGIN] ... – Provide code for the subprograms. END [PACKAGE_NAME]; Packages and Scope Any object declared in a package header is in scope and is visible outside the package. This may be useful for declaring global variables, and can be accessed by qualifying the object with the package name. E.g. DBMS_OUTPUT.PUT_LINE('hello'); The procedure call is the same as it would be for a stand-alone procedure.

Packages (contd.) Packages and Dependencies The package body depends on the header and referenced tables The package header does not depend on anything (this is the advantage!) -- we can change the package body without changing the header. If the header is changed, this automatically invalidates the body.

Triggers What are triggers? A trigger defines an action the database should take when some database-related event (such as inserts, updates, deletes) occurs. Triggers are similar to procedures, in that they are named PL/SQL blocks. Differences between Procedures and Triggers: A procedure is executed explicitly from another block via a procedure call with passing arguments, while a trigger is executed (or fired) implicitly whenever the triggering event (DML: INSERT, UPDATE, or DELETE) happens, and a trigger doesn't accept arguments. When triggers are used? Auditing information in a table by recording the changes Automatically signaling other programs that action needs to take place when changes are made to a table Enforcing complex business rules

Triggers (contd.) TYPES OF TRIGGERS: Row-level triggers Row-level triggers execute once for each row in a transaction. Row-level triggers are the most common type of triggers; they are often used in data auditing applications. Row-level trigger is identified by the FOR EACH ROW clause in the CREATE TRIGGER command. Statement-level triggers Statement-level triggers execute once for each transaction. Statement-level triggers are the default type of triggers created and are identified by omitting the FOR EACH ROW clause in the CREATE TRIGGER command. Before and After Triggers Since triggers occur because of events, they may be set to occur immediately before or after those events. The events that execute triggers are database transactions, triggers can be executed immediately BEFORE or AFTER the statements INSERTs, UPDATEs, DELETEs. AFTER row-level triggers are frequently used in auditing applications, since they do not fire until the row has been modified.

Triggers (contd.) CREATE [OR REPLACE] TRIGGER “TRIGGER_NAME” {BEFORE EVENT | AFTER EVENT | INSTEAD OF EVENT} [FOR EACH ROW] WHEN (CONDITION) PL_SQL_BLOCK EVENT CAN BE ONE OR MORE OF THE FOLLOWING (SEPARATE MULTIPLE EVENTS WITH OR) DELETE EVENT_REF INSERT EVENT_REF UPDATE EVENT_REF UPDATE OF COLUMN, COLUMN... EVENT_REF DDL_STATEMENT ON [SCHEMA.] {TABLE|VIEW} DDL_STATEMENT ON DATABASE SERVERERROR LOGON LOGOFF STARTUP SHUTDOWN EVENT_REF: ON [SCHEMA.]TABLE ON [SCHEMA.]VIEW

Triggers (contd.) Using :old and :new in Row-level Triggers A row-level trigger fires once per row processed by the triggering statement. Inside the trigger, you can access the row that is currently being processed through two pseudocodes -- :old and :new, e.g., :new.field or :old.field. However, they are not true records. :old refers to the data as it existed prior to the transaction. Updates and Deletes usually reference :old values. The :new values are the data values that the transaction creates (such as the columns in an inserted record). :old is undefined for INSERT statements, :new is undefined for DELETE statements. If you need to set a column value in an inserted row via your trigger, then you will need to use a BEFORE INSERT trigger in order to access the :new values. Using an AFTER INSERT trigger would not allow you to set the inserted value, since the row will already have been inserted into the table (and thus be :old). What is mutating table?

Thank You ! The end of my presentation ... but there are many more features in Oracle 9i, will be used during practice. - Kamlesh Chaudhary

ORACLE PL SQL

In this document

More Related Content

What's hot

Viewers also liked

Similar to ORACLE PL SQL

Recently uploaded

ORACLE PL SQL