--1 序列
CREATE SEQUENCE order_sq;


--2 产品类型表
CREATE TABLE product_types (
  product_type_id INTEGER
    CONSTRAINT product_types_pk PRIMARY KEY,
  name VARCHAR2(10) NOT NULL
);


--3 产品表
CREATE TABLE products (
  product_id INTEGER
    CONSTRAINT products_pk PRIMARY KEY,
  product_type_id INTEGER
    CONSTRAINT products_fk_product_types
    REFERENCES product_types(product_type_id),
  name VARCHAR2(30) NOT NULL,
  description VARCHAR2(50),
  price NUMBER(5, 2)
);


--4 客户表
CREATE TABLE customers (
  customer_id INTEGER
    CONSTRAINT customers_pk PRIMARY KEY,
  first_name VARCHAR2(10) NOT NULL,
  last_name VARCHAR2(10) NOT NULL,
  dob DATE,
  phone VARCHAR2(12)
);


--5 订单表
CREATE TABLE orders (
  order_id INTEGER
    CONSTRAINT orders_pk PRIMARY KEY,
  product_id INTEGER
    CONSTRAINT purchases_fk_products
    REFERENCES products(product_id),
  customer_id INTEGER
    CONSTRAINT purchases_fk_customers
    REFERENCES customers(customer_id),
  quantity INTEGER NOT NULL
);


--6 产品类型表、产品表、客户表数据
INSERT INTO customers (
  customer_id, first_name, last_name, dob, phone
) VALUES (
  1, 'John', 'Brown', to_date('19650101','yyyymmdd'), '800-555-1211'
);

INSERT INTO product_types (
  product_type_id, name
) VALUES (
  1, 'Book'
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  1, 1, 'Modern Science', 'A description of modern science', 19.95
);

COMMIT;


--7 存储过程
CREATE OR REPLACE PACKAGE pkg_dbfunc AS

  FUNCTION f_place_order (
    p_product_id INTEGER,
    p_customer_id INTEGER,
    p_quantity INTEGER
  )
  RETURN VARCHAR2;
  
END pkg_dbfunc;
/

CREATE OR REPLACE PACKAGE BODY pkg_dbfunc AS

  FUNCTION f_place_order (
    p_product_id INTEGER,
    p_customer_id INTEGER,
    p_quantity INTEGER
  )
  RETURN VARCHAR2 IS
    v_customer_count INTEGER;
    v_product_count INTEGER;
    v_order_id INTEGER;
  BEGIN
    -- count the number of products with the
    -- supplied p_product_id (should be 1 if the product exists)
    SELECT COUNT(*)
    INTO v_product_count
    FROM products
    WHERE product_id = p_product_id;

    IF v_product_count = 0 THEN
      RETURN 'No such product';
    END IF;

    -- count the number of customers with the
    -- supplied p_customer_id (should be 1)
    SELECT COUNT(*)
    INTO v_customer_count
    FROM customers
    WHERE customer_id = p_customer_id;

    IF v_customer_count = 0 THEN
      RETURN 'No such customer';
    END IF;

    -- get the next value from orders_sq
    SELECT order_sq.nextval
    INTO v_order_id
    FROM dual;

    -- place the order
    INSERT INTO orders (
      order_id, product_id, customer_id, quantity
    ) VALUES (
      v_order_id, p_product_id, p_customer_id, p_quantity
    );
    COMMIT;
    RETURN 'Order placed with id of ' || v_order_id;

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RETURN 'Order not placed';
  END f_place_order;
  
END pkg_dbfunc;
/