Lab 7: PL/SQL and Triggers
PL/SQL:
- Write a PL/SQL program to produce a chart giving conversions from grams
to ounces (one gram is .035 ounces). The chart should start at 100
grams and increase in steps of 50 to 500. The output should appear
as shown below:
GRAMS
OUNCES
100
3.50
150
5.25
200
7.00
.
.
500
17.50
- Write a PL/SQL program that will display the manager name and cash
for a given portfolio. The portfolio number should be passed as a parameter.
Your output should appear as:
Portfolio Number:
XXXX
Manager Name:
XXXXXXXX
Cash:
99999.99
- Write a PL/SQL program that will display the holding details - stock
symbol, purchase date, number of shares and purchase price - for a portfolio
number passed as a parameter.
Triggers:
For questions 1 and 2, you will need to edit the script files to change the
column names to correspond to the column names in your PORTFOLIO and HOLDING
tables.
- Run the script file check-cash.sql
to create the CHECK_CASH trigger.
- Run the script file set-price.sql
to create the SET_PRICE trigger.
- Insert the following row into the HOLDING table:
insert into holding values ('P005','JFM','24-Oct-99',500,15,holdseq.nextval)
What value is inserted for the stock price in the inserted row? Has
the corresponding entry in the PORTFOLIO table been updated?
- Insert the following row into the HOLDING table:
insert
into holding values ('P005','JFM','24-Oct-99',2500,10,holdseq.nextval);
What happens?
- Add a new column to the Portfolio table called NMBRHOLDINGS that will
contain the number of holdings currently in the portfolio. Set the
entry for portfolio number P004 to 2.
Create a trigger on the HOLDING table that will add 1 to the appropriate
value in NMBRHOLDINGS when a row is inserted into the HOLDING table.
Test the trigger by adding the following row to the HOLDING table:
'P004','COS','24-Oct-99',100,4.9,holdseq.nextval