ORACLE PLSQL
Procedural Language Structure Query Language
Part 1 : PLSQL Course
Part 2 : PLSQL practices and problems
Prepared By : Ashraf Omar Hussein
Email : [email protected]
Mobile No : +20101552319
Part 1 : PLSQL Course
1. Why do you need PLSQL? 2. PLSQL Block Structure 3. Variables Types 4. Anonymous Block 5. Stored Procedure 6. Parameter 7. Control Structure 7.1 IF Condition 7.2 Case Expression 7.3 Loop 8. Cursor 9. Handle Exception 10. DataBase Trigger 11. Function 12. Package
13. Q: What is PLSQL ?
A:It is a procedural anguage Structure Query language
Q: Why do you Need PLSQL Language ?
1 Bad peformance for the heavy network traffic
2 Cann’t write more than one SQL command at the same Time
3 SQL commands are not depended to each other and Not executed in a specific order 4 SQL command s are not saved in the DB server
5 SQL Commands are not shared for all users
6 SQL Commands cann’t handle user Error (Exception) 7 SQL Commands are static NOT Dynamic
8 SQL Commands are executed Manually not Automatically 9 SQL Commands don’t use IF condition
10 SQL Command don’t use LOOP statement 11 SQl Command don’t use
12 SQL Command don’t use Variable
Q: What are the benefits of PLSQL ?
1 Improve Performance and reduce network traffic between client and server by agrouping sql statements together in one block and send it to the server in one call
2 Portability: PlSQl is available on any kind of platform
3 Modularize Programs: can contain nested Blocks
4 Integration: it can be used as developer tools (in forms and reports)or as administration tools
Oracle Complete PLSQL Reference 2
Applicat
ion 1
SQL DATA BASE SQL SQLApplicat
ion 2
SQL SQL SQLQ: How does PLSQL solve the problems of the SQL commands? 1 By creating a database Block
Q: What is a datablock Block ?
1 It is a dataase structure that contain one or more SQL commands
2 Block require that all SQL Commands to be correct for the execution . if there is one SQL command is not Correct then the whole block with all SQL Commands will not be exeucted
3 If you write more than one SQL statement you must separate them by using semi comma (;)
Q: what are the block types?
1 Anonymus Bock 2 Subprogram – Program Unit
Not stored or saved in the DB server Stored or saved in the DB server
Not shared for all users Shared for all users
Executed only once Executed many times as user requests
Created and executed during run time Created before the execution time
Cann’t accept parameter Can Accept Parameter
Has only one type
1. Anonymus Block Has 3 types1. Stored Procedure
2. Function 3. Package
Anonymous Block Structure
Declaration Part Optional
Body
Mandatory Exception Part
Optional
Variable
Q: What is a Variable?
1. Is a space located in the memory can be used For Impermanent storage of One an ONLY One Value 2. Variable can store value identified by the user himself OR value retrieved form the data base table Q: Why do you use a variable?
1- Temporary storage of data: 1.1 Data can be temporarily stored in one or more variables for use when validating data input and for processing later in the data flow process such as Login Window we store the user name and password For user to check for these two values if they are stored in the data base the user will be logined successfully and if they are not stored in the data base the user will not be able to login. 2. Manipulation of stored values: 2.1 Variables can be used for calculations and other data manipulations without accessing database. 2.2 store values for calculation and the result of these calculations will be displayed in the report 3- Reusability:
3.1 After they are declared, variables can be used repeatedly in an application simply by referencing them in other statements, including other declarative statements such as local variables and global variables.
4- Ease of maintenance: 4.1 declare new variable has the same data type of other variable 4.2 declare new variable has the same data type of data base column and when changing the value in the database this value will changed automatically in the variable such as create variable storing the max value in empno column to create application trigger to increment this value for the next record Q: How do you declare a variable?
1. You must declare a variable in the declaration part of the block
2. You can decalre more than one variable in the same block BUT you must 2.1 define each varibale in a separate line
2.2 end each varibale with a semi comma (;)
3. Each variable Must has [name, data type] mandatory, [null ability, default value] optional Q: what are the rules of the Variable name ?
1. Variable names are unique within the same block but variable name may be duplicated in other block 2. Length between 1-30 charater length
3. Must start with character NOT with number or special character BUT it may include numbers and special character inside the name or at the end
4. Variable name mustn’t be the same as column name within the data base
5. It is preferable to start variable name with V_ColumnName if it contain changeable value and start variable name with C_ColumnName if it contain Constant value
6. If you declare a variable contain constant value so the keyword CONSTANT must precede the data type of the variable
7. String literals must be enclosed in single quotation marks. For example , 'Hello, world'.
If there is a single quotation mark in the string, use a single quotation mark twice, for example, to insert a value FISHERMAN'S DRIVE, the string would be 'FISHERMAN''S DRIVE '.
Q: what are the rules when you Assign a value for the Variabe ?
1. It is good code to assign value for the variable because if you did not assign value then null will be inserted 2. To assign value for the variable you can use := or use DEFAULT as keyword
3. Variable value can be constant value or default value or expression
4. Assigning string value must be enclosed between single quotes But numeric value does not require single quotes
5. If you assign value for the variable in the declaration section and assign new value for the variable in the execution section the new value will replace the old value in the declaration part
Q: What are the data types for the variables 1. Char(n) 2. Varcahr2(n) 3. Number(n) 4. Number(m,n) 5. Date 6. Binary_integer 7. Boolean 8. Long 9. Long raw
Examples for decalring variables 1. Use user defined datatype
Declare
v_job varchar2(20) null ;
v_location varchar2(50) null := 'cairo';
v_deptno number(5) not null := 10
v_total_sal number(9,2) := 0;
v_mgr number(6) DEFAULT 100;
c_comm CONSTANT number(5) null DEFAULT 1400;
v_hire_date date := TO_CHAR('1998/08/16', 'yyyy/mm/dd');
v_orderdate date := SYSDATE + 3;
c_tax_rate CONSTANT numebr(5,2) := 0.15;
v_valid BOOLEAN not null := TRUE;
2. Use TabeName.ColumnName%type Declare
v_job emp.job%type ;
v_location dept.dname%type := 'cairo' ; v_deptno dept.deptno%type not null := 10; v_total_sal emp.sal%type := 0;
v_mgr emp.mgr%type DEFAULT 100; c_comm CONSTANT emp.comm%type := 1400;
v_hire_date emp.hiredate%type := TO_CHAR('1998/08/16', 'yyyy/mm/dd'); 3. Use Variable%type
Declare
v_job emp.job%type ;
v_location v_job%type := 'cairo' ;
v_deptno dept.deptno%type not null := 10; v_total_sal v_deptno%type := 0;
v_mgr v_deptno%type DEFAULT 100; c_comm CONSTANT emp.comm%type := 1400;
v_hire_date emp.hiredate%type := TO_CHAR('1998/08/16', 'yyyy/mm/dd'); 4. Use TableName%rowtype
4.1 Decalre only one variable that store the value for all row in the table 4.2 you can print the whole row value or print a specific values only Declare Emp_Row Emp%RowType; begin DBMS_OUTPUT.PUT_LINE (Emp_Row.Empno); DBMS_OUTPUT.PUT_LINE (Emp_Row.Ename); DBMS_OUTPUT.PUT_LINE (Emp_Row.Salary); End;
5. Use recod data type
5.1 create an object of record data type 5.2 create a variable based on that object type Declare
TYPE emp_record_type ISRECORD
(employee_id NUMBER(6) NOT NULL := 100, last_name employees.last_name%TYPE,
job_id employees.job_id%TYPE); emp_record emp_record_type; 6. use table data type
Declare
Type Emp_Table is Table of EMP%RowType Index by binary integer;
V_Emp Emp_table;
DBMS_OUTPUT.PUT_LINE:
1. PRINT command. is DBMS_OUTPUT.PUT_LINE. DBMS_OUTPUT is Oracle-supplied package, and PUT_LINE is a procedure within that package.
2. Within a PL/SQL block, reference DBMS_OUTPUT.PUT_LINE and, in parentheses, specify the string that you want to print to the screen. The package must first be enabled in your SQL*Plus session. To do this, execute the SQL*Plus
SQL> SET SERVEROUTPUT ON command.
Anonymus Block Best Practices 1. How to write Insert Statement without Variables
2. How to write Insert Statement with Variables
3. How to write Update Statement without Variables
4. How to write Update Statement with Variables
5. How to write Delete Statement without Variables
It is not possible to write the select statement without variables because varaibles are MANDATORY in this case . Because the syntax for the SELECT statement is differe from the normal SELECT stamtent written by SQL
Oracle Complete PLSQL Reference 10
SELECT using SQL Syntac SELECT using PLSQL syntax Select
From table name Where
Group by Having Order by
Select
INTO variabls --- New From table name
Where Group by Having Order by
Variables are decalred imlicitly by oracle Variables are decalred Explicitly by Developer Values are printed implicitly by oralce Values are printed Explicitly by Developer
6. How to write Update Statement with Variables
You must declare variables with the same number of the columns that you retrieve in your select statement 1. Decalre variables for each column you retrive in select statement
2. Select column into variable
3. Print value for the variable by using DBMS_OUTPUT.PUT_LINE
4. you must enable DBMS_OUTPUT.PUT_LINE first by running command 5. SQL> set serveroutput on ;
6. DBMS_OUTPUT.PUT_LINE is used to pring value for one variable only .
7. If you print values for more that one variable then you can use Concat function or write DBMS_OUTPUT.PUT_LINE more than one time for each varaible you want to pring
7. How to write more that DML transactions within the anonymus block
8. How to write more that DML transactions within the anonymus block
9. Using Subsutitution Variable By using wild card &
9.1 Using Subsutitution Variable By using wild card &
9.2 Using Subsutitution Variable By using wild card &
9.3 Using Subsutitution Variable By using wild card &
10 . Using ROW%TYPE as a datatype
1. it can be used when you retrieve all cloumns from the table 2. you can print all the values or just a specific values only
11 . Using RECORD%TYPE as a datatype
Subprogram = Program Unit 1. Stored Procedure
Parameters are optional not mandatory
Declaration Part Optional Body Mandatory Declaration Part Optional
1. all Stored procedures created are stored in user_objects 2. source code is stored in user_source
1.1 procedure without parameters
1.1 procedure without parameters
1.1 procedure without parameters
1.1 procedure without parameters
1.1 procedure without parameters using TABLENAME%ROWTYPE as a datatype Using ROW%TYPE as a datatype
1. it can be used when you retrieve all cloumns from the table 2. you can print all the values or just a specific values only
1.1 procedure without parameters using TABLENAME%ROWTYPE as a datatype Using ROW%TYPE as a datatype
1.1 procedure without parameters using RECORD DATATYPE as a datatype Using RECORD DATATYPE as a datatype
1.2 procedure with parameters Parameter type are
1. IN Parameter ---- Default type 2. OUT Parameter
3. IN OUT Parameter
You can define more that one parameters in the same procedure All parameters are defined after the procedure name
1.2 procedure with parameters -- IN parameter
1.2 procedure with parameters -- IN parameter
1.2 procedure with parameters -- IN parameter
1.2 procedure with parameters -- IN parameter
1.2 procedure with parameters -- IN parameter
1.2 procedure with parameters -- IN parameter
1.2 procedure with parameters -- IN parameter 1.2 procedure with parameters -- IN parameter
1.2 procedure with parameters -- IN parameter
1.2 procedure with parameters -- IN Parameter using TABLENAME%ROWTYPE as a datatype
1.2 procedure with parameters -- IN Parameter using TABLENAME%ROWTYPE as a datatype
1.2 procedure with parameters -- IN Parameter using RECORD DATATYPE as a datatype
Part 1 : PLSQL Course
Control Structure
There are three Type of Control Structure 1 IF Condition
2 Case Expression
3 Loop
IF Condition
IF Condition There Are Three Types of IF Statement
1 IF Stamtement with ONLY one Conditon Syntax IF Condition1 then Action1 ; END IF; 2 IF Stamtement with ONLY two Conditons Syntax IF Condition1 then Action1 ;
ELSE Action2 ; END IF;
3 IF Stamtement with MORE than two Conditons Syntax IF Condition1 then Action1 ;
ELSIF Condition2 then Action2 ; ELSIF Condition3 then Action3 ; ELSIF Condition4 then Action4 ; ELSE action 5;
END IF;
All conditions in IF statements are Boolean expressions that evaluate to true or false. All Actions in IF Statements are PL/SQL statements
You can execute many actions as you like when the actions is TRUE IF Stamtement with ONLY one Conditon
IF Stamtement with ONLY one Conditon
IF Stamtement with ONLY two Conditons
IF Stamtement with MORE than two Conditons
IF Stamtement with MORE than two Conditons
create or replace procedure rep1(p1 number) is vsal number(5); v2 varchar2(20); begin select sal into vsal from emp where empno = p1; if LENGTH(vsal) < 6 then v2 := to_char(vsal,'000000'); dbms_output.put_line(v2); end if; end;
CASE Expression
CASE Expression Create or replace procedure rep1(p1 number) Is V_sal number(5); Begin Select sal Into v_sal From scott.Emp Where empno = p1; v_sal := CASE v_sal When null then 50 ; When <= 1000 then v_sal+ 100 ; When <= 2000 then v_sal+ 200 ; ELSE v_sal+ 300 ; END CASE; Update emp set sal = V_sal where empno = p1; End;
Loop
Loop
What is Loop ?
It is a technique that can be used to run spefic code more that one time within the block Why do you use Loop?
You may use a loop to run one or more statements multiple times What are Loop types ?
1. Basic Loop – Simple Loop 2. For loop
3. While Loop
In loop you must write a condition to exist the loop . If you didn’t specify the condition the loop will continue forever (infinity loop)
Basic Loop – Simple Loop A simple loop runs until you explicitly end the loop.
To end the loop, you use either an EXIT or EXIT WHEN statement. The EXIT statement ends a loop immediately.
EXIT WHEN statement ends a loop when a specified condition occurs. Basic Loop Syntax
Basic Loop
Basic Loop
Basic Loop
Basic Loop with IF condition
Basic Loop – LABEL Loop
Basic Loop
Basic Loop
For Loop
A FOR loop runs a predetermined number of times.
You can use a variable directory in the body without declaring it in the declaration Basic Loop Syntax
Where :
1. Loop_variable specifies the loop variable
2. REVERSE specifies that the loop variable value is to be decremented each time through the loop. 3. lower_bound specifies the loop's lower bound
4. upper_bound specifies the loop's upper bound
5. If REVERSE is used, the loop variable is initialized to this upper bound
6. The upper or lower bounds of the FOR loop can be defined as variables or functions.
For Loop
For Loop
For Loop
For Loop
For Loop
For Loop
Reversed For Loop with REVERSE Key Word
For Loop
For Loop
While Loop
While Loop
While Loop with Parameters
While Loop
While Loop
While Loop
Cursor
Explicit Cursor
Implicit Cursor
Reference Cursor
Cursor
Topics Cursor 1. Introduction( 13 ) 2. Cursor Declaration( 16 ) 3. Cursor Open( 3 ) 4. Fetch( 17 ) 5. LOOP( 10 ) 6. Close Cursor( 6 ) 7. Cursor Status( 6 ) 8. Implicit Cursor( 18 ) 9. Explicit Cursor( 2 ) 10. Cursor for Update( 5 ) 11. Cursor Parameter( 4 ) 12. ref cursor( 6 ) 13. REFCURSOR( 9 ) 14. Cursor Attributes( 9 ) 15. refcursor( 3 ) 16. Cursor function( 5 ) Q: What is Cursor?
1. Cursor is a space in the memory that can be used to store more than ONE record 2. cursor is basically a set of rows that you can access one at a time.
3. Cursor can accept parameters Q: when do you use a Cursor?
You use a cursor when you have a SELECT statement that returns more than one row from the database. Q: How do you use a Cursor?
1. Declare variables to store the column values from the SELECT statement. 2. Declare the cursor , specifying your SELECT statement.
3. Open the cursor.
4. Fetch the rows from the cursor. 5.Close the cursor.
Declaration part Declaration part Body part Body part Body part Q: what is the syntax for the cursor ?
Cursor Q: How do you use a Cursor?
Open Execute the SELECT statement
Fetch 1. Read column values into the variables that you specify
2. you must use LOOP to Fetch more than one record
3. If you did’not use Loop with the cursor then the first record retreived by the cursor will be retrieved ONLY
Close Frees up system resources
Q: what are the cursor’s type?
Explicit Curosr Declare Cursor then use open , fetch , close Defined in the decalarion part as follows
Cursor <CursorNmae> is <SELECT statement> ;
Implicit Cursor Declare Cursor then use Loop
Defined in the body part with loop statement as follows FOR I in <SELECT statement> Loop;
SQL%ROWCOUNT and SQL%BULK_ROWCOUNT SQL%FOUND and SQl%NOTFOUND
Reference Coursor Q: Functions with Cursor
<CursorName>%ISOPEN Return TRUE if the cursor is opend and Return FALSE if cursor is not opened <CursorName>%FOUND 1. Return TRUE if the cursor retun row and Return FALSE if cursor didn’t return row
2. MUST be used after the fetching of records
<CursorName>%NOTFOUND 1. Return TRUE if the cursor retun row and Return FALSE if cursor didn’t return row
2. MUST be used after the fetching of records <CursorName>%ROWCOUNT 1. Retun number of record retrieved by cursor
2. MUST be used before closing the cursor
Values of %FOUND, %NOTFOUND, and %ROWCOUNT are changed after every fetch.
If you use the %FOUND, %NOTFOUND, and %ROWCOUNT cursor variables before the cursor is opened or after the cursor is closed, they will raise an exception.
Cursor with NO Loop
If you didn’t use Curosr with Loop then only the first record retried by cursor will be displayed
Cursor with Basic Loop
Cursor with Basic Loop to Fetch cursor till cursorName%NOTFOUND
Cursor with Basic Loop to Fetch cursor till cursorName%NOTFOUND
Cursor with For Loop
Cursor with While Loop
Cursor with While Loop to Fetch cursor till cursorName%NOTFOUND
Cursor for count
Cursor to reference whole table
<CursorName>%ISOPEN
<CursorName>%FOUND or <CursorName>%NOTFOUND
<CursorName>%ROWCOUNT
Cursor with Parameters
Cursor with Parameters
Cursor with Parameters
Cursor with Parameters
Implicit Cursor
Implicit cursor open, fetch and close
Open When you begin the body by writing BEGIN keyword
Fetch When you start the Loop statement by writing (basic loop , for loop , while Loop) Close When you end the Loop statement by writing (end loop) key word
Oracle Complete PLSQL Reference 89
Implicit Cursor
Oracle Complete PLSQL Reference 90
Implicit Cursor
Implicit cursors: SQL%ROWCOUNT returns number of rows affected by SQL statement
Implicit Cursor
Reference Cursor
The REF CURSOR datatype cannot be used outside a PL/SQL environment. There are two kinds of REF CURSOR types:
1. weak 2. strong.
Weak Reference Cursor can point to any data set, as shown here:
Strong Reference Cursor Explicitly declares the type of data that can be referenced. Weak Ref Cursor
1.
SQL> Declare
type weak_rcty is ref cursor; c_weak rcty weak_rcty; 2.
declare
c_weak sys_refcursor Strong Ref Cursor declare
type strong_rcty is ref cursor return employee%ROWTYPE; c_strong_rcty strong_rcty;
Weak Reference Cursor
Weak Reference Cursor
Weak Reference Cursor
Weak Reference Cursor
Exception Handling
Exception Handling & Handling PL/SQL Errors Q: What is Exception?
In PL/SQL, the user can catch certain runtime errors. Exceptions can be internally defined by Oracle or the user.
Q: What is Exception Handling?
1. Exceptions are used to handle errors that occur in your PL/SQL code. 2. you can use more than one EXCEPTION in the same Block
3. You could always code a NULL statement if no action is to be taken. 4. you can declare variables in the Exception handler
Q: What are the oracle Error Types?
There are three types of exceptions: 1. Predefined Oracle errors
2. Undefined Oracle errors 3. User-defined errors Identifying Exception Types
Q: what are the Exception Types ?
1. Pre-defined Exception 2. Custom Exception
Q: what are the different parts of the exception?
The different parts of the exception. 1. Declare the exception.
2. Raise an exception. 3. Handle the exception. An exception has four attributes:
1. Name provides a short description of the problem. 2. Type identifies the area of the error.
3. Exception Code gives a numeric representation of the exception. 4. Error message provides additional information about the exception.
The predefined divide-by-zero exception has the following values for the attributes: 1. Name = ZERO_DIVIDE
2. Type = ORA (from the Oracle engine) 3. Exception Code = C01476
4. Error message = divisor is equal to zero
How do you write EXCEPTION in the PLSQL BLOCK?
Pre-Defined Exceptions
Exception Oracle Error SQLCODE Value
NO_DATA_FOUND ORA-01403 Single SELECT statement returned no data
TOO_MANY_ROWS ORA-01422 Single SELECT statement returned more than one row of
data.
ZERO_DIVIDE ORA-01476 A program attempts to divide a number by zero.
VALUE_ERROR ORA-06502 An arithmetic, conversion, truncation, or
size-constraint error occurs.
For example, when your program selects a column value
Oracle Complete PLSQL Reference 99
Error Code Prefix Indicates This Exception Type of Error
ORA Core RDBMS errors
PLS PL/SQL errors
FRM Oracle Forms errors
into a character variable,
if the value is longer than the declared length of the variable,
PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a
character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
DUP_VAL_ON_INDEX ORA-00001 A program attempts to store duplicate values in a
database column that is constrained by a unique index.
INVALID_NUMBER ORA-01722 In a SQL statement, the conversion of a character string
into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number. ACCESS_INTO_NULL ORA-06530 A program attempts to assign values to the attributes of
an uninitialized object.
CASE_NOT_FOUND ORA-06592 None of the choices in the WHEN clauses of a CASE
statement is selected, and there is no ELSE clause. COLLECTION_IS_NULL ORA-06531 A program attempts to apply collection methods other
than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the
elements of an uninitialized nested table or varray.
INVALID_CURSOR ORA-01001 A program attempts a cursor operation that is not
allowed, such as closing an unopened cursor.
LOGIN_DENIED ORA-01017 A program attempts to log on to Oracle with an invalid
username or password.
NOT_LOGGED_ON ORA-01012 A program issues a database call without being
connected to Oracle.
PROGRAM_ERROR ORA-06501 PL/SQL has an internal problem
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWID ORA-01410 The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
TIMEOUT_ON_RESOURCE ORA-00051 A time-out occurs while Oracle is waiting for a resource
Pre-Defined Exceptions
Exception Oracle Error SQLCODE Value
SUBSCRIPT_BEYOND_COUNT ORA-06533 A program references a nested table or varray element using an index number larger than the number of elements in the collection.
ROWTYPE_MISMATCH ORA-06504 The host cursor variable and PL/SQL cursor variable
involved in an assignment have incompatible return types. For example, when an open host cursor variable
is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SELF_IS_NULL ORA-30625 A program attempts to call a MEMBER method, but the
Instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is Always the first parameter passed to a MEMBER method.
STORAGE_ERROR ORA-06500 PL/SQL runs out of memory or memory has been
Corrupted.
CURSOR_ALREADY_OPEN ORA-06511 A program attempts to open an already open cursor. A Cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop.
OTHERS 1. Any other error occurred.
2. You can use the OTHERS exception to handle all exceptions
3. Because OTHERS handles all exceptions, you must list it after any specific exceptions in your EXCEPTION block.
Create the following Employee and Department tables to be used in the following exercises
when NO_DATA_FOUND then
Raised when Single SELECT statement returned no data
When TOO_MANY_ROWS then
Raised when Single SELECT statement returned more than one row of data.
Oracle Complete PLSQL Reference 102
System Error User Exception
System Error
when NO_DATA_FOUND & TOO_MANY_ROWS then
when ZERO_DEVIDE then
Raised when an attempt is made to divide a number by zero.
when DUP_VAL_ON_INDEX then
Raised when an attempt is made to store duplicate values in a column that is constrained by a unique index
Oracle Complete PLSQL Reference 104
More than one Exception
System Error
User ExceptionUser Exception System Error System Error
when INVALID_NUMBER then
Raised when an attempt is made to convert an invalid character string into a number.
Oracle Complete PLSQL Reference 105
System Error User Exception
when OTHERS then
You can use the OTHERS exception to handle all exceptions
when OTHERS then
Oracle Complete PLSQL Reference 106
System Error
User Exception
OTHERS Exception is not the last one
when OTHERS then
Declare Variables in the Exception Handler
Oracle Complete PLSQL Reference 107
Use more than one nested block in the main block
Use SQLCODE and SQLERRM Functions with Exceptions SQLCODE Return Error code ot Error number for any Error
SQLERRM Return Error Message for any Error
User Defined Exception
1. Is a user defined exception that can be used instead of the pre-defined exception
2. user-defined exceptions must be declared and must be raised explicitly by RAISE statements. 3. Exceptions can be declared only in the declarative part of a PL/SQL block,
4. You declare an exception by introducing its name, followed by the keyword EXCEPTION
5. You cannot declare an exception twice in the same block
6. Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosingblocks cannot reference exceptions declared in a sub-block
User Defined Exception
Defining Your Own Error Messages by using Procedure: RAISE_APPLICATION_ERROR
1.The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions. 2. error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048bytes long
OUT Parameter
1. OUT parameter acts like a variable. You can change its value, and reference the value after assigning it: 2. can be bused when pass parameter values from one procedure to another procedure
2.1 OUT parameter returns a value to the caller of a subprogram.
3. You must pass a variable, not a constant or an expression, to an OUT parameter. 4. OUT formal parameters are initialized to NULL
OUT Parameter
IN OUT Parameter
Call procedure from another procedure
Oracle Complete PLSQL Reference 114
Call External Procedure named Rep1
Call procedure from another procedure
Table Type
SQL> DECLARE
2 TYPE emp_table_struct IS TABLE OF emp.fname%TYPE INDEX BY BINARY_INTEGER;
3 emp_table emp_table_struct;
4 CURSOR emp_cursor IS SELECT fname FROM emp ORDER BY id;
5 v_row NUMBER := 1;
6 BEGIN
7 OPEN emp_cursor;
8 LOOP
9 FETCH emp_cursor INTO emp_table(v_row);
10 EXIT WHEN emp_cursor%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(emp_table(v_row));
12 v_row := v_row + 1;
13 END LOOP;
14 CLOSE emp_cursor;
15 DBMS_OUTPUT.PUT_LINE('Total rows: '||emp_table.COUNT);
16 END;
17 /
DataBase Trigger
What is a mutating and constraining table?
"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result
Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.
Functions Q: What is Fincation?
A function is similar to a procedure except that a function must return a value. Function can accept parameters or not
The simplified syntax for the CREATE FUNCTION statement is as follows:
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN type
{IS | AS}
BEGIN
function_body
END function_name;
Function with no parameters
Function with parameters
Function with parameters
Function to calculate raise as 10% of the salary
Function to calculate raise as % of the salary
Function to calculate total as sal + comm
Function returns week No of the Month
Functions return name of a Month
Functions return Quartner No of the year
Functions return Julian Date
Julian date must be between 1 and 5373484
Functions return name of a specific date
Functions return username
Functions return sysdate
Functions return default nationality
Functions return Max salary
Position Notation calls for the parameters
Name Notation calls for the parameters
Mixed Name and position Notation calls for the parameters
Database Trigger
DataBase Trigger Q: what is a Trigger ?
It is a PLSQL Block that is associated with specific table, view, schema, Database and fire implecitly when a specific event occure
Q: what are the trigger’s Types?
1. DataBase Trigger Fire When specific Database event Occurs (Database)
2. Application Trigger Fire When specific Event Occurs With Particular Application (Forms, report)
Q: what are the objects that the DB trigger associate with ?
1. Table 2. View 3. schema 4. Database
Q: what is the structur of the Trigger Code?
Part 1 Mandatory
Trigger Timing After – Before (Tables) – Instead of(View) Part 2
Mandatory
Trigger Event 1. DB Event 1. DML Trigger
Insert , update , update of <column > , delete 2. System Event Create , alter , drop , Logon , logoff ,
shutdown,startup,servererror Part 3
Optinal
Trigger Type For each row 1. The trigger body executes once for each row affected by the trigger event.
2. use :NEW , :OLD functions For Each Statement
[Default]
1. The trigger body executed for each bulk of rows affected by the trigger event .
2. NOT use :NEW , :OLD functions Part 4
Optional Trigger Condition When <Condition> If you want to restirct a specific condition
Part 5
Mandatory Trigger Action The actions that the trigger will execute when the event occur
Trigger Syntax
DDL Triggers Events
Avaliable Functions
BEFORE / AFTER ALTER
1.
ora_sysevent
BEFORE / AFTER CREATE
BEFORE / AFTER DROP
BEFORE / AFTER RENAME
BEFORE / AFTER ANALYZE
BEFORE / AFTER ASSOCIATE
STATISTICS
BEFORE / AFTER DISASSOCIATE
STATISTICS
BEFORE / AFTER AUDIT
BEFORE / AFTER NOAUDIT
BEFORE / AFTER COMMENT
BEFORE / AFTER DDL
BEFORE / AFTER GRANT
BEFORE / AFTER REVOKE
BEFORE / AFTER TRUNCATE
AFTER SUSPEND
2.
ora_client_ip_address
3.
Ora_database_name
4.
ora_des_encrypted_password
5.
ora_dict_obj_name
6.
ora_dict_obj_name_list
7.
ora_dict_obj_owner
8.
ora_dict_obj_type
9.
ora_grantee
10. ora_instance_num
11. ora_is_alter_column
12. ora_is_creating_nested_table
13. ora_is_drop_column
14. ora_is_servererror
15. ora_login_user
16. ora_partition_pos
17. ora_privilege_list
18. ora_revokee
19. ora_server_error
20. ora_server_error_depth
21. ora_server_error_msg
22. ora_server_error_num_params
23. ora_server_error_param
24. ora_sql_txt
25. ora_with_grant_option
26. space_error_info
What is a mutating and constraining table?
"
Mutating" means "changing". A mutating table is a table that
is currently being modified by an update, delete, or insert
statement. When a trigger tries to reference a table that is
in state of flux (being changed), it is considered "mutating"
and raises an error since Oracle should not return data that
has not yet reached its final state.
Another way this error can occur is if the trigger has
statements to change the primary, foreign or unique key
columns of the table off which it fires. If you must have
triggers on tables that have referential constraints, the
workaround is to enforce the referential integrity through
triggers as well.
There are several restrictions in Oracle regarding triggers:
A row-level trigger cannot query or modify a mutating table.
(Of course, NEW and OLD still can be accessed by the trigger)
.
A statement-level trigger cannot query or modify a mutating
table if the trigger is fired as the result
Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much
code as possible in procedures rather than triggers. At that
stage procedures executed faster than triggers as triggers
had to be re-compiled every time before executed (unless
cached). In more recent releases both triggers and procedures
are compiled when created (stored p-code) and one
create table company
(product_id number(4) not null, company_id NUMBER(8) not null,
company_short_name varchar2(30) not null, company_long_name varchar2(60));
insert into company values(1,1001,'A Inc.','Long Name A Inc.');
insert into company values(1,1002,'B Inc.','Long Name B Inc.');
insert into company values(1,1003,'C Inc.','Long Name C Inc.');
insert into company values(2,1004,'D Inc.','Long Name D Inc.');
insert into company values(2,1005,'E Inc.','Long Name E Inc.');
insert into company values(2,1006,'F Inc.','Long Name F Inc.');
create table product_audit (product_id number(4) not null,
num_rows number(8) not null);
CREATE OR REPLACE TRIGGER myTrigger AFTER INSERT ON company
FOR EACH ROW
DECLARE BEGIN
UPDATE product_audit
SET num_rows =num_rows+1
WHERE product_id =:NEW.product_id; IF (SQL%NOTFOUND) THEN
INSERT INTO product_audit VALUES (:NEW.product_id,1);
END IF;
END; /
insert into company values(3,1007,'E Inc.','Long Name E Inc.');
Examples SQL> create table emplpyee (empno number(5), name char(10), sal number(5), comm number(5),total number(5)) SQL> Create or replace trigger Tri1 After update of sal , comm on emp For each row Begin Update emp Set total = sal+comm Where sal =:new.sal or comm =:new.comm; End; SQL> insert into e values(1,'ali',100,200,null); SQL> insert into e values(2,'mona',200,300,null); SQL> commit; SQL> update e set sal = 300 where id=1; ERROR at line 1: ORA04091: table SCOTT.E is mutating, trigger/function may not see it ORA06512: at "SCOTT.TRI1", line 2 ORA04088: error during execution of trigger 'SCOTT.TRI1' Error Calrification IN Oracle you cann’t insrt into table X and run trigger to update the same table But you can run the trigger to update another table b because it is related to internal constraint that violate this issue
Instead OF Trigger
Use instead of trigger when you want to insert or update or
delete on unupdatable View (Read Only View) because the instead
of trigger works invisibly on the underlying table
Name Null? Type
--- --- DEPTNO NOT NULL NUMBER(5) Primary Key
DNAME CHAR(20) LOC CHAR(20)
Name Null? Type
--- --- EMPNO NOT NULL NUMBER(5)
ENAME CHAR(20) SAL NUMBER(5)
DEPTNO NUMBER(5) References d:deptno
Create or replace view v
As select e.empno , e.ename , e.sal , e.deptno , d.deptno as
deptatmentID , d.dname , d.loc
From e e , d d
Where e.deptno = d.deptno Create or replace trigger Tri1 Instead of insert on v
For each row Begin
Insert into d Values(:new.deptno , :new.dname ,:new.loc);
Insert into E Values(:new.empno , :new.ename ,:new.sal ,:new.deptno); End;
Insert into v
Values(1,’ahmed’,100,1,1,’sales’,’cairo’);
You must insert data into view in the same sequence for that view columns Q: How do I Enable and Disable the Trigger
SQL> Alter trigger <Trigger_name> Disable|Enable
Q: How do I Enable and Disable all trigger for a table SQL> Alter table <Table_name> Disable|Enable
Q: How do I Compile the trigger
SQL> Alter Trigger <Trigger_name> compile Q: How do I drop the trigger
SQL> drop Trigger <Trigger_name>
System Triggers
1.
DDL Trigers
http://www.psoug.org/reference/ddl_trigger.html
DDL Triggers Events
Avaliable Functions
BEFORE / AFTER ALTER
BEFORE / AFTER CREATE
BEFORE / AFTER DROP
BEFORE / AFTER RENAME
BEFORE / AFTER ANALYZE
BEFORE / AFTER ASSOCIATE
STATISTICS
BEFORE / AFTER DISASSOCIATE
STATISTICS
BEFORE / AFTER AUDIT
BEFORE / AFTER NOAUDIT
BEFORE / AFTER COMMENT
BEFORE / AFTER DDL
BEFORE / AFTER GRANT
BEFORE / AFTER REVOKE
BEFORE / AFTER TRUNCATE
AFTER SUSPEND
27. ora_sysevent
28. ora_client_ip_address
29. Ora_database_name
30. ora_des_encrypted_password
31. ora_dict_obj_name
32. ora_dict_obj_name_list
33. ora_dict_obj_owner
34. ora_dict_obj_type
35. ora_grantee
36. ora_instance_num
37. ora_is_alter_column
38. ora_is_creating_nested_table
39. ora_is_drop_column
40. ora_is_servererror
41. ora_login_user
42. ora_partition_pos
43. ora_privilege_list
44. ora_revokee
45. ora_server_error
46. ora_server_error_depth
47. ora_server_error_msg
48. ora_server_error_num_params
49. ora_server_error_param
50. ora_sql_txt
51. ora_with_grant_option
52. space_error_info
Example No:10
Create Table tets_DDL (server_Event varchar2(50), object_owner varchar2(50), object_name varchar2(50), user_name varchar2(50), system_date date)
CREATE OR REPLACE TRIGGER test_DDL
BEFORE CREATE or ALTER or DROP ON SCHEMA
BEGIN
INSERT INTO tets_ddl
SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, USER, SYSDATE FROM dual;
END;
Example No:11
Conn sys/password@<service_name> as sysdba; Create table ddl_log
(server_event char(40), owner char(20), objectname char(20), text varchar(200), username char(20), transaction_date date);
CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER OR DROP ON SCHEMA DECLARE oper varchar2(50); BEGIN SELECT ora_sysevent INTO oper FROM dual;
IF oper IN ('CREATE', 'DROP') THEN INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, NULL, USER, SYSDATE FROM dual;
ELSIF oper = 'ALTER' THEN INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,ora_dict_obj_name, sql_text, USER, SYSDATE
FROM sys.gv$open_cursor
WHERE UPPER(sql_text) LIKE 'ALTER%'; END IF;
END;
CREATE OR REPLACE TRIGGER save_our_db
BEFORE DROP OR TRUNCATE ON SCHEMA
DECLARE oper varcha2(20); BEGIN SELECT ora_sysevent INTO oper FROM dual;
IF oper = 'DROP' THEN
RAISE_APPLICATION_ERROR(-20998, 'Attempt To Drop In Production Has Been Logged'); ELSIF oper = 'TRUNCATE' THEN
RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A Production Table Has Been Logged'); END IF;
END;
DDL Trigger To Prevent Creating Objects That Whose Names
Begin With The Letter 'X'
CREATE OR REPLACE TRIGGER no_xtabs BEFORE CREATE ON SCHEMA
DECLARE x user_tables.table_name%TYPE; BEGIN SELECT ora_dict_obj_name INTO x FROM dual; IF SUBSTR(x, 1, 1) = 'X' THEN
RAISE_APPLICATION_ERROR(-20099, 'Table Names Can Not Start With The Letter X'); END IF;
END; /
2.
System Triggers
http://www.psoug.org/reference/system_trigger.html
System Event Trigger Types
AFTER LOGON AFTER STARTUP BEFORE LOGOFF BEFORE SHUTDOWN
AFTER SERVERERROR (does not trap
CREATE TABLE connection_audit (login_date DATE,
username VARCHAR2(30));
CREATE OR REPLACE TRIGGER logon_audit AFTER LOGON on database
BEGIN
INSERT INTO connection_audit(login_date, username) VALUES (SYSDATE, USER);
END;
Oracle Complete PLSQL Reference
CREATE TABLE log_logons (sqltext VARCHAR2(25) NOT NULL);
CREATE OR REPLACE PROCEDURE logproc Is
Begin
INSERT INTO log_logons(sqltext)
VALUES(user || ' - ' || TO_CHAR(SYSDATE)|| ' - '||'Logged On' ); End;
CREATE OR REPLACE TRIGGER logintrig AFTER LOGON ON DATABASE
CALL logproc
Oracle Complete PLSQL Reference Create table ip (ip_add char(20), user_name char(20), logon_user char(20), db_name char(20), instance_no number(5), logon_date date);
CREATE OR REPLACE TRIGGER ip_trace AFTER LOGON ON DATABASE
BEGIN IF (ora_sysevent=’LOGON’) THEN INSERT INTO IP VALUES(ora_client_ip_address,user,ora_login_user,Ora_database_name,ora_instance_num,sysdate); END IF; END;
I want to display all object names that has been altered
create table w
(username char(20),
object_name varchar2(255));
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER ON SCHEMA
BEGIN
INSERT INTO w VALUES(user,ora_dict_obj_name);
END ;
alter table dept
add constraint a check (dname is not null);
alter user scott identified by scott;
I want to display descripted password for the altered user
create table encpassword
(username char(20),
enc_user char(255),
encpassword varchar2(255));
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER ON DATABASE
BEGIN
INSERT INTO encpassword
VALUES(user,ora_dict_obj_name,ora_des_encrypted_password);
END;
Alter user scott identifiec by abc;
Select * from encpassword
Server Error Trigger
SQL > create table caught_errors (dt date, username varchar2( 30), msg varchar2(512), stmt varchar2(512));
create or replace trigger catch_errors After servererror on database
Declare
sql_text ora_name_list_t; msg_ varchar2(2000) := null; stmt_ varchar2(2000) := null; begin
for depth in 1 .. ora_server_error_depth loop msg_ := msg_ || ora_server_error_msg(depth); End loop;
For i in 1 .. ora_sql_txt(sql_text) loop stmt_ := stmt_ || sql_text(i);
End loop;
Insert into caught_errors (dt,username,msg ,stmt )values (sysdate,ora_login_user,msg_,stmt_); end;
Trigger Privelages
System Privileges
create trigger
create any trigger
administer database trigger -- required
for ON DATABASE
alter any trigger
drop any trigger
Triggers Data Dictionary
dba_triggers all_triggers user_triggers
You Can Enable And Disable Any Trigger According To the following Codes
Function
1. create or replace function emp_count
return number is
cnt number(2) := 0; begin
select count(*) into cnt from emp ;
return (cnt); end;
/ 2.
create or replace function emp_count (p_deptno in number) return number
is
cnt number(2) := 0; begin
select count(*) into cnt from emp
where deptno = p_deptno ; return (cnt);
end; / 3.
update emp set comm = sal where deptno = 10 ; commit;
create or replace function fun1 (p1 number , p2 number) return number is begin if p1 = p2 then return (0); else return (1); end if; end; / 4.
select ename , sal , sal*0.10 as raise from emp;
Create or replace function raise10(p_sal number) Return number
Is Begin
Return (p_sal * 0.10);
End;
Create or replace function raise(p_sal number ,p_percentage number ) Return number
Is Begin
Return (p_sal * p_percentage); End;
5.
select ename , sal , comm, nvl(sal,0)+nvl(comm,0) as total from emp;
Create or replace function total (p_sal number , p_comm number) return number is Begin return (nvl(p_sal,0)+nvl(p_comm,0)); End; 6.
select ename , hiredate , TO_CHAR(hiredate,'W') from emp;
Create or replace function week_month(p_date IN date) RETURN NUMBER
IS BEGIN
RETURN ( TO_NUMBER( TO_CHAR( p_date, 'W' ) ) ); END;
7.
select ename, hiredate , TO_CHAR(hiredate, 'fmMonth') as Month_name from emp;
Create or replace function Month_Name ( p_date IN DATE ) RETURN VARCHAR2
IS BEGIN
RETURN ( TO_CHAR( p_date, 'fmMonth' ) ); END Month_Name;
8.
select ename , hiredate , TO_CHAR( hiredate, 'Q' ) from emp
where deptno = 10 ;
Create or replace function quarter( p_date IN DATE ) RETURN NUMBER
IS BEGIN
RETURN ( TO_NUMBER( TO_CHAR( p_date, 'Q' ) ) ); END;
9- Function that return julian date from date select hiredate , TO_CHAR(hiredate, 'J' ) from emp
where deptno = 10 ;
Create or replace function julianfromdate(p_date IN date) RETURN NUMBER
IS BEGIN
RETURN (TO_NUMBER(TO_CHAR( p_date, 'J' ) ) ); END;
10- function that return date form julian
Create or replace function datefromjulian(p_num IN NUMBER ) RETURN DATE
IS BEGIN
IF p_num BETWEEN 1 and 5373484 THEN RETURN (TO_DATE(TRUNC(p_num ),'J')); ELSE
RAISE_APPLICATION_ERROR (-01854, 'Julian date must be between 1 and 5373484'); END IF;
END;
11- Function that return dayname
select ename ,hiredate , TO_CHAR( hiredate, 'fmDay') from emp
where deptno = 10 ;
Create or replace function dayname ( p_date IN DATE ) RETURN VARCHAR2
IS BEGIN
RETURN ( TO_CHAR( p_date, 'fmDay' ) ); END;
12.
Create Or Replace Function username RETURN VARCHAR2
IS BEGIN
RETURN user; END;
13. Function that return current date = sysdate select sysdate from dual;
Create Or Replace Function current_date RETURN date
IS BEGIN
RETURN sysdate; END;
14- Function that default value for nationality column
create table employee (name char(4),nationality varchar2(20)); Create Or Replace Function default_nationality
RETURN varchar2 IS
BEGIN
RETURN ‘egyptian’; END;
Insert into employee
Values('ali', default_nationality); 15- Function that return max_sal_emp select max(sal) from emp;
Create Or Replace Function max_sal_emp RETURN number IS v_max_sal number(5); BEGIN Select max(sal) Into v_max_sal From emp; RETURN v_max_sal; END;
16- Function that return statistic
select max(sal) , min(sal) , avg(sal) , sum(sal) from emp;
Create Or Replace Function Max_Min_Avg_Sum RETURN varchar2
IS
v_max_sal number(5); v_min_sal number(5);
v_avg_sal number(5); v_sum_sal number(5); BEGIN
Select max(sal) , min(sal), avg(sal) , sum(sal) Into v_max_sal , v_min_sal , v_avg_sal , v_sum_sal From emp;
RETURN (to_char(v_max_sal) || '-' || to_char(v_min_sal) || '-' || to_char(v_avg_sal) || '-' || to_char(v_sum_sal)); END;
17.
CREATE OR REPLACE FUNCTION add_three_numbers( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS BEGIN RETURN a + b + c; END; / BEGIN dbms_output.put_line(add_three_numbers(3,4,5)); END; / 18.
CREATE OR REPLACE FUNCTION add_three_numbers( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS BEGIN RETURN a + b + c; END; / BEGIN dbms_output.put_line(add_three_numbers(a => 4,b => 5,c => 3)); END; 19.
CREATE OR REPLACE FUNCTION add_three_numbers( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS BEGIN RETURN a + b + c; END; / BEGIN dbms_output.put_line(add_three_numbers(a => 4,b => 5,c => 3)); END;
Package
create table dd (deptno number(5),deptname varchar2(30),deptloc varchar2(5)); create sequence sqdept start with 1 increment by 1; CREATE or replace package over_pack IS / AS Procedure add_dept(dno number,dname varchar2,dloc varchar2); Procedure add_dept(dname varchar2,dloc varchar2); End; / CREATE or replace package BODY over_pack Is Procedure add_dept(dno number,dname varchar2,dloc varchar2) Is Begin Insert into dd values(dno , dname ,dloc); End; Procedure add_dept(dname varchar2,dloc varchar2) Is Begin Insert into dd values(sqdept.nextval , dname ,dloc); End; End; / exec over_pack.add_dept(’design’,’cairo’); exec over_pack.add_dept(1,’sales’,’alex’);
CREATE or replace package over_pack Is function total (v_sal number , v_comm number)return number; function max_sal_emp return number; End; CREATE or replace package body over_pack Is function total(v_sal number , v_comm number) return number is Begin return (nvl(v_sal,0)+nvl(v_comm,0)); End; Function max_sal_emp RETURN number IS v_max_sal number(5); BEGIN Select max(sal) Into v_max_sal From emp; RETURN (v_max_sal); END; End; / select sal , comm, over_pack.total(sal,comm) from emp ; select over_pack.max_sal_emp from dual;
System Package
DBMS_ALERT( 6 ) dbms_application_info( 4 ) DBMS_AQADM( 3 ) dbms_crypto( 8 ) DBMS_DB( 1 ) dbms_ddl( 1 )
DBMS_DEBUG( 1 ) DBMS_FGA( 1 ) DBMS_FILE_TRANSFER( 1 ) dbms_flashback( 3 ) dbms_job( 8 ) dbms_lob( 17 )
dbms_lock( 1 ) DBMS_METADATA( 2 ) DBMS_OBFUSCATION_TOOLKIT( 6 ) dbms_output( 16 ) DBMS_PIPE( 13 ) DBMS_RANDOM( 12 )
DBMS_REDEFINITION( 1 ) DBMS_REPAIR( 4 ) dbms_rowid( 7 ) dbms_scheduler( 1 ) DBMS_SESSION( 1 ) dbms_space( 1 )
dbms_sql( 27 ) dbms_stats( 12 ) DBMS_TRACE( 2 ) dbms_utility( 15 ) DBMS_WARNING( 2 ) dbms_xmlquery( 1 )
dbms_xmlschema( 2 ) htp( 1 ) ORA Error( 13 ) ORA( 18 ) outln_pkg( 1 ) TEXT_IO( 1 )
UTL_COMPRESS( 1 ) UTL_FILE( 9 ) UTL_HTTP( 1 ) utl_i18n( 1 ) UTL_INADDR( 1 ) UTL_MAIL( 1 )
utl_raw( 27 ) UTL_SMTP( 1 ) UTL_TCP( 1 )
Implicit Cursor http://www.java2s.com/Tutorial/Oracle/0500__Cursor/sqlnotfound.htm http://www.java2s.com/Tutorial/Oracle/0500__Cursor/UsingSELECTinaCursor.htm http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Cursorforobjecttable.htm http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Cursorwithorderby.htm http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Cursorforaggregatefunction.htm http://www.java2s.com/Tutorial/Oracle/0500__Cursor/FetchingAcrossCommitsExample2.htm http://www.java2s.com/Tutorial/Oracle/0500__Cursor/CursorFORLoop.htm http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Closecursorinexcpetionhandler.htm SQL > Create or replace procedure a Is Cursor Emp_Rec_Cur is select ename , deptno from emp; Begin for i in emp_rec_cur loop implicit open and implicit Fetch if i.deptno= 20 then dbms_output.put_line(i.ename || ' ' || i.deptno); end if; end loop; implicit close and implicit loop Exit end; the prvious example is equal to next example SQL > Create or replace procedure a Is Begin
for i in (select ename , deptno from emp) loop implicit open and implicit Fetch if i.deptno= 20 then dbms_output.put_line(i.ename || ' ' || i.deptno); end if; end loop; implicit close and implicit loop Exit end;
SQL%ROWCOUNT and SQL%BULK_ROWCOUNT
http://www.java2s.com/Tutorial/Oracle/0500__Cursor/usingSQLBULKROWCOUNTandSQLROWCOUNT.htm
Use cursor subquery
http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Usethecursorsubquery.htm Nested Cursor
http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Nestedcursor.htm VARRAY of Cursor
http://www.java2s.com/Tutorial/Oracle/0500__Cursor/VARRAYofCursor.htm
Assigning different queries to the same cursor variable
Ref Cursorhttp://www.java2s.com/Tutorial/Oracle/0500__Cursor/Assigningdifferentqueriestothesamecursorvariable.htm
Execute immediate statement
BEGIN
FOR i IN (SELECT table_name FROM user_tables
WHERE table_name = 'DEPT2' ) LOOP
6 EXECUTE IMMEDIATE 'DROP TABLE dummy ;' 7 8 END LOOP ; 9 10 END; http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Returninformationusingcursorstatusvariables.htm
Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)'; -- Using bind variables...
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; -- Returning a cursor...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer; rc integer; BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE); rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur); END;
/
More complex DBMS_SQL example using bind variables:
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS v_cursor integer;
v_dname char(20); v_rows integer; BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7); DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no); DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20); v_rows := DBMS_SQL.EXECUTE(v_cursor); loop if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then exit; end if; DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname); DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname); end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor); EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '|| sqlerrm);
END; /
Back to top of file
Execute procedure 1. exec rep1; 2. exec rep1()p1,p2,p3,……; 3. call rep1(); 4. call rep1(p1,p2,p3,…….); Triggers
