A First Programming Exercise For PLSQL code

A First Programming Exercise For PLSQL code

The Data Model

 The Physical Database Design

CREATE TABLE books(
 isbn VARCHAR2(13) NOT NULL PRIMARY KEY,
 title VARCHAR2(200),
 summary VARCHAR2(2000),
 author VARCHAR2(200),
 date_published DATE,
 page_count NUMBER
);
 
CREATE TABLE book_copies(
  barcode_id VARCHAR2(100) NOT NULL PRIMARY KEY,
  isbn VARCHAR2(13)
);

SQL> desc books;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ISBN                                      NOT NULL VARCHAR2(13)
 TITLE                                              VARCHAR2(200)
 SUMMARY                                            VARCHAR2(2000)
 AUTHOR                                             VARCHAR2(200)
 DATE_PUBLISHED                                     DATE
 PAGE_COUNT                                         NUMBER
 
SQL> desc book_copies;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BARCODE_ID                                NOT NULL VARCHAR2(100)
 ISBN                                               VARCHAR2(13)
 
SQL> 

Syntax to create a procedure

CREATE [ OR REPLACE ] PROCEDURE procedure_name
 (parameter1 MODE DATATYPE [ DEFAULT expression ],
parameter2 MODE DATATYPE [ DEFAULT expression ],
 ...)
AS
[ variable1 DATATYPE;
variable2 DATATYPE;
 ... ]
BEGIN
 executable_statements
[ EXCEPTION
 WHEN exception_name
 THEN
 executable_statements ]
END;
/

CREATE OR REPLACE PROCEDURE add_book(isbn_in           IN VARCHAR2,
                                     barcode_id_in     IN VARCHAR2,
                                     title_in          IN VARCHAR2,
                                     author_in         IN VARCHAR2,
                                     page_count_in     IN NUMBER,
                                     summary_in        IN VARCHAR2 DEFAULT NULL,
                                     date_published_in IN DATE DEFAULT NULL) AS
BEGIN
  /* check for reasonable inputs */
  IF isbn_in IS NULL THEN
    RAISE VALUE_ERROR;
  END IF;
  /* put a record in the "books" table */
  INSERT INTO books
    (isbn, title, summary, author, date_published, page_count)
  VALUES
    (isbn_in,
     title_in,
     summary_in,
     author_in,
     date_published_in,
     page_count_in);
  /* if supplied, put a record in the "book_copies" table */
  IF barcode_id_in IS NOT NULL THEN
    INSERT INTO book_copies
      (isbn, barcode_id)
    VALUES
      (isbn_in, barcode_id_in);
  END IF;
END add_book;
/

BEGIN
 add_book('1-56592-335-9',
 '100000001',
 'Oracle PL/SQL Programming',
 'Feuerstein, Steven, with Bill Pribyl',
 987,
 'Reference for PL/SQL developers, '
 || 'including examples and best practice recommendations.',
 TO_DATE('01-SEP-1997','DD-MON-YYYY'));
END;
/

SQL> select * from books;
ISBN          TITLE                                                                            SUMMARY                                                                          AUTHOR                                                                           DATE_PUBLISHED PAGE_COUNT
------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------- ----------
1-56592-335-9 Oracle PL/SQL Programming                                                        Reference for PL/SQL developers, including examples and best practice recommenda Feuerstein, Steven, with Bill Pribyl                                             9/1/97                987
 
SQL> 

 Named notation

add_book(isbn_in => '1-56592-335-9',
 title_in => 'Oracle PL/SQL Programming',
 summary_in => 'Reference for PL/SQL developers, ' ||
 'including examples and best practice recommendations.'
 author_in => 'Feuerstein, Steven, with Bill Pribyl',
 date_published_in => NULL,
 page_count_in => 987,
 barcode_id_in => '100000001')

Retrieving a Book Count with a Function

Syntax for Creating a Function

CREATE [ OR REPLACE ] FUNCTION procedure_name
 (parameter1 MODE DATATYPE DEFAULT expression,
parameter2 MODE DATATYPE DEFAULT expression,
 ...)
RETURN DATATYPE
AS
[ variable1 DATATYPE;
variable2 DATATYPE;
 ... ]
BEGIN
 executable_statement;
 RETURN expression;
[ EXCEPTION
 WHEN exception_name
 THEN
 executable_statement; ]
END;
/

this code differs from a procedure in only two places:

• the header, which specifies the returned datatype,
• and the body,which must explicitly convey a value back to the caller.

RETURN datatype

In the header,the RETURN clause is part of the function declaration. It tells the compiler (and other programs) what datatype to expect back when you invoke the function

RETURN expression

Inside the executable section,this use of RETURN is known as the return statement,and it says “Okay,I’m all done; it’s time to send back (return) the following value.” You can also put a RETURN statement in the EXCEPTION section.

Code for the book_copy_qty Function

CREATE OR REPLACE FUNCTION book_copy_qty(isbn_in IN VARCHAR2) 
RETURN NUMBER 
AS
  number_o_copies NUMBER := 0;
  CURSOR bc_cur IS
    SELECT COUNT(*) FROM book_copies WHERE isbn = isbn_in;
BEGIN
  IF isbn_in IS NOT NULL THEN
    OPEN bc_cur;
    FETCH bc_cur
      INTO number_o_copies;
    CLOSE bc_cur;
  END IF;
  RETURN number_o_copies;
END;
/

 Using the function

DECLARE
local_variable DATATYPE;
BEGIN
local_variable := function_name (argument1, argument2, ...);
END;
/

SQL> DECLARE
  2   how_many INTEGER;
  3  BEGIN
  4   how_many := book_copy_qty('1-56592-335-9');
  5  END;
  6  /
PL/SQL procedure successfully completed
 
SQL> 

pass the result to DBMS_OUTPUT.PUT_ LINE to print the result:

SQL> 
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2   DBMS_OUTPUT.PUT_LINE('Number of copies of 1-56592-335-9: '
  3   || book_copy_qty('1-56592-335-9'));
  4  END;
  5  /
Number of copies of 1-56592-335-9: 1
PL/SQL procedure successfully completed
 
SQL> 

Some rules about functions

• You can’t create one of these standalone (or top-level) functions with the same name as a standalone procedure. If you adopt the practice of naming procedures with verb phrases,and functions with noun phrases,you shouldn’t have to worry about this potential clash.

• If you forget the RETURN clause in the header,your function won’t compile. That’s a good thing,because compiler errors are considered “early notification.” But,if you forget the RETURN in the body,you won’t find out until you run the function,at which point Oracle will spit out the error ORA-06503: PL/SQL: Function returned without value. There’s a good argument for thorough testing.

• When you invoke a function,the calling program must do something with the value the function returns,such as store it in a local variable. PL/SQL doesn’t tolerate “ignored” function results the way C does.

• When the RETURN statement in the body gets executed,not only does the value flow back to the caller,but so does the “thread of execution.” In other words, code that appears below the RETURN will not run.

A “Gotcha” about Exceptions Raised by Functions in the Declaration Section

DECLARE
 how_many NUMBER := book_copy_qty('xyz');
BEGIN
 ...whatever...
EXCEPTION
 WHEN OTHERS
 THEN
 /* SURPRISE! Exceptions raised in the declaration section CANNOT be
 || handled here!
 */
...
END;
/

A Results-Checking Utility

SQL> 
SQL> CREATE OR REPLACE PROCEDURE reporteq (description IN VARCHAR2,
  2         expected_value IN VARCHAR2, actual_value IN VARCHAR2) AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT(description || ': ');
  5    IF expected_value = actual_value
  6       OR (expected_value IS NULL AND actual_value IS NULL)
  7    THEN
  8       DBMS_OUTPUT.PUT_LINE('PASSED');
  9    ELSE
 10       DBMS_OUTPUT.PUT_LINE('FAILED. Expected ' || expected_value
 11         || '; got ' || actual_value);
 12    END IF;
 13  END;
 14  /
Procedure created
 
SQL> 

Using PL/SQL Packages to Organize Code

Parts of Packages

For reasons that will become clear as we go on,packages usually have two parts: a specification (often abbreviated as spec) and a body.

The package specification

CREATE OR REPLACE PACKAGE package_name
AS
program1_header;
program2_header;
program3_header;
END package_name;
/

CREATE OR REPLACE PACKAGE book
AS
 PROCEDURE add(isbn_in IN VARCHAR2, title_in IN VARCHAR2,
 author_in IN VARCHAR2, page_count_in IN NUMBER,
 summary_in IN VARCHAR2 DEFAULT NULL,
 date_published_in IN DATE DEFAULT NULL,
 barcode_id_in IN VARCHAR2 DEFAULT NULL);
 PROCEDURE add_copy(isbn_in IN VARCHAR2, barcode_id_in IN VARCHAR2);
 FUNCTION book_copy_qty(isbn_in IN VARCHAR2)
 RETURN NUMBER;
 PROCEDURE change(isbn_in IN VARCHAR2, new_title IN VARCHAR2,
 new_author IN VARCHAR2, new_page_count IN NUMBER,
 new_summary IN VARCHAR2 DEFAULT NULL,
 new_date_published IN DATE DEFAULT NULL);
 PROCEDURE remove_copy(barcode_id_in IN VARCHAR2);
 PROCEDURE weed(isbn_in IN VARCHAR2);
END book;
/

The package body

CREATE OR REPLACE PACKAGE BODY package_name
AS
private_programs; /* optional */
program1_body;
program2_body;
program3_body;
END package_name;
/

CREATE OR REPLACE PACKAGE BODY book AS
  /* private procedure for use only in this package body */
  
  PROCEDURE assert_notnull(tested_variable IN VARCHAR2) IS
  BEGIN
    IF tested_variable IS NULL THEN
      RAISE VALUE_ERROR;
    END IF;
  END assert_notnull;
  
  FUNCTION book_copy_qty(isbn_in IN VARCHAR2) RETURN NUMBER AS
    number_o_copies NUMBER := 0;
    CURSOR bc_cur IS
      SELECT COUNT(*) FROM book_copies WHERE isbn = isbn_in;
  BEGIN
    IF isbn_in IS NOT NULL THEN
      OPEN bc_cur;
      FETCH bc_cur
        INTO number_o_copies;
      CLOSE bc_cur;
    END IF;
    RETURN number_o_copies;
  END;
  
  PROCEDURE add(isbn_in           IN VARCHAR2,
                title_in          IN VARCHAR2,
                author_in         IN VARCHAR2,
                page_count_in     IN NUMBER,
                summary_in        IN VARCHAR2,
                date_published_in IN DATE,
                barcode_id_in     IN VARCHAR2) IS
  BEGIN
    assert_notnull(isbn_in);
    INSERT INTO books
      (isbn, title, summary, author, date_published, page_count)
    VALUES
      (isbn_in,
       title_in,
       summary_in,
       author_in,
       date_published_in,
       page_count_in);
    IF barcode_id_in IS NOT NULL THEN
      add_copy(isbn_in, barcode_id_in);
    END IF;
  END add;
  
  PROCEDURE add_copy(isbn_in IN VARCHAR2, barcode_id_in IN VARCHAR2) IS
  BEGIN
    assert_notnull(isbn_in);
    assert_notnull(barcode_id_in);
    INSERT INTO book_copies
      (isbn, barcode_id)
    VALUES
      (isbn_in, barcode_id_in);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      NULL;
  END;
  
  PROCEDURE change(isbn_in            IN VARCHAR2,
                   new_title          IN VARCHAR2,
                   new_author         IN VARCHAR2,
                   new_page_count     IN NUMBER,
                   new_summary        IN VARCHAR2 DEFAULT NULL,
                   new_date_published IN DATE DEFAULT NULL) IS
  BEGIN
    assert_notnull(isbn_in);
    UPDATE books
       SET title          = new_title,
           author         = new_author,
           page_count     = new_page_count,
           summary        = new_summary,
           date_published = new_date_published
     WHERE isbn = isbn_in;
    IF SQL%ROWCOUNT = 0 THEN
      RAISE NO_DATA_FOUND;
    END IF;
  END change;
  
  PROCEDURE remove_copy(barcode_id_in IN VARCHAR2) IS
  BEGIN
    assert_notnull(barcode_id_in);
    DELETE book_copies WHERE barcode_id = barcode_id_in;
  END remove_copy;
  
  PROCEDURE weed(isbn_in IN VARCHAR2) IS
  BEGIN
    assert_notnull(isbn_in);
    DELETE book_copies WHERE isbn = isbn_in;
    DELETE books WHERE isbn = isbn_in;
    IF SQL%ROWCOUNT = 0 THEN
      RAISE NO_DATA_FOUND;
    END IF;
  END weed;
  
END book;
/

 Benefits of Using Packages

• Organization
• Ease of comprehension
• Design options
• Performance
• Session convenience features
• Special PL/SQL features
• Less recompilation pain