Database modelling
1 / 37

Database Modelling - PowerPoint PPT Presentation

  • Updated On :

Database Modelling. Lecture 8 (a): Database Programming & Introduction to PL/SQL. Learning Objectives. To show the need for database programming To introduce programming with stored procedures using PL/SQL. Suggested Reading.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Database Modelling' - bruis

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Database modelling l.jpg

Database Modelling

Lecture 8 (a): Database Programming &

Introduction to PL/SQL

Learning objectives l.jpg
Learning Objectives

  • To show the need for database programming

  • To introduce programming with stored procedures using PL/SQL.

Suggested reading l.jpg
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:

Overview l.jpg

  • Revisiting SQL – what is it?

  • What SQL cannot do

  • Need for Programming top-up

  • PL/SQL

    • Introduction

    • Security aspects

    • Introductory examples

Introduction l.jpg

  • 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

Sql classification l.jpg
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#.

Some properties of sql l.jpg
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

Sql program constructs overview l.jpg
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

Sql program constructs l.jpg
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)

Sql controls the filing cabinet l.jpg
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

Functional incompleteness in sql l.jpg
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

Requirements for production environment l.jpg
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

Sql procedures l.jpg
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

Oracle pl sql l.jpg
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.

Procedures are first class database objects l.jpg
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’

Procedures aid security l.jpg
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

Privileges on tables l.jpg
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

Privileges on procedures l.jpg
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

Importance of procedures l.jpg
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

Sql procedure construction l.jpg
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');


  • WHEN others THEN DBMS_OUTPUT.PUT_LINE('error');

  • end;

    7 /

    Warning: Procedure created with compilation errors

    SQL> show errors



    -------- -----------------------------------------

    3/1 PL/SQL: SQL Statement ignored

    3/13 PL/SQL: ORA-00947: not enough values

Technique l.jpg

  • Have procedure code in a text file managed by simple editor, e.g. Notepad

    create or replace procedure add_patient as


    insert into patient values('99','Smith','Newcastle','12-mar-1980');


    WHEN others THEN DBMS_OUTPUT.PUT_LINE('error');



  • 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

Features of procedure l.jpg
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

Error tracking l.jpg
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

Second attempt at procedure l.jpg
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);



6 end;

7 /

Procedure created.

Parameters l.jpg

  • 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

Data dictionary entry for procedure l.jpg
Data Dictionary entry for procedure

SQL> select object_type, object_name

2 from user_objects

3 where object_type = 'PROCEDURE';




Executing procedure l.jpg
Executing Procedure

SQL> execute add_patient('20-feb-2007');

PL/SQL procedure successfully completed.

SQL> select * from patient where pid = '99';


------- ----------- ---------------- -------------- --------------

99 Smith Newcastle 12-MAR-80 20-FEB-07

Note: can also use exec as a shortened version of execute

Features of execution l.jpg
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'

Same procedure run again l.jpg
Same procedure run again

SQL> set serveroutput on

SQL> execute add_patient('20-feb-2007');


PL/SQL procedure successfully completed.

Error why l.jpg
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

All values from parameters l.jpg
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);



7 end;

8 /

Procedure created.

No data hard coded output l.jpg
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


      • To view output, at the start of session need to set the following SQL environment command:

        • Set serveroutput on

Execution with all values as parameters l.jpg
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';


----- ----------- ----------------- -------------- ----------------

124 Smith Edinburgh 13-NOV-80 27-DEC-02

Make columns explicit l.jpg
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');



9 end;

10 /

Procedure created.

Specifying columns for patient makes procedure immune to

any later changes in order of columns in patient

Summary l.jpg

  • 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