Docstoc

Distributed Database Applications

Document Sample
Distributed Database Applications Powered By Docstoc
					Distributed Database
     Applications
     COSC 5050
     Week One
                         Outline
    Introduction
    Course overview
    Oracle client environment
    Data dictionary
    Language fundamentals
    Program control



Webster University   Distributed Database Applications   Jiangping Wang
                     Introduction
    Oracle database
    PL/SQL
    Accessing Oracle server
    Database objects
    DDL and DML statements




Webster University    Distributed Database Applications   Jiangping Wang
                         PL/SQL
    Many Oracle applications are built using
    client-server architecture
          The Oracle database resides on the server
    PL/SQL is like any other programming
    language
    PL/SQL is not a stand-alone
    programming language
    PL/SQL is a part of the Oracle RDBMS


Webster University   Distributed Database Applications   Jiangping Wang
                         PL/SQL
    Highly structured, readable, accessible
    language
    Standard and portable language
    Embedded language
    High-performance, highly integrated
    database language




Webster University   Distributed Database Applications   Jiangping Wang
              Advantages of PL/SQL




Webster University   Distributed Database Applications   Jiangping Wang
                     SQL Example
    The semicolon terminates CREATE, INSERT,
    SELECT, and DROP statements
      CREATE TABLE STUDENT
      ( FIRST_NAME VARCHAR2(20),
         LAST_NAME VARCHAR2(20)
      );

      INSERT INTO STUDENT VALUES (‘JOHN’, ‘LUCKY’);

      SELECT FIRST_NAME, LAST_NAME FROM STUDENT;

      DROP TABLE STUDENT;




Webster University    Distributed Database Applications   Jiangping Wang
                     PL/SQL Example
    Page 7, 8, 9, 39 examples
    When Oracle reads a PL/SQL block, a
    semicolon marks the end of the
    individual statement within the block
    It is not a block terminator!
    The “/” executes the PL/SQL block




Webster University     Distributed Database Applications   Jiangping Wang
                Integration with SQL
DECLARE
  l_book_count INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO l_book_count
    FROM books
    WHERE author LIKE '%Feuerstein, Steven%';

   DBMS_OUTPUT.PUT_LINE(
     'Steven have written (or co-written) ' ||
     l_book_count || ' books.');

  UPDATE books
     SET author = REPLACE (author, 'Steven', 'Stephen')
     WHERE author LIKE '%Feuerstein, Steven%';
END;



Webster University   Distributed Database Applications   Jiangping Wang
  Control and Conditional Logic
       CREATE OR REPLACE PROCEDURE pay_out_balance(
          account_id_in IN accounts.id%TYPE)
       IS
          l_balance_remaining NUMBER;
       BEGIN
          LOOP
            l_balance_remaining :=
               account_balance (account_id_in);

           IF l_balance_remaining < 1000
           THEN
             EXIT;
           ELSE
             apply_balance (
               accont_id_in, l_balance_remaining);
           END IF;
         END LOOP;
       END pay_out_balance;

Webster University   Distributed Database Applications   Jiangping Wang
            When Things Go Wrong
CREATE OR REPLACE PROCEDURE check_account(account_id_in IN accounts.id%TYPE)
IS
    l_balance_remaining           NUMBER;
    l_balance_below_minimum       EXCEPTION;
    l_account_name                accounts.name%TYPE;
BEGIN
    SELECT name INTO l_account_name FROM accounts WHERE id = account_id_in;
    l_balance_remaining := account_balance (account_id_in);
    DBMS_OUTPUT.put_line (
        'Balance for ' || l_account_name || ' = ' || l_balance_remaining);
    IF l_balance_remaining < 1000
    THEN
        RAISE l_balance_below_minimum;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        log_error (...);
    WHEN l_balance_below_minimum
    THEN
        log_error (...);
        RAISE;
END;


 Webster University       Distributed Database Applications       Jiangping Wang
       Creating a Stored Program
    CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
       RETURN PLS_INTEGER
    AS
       words PLS_INTEGER := 0;
       len PLS_INTEGER := NVL(LENGTH(str),0);
       inside_a_word BOOLEAN;
    BEGIN
       FOR i IN 1..len + 1
       LOOP
          IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len
          THEN
             IF inside_a_word
             THEN
                words := words + 1;
                inside_a_word := FALSE;
             END IF;
          ELSE
             inside_a_word := TRUE;
          END IF;
       END LOOP;
       RETURN words;
    END;



Webster University       Distributed Database Applications   Jiangping Wang
          Executing, Showing, and
         Dropping a Stored Program
BEGIN
     DBMS_OUTPUT.PUT_LINE(
        'There are ' || wordcount(CHR(9)) || ' words in a tab');
END;


SELECT * FROM USER_OBJECTS;


DESC wordcount


SELECT TEXT FROM USER_SOURCE WHERE NAME = 'WORDCOUNT';


DROP FUNCTION wordcount;




Webster University   Distributed Database Applications   Jiangping Wang
                     Access Oracle
    Oracle server (service and host name)
          cronus, icarus.acs.webster.edu
    Oracle client
          Oracle 11g client download
          SQL*Plus
          SQL Developer
          Application Express (APEX) Workspace
    Your login and password
    Documentations

Webster University    Distributed Database Applications   Jiangping Wang
                     Oracle SQL*Plus




Webster University     Distributed Database Applications   Jiangping Wang
              Oracle SQL Developer




Webster University   Distributed Database Applications   Jiangping Wang
                     Oracle APEX




Webster University    Distributed Database Applications   Jiangping Wang
                     Data Dictionary
    USER_
    ALL_
    DBA_

    select view_name from all_views where
    view_name like 'USER%';




Webster University     Distributed Database Applications   Jiangping Wang
          Language Fundamentals
    PL/SQL block structure
          Modularization
          Scope
    Anonymous blocks
    Named blocks
          Procedures
          Functions
    Scope and visibility



Webster University     Distributed Database Applications   Jiangping Wang
                PL/SQL Block Structure
    Header
    Declaration section
    Execution section
    Exception section
     PROCEDURE get_happy (ename_in IN VARCHAR2)
     IS
        hiredata DATE;
     BEGIN
        hiredate := SYSDATE – 2;
        INSERT INTO employee (emp_name, hiredate)
          VALUES (ename_in, hiredate);
     EXCEPTION
        WHEN dup_val_in_index
        THEN
          DBMS_OUTPUT.PUT_LINE (‘Cannot insert.’);
     END;

Webster University     Distributed Database Applications   Jiangping Wang
                     Anonymous Blocks
    No header
    Begin with either DECLARE or BEGIN
    Cannot be called
                BEGIN
                  DBMS_OUTPUT.PUT_LINE (SYSDATE);
                END;

                DECLARE
                  l_right_now DATE := SYSDATE;
                BEGIN
                  DBMS_OUTPUT.PUT_LINE (l_right_now);
                END;



Webster University       Distributed Database Applications   Jiangping Wang
                     Named Blocks
    Procedures
    Functions
procedure Add_employee
       (ssn          in    varchar2
       , fname       in    varchar2
       , lname       in    varchar2
       , dept_num    in    number
       , code        in    number
       , sup_ssn     in    varchar2)
is
begin
       insert into employee values
              (ssn, fname, lname, dept_num, code, sup_ssn);
end; -- Add_employee


Webster University    Distributed Database Applications   Jiangping Wang
                      Named Blocks
function get_department(ssn_in in employee.ssn%type)
     return department.dept_name%type
is
     l_dept_name department.dept_name%type;
begin
     select dept_name into l_dept_name
         from department inner join employee
         on employee.dept_num = department.dept_num
         where ssn = ssn_in;
     dbms_output.put_line('department name: ' || l_dept_name);
     return l_dept_name;
exception
     when no_data_found then
         dbms_output.put_line(
             'no such employee or not in any department!');
         return null;
end;


 Webster University    Distributed Database Applications   Jiangping Wang
               Named Blocks in DB2

                     CREATE PROCEDURE sum(
                          IN p_a INTEGER,
                          IN p_b INTEGER,
                          OUT p_s INTEGER)
                          LANGUAGE SQL
                     BEGIN
                          SET p_s = p_a + p_b;
                     END;


                     CALL sum(100,200,?);




Webster University          Distributed Database Applications   Jiangping Wang
          Language Fundamentals
    PL/SQL character set
    Case-insensitive language
    Identifiers
          Up to 30 characters in length
          Must start with a letter
          Can include $, _, and #
          Cannot contain spaces
    PL/SQL keywords
    Comments
          Single-line comments using “--”
          Multi-line comments using /* … */


Webster University     Distributed Database Applications   Jiangping Wang
               PL/SQL Character Set

 Type                 Characters


 Letters              A-Z, a-z

 Digits               0-9

 Symbols              ~!@#$%*()_-+=|:;"'<>,.?/^

 Whitespace           Tab, space, newline, carriage return




Webster University   Distributed Database Applications       Jiangping Wang
       Program Control
IF statements
CASE
LOOP
WHILE loop
Cursor loop




           Distributed Database Applications   Jiangping Wang
                    IF Statement
IF salary > 40000
THEN
  give_bonus (employee_id, 500);
END IF;

IF salary <= 40000
THEN
  give_bonus (employee_id, 0);
ELSE
  give_bonus (employee_id, 500);
END IF;

declare
  salary number := 40000;
begin
  IF salary > 40000
  THEN
    dbms_output.put_line('Salary is greater than 40000');
  ELSE
    dbms_output.put_line('Salary is not greater than 40000');
  END IF;
end;
/

                        Distributed Database Applications       Jiangping Wang
                IF Statement
declare
  salary number := &salary;
begin
  IF salary BETWEEN 10000 AND 20000
  THEN
     dbms_output.put_line('Give bonus 1500.');
  ELSIF salary BETWEEN 20000 AND 40000
  THEN
     dbms_output.put_line('Give bonus 1000.');
  ELSIF salary > 40000
  THEN
     dbms_output.put_line('Give bonus 500.');
  ELSE
     dbms_output.put_line('Give bonus 0.');
  END IF;
end;
/


                   Distributed Database Applications   Jiangping Wang
         CASE Statement
Simple CASE statement
 declare
   salary_level number := &salary;
 begin
   case salary_level
   when 1 then
      dbms_output.put_line('give bonus             1500.');
   when 2 then
      dbms_output.put_line('give bonus             1000.');
   when 3 then
      dbms_output.put_line('give bonus             500.');
   else
      dbms_output.put_line('give bonus             0.');
   end case;
 end;
 /

               Distributed Database Applications              Jiangping Wang
         CASE Statement
Searched CASE statement
  declare
    salary number := &salary;
  begin
    case
    when salary between 10000 and 20000 then
       dbms_output.put_line('give bonus 1500.');
    when salary between 20000 and 40000 then
       dbms_output.put_line('give bonus 1000.');
    when salary > 40000 then
       dbms_output.put_line('give bonus 500.');
    else
       dbms_output.put_line('give bonus 0.');
    end case;
  end;
  /

              Distributed Database Applications   Jiangping Wang
           CASE Statement
 CASE expression
declare
  salary number := &salary;
  bonus_amount number;
begin
  bonus_amount :=
    case
    when salary BETWEEN 10000 AND 20000 THEN 1500
    when salary BETWEEN 20000 AND 40000 THEN 1000
    when salary > 40000 THEN 500
    else 0
    end;
  dbms_output.put_line(
              'Give bonus ' || bonus_amount || '.');
end;
/

                 Distributed Database Applications   Jiangping Wang
       Loop Statement
Simple loop
FOR loop
WHILE loop
Cursor FOR loop




           Distributed Database Applications   Jiangping Wang
               Simple Loop
PROCEDURE set_all_ranks (max_rank_in IN INTEGER)
IS
   ranking_level NUMBER (3) := 1;
BEGIN
   LOOP
     EXIT WHEN ranking_level > max_rank_in;
     set_rank (ranking_level);
     ranking_level := ranking_level + 1;
   END LOOP;
END set_all_ranks;


  Use EXIT or EXIT WHEN to exit loop

                Distributed Database Applications   Jiangping Wang
                      For Loop
PROCEDURE set_all_ranks (max_rank_in IN INTEGER)
IS
   ranking_level NUMBER (3) := 1;
BEGIN
   FOR ranking_level IN 1 .. max_rank_in
   LOOP
     set_rank (ranking_level);
   END LOOP;
END set_all_ranks;

Reverse loop:
FOR counter IN REVERSE 1 .. max
LOOP
   …
END LOOP;


                     Distributed Database Applications   Jiangping Wang
              WHILE Loop
PROCEDURE set_all_ranks (max_rank_in IN INTEGER)
IS
   ranking_level NUMBER (3) := 1;
BEGIN
   WHILE ranking_level <= max_rank_in
   LOOP
     set_rank (ranking_level);
     ranking_level := ranking_level + 1;
   END LOOP;
END set_all_ranks;




                Distributed Database Applications   Jiangping Wang
              Cursor FOR Loop
  Cursor with simple loop
DECLARE
  CURSOR name_cur IS
     SELECT lname, fname
     FROM employee WHERE ssn like '8%';
  name_rec name_cur%ROWTYPE;
BEGIN
  OPEN name_cur;
  LOOP
     FETCH name_cur INTO name_rec;
     EXIT WHEN name_cur%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(
         name_rec.fname || ' ' || name_rec.lname);
  END LOOP;
  CLOSE name_cur;
END;
/


                     Distributed Database Applications   Jiangping Wang
              Cursor FOR Loop
  Cursor with simple loop
DECLARE
  CURSOR occupancy_cur IS
     SELECT pet_id, room_number
     FROM occupancy
     WHERE occupied_dt = TRUNC(SYSDATE);
  occupancy_rec occupancy_cur%ROWTYPE;
BEGIN
  OPEN occupancy_cur;
  LOOP
     FETCH occupancy_cur INTO occupancy_rec;
     EXIT WHEN occupancy_cur%NOTFOUND;
     update_bill
       (occupancy_rec.pet_id, occupancy_rec.room_number);
  END LOOP;
  CLOSE occupancy_cur;
END;
                     Distributed Database Applications      Jiangping Wang
               Cursor FOR Loop
  Cursor For loop
DECLARE
  CURSOR occupancy_cur IS
     SELECT pet_id, room_number
     FROM occupancy
     WHERE occupied_dt = TRUNC(SYSDATE);
BEGIN
  FOR occupancy_rec IN occupancy_cur
  LOOP
     update_bill
       (occupancy_rec.pet_id, occupancy_rec.room_number);
  END LOOP;
END;




                      Distributed Database Applications     Jiangping Wang
            Cursor FOR Loop
  Cursor FOR loop
DECLARE
  CURSOR name_cur IS
     SELECT lname, fname
     FROM employee WHERE ssn like '8%';
BEGIN
  FOR name_rec IN name_cur
  LOOP
     DBMS_OUTPUT.PUT_LINE(
         name_rec.fname || ' ' || name_rec.lname);
  END LOOP;
END;
/



                    Distributed Database Applications   Jiangping Wang
                     Homework
        Given the table layout, write the SQL to
        create the table
        Create a script file to load database table
        Create an anonymous PL/SQL block to
        retrieve data from above database table
        Create a simple procedure in your DB2
        Company schema to retrieve employee data
        Project proposal




Webster University   Distributed Database Applications   Jiangping Wang

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:5/4/2012
language:
pages:41