Oracle: PL/SQL



Lab.# 4 (Total score =approx. 240 Due date: 3/21/2006)

✌ For each question, include the question number, your answer (SQL program), and the result returned from Oracle.

✌ You need to make sure whether the result is right or not. Having response from Oracle does not necessarily mean that your programs works correctly.

1. Create a View answering the following questions: [4x9=36]

a. Create a view named vw_MyClinic. Vw_MyClinic consists of the columns clinicNo, city, state, and telNo.

b. Write a query to retrieve the clinicNo and city for every clinic in the view, vw_MyClinic.

c. Define a view named vw_MyPetOwner that consists of oFName, oLName, oCity, oState, and oTelNo.

d. Write a query to retrieve owner’s first name, last name, and telephone number in city “Fullerton”.

e. For the query used in the question d, what can be the actual SELECT command that DBMS will execute?

f. Insert your own pet owner record (with any values) into the PetOwner table. Can you see your record in the view vw_MyPetOwner (Yes/No)? If yes, show the record in the view.

g. Insert your own name, city, state, and telephone number into the view vw_MyPetOwner. Can you insert it? If you can't, explain why not?

h. Can you delete a record in the view vw_MyPetOwner? If you can’t, how can you delete the record?

i. Drop the view vw_MyPetOwner.

2. Create a sequence object using SEQUENCE command for the numbers such as 100, 102, 104, ……. and display the 3 next sequence numbers. Show one example of using sequence number for inserting a record in any table. [5]

3. The PerfectPets has used only ownerNo to search pet owner’s information in the PetOwner table. The company plans to build another index for the table. What column would you consider for the index? Justify your choice and create an index for the table based on the column of your choice. [5]

4. Create the following report using SQL*Plus report:

The title of the report should be “Current Stock for Clinic Items”. The report includes headings, “Clinic No”, “Item No”, “Item Name”, “Item Cost”, and “In Stock”. The report should include the detail data for each heading. The data must be sorted by clinic number. The “Item Cost” column should be displayed in currency format. If data for certain columns are too long, you need to properly truncate it to be fit in a row in your report. Print the total amount (sum of item cost in stock) of item for each clinic and the grand total of item amount for all clinics at the end of the report. [10]

5. Show the table structures for the USER_USERS and USER_SYS_PRIVS dictionary tables and briefly explain what information USER_SYS_PRIVS contains. Write the command to view all the usernames and user IDs in your account. [5]

6. Database Account and Security: [8]

Note that you may not be able to perform some commands for the following tasks in the Titan system. You can try these in your personal Oracle if you have after you logged in as a DBA. If you can’t perform some commands, you still need to write down the correct commands for the following tasks assuming you are DBA:

a. What are the commands to create a user, DBman. The user, DBman must be able to perform DDL commands as well as DML commands.

b. Give a minimum privilege to a user Scott (or your classmate) so that he/she can view the data in your Pet table in your account.

c. Revoke the privilege you gave to Scott's (or your classmate) on the Pet table.

7. Write a PL/SQL program to select a record with appNo = ‘a00005’ in the Appointment table and store it into an anchored type of record variable. (e.g., use %ROWTYPE to declare an anchored record variable). Display all the column values of the variable on the screen using DBMS_OUTPUT.PUT or PUT_LINE. [10]

8. Write a PL/SQL program that displays the clinic number and the amount of drug value for every clinic (e.g., a drug value is computed as drug cost * quantity in stock) [10]

9. Write a PL/SQL program that writes a report (on the screen) for pet registration summary as shown in the following format: [20]

Pets registered by season

Season: season_name Date reported: current_date

PetNo PetName PetType Clinic number

….

….

Number of pets registered in this season: subtotal

Season: season_name Date reported: current_date

PetNo PetName PetType Clinic number

….

….

Number of pets registered in this season: subtotal

Total pets registered: grand-total

--- end of report ---

The seaon_name can be determined by:

Spring: January ~ May, Summer: June ~ July, Fall: August ~ October, Winter: November ~ December

If there are no pets registered for a particular season, print a message like “no pets in this season”.

10. (a) Write a stored function, called sf_AllPetsInPen that returns the total number of pets currently staying in a pen. The function takes only one parameter, penNo. When we call this function with a proper penNo, the function will return the total number of pets currently staying in the given pen. Create a table tbl_PetSum(penNo, penCapacity, numberOfPets, penStatus, clinicNo). The numberOfPets is the total number of pets currently staying in the pen, which can be computed with sf_AllPetsInPen. (b) Write a stored procedure, sp_PopPetSum to populate the table tbl_PetSum using the function sf_AllPetsInPen. The procedure sp_PopPetSum takes no parameters. (c) Write a PL/SQL program to call the procedure. [20]

11. Create a PL/SQL package (specification and body) called pk_Perfectpets that consists of both sf_AllPetsInPen and sp_PopPetSum defined in the previous question. Create a PL/SQL program to test the package. [10]

12. Display the source code for the procedure, sp_PopPetSum using SELECT command? Drop the function, sf_AllPetsInPen? [10]

13. Create a table tbl_PharmHistory with the same structure as the Pharmacy table. Write a trigger tr_KeepPharmHistory for keeping only removed records from Pharmacy table. In other words, if someone updates/deletes a record in the Pharmacy table, the tbl_PharmHistory table keeps the old (or deleted) records. In that way, if you UNION the Pharmacy table and tbl_PharmHistory table, you will get a complete history of pharmacy data so far. Show the test results of your trigger. [15]

14. Create a table tbl_UnusualItem with the same structure as the Item table to collect unusual items. Create a trigger, tr_CatchUnusualItem that detects the unusual item entries whenever new items are inserted or existing items are updated, and put the unusual items in the tbl_UnusualItem table. We define an item as unusual if the cost of the item (itemCost) is greater than $80. Show the test results of your trigger. [15]

15. Write a trigger that automatically reorders item at the specified reorder quantity, if the the number of inStock items falls below the reorder level in the ItemClinicStock table. Define an additional table Current_Orders that stores all the pending orders which are identified by a unique order number. Additionally write two stored procedures:

• AddArrivingItemsFromOrderToStock() that updates the quantitities in the in ItemClinicStock table based on the arrival of the items ordered in order

• RemoveItemClinicStock(,, ) that removes the indicated item in the indicated quantity from the stock of the indicated clinic; if the specified quantity of the specified item is not available in the specified clinic, the procedure raises an examples.

Provide 4 examples that test the specified trigger and the two stored procedures that provide evidence that items are reordered correctly, and the two stored procedures interact properly with each other and the trigger, and errors are detected if the stored procedure is called incorrectly. [25]

Remember that the programming style (e.g., comment, good alignment or indentation of statement, meaningful and consistent variable definition, etc.) is important!!

-----------------------

System date

Title of report

Detail data

Subtotal of pets in this season

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download