Created
May 17, 2019 00:31
-
-
Save bobby5892/c026bd23e73eda76354188f48daa498b to your computer and use it in GitHub Desktop.
276 Week 7 Weds
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE | |
Cursor donors IS SELECT IDDONOR,FIRSTNAME,LASTNAME from Donor; | |
TYPE typeDonor IS RECORD( | |
IDDONOR donor.IDDONOR%TYPE, | |
FIRSTNAME donor.FIRSTNAME%TYPE, | |
LASTNAME donor.LASTNAME%TYPE | |
); | |
TYPE list_of_donors IS TABLE OF typeDonor INDEX BY PLS_INTEGER; | |
l_row PLS_INTEGER; | |
tableOfDonors list_of_donors; | |
current_donor typeDonor; | |
BEGIN | |
--Cursor Loop | |
for cur_donor IN donors | |
LOOP | |
current_donor.IDDONOR := cur_donor.IDDONOR; | |
current_donor.FIRSTNAME := cur_donor.FIRSTNAME; | |
current_donor.LASTNAME := cur_donor.LASTNAME; | |
-- tableOfDonors.EXTEND; | |
tableOfDonors (cur_donor.IDDONOR) := current_donor; | |
END LOOP; | |
-- Numeric Loop | |
l_row := tableOfDonors.FIRST; | |
while (l_row IS NOT NULL) | |
LOOP | |
--DBMS_OUTPUT.put_line(tableOfDonors(l_row).IDDONOR || ' ' || tableOfDonors(l_row).FIRSTNAME || ' ' || tableOfDonors(l_row).LASTNAME); | |
DBMS_OUTPUT.put_line( tableOfDonors(l_row).IDDONOR || ' ' || tableOfDonors(l_row).FIRSTNAME || ' ' || tableOfDonors(l_row).LASTNAME); | |
l_row := tableOfDonors.NEXT (l_row); | |
END LOOP; | |
END; | |
/*Create a procedure (or anonymous block) that will: | |
X Define a cursor to get the Donor IDs from the Donor table | |
XDefine a table TYPE and an associative array based on the Donor ID | |
XUse a cursor FOR loop to populate the table | |
XUse a numeric FOR loop to print out the Donor ID, the first name and last name | |
*/ | |
/* using midterm sql????? */ | |
DROP TABLE donor CASCADE CONSTRAINTS; | |
DROP TABLE project CASCADE CONSTRAINTS; | |
DROP TABLE status CASCADE CONSTRAINTS; | |
DROP TABLE pledge CASCADE CONSTRAINTS; | |
DROP TABLE payment CASCADE CONSTRAINTS; | |
CREATE TABLE Donor ( | |
idDonor number(4), | |
Firstname varchar2(15), | |
Lastname varchar2(30), | |
Typecode CHAR(1), | |
Street varchar2(40), | |
City varchar2(20), | |
State char(2), | |
Zip varchar2(9), | |
Phone varchar2(10), | |
Fax varchar2(10), | |
Email varchar2(25), | |
News char(1), | |
dtentered date DEFAULT SYSDATE, | |
CONSTRAINT donor_id_pk PRIMARY KEY(idDonor) ); | |
CREATE TABLE Project ( | |
idProj number(6), | |
Projname varchar2(60), | |
Projstartdate DATE, | |
Projenddate DATE, | |
Projfundgoal number(12,2), | |
ProjCoord varchar2(20), | |
CONSTRAINT project_id_pk PRIMARY KEY(idProj), | |
CONSTRAINT project_name_uk UNIQUE (Projname) ); | |
CREATE TABLE Status ( | |
idStatus number(2), | |
Statusdesc varchar2(15), | |
CONSTRAINT status_id_pk PRIMARY KEY(idStatus) ); | |
CREATE TABLE Pledge ( | |
idPledge number(5), | |
idDonor number(4), | |
Pledgedate DATE, | |
Pledgeamt number(8,2), | |
idProj number(5), | |
idStatus number(2), | |
Writeoff number(8,2), | |
paymonths number(3), | |
Campaign number(4), | |
Firstpledge char(1), | |
CONSTRAINT pledge_id_pk PRIMARY KEY(idPledge), | |
CONSTRAINT pledge_idDonor_fk FOREIGN KEY (idDonor) | |
REFERENCES donor (idDonor), | |
CONSTRAINT pledge_idProj_fk FOREIGN KEY (idProj) | |
REFERENCES project (idProj), | |
CONSTRAINT pledge_idStatus_fk FOREIGN KEY (idStatus) | |
REFERENCES status (idStatus)); | |
CREATE TABLE Payment ( | |
idPay number(6), | |
idPledge number(5), | |
Payamt number(8,2), | |
Paydate DATE, | |
Paymethod char(2), | |
CONSTRAINT payment_id_pk PRIMARY KEY(idPay), | |
CONSTRAINT pay_idpledge_fk FOREIGN KEY (idPledge) | |
REFERENCES pledge (idPledge) ); | |
INSERT INTO donor | |
VALUES (301, 'Mary', 'Treanor', 'I','243 main St.', 'Norfolk', 'VA','23510',NULL,NULL,'mtrea492@mdv.com','Y','01-SEP-2012'); | |
INSERT INTO donor | |
VALUES (302, 'Patrick', 'Lee', 'I','11 Hooper St.', 'Norfolk', 'VA','23510','7572115445',NULL,'pleeNorf@gmail.com','N','09-SEP-2012'); | |
INSERT INTO donor | |
VALUES (303, 'Terry', 'Venor', 'I','556 Loop Lane.', 'Chesapeake', 'VA','23320',NULL,NULL,'tervenr@drw.edu','Y','18-SEP-2012'); | |
INSERT INTO donor | |
VALUES (304, 'Sherry', 'Pane', 'I','Center Blvd.', 'Virginia Beach', 'VA','23455',NULL,NULL,'toppane@yahoo.com','Y','21-SEP-2012'); | |
INSERT INTO donor | |
VALUES (305, 'Thomas', 'Sheer', 'I','66 Train St.', 'Chesapeake', 'VA','23322','7579390022',NULL,'tls3488@sheer.com','Y','01-MAR-2013'); | |
INSERT INTO donor | |
VALUES (306, NULL, 'Coastal Developers', 'B','3667 Shore Dr.', 'Virginia Beach', 'VA','23450','8889220004',NULL,'coastVA@cdev.com','Y','30-SEP-2012'); | |
INSERT INTO donor | |
VALUES (307, NULL, 'VA Community Org', 'G','689 Bush Dr.', 'Norfolk', 'VA','23513','7578337467','7578337468','vacmorg@biz.com','Y','03-OCT-2012'); | |
INSERT INTO donor | |
VALUES (308, 'Betty', 'Konklin', 'I','11 Shark Ln.', 'Virginia Beach', 'VA','23455','7574550087',NULL,'shark11@cox.net','N','04-OCT-2012'); | |
INSERT INTO donor | |
VALUES (309, 'Jim', 'Tapp', 'I','200 Pine Tree Blvd.', 'Chesapeake', 'VA','23320','',NULL,'','N','08-OCT-2012'); | |
INSERT INTO donor | |
VALUES (310, NULL, 'Unique Dezigns', 'B','Connect Circle Unit 12', 'Chesapeake', 'VA','23320','7574442121',NULL,'UDezigns@cox.net','Y','11-SEP-2012'); | |
INSERT INTO project | |
VALUES (500,'Elders Assistance League', '01-SEP-2012','31-OCT-2012',15000,'Shawn Hasee'); | |
INSERT INTO project | |
VALUES (501,'Community food pantry #21 freezer equipment', '01-OCT-2012','31-DEC-2012',65000,'Shawn Hasee'); | |
INSERT INTO project | |
VALUES (502,'Lang Scholarship Fund', '01-JAN-2013','01-NOV-2013',100000,'Traci Brown'); | |
INSERT INTO project | |
VALUES (503,'Animal shelter Vet Connect Program', '01-DEC-2012','30-MAR-2013',25000,'Traci Brown'); | |
INSERT INTO project | |
VALUES (504,'Shelter Share Project 2013', '01-FEB-2013','31-JUL-2013',35000,'Traci Brown'); | |
INSERT INTO status | |
VALUES (10,'Open'); | |
INSERT INTO status | |
VALUES (20,'Complete'); | |
INSERT INTO status | |
VALUES (30,'Overdue'); | |
INSERT INTO status | |
VALUES (40,'Closed'); | |
INSERT INTO status | |
VALUES (50,'Hold'); | |
INSERT INTO pledge | |
VALUES (100,303,'18-SEP-2012',80,500,20,NULL,0,738,'Y'); | |
INSERT INTO pledge | |
VALUES (101,304,'21-SEP-2012',35,500,20,NULL,0,738,'Y'); | |
INSERT INTO pledge | |
VALUES (102,310,'01-OCT-2012',500,501,20,NULL,0,749,'Y'); | |
INSERT INTO pledge | |
VALUES (103,307,'03-OCT-2012',2000,501,20,NULL,0,749,'N'); | |
INSERT INTO pledge | |
VALUES (104,308,'04-OCT-2012',240,501,10,NULL,12,749,'Y'); | |
INSERT INTO pledge | |
VALUES (105,309,'08-OCT-2012',120,501,10,NULL,12,749,'Y'); | |
INSERT INTO pledge | |
VALUES (106,301,'12-OCT-2012',75,500,20,NULL,0,738,'N'); | |
INSERT INTO pledge | |
VALUES (107,302,'15-OCT-2012',1200,501,10,NULL,24,749,'Y'); | |
INSERT INTO pledge | |
VALUES (108,308,'20-JAN-2013',480,503,10,NULL,24,790,'N'); | |
INSERT INTO pledge | |
VALUES (109,301,'01-FEB-2013',360,503,10,NULL,12,790,'N'); | |
INSERT INTO pledge | |
VALUES (110,303,'01-MAR-2013',300,504,10,NULL,12,756,'N'); | |
INSERT INTO pledge | |
VALUES (111,306,'01-MAR-2013',1500,504,20,NULL,0,756,'Y'); | |
INSERT INTO pledge | |
VALUES (112,309,'16-MAR-2013',240,504,10,NULL,12,756,'N'); | |
INSERT INTO payment | |
VALUES (1425,100,80,'18-SEP-2012','CC'); | |
INSERT INTO payment | |
VALUES (1426,101,35,'21-SEP-2012','DC'); | |
INSERT INTO payment | |
VALUES (1427,102,500,'01-OCT-2012','CH'); | |
INSERT INTO payment | |
VALUES (1428,103,2000,'03-OCT-2012','CH'); | |
INSERT INTO payment | |
VALUES (1429,106,75,'12-OCT-2012','CC'); | |
INSERT INTO payment | |
VALUES (1430,104,20,'01-NOV-2012','CC'); | |
INSERT INTO payment | |
VALUES (1431,105,10,'01-NOV-2012','CC'); | |
INSERT INTO payment | |
VALUES (1432,107,50,'01-NOV-2012','CC'); | |
INSERT INTO payment | |
VALUES (1433,104,20,'01-DEC-2012','CC'); | |
INSERT INTO payment | |
VALUES (1434,105,10,'01-DEC-2012','CC'); | |
INSERT INTO payment | |
VALUES (1435,107,50,'01-DEC-2012','CC'); | |
INSERT INTO payment | |
VALUES (1436,104,20,'01-JAN-2013','CC'); | |
INSERT INTO payment | |
VALUES (1437,105,10,'01-JAN-2013','CC'); | |
INSERT INTO payment | |
VALUES (1438,107,50,'01-JAN-2013','CC'); | |
INSERT INTO payment | |
VALUES (1439,104,20,'01-FEB-2013','CC'); | |
INSERT INTO payment | |
VALUES (1440,105,10,'01-FEB-2013','CC'); | |
INSERT INTO payment | |
VALUES (1441,107,50,'01-FEB-2013','CC'); | |
INSERT INTO payment | |
VALUES (1442,108,20,'01-FEB-2013','CC'); | |
INSERT INTO payment | |
VALUES (1443,109,30,'01-FEB-2013','CC'); | |
INSERT INTO payment | |
VALUES (1444,104,20,'01-MAR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1445,105,10,'01-MAR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1446,107,50,'01-MAR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1447,108,20,'01-MAR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1448,109,30,'01-MAR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1449,110,25,'01-MAR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1450,111,1500,'01-MAR-2013','CH'); | |
INSERT INTO payment | |
VALUES (1451,104,20,'01-APR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1452,105,10,'01-APR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1453,107,50,'01-APR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1454,108,20,'01-APR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1455,109,30,'01-APR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1456,110,25,'01-APR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1457,112,20,'01-APR-2013','CC'); | |
INSERT INTO payment | |
VALUES (1458,104,20,'01-MAY-2013','CC'); | |
INSERT INTO payment | |
VALUES (1459,105,10,'01-MAY-2013','CC'); | |
INSERT INTO payment | |
VALUES (1460,107,50,'01-MAY-2013','CC'); | |
INSERT INTO payment | |
VALUES (1461,108,20,'01-MAY-2013','CC'); | |
INSERT INTO payment | |
VALUES (1462,109,30,'01-MAY-2013','CC'); | |
INSERT INTO payment | |
VALUES (1463,110,25,'01-MAY-2013','CC'); | |
INSERT INTO payment | |
VALUES (1464,112,20,'01-MAY-2013','CC'); | |
COMMIT; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment