![Fechar [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Listagem 2. Código de exemplo
CONNECT TO test
/
CREATE USER TEMPORARY TABLESPACE usertemp
/
CREATE SEQUENCE seq_customer_id start WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE NO CACHE
/
CREATE TABLE customer(
customer_id NUMBER(10) NOT NULL,
first_name VARCHAR2(30) NOT NULL,
last_name VARCHAR2(40) NOT NULL,
email VARCHAR2(100) NOT NULL,
phone_number char(14) NOT NULL,
birth_date date NOT NULL,
registration_time TIMESTAMP(0) DEFAULT SYSDATE NOT NULL)
/
INSERT INTO CUSTOMER(customer_id, first_name, last_name, email, phone_number, birth_date,
registration_time) VALUES
(seq_customer_id.nextval, 'Mike', 'Smith' , 'mikesmith@yahoo.com', '534-234-2323',
TO_DATE('1988-01-16','yyyy-mm-dd'), DEFAULT),
(seq_customer_id.nextval, 'Joan', 'Jett' , 'jjett@cardinal.net', '585-245-1212',
TO_DATE('1960-02-02','yyyy-mm-dd'), DEFAULT),
(seq_customer_id.nextval, 'Colin', 'Taylor' , 'colin@yahoo.com', '234-321-2341',
TO_DATE('1982-03-16','yyyy-mm-dd'), DEFAULT),
(seq_customer_id.nextval, 'Graham', 'Norton' , 'spider@gmail.com', '416-683-1092',
TO_DATE('1985-04-24','yyyy-mm-dd'), DEFAULT),
(seq_customer_id.nextval, 'Patsy' , 'Stone' , 'patsy01@yahoo.ca', '904-643-1432',
TO_DATE('1959-05-30','yyyy-mm-dd'), DEFAULT)
/
CREATE SEQUENCE seq_product_id start WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE NO CACHE
/
CREATE TABLE product(
product_id NUMBER(10) NOT NULL,
category_name VARCHAR2(30) NOT NULL,
product_name VARCHAR2(30) NOT NULL,
product_price NUMBER(8,2) NOT NULL,
inventory_quantity NUMBER default 0 NOT NULL)
/
INSERT INTO product (product_id, category_name, product_name, product_price,
inventory_quantity) VALUES
(seq_product_id.nextval, 'Wild Rose', 'Flowers', 19.99, 20000),
(seq_product_id.nextval, 'Tulip', 'Flowers', 15.99, 30000),
(seq_product_id.nextval, 'Garden Rake', 'Hand Tools', 18.99, 5000),
(seq_product_id.nextval, 'Electric Trimmer', 'Power Tools', 68.99, 9000),
(seq_product_id.nextval, 'Air Freshener', 'Automotive', 5.99, 44000),
(seq_product_id.nextval, 'Wax', 'Automotive', 15.68, 100000),
(seq_product_id.nextval, 'Antifreeze', 'Automotive', 20.98, 150000)
/
CREATE SEQUENCE seq_order_id start WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE NO CACHE
/
CREATE TABLE orders(
order_id NUMBER(10) NOT NULL,
customer_id NUMBER(10) NOT NULL,
creation_time DATE default sysdate NOT NULL,
estimated_delivery_time date,
total_price NUMBER(8,2) NOT NULL,
order_status VARCHAR2(15),
CONSTRAINT check_order_status CHECK (order_status IN ('PROCESSING','ENROUTE',
'COMPLETE')))
/
CREATE GLOBAL TEMPORARY TABLE shopping_cart (
product_id NUMBER(10),
product_quantity NUMBER
) ON COMMIT PRESERVE ROWS
/
CREATE OR REPLACE PROCEDURE add_item_to_shopping_cart(p_product_id
shopping_cart.product_id%TYPE, p_product_quantity shopping_cart.product_quantity%TYPE) IS
v_total_quantity NUMBER := p_product_quantity;
product_exists_in_cart BOOLEAN :=FALSE;
CURSOR test_existence IS SELECT product_quantity FROM shopping_cart
WHERE product_id=p_product_id;
BEGIN
OPEN test_existence;
FETCH test_existence INTO v_total_quantity;
IF(test_existence%FOUND) THEN
UPDATE shopping_cart SET product_quantity=v_total_quantity+p_product_quantity;
ELSE
INSERT INTO shopping_cart VALUES(p_product_id, v_total_quantity);
END IF;
END;
/
CREATE OR REPLACE PROCEDURE create_order (p_customer_id customer.customer_id%TYPE,
o_order_id OUT orders.order_id%TYPE) IS
CURSOR c_get_shopping_cart IS SELECT product_id, product_quantity FROM shopping_cart;
i INTEGER := 1;
v_total_price orders.total_price%TYPE := 0;
v_product_price product.product_price%TYPE;
v_customer customer%ROWTYPE;
v_order orders%ROWTYPE;
BEGIN
FOR shopping_cart_rec IN c_get_shopping_cart LOOP
SELECT product_price INTO v_product_price FROM product WHERE product_id=
shopping_cart_rec.product_id;
v_total_price := v_total_price +
v_product_price*shopping_cart_rec.product_quantity;
i := i + 1;
END LOOP;
IF(i < 1) THEN
DBMS_OUTPUT.PUT_LINE('You need to have at least 1 product in your shopping cart');
ELSE
INSERT INTO orders VALUES(seq_order_id.NEXTVAL, p_customer_id, sysdate, sysdate
+ 3.5, v_total_price, 'PROCESSING');
SELECT * INTO v_customer FROM customer WHERE customer_id=p_customer_id;
SELECT * INTO v_order FROM orders WHERE order_id=seq_order_id.CURRVAL;
DBMS_OUTPUT.PUT_LINE('Customer : ' || v_customer.first_name || ', ' ||
v_customer.last_name);
DBMS_OUTPUT.PUT_LINE('Order creation : ' || TO_CHAR(v_order.creation_time,
'MM-DD-YYYY'));
DBMS_OUTPUT.PUT_LINE('Estimated Delivery : ' || TO_CHAR(
v_order.estimated_delivery_time,'MM-DD-YYYY'));
DBMS_OUTPUT.PUT_LINE('Status : ' || DECODE(v_order.order_status,'D',
'Delivered', 'Shipped'));
DBMS_OUTPUT.PUT_LINE('Total price : ' || TO_CHAR(v_order.total_price ,
'$999,999.99'));
END IF;
END create_order;
/
|