Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 26, 2024

Assignment 4-12: Using a Cursor Variable Create a block with a single cursor that can perform a different query of pledge payment data based on

Assignment 4-12: Using a Cursor Variable

Create a block with a single cursor that can perform a different query of pledge payment data based on user input. Input provided to the block includes a donor ID and an indicator value of D or S. The D represents details and indicates that each payment on all pledges the donor has made should be displayed. The S indicates displaying summary data of the pledge payment total for each pledge the donor has made.

Here's the data charts relating to the problem:

DROP VIEW bb_basketitem_vu;

DROP VIEW bb_ship_vu;

DROP TABLE bb_department CASCADE CONSTRAINTS;

DROP TABLE bb_product CASCADE CONSTRAINTS;

DROP TABLE bb_productoption CASCADE CONSTRAINTS;

DROP TABLE bb_productoptiondetail CASCADE CONSTRAINTS;

DROP TABLE bb_productoptioncategory CASCADE CONSTRAINTS;

DROP TABLE bb_basketitem CASCADE CONSTRAINTS;

DROP TABLE bb_basket CASCADE CONSTRAINTS;

DROP TABLE bb_shopper CASCADE CONSTRAINTS;

DROP TABLE bb_basketstatus CASCADE CONSTRAINTS;

DROP TABLE bb_tax CASCADE CONSTRAINTS;

DROP TABLE bb_shipping CASCADE CONSTRAINTS;

DROP TABLE bb_promolist CASCADE CONSTRAINTS;

DROP TABLE bb_promo CASCADE CONSTRAINTS;

DROP TABLE bb_test1 CASCADE CONSTRAINTS;

DROP TABLE bb_test2 CASCADE CONSTRAINTS;

DROP TABLE bb_prod_sales CASCADE CONSTRAINTS;

DROP TABLE bb_shop_sales CASCADE CONSTRAINTS;

DROP TABLE bb_audit_logon CASCADE CONSTRAINTS;

DROP TABLE bb_product_request CASCADE CONSTRAINTS;

DROP TABLE bb_blocktest CASCADE CONSTRAINTS;

DROP TABLE bb_trans_log CASCADE CONSTRAINTS;

DROP SEQUENCE bb_prodid_seq;

DROP SEQUENCE bb_shopper_seq;

DROP SEQUENCE bb_poption_seq;

DROP SEQUENCE bb_idbasket_seq;

DROP SEQUENCE bb_idbasketitem_seq;

DROP SEQUENCE bb_status_seq;

DROP SEQUENCE bb_prodreq_seq;

CREATE TABLE BB_Department (

idDepartment number(2) ,

DeptName varchar2(25) ,

DeptDesc varchar2(100) ,

DeptImage varchar2(25) ,

CONSTRAINT dept_id_pk PRIMARY KEY(idDepartment) );

insert into bb_department

values(1,'Coffee','Many types of coffee beans','coffee.gif');

insert into bb_department

values(2,'Equipment and Supplies','Coffee makers to coffee filters

available','machines.gif');

insert into bb_department

values(3,'Coffee Club','Benefits of our club membership?','club.gif');

CREATE TABLE BB_Product (

idProduct number(2) ,

ProductName varchar2(25) ,

Description varchar2(100) ,

ProductImage varchar2(25),

Price number(6,2),

SaleStart date,

SaleEnd date,

SalePrice number(6,2),

Active number(1),

Featured number(1),

FeatureStart date,

FeatureEnd date,

Type char(1),

idDepartment number(2),

CONSTRAINT prod_id_pk PRIMARY KEY(idProduct),

CONSTRAINT prod_idDept_fk FOREIGN KEY (idDepartment)

REFERENCES BB_Department (idDepartment) );

insert into bb_product(idProduct, type, ProductName, Description,

ProductImage, Price, Active, idDepartment)

values(1,'E','CapressoBar Model #351', 'A fully programmable pump

espresso machine and 10-cup coffee maker complete with GoldTone filter',

'capresso.gif', 99.99, 1, 2);

insert into bb_product(idProduct, type, ProductName, Description,

ProductImage, Price, Active, idDepartment)

values(2,'E','Capresso Ultima', 'Coffee and Espresso and Cappuccino

Machine. Brews from one espresso to two six ounce cups of coffee',

'capresso2.gif', 129.99, 1, 2);

insert into bb_product(idProduct, Type, ProductName, Description,

ProductImage, Price, Active, idDepartment)

values(3,'E','Eileen 4-cup French Press', 'A unique coffeemaker from

those proud craftsmen in windy Normandy.', 'frepress.gif', 32.50, 1, 2);

insert into bb_product(idProduct, Type, ProductName, Description,

ProductImage, Price, Active, idDepartment)

values(4,'E','Coffee Grinder', 'Avoid blade grinders! This mill grinder

allows you to choose a fine grind to a coarse grind.', 'grind.gif', 28.50,

1, 2);

insert into bb_product(idProduct, Type, ProductName, Description,

ProductImage, Price, Active, idDepartment)

values(5,'C','Sumatra', 'Spicy and intense with herbal aroma. ',

'sumatra.jpg', 10.50, 1, 1);

insert into bb_product(idProduct, Type, ProductName, Description,

ProductImage, Price, Active, idDepartment)

values(6,'C','Guatamala', 'heavy body, spicy twist, aromatic and smokey

flavor.', 'Guatamala.jpg', 10.00, 1, 1);

insert into bb_product(idProduct, Type, ProductName, Description,

ProductImage, Price, Active, idDepartment)

values(7,'C','Columbia', 'dry, nutty flavor and smoothness',

'columbia.jpg', 10.80, 1, 1);

insert into bb_product(idProduct, Type, ProductName, Description,

ProductImage, Price, Active, idDepartment)

values(8,'C','Brazil', 'well-balanced mellow flavor, a medium body with

hints of cocoa and a mild, nut-like aftertaste', 'brazil.jpg', 10.80, 1,

1);

insert into bb_product(idProduct, Type, ProductName, Description,

ProductImage, Price, Active, idDepartment)

values(9,'C','Ethiopia', 'distinctive berry-like flavor and aroma,

reminds many of a fruity, memorable wine. ', 'ethiopia.jpg', 10.00, 1, 1);

insert into bb_product(idProduct, Type, ProductName, Description,

ProductImage, Price, Active, idDepartment)

values(10,'C','Espresso', 'dense, caramel-like sweetness with a soft

acidity. Roasted somewhat darker than traditional Italian.',

'espresso.jpg', 10.00, 1, 1);

CREATE SEQUENCE bb_prodid_seq start with 15;

Alter table bb_product

ADD (stock number(5,1),

ordered number(3),

reorder number(3) );

UPDATE bb_product

SET stock = 23, ordered = 0, reorder = 12

WHERE idproduct = 1;

UPDATE bb_product

SET stock = 15, ordered = 0, reorder = 9

WHERE idproduct = 2 ;

UPDATE bb_product

SET stock = 30, ordered = 0, reorder = 15

WHERE idproduct = 3 ;

UPDATE bb_product

SET stock = 26, ordered = 0, reorder = 25

WHERE idproduct = 4 ;

UPDATE bb_product

SET stock = 41, ordered = 0, reorder = 45

WHERE idproduct = 5 ;

UPDATE bb_product

SET stock = 42, ordered = 0, reorder = 35

WHERE idproduct = 6 ;

UPDATE bb_product

SET stock = 61, ordered = 0, reorder = 35

WHERE idproduct = 7 ;

UPDATE bb_product

SET stock = 53, ordered = 0, reorder = 35

WHERE idproduct = 8 ;

UPDATE bb_product

SET stock = 54 , ordered = 0, reorder = 35

WHERE idproduct = 9 ;

UPDATE bb_product

SET stock = 50, ordered = 50, reorder = 50

WHERE idproduct = 10 ;

UPDATE bb_product

set salestart = '01-JUN-2012', saleend = '15-JUN-2012', SalePrice = 8.00

WHERE idProduct = 6;

CREATE TABLE bb_Product_request

(idRequest NUMBER(4),

idproduct number(2),

dtrequest DATE DEFAULT SYSDATE,

dtrecd DATE,

cost number(6,2),

qty number(3),

idvender number(3),

CONSTRAINT prodreq_id_pk PRIMARY KEY (idrequest),

CONSTRAINT prodreq_idprod_fk FOREIGN KEY (idProduct)

REFERENCES bb_product (idproduct) );

CREATE TABLE BB_Shopper (

idShopper number(4),

FirstName varchar2(15),

LastName varchar2(20),

Address varchar2(40),

City varchar2(20),

State char(2),

ZipCode varchar2(15),

Phone varchar2(10),

Fax varchar2(10),

Email varchar2(25),

UserName varchar2(8),

Password varchar2(8),

Cookie number(4) DEFAULT 0,

dtEntered date DEFAULT SYSDATE,

Province varchar2(15),

Country varchar2(15),

CONSTRAINT shopper_id_pk PRIMARY KEY(idShopper) );

create sequence bb_shopper_seq

start with 30;

insert into bb_shopper

values (21, 'John', 'Carter', '21 Front St.', 'Raleigh',

'NC','54822', '9014317701', NULL, 'Crackjack@aol.com',

'Crackj',

'flyby', 1, '13-JAN-2012', NULL, 'USA');

insert into bb_shopper

values (22, 'Margaret', 'Somner', '287 Walnut Drive', 'Cheasapeake',

'VA','23321', '7574216559', NULL, 'MargS@infi.net', 'MaryS',

'pupper', 1, '03-FEB-2012', NULL, 'USA');

insert into bb_shopper

values (23, 'Kenny', 'Ratman', '1 Fun Lane', 'South Park',

'NC','54674', '9015680902', NULL, 'ratboy@msn.net', 'rat55',

'kile', 0, '26-JAN-2012', NULL, 'USA');

insert into bb_shopper

values (24, 'Camryn', 'Sonnie', '40162 Talamore', 'South Riding',

'VA','20152', '7035556868', NULL, 'kids2@xis.net', 'kids2',

'steel', 1, '19-MAR-2012', NULL, 'USA');

insert into bb_shopper

values (25, 'Scott', 'Savid', '11 Pine Grove', 'Hickory',

'VA','22954', '7578221010', NULL, 'scott1@odu.edu', 'fdwell',

'tweak', 1, '19-FEB-2012', NULL, 'USA');

insert into bb_shopper

values (26, 'Monica', 'Cast', '112 W. 4th', 'Greensburg',

'VA','27754', '7573217384', NULL, 'gma@earth.net', 'gma1',

'goofy', 1, '09-FEB-2012', NULL, 'USA');

insert into bb_shopper

values (27, 'Pete', 'Parker', '1 Queens', 'New York',

'NY','67233', '1013217384', NULL, 'spider@web.net', '',

'', 0, '14-FEB-2012', NULL, 'USA');

ALter table bb_shopper

ADD (promo CHAR(1));

CREATE TABLE bb_ProductOption (

idProductoption number(3),

idoption number(2),

idProduct number(2),

CONSTRAINT prodattr_id_pk PRIMARY KEY (idProductoption),

CONSTRAINT prodopt_prodid_fk FOREIGN KEY(idProduct) REFERENCES

BB_Product(idProduct) );

create sequence bb_poption_seq

start with 30;

CREATE TABLE bb_ProductOptionCategory (

idOptionCategory number(2),

CategoryName varchar2(25),

CONSTRAINT prodoptcat_id_pk PRIMARY KEY(idOptionCategory) );

CREATE TABLE bb_ProductOptionDetail (

idoption number(2),

OptionName varchar2(25),

idOptionCategory number(2),

CONSTRAINT prodoptdet_id_pk PRIMARY KEY (idOption),

CONSTRAINT prodoptdet_idoptcat_fk FOREIGN KEY(idOptionCategory)

REFERENCES bb_ProductOptionCategory(idOptionCategory) );

insert into bb_ProductOptionCategory(idOptionCategory, CategoryName)

values(1,'Size');

insert into bb_ProductOptionCategory(idOptionCategory, CategoryName)

values(2,'Form');

insert into bb_ProductOptionDetail(idOption, OptionName, idOptionCategory)

values(1,'1/2 LB.', 1);

insert into bb_ProductOptionDetail(idOption, OptionName, idOptionCategory)

values(2,'1 LB.', 1);

insert into bb_ProductOptionDetail(idOption, OptionName, idOptionCategory)

values(3,'Whole Bean', 2);

insert into bb_ProductOptionDetail(idOption, OptionName, idOptionCategory)

values(4,'Regular Grind', 2);