Transcript PL SQl

PL/SQL
What is PL/SQL



Procedural Language – SQL
An extension to SQL with design features of
programming languages (procedural and object
oriented)
PL/SQL and Java are both supported as internal
host languages within Oracle products.
Why PL/SQL







Acts as host language for stored procedures and
triggers.
Provides the ability to add middle tier business logic to
client/server applications.
Provides Portability of code from one environment to
another
Improves performance of multi-query transactions.
Provides error handling
Full-featured programming language
Interpreted language
PL/SQL BLOCK STRUCTURE
DECLARE (optional)
- variable declarations
BEGIN (required)
- SQL statements
- PL/SQL statements or sub-blocks
EXCEPTION (optional)
- actions to perform when errors occur
END; (required)
Comments


Enclosed within /* and */ for several lines’ comments
-- for single line comments
PL/SQL Variables and Data Types


Variable names must follow the Oracle naming
standard (Example: current_s_id, not $current_s_id)
Strongly typed language
All variables must be declared before their use.
 The assignment statement : = is not the same as the equality
operator =
 All statements end with a ;


Variable declaration syntax:
variable_name data_type_declaration;

Default value always NULL
6
Variable Naming Conventions


Two variables can have the same name if they are
in different blocks (bad idea)
The variable name should not be the same as any
table column names used in the block.
PL/SQL Variables
1.
2.
3.
4.
Scalar (char, varchar2, number, date, etc)
Composite (%rowtype)
Reference (pointers)
LOB (large objects)
1. Scalar Variables


Reference single value such as number, date, string
Data types correspond to Oracle 10g database data types
 VARCHAR2
 CHAR
 DATE
 NUMBER
9
Assigning a value to a variable

Assigning directly
var_emp_id:= ‘E101’
var_salary := 85100;

Assigning from the database, using SELECT query
SELECT emp_salary
INTO var_salary FROM employee
WHERE emp_id = var_emp_id;
2. Composite Variables



Data object made up of multiple individual data elements
Data structure contains multiple scalar variables
Composite variable data types include:
A
R
R
A
Y
RECORD (multiple scalar values similar to a table’s record)
TABLE (tabular structure with multiple columns and rows)
VARRAY (variable-sized array. Tabular structure that can expand or
contract based on data values)
11
Placeholders



Placeholders are temporary storage area.
PL/SQL Placeholders can be any of Variables,
Constants and Records.
Oracle defines placeholders to store data
temporarily, which are used to manipulate data
during the execution of a PL SQL block.
PL/SQL placeholders

Scalar type



variable
constant
Single composite/vector type

Record



Used for reading rows from table
Can contain multiple types of data
Collections
TYPE T_TIME IS RECORD (minutes INTEGER, hours NUMBER(2));
Associative Array
current_time_rec
T_TIME;
 Variable-sized Array
(VARRAY)
Current_time_rec.hours
:= 12;
 Nested Tables

PL/SQL placeholders
 Scalar type
DECLARE
 variable
TYPE
T_POPULATION IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
l_city_population
T_POPULATION;
 constant
l_i number;
BEGIN

Single composite/vector type := 2000;
l_city_population('Smallville')
l_i:=
l_city_population('Smallville') ;
 record
END;
 used for reading rows from table
/

Collections

PL/SQL placeholders

Scalar type
DECLARE
TYPE
 T_FOURSOME IS VARRAY(4) OF VARCHAR2(15);
l_team T_FOURSOME := T_FOURSOME('John', 'Mary', 'Alberto');
BEGIN 
l_team.EXTEND;
-- Append one null element
l_team(4):='Mike';
-- Set 5th element element
DBMS_OUTPUT.PUT_LINE( l_team( l_team.first ) ); -- Print first element
DBMS_OUTPUT.PUT_LINE(
l_team( l_team.last ) ); -- Print last element

variable
constant
Single composite/vector type
record

END;
/

used for reading rows from table
Collections

PL/SQL placeholders

Scalar type
DECLARE
TYPE
 T_ROSTER IS TABLE OF VARCHAR2(15);
l_names T_ROSTER := T_ROSTER('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
l_i 
number;
BEGIN
FOR l_i IN l_names.FIRST .. L_names.LAST LOOP --For first to last element
 DBMS_OUTPUT.PUT_LINE(l_names(l_i));
END LOOP;
END; 
/
 used for reading rows from table
variable
constant
Single composite/vector type
record

Collections

3. Reference Variables

Directly reference specific database column or row

Assume data type of associated column or row

%TYPE data declaration syntax: variable_name tablename.fieldname%TYPE;

%ROWTYPE data declaration syntax: variable_name tablename%ROWTYPE;
17
Attributes %TYPE & %ROWTYPE



%TYPE references type of a variable or a
database column
%ROWTYPE references type of a record structure,
table row or a cursor
Advantages:
 Actual
type does not need to be known
 referenced type had changed -> will be recompiled
automatically
%TYPE & %ROWTYPE Examples
variable declarations
balance
NUMBER(7,2);
minimum_balance
balance%TYPE := 10.00;
my_dname
scott.dept.dname%TYPE;
dept_rec
dept%ROWTYPE;
SELECT deptno, dname, loc
INTO dept_rec
FROM dept WHERE deptno = 30;
using record variable to read a row from a table
Writing a PL/SQL Program
1.
2.
3.
4.
5.
Write PL/SQL program in Notepad or another
text editor
Copy and paste program commands from text
editor into SQL*Plus
Press Enter after last program command
Type front slash ( / )
Then press Enter again
20
PL/SQL Sample Program
PL/SQL Sample Program
DECLARE
g_inv_value number(2);
v_price
number(8,2) := 10.25;
v_quantity
number(8,0) := 400;
BEGIN
g_inv_value := v_price * v_quantity;
END;
/
Print g_inv_value
/
PL/SQL Sample Program
Set serveroutput on
DECLARE
v_inv_value number(10,2);
v_price
number(8,2) := 10.25;
v_quantity
number(8,0) := 400;
BEGIN
v_inv_value := v_price * v_quantity;
dbms_output.put('The value is: ');
dbms_output.put_line(v_inv_value);
END;
/
PL/SQL Comments
DECLARE
v_salary number(9,2) := 40000;
BEGIN
/* this is a multi-line comment that
will be ignored by the pl/sql
interpreter */
v_salary := v_salary * 2; -- nice raise
END; -- end of program
CONDITIONAL STATEMENTS IN PL/SQL
PL/SQL Control Structures

Conditional Control

Using IF and CASE statements
DECLARE
DECLARE := 20000;
l_sales NUMBER(8,2)
l_grade CHAR(1) := 'B';
l_bonus NUMBER(6,2);
BEGIN
BEGIN
CASETHEN
l_grade
IF l_sales > 50000
l_bonus := 1500;
WHEN
'A' l_bonus
THEN DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF l_sales > 35000
THEN
:= 500;
ELSE l_bonus := WHEN
100; 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
END IF;
WHEN 'D'
THEN DBMS_OUTPUT.PUT_LINE('Fair');
UPDATE employees SET salary
= salary
+ l_bonus;
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
END;
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
PL/SQL Control Structures

Iterative loops



Simple loop (infinite)
WHILE loop
FOR loop


DECLARE
l_i
NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
l_i:=l_i+1;
END LOOP;
Numeric range
 Reversed
Cursor based
WHILE l_i < 10 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
l_i := l_i + 1;
END LOOP;
FOR l_i IN 1..500 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
END LOOP;
FOR l_i IN REVERSE 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
END LOOP;
END;
PL/SQL Control Structures

Iterative loops

Named loops
DECLARE
l_i NUMBER := 0;
l_j NUMBER := 0;
l_s NUMBER :=0;
BEGIN
<<outer_loop>>

Exiting loops


LOOP
l_i := l_i + 1;
EXIT statement
<<inner_loop>>
LOOP
Loop skipping

l_j := l_j + 1;
l_s := l_s + l_i * l_j;
CONTINUE
EXIT inner_loop WHEN (l_j > 5);
EXIT outer_loop WHEN ((l_i * l_j) > 15);
END LOOP inner_loop;
DBMS_OUTPUT.PUT_LINE('Sum:'||TO_CHAR(l_s));
IF l_s > 100 THEN EXIT;
END IF;
END LOOP outer_loop;
END;
Oracle Tutorials: PL/SQL
Accessing Data in the Database

Selecting at most one row:

SELECT INTO statement
SELECT COUNT(*) INTO variable FROM table;
SELECT * INTO record FROM table WHERE …;

Selecting Multiple rows:


Cursors
Inserting and updating
INSERT INTO table VALUES (var1, var2, …);
Remember the SELECT INTO…… ?

It only allowed the retrieval of one row
Select attribute into variable from … where …
Or
Select count(*) into variable from ………
But when we want to retrieve multiple rows we need
to use what is called a CURSOR
Cursors
To process an SQL statement, ORACLE needs to
create an area of memory known as the
context area
Cursors




A cursor is a temporary work area created in the
system memory when a SQL statement is executed
A cursor contains information on a select statement
and the rows of data accessed by it.
This area is used to store the data retrieved from
the database, and manipulate this data. A cursor
can hold more than one row, but can process only
one row at a time.
The set of rows the cursor holds is called the active
set.
Cursor structure

Declare it
 This
is achieved by a SELECT command
 And by giving the CURSOR a name



Open it
Fetch row(s) from it
Close it
Declaring the Cursor
34
DECLARE
CURSOR low_pay
IS SELECT surname,salary
FROM Personnel
where salary < 12000;
v_surname
personnel.surname%TYPE;
v_salary
personnel.salary%TYPE;
BEGIN
…..
Because a cursor is associated with multiple rows they
are normally used with LOOP structures
Types Of Cursors
An IMPLICIT cursor is automatically declared by Oracle every time an SQL
statement is executed.

The user will not be aware of this happening and will not be able to
control or process the information in an implicit cursor
An EXPLICIT cursor is defined by the program for any query that returns more
than one row of data.

They must be created when you are executing a SELECT statement that
returns more than one row. Even though the cursor stores multiple records,
only one record can be processed at a time, which is called as current row.
When you fetch a row the current row position moves to next row.
Implicit Cursors

The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and
%ISOPEN.
Example-Implicit Cursor
DECLARE var rows number(5);
BEGIN
UPDATE employee
SET salary = salary + 1000;
Implicit cursor
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries were updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
Explicit Cursor


An explicit cursor is defined in the declaration
section of the PL/SQL Block.
It is created on a SELECT Statement which returns
more than one row.

CURSOR <cursor_name> IS <select_statement>;
cursor_name – A suitable name for the cursor.

select_statement – A select query which returns multiple
rows.

How to use Explicit Cursor?

Four steps
1.
DECLARE the cursor in the declaration section.
2.
OPEN the cursor in the Execution Section.
3.
4.
FETCH the data from cursor into PL/SQL variables or records in the
Execution Section.
CLOSE the cursor in the Execution Section before you end the PL/SQL
Block.
Syntax
DECLARE
variables;
records;
declare a cursor;
BEGIN
OPEN cursor;
FETCH cursor;
process the records;
CLOSE cursor;
END;
Example
DECLARE
emp_rec emp_tbl%rowtype;
CURSOR emp_cur IS
SELECT *
FROM employee
WHERE salary > 10;
BEGIN
OPEN emp_cur;
FETCH emp_cur INTO emp_rec;
dbms_output.put_line (emp_rec.first_name || ' ' || emp_rec.last_name);
CLOSE emp_cur;
END;
Using explicit cursors with 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;
Procedures


A subprogram is a program unit/module that performs a particular task. These
subprograms are combined to form larger programs. This is basically called the
'Modular design'. A subprogram can be invoked by another subprogram or
program which is called the calling program.
A subprogram can be created:

At schema level

Inside a package

Inside a PL/SQL block

PL/SQL subprograms are named PL/SQL blocks that
can be invoked with a set of parameters. PL/SQL
provides two kinds of subprograms:
Functions: these subprograms return a single value, mainly
used to compute and return a value.
 Procedures: these subprograms do not return a value
directly, mainly used to perform an action.

Creating a Procedure

A procedure is created with the CREATE OR REPLACE
PROCEDURE statement.
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS} BEGIN
< procedure_body >
END procedure_name;

Where,





procedure-name specifies the name of the procedure.
[OR REPLACE] option allows modifying an existing procedure.
The optional parameter list contains name, mode and types of the
parameters. IN represents that value will be passed from outside and
OUT represents that this parameter will be used to return a value
outside of the procedure.
procedure-body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a
standalone procedure.
Example-1





CREATE OR REPLACE PROCEDURE greetings
AS BEGIN
dbms_output.put_line('Hello World!');
END;
/
Executing a Standalone Procedure

A standalone procedure can be called in two ways:
 Using
the EXECUTE keyword
EXECUTE greetings;
 Calling
the name of the procedure from a PL/SQL
block
BEGIN
greetings;
END;
/
Deleting a Standalone Procedure

DROP PROcedure
BEGIN
DROP PROCEDURE greetings;
END;
Parameter Modes in PL/SQL
Subprograms
1.
2.
3.
IN-parameters - These types of parameters are used to send
values to stored procedures.
OUT-parameters - These types of parameters are used to get
values from stored procedures.
IN OUT-parameters - This is similar to a return type in
functions. These types of parameters are used to send values
and get values from stored procedures.
NOTE: If a parameter is not explicitly defined a parameter type,
then by default it is an IN type parameter.
Functions
Creating a function




CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS} BEGIN
< function_body >
END [function_name];

function-name specifies the name of the function.

[OR REPLACE] option allows modifying an existing function.

The optional parameter list contains name, mode and types of the parameters. IN
represents that value will be passed from outside and OUT represents that this
parameter will be used to return a value outside of the procedure.

The function must contain a return statement.

RETURN clause specifies that data type you are going to return from the function.

function-body contains the executable part.

The AS keyword is used instead of the IS keyword for creating a standalone
function.
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total FROM customers;
RETURN total;
END; /
It returns the total number of CUSTOMERS in the
customers table.
Calling a function
DECLARE
c number(2);
BEGIN c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END; /
Output:
Total no. of Customers: 6
PL/SQL procedure successfully completed.
How to execute a PL/SQL Function?
1) Since a function returns a value we can assign it to a variable.
employee_name := employer_details_func();
If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning
the return type of the function to it.
2) As a part of a SELECT statement
SELECT employer_details_func FROM dual;
3) In a PL/SQL Statements like,
dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.
Procedures vs Functions
Triggers
Triggers are stored programs, which are automatically executed or fired when some
events occcur.

Executed in response to any of the following events:

A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

A database definition (DDL) statement (CREATE, ALTER, or DROP).

A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Note: Triggers could be defined on the table, view, schema, or database with which the
event is associated.
Triggers can be written for the following
purposes:

Generating some derived column values automatically

Enforcing referential integrity

Event logging and storing information on table access

Auditing

Synchronous replication of tables

Imposing security authorizations

Preventing invalid transactions
Creating Triggers


CREATE [OR REPLACE] TRIGGER trigger_name: Creates or replaces an existing trigger with
thetrigger_name.
{BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed. The INSTEAD OF
clause is used for creating trigger on a view.

{INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.

[OF col_name]: This specifies the column name that would be updated.

[ON table_name]: This specifies the name of the table associated with the trigger.



[REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values for various DML
statements, like INSERT, UPDATE, and DELETE.
[FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be executed for each row being
affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a
table level trigger.
WHEN (condition): This provides a condition for rows for which the trigger would fire. This clause is valid
only for row level triggers.
Example
Triggering a Trigger
References



http://www.plsql-tutorial.com
http://www.java2s.com/Tutorial/Oracle/
http://www.tutorialspoint.com/plsql