Introduction to PL/SQL

PL/SQL is a procedural language that can use a step through records approach to handle processing. It implements IF statements and loops, variables and types, procedures and functions while still allowing for traditional query and maintenance type processing through SQL. The procedural constructions available in PL/SQL add tremendous power to the processing capabilities available with the Oracle relational database.

PL/SQL is written in a block structure where each block performs a logical task. Blocks can be nested within each other. The structure of a block is:

DECLARE (optional): Declarative section - allows for variables, types, cursors, user defined-exceptions etc
BEGIN (required): Executable section - PL/SQL procedural statements and SQL statements
EXCEPTION (optional): Exception section - error and exception handling
END; (required)

Example:

In this example, I declared a message called v_msg in the declarative section. Then I assigned a literal to v_msg. The assign is done using the assignment code :=. This means that the data to the right of the assignment sign will be assigned to the dataname on the left of the assignment sign.This ended the PL/SQL block.

Looking at the code below, the edit afst_intro takes me into the editor where I coded the PL/SQL code. When I had completed the code and saved it, I exited the editor and ran the code. This can be done with START followed by the name of the code or @ followed by the name of the code. In this case it would be START afst_intro or @ afst_intro.

SQL CODE:

SQL> EDIT afst_intro

PL/SQL CODE:

DECLARE
  v_msg VARCHAR2(25);
BEGIN
  v_msg :='This PL/SQL block works!';
END;
/

SQL CODE:

SQL> @ afst_intro

PL/SQL procedure successfully completed.

If I want to be able to see the message and assure myself that it works, I can use the following code to show the output. If you set serveroutput on in SQL*PLUS (notice you also set it off) then you can display output on the screen using dbms_output.put_line and enclosing the information you want to see within quotes. In the example below, I have a literal message followed by the contents of a PL/SQL defined variable.

SQL CODE:

SQL> edit afst1_intro

PL/SQL CODE:

SET SERVEROUTPUT ON
DECLARE
  v_msg1 VARCHAR2(25);
BEGIN
  v_msg1 :='This PL/SQL block works!';
  DBMS_OUTPUT.PUT_LINE('The output is: '|| v_msg1);
END;
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> edit afst1_intro
SQL> @ afst1_intro
The output is: This PL/SQL block works!

PL/SQL procedure successfully completed.

This example could also have been done by declaring the variable within SQL*Plus instead of within PL/SQL. When you declare the variable within SQL*Plus, you can use the PRINT followed by the field you want to see to display information on the screen.

I wanted to see the results and make sure the message was in v_msg so I added the PRINT line telling it to display the contents of v_msg so you could see how it worked. This command works after a declaration in SQL*Plus and assignment in PL/SQL. It cannot be done if v_msg is declared within the PL/SQL block. This line can be executed at the SQL> prompt or it can be added after the / in the code in the editor. Throughout this session, I can access the variable and give the command PRINT v_msg and see the results.

The :v_msg inside the begin is necessary because v_msg was declared externally rather then within the PL/SQL block. To reference a bind variable which is a variable that is declared outside the PL/SQL block, you need to have a colon in front of the name when you refer to it within the PL/SQL block.

SQL CODE:

SQL> VARIABLE v_msg VARCHAR2(25);
SQL> edit bfst_intro

PL/SQL CODE:

BEGIN
  :v_msg :='This PL/SQL still works!';
END;
/
SQL CODE:
SQL> START bfst_intro;

PL/SQL procedure successfully completed.

SQL> print v_msg

V_MSG
--------------------------------
This PL/SQL still works!

Or the variable assignment and/or the print can be included in the editor code, although it 
is still not inside the block.  Notice the :v_msg inside the block, this is necessary 
because v_msg is declared externally.


SQL CODE:
SQL> EDIT fst_intro

PL/SQL CODE:
VARIABLE v_msg VARCHAR2(25)
BEGIN
  :v_msg :='This PL/SQL works again!';
END;
/
PRINT v_msg

SQL CODE:
SQL> START fst_intro

PL/SQL procedure successfully completed.

Input truncated to 11 characters
 
V_MSG
--------------------------------
This PL/SQL works again!

You can add comments to your PL/SQL code in two ways.  If it is a single line comment you 
can use --Comment.  If you are commenting several lines, you would use /* at the beginning 
of the comment area and */ to end the comment.


SQL CODE:
SQL> edit afst2_intro

PL/SQL CODE:
SET SERVEROUTPUT ON
DECLARE
  v_msg1 VARCHAR2(25);
BEGIN
  v_msg1 :='This PL/SQL block works!'; --This assigns the message to v_msg1
  /* The dbms_output.put_line allows the programmer to put information on the
     screen as they process.  It is an excellent tool for debugging.  Notice that
     you need to have the set serveroutput on and the set serveroutput off when you 
     are using this command */
  DBMS_OUTPUT.PUT_LINE('The output is: '|| v_msg1);
END;
/
SET SERVEROUTPUT OFF

SQL CODE:
SQL> @ afst2_intro
The output is: This PL/SQL block works!

PL/SQL procedure successfully completed.

In this example, I am again using serveroutput to show the results of the processing.  I 
have declared three variables.  Notice that instead of giving them a type and a length, I 
am telling them to use the type and length of the fields that I show.  For example, with 
v_idno, I am establishing the type and length as the type and length of the idno field in 
the table donornew.  The format is table name.column name %TYPE.  The processing in the 
executable part does a select of the idno, name and yrgoal for a particular record and 
puts the information into the variables listed in the INTO clause.  The SELECT can only 
select one record.  Later we will deal with the concepts to handle multiple records. 
Notice that order is critical here.  The dbms_output.put_line(...) is used to show the 
information in the variables.


SQL CODE:
SQL> edit snd_intro

PL/SQL CODE:
SET SERVEROUTPUT ON
DECLARE
  v_idno donornew.idno%TYPE;
  v_name donornew.name%TYPE;
  v_yrgoal donornew.yrgoal%TYPE;
BEGIN
  SELECT idno, name, yrgoal
  INTO v_idno, v_name, v_yrgoal
  FROM donornew
  WHERE idno = '12121';
  dbms_output.put_line (v_idno||'  '||v_name||'  '||v_yrgoal);
END;
/
SET SERVEROUTPUT OFF
 
SQL CODE:
SQL> @ snd_intro
12121  Jennifer Ames  440

PL/SQL procedure successfully completed.

In the example below, instead of having the identification number a constant in the code, I 
want the user to enter the identificaiton number and then I want to retrieve the matching 
record.  I will illustrate two ways to do this below.  The first way takes in the input 
within the BEGIN and the second takes it in from SQL*Plus ACCEPT statement.


SQL CODE:
SQL> edit snd1_intro

PL/SQL CODE:
SET SERVEROUTPUT ON
DECLARE
  v_idno donornew.idno%TYPE;
  v_name donornew.name%TYPE;
  v_yrgoal donornew.yrgoal%TYPE;
  v_in_idno donornew.idno%TYPE;
BEGIN
  v_in_idno := &input_idno;
  SELECT idno, name, yrgoal
  INTO v_idno, v_name, v_yrgoal
  FROM donornew
  WHERE idno = v_in_idno;
  dbms_output.put_line (v_idno||'  '||v_name||'  '||v_yrgoal);
END;
/
SET SERVEROUTPUT OFF

SQL CODE:
SQL> START snd1_intro
Enter value for input_idno: 11111
old   7:   v_in_idno := &input_idno;
new   7:   v_in_idno := 11111;
11111  Stephen Daniels  650

PL/SQL procedure successfully completed.

SQL CODE:
SQL> edit snd2_intro

PL/SQL CODE:
SET SERVEROUTPUT ON
ACCEPT input_idno PROMPT 'Enter the identification number: '
DECLARE
  v_idno donornew.idno%TYPE;
  v_name donornew.name%TYPE;
  v_yrgoal donornew.yrgoal%TYPE;
 BEGIN
  SELECT idno, name, yrgoal
  INTO v_idno, v_name, v_yrgoal
  FROM donornew
  WHERE idno = &input_idno;
  dbms_output.put_line (v_idno||'  '||v_name||'  '||v_yrgoal);
END;
/
SET SERVEROUTPUT OFF

SQL CODE:
SQL> @ snd2_intro
Enter the identification number: 11111
old   9:   WHERE idno = &input_idno;
new   9:   WHERE idno = 11111;
11111  Stephen Daniels  650

PL/SQL procedure successfully completed.

Nesting blocks:
The next topic will deal with a brief introduction to nesting blocks within each other and 
the scope of the variables defined in the blocks.


SQL CODE:
SQL> edit nest1_intro

I defined v_first and v_msg in the outer block and in the inner block.  I gave them initial 
values in both places.  In the outer block v_first has an initial value of 212 and v_msg 
has an initial value of * Outer define *.  In the inner block v_msg has an initial value 
of 555 and v_msg has an initial value of * Inner define *.  The things defined in the outer 
loop and the processing that takes place in the BEGIN are show first.  Then I go to the 
inner loop and v_first number is no longer 212, it now picks up the value of 555 because 
the definitions in the inner loop takes precedence over the definitions in the outer loop.  
The same applies to the message.  In the inner loop I print the information and see those 
things uniquely defined in the outer, those things uniquely defined in the inner and where 
the definitions are the same, I see the results of the inner.  When I leave the inner loop, 
I no longer have access to the things uniquely defined in the inner loop.  The things 
defined in both the inner and the outer revert to their outer defined values.


PL/SQL CODE:
SET SERVEROUTPUT ON
DECLARE
  v_first NUMBER :=212;
  v_msg   VARCHAR2(20) :='* Outer define *';
  out_msg VARCHAR2(20);
  out_ans   NUMBER;
BEGIN
    out_msg := v_msg;
    out_ans := v_first *2;
    dbms_output.put_line('BEFORE INNER DEFINE');
    dbms_output.put_line(v_first||v_msg||out_msg||out_ans);
    DECLARE
      v_first NUMBER := 555;
      v_msg VARCHAR2(20) := '* Inner define *';
      in_msg VARCHAR2(20);
      in_ans NUMBER;
    BEGIN
      in_msg := v_msg;
      in_ans := v_first * 2;
      dbms_output.put_line('INNER LOOP SHOWING INNER');
      dbms_output.put_line(v_first||v_msg||in_msg||in_ans);
      dbms_output.put_line('INNER LOOP SHOWING OUTER');
      dbms_output.put_line(out_msg||out_ans);
    END;
    dbms_output.put_line('AFTER INNER/BACK TO OUTER'||v_first||v_msg);
    dbms_output.put_line('OUTER LOOP SHOWING OUTER'||out_msg||out_ans);
    /* Note that when I try to show inner definitions here, in_msg and
       in_ans it crashes */
END;
/
SET SERVEROUTPUT OFF

SQL CODE:
SQL> @ nest1_intro

BEFORE INNER DEFINE
212* Outer define ** Outer define *424
INNER LOOP SHOWING INNER
555* Inner define ** Inner define *1110
INNER LOOP SHOWING OUTER
* Outer define *424
AFTER INNER/BACK TO OUTER212* Outer define *
OUTER LOOP SHOWING OUTER* Outer define *424

PL/SQL procedure successfully completed.