Introduction to PL/SQL Lecture 1 [Part 1]

Introduction to PL/SQL Lecture 1 [Part 1] Nick Rossiter (Emma-Jane Phillips-Tait) Room PB121 nick.rossiter@unn.ac.uk

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 : End; Begin : End; Begin : End; Multiple copies of executable code on the decentralised system – multiple copies to maintain leading to increase difficulty in maintaining the system Begin : End; Server Server Common copy of executed code – one copy to maintain

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. • Conditions IF-THEN-ELSE-END IF; • Jumps GOTO • Provides loops for controlling iteration • 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 Variable declarations Body of code Create or replace procedure sample1 as v_num1 constant number := 2.5; v_num2 constant number := 4; v_product number; 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.

Empid empname addr1 addr2 addr3 postcode grade salary 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

Selects entire row of data into 1 variable called v_employee Is updating the value of salary based on selected element of a variable Example – Single record retrieval – No cursor necessary Declare v_employee employee%rowtype; Begin select * into v_employee from employee where empid = 65284; update employee set salary = v_employee.salary + 10000 where empid = v_employee.empid; end

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.

PL/SQL programming Procedures and Cursors Lecture 1 [Part 2] Nick Rossiter (Emma-Jane Phillips-Tait)

SQL refresher • Basic commands • SELECT, INSERT, DELETE, UPDATE • Always remember to state the table(s) you are selecting data from • Join tables by primary and foreign keys • Filter data using WHERE clause • SELECT ... FROM ... [WHERE ...]

Script called: Create_lecturer_copy.sql SQL scripts • Set of commands to run in sequence. • Stored in a ‘note pad’ not data dictionary and accessed by file name • Executed by @ command Executed by: SQL> @create_lecturer_copy.sql

The SQL Procedure • 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 server not client • Does not return value to calling program • Not available in Oracle 6 or older • Aids security as DBA may grant access to procedures not tables, therefore some users cannot access tables except through a procedure

Building a procedure: contents • Create or replace command • Object to be created • Name of object • Any variables accessed or imported • Local variables declared • Code • End procedure declaration

This procedure is called inflation_rise and uses a variable accessed as inf_rate which is a number, this is passed in when the procedure is used. It simply updates the salary by the rate of inflation. Create or replace command Object to be created Name of object Any variables accessed or imported Declared local variables Code End procedure declaration Create or replace procedure inflation_rise (inf_rate in number) Begin update employee set salary = salary + (salary * inf_rate / 100); commit; End;

Compiling and executing procedures • Like any program the code needs to be compiled. • @inflation_rise • compiles the procedure in a file with this name • makes it available to the database • Execute inflation_rise executes the procedure. • Remember to compile a procedure again once it has been amended. • For ease of use, it is best to write procedures in notepad and then run them, this means that they can be easily edited and you have a backup copy

Local variables used by procedure Any variables passed into procedure SQL Example – Counting Entries Create or replace procedure validate_customer (v_cust in varchar2(10)) as Count number; Begin count = select count(*) from customer where cust_code = v_cust; if count > 0 then dbms.output ‘customer valid’; else dbms.output ‘customer not recognised’; end if; End;

Cursors in SQL • Enable users to loop around a set of data. • Store data selected from a query in a temp area for use when opened. • Useful in complex actions which would not be feasible in standard SQL selection queries • Cursor attributes - %notfound, %rowcount, %found & %isopen

Cursor cur_emp is Select emp_id, surname ‘name’, grade, salary From employee Where regrade is true; Syntax for Cursors • Declared as a variable in the same way as standard variables • Identified as cursor type • SQL retrieval set is included • e.g.

Cursor Population • The data is populated when the cursor is opened. • Once opened the data must be moved from the temp 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 cursor data is looped around until an exit clause is reached.

THE JELLY BABY DEMO! • Data has been selected from the employee table. This data needs to be amended in the following way: • Each member of staff is to be increased one grade. • Each member of staff is to have a £500 pay rise • If the pay rise does not take them to the minimum for their new grade they are to be increased to the minimum for that grade • If the pay rise moves them above the maximum for a grade they are to be increased to the maximum only

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 > 4; Begin open cur_sample; loop fetch cur_sample into v_empid; exit when cur_sample%notfound update employee set salary = salary + 500 where empid = v_empid; end loop; End; DeclareCursor Stop when no more records are found Datareturned by cursor Open cursor for use Loops round each value returned by the cursor Places the value from the cursor into the variable v_empid

While loops and cursors While - loop Declare V_empid number; Cursor cur_sample is Select empid from employee where grade > 4; Begin open cur_sample; while cur_sample%found loop fetch cur_sample into v_empid; update employee set salary = salary + 500 where empid = v_empid; end loop; End; Will loop around as long as %found returns a true value

for - loop Declare V_empid number; V_count number :=1; V_max number; Cursor cur_sample is Select empid from employee where grade > 4; Begin open cur_sample; v_max :=cur_sample%rowcount for v_count in 1 .. V_max loop fetch cur_sample into v_empid; update employee set salary = salary + 500 where empid = v_empid; v_count := v_count + 1; end loop; End; For loops and cursors Declare the additional variables required Will loop around as long as v_count is not greater than the number of rows returned Remember to increment the value of v_count

Placing cursors into procedures Create or replace procedure sample1 as v_deptname varchar2(10); v_deptid number cursor cur_deptchange is select distinct deptid, deptname from dept; BEGIN open cur_deptchange; loop fetch cur_dept into v_deptid, v_deptname; exit when cur_deptchange%notfound update employee set emp_deptname = v_deptname where emp_deptid = v_deptid; commit; end loop; END; Creation command Variable declarations Body of code

Use of conditions • If statements can be used If <condition> then … Else …... End if; Remember to end the if statement Use of indented code will make the code easier to debug!

Notepad file called: Create_procedures.sql 1) Open SQL*Plus and logon 2) At the prompt enter: @create_procedures You will get a prompt which should say ‘procedure created’ 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

Lecture 2: Active Databases Procedural Extension of DBMS using Triggers Advanced Databases CG096 Nick Rossiter [Emma-Jane Phillips-Tait]

Content 1 Limitations of Relational Data Model for performing Information Processing 2 Database Triggers in SQL 3 Using Database Triggers for Information Processing within DBMS 4 Restrictions for Database Triggers

Limitations of Relational Data Model • Database vs. Information Systems • DBMS manages data regardless of its usage • IS processes information with respect to its usage • Data model vs. system architecture • data model does not give interpretation in terms of the application domain • e.g. relational model, hierarchical model, set model • IS architecture is developed so, that the data can be interpreted as information about a particular applied domain • e.g. HR information, financial information, sales information

ECA • Event occurs in database • e.g. addition of new row, deletion of row • Condition is checked • e.g. is batch complete? Has student passed? • Actions are executed if condition is satisfied • e.g. send batch to supplier, congratulate student

Extending Information Processing Capabilities of DBMS using Triggers • Processing of database content, performed by the DBMS engine itself, not by the application client • execution of the trigger (E) • Initiated by certain specified condition, depending on the type of the trigger • firing of the trigger (C) • All data actions performed by the trigger execute within the same transaction in which the trigger fires, but in a separate session (A) • Triggers are checked for different privileges as necessary for the processed data • Cannot contain transaction control statements (COMMIT, SAVEPOINT, ROLLBACK not allowed)

Database Triggers in SQL • Not specified in SQL-92, but standardized in SQL3 (SQL1999) • Available in most enterprise DBMS (Oracle, IBM DB2, MS SQL server) and some public domain DBMS (Postgres) • but not present in smaller desktop (Oracle Lite) and public domain DBMS (MySQL) • Some vendor DBMS permit native extensions to SQL for specifying the triggers • e.g. PL/SQL in Oracle, Transact SQL in MS SQL Server • Some DBMS also allow use of general purpose programming language instead of SQL • e.g. C/C++ in Poet, Java in Oracle, VB in MS Access • Some DBMS extend the triggers beyond tables • for example also to views as in Oracle

Types of SQL Triggers • How many times should the trigger body execute when the triggering event takes place? • Per statement: the trigger body executes once for the triggering event. This is the default. • For each row: the trigger body executes once for each row affected by the triggering event. • When the trigger can be fired • Relative to the execution of an SQL DML statement (before or after or instead of it) • Exactly in a situation depending on specific system resources (e.g. signal from the system clock, expiring timer, exhausting memory)

Example 2: Monitoring Row Events SQL> UPDATE emp 2 SET sal = sal * 1.1 3 WHERE deptno = 30; Statement and Row Triggers Example 1: Monitoring Statement Events SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'EDUCATION', 'NEW YORK'); Execute only once even if multiple rows affected Execute for each row of the table affected by the event

BEFORE statement trigger AFTER statement trigger Firing Sequence of DatabaseTriggers on a Single Row DEPT table DEPTNO 10 20 30 40 DNAME ACCOUNTING RESEARCH SALES OPERATIONS LOC NEW YORK DALLAS CHICAGO BOSTON BEFORE row trigger AFTER row trigger

BEFORE statement trigger BEFORE row trigger AFTER row trigger BEFORE row trigger AFTER row trigger BEFORE row trigger AFTER row trigger AFTER statement trigger Firing Sequence of Database Triggers on Multiple Rows EMP table EMPNO 7839 7698 7788 ENAME KING BLAKE SMITH DEPTNO 30 30 30

Syntax for creating triggers in SQL • Trigger name - unique within one database schema • Timing - depends on the order of controlled events (before or after or instead of) • Triggering event - event which fires the trigger (E) • Filtering condition - checked when the triggering event occurs (C) • Target - table (or view) against which the trigger is fired; they should be both created within the same schema • Trigger Parameters - parameters used to denote the record columns; preceded by colon • :new, :old for new and old versions of the values respectively • Trigger action - SQL statements, executed when the trigger fires; surrounded by begin ... End (A)

Syntax for Creating Statement Triggers CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name BEGIN SQL statements; END The trigger body consisting of SQL statements will be executed only once according to the prescribed timing, when the event1 (event2, event3) occurs against the monitored table in question table_name

Introduction to PL/SQL Lecture 1 [Part 1]