PL/SQL User's Guide and Reference

**SQL*Plus Environment**

After entering the SQL*Plus environment, you can use PL/SQL in several ways:

input, store, and run a PL/SQL block

create, load, and run a script containing PL/SQL blocks, subprograms, and/or packages

pass bind variables to PL/SQL

call a PL/SQL stored subprogram

Note: This section discusses these topics briefly. For a full discussion, see SQL*Plus User's Guide and Reference.

Inputting an Anonymous Block

Every PL/SQL block begins with the keyword DECLARE or, if the block has no declarative part, with the keyword BEGIN. Typing either keyword at the SQL*Plus prompt (SQL>) signals SQL*Plus to

clear the SQL buffer

enter INPUT mode

ignore semicolons (the SQL statement terminator)

SQL*Plus expects you to input an unlabeled PL/SQL block, so you cannot start with a block label.

You input the PL/SQL block line by line. Ending the block with a period (.) on a line by itself stores the block in the SQL buffer.

You can save your PL/SQL block in a script file as follows:

SQL> SAVE <filename>

If you want to edit the file, you can use the SQL*Plus line editor. For instructions, see SQL*Plus User's Guide and Reference. After editing the file, you can save it again as follows:

SQL> SAVE <filename> REPLACE

Executing an Anonymous Block

After inputting a PL/SQL block, you need not end it with a period. Ending the block with a slash (/) on a line by itself stores the block in the SQL buffer, then runs the block. Once it is stored in the SQL buffer, you can run the PL/SQL block again, as follows:

SQL> RUN  or  SQL> /

When the block is finished running, you are returned to the SQL*Plus prompt. The SQL buffer is not cleared until you start inputting the next SQL statement or PL/SQL block.

Creating a Script

You can use your favorite text editor to create scripts containing SQL*Plus statements and PL/SQL blocks, subprograms, and/or packages. In the following example, a PL/SQL block is preceded by SQL*Plus statements that set up a report:

CLEAR BREAKS
CLEAR COLUMNS
COLUMN ENAME HEADING Name
TTITLE 'CLERICAL STAFF'
DECLARE
   avg_sal NUMBER(7,2);
BEGIN
   SELECT AVG(sal) INTO avg_sal FROM emp;
   IF avg_sal < 1500 THEN
      UPDATE emp SET sal = sal * 1.05 WHERE job = 'CLERK';
   END IF;
END;
/
SELECT ENAME, SAL FROM EMP WHERE JOB = 'CLERK';

The two CLEAR statements get rid of any settings left over from a previous report. The COLUMN statement changes the ENAME column heading to Name. The TTITLE statement specifies a title that appears at the top of each page in the report. The semicolon (;) following each SQL*Plus statement executes that statement. Likewise, the slash (/) following the PL/SQL block executes that block.

Loading and Running a Script

After invoking SQL*Plus, you can load and run a script in one step, as follows:

SQL> START <filename>  or  SQL> @<filename>

Your PL/SQL block can take advantage of the SQL*Plus substitution variable feature. Before running a script, SQL*Plus prompts for the value of any variable prefixed with an ampersand (&). In the following example, SQL*Plus prompts for the value of num:

SQL> BEGIN
  2     FOR i IN 1..&num LOOP ...
  ...
  8  END;
  9  /
Enter value for num:

Creating a Stored Subprogram, Package, or Trigger

To create PL/SQL subprograms, packages, and triggers and store them permanently in an Oracle database, you use the following SQL commands:

CREATE FUNCTION

CREATE PACKAGE

CREATE PACKAGE BODY

CREATE PROCEDURE

CREATE TRIGGER

When you type any of these commands, SQL*Plus clears the SQL buffer and enters INPUT mode. In the following example, you input a PL/SQL procedure, then create and store it in the database by typing a slash:

SQL> CREATE PROCEDURE create_dept (new_name CHAR, new_loc CHAR) AS
  2  BEGIN
  3     INSERT INTO dept
  4        VALUES (deptno_seq.NEXTVAL, new_name, new_loc);
  5  END create_dept;
  6  /
Procedure created.

If SQL*Plus tells you that the subprogram, package, or trigger was created with compilation errors, you can view them by typing the SQL*Plus command SHOW ERRORS, as follows:

SQL> SHOW ERRORS

Using Bind Variables

A bind variable is a variable you declare in SQL*Plus, then pass to one or more PL/SQL programs, which can use it like any other variable. Both SQL*Plus and PL/SQL can reference the bind variable, and SQL*Plus can display its value.

To declare a bind variable, you use the SQL*Plus command VARIABLE. In the following example, you declare a variable of type NUMBER:

VARIABLE return_code NUMBER

Note: If you declare a bind variable with the same name as a PL/SQL program variable, the latter takes precedence.

To reference a bind variable in PL/SQL, you must prefix its name with a colon(:), as the following example shows:

:return_code := 0;
IF credit_check_ok(acct_no) THEN 
   :return_code := 1;
END IF;

To display the value of a bind variable in SQL*Plus, you use the PRINT command, as follows:

SQL> PRINT return_code

RETURN_CODE
-----------
          1

In the script below, you declare a bind variable of type REFCURSOR. (The SQL*Plus datatype REFCURSOR lets you declare cursor variables, which you can use to return query results from stored subprograms.) You use the SQL*Plus command SET AUTOPRINT ON to display the query results automatically.

CREATE PACKAGE emp_data AS
   TYPE EmpRecTyp IS RECORD (
      emp_id    NUMBER(4),
      emp_name  CHAR(10),
      job_title CHAR(9), 
      dept_name CHAR(14),
      dept_loc  CHAR(13));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   PROCEDURE get_staff (dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp);
END;
/
CREATE PACKAGE BODY emp_data AS
   PROCEDURE get_staff (dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp) IS
   BEGIN
      OPEN emp_cv FOR 
         SELECT empno, ename, job, dname, loc FROM emp, dept
            WHERE emp.deptno = dept_no AND 
                  emp.deptno = dept.deptno 
            ORDER BY empno;
   END;
END;
/
COLUMN EMPNO HEADING Number
COLUMN ENAME HEADING Name
COLUMN JOB HEADING JobTitle
COLUMN DNAME HEADING Department
COLUMN LOC HEADING Location
SET AUTOPRINT ON
VARIABLE cv REFCURSOR
EXECUTE emp_data.get_staff(20, :cv)

Calling Stored Subprograms

From SQL*Plus, you can call standalone and packaged subprograms stored in a local or remote database. For example, you might call the local standalone procedure create_dept, as follows:

SQL> EXECUTE create_dept('ADVERTISING', 'NEW YORK')

This call is equivalent to the following call issued from an anonymous PL/SQL block:

SQL> BEGIN create_dept('ADVERTISING', 'NEW YORK'); END;

In the next example, you use the database link newyork to call the remote stored procedure raise_salary:

SQL> EXECUTE raise_salary@newyork(7499, 1500)

You can create synonyms to provide location transparency for remote standalone procedures.

Displaying Output

Currently, PL/SQL does not support I/O. However, the package DBMS_OUTPUT (supplied with Oracle7) allows you to display output from PL/SQL blocks and subprograms, which makes it easier to test and debug them. The procedure put_line lets you output information to a buffer. The SQL*Plus command SET SERVEROUTPUT ON lets you display the information. For example, suppose you create the following stored procedure:

CREATE PROCEDURE calc_payroll (payroll IN OUT REAL) AS
   CURSOR c1 IS SELECT sal,comm FROM emp;
BEGIN
   payroll := 0;
   FOR c1rec IN c1 LOOP
      c1rec.comm := NVL(c1rec.comm, 0);
      payroll := payroll + c1rec.sal + c1rec.comm;
   END LOOP;
   /* Display debug info. */
   dbms_output.put_line('payroll: ' || TO_CHAR(payroll));
END calc_payroll;

When you issue the following commands, SQL*Plus displays the value of payroll calculated by the procedure:

SQL> SET SERVEROUTPUT ON
SQL> VARIABLE num NUMBER
SQL> EXECUTE calc_payroll(:num)

For more information about package DBMS_OUTPUT, see Oracle7 Server Application Developer's Guide.