A Guide to SQL
A Guide to SQL
9th Edition
ISBN: 9781337668880
Author: Pratt
Publisher: Cengage
bartleby

Concept explainers

Question
Book Icon
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;

Expert Solution
Check Mark

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”.

Expert Solution
Check Mark
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”.

Expert Solution
Check Mark
Sample Output

Output:

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

SELECT * FROM GUIDE;

Screenshot of output

A Guide to SQL, Chapter 8, Problem 4CAT , additional homework tip  1

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

A Guide to SQL, Chapter 8, Problem 4CAT , additional homework tip  2

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.

Expert Solution
Check Mark
Sample Output

Output:

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

SELECT * FROM GUIDE;

Screenshot of output

A Guide to SQL, Chapter 8, Problem 4CAT , additional homework tip  3

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

A Guide to SQL, Chapter 8, Problem 4CAT , additional homework tip  4

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’.

Expert Solution
Check Mark
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

A Guide to SQL, Chapter 8, Problem 4CAT , additional homework tip  5

Want to see more full solutions like this?

Subscribe now to access step-by-step solutions to millions of textbook problems written by subject matter experts!
Students have asked these similar questions
Of the five primary components of an information system (hardware, software, data, people, process), which do you think is the most important to the success of a business organization? Part A - Define each primary component of the information system. Part B - Include your perspective on why your selection is most important. Part C - Provide an example from your personal experience to support your answer.
Management Information Systems
Q2/find the transfer function C/R for the system shown in the figure Re ད
Knowledge Booster
Background pattern image
Computer Science
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
SEE MORE QUESTIONS
Recommended textbooks for you
  • Text book image
    A Guide to SQL
    Computer Science
    ISBN:9781111527273
    Author:Philip J. Pratt
    Publisher:Course Technology Ptr
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr