Listing 9.16. Use of different argument types—IN, OUT, and IN OUT.

SQL> declare
  2
  2  This_Arg1 number;
  3  This_Arg2 number;
  4  This_Arg3 number;
  5
  5  procedure Different_Arguments
  6            (arg1 IN     number,
  7             arg2 OUT    number,
  8             arg3 IN OUT number) is
  9
  9  begin
 10
 10  arg2 := arg1;
 11  arg3 := arg3 + 1;
 12
 12  end;
 13
 13  --  Beginning of executable section of anonymous block.
 14
 14  begin
 15
 15  This_Arg1 := 3.14159;
 16
 16  Different_Arguments (This_Arg1, This_Arg2, This_Arg3);
 17
 17  end;
 18  /
PL/SQL procedure successfully completed.

Summary

This lesson discussed these basic features of PL/SQL:

• PL/SQL is a block-structured language in which a block consists of an optional declaration section, an executable section, and an optional exception-handling section.
• Some additional datatypes are available in PL/SQL's SQL-extensions: BINARY_INTEGER, NATURAL, POSITIVE, %TYPE, %ROWTYPE, TABLE, and RECORD.
• Executable PL/SQL statements include IF-THEN-ELSIF, LOOP, EXIT, the WHILE-LOOP, the FOR-LOOP, and GOTO.
• A PL/SQL subprogram can include SQL statements that reference PL/SQL variables.
• A PL/SQL subprogram can declare its own procedures and functions.
  

What Comes Next?

On Day 10, you learn much more about the declaration and use of procedures, functions, and packages.

Q&A

Q Can a function call other procedures and functions and can a procedure call other procedures and functions?

A Yes, this is supported by PL/SQL. In fact, PL/SQL also supports recursion.

Workshop

The purpose of the Workshop is to allow you to test your knowledge of the material discussed in the lesson. See if you can correctly answer the questions in the quiz and complete the exercise before you continue with tomorrow's lesson.

Quiz

1. Name the three sections of a PL/SQL subprogram.
2. True or false? A PL/SQL variable that stores a column value must have the same name as the column.
3. Why is it a good practice to use %TYPE when declaring variables?

Exercise

Construct an anonymous block that declares a procedure that will set the additional fees for a course to $50 if there are no current additional fees. The procedure should have only one argument—Department_ID—and should perform this action only for the value of Department_ID supplied to the procedure.