|
Create PL/SQL to populate table using
Notepad
Hands-On
introduction
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
stocks market value (portfolio_value) for
that traded date.
You write a
PL/SQL procedure to define an explicit
cursor to query all the customers, the trade
date, and their current stocks market value
for the traded date. Then the procedure
should be able to populate the customer
portfolio statistics into the �cust_stat�
table. Save the file in your folder as "cust_stat"
with the "sql" extension.
Go to �MS-DOS�
Change directory to your directory. Login to
�SQLPLUS� as "oracle" password "learning".
Then create the customer statistics table
and name it "cust_stat."
This table
will keep the customer last names, the
traded date, and the total stock market
value for the traded date.
SQL> CREATE
TABLE cust_stat
(customer_lname
VARCHAR2(20),
trade_date
DATE,
portfolio_value NUMBER(8,2));
Writing PL/SQL
using NOTEPAD
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. Use a sub-query
with a max(trade_date) function to guarantee
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 your folder as "cust_stat" with the "sql"
extension.
Creating a
PL/SQL procedure
CREATE OR
REPLACE PROCEDURE
(Notepad)
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;
/
Saving a
PL/SQL procedure
Go back to
�SQLPLUS.� Run the file.
SQL> @cust_stat
�/� command,
not only will compile the PL/SQL procedure
but also save it as �cust_stat_proc� in the
Oracle database server.
Query the
customer statistics table.
SQL> SELECT * FROM cust_stat;
Notice that
the table is empty, since the procedure was
not executed yet.
Running a
PL/SQL procedure
Then run the
procedure.
SQL> EXECUTE
cust_stat;
Testing the
PL/SQL procedure
Check to see
your table was populated. Query the customer
statistics table again.
SQL> SELECT * FROM cust_stat;
Here are the
customer portfolio values.
Exit �SQLPLUS�
and close the windows.
Questions:
Q: How do you
write a PL/SQL language using NOTEPAD?
Q: Create a
table to keep your customer�s portfolio
statistics and name it CUST_STAT. You should
populate into this table a customer last
name, his/her traded date, and total stock
market value for the traded date.
See the
following columns and datatypes:
customer_lname
VARCHAR2(20)
trade_date
DATE
portfolio_value NUMBER(8,2)
Q: Write a
stored procedure to populate the customer
statistics table. Declare a cursor to query
all the customer last names, the traded
date, and the total stock market value for
the traded date. Use a sub-query with a MAX
(trade_date) function to guarantee 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.
Q: Then run
your created procedure.
Q: Verify that
your table was populated. |
