Transcript Introduction_to_PLSQL

Introduction to PL/SQL
Lecture 4 [Part 1]
Emma-Jane Phillips-Tait [Akhtar Ali]
Overview of next 3 sessions






Overview of PL/SQL
Development of a coded block
Interacting with an Oracle Database
Controlling PL/SQL process flow
Cursor handling
Error handling
Session 1



Using PL/SQL to access Oracle
Variable assignment
Overview of the next 2 weeks
Re-visiting SQL



Instructions to Oracle identifying the
information you wish to select, insert,
delete or update
SQL*Plus is Oracle's version of the SQL
standard
Notes on SQL are on Blackboard
PL/SQL - introduction




Procedural extension allowing for
modularity, variable declaration, loops and
logical constructs.
Allows for advanced error handling
Communicates natively with other oracle
database objects.
Managed centrally within the Oracle
database.
Other Databases


All have procedural facilities
SQL is not functionally complete



Lacks full facilities of a programming language
So top up functionality by embedding SQL in a
procedural language
PL/SQL techniques are specific to Oracle

but procedures and functions can be ported to other
systems
Why use PL/SQL




Manage business rules – through middle
layer application logic.
Generate code for triggers
Generate code for interface
Enable database-centric client/server
applications
Centralised V’s De-centralised
Begin
:
Server
End;
Common copy of
executed code – one
copy to maintain
Begin
Begin
Begin
:
:
:
End;
End;
End;
Multiple copies of
executable code on
the decentralised
system – multiple
copies to maintain
leading to increase
difficulty in
maintaining the
system
Server
Advantages of using PL/SQL to
access Oracle




PL/SQL is managed centrally within the database
Code is managed by the DBA and execution
privileges are managed in the same was as with
other objects
PL/SQL objects are first-class Oracle DB objects
Easy to read
 With modularity features and error handling
Centralised control

Enables DBA to:


Specify rules in one place (as procedure,
function, package in PL/SQL)
Force user access through the predefined
PL/SQL so users cannot write their own
procedural code and use this instead.

Define for instance security privileges giving users
access to table(s) only through a particular
procedure
Using PL/SQL as a programming
language

Permits all operations of standard programming
languages e.g.



Provides loops for controlling iteration


Conditions IF-THEN-ELSE-END IF;
Jumps GOTO
LOOP-EXIT; WHEN-END LOOP; FOR-END
LOOP; WHILE-END LOOP
Allows extraction of data into variables and its
subsequent manipulation
Modules in PL/SQL
There are 4 types of modules in PL/SQL

Procedures – series of statements may or may
not return a value
 Functions – series of statements must return a
single value
 Triggers – series of PL/SQL statements (actions)
executing after an event has triggered a condition
(ECA)
 Packages – collection of procedures and function
that has 2 parts:
 a listing and a body.
Procedures
Creation command
Create or replace procedure sample1 as
Variable declarations
v_num1 constant number := 2.5;
v_num2 constant number := 4;
v_product number;
Body of code
BEGIN
v_product := v_num1 * v_num2;
END;
Use of Data-Types




Number – used to store any number
Char(size) & varchar2(size) e.g.: char(10) –
used to store alphanumerical text strings,
the char data type will pad the value stored
to the full length declared.
Date – used to store dates
Long – used to store large blocks of text up
to 2 gigabytes in length (limited operations)
More data-types



Long raw – stores large blocks of data
stored in binary format
Raw – stores smaller blocks of data in
binary formal
Rowid – used to store the special format of
rowid’s on the database
Non-database Data Types





DEC, DECIMAL, REAL, INTEGER, INT – these are
numerical data types that are a subset of number.
Binary_integer – binary format for number type but can
not be stored in database unless converted first.
Character – same as char
Boolean – true/false value
Table/record – tables can be used to store the equivalent
of an array while records store the variables with
composite data types.
Using SQL in procedures



Select values into PL/SQL variables
 using INTO
Record.element notation will address
components of tuples (dot notation)
%rowtype allows full rows to be selected
into one variable
V_employee employee%rowtype
Empid
empname addr1
addr2
addr3
postcode
grade
salary
Example
Declare
v_employee
employee%rowtype;
Begin
select *
Selects entire row of data into 1
variable called v_employee
into v_employee
from employee
where empid = 65284;
update employee
set salary = v_employee.salary + 10000
where empid = v_employee.empid;
end
Is updating the value of
salary based on selected
element of a variable
Cursor overview




Very powerful in PL/SQL modules
Allows more than one set of data to be
retrieved and accessed at the same time in
loops
Sets are created by executing SQL
statements embedded in the PL/SQL code
Cursor attributes - %notfound, %rowcount,
%found & %isopen
Error handling




Prevents database locking
Ensures that errors are presented to the user
in a sensible format
Makes code robust
Essential when using PL/SQL as formal
programming language or interfacing with
Oracle applications.