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;

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

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

Sample Output

Output:

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

SELECT * FROM OWNER;

Screenshot of output

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

Sample Output

Output:

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

SELECT * FROM OWNER;

Screenshot of output

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.

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