Project 2 is about printing variables for a single record from DDI.LEDGER_VIEW using PL/SQL

1. SPOOL your output

2. Set up variables in the DECLARE section. Use either %TYPE or %ROWTYPE

3. SELECT all the fields in DDI.LEDGER_VIEW where REGID is to be determined at run time.

4. Use DBMS__OUTPUT.OUTPUT_LINE to print the values for each field.

5. Compile and run the procedure for the REGID 17

Script for DDI user tables:

DROP USER DDI CASCADE; CREATE USER DDI PROFILE DEFAULT IDENTIFIED BY oracle DEFAULT TABLESPACE USERS ACCOUNT UNLOCK; GRANT CONNECT TO DDI; GRANT RESOURCE TO DDI; CREATE TABLE DDI.ROOMS ( RoomNum NUMBER NOT NULL, RoomSize VARCHAR2(6) NOT NULL, RoomBedCnt NUMBER NOT NULL, RoomRate NUMBER(18,2) NOT NULL, PRIMARY KEY (RoomNum)) TABLESPACE USERS; REM INSERTING into DDI.ROOMS Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (101,'Double',1,44); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (102,'Double',2,49); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (103,'Queen ',2,61); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (104,'Queen ',2,61); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (105,'Queen ',2,61); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (106,'King ',1,61); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (107,'King ',1,61); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (108,'King ',1,61); CREATE TABLE DDI.PATRONS ( PatronID NUMBER NOT NULL, FirstName VARCHAR2(16) NOT NULL, LastName VARCHAR2(16) NOT NULL, PhoneNum VARCHAR2(12) Null, eMail VARCHAR2(100) Null, PRIMARY KEY (PatronID)) TABLESPACE USERS; REM INSERTING into DDI.PATRONS Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (1,'Bill','Tulsa','555-485-8356','Bill.Tulsa@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (2,'Chet','Travis','555-972-3076','Chet.Travis@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (3,'Chip','Marino','555-933-2815','Chip.Marino@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (4,'Dag','Renborn','555-595-6240','Dag.Renborn@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (5,'Darryl','Princeton','555-150-3607','Darryl.Princeton@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (6,'Donna','Smith','555-647-4949','Donna.Smith@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (7,'Donna','Smith','555-708-3498','Donna.Smith@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (8,'Douglas','Fischer','555-664-2731','Douglas.Fischer@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (9,'Elizabeth','Stanley','555-187-9958','Elizabeth.Stanley@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (10,'Eric','Jones','555-780-1222','Eric.Jones@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (11,'Eric','Parks','555-752-6266','Eric.Parks@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (12,'Fred','Price','555-176-4902','Fred.Price@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (13,'George','Berkely','555-662-3565','George.Berkely@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (14,'Hilary','Evans','555-860-5908','Hilary.Evans@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (15,'James','Thompson','555-243-8344','James.Thompson@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (16,'Jason','Tully','555-172-8639','Jason.Tully@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (17,'John','Watson','555-931-6794','John.Watson@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (18,'Kelly','Oxford','555-208-5106','Kelly.Oxford@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (19,'Linda','Becky','555-761-8562','Linda.Becky@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (20,'Lisa','Brisbon','555-115-5534','Lisa.Brisbon@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (21,'Liz','Frier','555-141-4440','Liz.Frier@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (22,'Marsha','Downs','555-248-7905','Marsha.Downs@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (23,'Marsha','Downs','555-346-5630','Marsha.Downs@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (24,'Mary','Harvard','555-558-2864','Mary.Harvard@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (25,'Matthew','Andrews','555-957-378','Matthew.Andrews@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (26,'Matthew','Andrews','555-957-3780','Matthew.Andrews@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (27,'Merle','Atkins','555-666-1794','Merle.Atkins@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (28,'Mike','Sailors','555-434-2463','Mike.Sailors@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (29,'Miles','Trent','555-971-2236','Miles.Trent@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (30,'Pamela','Birch','555-139-1378','Pamela.Birch@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (31,'Richard','James','555-455-5163','Richard.James@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (32,'Richard','James','555-815-4831','Richard.James@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (33,'Richard','James','555-906-6780','Richard.James@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (34,'Richardo','Romez','555-875-7101','Richardo.Romez@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (35,'Sally','Smith','555-329-4189','Sally.Smith@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (36,'Samantha','Jackson','555-632-7417','Samantha.Jackson@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (37,'Terry','Hwang','555-119-9217','Terry.Hwang@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (38,'Tzu','Lai','555-986-2353','Tzu.Lai@ourcampus.edu'); CREATE TABLE DDI.REGISTRATIONS ( RegID NUMBER NOT NULL, RegDate DATE NOT NULL, PatronID NUMBER NOT NULL, AdultCnt NUMBER Null, ChildCnt NUMBER Null, RoomNum NUMBER NOT NULL, RegNote VARCHAR2(100) Null, PRIMARY KEY (RegID), FOREIGN KEY (PatronID) REFERENCES DDI.PATRONS, FOREIGN KEY (RoomNum) REFERENCES DDI.ROOMS) TABLESPACE USERS; REM INSERTING into DDI.REGISTRATIONS Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (1,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,2,0,104,'On vacation'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (2,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,0,2,103,'On vacation'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (3,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),2,2,0,107,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (4,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),3,2,0,108,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (5,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (6,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),5,1,0,106,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (7,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),6,2,1,105,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (8,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),7,2,0,102,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (9,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,2,0,104,'On vacation'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (10,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,0,2,103,'On vacation'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (11,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),8,1,1,105,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (12,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),9,2,0,106,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (13,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),10,2,0,107,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (14,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),11,2,1,108,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (15,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,2,0,104,'On vacation'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (16,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,0,2,103,'On vacation'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (17,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),12,2,0,102,'Early arrival'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (18,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),13,2,0,105,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (19,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),14,1,0,106,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (20,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (21,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),15,2,1,103,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (22,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),16,2,0,104,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (23,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),17,2,1,106,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (24,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),18,1,0,107,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (25,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (26,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),19,2,0,106,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (27,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),20,1,0,107,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (28,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),21,2,0,103,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (29,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),22,2,1,104,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (30,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),23,2,0,108,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (31,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),24,2,0,105,'Early arrival'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (32,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),25,2,0,106,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (33,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),26,1,0,101,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (34,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),27,2,0,107,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (35,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),28,1,0,108,'Different Donna Smith'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (36,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (37,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),29,2,0,103,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (38,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),30,2,0,104,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (39,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),31,1,0,106,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (40,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),32,2,1,105,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (41,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),33,2,0,107,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (42,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),34,1,0,108,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (43,to_timestamp('08-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),32,2,0,102,'Early arrival'); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (44,to_timestamp('08-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),30,2,0,103,null); Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (45,to_timestamp('08-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),23,2,0,106,null); CREATE VIEW DDI.LEDGER_VIEW AS SELECT REG.RegID, REG.RegDate, P.LastName, P.FirstName, REG.AdultCnt, REG.ChildCnt, REG.RoomNum, RM.RoomSize, RM.RoomBedCnt, RM.RoomRate FROM DDI.ROOMS RM, DDI.PATRONS P, DDI.REGISTRATIONS REG WHERE REG.PatronID = P.PatronID AND REG.RoomNum = RM.RoomNum ORDER BY REG.RegDate, REG.RoomNum;