Chapter 8, Problem 4CAT

Program Plan Intro

a.

Stored procedures:

• When user expects to running a particular query often, user can expand total performance by saving the query in a file called a stored procedure.
  • The stored procedure is located on the server.
  • The DBMS compiles the stored procedure and creates an execution plan, which is the well-organized method of finding the results.
• It is a procedure which contains collection of procedural and SQL statements.

Syntax for stored procedure:

CREATE FUNCTION fun_name(argument IN data-type)RETRUN data-type[IS]

BEGIN

    PL/SQL statements;

    Return (value or expression);

END;

Explanation of Solution

Query to create stored procedure:

CREATE OR REPLACE PROCEDURE DISP_GUIDE (I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE) AS

I_LAST_NAME   GUIDE.LAST_NAME%TYPE;

I_FIRST_NAME  GUIDE.FIRST_NAME%TYPE;

BEGIN

SELECT LAST_NAME, FIRST_NAME

INTO I_LAST_NAME, I_FIRST_NAME

FROM GUIDE

WHERE GUIDE_NUM = I_GUIDE_NUM;

DBMS_OUTPUT.PUT_LINE(I_GUIDE_NUM);

DBMS_OUTPUT.PUT_LINE(RTRIM(I_FIRST_NAME)||' '||RTRIM(I_LAST_NAME));

END;

/

Explanation:

• The above query is used to create a procedure named “DISP_GUIDE” to select the records in the “GUIDE” table.
• Change the “GUIDE_NUM” into “I_GUIDE_NUM” and place the “LAST_NAME” and “FIRST_NAME” values into “I_LAST_NAME” and “I_FIRST_NAME”.
• After placing these values, display the “I_GUIDE_NUM” and “I_FIRST_NAME” and “I_LAST_NAME” with separated by a space from the “GUIDE” table.
• Once the stored procedure is created, it needs to be executed.

Query to view the guide number and its first name and last name of guide with space:

BEGIN

DISP_GUIDE ('GZ01');

END;

The above query is used to view the guide number and its first name and last name with separated by a space.

Sample Output

Output:

GZ01

Zach Gregory

Explanation of Solution

b.

Query to create stored procedure:

CREATE OR REPLACE PROCEDURE DISP_RESERVATION_INFO (I_RESERVATION_ID IN RESERVATION.RESERVATION_ID%TYPE) AS

I_NUM_PERSONS          RESERVATION.NUM_PERSONS%TYPE;

I_CUSTOMER_NUM CUSTOMER.CUSTOMER_NUM%TYPE;

I_LAST_NAME CUSTOMER.LAST_NAME%TYPE;

BEGIN

SELECT NUM_PERSONS, CUSTOMER.CUSTOMER_NUM, LAST_NAME

INTO I_NUM_PERSONS, I_CUSTOMER_NUM, I_LAST_NAME

FROM RESERVATION, CUSTOMER

WHERE RESERVATION.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM

AND RESERVATION_ID = I_RESERVATION_ID;

DBMS_OUTPUT.PUT_LINE(I_NUM_PERSONS);

DBMS_OUTPUT.PUT_LINE(I_CUSTOMER_NUM);

DBMS_OUTPUT.PUT_LINE(I_LAST_NAME);

END;

/

Explanation:

• The above query is used to create a procedure named “DISP_RESERVATION_INFO” to select the records in the “CUSTOMER” and “RESERVATION” tables.
• Change the “RESERVATION_ID” into “I_RESERVATION_ID” and place the “NUM_PERSONS”, “CUSTOMER_NUM”, and “LAST_NAME” values into “I_NUM_PERSONS”, “I_CUSTOMER_NUM”, and “I_LAST_NAME”.
• After placing these values, display the “I_NUM_PERSONS”, “I_CUSTOMER_NUM”, and “I_LAST_NAME” from the “CUSTOMER” and “RESERVATION” tables.
• Once the stored procedure is created, it needs to be executed.

Query to view the number of persons, customer number and customer last name:

BEGIN

DISP_RESERVATION_INFO (1600020);

END;

The above query is used to view the number of persons, customer number and customer last name for the reservation ID “1600020”.

Sample Output

Output:

2

124

Busa                        

Explanation of Solution

c.

Query to insert the value:

CREATE OR REPLACE PROCEDURE ADD_GUIDE

(I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE,

I_LAST_NAME IN GUIDE.LAST_NAME%TYPE,

I_FIRST_NAME IN GUIDE.FIRST_NAME%TYPE) AS

BEGIN

INSERT INTO GUIDE (GUIDE_NUM, LAST_NAME, FIRST_NAME)

VALUES

(I_GUIDE_NUM, I_LAST_NAME, I_FIRST_NAME);

END;

/

Explanation:

The above query is used to create a stored procedure named “ADD_GUIDE” to insert the new record in the “GUIDE” table. Once the stored procedure is created, it needs to be executed.

Query to execute the stored procedure:

BEGIN

        ADD_GUIDE ('QR01', 'John', 'Merry');

END;

After executing the above query, the new record is inserted into the table “GUIDE”.

Sample Output

Output:

Query to view the contents in “GUIDE” table is as follows:

SELECT * FROM GUIDE;

Screenshot of output

Explanation of Solution

d.

Query to update stored procedure:

CREATE OR REPLACE PROCEDURE CHANGE_GUIDE_LASTNAME

(I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE,

I_LAST_NAME  GUIDE.LAST_NAME%TYPE) AS

BEGIN

UPDATE GUIDE

SET LAST_NAME = I_LAST_NAME

WHERE GUIDE_NUM = I_GUIDE_NUM;

END;

/

Explanation:

The above query is used to create a stored procedure named “CHANGE_GUIDE_LASTNAME” to change the last name of the guide whose number is stored in “I_GUIDE_NUM” to the value presently found in “I_LAST_NAME”, it needs to be executed.

Executing the stored procedure:

The content of “GUIDE” table before creating the procedure is given below:

Query to view the contents in “GUIDE” table is as follows:

SELECT * FROM GUIDE;

Screenshot of output

Query to execute the stored procedure:

BEGIN

CHANGE_GUIDE_LASTNAME ('GZ01', 'Rose');

END;

/

After executing the above query, the last name is changed for the guide number “GZ01” in the “GUIDE” table.

Sample Output

Output:

Query to view the contents in “GUIDE” table is as follows:

SELECT * FROM GUIDE;

Screenshot of output

Explanation of Solution

e.

Query to delete the value:

CREATE OR REPLACE PROCEDURE DELETE_GUIDE_RECORD

(I_GUIDE_NUM IN GUIDE.GUIDE_NUM%TYPE) AS

BEGIN

DELETE

FROM GUIDE

WHERE GUIDE_NUM = I_GUIDE_NUM;

END;

/

Explanation:

• The above query is used to create a procedure named “DELETE_GUIDE_RECORD” to delete a record in the “GUIDE” table.
• Once the record is deleted, a procedure should create guide number as a parameter.
• Once the stored procedure is created, it needs to be executed.

Executing the stored procedure:

The content of “GUIDE” table before creating the procedure is given below:

Query to view the contents in “GUIDE” table is as follows:

SELECT * FROM GUIDE;

Screenshot of output

Query to execute the stored procedure:

BEGIN

        DELETE_GUIDE_RECORD ('QR01');

END;

The above query is used to delete the record of guide number ‘QR01’.

Sample Output

Output:

Query to view the contents in “GUIDE” table after deleting the guide number ‘QR01’ as follows:

SELECT * FROM GUIDE;

Screenshot of output