A Guide to SQL
A Guide to SQL
9th Edition
ISBN: 9781111527273
Author: Philip J. Pratt
Publisher: Course Technology Ptr
bartleby

Concept explainers

Question
Book Icon
Chapter 8, Problem 4SCG
Program Plan Intro

a.

Stored procedures:

  • A procedure is a collection of procedural and SQL statements.
  • A procedure may have input parameter or output parameter or sometimes both the parameters.
  • It is declared with a unique name with a unit of procedural code using the proprietary RDBMS and it is invoked by a host language library routine.

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_OWNER (I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE) AS

I_FIRST_NAME   OWNER.FIRST_NAME%TYPE;

I_LAST_NAME    OWNER.LAST_NAME%TYPE;

BEGIN

SELECT FIRST_NAME, LAST_NAME

INTO I_FIRST_NAME, I_LAST_NAME

FROM OWNER

WHERE OWNER_NUM = I_OWNER_NUM;

DBMS_OUTPUT.PUT_LINE(I_OWNER_NUM);

DBMS_OUTPUT.PUT_LINE(I_FIRST_NAME);

DBMS_OUTPUT.PUT_LINE(I_LAST_NAME);

END;

/

Explanation:

  • The above query is used to create a procedure named “DISP_OWNER” to select the records in the “OWNER” table.
  • Change the “OWNER_NUM” into “I_OWNER_NUM” and place the “FIRST_NAME” and “LAST_NAME” values into “I_FIRST_NAME” and “I_LAST_NAME”.
  • After placing these values, display the “I_OWNER_NUM”, “I_FIRST_NAME”, and “I_LAST_NAME” from the “OWNER” table.
  • Once the stored procedure is created, it needs to be executed.

Query to view the owner name and ID:

BEGIN

DISP_OWNER ('AN175');

END;

The above query is used to view the owner ID, first name, and last name for the number “AN175”.

Sample Output

Output:

AN175

Bill                    

Anderson 

Explanation of Solution

b.

Query to create stored procedure:

CREATE OR REPLACE PROCEDURE DISP_CONDO_UNIT

(I_CONDO_ID IN  CONDO_UNIT.CONDO_ID%TYPE) AS

I_LOCATION_NUM      CONDO_UNIT.LOCATION_NUM%TYPE;

I_UNIT_NUM        CONDO_UNIT.UNIT_NUM%TYPE;

I_OWNER_NUM       CONDO_UNIT.OWNER_NUM%TYPE;

I_FIRST_NAME      OWNER.FIRST_NAME%TYPE;

I_LAST_NAME   OWNER.LAST_NAME%TYPE;

BEGIN

SELECT LOCATION_NUM, UNIT_NUM, CONDO_UNIT.OWNER_NUM, FIRST_NAME, LAST_NAME

INTO I_LOCATION_NUM, I_UNIT_NUM, I_OWNER_NUM, I_FIRST_NAME, I_LAST_NAME

FROM CONDO_UNIT, OWNER

WHERE CONDO_UNIT.OWNER_NUM = OWNER.OWNER_NUM

AND CONDO_ID = I_CONDO_ID;

DBMS_OUTPUT.PUT_LINE(I_CONDO_ID);

DBMS_OUTPUT.PUT_LINE(I_LOCATION_NUM);

DBMS_OUTPUT.PUT_LINE(I_UNIT_NUM);

DBMS_OUTPUT.PUT_LINE(I_OWNER_NUM);

DBMS_OUTPUT.PUT_LINE(I_FIRST_NAME);

DBMS_OUTPUT.PUT_LINE(I_LAST_NAME);

END;

/

Explanation:

  • The above query is used to create a procedure named “DISP_CONDO_UNIT” to select the records in the “CONDO_UNIT” and “OWNER” tables.
  • Change the “CONDO_ID” into “I_CONDO_ID” and place the “LOCATION_NUM”, “UNIT_NUM”, “OWNER_NUM”, “FIRST_NAME”, and “LAST_NAME” values into “I_LOCATION_NUM”, “I_UNIT_NUM”, “I_OWNER_NUM”, “I_FIRST_NAME”, and “I_ LAST_NAME”.
  • After placing these values, display the “I_CONDO_ID”, I_LOCATION_NUM”, “I_UNIT_NUM”, “I_OWNER_NUM”, “I_FIRST_NAME”, and “I_ LAST_NAME” from the “CONDO_UNIT” and “OWNER” tables.
  • Once the stored procedure is created, it needs to be executed.

Query to view a result:

BEGIN

DISP_CONDO_UNIT(3);

END;

The above query is used to view the condo Id, location number, unit number, owner number, first name and last name for the condo id “3”.

Expert Solution
Check Mark
Sample Output

Output:

3

1

306

AN175

Bill                    

Anderson                

Explanation of Solution

c.

Query to insert the value:

CREATE OR REPLACE PROCEDURE ADD_OWNER

(I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE,

I_LAST_NAME IN OWNER.LAST_NAME%TYPE,

I_FIRST_NAME IN OWNER.FIRST_NAME%TYPE,

I_ADDRESS IN OWNER.ADDRESS%TYPE,

I_CITY IN OWNER.CITY%TYPE,

I_STATE IN OWNER.STATE%TYPE,

I_POSTAL_CODE IN OWNER.POSTAL_CODE%TYPE) AS

BEGIN

INSERT INTO OWNER (OWNER_NUM, LAST_NAME, FIRST_NAME, ADDRESS, CITY, STATE, POSTAL_CODE)

VALUES

(I_OWNER_NUM, I_LAST_NAME, I_FIRST_NAME, I_ADDRESS, I_CITY, I_STATE,

I_POSTAL_CODE);

END;

/

Explanation:

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

Query to execute the stored procedure:

BEGIN

  ADD_OWNER ('AZ245', 'William', 'John', '125 North St.', 'Mills', 'SC', 29707);

END;

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

Expert Solution
Check Mark
Sample Output

Output:

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

SELECT * FROM OWNER;

Screenshot of output

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

Explanation of Solution

d.

Query to update stored procedure:

CREATE OR REPLACE PROCEDURE CHG_OWNER

(I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE,

I_LAST_NAME IN OWNER.LAST_NAME%TYPE) AS

BEGIN

UPDATE OWNER

SET LAST_NAME = I_LAST_NAME

WHERE OWNER_NUM = I_OWNER_NUM;

END;

/

Explanation:

The above query is used to create a stored procedure named “CHG_OWNER” to update the last name of the owner whose number is stored in “I_OWNER_NUM” to the value presently found in “I_OWNER_NUM”, it needs to be executed.

Query to execute the stored procedure:

BEGIN

CHG_OWNER ('AZ245', 'Andrew');

END;

After executing the above query, the last name is changed in the table “OWNER”.

Expert Solution
Check Mark
Sample Output

Output:

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

SELECT * FROM OWNER;

Screenshot of output

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

Explanation of Solution

e.

Query to delete the value:

CREATE OR REPLACE PROCEDURE DELETE_OWNER

(I_OWNER_NUM IN OWNER.OWNER_NUM%TYPE) AS

BEGIN

DELETE

FROM OWNER

WHERE OWNER_NUM = I_OWNER_NUM;

END;

/

Explanation:

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

Query to execute the stored procedure:

BEGIN

        DELETE_OWNER ('AZ245');

END;

The above query is used to delete the order number AZ245.

Expert Solution
Check Mark
Sample Output

Output:

Query to view the contents in “OWNER” table after deleting the order number AZ245 as follows:

SELECT * FROM OWNER;

Screenshot of output

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

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
T-SQL procedure A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. B: obtain the order date, customer number and name for the order whose number currently is stored in I_ORDER_NUM. place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME , respectively. output the contents of I_ORDER_DATE, I_CUSTOMER_NUM , and I_CUSTOMER_NAME. C: add a row to the ORDERS table. D: change the date of the whole number is stored in I_ORDER_DATE to the date currently found in I_ORDER_DATE. E: delete the order whose number is stored in I_ORDER_NUM.
SQL Database   Write PL/SQL or T-SQL procedures to accomplish the following tasks: 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.  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. Add a row to the GUIDE table. Change the last name of the guide whose number is stored in I_GUIDE_NUM to the value currently found in I_LAST_NAME. Delete the guide whose number is stored in I_GUIDE_NUM
Write PL/SQL or T-SQL procedures to accomplish the following tasks: A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. B: obtain the order date, customer number and name for the order whose number currently is stored in I_ORDER_NUM. place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME , respectively. output the contents of I_ORDER_DATE, I_CUSTOMER_NUM , and I_CUSTOMER_NAME. C: add a row to the ORDERS table. D: change the date of the whole number is stored in I_ORDER_DATE to the date currently found in I_ORDER_DATE. E: delete the order whose number is stored in I_ORDER_NUM.
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
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
    Database Systems: Design, Implementation, & Manag...
    Computer Science
    ISBN:9781285196145
    Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
    Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning