Concept explainers
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.
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”.
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”.
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.
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’.
Output:
Query to view the contents in “GUIDE” table after deleting the guide number ‘QR01’ as follows:
SELECT * FROM GUIDE;
Screenshot of output
Want to see more full solutions like this?
Chapter 8 Solutions
A Guide to SQL
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning