SkillBuilders | Oracle 10g PL/SQL Programming | Lesson 3: PL/SQL and SQL Basics

Instructor's Notes

(If applicable)

Lesson 3: PL/SQL Programming Basics for Oracle 10g
An in-depth lesson on embedding simple SQL statements in PL/SQL programs, including:
Implicit Cursors
- SELECT INTO
- Implicit Cursor loops
DML
- INSERT, UPDATE and DELETE
Embedding DDL
- An Introduction to NDS

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Lesson 3 Topics

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The SELECT INTO statement is an efficient and programmer-friendly technique for retrieving a single row from a table. By “programmer-friendly” I mean that, for example, with this statement you cannot forget to close the cursor – PL/SQL does that for you. Note that a ORA-01427 exception is raised if more than one row is returned.
The SELECT INTO statement is really a type of implicit cursor. On behalf of the SELECT INTO statement, PL/SQL will create, open, fetch from and close the cursor.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

In this example, we declare three variables, one for each column that will be retrieved from the SELECT INTO. This is a requirement for SELECT INTO – you must declare and specify a variable for each column retrieved from the table(s). Notice how we use %TYPE to define the variable datatype instead of specifying a datatype such as NUMBER or VARCHAR2.
You’ll learn a lot more about exception handling later in this course. As an introduction, note how we “handle” the likely error if the customer is not found. The SELECT INTO statement will fail, causing automatic transfer of control to the EXCEPTION block. The NO_DATA_FOUND handler (NO_DATA_FOUND is a predefined exception name) will trap the exception – I use the RAE procedure to pass a meaningful error message to the client. In SQL*Plus, the error is reported like this:
declare
*
ERROR at line 1:
ORA-20000: Customer not found
ORA-06512: at line 14

Other Text:
(Examples or comments displayed on slide, if any).

SQL> declare
2 v_lastname customer.lastname%type;
3 v_area_code customer.area_code%type;
4 v_phone customer.phone%type;
5 begin
6 SELECT lastname, area_code, phone
7 INTO v_lastname, v_area_code, v_phone
8 FROM customer
9 WHERE cust_no = 1;
10 dbms_output.put_line(v_lastname || ' (' ||
11 v_area_code || ')' || v_phone);
12 exception
13 when no_data_found then
14 raise_application_error(-20000, 'Customer not found');
15 end;
16 /
JONES (212)221-4333

PL/SQL procedure successfully completed.

One variable for each column retrieved

Instructor's Notes

(If applicable)

The implicit cursor FOR LOOP is an efficient technique for retrieval of a small number (e.g. approximately 100) rows from a table. Later, you will learn to use array processing – an efficient technique for retrieval of many rows.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> begin
2 for x in (select lastname from customer) loop
3 dbms_output.put_line(x.lastname);
4 end loop;
5 END;
6 /
Jones
Smith
Anderson
Murdy
. . .

Instructor's Notes

(If applicable)

Workshop – Implicit Cursor Loop

Write and test a procedure that uses an implicit cursor loop to load an array with customer number and name. Use PUT_LINE to display the customer names as they are loaded into the array.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

PL/SQL allows seamless embedding of INSERT, UPDATE and DELETE SQL statements. All three statements support an optional RETURNING clause, which returns the value(s) from the specified columns of the affected row(s). (Note: if more than one row is affected, you will need to incorporate the use of PL/SQL arrays which you will learn later in this course.)
When might it be useful to return values with an INSERT? Well, perhaps a trigger has been created which alters inserted values. For example:
create or replace trigger phone_book
before insert on phone_book
for each row
begin
:new.lastname := upper(:new.lastname);
:new.firstname := upper(:new.firstname);
end;

Refer to the supplied script PHONE_BOOK.SQL for a working copy of this code.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create or replace procedure phone_book_load
2 as
3 v_lastname customer.lastname%type;
4 v_firstname customer.firstname%type;
5 begin
6 for x in (select lastname,firstname, phone
7 from customer) loop
8 insert into phone_book
9 values(x.lastname, x.firstname, x.phone)
10 returning lastname, firstname
11 into v_lastname, v_firstname;
12 dbms_output.put_line(v_lastname);
13 end loop;
14 end;
15 /

Procedure created.

SQL> exec phone_book_load
JONES
SMITH
ANDERSON
MURDY

Instructor's Notes

(If applicable)

Cursor attributes can be used to determine the effect of your DML statements. For implicit cursor operations, preface the attribute name with “SQL”.
Attribute List

SQL%NOTFOUND
SQL%FOUND
SQL%ROWCOUNT

Other Text:
(Examples or comments displayed on slide, if any).

update customer
set discount = v_discount
where cust_no = v_cust_no;

if sql%rowcount = 0 then
/* some processing */
end if;
. . .

Instructor's Notes

(If applicable)

As I mentioned earlier in this lesson, you cannot directly code DDL (CREATE, DROP, GRANT, etc) within a PL/SQL program. DDL must be executed dynamically. One simple technique – shown here – is to use Native Dynamic SQL (NDS). NDS is invoked with the EXECUTE IMMEDIATE or OPEN FOR statements.
This example uses the EXECUTE IMMEDIATE statement to create a simple test table. An interesting thing about this example is the use of the pragma AUTHID CURRENT USER to make this an “invoker rights” procedure. In other words, the CREATE TABLE statement will create a table in the schema of the invoker – not the creator (definer) of the procedure. So if I grant privileges for user SCOTT to execute the procedure:
grant execute on create_table to scott;
and login to SCOTT and execute the procedure, SCOTT has a new table called TEST (not DAVE):
SQL> connect scott/tiger@class2
Connected.
SQL> exec dave.create_table

PL/SQL procedure successfully completed.

SQL> desc test
Name Null? Type
----------------------- -------- ------
C1 NUMBER

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create or replace procedure create_table
2 authid current_user as
3 begin
4 execute immediate 'create table test(c1 number)';
5 end;
6 /

Procedure created.

SQL> exec create_table

PL/SQL procedure successfully completed.

Instructor's Notes

(If applicable)

Workshop – Native Dynamic SQL (NDS)

Write and test a procedure called “GRANT_IT” that accepts a username and grants the privilege to execute the procedure you created in the previous workshop (Implicit Cursor Loops).
Embed the GRANT EXECUTE ON proc1 TO p_username statement in this procedure.
Grant a fellow student the privilege to execute this procedure.

Ask that student to execute the GRANT_IT procedure, passing their own username as the parameter.
Then ask the student to test the outcome by attempting to execute the procedure you wrote in the previous workshop.

Other Text:
(Examples or comments displayed on slide, if any).