sfdv3002
Download
Skip this Video
Download Presentation
SFDV3002

Loading in 2 Seconds...

play fullscreen
1 / 45

SFDV3002 - PowerPoint PPT Presentation


  • 130 Views
  • Uploaded on

SFDV3002. Chapter 4: Programming with SQL. Overview. Database and application layers Server-side SQL Oracle’s PL/SQL Cursors Integrity checking using triggers Interfacing with external applications SQL interfaces & dynamic SQL Call-level interfaces. Database and application layers.

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

PowerPoint Slideshow about ' SFDV3002' - cachez


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
sfdv3002

SFDV3002

Chapter 4:

Programming with SQL

SFDV3002

overview
Overview
  • Database and application layers
  • Server-side SQL
    • Oracle’s PL/SQL
    • Cursors
  • Integrity checking using triggers
  • Interfacing with external applications
    • SQL interfaces & dynamic SQL
    • Call-level interfaces

SFDV3002

database and application layers
Database and application layers

Data &

metadata

External application code

Interaction between external

applications and database

(middleware layer)

“DATABASE”

Internal application

code (stored procedures)

Integrity constraints

(domain, PKs, FKs,

CHECKs, triggers)

DBMS

SFDV3002

server side sql

Server-side SQL

Storing application code

in the database

SFDV3002

overview1
Overview
  • The internal application layer
  • Procedural vs. declarative
  • Oracle’s PL/SQL:
    • basic language constructs
    • retrieving data into variables (cursors)
    • stored code
    • exception handling
    • packages

SFDV3002

the internal application layer
The internal application layer
  • Application code stored in database.
  • Used by DBMS and external programs.
  • Procedural vs. declarative.
  • Major uses:
    • arbitrarily complex (procedural) business rules
    • commonly used application code (not business rules), esp. large intermediate results or CPU intensive

SFDV3002

procedural vs declarative kifer p 127 example 4 1
Procedural vs. declarative(Kifer p. 127; Example 4–1)
  • SQL generally declarative: “what”.
  • “Conventional” programming languages (C++, Java, Visual Basic, C#, …) procedural: “how”.
  • Oracle10g’s PL/SQL is “procedural SQL”:
    • block-structured, similar to Ada or Pascal
    • other DBMSs have similar
    • some standardisation in SQL:1999

SFDV3002

pl sql oracle10g concepts ch 24 pl sql user s guide reference example 4 2
PL/SQL(Oracle10g Concepts ch. 24; PL/SQL User’s Guide & Reference; Example 4–2)

A PL/SQL block

DECLARE

<declarations>← variables, constants, etc.

BEGIN

<code>← SQL statements, loops, if/then, etc.

EXCEPTION← optional exception-handling section

<exception handlers>

END;

SFDV3002

pl sql declarations pl sql user s guide reference ch 2
PL/SQL Declarations(PL/SQL User’s Guide & Reference ch. 2)
  • Variables & constants
  • Cursors
  • Procedures, functions
  • Exceptions

SFDV3002

flow of control pl sql user s guide reference ch 4
Flow of control(PL/SQL User’s Guide & Reference ch. 4)

Conditional

IF <condition-1> THEN <code-1>

[ELSIF <condition-2> THEN <code-2>]...

ELSE <code-n>

END IF;

Loops

WHILE <condition> LOOP <code> END LOOP;

FOR <var> IN [REVERSE] <start>..<end>

LOOP <code> END LOOP;

EXIT & EXIT WHEN

SFDV3002

slide11
Retrieving data into variables(PL/SQL User’s Guide & Reference ch. 13 ▷ “SELECT INTO Statement”; Example 4–3)

SELECT…INTO for single values or rows

DECLARE

Sal NUMBER(6);

ID NUMBER(5);

BEGIN

ID := 7659;

SELECT E.Salary INTO Sal

FROM Employee E

WHERE (E.Employee_ID = ID);

END;

SFDV3002

rowtype pl sql user s guide reference ch 13 rowtype attribute
%ROWTYPE(PL/SQL User’s Guide & Reference ch. 13 ▷ “%ROWTYPE Attribute”)

“Data type” corresponding to row of table

DECLARE

Emp_Row Employee%ROWTYPE;

ID NUMBER(4);

BEGIN

ID := 7659;

SELECT E.* INTO Emp_Row

FROM Employee E

WHERE (E.Employee_ID = ID);

END;

SFDV3002

cursors kifer 8 2 4 pl sql user s guide reference ch 6 example 4 4
Cursors(Kifer §8.2.4; PL/SQL User’s Guide & Reference ch. 6; Example 4–4)
  • Table is basic relational structure.
  • Procedural languages oriented toward files of records:
    • open file
    • read and process records one at a time
    • close file
  • Cursors enable procedural access to tables in SQL.

SFDV3002

declaring a cursor
Declaring a cursor

DECLARE

CURSOR <name> [<arguments>] IS

<SELECT statement>;

Example

DECLARE

CURSOR Order_Cursor (OrdDate DATE) IS

SELECT SO.Order_ID, SO.Customer_ID, SO.Ship_date

FROM Sales_order SO

WHERE SO.Ship_date > OrdDate ;

SFDV3002

using cursors
Using cursors

OPEN <cursor> [(<arguments>)];

FETCH <cursor> INTO <variable>;

CLOSE <cursor>;

Example (usually FETCH in a loop)

DECLARE This_order Sales_order%ROWTYPE;

...

OPEN Order_Cursor (\'12-DEC-2007\');

FETCH Order_Cursor INTO This_order;

CLOSE Order_Cursor;

SFDV3002

cursor for loop
Cursor for loop

FOR <row variable> IN <cursor> LOOP

<code>

END LOOP;

  • Don’t need to declare <row variable> in advance.
  • OPEN, FETCH and CLOSE automatically.
  • Rows fetched one at a time into <row variable>.

SFDV3002

cursor properties
Cursor properties

%FOUND:true after FETCH if row retrieved, otherwise false (inverse %NOTFOUND)

%ROWCOUNT: number of rows retrieved so far

%ISOPEN:true if cursor is open

Examples

IF Order_Cursor%ISOPEN THEN ... ;

WHILE Order_Cursor%FOUND LOOP ... ;

SFDV3002

slide18

Procedures(Example 4–5; Concepts ch. 24; PL/SQL User’s Guide & Reference ch. 8; Oracle10g Application Developer’s Guide—Fundamentals ch. 7)

PROCEDURE <name> [(<arguments>)] IS

[<local declarations>]

BEGIN

<code>

[EXCEPTION

<exception handlers>]

END;

SFDV3002

functions
Functions

FUNCTION <name> [(<arguments>)]

RETURN <datatype> IS

[<local declarations>]

... (remainder as for procedures)

SFDV3002

stored procedures functions kifer 8 2 5 example 4 6
Stored procedures & functions(Kifer §8.2.5; Example 4–6)
  • CREATE OR REPLACE PROCEDURE <name>

[(<arguments>)] IS

... (remainder as for non-stored procedure)

CREATE OR REPLACE FUNCTION <name>

[(<arguments>)] RETURN <datatype> IS

... (remainder as for non-stored function)

  • Stored permanently in database.
  • Once stored, can call from both inside (SELECT) and outside database.

SFDV3002

slide21

Exceptions(Oracle10g Application Developer’s Guide—Fundamentals ch. 7 ▷ “Handling Run-Time PL/SQL Errors”;PL/SQL User’s Guide & Reference ch. 10)

  • Unusual situations or errors, e.g.:
    • divide by zero
    • integrity constraint violated
    • lock conflict
    • SELECT returned no data
  • Oracle10g errors unfriendly, so need to “trap” exceptions.
  • Compare Java.

SFDV3002

the exception block example 4 7
The EXCEPTION block(Example 4–7)

Optional EXCEPTION section in every PL/SQL block (cf. Java CATCH)

EXCEPTION

WHEN <exception-1> THEN

<do something>

WHEN <exception-2> THEN

<do something else>

[WHEN OTHERS THEN

<do default something>]

END;

Exception

handler

SFDV3002

types of exception pl sql user s guide reference ch 10
Types of exception(PL/SQL User’s Guide & Reference, ch. 10)

Built-in

  • Division by zero
  • No data found
  • Duplicate index key value
  • etc…

User-defined

DECLARE

The_Server_is_on_Fire EXCEPTION;

Total_Meltdown EXCEPTION;

SFDV3002

raising exceptions manually
Raising exceptions manually

IF <something really bad happens> THEN

RAISE Total_Meltdown; user defined

FETCH Order_Cursor INTO Order_row;

IF Order_Cursor%NOTFOUND THEN

RAISE No_Data_Found; built-in

SFDV3002

exception flow of control example 4 8
Exception flow of control(Example 4–8)
  • After exception handler completes, control returns to calling block. (cf. Java)
  • To avoid this, check for possible error conditions before exception handler is activated.

SFDV3002

slide26

Packages(Example 4–9; Concepts ch. 24; Oracle10g Application Developer’s Guide—Fundamentals ch. 7; PL/SQL User’s Guide & Reference ch. 9)

  • Collection of cursors, variables, constants, exceptions, code blocks, procedures, functions…
  • Using any item loads entire package into memory.
  • Many built-in or add-on packages:
    • web access
    • data replication
    • exception handling
    • screen I/O (DBMS_Output)
    • XML
    • etc…

SFDV3002

package specification
Package specification

CREATE OR REPLACE PACKAGE <name> IS

<public declarations>

<signatures of public procedures/functions>

END <name>;

SFDV3002

package body
Package body

CREATE OR REPLACE PACKAGE BODY <name> IS

<private declarations>

<implementations of public procs/fns>

[BEGIN

<optional "static" initialisation> ]

END <name>;

SFDV3002

package visibility
Package visibility

Public

  • Accessible from outside package
  • Declared in package specification
  • Implemented in package body

Private

  • Not accessible from outside package
  • Not declared in package specification
  • Implemented in package body

SFDV3002

overloading
Overloading
  • Define same procedure several times with different arguments (number and data type).
  • For example, print customer orders in different ways:
    • all orders that include product
    • all orders placed on date
    • all orders worth more than amount

SFDV3002

summary
Summary
  • PL/SQL is a full programming language providing many useful features:
    • flow of control
    • procedures & functions
    • cursors
  • Other DBMSs use similar languages; some standardisation in SQL:1999.
  • Ideal for complex stored code or business rules.

Next: Triggers

SFDV3002

triggers

Triggers

Complex automated

integrity constraints

SFDV3002

overview2
Overview
  • Definition and use
  • Specification
  • Triggers in Oracle10g

SFDV3002

automated actions triggers kifer 3 3 5 7 1
Automated actions: triggers(Kifer §3.3.5, 7.1)
  • Specific operation(s) on table automatically trigger other operation(s).
  • Typical uses:
    • automatically computed columns (e.g., update sale total when line items added/removed)
    • setting status values in response to updates
    • maintaining referential integrity (cf. ON DELETE CASCADE)
  • Standardised in SQL:1999, still wide variation.

SFDV3002

trigger specification kifer 7 2 7 3
Trigger specification(Kifer §7.2–7.3)

Activation condition(s)

  • Timing: BEFORE, AFTER
  • Type of operation: INSERT, DELETE, UPDATE
  • Column affected (UPDATE only, optional)
  • Table affected
  • Other conditions (optional)
  • Triggered operation
    • What to do when trigger is activated

SFDV3002

slide36

Oracle10g triggers(SQL Reference ch. 16 ▷ “CREATE TRIGGER”; Oracle10g Application Developer’s Guide—Fundamentals ch. 9; Concepts ch. 22; Example 4–10(a); see also Kifer §7.3)

CREATE [OR REPLACE] TRIGGER <name>

<timing><operation> ON <table/view>

[<referencing clause>]

[FOR EACH ROW|STATEMENT]

[WHEN <condition>]

BEGIN

<PL/SQL code>

END;

SFDV3002

slide37

Oracle10g triggers(SQL Reference ch. 16 ▷ “CREATE TRIGGER”; Oracle10g Application Developer’s Guide—Fundamentals ch. 9; Concepts ch. 22; Example 4–10(a); see also Kifer §7.3)

<timing>BEFORE, AFTER, INSTEAD OF

<operation> any combination of INSERT, UPDATE, DELETE

  • Also triggers on events (e.g., system startup & shutdown, user logon & logoff, DDL statements, etc.)

SFDV3002

update triggers example 4 10 b c
Update triggers(Example 4–10(b), (c))

Triggers activated only if particular columns modified

CREATE TRIGGER Update_Salary

BEFORE UPDATE OF Salary ON Employee

...

SFDV3002

row vs statement triggers kifer pp 254 255 concepts ch 22 types of trigger example 4 10 e
Row vs. statement triggers(Kifer pp. 254–255; Concepts ch. 22 ▷ “Types of Trigger”; Example 4–10(e))

FOR EACH ROW

  • Trigger executed once per row affected by activating operation.

FOR EACH STATEMENT (default)

  • Trigger executed once only, no matter how many rows affected.

SFDV3002

accessing row values in triggers
Accessing row values in triggers
  • :OLD and :NEW versions of affected row, depending on activating operation.
  • Rename :OLD and :NEW using REFERENCING clause. (see Example 4–10(d))

SFDV3002

conditional triggers when example 4 10 f
Conditional triggers: WHEN(Example 4–10(f))
  • Extra condition(s) (no subqueries).
  • Not for:
    • statement triggers
    • INSTEAD OF triggers (see slide 42)

SFDV3002

instead of triggers concepts ch 22 types of trigger kifer example 7 3 4 example 4 10 g
INSTEAD OF triggers(Concepts ch. 22 ▷ “Types of Trigger”; Kifer Example 7.3.4; Example 4–10(g))
  • Modify data “stored in” views that cannot normally be updated.
  • Example: instead of DELETEing from view, rewrite to DELETE from underlying base tables.

SFDV3002

triggers referential integrity kifer pp 262 264
Triggers & referential integrity(Kifer pp. 262–264)
  • Four possible reactions to PK value affected by UPDATE or DELETE:
    • Disallow change
    • Cascade change (ON <xxx> CASCADE)
    • Referencing FKs ⇒ null (ON <xxx> SET NULL)
    • Referencing FKs ⇒ default (ON <xxx> SET DEFAULT)
  • Oracle10g: ON DELETE CASCADE and ON DELETE SET NULL.

SFDV3002

slide44

Mutating tables (Oracle only)(Oracle10g Application Developer’s Guide—Fundamentals ch. 9 ▷ “Restrictions on Creating Triggers”; Example 4–11)

  • Table(s) being modified by activating operation of trigger.
  • Includes effects of ON DELETE CASCADE but not changes by other users.
  • Triggers cannot access mutating tables.
  • Row triggers only.

SFDV3002

summary1
Summary
  • Triggers provide means of automatically executing certain actions before or after some specific operation occurs.
  • Typically used for automatic calculations or referential integrity maintenance.

SFDV3002

ad