Database Modelling

1. Database Modelling Lecture 8 (a): Database Programming & Introduction to PL/SQL

2. Learning Objectives • To show the need for database programming • To introduce programming with stored procedures using PL/SQL.

3. Suggested Reading • Sunderraman, R. (2007) Oracle 10g Programming: A Primer. Pearson. • Completely adequate replacement is the Oracle 9i primer by the same author. • Urman, S., Hardman, R. & McLaughlin, M. (2004) Oracle Database 10g PL/SQL Programming. McGraw-Hill. • Connolly, T. & Begg, C. (2005)Database Systems. 4th edn. Addison-Wesley. • Oracle (2005) Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (10.2). Available at: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/title.htm

4. Database Programming

5. Overview • Revisiting SQL – what is it? • What SQL cannot do • Need for Programming top-up • PL/SQL • Introduction • Security aspects • Introductory examples

6. Introduction • Database Programming • A program is defined simply as: • a sequence of instructions that a computer can interpret and execute • So SQL (Structured Query Language) • the ISO standard language for relational databases • is a programming language

7. SQL - Classification • SQL is the basis of all database programming • As a language, SQL is: • Non-procedural • Specify the target, not the mechanism (what not how) • Set-oriented • All operations are on entire sets of tuples (relations) • Relationally complete • Has the power of the relational algebra • Functionally incomplete • Does not have the power of a programming language like Java/C/C++/C#.

8. Some properties of SQL • Non-procedural (unlike PL/SQL) • No loops or tests for end of file • Set-oriented • The operation is automatically applied to all the rows in a table, e.g. STUDENT • Relationally complete • Has power of the relational algebra as defined by Codd in 1970. • Functionally incomplete • Does not matter so much if just want information displayed, rather than manipulated

9. SQL – Program Constructs Overview • SELECT statements – used to retrieve a set of data from the database. • Restrict the dataset using WHERE clauses • WHERE clauses also used to join tables. Joins make use of mostly primary keys (PKs) and foreign keys (FKs) • Aggregate functions e.g. AVG, SUM, MAX, MIN etc • Single-row date & character functions e.g. use of SYSDATE and TO_CHAR to format dates

10. SQL – Program Constructs SELECT id, name, dobirth FROM student WHERE name = ‘Mary Brown’; • FROM clause specifies tables to be queried (source/range) • WHERE clause specifies restriction on values to be processed (predicate) • SELECT clause specifies what is to be retrieved (target)

11. SQL controls the filing cabinet • Defines data structures (CREATE TABLE, CREATE VIEW, …) • Handles updates (INSERT, DELETE, COMMIT, ROLLBACK, …) • Provides retrieval (SELECT) But it is not functionally complete

12. Functional Incompleteness in SQL • No control statements such as: • Case, Repeat, If, While, Loop • Little substitution at run time: • e.g. … WHERE id = :idread • idread is a program variable • You don’t see travel agents typing in SQL statements to search for holiday vacancies • although they may be searching a relational database • There is SQL underneath • But its functionality is increased through additional features

13. Requirements for Production Environment • Encourage: • business rules in one place • application of rules then controlled by DBA • users need permission to apply rules • permission is granted/revoked by DBA • Discourage: • duplicated, potentially inconsistent, rules • access by users to anything they like

14. PL/SQL - introduction

15. SQL Procedures • An important technique for databases in production environment • Part of PL/SQL in Oracle • Procedural Language/Structured Query Language • Part of the SQL standard • approximate portability from one system to another • Techniques are available for: • procedural control (case, if, while, …) • parameterised input/output • security

16. Oracle PL/SQL • Available in Oracle 10g at Northumbria (SQL Plus) • Available in Oracle 10g Personal Edition for Windows and Linux, but not in any Lite versions. http://www.oracle.com/technology/software/products/database/oracle10g/index.html

17. Procedures are First-class Database Objects • Procedures are held in database tables under the control of the database system in the data dictionary • e.g. select object_type, object_name from user_objects where object_type = 'PROCEDURE'; • user_objects is a data dictionary table maintained by Oracle • object_type is attribute of table user_objects holding value ‘PROCEDURE’ • (other values for object_type include ‘TABLE’, ‘VIEW’) • object_name is user assigned name for object e.g. ‘PATIENT’

18. Procedures Aid Security • Available Privileges on Tables: • Select • query the table with a select statement. • Insert • add new rows to the table with insert statement. • Update • update rows in the table with update statement. • Delete • delete rows from the table with delete statement. • References • create a constraint that refers to the table. • Alter • change the table definition with the alter table statement. • Index • create an index on the table with the create index statement

19. Privileges on Tables • SQL statement -- issued by DBA: • GRANT select, insert, update, delete ON patient TO cgel1; • no grants to user cgel2 for table access • Allows user cgel1 to issue SQL commands: • beginning with SELECT, INSERT, UPDATE, DELETE on table patient • but user cgel1 cannot issue SQL commands • beginning with REFERENCES, ALTER, INDEX on table patient • User cgel2 does not know that table patient exists

20. Privileges on Procedures • The SQL statement • GRANT execute ON add_patient TO cgel2; • allows user cgel2 to execute the procedure called add_patient • So user cgel2 can add patients • presumably the task of add_patient • but cannot do any other activity on the patient table • including SELECT • So procedures give security based on tasks • powerful task-based security system

21. Importance of Procedures • A stored procedure • Is a unique definition of a process • At a given time • Implements • One or more business rules • Is available • To selected users • Avoids redundancy (and inconsistency) in definition of process • Should implement business rules faithfully • Provides security in task-based sense

22. SQL Procedure Construction Simple example, SQL*Plus Window: SQL> create or replace procedure add_patient as 2 begin • insert into patient values('99','Smith','Newcastle','12-mar-1980'); • EXCEPTION • WHEN others THEN DBMS_OUTPUT.PUT_LINE('error'); • end; 7 / Warning: Procedure created with compilation errors SQL> show errors Errors for PROCEDURE ADD_PATIENT LINE/COL ERROR -------- ----------------------------------------- 3/1 PL/SQL: SQL Statement ignored 3/13 PL/SQL: ORA-00947: not enough values

23. Technique • Have procedure code in a text file managed by simple editor, e.g. Notepad create or replace procedure add_patient as begin insert into patient values('99','Smith','Newcastle','12-mar-1980'); EXCEPTION WHEN others THEN DBMS_OUTPUT.PUT_LINE('error'); end; / • Copy and paste code from text file into SQL*Plus window or run as a script using @ • Oracle does keep a copy in its data dictionary • user_objects header info; user_source complete code

24. Features of Procedure • CREATE OR REPLACE add_patient AS • Either add or over-write procedure called add_patient • Needs care • Could over-write existing procedure • IS is alternative syntax for AS • BEGIN and END • Start and finish block • INSERT is standard SQL statement • DBMS_OUTPUT.PUT_LINE('error') prints out message – visible if SERVEROUTPUT is ON • EXCEPTION handles any exceptions raised • / means compile

25. Error Tracking • ‘created with compilation errors’ • Problem(s) encountered in compilation • Look at these through SQL command • SHOW ERRORS (SHO ERR abbreviation) • Diagnostics: • Statement at line 3 ignored • As not enough values at line 3, column 13 for patient • Five columns in patient, four given in the insert statement • So at compilation time, tables are checked for compatibility with procedure operations • ORA-00947 is an Oracle return code for ‘not enough values’ • Only execute procedures compiled without errors

26. Second attempt at procedure SQL> create or replace procedure add_patient (reg in char) as 2 begin 3 insert into patient values('99','Smith','Newcastle','12-mar-1980',reg); 4 EXCEPTION 5 WHEN others THEN DBMS_OUTPUT.PUT_LINE('error'); 6 end; 7 / Procedure created.

27. Parameters • Have added 5th variable to values • Also added a parameter • Reg • type char (as in SQL types) and in (input, read-only) • Other types at this level are number, date • ‘Broad-brush’ datatypes • Message ‘Procedure created’ means: • No errors found • Procedure can be executed • Procedure is held in Oracle’s data dictionary

28. Data Dictionary entry for procedure SQL> select object_type, object_name 2 from user_objects 3 where object_type = 'PROCEDURE'; OBJECT_TYPE OBJECT_NAME -------------------------------------------------- PROCEDURE ADD_PATIENT

29. Executing Procedure SQL> execute add_patient('20-feb-2007'); PL/SQL procedure successfully completed. SQL> select * from patient where pid = '99'; PID PNAME ADDRESS DOBIRTH DATE_REG ------- ----------- ---------------- -------------- -------------- 99 Smith Newcastle 12-MAR-80 20-FEB-07 Note: can also use exec as a shortened version of execute

30. Features of Execution • '20-feb-2007' is value for parameter of type date • Other values are hard-wired in procedure • Message '… successfully completed' • No errors during run OR • If errors have arisen, they have been handled (Exception Handling) • No other completion condition is acceptable • Hangs up tasks, locking problems, wrecks progress • Subsequent SELECT confirms • New data entered for patient with pid = '99'

31. Same procedure run again SQL> set serveroutput on SQL> execute add_patient('20-feb-2007'); error PL/SQL procedure successfully completed.

32. Error – why? • Attempt to add row with same primary key as last run ('99'). • Exception handling gives very little information • Could be much more tightly defined to show: • violation is of constraint CGEL1.PKP • CGEL1 is user id • PKP is constraint from CREATE TABLE • create table patient ( pid char(6) constraint pkp primary key, …. • Must have exception handling (more detail later) • Message ‘… successfully completed’ means exception raised (duplicate primary key) handled. Does NOT mean that the patient has been successfully added

33. All values from parameters SQL> create or replace procedure add_patient (pid in char, pname in char,address in char, 2 dobirth in date, regdate in date) as 3 begin 4 insert into patient values(pid,pname,address,dobirth,regdate); 5 EXCEPTION 6 WHEN others THEN DBMS_OUTPUT.PUT_LINE('error'); 7 end; 8 / Procedure created.

34. No data hard-coded/output • Usually meaningless to have hard-coded data values • Need dynamic input at run-time • Note two types – char, date • Values may be captured through Oracle Forms • Output strings • Varies from system to system • In Oracle • Use DBMS_OUTPUT.PUT_LINE • To view output, at the start of session need to set the following SQL environment command: • Set serveroutput on

35. Execution with all values as parameters SQL> execute add_patient('124','Smith','Edinburgh','13- nov-1980','27-dec-2002'); PL/SQL procedure successfully completed. SQL> select * from patient where pid = '124'; PID PNAME ADDRESS DOBIRTH DATE_REG ----- ----------- ----------------- -------------- ---------------- 124 Smith Edinburgh 13-NOV-80 27-DEC-02

36. Make columns explicit SQL> create or replace procedure add_patient (pid in char, pname in char, 2 address in char, dobirth in date, regdate in date) as 3 begin 4 DBMS_OUTPUT.PUT_LINE ('Insert attempted'); 5 insert into patient(pid,pname,address,dobirth,date_reg) values(pid,pname,address,dobirth,regdate); 6 DBMS_OUTPUT.PUT_LINE ('Insert succeeded'); 7 EXCEPTION 8 WHEN others THEN DBMS_OUTPUT.PUT_LINE('error'); 9 end; 10 / Procedure created. Specifying columns for patient makes procedure immune to any later changes in order of columns in patient

37. Summary • Stored procedures are used to meet a business rule, provide reusable code components & provide a means of security • Can use SHOW ERRORS to debug errors in procedure compilation • Can use exception handling to show errors at runtime • Can pass multiple values into procedures using parameters • Use execute command to run procedures in SQL*Plus