Topics: Hands-On 01 – Create PL/SQL Procedure
In this Hands-On,
you create a table called "cust_stat." This table keeps the customers last
name (customer_lname), the traded date (trade_date), and the total stock
market value (portfolio_value) for that traded date.
Write a PL/SQL
procedure to define an explicit cursor to query all the customers, the trade
date, and their current stock market value for the traded date. Then populate
the customer portfolio statistics into the “cust_stat” table.
Save the file in
the “iself” folder as "cust_stat" with the "sql" extension.
You will:
1- Create a
table named “cust_stat.”
2- Write a
stored procedure using notepad to populate customers _statistics.
3- Run the
stored procedure.
4- Test the
stored procedure.
|
More Resources by
Google: |
|
|
|
|
Go to “MS-DOS”
Change directory to
"iself."
=
Login to “SQLPLUS”
as "iself” password “schooling."
=
Create the customer
statistics table and name it "cust_stat."
It keeps the
customer last names, the traded date, and the total stock market value for the
traded date.
=
create table cust_stat
(customer_lname
varchar2(20),
trade_date date,
portfolio_value number(8,2));
Then open the
“Notepad” editor.
Write a stored
procedure to populate the customer statistics table.
Use the “create or
replace” statement for the named block.
Declare a cursor to
query all the customer last names, the traded date, and the total stock market
value for the traded date.
This sub-query for
max(trade_date) guaranties the current stock market value for the traded date.
In the PL/SQL body,
use the “FOR LOOP” statement to read the cursor information one record at a
time. Then insert the summary statistics data into the customer statistics
table.
Use “commit” to
save the transaction.
In the exception
section, add the “no data found” exception and use the “dbms_output” package to
display the error message.
Add the “invalid
number” exception to detect any invalid input data into the insert command.
Add the “Others”
exception to detect other problems.
Always use the “others” exception in case you miss some other exceptions.
Save the file in
the “iself” folder as "cust_stat" with the "sql" extension.
create or replace procedure cust_stat_proc
is
-- define cursor
cursor c_cs is
select last_name, trade_date,
sum(shares_owned*current_price)
portfolio_value
from customers,
portfolio, stocks s
where id = customer_id
and stock_symbol = symbol
and trade_date =
(select max(trade_date) from
stocks
where symbol = s.symbol)
group by last_name,
trade_date;
begin
for v_cs in c_cs loop
- insert into cust_stat
insert into cust_stat
values (v_cs.last_name,
v_cs.trade_date,
v_cs.portfolio_value);
-- save the insert
transaction.
commit;
end loop;
exception
-- no data found
when no_data_found then
dbms_output.put_line(‘No data
found.’);
when invalie_number then
dbsm_output.put_line(‘Invalid number’);
when others then
dbsm_output.put_line(‘Other problem.’);
end;
/
Go back to “SQLPLUS.”
Run the file.
>> @cust_stat
The procedure was
created.
Query the customer
statistics table.
>> select * from cust_stat;
Then run the
procedure.
>> execute cust_stat;
Query the customer
statistics table again.
>> select * from cust_stat;
Here are the
customer portfolio values.
Exit “SQLPLUS” and
close the windows.
Now, you should
practice this over and over, until you become a master at it.
Good Luck.
|