|
“You're alive. Do
something. The directive in life, the moral imperative was so
uncomplicated. It could be expressed in single words, not
complete sentences. It sounded like this: Look. Listen. Choose.
Act.” Barbara Hall, A Summons to New Orleans, 2000 |
Read
first then play the video:
PLS008(VIDEO)-Advanced
PL/SQL Introduction
PLS009(VIDEO)-Create
PL/SQL to populate table using Notepad
Section
1: Creating PL/SQL
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 the “iself”
folder as "cust_stat" with the "sql" extension.
Go to “MS-DOS” Change
directory to "iself." Login to “SQLPLUS” as "iself”
password “schooling." 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 the “iself”
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.
|
“To love deeply in
one direction makes us more loving in all others.” Anne-Sophie
Swetchine |
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.
|