Post on 14-Apr-2018
7/29/2019 PLSQL Presentation
1/49
PL/SQL
7/29/2019 PLSQL Presentation
2/49
PL/SQL Block Structure
DECLARE (optional)
BEGIN (required)
EXCEPTION (optional)
END; (required)
7/29/2019 PLSQL Presentation
3/49
Comments in PL/SQL Block
Single Line(-- Comment)
Multi Line(/* Comment */)
7/29/2019 PLSQL Presentation
4/49
PL/SQL Block Types
Anonymous
DECLAREBEGIN
-statements
EXCEPTION
END;
Procedure
PROCEDURE(param1,2..)
IS
BEGIN
-statements
EXCEPTIONEND;
Function
FUNCTION>(param1,2
..)
RETURN
IS
BEGIN-statements
EXCEPTION
END;
7/29/2019 PLSQL Presentation
5/49
PL/SQL placeholders
All SQL types are supported by PL/SQL Numerical types
NUMBER, PLS_INTEGER
Many derived types, e.g. POSITIVE
Character types
CHAR, VARCHAR2, NCHAR,
Other scalar types
BOOLEAN, DATE
7/29/2019 PLSQL Presentation
6/49
PL/SQL placeholders
Scalar type
variable
constant
Composite/vector type
record - used for reading rows from table
7/29/2019 PLSQL Presentation
7/49
PL/SQL placeholders
Scalar type
variable
Constant
DECLAREl_x NUMBER := 20000;
l_message VARCHAR2(40);
C_PI CONSTANT NUMBER(3,2):=3.14;
BEGIN
l_x := 1000 * C_PI;
l_message := 'Hello world';
END;
7/29/2019 PLSQL Presentation
8/49
Attributes %TYPE and %ROWTYPE
%TYPE references type of a variable or a
database column
%ROWTYPE references type of a record
structure, table row or a cursor
Advantage: an actual type does not need to be known
7/29/2019 PLSQL Presentation
9/49
%TYPE and %ROWTYPE examples
balance NUMBER(7,2);
minimum_balance balance%TYPE := 10.00;
my_dname dept.dname%TYPE;
dept_rec dept%ROWTYPE;
SELECT deptno, dname, loc INTO dept_rec
FROM dept WHERE deptno = 30;
7/29/2019 PLSQL Presentation
10/49
User-Defined Records
Defining a Record
TYPE
type_name IS RECORD
( field_name1 datatype1 ,field_name2 datatype2 );
record-name type_name;
DECLARE
TYPE books IS RECORD
( title varchar(50),
author varchar(50),subject varchar(100),
book_id number);
book1 books;
book2 books;
7/29/2019 PLSQL Presentation
11/49
PL/SQL Control Structures
Conditional Control Using IF and CASE statements
7/29/2019 PLSQL Presentation
12/49
Example
DECLARE
l_sales NUMBER(8,2) := 20000;
l_bonus NUMBER(6,2);
BEGIN
IF l_sales > 50000 THEN l_bonus :=
1500;
ELSIFl_sales > 35000 THENl_bonus :=
500;ELSEl_bonus := 100;
END IF;
UPDATE employees SET salary = salary +
l_bonus;
END;
DECLARE
l_grade CHAR(1) := 'B';
BEGIN
CASEl_grade
WHEN'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN'B' THEN
DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN'C' THEN
DBMS_OUTPUT.PUT_LINE('Good');
WHEN'D' THEN
DBMS_OUTPUT.PUT_LINE('Fair');
WHEN'F' THEN
DBMS_OUTPUT.PUT_LINE('Poor');
ELSEDBMS_OUTPUT.PUT_LINE('No
such grade');
END CASE;
END;
7/29/2019 PLSQL Presentation
13/49
PL/SQL Control Structures
Iterative loops Simple loop (infinite)
WHILE loop
FOR loop
Numeric range
Reversed
Cursor based
DECLARE
l_i NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_i));
l_i:=l_i+1;
END LOOP;
WHILEl_i < 10 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
l_i := l_i + 1;
END LOOP;
FORl_i IN 1..500 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
END LOOP;
FORl_i INREVERSE1..3 LOOPDBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
END LOOP;
END;
P i P d d
7/29/2019 PLSQL Presentation
14/49
Parameters in Procedure and
Functions
In PL/SQL, we can pass parameters to procedures andfunctions in three ways.
1) IN type parameter: These types of parameters are usedto send values to stored procedures.2) OUT type parameter: These types of parameters areused to get values from stored procedures. This is similar toa return type in functions.3) IN OUT parameter: These types of parameters are usedto send values and get values from stored procedures.
NOTE: If a parameter is not explicitly defined a parametertype, then by default it is an IN type parameter.
7/29/2019 PLSQL Presentation
15/49
Example
DECLARE
a number;
b number;
c number;
PROCEDURE findSum(x IN number, y IN number, z IN OUT number)
IS
BEGIN
z:=x+y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findSum (a, b, c);
dbms_output.put_line(' Sum of (23, 45) : ' || c);
END;
7/29/2019 PLSQL Presentation
16/49
Cursors
7/29/2019 PLSQL Presentation
17/49
What are Cursors?
A cursor is a temporary work area created in the
system memory when a SQL statement is
executed. A cursor contains information on a
select statement and the rows of data accessedby it.
There are two types of cursors in PL/SQL:
Implicit cursors
Explicit cursors
7/29/2019 PLSQL Presentation
18/49
Implicit Cursors
Automatically created and destroyed by
Oracle.
Outcome of the statement can be determined
by using
SQL%ATTRIBUTE_NAME
Some attributes are explained on the next
slide.
7/29/2019 PLSQL Presentation
19/49
7/29/2019 PLSQL Presentation
20/49
Example
DECLARE var_rows number(5);
BEGIN
UPDATE employee
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees areupdated');
END IF;
END;
7/29/2019 PLSQL Presentation
21/49
Explicit Cursors
An explicit cursor is defined in the declaration sectionof the PL/SQL Block.
General Syntax for creating a cursor is as given below:
CURSOR cursor_name IS select_statement;
cursor_name A suitable name for the cursor.
select_statementA select query which returnsmultiple rows.
7/29/2019 PLSQL Presentation
22/49
How to use Explicit Cursor?
There are four steps in using an Explicit Cursor.
DECLARE the cursor in the declaration section.
OPEN the cursor in the Execution Section. FETCH the data from cursor into PL/SQL
variables or records in the Execution Section.
CLOSE the cursor in the Execution Sectionbefore you end the PL/SQL Block.
7/29/2019 PLSQL Presentation
23/49
Declaration
DECLARE
CURSOR emp_cur IS
SELECT *FROM emp_tbl
WHERE salary > 5000;
7/29/2019 PLSQL Presentation
24/49
How to access an Explicit Cursor?
These are the three steps in accessing the cursor.1) Open the cursor.
OPEN cursor_name;
2) Fetch the records in the cursor one at a time.FETCH cursor_name INTO record_name;
ORFETCH cursor_name INTO variable_list;
3) Close the cursor.
CLOSE cursor_name;
7/29/2019 PLSQL Presentation
25/49
Cursor with a Simple Loop
DECLARE
CURSOR emp_cur IS
SELECT first_name, last_name, salary FROM emp_tbl;
emp_rec emp_cur %rowtype;
BEGIN
IF NOT sales_cur%ISOPEN THEN
OPEN sales_cur;
END IF;
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name|| ' ' ||emp_cur.salary);
END LOOP;
END;
7/29/2019 PLSQL Presentation
26/49
Cursor with a While Loop
DECLARE
CURSOR emp_cur IS
SELECT first_name, last_name, salary FROM emp_tbl;
emp_rec emp_cur%rowtype;
BEGIN
IF NOT sales_cur%ISOPEN THEN
OPEN sales_cur;
END IF;
FETCH sales_cur INTO sales_rec;
WHILE sales_cur%FOUND THEN
LOOP
dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name|| ' ' ||emp_cur.salary);
FETCH sales_cur INTO sales_rec;
END LOOP;
END;
7/29/2019 PLSQL Presentation
27/49
Cursor with a FOR Loop
DECLARE
CURSOR emp_cur IS
SELECT first_name, last_name, salary FROM emp_tbl;
BEGIN
FOR emp_rec in c1
LOOP
dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
|| ' ' ||emp_cur.salary);
END LOOP;
END;
7/29/2019 PLSQL Presentation
28/49
Parameterized Cursor
DECLARE
cursor c(no number) is select * from emp_information
where emp_no = no;
tmp emp_information%rowtype;
BEGIN
FOR tmp IN c(4) LOOP
dbms_output.put_line('EMP_No: '||tmp.emp_no);
dbms_output.put_line('EMP_Name: '||tmp.emp_name);
dbms_output.put_line('EMP_Dept: '||tmp.emp_dept);
dbms_output.put_line('EMP_Salary:'||tmp.emp_salary);
END LOOP;
END;
7/29/2019 PLSQL Presentation
29/49
Exception Handling
7/29/2019 PLSQL Presentation
30/49
Exception Handling
PL/SQL Exception message consists of three
parts.
1) Type of Exception
2) An Error Code3) A message
7/29/2019 PLSQL Presentation
31/49
Structure of Exception Handling
DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN-Error handling statements
END;
7/29/2019 PLSQL Presentation
32/49
Types of Exception
There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions
7/29/2019 PLSQL Presentation
33/49
a) Named System Exceptions
System exceptions are automatically raised by Oracle.
There are some system exceptions which are raisedfrequently, so they are pre-defined and given a name inOracle which are known as Named System Exception.
E.g.:
BEGIN
Execution section
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('A SELECT...INTO did not return anyrow.');
END;
7/29/2019 PLSQL Presentation
34/49
Example
7/29/2019 PLSQL Presentation
35/49
Unnamed System Exceptions
The system exception for which oracle does
not provide a name is known as unnamed
system exception.
These exception do not occur frequently.
These Exceptions have a code and an
associated message.
7/29/2019 PLSQL Presentation
36/49
Syntax
DECLARE
exception_name EXCEPTION;
PRAGMA
EXCEPTION_INIT (exception_name, Err_code);
BEGIN
Execution section
EXCEPTION
WHEN exception_name THEN
handle the exception
END;
7/29/2019 PLSQL Presentation
37/49
Example
Example: Lets consider the product table and order_items table.
Here product_id is a primary key in product table and a foreign key in order_items table.
If we try to delete a product_id from the product table when it has child records in order_id
table an exception will be thrown with oracle code number -2292.
We can provide a name to this exception and handle it in the exception section as given
below.
DECLARE
Child_rec_exception EXCEPTION;
PRAGMA
EXCEPTION_INIT (Child_rec_exception, -2292);
BEGINDelete FROM product where product_id= 104;
EXCEPTION
WHEN Child_rec_exception
THEN Dbms_output.put_line('Child records are present for this product_id.');
END;
7/29/2019 PLSQL Presentation
38/49
User-defined Exceptions
Apart from system exceptions we can explicitlydefine exceptions based on business rules.
Steps to be followed to use user-defined
exceptions: They should be explicitly declared in thedeclaration section. They should be explicitly raised in the Execution
Section. They should be handled by referencing the user-defined exception name in the exception section.
7/29/2019 PLSQL Presentation
39/49
Example
DECLARE
c_id customers.id%type := &cc_id;
c_name customers.name%type;
c_addr customers.address%type;
ex_invalid_id EXCEPTION;
BEGIN
IF c_id
7/29/2019 PLSQL Presentation
40/49
RAISE_APPLICATION_ERROR ( )
RAISE_APPLICATION_ERROR is a built-in procedure inoracle which is used to display the user-defined errormessages along with the error number whose range is inbetween -20000 and -20999.
RAISE_APPLICATION_ERROR raises an exception butdoes not handle it.
RAISE_APPLICATION_ERROR is used for the followingreasons,
a) to create a unique id for an user-defined exception.b) to make the user-defined exception look like anOracle error.
7/29/2019 PLSQL Presentation
41/49
Syntax
RAISE_APPLICATION_ERROR (error_number,error_message); The Error number must be between -20000 and -20999 The Error_message is the message you want to displaywhen the error occurs.
Steps to be folowed to use RAISE_APPLICATION_ERRORprocedure:1. Declare a user-defined exception in the declarationsection.2. Raise the user-defined exception based on a specific
business rule in the execution section.3. Finally, catch the exception and link the exception to auser-defined error number in RAISE_APPLICATION_ERROR.
7/29/2019 PLSQL Presentation
42/49
Example
CREATE OR REPLACE PROCEDURE Register is
BEGIN
RAISE_APPLICATION_ERROR(-20000, 'Can''t add more Employee');
EXCEPTIONWHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(20001, ' doesn''t exist!');
END Register;
7/29/2019 PLSQL Presentation
43/49
SQLCODE and SQLERRM
SQLCODE: It returns the error number for the error.SQLERRM: It returns the actual error message ofthe error.
When a SQL statement raises an exception,Oracle captures the error codes by using theSQLCODE and SQLERRM globally-definedvariables.
SQLCODE and SQLERRM can track exceptions thatare handled by the OTHERS clause of theexception handler.
7/29/2019 PLSQL Presentation
44/49
Example
Declarev_sal1 emp.empno%Type := &sal1;v_ename1 emp.ename%Type;
Begin
Select ename into v_ename1
From emp Where sal=v_sal1;dbms_output.put_line('Name: '||v_ename1);
ExceptionWhen no_data_found then
dbms_output.put_line('Employee not found with '||v_sal1);When others then
dbms_output.put_line('The program received an error. Errormessage returned was: ' || SQLCODE || ',' || SQLERRM);
End;
7/29/2019 PLSQL Presentation
45/49
Triggers
A trigger is a pl/sql block structure which is
fired when a DML statements like Insert,
Delete, Update is executed on a database
table.
A trigger is triggered automatically when an
associated DML statement is executed.
7/29/2019 PLSQL Presentation
46/49
Syntax
CREATE [OR REPLACE ] TRIGGER trigger_name{BEFORE | AFTER }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN--- sql statements
END;
7/29/2019 PLSQL Presentation
47/49
Example
CREATE OR REPLACE TRIGGER audit_sal
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO emp_audit
VALUES( :old.employee_id, SYSDATE, :new.salary, :old.salary );
COMMIT;
END;
7/29/2019 PLSQL Presentation
48/49
Q & A
7/29/2019 PLSQL Presentation
49/49
Thank You