logo for smart-soft-nz.com
[?] Subscribe To This Site

XML RSS
Add to Google
Add to My Yahoo!
Add to My MSN
Subscribe with Bloglines


Home
Smartsoft Blog
Oracle Training
Oracle 9i
Oracle 10g
Oracle 11g
Oracle App Server
Oracle Utilities
Backup and Recovery
Oracle Certification
Oracle Tutorials
PL/SQL
Free SQL Tutorial
SQL
News
Oracle Resources
Oracle Secrets Signup
Oracle and .Net
Contact Us
leftimage for smart-soft-nz.com

PLSQL Examples

The PLSQL examples presented here have been selected to illustrate the use of a few specific features that enhance the flexibility of your PLSQL code and make life easier for you as a developer.

Anchoring Variable Declarations In PLSQL

The first of our PLSQL examples that we'll look at shows you how to anchor PLSQL variable declarations to their corresponding data structures in your Oracle database.

Let's assume we have a table called emp in our Oracle database with the following structure:

emp (
     id         number,
     first_name varchar2(5),
     last_name  varchar2(30),
     dept_id    number
       )

Now let's create an anonymous block in PLSQL to read from the table and store the results in local variables. Our declarations would like this:

1  declare
2
3  type emp_rec is record(
4                        first_name varchar2(10),
5                        last_name  varchar2(30),
6                        dept_id number
7                        );
8
9  employee emp_rec;
10
11 first varchar2(10);
12 last  varchar2(30);
13
14   begin
15
16     select first_name
17           ,last_name
18           ,dept_id
19     into employee from emp
20     where rownum < 2;
21
22     select first_name,last_name
23     into first,last from emp
24     where rownum < 2;
25
26     dbms_output.put_line(employee.first_name|| 
27                       ' '||employee.last_name);
28
29 end;
 /

This is a straightforward example - we've created 3 local variables (one a record type) to hold data from the employee table.

As you can see declaring a record makes the select statement shorter but it means we need to prefix the individual fields with the record name to access them as shown on lines 26-27. Also the types and lengths of the PL/SQL variables match those of the table columns exactly.

What happens though when we decide that the column definitions in our Oracle table are too short or we want to change dept_id to a character string?

Now we're in trouble. We have to go through all our PLSQL code and change every declaration that's using this table.

Alternatively we could make our PLSQL code bullet-proof to start with by anchoring our type definitions to the Oracle database definitions as in the next of our PLSQL examples.

1  declare
2
3  cursor emp_cur is
4    select first_name,last_name,dept_id from emp;
5
6  employee emp_cur%rowtype;
7
8  first emp.first_name%type;
9  last  emp.last_name%type
10
11 begin
12
13     select first_name
14           ,last_name
15           ,dept_id
16     into employee from emp
17     where rownum < 2;
18
19     select first_name,last_name
20     into first,last from emp
21     where rownum < 2;
22
23     dbms_output.put_line(employee.first_name||
24                       ' '||employee.last_name);
25
26 end;
/

This example shows that we can anchor the definitions to individual columns or to the result of a cursor. We could also have kept our original record definition and anchored the record's individual fields to the column types in Oracle.

Anchoring your variable declarations like this protects you from changes to the table definitions in your Oracle database. It also simplifies your code and saves you time as you don't have to check exactly what datatype your PLSQL variables need.

PLSQL Cursor For Loops

The next example demonstrates another great time saver for developers. When you use cursor for loops all the hard work of opening, closing and fetching cursors is done for you by Oracle.

Let's demonstrate this using our emp table again. In our previous PLSQL examples we could only handle 1 row from the table because we were using an implicit cursor. To process more than one row in the table we need to use an explicit cursor.

One way (the hard way) to do this is show in this example:

DECLARE
    CURSOR emp_cur IS
            SELECT first_name,last_name FROM emp;

    emp_rec emp_cur%ROWTYPE;

BEGIN

    OPEN emp_cur;

    LOOP
        FETCH emp_cur INTO emp_rec;
        EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(
           'first name = '|| emp_rec.first_name||
           ',last name = '|| emp_rec.last_name);
    END LOOP;

    CLOSE emp_cur;

END;

On the other hand, the last of our PLSQL examples shows the easy way to do this:

BEGIN

    FOR emp_rec IN (SELECT first_name,last_name FROM emp) LOOP
        DBMS_OUTPUT.PUT_LINE(
             'first name = '|| emp_rec.first_name||
             ', last name =  ' || emp_rec.last_name);
    END LOOP;

END;

That's it! No cursor declaration, no record declaration, no opening/closing/fetching of the cursor, it's all done for you by Oracle. There's much less code to write and what remains is much more elegant.

The only disadvantage of using cursor for loops like this in PL/SQL is that if the query returns no rows the body of the loop will not be executed, so you may need extra logic to cater for this.

footer for Oracle page