Introduction to PLSQL

1
Lecture 4

• Introduction to PL/SQL
• Procedures Cursors

2
Overview

• Overview of PL/SQL
• Development of a coded block
• Interacting with an Oracle Database
• Controlling PL/SQL process flow
• Cursor handling

3
Embedding SQL

• SQL is not functionally complete
• Lacks the full facilities of a programming
  language
• variables, flow of control etc.
• All DBMSs top up functionality by embedding SQL
  in a procedural language
• But details vary from one DBMS to another.
• However, procedures and functions can be ported
  between systems.

4
PL/SQL - Introduction

• An Oracle-specific procedural extension to SQL,
  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.

5
Why use PL/SQL?

• Manage business rules through middle layer
  application logic.
• Generate code for triggers.
• Generate code for the user interface.
• Enable database-centric client/server
  applications.

6
Centralised vs. Decentralised
7
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 way as with
  other objects.
• PL/SQL objects are first-class Oracle DB objects.
• Easy to read
• with modularity features and error handling.

8
Centralised Control

• Enables the DBA to
• specify rules in one place (as a procedure,
  function, trigger or package in PL/SQL)
• force user access through the predefined PL/SQL,
  so users cannot write their own procedural code
  and use this instead
• e.g. define security privileges giving users
  access to table(s) only through a particular
  procedure.

9
Using PL/SQL as a Programming Language

• Permits all flow of control operations of
  standard programming languages, e.g.
• Jumps GOTO
• Conditions IF-THEN-END IF IF-THEN-ELSE-END
  IF
• Loops LOOP-EXIT WHEN-END LOOP FOR-END
  LOOP WHILE-END LOOP
• Allows extraction of data into variables and its
  subsequent manipulation.

10
Modules in PL/SQL

• There are 4 types of modules in PL/SQL
• Procedure a series of statements which may or
  may not return a value.
• Function a series of statements which must
  return a single value.
• Trigger a series of statements which is
  executed after an event has triggered a
  condition.
• Package a collection of procedures and
  functions which has 2 parts
• a listing and a body.

11
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 and times.
• Long used to store large blocks of text up to 2
  gigabytes in length (limited operations)

12
Non-DB 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.

13
SQL Scripts

• A set of commands to run in sequence.
• Stored as a text file (e.g. using Notepad) on
  disk and not in the data dictionary. It is
  accessed by its file name using _at_ or Start.

Executed by SQLgt _at_U\create_lecturer_copy
14
The SQL Procedure

• A block of SQL statements stored in the Data
  Dictionary and called by applications.
• Satisfies frequently-used or critical application
  logic.
• When called, all code within the procedure is
  executed (unlike packages).
• Action takes place on the server, not the client.
• Does not (normally) return a value to the calling
  program.
• Not available in Oracle 6 or older.
• Aids security as DBA may grant access to
  procedures rather than tables, therefore some
  users cannot access tables except through a
  procedure.

15
Building a Procedure Contents

• CREATE OR REPLACE command
• Object to be created
• Name of object
• Any variables accessed or imported
• Local variables declared
• Code block enclosed by BEGIN END

16
Create or replace procedure inflation_rise
(inf_rate in number) Begin update employee
set salary salary (salary inf_rate /
100) commit End
17
Compiling and Executing Procedures

• Like any program the code needs to be compiled.
• _at_inflation_rise
• compiles the procedure from a file with this
  name
• makes it available to the data base.
• Execute inflation_rise executes the procedure.
• Remember to re-compile a procedure after editing.
• For ease of use, it is best to write procedures
  in Notepad, then they can be easily edited and
  you have a back-up copy.

18
Example
CREATE OR REPLACE PROCEDURE validate_customer
(v_cust VARCHAR) AS v_count NUMBER BEGIN SELECT
COUNT() INTO v_count FROM CUSTOMER WHERE
CUST_CODE v_cust IF v_count gt 0
THEN DBMS_OUTPUT.PUT_LINE(customer
valid) ELSE DBMS_OUTPUT.PUT_LINE(customer
not recognised) END IF END
19
Cursors in SQL

• Enables users to loop round a selection of data.
• Stores data select from a query in a temp area
  for use when opened.
• Use complex actions which would not be feasible
  in standard SQL selection queries

20
Declaring Cursors

• Declared as a variable in the same way as
  standard variables.
• Identified as cursor type.
• SQL included, e.g.

21
Cursors

• A cursor is a temporary store of data.
• The data is populated when the cursor is opened.
• Once opened, the data must be moved from the
  temporary area to a local variable to be used by
  the program. These variables must be populated
  in the same order that the data is held in the
  cursor.
• The data set is looped round till an exit clause
  is reached.

22
Cursor Functions
23
Controlling the Cursor
No
DECLARE
CLOSE
Yes

• Create a named SQL area

• Identify the active set

• Load the current row into variables

• Test for existing rows

• Release the active set

• Return to FETCH if rows found

24
Controlling the Cursor
Open the cursor.





Fetch a row from the cursor.

Continue until empty.

Close the cursor.
25
Cursor Attributes

• To obtain status information about a cursor.

26
25463 12245 55983 12524 98543

• Create or replace procedure proc_test as
• v_empid number
• Cursor cur_sample is
• Select empid from employee
• where grade gt 4
• Begin
• open cur_sample
• loop
• fetch cur_sample into v_empid
• exit when cur_samplenotfound
• update employee
• set salary salary 500
• where empid v_empid
• end loop
• End

Data returned by cursor
Declare Cursor
Open cursor for use. Loops round each value
returned by the cursor Place the value from the
cursor into the variable v_empid
Stop when no more records are found
27
Notepad file called Create_procedures.sql
1) Open SQLPlus and logon 2) At the prompt
enter _at_create_procedures You will get a prompt
which should say procedure created otherwise
use SHOW ERRORS to view
errors in the code. 3) To run the procedure
enter Execute proc_test 4) If you check your
data you should now find that the procedure has
run successfully
28
Use of conditions

• IF statements can be used
• If ltconditiongt Then
• ..
• End if
• E.g.
• Remember to end the IF statement
• Use of indented code will make it easier to debug!

. . . IF v_ename 'MILLER' THEN v_job
'SALESMAN' v_deptno 35 v_new_comm
sal 0.20 END IF . . .
29
The ISOPEN Attribute

• Can fetch rows only when the cursor is open.
• Use the ISOPEN cursor attribute before
  performing a fetch to test whether the cursor is
  open.
• Example

IF NOT cur_sampleISOPEN THEN OPEN
cur_sample END IF LOOP FETCH cur_sample...
30
Cursors and Records

• Process the rows of the active set conveniently
  by fetching values into a PL/SQL RECORD.
• Example

DECLARE CURSOR emp_cursor IS SELECT empno,
ename FROM emp emp_record emp_cursorROWTY
PE BEGIN OPEN emp_cursor LOOP FETCH
emp_cursor INTO emp_record ...
31
Cursor FOR Loops

• The cursor FOR loop is a shortcut to process
  cursors.
• Syntax
• Implicitly opens, fetches, and closes cursor.
• The record is implicitly declared.

FOR record_name IN cursor_name LOOP
statement1 statement2 . . . END LOOP
32
Cursor FOR Loops An Example

• Retrieve employees one by one until no more
  areleft

DECLARE CURSOR emp_cursor IS SELECT ename,
deptno FROM emp BEGIN FOR emp_record IN
emp_cursor LOOP -- implicit open and
implicit fetch occur IF emp_record.deptno
30 THEN ... END LOOP -- implicit close
occurs END