Using MySQL....Please respond with the necessary statements Project 2 is about p

Question

Using MySQL....Please respond with the necessary statements

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

1.      SPOOL your output to c:CS4210project2spool.txt

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

Thank you

Explanation / Answer

Stored Procedure:

spool project2spool.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;

Feel free to reach out if you have any doubts.
Rate if the answer was helpful.
Thanks

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.