Local Procedures In Pl/Sql What Is The Assignment Operator

8Using PL/SQL Subprograms

Civilization advances by extending the number of important operations that we can perform without thinking about them. —Alfred North Whitehead

This chapter shows you how to turn sets of statements into reusable subprograms. Subprograms are like building blocks for modular, maintainable applications.

This chapter contains these topics:

What Are Subprograms?

Subprograms are named PL/SQL blocks that can be called with a set of parameters. PL/SQL has two types of subprograms, procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value.

Like anonymous blocks, subprograms have:

  • A declarative part, with declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local and cease to exist when the subprogram ends.

  • An executable part, with statements that assign values, control execution, and manipulate Oracle data.

  • An optional exception-handling part, which deals with runtime error conditions.

Example 8-1 Simple PL/SQL Procedure

The following example shows a string-manipulation procedure that accepts both input and output parameters, and handles potential errors:

CREATE OR REPLACE PROCEDURE double ( original IN VARCHAR2, new_string OUT VARCHAR2 ) AS BEGIN new_string := original || original; EXCEPTION WHEN VALUE_ERROR THEN dbms_output.put_line('Output buffer not long enough.'); END; /

Example 8-2 Simple PL/SQL Function

The following example shows a numeric function that declares a local variable to hold temporary results, and returns a value when finished:

CREATE OR REPLACE FUNCTION square(original NUMBER) RETURN NUMBER AS original_squared NUMBER; BEGIN original_squared := original * original; RETURN original_squared; END; /

Advantages of PL/SQL Subprograms

Subprograms let you extend the PL/SQL language. Procedures act like new statements. Functions act like new expressions and operators.

Subprograms let you break a program down into manageable, well-defined modules. You can use top-down design and the stepwise refinement approach to problem solving.

Subprograms promote reusability. Once tested, a subprogram can be reused in any number of applications. You can call PL/SQL subprograms from many different environments, so that you do not have to reinvent the wheel each time you use a new language or API to access the database.

Subprograms promote maintainability. You can change the internals of a subprogram without changing other subprograms that call it. Subprograms play a big part in other maintainability features, such as packages and object types.

Dummy subprograms (stubs) let you defer the definition of procedures and functions until after testing the main program. You can design applications from the top down, thinking abstractly, without worrying about implementation details.

When you use PL/SQL subprograms to define an API, you can make your code even more reusable and maintainable by grouping the subprograms into a PL/SQL package. For more information about packages, see Chapter 9, " Using PL/SQL Packages".

Understanding PL/SQL Procedures

A procedure is a subprogram that performs a specific action. You write procedures using the SQL statement. You specify the name of the procedure, its parameters, its local variables, and the block that contains its code and handles any exceptions.

For each parameter, you specify:

  • Its name.

  • Its parameter mode (, , or ). If you omit the mode, the default is . The optional keyword speeds up processing of large or parameters.

  • Its datatype. You specify only the type, not any length or precision constraints.

  • Optionally, its default value.

You can specify whether the procedure executes using the schema and permissions of the user who defined it, or the user who calls it. For more information, see "Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)".

You can specify whether it should be part of the current transaction, or execute in its own transaction where it can or without ending the transaction of the caller. For more information, see "Doing Independent Units of Work with Autonomous Transactions".

Procedures created this way are stored in the database. You can execute the statement interactively from SQL*Plus, or from a program using native dynamic SQL (see Chapter 7, " Performing SQL Operations with Native Dynamic SQL").

A procedure has two parts: the specification (spec for short) and the body. The procedure spec begins with the keyword and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses.

The procedure body begins with the keyword (or ) and ends with the keyword followed by an optional procedure name. The procedure body has three parts: a declarative part, an executable part, and an optional exception-handling part.

The declarative part contains local declarations. The keyword is used for anonymous PL/SQL blocks, but not procedures. The executable part contains statements, which are placed between the keywords and (or ). At least one statement must appear in the executable part of a procedure. You can use the statement to define a placeholder procedure or specify that the procedure does nothing. The exception-handling part contains exception handlers, which are placed between the keywords and .

A procedure is called as a PL/SQL statement. For example, you might call the procedure as follows:

raise_salary(emp_id, amount);

Understanding PL/SQL Functions

A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a clause.

Functions have a number of optional keywords, used to declare a special class of functions known as table functions. They are typically used for transforming large amounts of data in data warehousing applications.

The clause lets you create standalone functions, which are stored in an Oracle database. You can execute the statement interactively from SQL*Plus or from a program using native dynamic SQL.

The clause determines whether a stored function executes with the privileges of its owner (the default) or current user and whether its unqualified references to schema objects are resolved in the schema of the owner or current user. You can override the default behavior by specifying .

The option declares that a stored function can be used safely in the slave sessions of parallel DML evaluations. The state of a main (logon) session is never shared with slave sessions. Each slave session has its own state, which is initialized when the session begins. The function result should not depend on the state of session () variables. Otherwise, results might vary across sessions.

The hint helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only functions can be called from a function-based index or a materialized view that has query-rewrite enabled. For more information, see Oracle Database SQL Reference.

The pragma instructs the PL/SQL compiler to mark a function as autonomous (independent). Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.

You cannot constrain (with for example) the datatype of a parameter or a function return value. However, you can use a workaround to size-constrain them indirectly. See "Understanding PL/SQL Procedures".

Like a procedure, a function has two parts: the spec and the body. The function spec begins with the keyword and ends with the clause, which specifies the datatype of the return value. Parameter declarations are optional. Functions that take no parameters are written without parentheses.

The function body begins with the keyword (or ) and ends with the keyword followed by an optional function name. The function body has three parts: a declarative part, an executable part, and an optional exception-handling part.

The declarative part contains local declarations, which are placed between the keywords and . The keyword is not used. The executable part contains statements, which are placed between the keywords and (or ). One or more statements must appear in the executable part of a function. The exception-handling part contains exception handlers, which are placed between the keywords and .

A function is called as part of an expression:

IF sal_ok(new_sal, new_title) THEN ...

Using the RETURN Statement

The statement immediately ends the execution of a subprogram and returns control to the caller. Execution continues with the statement following the subprogram call. (Do not confuse the statement with the clause in a function spec, which specifies the datatype of the return value.)

A subprogram can contain several statements. The subprogram does not have to conclude with a statement. Executing any statement completes the subprogram immediately.

In procedures, a statement does not return a value and so cannot contain an expression. The statement returns control to the caller before the end of the procedure.

In functions, a statement must contain an expression, which is evaluated when the statement is executed. The resulting value is assigned to the function identifier, which acts like a variable of the type specified in the clause. Observe how the function returns the balance of a specified bank account:

FUNCTION balance (acct_id INTEGER) RETURN REAL IS acct_bal REAL; BEGIN SELECT bal INTO acct_bal FROM accts WHERE acct_no = acct_id; RETURN acct_bal; END balance; /

The following example shows that the expression in a function statement can be arbitrarily complex:

FUNCTION compound ( years NUMBER, amount NUMBER, rate NUMBER) RETURN NUMBER IS BEGIN RETURN amount * POWER((rate / 100) + 1, years); END compound; /

In a function, there must be at least one execution path that leads to a statement. Otherwise, you get a function returned without value error at run time.

Declaring Nested PL/SQL Subprograms

You can declare subprograms in any PL/SQL block, subprogram, or package. The subprograms must go at the end of the declarative section, after all other items.

You must declare a subprogram before calling it. This requirement can make it difficult to declare several nested subprograms that call each other.

You can declare interrelated nested subprograms using a forward declaration: a subprogram spec terminated by a semicolon, with no body.

Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram body. You can place the subprogram body anywhere after the forward declaration, but they must appear in the same program unit.

Example 8-3 Forward Declaration for a Nested Subprogram

DECLARE PROCEDURE proc1(arg_list); -- forward declaration PROCEDURE proc2(arg_list); -- calls proc1 PROCEDURE proc1(arg_list) IS BEGIN proc2; END; -- calls proc2 BEGIN NULL; END; /

Passing Parameters to PL/SQL Subprograms

This section explains how to pass information in and out of PL/SQL subprograms using parameters:

Actual Versus Formal Subprogram Parameters

Subprograms pass information using parameters:

  • The variables declared in a subprogram spec and referenced in the subprogram body are formal parameters.

  • The variables or expressions passed from the calling subprogram are actual parameters.

A good programming practice is to use different names for actual and formal parameters.

When you call a procedure, the actual parameters are evaluated and the results are assigned to the corresponding formal parameters. If necessary, before assigning the value of an actual parameter to a formal parameter, PL/SQL converts the datatype of the value. For example, if you pass a number when the procedure expects a string, PL/SQL converts the parameter so that the procedure receives a string.

The actual parameter and its corresponding formal parameter must have compatible datatypes. For instance, PL/SQL cannot convert between the and datatypes, or convert a string to a number if the string contains extra characters such as dollar signs.

Example 8-4 Formal Parameters and Actual Parameters

The following procedure declares two formal parameters named and :

PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS BEGIN UPDATE emp SET sal = sal + amount WHERE empno = emp_id; END raise_salary; /

This procedure call specifies the actual parameters and :

raise_salary(emp_num, amount);

Expressions can be used as actual parameters:

raise_salary(emp_num, merit + cola);

Using Positional, Named, or Mixed Notation for Subprogram Parameters

When calling a subprogram, you can write the actual parameters using either:

  • Positional notation. You specify the same parameters in the same order as they are declared in the procedure.

    This notation is compact, but if you specify the parameters (especially literals) in the wrong order, the bug can be hard to detect. You must change your code if the procedure's parameter list changes.

  • Named notation. You specify the name of each parameter along with its value. An arrow () serves as the association operator. The order of the parameters is not significant.

    This notation is more verbose, but makes your code easier to read and maintain. You can sometimes avoid changing your code if the procedure's parameter list changes, for example if the parameters are reordered or a new optional parameter is added. Named notation is a good practice to use for any code that calls someone else's API, or defines an API for someone else to use.

  • Mixed notation. You specify the first parameters with positional notation, then switch to named notation for the last parameters.

    You can use this notation to call procedures that have some required parameters, followed by some optional parameters.

Example 8-5 Subprogram Calls Using Positional, Named, and Mixed Notation

DECLARE acct INTEGER := 12345; amt REAL := 500.00; PROCEDURE credit_acct (acct_no INTEGER, amount REAL) IS BEGIN NULL; END; BEGIN -- The following calls are all equivalent. credit_acct(acct, amt); -- positional credit_acct(amount => amt, acct_no => acct); -- named credit_acct(acct_no => acct, amount => amt); -- named credit_acct(acct, amount => amt); -- mixed END; /

Specifying Subprogram Parameter Modes

You use parameter modes to define the behavior of formal parameters. The three parameter modes are (the default), , and .

Any parameter mode can be used with any subprogram. Avoid using the and modes with functions. To have a function return multiple values is a poor programming practice. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.

Using the IN Mode

An parameter lets you pass values to the subprogram being called. Inside the subprogram, an parameter acts like a constant. It cannot be assigned a value.

You can pass a constant, literal, initialized variable, or expression as an IN parameter.

parameters can be initialized to default values, which are used if those parameters are omitted from the subprogram call. For more information, see "Using Default Values for Subprogram Parameters".

Using the OUT Mode

An parameter returns a value to the caller of a subprogram. Inside the subprogram, an parameter acts like a variable. You can change its value, and reference the value after assigning it:

PROCEDURE split_name ( phrase IN VARCHAR2, first OUT VARCHAR2, last OUT VARCHAR2 ) IS first := SUBSTR(phrase, 1, INSTR(phrase, ' ')-1); last := SUBSTR(phrase, INSTR(phrase, ' ')+1); IF first = 'John' THEN DBMS_OUTPUT.PUT_LINE('That is a common first name.'); END IF; END; /

You must pass a variable, not a constant or an expression, to an parameter. Its previous value is lost unless you specify the keyword (see "Using Default Values for Subprogram Parameters") or the subprogram exits with an unhandled exception.

Like variables, formal parameters are initialized to . The datatype of an formal parameter cannot be a subtype defined as , such as the built-in subtypes and . Otherwise, when you call the subprogram, PL/SQL raises .

Before exiting a subprogram, assign values to all formal parameters. Otherwise, the corresponding actual parameters will be null. If you exit successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

Using the IN OUT Mode

An parameter passes initial values to a subprogram and returns updated values to the caller. It can be assigned a value and its value can be read. Typically, an parameter is a string buffer or numeric accumulator, that is read inside the subprogram and then updated.

The actual parameter that corresponds to an formal parameter must be a variable; it cannot be a constant or an expression.

If you exit a subprogram successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

Summary of Subprogram Parameter Modes

Table 8-1 summarizes all you need to know about the parameter modes.

Table 8-1 Parameter Modes

INOUTIN OUT
The defaultMust be specifiedMust be specified
Passes values to a subprogramReturns values to the callerPasses initial values to a subprogram and returns updated values to the caller
Formal parameter acts like a constantFormal parameter acts like an uninitialized variableFormal parameter acts like an initialized variable
Formal parameter cannot be assigned a valueFormal parameter must be assigned a valueFormal parameter should be assigned a value
Actual parameter can be a constant, initialized variable, literal, or expressionActual parameter must be a variableActual parameter must be a variable
Actual parameter is passed by reference (a pointer to the value is passed in)Actual parameter is passed by value (a copy of the value is passed out) unless is specifiedActual parameter is passed by value (a copy of the value is passed in and out) unless is specified

Using Default Values for Subprogram Parameters

By initializing parameters to default values, you can pass different numbers of actual parameters to a subprogram, accepting the default values for any parameters you omit. You can also add new formal parameters without having to change every call to the subprogram.

Example 8-6 Procedure with Default Parameter Values

PROCEDURE create_dept ( new_dname VARCHAR2 DEFAULT 'TEMP', new_loc VARCHAR2 DEFAULT 'TEMP') IS BEGIN NULL; END; /

If a parameter is omitted, the default value of its corresponding formal parameter is used. Consider the following calls to :

create_dept; -- Same as create_dept('TEMP','TEMP'); create_dept('SALES'); -- Same as create_dept('SALES','TEMP'); create_dept('SALES', 'NY');

You cannot skip a formal parameter by leaving out its actual parameter. To omit the first parameter and specify the second, use named notation:

create_dept(new_loc => 'NEW YORK');

You cannot assign a null to an uninitialized formal parameter by leaving out its actual parameter. You must pass the null explicitly, or you can specify a default value of in the declaration.

Overloading Subprogram Names

PL/SQL lets you overload subprogram names and type methods. You can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family.

Suppose you want to initialize the first n rows in two index-by tables that were declared as follows:

DECLARE TYPE DateTabTyp IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE RealTabTyp IS TABLE OF REAL INDEX BY BINARY_INTEGER; hiredate_tab DateTabTyp; sal_tab RealTabTyp; BEGIN NULL; END; /

You might write a procedure to initialize one kind of collection:

PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS BEGIN FOR i IN 1..n LOOP tab(i) := SYSDATE; END LOOP; END initialize; /

You might also write a procedure to initialize another kind of collection:

PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS BEGIN FOR i IN 1..n LOOP tab(i) := 0.0; END LOOP; END initialize; /

Because the processing in these two procedures is the same, it is logical to give them the same name.

You can place the two overloaded procedures in the same block, subprogram, package, or object type. PL/SQL determines which procedure to call by checking their formal parameters. In the following example, the version of that PL/SQL uses depends on whether you call the procedure with a or parameter:

DECLARE TYPE DateTabTyp IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE RealTabTyp IS TABLE OF REAL INDEX BY BINARY_INTEGER; hiredate_tab DateTabTyp; comm_tab RealTabTyp; indx BINARY_INTEGER; PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS BEGIN NULL; END; PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS BEGIN NULL; END; BEGIN indx := 50; initialize(hiredate_tab, indx); -- calls first version initialize(comm_tab, indx); -- calls second version END; /

Guidelines for Overloading with Numeric Types

You can overload two subprograms if their formal parameters differ only in numeric datatype. This technique might be useful in writing mathematical APIs, where several versions of a function could use the same name, each accepting a different numeric type. For example, a function accepting might be faster, while a function accepting might provide more precision.

To avoid problems or unexpected results passing parameters to such overloaded subprograms:

  • Make sure to test that the expected version of a subprogram is called for each set of expected parameters. For example, if you have overloaded functions that accept and , which is called if you pass a literal such as '5.0'?

  • Qualify numeric literals and use conversion functions to make clear what the intended parameter types are. For example, use literals such as (for ), (for ), or conversion functions such as , , and .

PL/SQL looks for matching numeric parameters starting with or , then , then , then . The first overloaded subprogram that matches the supplied parameters is used. A value can match a , , or parameter.

For example, consider the function, which takes a single parameter. There are overloaded versions that accept a , a , or a parameter. If you pass a parameter, the first matching overload (using the order given in the preceding paragraph) is the one with a parameter, which is likely to be the slowest. To use one of the faster versions, use the or functions to convert the parameter to the right datatype.

For another example, consider the function, which takes two parameters of the same type. If you pass two parameters of the same type, you can predict which overloaded version is used through the same rules as before. If you pass parameters of different types, for example one and one , PL/SQL tries to find a match where both parameters use the "higher" type. In this case, that is the version of that takes two parameters; the parameter is converted "upwards".

The preference for converting "upwards" holds in more complicated situations. For example, you might have a complex function that takes two parameters of different types. One overloaded version might take a and a parameter. Another overloaded version might take a and a parameter. What happens if you call this procedure name and pass two parameters? PL/SQL looks "upward" first to find the overloaded version where the second parameter is . Because this parameter is a closer match than the parameter in the other overload, PL/SQL then looks "downward" and converts the first parameter to .

Restrictions on Overloading

Only local or packaged subprograms, or type methods, can be overloaded. You cannot overload standalone subprograms.

You cannot overload two subprograms if their formal parameters differ only in name or parameter mode. For example, you cannot overload the following two procedures:

DECLARE PROCEDURE reconcile (acct_no IN INTEGER) IS BEGIN NULL; END; PROCEDURE reconcile (acct_no OUT INTEGER) IS BEGIN NULL; END; /

You cannot overload subprograms whose parameters differ only in subtype. For example, you cannot overload procedures where one accepts an parameter and the other accepts a parameter, even though and are both subtypes of and so are in the same family.

You cannot overload two functions that differ only in the datatype of the return value, even if the types are in different families. For example, you cannot overload two functions where one returns and the other returns .

How Subprogram Calls Are Resolved

Figure 8-1 shows how the PL/SQL compiler resolves subprogram calls. When the compiler encounters a procedure or function call, it tries to find a declaration that matches the call. The compiler searches first in the current scope and then, if necessary, in successive enclosing scopes. The compiler looks more closely when it finds one or more subprogram declarations in which the subprogram name matches the name of the called subprogram.

To resolve a call among possibly like-named subprograms at the same level of scope, the compiler must find an exact match between the actual and formal parameters. They must match in number, order, and datatype (unless some formal parameters were assigned default values). If no match is found or if multiple matches are found, the compiler generates a semantic error.

The following example calls the enclosing procedure from the function , generating an error because neither declaration of within the current scope matches the procedure call:

PROCEDURE swap (n1 NUMBER, n2 NUMBER) IS num1 NUMBER; num2 NUMBER; FUNCTION balance (...) RETURN REAL IS PROCEDURE swap (d1 DATE, d2 DATE) IS BEGIN NULL; END; PROCEDURE swap (b1 BOOLEAN, b2 BOOLEAN) IS BEGIN NULL; END; BEGIN swap(num1, num2); RETURN ... END balance; BEGIN NULL; END; /

How Overloading Works with Inheritance

The overloading algorithm allows substituting a subtype value for a formal parameter that is a supertype. This capability is known as substitutability. If more than one instance of an overloaded procedure matches the procedure call, the following rules apply to determine which procedure is called:

If the only difference in the signatures of the overloaded procedures is that some parameters are object types from the same supertype-subtype hierarchy, the closest match is used. The closest match is one where all the parameters are at least as close as any other overloaded instance, as determined by the depth of inheritance between the subtype and supertype, and at least one parameter is closer.

A semantic error occurs when two overloaded instances match, and some argument types are closer in one overloaded procedure to the actual arguments than in any other instance.

A semantic error also occurs if some parameters are different in their position within the object type hierarchy, and other parameters are of different datatypes so that an implicit conversion would be necessary.

For example, here we create a type hierarchy with 3 levels:

CREATE TYPE super_t AS object (n NUMBER) NOT final; CREATE OR replace TYPE sub_t under super_t (n2 NUMBER) NOT final; CREATE OR replace TYPE final_t under sub_t (n3 NUMBER);

We declare two overloaded instances of a function, where the only difference in argument types is their position in this type hierarchy:

CREATE PACKAGE p IS FUNCTION foo (arg super_t) RETURN NUMBER; FUNCTION foo (arg sub_t) RETURN NUMBER; END; / CREATE PACKAGE BODY p IS FUNCTION foo (arg super_t) RETURN NUMBER IS BEGIN RETURN 1; END; FUNCTION foo (arg sub_t) RETURN NUMBER IS BEGIN RETURN 2; END; END; /

We declare a variable of type , then call the overloaded function. The instance of the function that is executed is the one that accepts a parameter, because that type is closer to in the hierarchy than is.

set serveroutput on declare v final_t := final_t(1,2,3); begin dbms_output.put_line(p.foo(v)); end; /

In the previous example, the choice of which instance to call is made at compile time. In the following example, this choice is made dynamically.

CREATE TYPE super_t2 AS object (n NUMBER, MEMBER FUNCTION foo RETURN NUMBER) NOT final; / CREATE TYPE BODY super_t2 AS MEMBER FUNCTION foo RETURN NUMBER IS BEGIN RETURN 1; END; END; / CREATE OR replace TYPE sub_t2 under super_t2 (n2 NUMBER, OVERRIDING MEMBER FUNCTION foo RETURN NUMBER) NOT final; / CREATE TYPE BODY sub_t2 AS OVERRIDING MEMBER FUNCTION foo RETURN NUMBER IS BEGIN RETURN 2; END; END; / CREATE OR replace TYPE final_t2 under sub_t2 (n3 NUMBER); /

We declare as an instance of , but because we assign a value of to it, the appropriate instance of the function is called. This feature is known as dynamic dispatch.

set serveroutput on declare v super_t2 := final_t2(1,2,3); begin dbms_output.put_line(v.foo); end; /

Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)

By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user. Such definer's rights subprograms are bound to the schema in which they reside, allowing you to refer to objects in the same schema without qualifying their names. For example, if schemas and both have a table called dept, a procedure owned by can refer to rather than . If user calls 's procedure, the procedure still accesses the table owned by .

If you compile the same procedure in both schemas, you can define the schema name as a variable in SQL*Plus and refer to the table like . The code is portable, but if you change it, you must recompile it in each schema.

A more maintainable way is to use the clause, which makes stored procedures and SQL methods execute with the privileges and schema context of the calling user. You can create one instance of the procedure, and many users can call it to access their own data.

Such invoker's rights subprograms are not bound to a particular schema. The following version of procedure executes with the privileges of the calling user and inserts rows into that user's table:

CREATE PROCEDURE create_dept ( my_deptno NUMBER, my_dname VARCHAR2, my_loc VARCHAR2) AUTHID CURRENT_USER AS BEGIN INSERT INTO dept VALUES (my_deptno, my_dname, my_loc); END; /

Advantages of Invoker's Rights

Invoker's rights subprograms let you reuse code and centralize application logic. They are especially useful in applications that store data using identical tables in different schemas. All the schemas in one instance can call procedures owned by a central schema. You can even have schemas in different instances call centralized procedures using a database link.

Consider a company that uses a stored procedure to analyze sales. If the company has several schemas, each with a similar table, normally it would also need several copies of the stored procedure, one in each schema.

To solve the problem, the company installs an invoker's rights version of the stored procedure in a central schema. Now, all the other schemas can call the same procedure, which queries the appropriate to table in each case.

You can restrict access to sensitive data by calling from an invoker's rights subprogram to a definer's rights subprogram that queries or updates the table containing the sensitive data. Although multiple users can call the invoker's rights subprogram, they do not have direct access to the sensitive data.

Specifying the Privileges for a Subprogram with the AUTHID Clause

To implement invoker's rights, use the clause, which specifies whether a subprogram executes with the privileges of its owner or its current user. It also specifies whether external references (that is, references to objects outside the subprogram) are resolved in the schema of the owner or the current user.

The clause is allowed only in the header of a standalone subprogram, a package spec, or an object type spec. In the , , , or statement, you can include either or immediately before the or keyword that begins the declaration section.

is the default option. In a package or object type, the clause applies to all subprograms.

Note: Most supplied PL/SQL packages (such as , , , , and ) are invoker's rights packages.

Who Is the Current User During Subprogram Execution?

In a sequence of calls, whenever control is inside an invoker's rights subprogram, the current user is the session user. When a definer's rights subprogram is called, the owner of that subprogram becomes the current user. The current user might change as new subprograms are called or as subprograms exit.

To verify who the current user is at any time, you can check the data dictionary view. Inside an invoker's rights subprogram, the value from this view might be different from the value of the built-in function, which always returns the name of the session user.

How External References Are Resolved in Invoker's Rights Subprograms

If you specify , the privileges of the current user are checked at run time, and external references are resolved in the schema of the current user. However, this applies only to external references in:

  • , , , and data manipulation statements

  • The transaction control statement

  • and cursor control statements

  • and dynamic SQL statements

  • SQL statements parsed using

For all other statements, the privileges of the owner are checked at compile time, and external references are resolved in the schema of the owner. For example, the assignment statement below refers to the packaged function . This external reference is resolved in the schema of the owner of procedure .

CREATE PROCEDURE reconcile (acc_id IN INTEGER) AUTHID CURRENT_USER AS bal NUMBER; BEGIN bal := bank_ops.balance(acct_id); ... END; /

The Need for Template Objects in Invoker's Rights Subprograms

The PL/SQL compiler must resolve all references to tables and other objects at compile time. The owner of an invoker's rights subprogram must have objects in the same schema with the right names and columns, even if they do not contain any data. At run time, the corresponding objects in the caller's schema must have matching definitions. Otherwise, you get an error or unexpected results, such as ignoring table columns that exist in the caller's schema but not in the schema that contains the subprogram.

Overriding Default Name Resolution in Invoker's Rights Subprograms

Occasionally, you might want an unqualified name to refer to some particular schema, not the schema of the caller. In the same schema as the invoker's rights subprogram, create a public synonym for the table, procedure, function, or other object using the statement:

CREATE PUBLIC SYNONYM emp FOR hr.employees;

When the invoker's rights subprogram refers to this name, it will match the synonym in its own schema, which resolves to the object in the specified schema. This technique does not work if the calling schema already has a schema object or private synonym with the same name. In that case, the invoker's rights subprogram must fully qualify the reference.

Granting Privileges on Invoker's Rights Subprograms

To call a subprogram directly, users must have the privilege on that subprogram. By granting the privilege, you allow a user to:

  • Call the subprogram directly

  • Compile functions and procedures that call the subprogram

For external references resolved in the current user's schema (such as those in DML statements), the current user must have the privileges needed to access schema objects referenced by the subprogram. For all other external references (such as function calls), the owner's privileges are checked at compile time, and no run-time check is done.

A definer's rights subprogram operates under the security domain of its owner, no matter who is executing it. The owner must have the privileges needed to access schema objects referenced by the subprogram.

You can write a program consisting of multiple subprograms, some with definer's rights and others with invoker's rights. Then, you can use the privilege to restrict program entry points. That way, users of an entry-point subprogram can execute the other subprograms indirectly but not directly.

Granting Privileges on an Invoker's Rights Subprogram: Example

Suppose user grants the privilege on subprogram to user :

GRANT EXECUTE ON util.fft TO app;

Now, user can compile functions and procedures that call subprogram . At run time, no privilege checks on the calls are done. As Figure 8-2 shows, user need not grant the privilege to every user who might call indirectly.

Since subprogram is called directly only from invoker's rights subprogram , user must grant the privilege only to user . When is executed, its current user could be , , or even though and were not granted the privilege.

Using Roles with Invoker's Rights Subprograms

The use of roles in a subprogram depends on whether it executes with definer's rights or invoker's rights. Within a definer's rights subprogram, all roles are disabled. Roles are not used for privilege checking, and you cannot set roles.

Within an invoker's rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer's rights subprogram). Roles are used for privilege checking, and you can use native dynamic SQL to set roles for the session. However, you cannot use roles to grant privileges on template objects because roles apply at run time, not at compile time.

Using Views and Database Triggers with Invoker's Rights Subprograms

For invoker's rights subprograms executed within a view expression, the schema that created the view, not the schema that is querying the view, is considered to be the current user.

This rule also applies to database triggers.

Using Database Links with Invoker's Rights Subprograms

You can create a database link to use invoker's rights:

CREATE DATABASE LINK link_name CONNECT TO CURRENT_USER USING connect_string;

A current-user link lets you connect to a remote database as another user, with that user's privileges. To connect, Oracle uses the username of the current user (who must be a global user). Suppose an invoker's rights subprogram owned by user references the database link below. If global user calls the subprogram, it connects to the Dallas database as user , who is the current user.

CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING ...

If it were a definer's rights subprogram, the current user would be , and the subprogram would connect to the Dallas database as global user .

Using Object Types with Invoker's Rights Subprograms

To define object types for use in any schema, specify the clause. (For more information about object types, see Chapter 12, " Using PL/SQL Object Types".) Suppose user creates the following object type:

CREATE TYPE Num AUTHID CURRENT_USER AS OBJECT ( x NUMBER, STATIC PROCEDURE new_num ( n NUMBER, schema_name VARCHAR2, table_name VARCHAR2) ); / CREATE TYPE BODY Num AS STATIC PROCEDURE new_num ( n NUMBER, schema_name VARCHAR2, table_name VARCHAR2) IS sql_stmt VARCHAR2(200); BEGIN sql_stmt := 'INSERT INTO ' || schema_name || '.' || table_name || ' VALUES (blake.Num(:1))'; EXECUTE IMMEDIATE sql_stmt USING n; END; END; /

Then, user grants the privilege on object type to user :

GRANT EXECUTE ON Num TO scott;

Finally, user creates an object table to store objects of type , then calls procedure to populate the table:

CONNECT scott/tiger; CREATE TABLE num_tab OF blake.Num; / BEGIN blake.Num.new_num(1001, 'scott', 'num_tab'); blake.Num.new_num(1002, 'scott', 'num_tab'); blake.Num.new_num(1003, 'scott', 'num_tab'); END; /

The calls succeed because the procedure executes with the privileges of its current user (), not its owner ().

For subtypes in an object type hierarchy, the following rules apply:

  • If a subtype does not explicitly specify an clause, it inherits the of its supertype.

  • If a subtype does specify an clause, its must match the of its supertype. Also, if the is , both the supertype and subtype must have been created in the same schema.

Calling Invoker's Rights Instance Methods

An invoker's rights instance method executes with the privileges of the invoker, not the creator of the instance. Suppose that is an invoker's rights object type, and that user creates , an object of type . If user calls instance method to operate on object , the current user of the method is , not . Consider the following example:

-- user blake creates a definer-rights procedure CREATE PROCEDURE reassign (p Person, new_job VARCHAR2) AS BEGIN -- user blake calls method change_job, so the -- method executes with the privileges of blake p.change_job(new_job); ... END; / -- user scott passes a Person object to the procedure DECLARE p1 Person; BEGIN p1 := Person(...); blake.reassign(p1, 'CLERK'); ... END; /

Using Recursion with PL/SQL

Recursion is a powerful technique for simplifying the design of algorithms. Basically, recursion means self-reference. In a recursive mathematical sequence, each term is derived by applying a formula to preceding terms. The Fibonacci sequence (0, 1, 1, 2, 3, 5, 8, 13, 21, ...), is an example. Each term in the sequence (after the second) is the sum of the two terms that immediately precede it.

In a recursive definition, something is defined as simpler versions of itself. Consider the definition of n factorial (n!), the product of all integers from 1 to n:

n! = n * (n - 1)!

What Is a Recursive Subprogram?

A recursive subprogram is one that calls itself. Each recursive call creates a new instance of any items declared in the subprogram, including parameters, variables, cursors, and exceptions. Likewise, new instances of SQL statements are created at each level in the recursive descent.

Be careful where you place a recursive call. If you place it inside a cursor loop or between and statements, another cursor is opened at each call, which might exceed the limit set by the Oracle initialization parameter .

There must be at least two paths through a recursive subprogram: one that leads to the recursive call and one that does not. At least one path must lead to a terminating condition. Otherwise, the recursion would go on until PL/SQL runs out of memory and raises the predefined exception .

Calling External Subprograms

Although PL/SQL is a powerful, flexible language, some tasks are more easily done in another language. Low-level languages such as C are very fast. Widely used languages such as Java have reusable libraries for common design patterns.

You can use PL/SQL call specs to invoke external subprograms written in other languages, making their capabilities and libraries available from PL/SQL.

For example, you can call Java stored procedures from any PL/SQL block, subprogram, or package. Suppose you store the following Java class in the database:

import java.sql.*; import oracle.jdbc.driver.*; public class Adjuster { public static void raiseSalary (int empNo, float percent) throws SQLException { Connection conn = new OracleDriver().defaultConnection(); String sql = "UPDATE emp SET sal = sal * ? WHERE empno = ?"; try { PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setFloat(1, (1 + percent / 100)); pstmt.setInt(2, empNo); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) {System.err.println(e.getMessage());} } }

The class has one method, which raises the salary of an employee by a given percentage. Because is a method, you publish it as a procedure using this call spec:

CREATE PROCEDURE raise_salary (empno NUMBER, pct NUMBER) AS LANGUAGE JAVA NAME 'Adjuster.raiseSalary(int, float)';

You might call procedure from an anonymous PL/SQL block:

DECLARE emp_id NUMBER; percent NUMBER; BEGIN -- get values for emp_id and percent raise_salary(emp_id, percent); -- call external subprogram END; /

External C subprograms are used to interface with embedded systems, solve engineering problems, analyze data, or control real-time devices and processes. External C subprograms extend the functionality of the database server, and move computation-bound programs from client to server, where they execute faster.

For more information about Java stored procedures, see Oracle Database Java Developer's Guide. For more information about external C subprograms, see Oracle Database Application Developer's Guide - Fundamentals.

Creating Dynamic Web Pages with PL/SQL Server Pages

PL/SQL Server Pages (PSPs) enable you to develop Web pages with dynamic content. They are an alternative to coding a stored procedure that writes out the HTML code for a web page, one line at a time.

Using special tags, you can embed PL/SQL scripts into HTML source code. The scripts are executed when the pages are requested by Web clients such as browsers. A script can accept parameters, query or update the database, then display a customized page showing the results.

During development, PSPs can act like templates with a static part for page layout and a dynamic part for content. You can design the layouts using your favorite HTML authoring tools, leaving placeholders for the dynamic content. Then, you can write the PL/SQL scripts that generate the content. When finished, you simply load the resulting PSP files into the database as stored procedures.

For more information about creating and using PSPs, see Oracle Database Application Developer's Guide - Fundamentals.

Controlling Side Effects of PL/SQL Subprograms

To be callable from SQL statements, a stored function (and any subprograms called by that function) must obey certain "purity" rules, which are meant to control side effects:

  • When called from a statement or a parallelized , , or statement, the function cannot modify any database tables.

  • When called from an , , or statement, the function cannot query or modify any database tables modified by that statement.

  • When called from a , , , or statement, the function cannot execute SQL transaction control statements (such as ), session control statements (such as ), or system control statements (such as ). Also, it cannot execute DDL statements (such as ) because they are followed by an automatic commit.

If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed).

To check for violations of the rules, you can use the pragma (compiler directive) . The pragma asserts that a function does not read or write database tables or package variables. For example, the following pragma asserts that packaged function writes no database state () and reads no package state ():

CREATE PACKAGE loans AS FUNCTION credit_ok RETURN BOOLEAN; PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS, RNPS); END loans; /

Note: A static , , or statement always violates . It also violates (reads no database state) if it reads any columns. A dynamic , , or statement always violates and .

For full syntax details, see "RESTRICT_REFERENCES Pragma". For more information about the purity rules, see Oracle Database Application Developer's Guide - Fundamentals.

Understanding Subprogram Parameter Aliasing

To optimize a subprogram call, the PL/SQL compiler can choose between two methods of parameter passing. With the by-value method, the value of an actual parameter is passed to the subprogram. With the by-reference method, only a pointer to the value is passed; the actual and formal parameters reference the same item.

The compiler hint increases the possibility of aliasing (that is, having two different names refer to the same memory location). This can occur when a global variable appears as an actual parameter in a subprogram call and then is referenced within the subprogram. The result is indeterminate because it depends on the method of parameter passing chosen by the compiler.

Example 8-7 Aliasing from Passing Global Variable with NOCOPY Hint

In the example below, procedure refers to varray both as a parameter and as a global variable. When is called, the identifiers and point to the same varray.

DECLARE TYPE Definition IS RECORD ( word VARCHAR2(20), meaning VARCHAR2(200)); TYPE Dictionary IS VARRAY(2000) OF Definition; lexicon Dictionary := Dictionary(); PROCEDURE add_entry (word_list IN OUT NOCOPY Dictionary) IS BEGIN word_list(1).word := 'aardvark'; lexicon(1).word := 'aardwolf'; END; BEGIN lexicon.EXTEND; add_entry(lexicon); dbms_output.put_line(lexicon(1).word); END; /

The program prints if the compiler obeys the hint. The assignment to is done immediately through a pointer, then is overwritten by the assignment to .

The program prints if the hint is omitted, or if the compiler does not obey the hint. The assignment to uses an internal copy of the varray, which is copied back to the actual parameter (overwriting the contents of ) when the procedure ends.

Example 8-8 Aliasing Passing Same Parameter Multiple Times

Aliasing can also occur when the same actual parameter appears more than once in a subprogram call. In the example below, is an parameter, so the value of the actual parameter is not updated until the procedure exits. That is why the first prints 10 (the initial value of ) and the third prints 20. However, is a parameter, so the value of the actual parameter is updated immediately. That is why the second prints 30.

DECLARE n NUMBER := 10; PROCEDURE do_something ( n1 IN NUMBER, n2 IN OUT NUMBER, n3 IN OUT NOCOPY NUMBER) IS BEGIN n2 := 20; dbms_output.put_line(n1); -- prints 10 n3 := 30; dbms_output.put_line(n1); -- prints 30 END; BEGIN do_something(n, n, n); dbms_output.put_line(n); -- prints 20 END; /

Example 8-9 Aliasing from Assigning Cursor Variables to Same Work Area

Because they are pointers, cursor variables also increase the possibility of aliasing. In the following example, after the assignment, is an alias of ; both point to the same query work area. The first fetch from fetches the third row, not the first, because the first two rows were already fetched from . The second fetch from fails because is closed.

PROCEDURE get_emp_data ( emp_cv1 IN OUT EmpCurTyp, emp_cv2 IN OUT EmpCurTyp) IS emp_rec employees%ROWTYPE; BEGIN OPEN emp_cv1 FOR SELECT * FROM employees; emp_cv2 := emp_cv1; FETCH emp_cv1 INTO emp_rec; -- fetches first row FETCH emp_cv1 INTO emp_rec; -- fetches second row FETCH emp_cv2 INTO emp_rec; -- fetches third row CLOSE emp_cv1; FETCH emp_cv2 INTO emp_rec; -- raises INVALID_CURSOR END; /

This section discusses the PL/SQL language, which can be use to develop applications for Oracle Database Express Edition.

Overview of PL/SQL

PL/SQL is an Oracle's procedural language extension to SQL. It is a server-side, stored procedural language that is easy-to-use, seamless with SQL, portable, and secure.

PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can create and run PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous blocks, stored functions, stored procedures, and packages.

The following can be constructed with the PL/SQL language:

  • Anonymous block

    An anonymous block is a PL/SQL block that appears in your application and is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear. A PL/SQL block groups related declarations and statements. Because these blocks are not stored in the database, they are generally for one-time use.

  • Stored or standalone procedure and function

    A stored procedure or function is a PL/SQL block that Oracle Database XE stores in the database and can be called by name from an application. Functions are different than procedures in that functions return a value when executed. When you create a stored procedure or function, Oracle Database XE parses the procedure or function, and stores its parsed representation in the database. See Chapter 5, "Using Procedures, Functions, and Packages".

  • Package

    A package is a group of procedures, functions, and variable definitions that Oracle Database XE stores in the database. Procedures, functions, and variables in packages can be called from other packages, procedures, or functions. See Chapter 5, "Using Procedures, Functions, and Packages".

  • Trigger

    A database trigger is a stored procedure associated with a database table, view, or event. The trigger can be called after the event, to record it, or take some follow-up action. The trigger can be called before the event, to prevent erroneous operations or fix new data so that it conforms to business rules. See Chapter 6, "Using Triggers".

Entering and Running PL/SQL Code

You can enter and run PL/SQL code from the SQL Commands page, Script Editor page, or SQL Command Line (SQL*Plus).

Using the SQL Commands page is described in this section. The SQL Commands page is a simpler interface and easier to use.

Both SQL Commands and Script Editor pages enable you to save your SQL statements as a script file in a database repository for future use. You can run multiple SQL statements in the Script Editor page. Script Editor also enables you to download the script to the local file system. For information about using the Script Editor page, see "Running SQL Statements in the Script Editor Page".

You can create a text file of the PL/SQL code with the Script Editor page or a text editor to run as a SQL script from SQL Command Line. Using a script makes correcting mistakes easier because you only need to make the necessary updates to correct the problem, rather than entering again all the PL/SQL code at the SQL Command Line prompt. For information about using SQL Command Line and running SQL scripts from SQL Command Line, see Appendix A, "Using SQL Command Line".

This section contains the following topic:

Running PL/SQL Code in the SQL Commands Page

To enter and run PL/SQL code in the SQL Commands page:

  1. Log in to the Database Home Page. See "Logging in to the Database Home Page".

  2. On the home page, click the SQL icon to display the SQL page.

  3. Click the SQLCommands icon to display the SQL Commands page.

  4. On the SQL Commands page, enter the PL/SQL code in Example 4-1. Note that some of the lines of code are terminated with a semi colon (;) and the entire code unit is terminated with a slash (/). The slash is required when running the PL/SQL in a SQL script or at the SQL Command Line prompt, but it is optional on the SQL Commands page.

  5. Click the Run button to run the PL/SQL code. If necessary, select (highlight) only the PL/SQL code block before clicking the Run button. Any comments outside the PL/SQL code block are not legal in the SQL Commands page.


    Description of the illustration xe_plsql_commands.gif

  6. If you want to save the PL/SQL code for future use, click the Save button.

  7. In the Name field, enter a name for the saved PL/SQL code. You can also enter an optional description. Click the Save button to save the SQL.

  8. To access saved PL/SQL code, click the SavedSQL tab, and select the name of the saved PL/SQL code that you want to access.

Using the Main Features of PL/SQL

PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages. You can control program flow with statements, such as and . As with other procedural programming languages, you can declare variables, define procedures and functions, and trap run time errors.

PL/SQL lets you break complex problems down into understandable procedural code, and reuse this code across multiple applications. When a problem can be solved through plain SQL, you can issue SQL statements directly inside your PL/SQL programs, without learning new APIs. PL/SQL datatypes correspond with SQL column types, enabling you to interchange PL/SQL variables with data inside a table.

This section contains the following topics:

Using the PL/SQL Block Structure

As Example 4-1 shows, a PL/SQL block has three basic parts: a declarative part (), an executable part ( ... ), and an exception-handling () part that handles error conditions. For a discussion about exception handling, see "Handling PL/SQL Errors".

Only the executable part is required. The optional declarative part is written first, where you define types, variables, and similar items. These items are manipulated in the executable part. Errors that occur during execution can be dealt with in the exception-handling part.

Note the comments that are added to the PL/SQL code. See "Using Comments". Also, note the use of to display output. See "Inputting and Outputting Data with PL/SQL".

Example 4-1 Using a Simple PL/SQL Block

-- the following is an optional declarative part DECLARE monthly_salary NUMBER(6); number_of_days_worked NUMBER(2); pay_per_day NUMBER(6,2); -- the following is the executable part, from BEGIN to END BEGIN monthly_salary := 2290; number_of_days_worked := 21; pay_per_day := monthly_salary/number_of_days_worked; -- the following displays output from the PL/SQL block DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day)); -- the following is an optional exception part that handles errors EXCEPTION WHEN ZERO_DIVIDE THEN pay_per_day := 0; END; /

For another example of a PL/SQL block structure, see Example 4-13.

Inputting and Outputting Data with PL/SQL

Most PL/SQL input and output is through SQL statements, to store data in database tables or to query those tables. All other PL/SQL I/O is done through APIs that interact with other programs. For example, the package has procedures such as . To see the result outside of PL/SQL requires another program, such as the SQL Commands page or SQL Command Line (SQL*Plus), to read and display the data passed to .

The SQL Commands page is configured to display output with . SQL Command Line does not display data unless you first issue the SQL*Plus command . For information about SQL Command Line command, see "SQL Command Line SET Commands" .

Example 4-2 show the use of . Note the use of to enable output.

Example 4-2 Using DBMS_OUTPUT.PUT_LINE to Display PL/SQL Output

-- enable SERVEROUTPUT in SQL Command Line (SQL*Plus) to display output with -- DBMS_OUTPUT.PUT_LINE, this enables SERVEROUTPUT for this SQL*Plus session only SETSERVEROUTPUTON DECLARE answer VARCHAR2(20); -- declare a variable BEGIN -- assign a value to a variable answer := 'Maybe'; -- use PUT_LINE to display data from the PL/SQL block DBMS_OUTPUT.PUT_LINE( 'The answer is: ' || answer ); END; /

The package is a predefined Oracle package. For information about Oracle supplied packages, see "Oracle Provided Packages".

Using Comments

The PL/SQL compiler ignores comments, but you should not. Adding comments to your program improves readability and helps others understand your code. Generally, you use comments to describe the purpose and use of each code segment. PL/SQL supports single-line and multiple-line comment styles.

Single-line comments begin with a double hyphen () anywhere on a line and extend to the end of the line. Multiple-line comments begin with a slash and an asterisk (), end with an asterisk and a slash (), and can span multiple lines. See Example 4-3.

Example 4-3 Using Comments in PL/SQL

DECLARE -- Declare variables here. monthly_salary NUMBER(6); -- This is the monthly salary. number_of_days_worked NUMBER(2); -- This is the days in one month. pay_per_day NUMBER(6,2); -- Calculate this value. BEGIN -- First assign values to the variables. monthly_salary := 2290; number_of_days_worked := 21; -- Now calculate the value on the following line. pay_per_day := monthly_salary/number_of_days_worked; -- the following displays output from the PL/SQL block DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day)); EXCEPTION /* This is a simple example of an exeception handler to trap division by zero. In actual practice, it would be best to check whether a variable is zero before using it as a divisor. */ WHEN ZERO_DIVIDE THEN pay_per_day := 0; -- set to 0 if divisor equals 0 END; /

While testing or debugging a program, you might want to disable a line of code. The following example shows how you can disable a single line by making it a comment:

You can use multiple-line comment delimiters to comment out large sections of code.

Declaring Variables and Constants

Variables can have any SQL datatype, such as , , or , or a PL/SQL-only datatype, such as a or . You can also declare nested tables, variable-size arrays (varrays for short), and records using the , , and composite datatypes. See "Working With PL/SQL Data Structures".

Declaring a constant is similar to declaring a variable except that you must add the keyword and immediately assign a value to the constant. No further assignments to the constant are allowed. For an example, see the constant in Example 4-4.

For example, assume that you want to declare variables for employee data, such as to hold 6-digit numbers and to hold the Boolean value or . You declare these and related employee variables and constants, as shown in Example 4-4.

Note that there is a semi colon (;) at the end of each line in the declaration section. Also, note the use of the statement that enables you to run and test the PL/SQL block.

You can choose any naming convention for variables that is appropriate for your application, but the names must be valid PL/SQL identifiers. See "Using Identifiers in PL/SQL".

Example 4-4 Declaring Variables in PL/SQL

DECLARE -- declare the variables in this section last_name VARCHAR2(30); first_name VARCHAR2(25); employee_id NUMBER(6); active_employee BOOLEAN; monthly_salary NUMBER(6); number_of_days_worked NUMBER(2); pay_per_day NUMBER(6,2); avg_days_worked_month CONSTANT NUMBER(2) := 21; -- a constant variable BEGIN NULL; -- NULL statement does nothing, allows this block to executed and tested END; /

Using Identifiers in PL/SQL

You use identifiers to name PL/SQL program items and units, such as constants, variables, exceptions, and subprograms. An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Characters such as ampersands (&), hyphens (-), slashes (/), and spaces ( ) are not allowed.

You can use uppercase, lowercase, or mixed case to write identifiers. PL/SQL is not case-sensitive except within string and character literals. Every character, including dollar signs, underscores, and number signs, is significant. If the only difference between identifiers is the case of corresponding letters, PL/SQL considers the identifiers the same.

The declaration section in Example 4-5 show some PL/SQL identifiers. You can see additional examples of identifiers for variable names in Example 4-3 and Example 4-4.

Example 4-5 Using Identifiers for Variables in PL/SQL

DECLARE lastname VARCHAR2(30); -- valid identifier last_name VARCHAR2(30); -- valid identifier, _ allowed last$name VARCHAR2(30); -- valid identifier, $ allowed last#name VARCHAR2(30); -- valid identifier, # allowed -- last-name is invalid, hypen not allowed -- last/name is invalid, slash not allowed -- last name is invalid, space not allowed -- LASTNAME is invalid, same as lastname and LastName -- LastName is invalid, same as lastname and LASTNAME BEGIN NULL; -- NULL statement does nothing, allows this block to executed and tested END; /

The size of an identifier cannot exceed 30 characters. Identifiers should be descriptive. When possible, avoid obscure names such as . Instead, use meaningful names such as . You can use prefixes for more clarification. For example, you could begin each variable name with the and each constant name with .

Some identifiers, called reserved words or keywords, have a special syntactic meaning to PL/SQL. For example, the words and are reserved. Often, reserved words and keywords are written in upper case for readability. Neither reserved words or keywords should be used as identifiers and the use can cause compilation errors. For a list of PL/SQL reserved words and keywords, see Appendix B, "Reserved Words".

Assigning Values to a Variable With the Assignment Operator

You can assign values to a variable in several ways. One way uses the assignment operator (), a colon followed by an equal sign, as shown in Example 4-6. You place the variable to the left of the operator and an expression, including function calls, to the right. Note that you can assign a value to a variable when it is declared.

Example 4-6 Assigning Values to Variables With the PL/SQL Assignment Operator

DECLARE -- declare and assiging variables wages NUMBER(6,2); hours_worked NUMBER := 40; hourly_salary NUMBER := 22.50; bonus NUMBER := 150; country VARCHAR2(128); counter NUMBER := 0; done BOOLEAN := FALSE; valid_id BOOLEAN; BEGIN wages := (hours_worked * hourly_salary) + bonus; -- compute wages country := 'France'; -- assign a string literal country := UPPER('Canada'); -- assign an uppercase string literal done := (counter > 100); -- assign a BOOLEAN, in this case FALSE valid_id := TRUE; -- assign a BOOLEAN END; /

Using Literals

A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. For example, is a numeric literal, and is a Boolean literal.

Numeric Literals

Two kinds of numeric literals can be used in arithmetic expressions: integer and real. An integer literal is an optionally signed whole number without a decimal point, such as . A real literal is an optionally signed whole or fractional number with a decimal point, such as . PL/SQL considers a number such as to be real, even though it has an integral value.

Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Add an (or ) after the base number, followed by an optionally signed integer, for example . The (or e) represents the base number ten and the following integer represents the exponent.

Example 4-7 shows some examples of numeric literals.

Example 4-7 Using Numeric Literals in PL/SQL

DECLARE -- declare and assign variables number1 PLS_INTEGER := 32000; -- numeric literal number2 NUMBER(8,3); BEGIN number2 := 3.125346e3; -- numeric literal number2 := -8300.00; -- numeric literal number2 := -14; -- numeric literal END; /

Character Literals

A character literal is an individual character enclosed by single quotation marks (apostrophes), such as or . Character literals include all the printable characters in the PL/SQL character set: letters, numbers, spaces, and special symbols.

PL/SQL is case-sensitive within character literals. For example, PL/SQL considers the character literals and to be different. The character literals ... are not equivalent to integer literals, but can be used in arithmetic expressions because they are implicitly convertible to integers.

Example 4-8 shows some examples of character literals.

Example 4-8 Using Character Literals in PL/SQL

DECLARE -- declare and assign variables char1 VARCHAR2(1) := 'x'; -- character literal char2 VARCHAR2(1); BEGIN char2 := '5'; -- character literal END; /

String Literals

A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotation marks, such as and .

PL/SQL is case-sensitive within string literals. For example, PL/SQL considers the string literals and to be different:

To represent an apostrophe within a string, you can use two single quotation marks (), which is not the same as a quotation mark (). You can also use the quote-delimiter mechanism, which enables you to specify or followed by a single quotation mark and then another character to be used as the quotation mark delimiter. See "Using Character Literals in SQL Statements".

Example 4-9 shows some examples of string literals.

Example 4-9 Using String Literals in PL/SQL

DECLARE -- declare and assign variables string1 VARCHAR2(1000); string2 VARCHAR2(32767); BEGIN string1 := '555-111-2323'; -- the following needs two single quotation marks to represent one in the string string2 := 'Here''s an example of two single quotation marks used in a string.'; END; /

BOOLEAN Literals

literals are the predefined values: , , and . is a missing, unknown, or inapplicable value. literals are values, not strings.

Example 4-10 shows some examples of literals.

Example 4-10 Using BOOLEAN Literals in PL/SQL

DECLARE -- declare and assign variables finished BOOLEAN := TRUE; -- BOOLEAN literal complete BOOLEAN; -- BOOLEAN literal true_or_false BOOLEAN; BEGIN finished := FALSE; -- BOOLEAN literal set to FALSE complete := NULL; -- BOOLEAN literal with unknown value true_or_false := (3 = 4); -- BOOLEAN literal set to FALSE true_or_false := (3 < 4); -- BOOLEAN literal set to TRUE END; /

Date-time Literals

Date-time literals have various formats depending on the date-time datatype used, such as or .

Example 4-11 shows some examples of date-time literals.

Example 4-11 Using Date-time Literals in PL/SQL

DECLARE -- declare and assign variables date1 DATE := '11-AUG-2005'; -- DATE literal time1 TIMESTAMP; time2 TIMESTAMP WITH TIME ZONE; BEGIN time1 := '11-AUG-2005 11:01:01 PM'; -- TIMESTAMP literal time2 := '11-AUG-2005 09:26:56.66 PM +02:00'; -- TIMESTAMP WITH TIME ZONE END; /

Declaring Variables With the DEFAULT Keyword or NOT NULL Constraint

You can use the keyword instead of the assignment operator to initialize variables when they are declared. Use for variables that have a typical value. Use the assignment operator for variables (such as counters and accumulators) that have no typical value. You can also use to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

In addition to assigning an initial value, declarations can impose the constraint so that assigning a causes an error. The constraint must be followed by an initialization clause.

In Example 4-12 the declaration for the variable uses the to assign a value of 21 and the declarations for the and variables use the constraint.

Example 4-12 Using DEFAULT and NOT NULL in PL/SQL

DECLARE -- declare and assign variables last_name VARCHAR2(30); first_name VARCHAR2(25); employee_id NUMBER(6); active_employee BOOLEAN NOTNULL := TRUE; -- value cannot be NULL monthly_salary NUMBER(6) NOTNULL := 2000; -- value cannot be NULL number_of_days_worked NUMBER(2); pay_per_day NUMBER(6,2); employee_count NUMBER(6) := 0; avg_days_worked_month NUMBER(2) DEFAULT 21; -- assign a default value BEGIN NULL; -- NULL statement does nothing, allows this block to executed and tested END; /

Assigning Values to a Variable With the PL/SQL SELECT INTO Statement

Another way to assign values to a variable is by selecting (or fetching) database values into it. With the PL/SQL statement, you can retrieve data from one row in a table. In Example 4-13, 10 percent of the salary of an employee is selected into the variable. Now, you can use the variable in another computation, or insert its value into a database table.

In the example, the procedure is used to display output from the PL/SQL program. For more information, see "Inputting and Outputting Data with PL/SQL".

Example 4-13 Assigning Values to Variables Using PL/SQL SELECT INTO

DECLARE -- declare and assign values bonus_rate CONSTANT NUMBER(2,3) := 0.05; bonus NUMBER(8,2); emp_id NUMBER(6) := 120; -- assign a test value for employee ID BEGIN -- retreive a salary from the employees table, then calculate the bonus and -- assign the value to the bonus variable SELECT salary * bonus_rate INTO bonus FROM employees WHERE employee_id = emp_id; -- display the employee_id, bonus amount, and bonus rate DBMS_OUTPUT.PUT_LINE ( 'Employee: ' || TO_CHAR(emp_id) || ' Bonus: ' || TO_CHAR(bonus) || ' Bonus Rate: ' || TO_CHAR(bonus_rate)); END; /

Using %TYPE and %ROWTYPE Attributes to Declare Identical Datatypes

As part of the declaration for each PL/SQL variable, you declare its datatype. Usually, this datatype is one of the types shared between PL/SQL and SQL, such as or . For easier code maintenance that interacts with the database, you can also use the special qualifiers and to declare variables that hold table columns or table rows.

This section contains the following topics:

Using the %TYPE Attribute to Declare Variables

The attribute provides the datatype of a variable or table column. This is particularly useful when declaring variables that will hold values of a table column. For example, suppose you want to declare variables as the same datatype as the and columns in table. To declare variables named and that have the same datatype as the table columns, use dot notation and the attribute. See Example 4-14.

Example 4-14 Using %TYPE With Table Columns in PL/SQL

DECLARE -- declare variables using %TYPE attribute empid employees.employee_id%TYPE; -- employee_id datatype is NUMBER(6) emplname employees.last_name%TYPE; -- last_name datatype is VARCHAR2(25) BEGIN empid := 100301; -- this is OK because it fits in NUMBER(6) -- empid := 3018907; -- this is too large and will cause an overflow emplname := 'Patel'; -- this is OK because it fits in VARCHAR2(25) DBMS_OUTPUT.PUT_LINE('Employee ID: ' || empid); -- display data DBMS_OUTPUT.PUT_LINE('Employee name: ' || emplname); -- display data END; /

Declaring variables with the attribute has two advantages. First, you do not need to know the exact datatype of the table columns. Second, if you change the database definition of columns, such as or , the datatypes of and in Example 4-14 change accordingly at run time.

Using the %ROWTYPE Attribute to Declare Variables

For easier maintenance of code that interacts with the database, you can use the attribute to declare a variable that represents a row in a table. A PL/SQL record is the datatype that stores the same information as a row in a table.

In PL/SQL, records are used to group data. A record consists of a number of related fields in which data values can be stored. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. For information about records, see "Using Record Types".

Columns in a row and corresponding fields in a record have the same names and datatypes. In Example 4-15, you declare a record named . Its fields have the same names and datatypes as the columns in the table. You use dot notation to reference fields, such as .

In Example 4-15, the statement is used to store row information from the table into the record. When you run the statement, the value in the column of the table is assigned to the field of ; the value in the column is assigned to the field of ; and so on.

Example 4-15 Using %ROWTYPE with a PL/SQL Record

DECLARE -- declare variables -- declare record variable that represents a row fetched from the employees table emp_rec employees%ROWTYPE; -- declare variable with %ROWTYPE attribute BEGIN SELECT * INTO emp_rec FROM EMPLOYEES WHERE employee_id = 120; -- retrieve record DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_rec.first_name || ' ' || emp_rec.last_name); -- display END; /

Declaring variables with the attribute has several advantages. First, you do not need to know the exact datatype of the table columns. Second, if you change the database definition of any of the table columns, the datatypes associated with the declaration change accordingly at run time.

Using PL/SQL Control Structures

Control structures are the most important PL/SQL extension to SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data using conditional, iterative, and sequential flow-of-control statements such as , , , , , and .

This section contains the following topics:

Conditional Control With IF-THEN

Often, it is necessary to take alternative actions depending on circumstances. The statement lets you run a sequence of statements conditionally. The forms of the statement can be , , or . The clause checks a condition; the clause defines what to do if the condition is true; and the clause defines what to do if the condition is false or null. Example 4-16 shows a simple use of the statement.

Example 4-16 Using a Simple IF-THEN Statement in PL/SQL

DECLARE sal NUMBER(8,2); bonus NUMBER(6,2); hiredate DATE; empid NUMBER(6) := 128; -- use employee 120 for testing BEGIN -- retrieve the salary and the date that employee was hired, the date is checked -- to calculate the amount of the bonus for the employee SELECT salary, hire_date INTO sal, hiredate FROM employees WHERE employee_id = empid; IF hiredate > TO_DATE('01-JAN-00') THEN bonus := sal/20; DBMS_OUTPUT.PUT_LINE('Bonus for employee: ' || empid || ' is: ' || bonus ); ENDIF; END; /

Example 4-17 shows the use of to determine the salary raise an employee receives based on the hire date of the employee.

Example 4-17 Using the IF-THEN-ELSEIF Statement in PL/SQL

DECLARE bonus NUMBER(6,2); empid NUMBER(6) := 120; hiredate DATE; BEGIN -- retrieve the date that employee was hired, the date is checked -- to determine the amount of the bonus for the employee SELECT hire_date INTO hiredate FROM employees WHERE employee_id = empid; IF hiredate > TO_DATE('01-JAN-98') THEN bonus := 500; ELSIF hiredate > TO_DATE('01-JAN-96') THEN bonus := 1000; ELSE bonus := 1500; ENDIF; DBMS_OUTPUT.PUT_LINE('Bonus for employee: ' || empid || ' is: ' || bonus ); END; /

Conditional Control With the CASE Statement

To choose among several values or courses of action, you can use constructs. The expression evaluates a condition and returns a value for each case. The case statement evaluates a condition, and performs an action, such as an entire PL/SQL block, for each case. When possible, rewrite lengthy statements as statements because the statement is more readable and more efficient.

Example 4-18 shows a simple statement.

Example 4-18 Using the CASE-WHEN Statement in PL/SQL

DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); ENDCASE; END; /

Example 4-19 determines the salary raise an employee receives based on the current salary of the employee and the job ID. This complex example combines the expression with statements.

Example 4-19 Using the IF-THEN_ELSE and CASE Statement in PL/SQL

DECLARE -- declare variables empid NUMBER(6) := 115; jobid VARCHAR2(10); sal NUMBER(8,2); sal_raise NUMBER(3,2); -- this is the rate of increase for the raise BEGIN -- retrieve the job ID and salary for the employee and -- assign the values to variables jobid and sal SELECT job_id, salary INTO jobid, sal from employees WHERE employee_id = empid; CASE -- determine the salary raise rate based on employee job ID WHEN jobid = 'PU_CLERK' THENIF sal < 3000 THEN sal_raise := .08; ELSE sal_raise := .07; ENDIF; WHEN jobid = 'SH_CLERK' THENIF sal < 4000 THEN sal_raise := .06; ELSE sal_raise := .05; END IF; WHEN jobid = 'ST_CLERK' THENIF sal < 3500 THEN sal_raise := .04; ELSE sal_raise := .03; ENDIF; ELSE BEGIN -- if no conditions met, then the following DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid); END; ENDCASE; -- display the percent raise for the employee DBMS_OUTPUT.PUT_LINE('Percent salary raise for employee: ' || empid || ' is: ' || sal_raise ); END; /

A sequence of statements that uses query results to select alternative actions is common in database applications. Another common sequence inserts or deletes a row only if an associated entry is found in another table. You can bundle these common sequences into a PL/SQL block using conditional logic.

Iterative Control With LOOPs

statements let you run a sequence of statements multiple times. You place the keyword before the first statement in the sequence and the keywords after the last statement in the sequence.

The statement lets you specify a range of integers, then run a sequence of statements once for each integer in the range. In Example 4-20, the loop displays the number and the square of the number for numbers 1 to 10. Note that you do not have to declare or initialize the counter in the and any valid identifier can be used for the name, such as .

Example 4-20 Using the FOR-LOOP in PL/SQL

BEGIN -- use a FOR loop to process a series of numbers FOR loop_counter IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(loop_counter) || ' Square: ' || TO_CHAR(loop_counter**2)); ENDLOOP; END; /

The statement associates a condition with a sequence of statements. Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.

In Example 4-21, the loop displays the number and the cube of the number while the number is less than or equal to 10.

Example 4-21 Using WHILE-LOOP for Control in PL/SQL

DECLARE -- declare variables i NUMBER := 1; -- loop counter, initialize to one i_cubed NUMBER; BEGIN -- use WHILE LOOP to process data WHILE i <= 10 LOOP i_cubed := i**3; DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(i) || ' Cube: ' || TO_CHAR(i_cubed)); i := i + 1; ENDLOOP; END; /

The statement lets you complete a loop if further processing is impossible or undesirable. When the statement is encountered, the condition in the clause is evaluated. If the condition is true, the loop completes and control passes to the next statement. In Example 4-22, the loop completes when the value of exceeds 25,000:

Example 4-22 Using the EXIT-WHEN Statement in PL/SQL

DECLARE -- declare and assign values to variables total NUMBER(9) := 0; counter NUMBER(6) := 0; BEGIN LOOP counter := counter + 1; -- increment counter variable total := total + counter * counter; -- compute total -- exit loop when condition is true EXITWHEN total > 25000; -- LOOP until condition is met ENDLOOP; DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' || TO_CHAR(total)); -- display results END; /

Sequential Control With GOTO

The statement lets you branch to a label unconditionally; however, you would usually try to avoid exiting a loop in this manner. The label, an undeclared identifier enclosed by double angle brackets, must precede an executable statement or a PL/SQL block. When executed, the statement transfers control to the labeled statement or block.

Example 4-23 shows the use of the statement in a loop that is testing for prime numbers. When a number can be divided into evenly (no remainder), then it is not a prime and the loop is immediately exited. Note the use of the SQL numeric function to check for no (zero) remainder. See "Using Numeric Functions" for information about SQL numeric functions.

Example 4-23 Using the GOTO Statement in PL/SQL

DECLARE -- declare variables p VARCHAR2(30); n PLS_INTEGER := 37; -- test any integer > 2 for prime, here 37 BEGIN -- loop through divisors to determine if a prime number FOR j in 2..ROUND(SQRT(n)) LOOP IF n MOD j = 0 THEN -- test for prime p := ' is NOT a prime number'; -- not a prime number GOTOprint_now; END IF; END LOOP; p := ' is a prime number'; <<print_now>> DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p); -- display results END; /

Using Local PL/SQL Procedures and Functions in PL/SQL Blocks

Procedures and functions (subprograms) are named PL/SQL blocks that can be called with a set of parameters from inside of a PL/SQL block.

A procedure is a subprogram that performs a specific action. You specify the name of the procedure, its parameters, its local variables, and the block that contains its code and handles any exceptions. A function is a subprogram that computes and returns a value. Functions and procedures are structured alike, except that functions return a value.

When passing parameters to functions and procedures, the parameters can be declared as or or parameters.

  • indicates that you must supply a value for the argument when calling the function or procedure. This is the default.

  • indicates that the function or procedure will set the value of the argument.

  • indicates that a value for the argument can be supplied by you and can be set by the function or procedure.

Example 4-24 is an example of a declaration of a PL/SQL procedure in a PL/SQL block. Note that the and variables are declared as parameters to a subprogram.

Example 4-24 Declaring a Local PL/SQL Procedure With IN OUT Parameters

DECLARE -- declare variables and subprograms fname VARCHAR2(20) := 'randall'; lname VARCHAR2(25) := 'dexter'; -- declare a local procedure which can only be used in this block PROCEDUREupper_name ( v1 INOUT VARCHAR2, v2 INOUT VARCHAR2) AS BEGIN v1 := UPPER(v1); -- change the string to uppercase v2 := UPPER(v2); -- change the string to uppercase ENDupper_name; -- start of executable part of block BEGIN DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display initial values upper_name (fname, lname); -- call the procedure with parameters DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname ); -- display new values END; /

Example 4-25 is an example of a declaration of a PL/SQL function in a PL/SQL block. Note that the value returned by the function is used directly in the statement. Note that the and variables are declared as parameters to a subprogram. An parameter passes an initial value that is read inside of a subprogram. Any update to the value of the parameter inside of the subprogram is not accessible outside of the subprogram.

Example 4-25 Declaring a Local PL/SQL Function With IN Parameters

DECLARE -- declare variables and subprograms fname VARCHAR2(20) := 'randall'; lname VARCHAR2(25) := 'dexter'; -- declare local function which can only be used in this block FUNCTIONupper_name ( v1 IN VARCHAR2, v2 IN VARCHAR2) RETURN VARCHAR2 AS v3 VARCHAR2(45); -- this variable is local to the function BEGIN -- build a string that will be returned as the function value v3 := v1 || ' + ' || v2 || ' = ' || UPPER(v1) || ' ' || UPPER(v2); RETURN v3; -- return the value of v3 ENDupper_name; -- start of executable part of block BEGIN -- call the function and display results DBMS_OUTPUT.PUT_LINE(upper_name (fname, lname)); END; /

In Example 4-26, both a variable and a numeric literal are passed as a parameter to a more complex procedure.

Example 4-26 Declaring a Complex Local Procedure in a PL/SQL Block

DECLARE -- declare variables and subprograms empid NUMBER; -- declare local procedure for this block PROCEDUREavg_min_max_sal (empid IN NUMBER) IS jobid VARCHAR2(10); avg_sal NUMBER; min_sal NUMBER; max_sal NUMBER; BEGIN -- determine the job ID for the employee SELECT job_id INTO jobid FROM employees WHERE employee_id = empid; -- calculate the average, minimum, and maximum salaries for that job ID SELECT AVG(salary), MIN(salary), MAX(salary) INTO avg_sal, min_sal, max_sal FROM employees WHERE job_id = jobid; -- display data DBMS_OUTPUT.PUT_LINE ('Employee ID: ' || empid || ' Job ID: ' || jobid); DBMS_OUTPUT.PUT_LINE ('The average salary for job ID: ' || jobid || ' is ' || TO_CHAR(avg_sal)); DBMS_OUTPUT.PUT_LINE ('The minimum salary for job ID: ' || jobid || ' is ' || TO_CHAR(min_sal)); DBMS_OUTPUT.PUT_LINE ('The maximum salary for job ID: ' || jobid || ' is ' || TO_CHAR(max_sal)); ENDavg_min_max_sal; -- end of local procedure -- start executable part of block BEGIN -- call the procedure with several employee IDs empid := 125; avg_min_max_sal(empid); avg_min_max_sal(112); END; /

Subprograms can also be declared in packages. For an example of a subprogram declaration in a package, see Example 5-9. You can create standalone subprograms that are stored in the database. These subprograms can be called from other subprograms, packages, and SQL statements. See Chapter 5, "Using Procedures, Functions, and Packages".

Using Cursors and Cursor Variables To Retrieve Data

A cursor is a name for a private SQL area in which information for processing the specific statement is kept. PL/SQL uses both implicit and explicit cursors. Cursor attributes return useful information about the status of cursors in the execution of SQL statements.

PL/SQL implicitly creates a cursor for all SQL data manipulation statements on a set of rows, including queries that return only one row. Implicit cursors are managed automatically by PL/SQL so you are not required to write any code to handle these cursors. However, you can track information about the execution of an implicit cursor through its cursor attributes.

You can explicitly declare a cursor for one row or multiple rows if you want precise control over query processing. You must declare an explicit cursor for queries that return more than one row. For queries that return multiple rows, you can process the rows individually.

A cursor variable () is similar to a cursor and points to the current row in the result set of a multi-row query.

This section contains the following topics:

Explicit Cursors

Example 4-27 is an example of explicit cursor used to process one row of a table.You should explicitly open and close a cursor before and after use.

Example 4-27 Fetching a Single Row With a Cursor in PL/SQL

DECLARE -- declare variables for first_name and last_name fetched from the employees table firstname employees.first_name%TYPE; -- variable for first_name lastname employees.last_name%TYPE; -- variable for last_name -- declare a cursor to fetch data from a row (employee 120) in the employees table CURSORcursor1 IS SELECT first_name, last_name FROM employees WHERE employee_id = 120; BEGIN OPENcursor1; -- open the cursor FETCHcursor1 INTO firstname, lastname; -- fetch data into local variables DBMS_OUTPUT.PUT_LINE('Employee name: ' || firstname || ' ' || lastname); CLOSEcursor1; -- close the cursor END; /

Example 4-28 shows examples of the use of a cursor to process multiple rows in a table. The statement retrieves the rows in the result set one at a time. Each fetch retrieves the current row and advances the cursor to the next row in the result set. Note the use of the cursor attributes and . For information about cursor attributes, see "Cursor Attributes".

Example 4-28 Fetching Multiple Rows With a Cursor in PL/SQL

DECLARE -- declare variables for data fetched from cursors empid employees.employee_id%TYPE; -- variable for employee_id jobid employees.job_id%TYPE; -- variable for job_id lastname employees.last_name%TYPE; -- variable for last_name rowcount NUMBER; -- declare the cursors CURSORcursor1 IS SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK'; CURSORcursor2 is SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%MAN' OR job_id LIKE '%MGR'; BEGIN -- start the processing with cursor1 OPENcursor1; -- open cursor1 before fetching DBMS_OUTPUT.PUT_LINE( '---------- cursor 1-----------------' ); LOOP FETCHcursor1 INTO lastname, jobid; -- fetches 2 columns into variables -- check the cursor attribute NOTFOUND for the end of data EXIT WHEN cursor1%NOTFOUND; -- display the last name and job ID for each record (row) fetched DBMS_OUTPUT.PUT_LINE( RPAD(lastname, 25, ' ') || jobid ); END LOOP; rowcount := cursor1%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount ); CLOSEcursor1; -- start the processing with cursor2 OPENcursor2; DBMS_OUTPUT.PUT_LINE( '---------- cursor 2-----------------' ); LOOP -- fetch 3 columns into the variables FETCHcursor2 INTO empid, lastname, jobid; EXIT WHEN cursor2%NOTFOUND; -- display the employee ID, last name, and job ID for each record (row) fetched DBMS_OUTPUT.PUT_LINE( empid || ': ' || RPAD(lastname, 25, ' ') || jobid ); END LOOP; rowcount := cursor2%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount ); CLOSEcursor2; END; /

In Example 4-28, the condition operator is used to specify the records to return with the query. For information about , see "Restricting Data Using the WHERE Clause".

Example 4-29 shows how to pass a parameter to an explicit cursor. In the example, the current month value is passed to the cursor to specify that only those employees hired during this month are displayed. This provides a list of employees that have their yearly anniversary dates and their bonus amount.

Example 4-29 Passing Parameters to a Cursor in PL/SQL

DECLARE -- declare variables for data fetched from cursor empid employees.employee_id%TYPE; -- variable for employee_id hiredate employees.hire_date%TYPE; -- variable for hire_date firstname employees.first_name%TYPE; -- variable for first_name lastname employees.last_name%TYPE; -- variable for last_name rowcount NUMBER; bonusamount NUMBER; yearsworked NUMBER; -- declare the cursor with a parameter, CURSORcursor1 (thismonthNUMBER)IS SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE EXTRACT(MONTH FROM hire_date) = thismonth; BEGIN -- open and pass a parameter to cursor1, select employees hired on this month OPENcursor1(EXTRACT(MONTH FROM SYSDATE)); DBMS_OUTPUT.PUT_LINE('----- Today is ' || TO_CHAR(SYSDATE, 'DL') || ' -----'); DBMS_OUTPUT.PUT_LINE('Employees with yearly bonus amounts:'); LOOP -- fetches 4 columns into variables FETCHcursor1 INTO empid, firstname, lastname, hiredate; -- check the cursor attribute NOTFOUND for the end of data EXIT WHEN cursor1%NOTFOUND; -- calculate the yearly bonus amount based on months (years) worked yearsworked := ROUND( (MONTHS_BETWEEN(SYSDATE, hiredate)/12) ); IF yearsworked > 10 THEN bonusamount := 2000; ELSIF yearsworked > 8 THEN bonusamount := 1600; ELSIF yearsworked > 6 THEN bonusamount := 1200; ELSIF yearsworked > 4 THEN bonusamount := 800; ELSIF yearsworked > 2 THEN bonusamount := 400; ELSIF yearsworked > 0 THEN bonusamount := 100; END IF; -- display the employee Id, first name, last name, hire date, and bonus -- for each record (row) fetched DBMS_OUTPUT.PUT_LINE( empid || ' ' || RPAD(firstname, 21, ' ') || RPAD(lastname, 26, ' ') || hiredate || TO_CHAR(bonusamount, '$9,999')); END LOOP; rowcount := cursor1%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('The number of rows fetched is ' || rowcount ); CLOSEcursor1; END; /

Cursor Variables (REF CURSORs)

Cursor variables () are like pointers to result sets. A cursor variable is more flexible than a cursor because it is not tied to a specific query. You can open a cursor variable for any query that returns the correct set of columns.

Cursor variables are used when you want to perform a query in one function or procedure, and process the results in a different subprogram, possibly in a different language. A cursor variable has the datatype , and is often referred to informally as a .

A can be declared with a return type (strong type) or without a return type (weak type). A strong type is less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of columns. A weak types is more flexible because the compiler lets you associate a weakly typed cursor variable with any query. Because there is no type checking with a weak , all such types are interchangeable. Instead of creating a new type, you can use the predefined type .

Example 4-30 show how to declare a cursor variable of datatype, then use that cursor variable as a formal parameter in a procedure. For additional examples of the use of , see "Accessing Types in Packages". For an example of the use of a with a PHP program, see Appendix C, "Using a PL/SQL Procedure With PHP". For an example of the use of a with a Java program, see Appendix D, "Using a PL/SQL Procedure With JDBC".

Example 4-30 Using a Cursor Variable (REF CURSOR)

DECLARE -- declare a REF CURSOR that returns employees%ROWTYPE (strongly typed) TYPE emp_refcur_typ IS REFCURSOR RETURN employees%ROWTYPE; emp_cursor emp_refcur_typ; -- use the following local procedure to process all the rows after -- the result set is built, rather than calling a procedure for each row PROCEDURE process_emp_cv (emp_cv IN emp_refcur_typ) IS person employees%ROWTYPE; BEGIN DBMS_OUTPUT.PUT_LINE('-- Here are the names from the result set --'); LOOP FETCH emp_cv INTO person; EXIT WHEN emp_cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE(person.last_name || ', ' || person.first_name); END LOOP; END; BEGIN -- find employees whose employee ID is less than 108 OPEN emp_cursor FOR SELECT * FROM employees WHERE employee_id < 108; process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing CLOSE emp_cursor; -- find employees whose last name starts with R OPEN emp_cursor FOR SELECT * FROM employees WHERE last_name LIKE 'R%'; process_emp_cv(emp_cursor); -- pass emp_cursor to the procedure for processing CLOSE emp_cursor; END; /

Cursor Attributes

Cursor attributes return information about the execution of DML and DDL statements, such , , , , , or statements. The cursor attributes are , , , and . These attributes return useful information about the most recently executed SQL statement. When using an explicit cursor, add the explicit cursor or cursor variable name to the beginning of the attribute, such as , to return information for the most recently executed SQL statement for that cursor.

The attributes provide the following information:

  • Attribute: Has a Row Been Fetched?

    After a cursor or cursor variable is opened but before the first fetch, returns . After any fetches, it returns if the last fetch returned a row, or if the last fetch did not return a row.

  • Attribute: Is the Cursor Open?

    If a cursor or cursor variable is open, then returns ; otherwise, returns .

    Note that implicit cursors are automatically opened before and closed after executing the associated SQL statement so always returns .

  • Attribute: Has a Fetch Failed?

    If the last fetch returned a row, then returns . If the last fetch failed to return a row, then returns . is the logical opposite of .

  • Attribute: How Many Rows Fetched So Far?

    After a cursor or cursor variable is opened, returns 0 before the first fetch. Thereafter, it returns the number of rows fetched so far. The number is incremented if the last fetch returned a row.

Working With PL/SQL Data Structures

Data structure are composite datatypes that let you work with the essential properties of data without being too involved with details. After you design a data structure, you can focus on designing algorithms that manipulate the data structure.

This section contains the following topics:

Using Record Types

Record types are composite data structures whose fields can have different datatypes. You can use records to hold related items and pass them to subprograms with a single parameter. When declaring records, you use the definition, as shown in Example 4-31.

Usually you would use a record to hold data from an entire row of a database table. You can use the attribute to declare a record that represents a row in a table or a row from a query result set, without specifying the names and types for the fields. When using , the record type definition is implied, and the keyword is not necessary, as shown in Example 4-32.

Example 4-31 shows how are records are declared and initialized.

Example 4-31 Declaring and Initializing a PL/SQL Record Type

DECLARE -- declare RECORD type variables -- the following is a RECORD declaration to hold address information TYPE location_rec ISRECORD ( room_number NUMBER(4), building VARCHAR2(25) ); -- you use the %TYPE attribute to declare the datatype of a table column -- you can include (nest) a record inside of another record TYPE person_rec ISRECORD ( employee_id employees.employee_id%TYPE, first_name employees.first_name%TYPE, last_name employees.last_name%TYPE, location location_rec ); person person_rec; -- declare a person variable of type person_rec BEGIN -- insert data in a record, one field at a time person.employee_id := 20; person.first_name := 'James'; person.last_name := 'Boynton'; person.location.room_number := 100; person.location.building:= 'School of Education'; -- display data in a record DBMS_OUTPUT.PUT_LINE( person.last_name || ', ' || person.first_name ); DBMS_OUTPUT.PUT_LINE( TO_CHAR(person.location.room_number) || ' ' || person.location.building ); END; /

Example 4-32 shows the use of in a record type declaration. This record is used with a cursor that fetches an entire row.

Example 4-32 Using %ROWTYPE With a Cursor When Declaring a PL/SQL Record

DECLARE -- declare variables CURSOR cursor1 IS SELECT * FROM employees WHERE department_id = 60; -- declare cursor -- declare record variable that represents a row fetched from the employees table -- do not need to use TYPE .. IS RECORD with %ROWTYPE attribute employee_rec cursor1%ROWTYPE; BEGIN -- open the explicit cursor c1 and use it to fetch data into employee_rec OPEN cursor1; LOOP FETCH cursor1 INTO employee_rec; -- retrieve entire row into record EXIT WHEN cursor1%NOTFOUND; -- the record contains all the fields for a row in the employees table -- the following displays the data from the row fetched into the record DBMS_OUTPUT.PUT_LINE( ' Department ' || employee_rec.department_id || ', Employee: ' || employee_rec.employee_id || ' - ' || employee_rec.last_name || ', ' || employee_rec.first_name ); END LOOP; CLOSE cursor1; END; /

Example 4-34 shows the use of record as an element in a varray.

Using Collections

PL/SQL collection types let you declare high-level datatypes similar to arrays, sets, and hash tables found in other languages. In PL/SQL, array types are known as varrays (short for variable-size arrays), set types are known as nested tables, and hash table types are known as associative arrays. Each kind of collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection. When declaring collections, you use a definition. To reference an element, use subscript notation with parentheses.

Example 4-33 shows the use of a varray with elements of character type. A varray must be initialized before use. When initializing a varry, you can also insert values into the elements. After initialization, you need to use to add additional elements before inserting more values into the varray.

Example 4-33 Using a PL/SQL VARRAY Type With Character Elements

DECLARE -- declare variables TYPE jobids_array ISVARRAY(20) OF VARCHAR2(10); -- declare VARRAY jobids jobids_array; -- declare a variable of type jobids_array howmany NUMBER; -- declare a variable to hold employee count BEGIN -- initialize the arrary with some job ID values jobids := jobids_array('AC_ACCOUNT', 'AC_MGR', 'AD_ASST', 'AD_PRES', 'AD_VP', 'FI_ACCOUNT', 'FI_MGR', 'HR_REP', 'IT_PROG', 'PU_MAN', 'SH_CLERK', 'ST_CLERK', 'ST_MAN'); -- display the current size of the array with COUNT DBMS_OUTPUT.PUT_LINE('The number of elements (current size) in the array is ' || jobids.COUNT); -- display the maximum number of elements for the array LIMIT DBMS_OUTPUT.PUT_LINE('The maximum number (limit) of elements in the array is ' || jobids.LIMIT); -- check whether another element can be added to the array IF jobids.LIMIT - jobids.COUNT >= 1 THEN jobids.EXTEND(1); -- add one more element jobids(14) := 'PU_CLERK'; -- assign a value to the element END IF; -- loop through all the varray values, starting -- with the FIRST and ending with the LAST element FOR i IN jobids.FIRST..jobids.LAST LOOP -- determine the number of employees for each job ID in the array SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobids(i); DBMS_OUTPUT.PUT_LINE ( 'Job ID: ' || RPAD(jobids(i), 10, ' ') || ' Number of employees: ' || TO_CHAR(howmany)); END LOOP; -- display the current size of the array with COUNT DBMS_OUTPUT.PUT_LINE('The number of elements (current size) in the array is ' || jobids.COUNT); END; /

Example 4-34 shows the use of a varray with record type elements.

Example 4-34 Using a PL/SQL VARRAY Type With Record Type Elements

DECLARE -- declare variables CURSOR cursor1 IS SELECT * FROM jobs; -- create a cursor for fetching the rows jobs_rec cursor1%ROWTYPE; -- create a record to hold the row data -- declare VARRAY with enough elements to hold all the rows in the jobs table TYPE jobs_array ISVARRAY(25) OF cursor1%ROWTYPE; jobs_arr jobs_array; -- declare a variable of type jobids_array howmany NUMBER; -- declare a variable to hold employee count i NUMBER := 1; -- counter for the number of elements in the array BEGIN jobs_arr := jobs_array(); -- initialize the array before using OPEN cursor1; -- open the cursor before using LOOP FETCH cursor1 INTO jobs_rec; -- retrieve a row from the jobs table EXIT WHEN cursor1%NOTFOUND; -- exit when no data is retrieved jobs_arr.EXTEND(1); -- add another element to the varray with EXTEND jobs_arr(i) := jobs_rec; -- assign the fetched row to an element the array i := i + 1; -- increment the element count END LOOP; CLOSE cursor1; -- close the cursor when finished with it FOR j IN jobs_arr.FIRST..jobs_arr.LAST LOOP -- loop through the varray elements -- determine the number of employees for each job ID in the array SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobs_arr(j).job_id; DBMS_OUTPUT.PUT_LINE ( 'Job ID: ' || RPAD(jobs_arr(j).job_id, 11, ' ') || RPAD(jobs_arr(j).job_title, 36, ' ') || ' Number of employees: ' || TO_CHAR(howmany)); END LOOP; END; /

Collections can be passed as parameters, so that subprograms can process arbitrary numbers of elements.

Using Bind Variables With PL/SQL

When you embed an , , , or SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the and clauses into bind variables automatically. Oracle Database XE can reuse these SQL statement each time the same code is executed. When running similar statements with different variable values, you can improve performance by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the appropriate places.

You need to specify bind variables with dynamic SQL, in clauses such as and where you normally use variables. Instead of concatenating literals and variable values into a single string, replace the variables with the names of bind variables (preceded by a colon), and specify the corresponding PL/SQL variables with the clause. Using the clause, instead of concatenating the variables into the string, reduces parsing overhead and lets Oracle Database XE reuse the SQL statements.

In Example 4-35, , , , and are examples of bind variables.

Using Dynamic SQL in PL/SQL

PL/SQL supports both dynamic and static SQL. Dynamic SQL enables you to build SQL statements dynamically at run time while static SQL statements are known in advance. You can create more general-purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation time.

To process most dynamic SQL statements, you use the statement. Dynamic SQL is especially useful for executing SQL statements to create database objects, such as .

Example 4-35 shows an example of the use of dynamic SQL to manipulate data in a table.

Example 4-35 Using Dynamic SQL to Manipulate Data in PL/SQL

DECLARE sql_stmt VARCHAR2(200); -- variable to hold SQL statement column_name VARCHAR2(30); -- variable for column name dept_id NUMBER(4); dept_name VARCHAR2(30); mgr_id NUMBER(6); loc_id NUMBER(4); BEGIN -- create a SQL statement (sql_stmt) to execute with EXECUTE IMMEDIATE -- the statement INSERTs a row into the departments table using bind variables -- note that there is no semi-colon (;) inside the quotation marks '...' sql_stmt := 'INSERT INTO departments VALUES (:dptid, :dptname, :mgrid, :locid)'; dept_id := 46; dept_name := 'Special Projects'; mgr_id := 200; loc_id := 1700; -- execute the sql_stmt using the values of the variables in the USING clause -- for the bind variables EXECUTEIMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id; -- use EXECUTE IMMEDIATE to delete the row that was previously inserted, -- substituting for the column name and using a bind variable column_name := 'DEPARTMENT_ID'; EXECUTEIMMEDIATE 'DELETE FROM departments WHERE ' || column_name || ' = :num' USING dept_id; END; /

Example 4-36 is an example of the use of dynamic SQL to create a table. For a more complete example, see Example 5-3.

Example 4-36 Using Dynamic SQL to Create a Table in PL/SQL

DECLARE tabname VARCHAR2(30); -- variable for table name current_date VARCHAR2(8); -- varible for current date BEGIN -- extract, format, and insert the year, month, and day from SYSDATE into -- the current_date variable SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) || TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'FM09') || TO_CHAR(EXTRACT(DAY FROM SYSDATE),'FM09') INTO current_date FROM DUAL; -- construct the table name with the current date as a suffix tabname := 'log_table_' || current_date; -- use EXECUTE IMMEDIATE to create a table with tabname as the table name EXECUTEIMMEDIATE 'CREATE TABLE ' || tabname || '(op_time VARCHAR2(10), operation VARCHAR2(50))' ; DBMS_OUTPUT.PUT_LINE(tabname || ' has been created'); -- now drop the table EXECUTEIMMEDIATE 'DROP TABLE ' || tabname; END; /

0 thoughts on “Local Procedures In Pl/Sql What Is The Assignment Operator”

    -->

Leave a Comment

Your email address will not be published. Required fields are marked *