iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 07

“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:

   PLS-VIDEO -Advanced PL/SQL Introduction
   PLS-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.