Project 3 1. Project 3 is about printing multiple records from DDI.LEDGER_VIEW u
ID: 3756451 • Letter: P
Question
Project 3
1. Project 3 is about printing multiple records from DDI.LEDGER_VIEW using a PL/SQL LOOP
2. SPOOL your output to c:CS4210project3spool.txt
3. Set up a record variable in the DECLARE section using %ROWTYPE
4. SELECT all the fields in DDI.LEDGER_VIEW where ROOMNUM is 107
5. Use DBMS__OUTPUT.PUT_LINE to print the values for each field for each record.
6. Compile and run the procedure.
Here is the coding part that goes with project 3 so that you can do the problem
REM The next command will fail on the first run 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;
Explanation / Answer
Hi,
Below is the stored procedure-
spool test.txt
set serveroutput on
create or replace PROCEDURE Test_Reg_View
(
p_RegID NUMBER DEFAULT -1
)
AS
v_RegID LEDGER_VIEW.RegID%TYPE;
v_RegDate LEDGER_VIEW.RegDate%TYPE;
v_LastName LEDGER_VIEW.LastName%TYPE;
v_FirstName LEDGER_VIEW.FirstName%TYPE;
v_AdultCnt LEDGER_VIEW.AdultCnt%TYPE;
v_ChildCnt LEDGER_VIEW.ChildCnt%TYPE;
v_RoomSize LEDGER_VIEW.RoomSize%TYPE;
v_RoomBedCnt LEDGER_VIEW.RoomBedCnt%TYPE;
v_RoomRate LEDGER_VIEW.RoomRate%TYPE;
BEGIN
SELECT RegID, RegDate, LastName, FirstName,
AdultCnt, ChildCnt, RoomNum,
RoomSize, RoomBedCnt, RoomRate
INTO v_RegID, v_RegDate, v_LastName, v_FirstName,v_AdultCnt,v_ChildCnt,v_RoomSize,v_RoomBedCnt,v_RoomRate
FROM DDI.LEDGER_VIEW
WHERE RegID = p_RegID;
DBMS_OUTPUT.PUT_LINE('RegID: ' || v_RegID);
DBMS_OUTPUT.PUT_LINE('RegDate : ' || v_RegDate);
DBMS_OUTPUT.PUT_LINE('LastName: ' || v_LastName);
DBMS_OUTPUT.PUT_LINE('FirstName: ' || v_FirstName);
DBMS_OUTPUT.PUT_LINE('AdultCnt: ' || v_AdultCnt);
DBMS_OUTPUT.PUT_LINE('ChildCnt: ' || v_ChildCnt);
DBMS_OUTPUT.PUT_LINE('RoomSize: ' || v_RoomSize);
DBMS_OUTPUT.PUT_LINE('RoomBedCnt: ' || v_RoomBedCnt);
DBMS_OUTPUT.PUT_LINE('RoomRate): ' || v_RoomRate);
END;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.