Write PL/SQL a. Obtain the first name and last name, separated by a space, of th

Question

Write PL/SQL

a. Obtain the first name and last name, separated by a space, of the guide whose number currently is stored in I_GUIDE_NUM. Place these values in the variables I_FIRST_NAME and I_LAST_NAME. Output the contents of I_GUIDE_NUM, I_FIRST_NAME, and I_LAST_NAME.

b. Obtain the number of persons, customer number, and customer last name for every reservation whose ID currently is stored in I_RESERVATION_ID. Place these values in the variables I_NUM_PERSONS, I_CUSTOMER_NUM and I_ LAST_NAME, respectively. Output the contents of I_NUM_PERSONS, I_CUSTOMER_NUM and I_LAST_NAME.

c. Add a row to the GUIDE table.

d. Change the last name of the guide whose number is stored in I_GUIDE_NUM to the value currently found in I_LAST_NAME.

e. Delete the guide whose number is stored in I_GUIDE_NUM.

Explanation / Answer

a)

DECLARE

I_GUIDE_NUM   NUMBER := &guide_id;

I_FIRST_NAME VARCHAR2(35);

I_LAST_NAME VARCHAR2(35);

BEGIN

SELECT GUIDE_NUM, RTRIM(GUIDE_FIRST)+RTRIM(GUIDE_LAST)

INTO I_GUIDE_NUM , I_FIRST_NAME , I_LAST_NAME

FROM GUIDE

WHERE GUIDE_NUM= I_GUIDE_NUM   ;

DBMS_OUTPUT.PUT_LINE (I_GUIDE_NUM ||' '|| I_FIRST_NAM||' '|| I_LAST_NAME);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('There is no such guide');

END;

c)

DECLARE

I_GUIDE_NUM   NUMBER(20);

I_FIRST_NAME VARCHAR2(35);

I_LAST_NAME VARCHAR2(35);

BEGIN

INSERT INTO GUIDE VALUES( I_GUIDE_NUM , I_FIRST_NAME , I_LAST_NAME)   ;

DBMS_OUTPUT.PUT_LINE (I_GUIDE_NUM ||' '|| I_FIRST_NAM||' '|| I_LAST_NAME);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('There is no such guide');

END;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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