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
- Please original work select a topic related to architectures or infrastructures (Data Lakehouse Architecture). Discussing how you would implement your chosen topic in a data warehouse project Please cite in text references and add weblinksarrow_forwardPlease original work What topic would be related to architectures or infrastructures. How you would implement your chosen topic in a data warehouse project. Please cite in text references and add weblinksarrow_forwardWhat is cloud computing and why do we use it? Give one of your friends with your answer.arrow_forward
- What are triggers and how do you invoke them on demand? Give one reference with your answer.arrow_forwardDiscuss with appropriate examples the types of relationships in a database. Give one reference with your answer.arrow_forwardDetermine the velocity error constant (k,) for the system shown. + R(s)- K G(s) where: K=1.6 A(s+B) G(s) = as²+bs C(s) where: A 14, B =3, a =6. and b =10arrow_forward
- • Solve the problem (pls refer to the inserted image)arrow_forwardWrite .php file that saves car booking and displays feedback. There are 2 buttons, which are <Book it> <Select a date>. <Select a date> button gets an input from the user, start date and an end date. Book it button can be pressed only if the start date and ending date are chosen by the user. If successful, it books cars for specific dates, with bookings saved. Booking should be in the .json file which contains all the bookings, and have the following information: Start Date. End Date. User Email. Car ID. If the car is already booked for the selected period, a failure message should be displayed, along with a button to return to the homepage. In the booking.json file, if the Car ID and start date and end date matches, it fails Use AJAX: Save bookings and display feedback without page refresh, using a custom modal (not alert).arrow_forwardWrite .php file with the html that saves car booking and displays feedback. Booking should be in the .json file which contains all the bookings, and have the following information: Start Date. End Date. User Email. Car ID. There are 2 buttons, which are <Book it> <Select a date> Book it button can be pressed only if the start date and ending date are chosen by the user. If successful, book cars for specific dates, with bookings saved. If the car is already booked for the selected period, a failure message should be displayed, along with a button to return to the homepage. Use AJAX: Save bookings and display feedback without page refresh, using a custom modal (not alert). And then add an additional feature that only free dates are selectable (e.g., calendar view).arrow_forward
- • Solve the problem (pls refer to the inserted image) and create line graph.arrow_forwardwho started the world wide webarrow_forwardQuestion No 1: (Topic: Systems for collaboration and social business The information systems function in business) How does Porter's competitive forces model help companies develop competitive strategies using information systems? • List and describe four competitive strategies enabled by information systems that firms can pursue. • Describe how information systems can support each of these competitive strategies and give examples.arrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr