database modelling
Skip this Video
Download Presentation
Database Modelling

Loading in 2 Seconds...

play fullscreen
1 / 37

Database Modelling - PowerPoint PPT Presentation

  • Uploaded 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

Database Modelling

Lecture 8 (a): Database Programming &

Introduction to PL/SQL

learning objectives
Learning Objectives
  • To show the need for database programming
  • To introduce programming with stored procedures using PL/SQL.
suggested reading
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:
  • Revisiting SQL – what is it?
  • What SQL cannot do
  • Need for Programming top-up
  • PL/SQL
    • Introduction
    • Security aspects
    • Introductory examples
  • 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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

  • 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\');





  • 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
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
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
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);


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

6 end;

7 /

Procedure created.

  • 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
Data Dictionary entry for procedure

SQL> select object_type, object_name

2 from user_objects

3 where object_type = \'PROCEDURE\';




executing procedure
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
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
Same procedure run again

SQL> set serveroutput on

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


PL/SQL procedure successfully completed.

error why
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
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);


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

7 end;

8 /

Procedure created.

no data hard coded output
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
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
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\');


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

  • 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