Lab8

gistfile1.txt

--To avoid errors, drop tables if they exist. 

DROP TABLE line_item cascade constraints;
DROP TABLE customer_order cascacde constraints; 
DROP TABLE customer cascade constraints; 
DROP TABLE item cascade constraints;

CREATE TABLE customer(
customer_ID	DECIMAL(10)	NOT NULL,
customer_first	VARCHAR(30),
customer_last	VARCHAR(40),
customer_total	DECIMAL(12,	2),
PRIMARY KEY (customer_ID));

INSERT INTO customer VALUES(1,'John','Smith',0);

INSERT INTO customer VALUES(2,'Mary','Berman',0);
INSERT INTO customer VALUES(3,'Elizabeth','Johnson',0);
INSERT INTO customer VALUES(4,'Peter','Quigley',0);
INSERT INTO customer VALUES(5,'Stanton','Hurley',0);
INSERT INTO customer VALUES(6,'Yvette','Presley',0);
INSERT INTO customer VALUES(7,'Hilary','Marsh',0);

CREATE TABLE ITEM(

item_id	DECIMAL(10) NOT NULL,
description	VARCHAR(30),
price	DECIMAL(10),
PRIMARY KEY (item_id));

INSERT INTO item VALUES(1,'Plate',10);

INSERT INTO item VALUES(2,'Bowl',11);
INSERT INTO item VALUES(3,'Knife',5);
INSERT INTO item VALUES(4,'Fork',5);
INSERT INTO item VALUES(5,'Spoon',5);
INSERT INTO item VALUES(6,'Cup',12);
 
CREATE TABLE customer_order (

order_id	DECIMAL(10) NOT NULL,
customer_id DECIMAL(10) NOT NULL,
order_total DECIMAL(12,2),
order_date	DATE,
PRIMARY KEY (ORDER_ID),
FOREIGN KEY (CUSTOMER_ID) REFERENCES customer);

INSERT INTO customer_order VALUES(1,1,506,CAST('18-DEC-2005' AS DATE));

INSERT INTO customer_order VALUES(2,1,1000,CAST('17-DEC-2005' AS DATE));
INSERT INTO customer_order VALUES(3,3,15,CAST('19-DEC-2005' AS DATE));
INSERT INTO customer_order VALUES(4,3,15,CAST('20-DEC-2005' AS DATE));
INSERT INTO customer_order VALUES(5,2,1584,CAST('18-DEC-2005' AS DATE));
INSERT INTO customer_order VALUES(6,4,100,CAST('17-DEC-2005' AS DATE));
INSERT INTO customer_order VALUES(7,5,40,CAST('18-DEC-2005' AS DATE));
INSERT INTO customer_order VALUES(8,1,10,CAST('19-DEC-2005' AS DATE));

CREATE TABLE line_item(
order_id	DECIMAL(10) NOT NULL,
item_id	DECIMAL(10)	NOT NULL,
item_quantity	DECIMAL(10)	NOT NULL,
line_price	DECIMAL(12,2),
PRIMARY KEY (ORDER_ID, ITEM_ID),
FOREIGN KEY (ORDER_ID) REFERENCES customer_order,
FOREIGN KEY (ITEM_ID) REFERENCES item);

INSERT INTO line_item VALUES(1,1,10,100);

INSERT INTO line_item VALUES(1,5,2,10);
INSERT INTO line_item VALUES(1,2,36,396);
INSERT INTO line_item VALUES(2,1,95,950);
INSERT INTO line_item VALUES(2,3,10,50);
INSERT INTO line_item VALUES(3,4,3,15);
INSERT INTO line_item VALUES(4,4,3,15);

INSERT INTO line_item VALUES(5,6,132,1584);
INSERT INTO line_item VALUES(6,1,10,100);
INSERT INTO line_item VALUES(7,5,5,25);
INSERT INTO line_item VALUES(7,4,3,15);
INSERT INTO line_item VALUES(8,5,2,10);

COMMIT;


CREATE OR REPLACE PROCEDURE ADD_CUSTOMER_HARRY
IS
BEGIN
INSERT INTO CUSTOMER
(customer_id,customer_first,customer_last,customer_total)
VALUES(8, 'Harry', 'Joker', 0);
END;


CREATE OR REPLACE PROCEDURE ADD_CUSTOMER_HARRY
IS
BEGIN
INSERT INTO CUSTOMER
(customer_id,customer_first,customer_last,customer_total)
VALUES(8, 'Harry', 'Joker', 0);
END;

CREATE OR REPLACE PROCEDURE ADD_CUSTOMER( -- Create a new customer
cus_id_arg IN DECIMAL, -- The new customer ID, must be unused
first_name_arg IN VARCHAR, -- The new customer’s first name
last_name_arg IN VARCHAR) -- The new customer’s last name
IS -- Required by the syntax, but it doesn’t do anything in particular
BEGIN
INSERT INTO CUSTOMER
(customer_id,customer_first,customer_last,customer_total)
VALUES(cus_id_arg,first_name_arg,last_name_arg,0);
-- We start the customer with zero balance.
END;

BEGIN
ADD_CUSTOMER(9, 'Mary', 'Smith');
END;
SELECT * FROM CUSTOMER;


CREATE OR REPLACE PROCEDURE ADD_CUSTOMER( -- Create a new customer
cus_id_arg IN DECIMAL, -- The new customer ID, must be unused
first_name_arg IN VARCHAR, -- The new customer’s first name
last_name_arg IN VARCHAR, -- The new customer’s last name
CUST_BALANCE IN DECIMAL)
IS -- Required by the syntax, but it doesn’t do anything in particular
BEGIN
INSERT INTO CUSTOMER
(customer_id,customer_first,customer_last,customer_total)
VALUES(cus_id_arg,first_name_arg,last_name_arg,CUST_BALANCE);
-- We start the customer with zero balance.
END;

BEGIN
ADD_CUSTOMER(10, 'Gabriela', 'Jury', 10.99);
END;

SELECT * FROM CUSTOMER;

/*Do so now. 

Create such a stored procedure, then execute it to delete John Smith and
all Order and Line_item information associated with John Smith. 

Capture a screenshot of the commands to create and execute the stored procedure, as well as
the results of their execution. Also, be sure to capture screenshots of the SELECT
statements that list out the Customer, Order, and Line_item table after the stored
procedure has been executed. 

This will show that your stored procedure behaves as expected.
*/
SELECT * FROM CUSTOMER;
 

CREATE OR REPLACE PROCEDURE DELETE_JOHN_SMITH_STUFF(
first_name VARCHAR, last_name VARCHAR)
IS
   CUSTOMER_TO_DELETE DECIMAL(10) :=0;
   c1 SYS_REFCURSOR;
   SET SERVEROUTPUT ON;
BEGIN
    SELECT customer_ID INTO CUSTOMER_TO_DELETE FROM customer 
        WHERE customer_first=first_name AND customer_last=last_name;
    
    IF (CUSTOMER_TO_DELETE != 0) THEN
        open c1 for
        SELECT * FROM customer WHERE customer_ID=CUSTOMER_TO_DELETE;
        DBMS_SQL.RETURN_RESULT(c1);
    END IF;
    COMMIT;
END;

EXECUTE DELETE_JOHN_SMITH_STUFF('John','Smith');