CREATE TABLE purchases_timestamp_with_tz (
product_id INTEGER REFERENCES products(product_id),
customer_id INTEGER REFERENCES customers(customer_id),
made_on TIMESTAMP(4) WITH TIME ZONE
);
CREATE TABLE purchases_with_local_tz (
product_id INTEGER REFERENCES products(product_id),
customer_id INTEGER REFERENCES customers(customer_id),
made_on TIMESTAMP(4) WITH LOCAL TIME ZONE
);
CREATE TABLE coupons (
coupon_id INTEGER CONSTRAINT coupons_pk PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
duration INTERVAL YEAR(3) TO MONTH
);
CREATE TABLE promotions (
promotion_id INTEGER CONSTRAINT promotions_pk PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
duration INTERVAL DAY(3) TO SECOND (4)
);
CREATE TABLE order_status (
order_status_id INTEGER
CONSTRAINT default_example_pk PRIMARY KEY,
status VARCHAR2(20) DEFAULT 'Order placed' NOT NULL,
last_modified DATE DEFAULT SYSDATE
);
CREATE TABLE reg_exps (
id NUMBER
CONSTRAINT reg_exps_pk PRIMARY KEY,
text VARCHAR2(512) NOT NULL
);
-- create the PL/SQL functions, procedures, packages and triggers
CREATE OR REPLACE PROCEDURE update_product_price(
p_product_id IN products.product_id%TYPE,
p_factor IN NUMBER
) AS
v_product_count INTEGER;
BEGIN
-- count the number of products with the
-- supplied product_id (should be 1 if the product exists)
SELECT COUNT(*)
INTO v_product_count
FROM products
WHERE product_id = p_product_id;
-- if the product exists (v_product_count = 1) then
-- update that product's price
IF v_product_count = 1 THEN
UPDATE products
SET price = price * p_factor
WHERE product_id = p_product_id;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END update_product_price;
/
CREATE OR REPLACE FUNCTION circle_area (
p_radius IN NUMBER
) RETURN NUMBER AS
v_pi NUMBER := 3.1415926;
v_area NUMBER;
BEGIN
v_area := v_pi * POWER(p_radius, 2);
RETURN v_area;
END circle_area;
/
CREATE OR REPLACE FUNCTION average_product_price (
p_product_type_id IN INTEGER
) RETURN NUMBER AS
v_average_product_price NUMBER;
BEGIN
SELECT AVG(price)
INTO v_average_product_price
FROM products
WHERE product_type_id = p_product_type_id;
RETURN v_average_product_price;
END average_product_price;
/
CREATE OR REPLACE PACKAGE product_package AS
TYPE t_ref_cursor IS REF CURSOR;
FUNCTION get_products_ref_cursor RETURN t_ref_cursor;
PROCEDURE update_product_price (
p_product_id IN products.product_id%TYPE,
p_factor IN NUMBER
);
END product_package;
/作者: beneastsun 时间: 2011-3-13 23:28
CREATE OR REPLACE PACKAGE BODY product_package AS
FUNCTION get_products_ref_cursor
RETURN t_ref_cursor IS
products_ref_cursor t_ref_cursor;
BEGIN
-- get the REF CURSOR
OPEN products_ref_cursor FOR
SELECT product_id, name, price
FROM products;
-- return the REF CURSOR
RETURN products_ref_cursor;
END get_products_ref_cursor;
PROCEDURE update_product_price (
p_product_id IN products.product_id%TYPE,
p_factor IN NUMBER
) AS
v_product_count INTEGER;
BEGIN
-- count the number of products with the
-- supplied product_id (should be 1 if the product exists)
SELECT COUNT(*)
INTO v_product_count
FROM products
WHERE product_id = p_product_id;
-- if the product exists (v_product_count = 1) then
-- update that product's price
IF v_product_count = 1 THEN
UPDATE products
SET price = price * p_factor
WHERE product_id = p_product_id;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- perform a rollback when an exception occurs
ROLLBACK;
END update_product_price;
END product_package;
/
CREATE OR REPLACE TRIGGER before_product_price_update
BEFORE UPDATE OF price
ON products
FOR EACH ROW WHEN (new.price < old.price * 0.75)
BEGIN
dbms_output.put_line('product_id = ' || ld.product_id);
dbms_output.put_line('Old price = ' || ld.price);
dbms_output.put_line('New price = ' || :new.price);
dbms_output.put_line('The price reduction is more than 25%');
-- insert row into the product_price_audit table
INSERT INTO product_price_audit (
product_id, old_price, new_price
) VALUES ( ld.product_id, ld.price, :new.price
);
END before_product_price_update;
/
ld.product_id);