server_examples.sql 4.03 KB
Newer Older
O'Reilly Media, Inc. committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167
-- The SQL*Plus script server_examples.sql performs the following:
--   Creates the various wrappers, procedures and triggers for the
--   Deployment chapter.

-- connect as the fundamental_user
CONNECT fundamental_user/fundamental_password;

-- create a procedure for the ServerExample1.testMessage() method
CREATE OR REPLACE PROCEDURE test_message
AS
LANGUAGE JAVA
NAME 'ServerExample1.testMessage()';
/

-- create a procedure for the ServerExample1.addCustomer() method
CREATE OR REPLACE PROCEDURE add_customer(
  first_name VARCHAR2,
  last_name  VARCHAR2
) AS
LANGUAGE JAVA
NAME 'ServerExample1.addCustomer(java.lang.String, java.lang.String)';
/

-- create a procedure for the ServerExample1.displayCustomer() method
CREATE OR REPLACE PROCEDURE display_customer(
  id NUMBER
) AS
LANGUAGE JAVA
NAME 'ServerExample1.displayCustomer(int)';
/

-- create a function for the ServerExample1.countCustomers() method
CREATE OR REPLACE FUNCTION count_customers
RETURN NUMBER AS
LANGUAGE JAVA
NAME 'ServerExample1.countCustomers() return int';
/


-- create a package named server_example1 for
-- the methods in the ServerExample1 class
CREATE OR REPLACE PACKAGE server_example1 AS

  PROCEDURE test_message;

  PROCEDURE add_customer (
    first_name VARCHAR2,
    last_name  VARCHAR2
  );

  PROCEDURE display_customer (
    id NUMBER
  );

  FUNCTION count_customers
  RETURN NUMBER;

END server_example1;
/

-- create a package body for server_example1
CREATE OR REPLACE PACKAGE BODY server_example1 AS

  PROCEDURE test_message
  AS
  LANGUAGE JAVA
  NAME 'ServerExample1.testMessage()';

  PROCEDURE add_customer (
    first_name  VARCHAR2,
    last_name   VARCHAR2
  ) AS
  LANGUAGE JAVA
  NAME 'ServerExample1.addCustomer(java.lang.String, java.lang.String)';
  
  PROCEDURE display_customer (
    id NUMBER
  ) AS
  LANGUAGE JAVA
  NAME 'ServerExample1.displayCustomer(int)';

  FUNCTION count_customers
  RETURN NUMBER AS
  LANGUAGE JAVA
  NAME 'ServerExample1.countCustomers() return int';

END server_example1;
/


-- create a procedure named add_display_count that
-- calls the wrappers in the package server_example1
-- to add and display a new customer, and count the number
-- of customers
CREATE OR REPLACE PROCEDURE add_display_count AS

  number_of_customers INT;

BEGIN

  server_example1.add_customer('Joe', 'Smith');
  server_example1.display_customer(8);
  number_of_customers := server_example1.count_customers;
  dbms_output.put_line('There are ' || number_of_customers ||
    ' customers');

END add_display_count;
/


-- create a database trigger named price_update_trigger
-- that fires when the new price for a row in the products table
-- is 10% greater than the old price.
-- The trigger audits the event by storing the product id,
-- the new price and the old price in the price_audit table.
CREATE OR REPLACE TRIGGER
  product_price_update_trigger
BEFORE UPDATE OF
  price
ON
  products
FOR EACH ROW
WHEN
  (new.price > old.price * 1.1)
BEGIN

  dbms_output.put_line('For product id ' || :old.id);
  dbms_output.put_line('Old price = ' || :old.price);
  dbms_output.put_line('New price = ' || :new.price);
  dbms_output.put_line('The new price is 10% greater than the old price - auditing change');

  -- insert row into the price_audit table
  INSERT INTO
    price_audit
  VALUES (
    :old.id, :old.price, :new.price
  );

END;
/


CREATE OR REPLACE PROCEDURE product_price_update(
  product_id NUMBER,
  old_price  NUMBER,
  new_price  NUMBER
) AS
LANGUAGE JAVA
NAME 'ServerExample2.productPriceUpdate(int, double, double)';
/


-- recreate the database trigger named price_update_trigger
--
-- The body of the trigger has been rewritten in SQLJ and
-- moved to productPriceUpdate() in the file ServerExample2.sqlj.
CREATE OR REPLACE TRIGGER
  product_price_update_trigger
BEFORE UPDATE OF
  price
ON
  products
FOR EACH ROW
WHEN
  (new.price > old.price * 1.1)
CALL product_price_update(:old.id, :old.price, :new.price)
/