An assignment statement sets the current value of a variable, field, parameter, or element. The statement consists of an assignment target followed by the assignment operator and an expression. When the statement is executed, the expression is evaluated and the resulting value is stored in the target. For more information, see "Assigning Values to Variables".
assignment statement ::=
Description of the illustration assignment_statement.gif
Keyword and Parameter Description
An attribute of an object type. The name must be unique within the object type (but can be reused in other object types). You cannot initialize an attribute in its declaration using the assignment operator or clause. Also, you cannot impose the constraint on an attribute.
A nested table, index-by table, or varray previously declared within the current scope.
A PL/SQL cursor variable previously declared within the current scope. Only the value of another cursor variable can be assigned to a cursor variable.
A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of , see "Expression Definition". When the assignment statement is executed, the expression is evaluated and the resulting value is stored in the assignment target. The value and target must have compatible datatypes.
A field in a user-defined or record.
A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
A variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Host variables must be prefixed with a colon.
A numeric expression that must return a value of type , , or a value implicitly convertible to that datatype.
An indicator variable declared in a PL/SQL host environment and passed to PL/SQL. Indicator variables must be prefixed with a colon. An indicator variable indicates the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, indicator variables let you detect nulls or truncated values in output host variables.
An instance of an object type previously declared within the current scope.
A formal or parameter of the subprogram in which the assignment statement appears.
A user-defined or record previously declared within the current scope.
A PL/SQL variable previously declared within the current scope.
By default, unless a variable is initialized in its declaration, it is initialized to every time a block or subprogram is entered. Always assign a value to a variable before using that variable in an expression.
You cannot assign nulls to a variable defined as . If you try, PL/SQL raises the predefined exception . Only the values , , and can be assigned to a Boolean variable. You can assign the result of a comparison or other test to a Boolean variable.
You can assign the value of an expression to a specific field in a record. You can assign values to all fields in a record at once. PL/SQL allows aggregate assignment between entire records if their declarations refer to the same cursor or table. Example 1-2, "Assigning Values to Variables With the Assignment Operator" shows how to copy values from all the fields of one record to another:
You can assign the value of an expression to a specific element in a collection, by subscripting the collection name.
Example 13-1 illustrates various ways to declare and then assign values to variables.
Example 13-1 Declaring and Assigning Values to VariablesDECLARE wages NUMBER; hours_worked NUMBER := 40; hourly_salary CONSTANT NUMBER := 17.50; -- constant value does not change country VARCHAR2(64) := 'UNKNOWN'; unknown BOOLEAN; TYPE comm_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER; commissions comm_tab; TYPE jobs_var IS VARRAY(10) OF employees.job_id%TYPE; jobids jobs_var; CURSOR c1 IS SELECT department_id FROM departments; -- cursor declaration deptid departments.department_id%TYPE; emp_rec employees%ROWTYPE; -- do not need TYPE declaration in this case BEGIN /* the following are examples of assignment statements */ wages := hours_worked * hourly_salary; -- compute wages country := UPPER('italy'); unknown := (country = 'UNKNOWN'); commissions(5) := 20000 * 0.15; commissions(8) := 20000 * 0.18; jobids := jobs_var('ST_CLERK'); jobids.EXTEND(1); jobids(2) := 'SH_CLERK'; OPEN c1; FETCH c1 INTO deptid; CLOSE c1; emp_rec.department_id := deptid; emp_rec.job_id := jobids(2); END; /
For examples, see the following:
Example 1-2, "Assigning Values to Variables With the Assignment Operator"
Example 1-3, "Assigning Values to Variables by SELECTing INTO"
Example 1-4, "Assigning Values to Variables as Parameters of a Subprogram"
Example 2-10, "Assigning Values to a Record With a %ROWTYPE Declaration"
"Assigning Values to Variables"
"Constant and Variable Declaration"
"SELECT INTO Statement"
PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's proceduralextension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 6 - stored PL/SQL procedures/functions/packages/triggers since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7). Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.
PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.
PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, SybaseASE and MicrosoftSQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language, which conforms to the ISO SQL’s SQL/PSM standard.
The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.
PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables.
PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages, but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor, CEITON MSSQL/Oracle Compiler and SwisSQL.
The StepSqlite product is a PL/SQL compiler for the popular small database SQLite which supports a subset of PL/SQL syntax. Oracle's Berkeley DB 11g R2 release added support for SQL based on the popular SQLite API by including a version of SQLite in Berkeley DB. Consequently, StepSqlite can also be used as a third-party tool to run PL/SQL code on Berkeley DB..plsql contains dynamic sql to execute queries at runtime.
PL/SQL program unit
The main feature of SQL (non procedural) is also a drawback of SQL. One can not use control statements like decision making, iterative control if only SQL is to be used. PL/SQL is basically a procedural language, which provides functionality of decision making, iteration and many more features like other procedural programming languages. A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database.
PL/SQL anonymous block
The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label.
The symbol := functions as an assignment operator to store a value in a variable.
Blocks can be nested – i.e., because a block is an executable statement, it can appear in another block wherever an executable statement is allowed. A block can be submitted to an interactive tool (such as SQL*Plus) or embedded within an Oracle Precompiler or OCI program. The interactive tool or program runs the block once. The block is not stored in the database, and for that reason, it is called an anonymous block (even if it has a label).
The purpose of a PL/SQL function is generally to compute and return a single value. This returned value may be a single scalar value (such as a number, date or character string) or a single collection (such as a nested table or varray). User-defined functions supplement the built-in functions provided by Oracle Corporation.
The PL/SQL function has the form:
Pipelined table functions return collections and take the form:
A function should only use the default IN type of parameter. The only out value from the function should be the value it returns.
Procedures resemble functions in that they are named program units that can be invoked repeatedly. The primary difference is that functions can be used in a SQL statement whereas procedures cannot. Another difference is that the procedure can return multiple values whereas a function should only return a single value.
The procedure begins with a mandatory heading part to hold the procedure name and optionally the procedure parameter list. Next come the declarative, executable and exception-handling parts, as in the PL/SQL Anonymous Block. A simple procedure might look like this:
The example above shows a standalone procedure - this type of procedure is created and stored in a database schema using the CREATE PROCEDURE statement. A procedure may also be created in a PL/SQL package - this is called a Package Procedure. A procedure created in a PL/SQL anonymous block is called a nested procedure. The standalone or package procedures, stored in the database, are referred to as "stored procedures".
Procedures can have three types of parameters: IN, OUT and IN OUT.
- An IN parameter is used as input only. An IN parameter is passed by reference, though it can be changed by the inactive program.
- An OUT parameter is initially NULL. The program assigns the parameter a value and that value is returned to the calling program.
- An IN OUT parameter may or may not have an initial value. That initial value may or may not be modified by the called program. Any changes made to the parameter are returned to the calling program by default by copying but - with the NOCOPY hint - may be passed by reference.
PL/SQL also supports external procedures via the Oracle database's standard process. 
Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors, etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are:
- Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation.
- Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.
Main article: Database trigger
A database trigger is like a stored procedure that Oracle Database invokes automatically whenever a specified event occurs. It is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled, the database automatically invokes it—that is, the trigger fires—whenever its triggering event occurs. While a trigger is disabled, it does not fire.
You create a trigger with the CREATE TRIGGER statement. You specify the triggering event in terms of triggering statements, and the item they act on. The trigger is said to be created on or defined on the item—which is either a table, a view, a schema, or the database. You also specify the timing point, which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects.
If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger. If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger.
An INSTEAD OF trigger is either: A DML trigger created on a view or a system trigger defined on a CREATE statement. The database fires the INSTEAD OF trigger instead of running the triggering statement.
Purpose of triggers
Triggers can be written for the following purposes:
- Generating some derived column values automatically
- Enforcing referential integrity
- Event logging and storing information on table access
- Synchronous replication of tables
- Imposing security authorizations
- Preventing invalid transactions
The major datatypes in PL/SQL include NUMBER, CHAR, VARCHAR2, DATE and TIMESTAMP.
To define a numeric variable, the programmer appends the variable type NUMBER to the name definition. To specify the (optional) precision (P) and the (optional) scale (S), one can further append these in round brackets, separated by a comma. ("Precision" in this context refers to the number of digits the variable can hold, and "scale" refers to the number of digits that can follow the decimal point.)
A selection of other datatypes for numeric variables would include: binary_float, binary_double, dec, decimal, double precision, float, integer, int, numeric, real, smallint, binary_integer.
To define a character variable, the programmer normally appends the variable type VARCHAR2 to the name definition. There follows in brackets the maximum number of characters the variable can store.
Other datatypes for character variables include: varchar, char, long, raw, long raw, nchar, nchar2, clob, blob, and bfile.
Date variables can contain date and time. The time may be left out, but there is no way to define a variable that only contains the time. There is no DATETIME type. And there is a TIME type. But there is no TIMESTAMP type that can contain fine grained timestamp up to millisecond or nanosecond. Oracle Datatypes
The function can be used to convert strings to date values. The function converts the first quoted string into a date, using as a definition the second quoted string, for example:
To convert the dates to strings one uses the function .
PL/SQL also supports the use of ANSI date and interval literals. The following clause gives an 18-month range:
Exceptions—errors during code execution—are of two types: user defined and predefined.
User-defined exceptions are always raised explicitly by the programmers, using the or commands, in any situation where they determine it is impossible for normal execution to continue. The command has the syntax:
Oracle Corporation has predefined several exceptions like , , etc. Each exception has an SQL error number and SQL error message associated with it. Programmers can access these by using the and functions.
Datatypes for specific columnsVariable_name Table_name.Column_name%type;
This syntax defines a variable of the type of the referenced column on the referenced tables.
Programmers specify user-defined datatypes with the syntax:
This sample program defines its own datatype, called t_address, which contains the fields name, street, street_number and postcode.
So according to the example, we are able to copy the data from the database to the fields in the program.
Using this datatype the programmer has defined a variable called v_address and loaded it with data from the ADDRESS table.
Programmers can address individual attributes in such a structure by means of the dot-notation, thus:v_address.street := 'High Street';
The following code segment shows the IF-THEN-ELSIF-ELSE construct. The ELSIF and ELSE parts are optional so it is possible to create simpler IF-THEN or, IF-THEN-ELSE constructs.
The CASE statement simplifies some large IF-THEN-ELSIF-ELSE structures.
CASE statement can be used with predefined selector:
PL/SQL refers to arrays as "collections". The language offers three types of collections:
- Associative arrays (Index-by tables)
- Nested tables
- Varrays (variable-size arrays)
Programmers must specify an upper limit for varrays, but need not for index-by tables or for nested tables. The language includes several collection methods used to manipulate collection elements: for example FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, DELETE, etc. Index-by tables can be used to simulate associative arrays, as in this example of a memo function for Ackermann's function in PL/SQL.
Associative arrays (index-by tables)
With index-by tables, the array can be indexed by numbers or strings. It parallels a Javamap, which comprises key-value pairs. There is only one dimension and it is unbounded.
With nested tables the programmer needs to understand what is nested. Here, a new type is created that may be composed of a number of components. That type can then be used to make a column in a table, and nested within that column are those components.
Varrays (variable-size arrays)
With Varrays you need to understand that the word "variable" in the phrase "variable-size arrays" doesn't apply to the size of the array in the way you might think that it would. The size the array is declared with is in fact fixed. The number of elements in the array is variable up to the declared size. Arguably then, variable-sized arrays aren't that variable in size.
A cursor is a mechanism, pointer to a private SQL area that stores information coming from a SELECT or data manipulation language (DML) statement (INSERT, UPDATE, DELETE, or MERGE). A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.
A cursor can be explicit or implicit. In a FOR loop, an explicit cursor shall be used if the query will be reused, otherwise an implicit cursor is preferred. If using a cursor inside a loop, use a FETCH is recommended when needing to bulk collect or when needing dynamic SQL.
As a procedural language by definition, PL/SQL provides several iteration constructs, including basic LOOP statements, WHILE loops, FOR loops, and Cursor FOR loops. Since Oracle 7.3 the REF CURSOR type was introduced to allow recordsets to be returned from stored procedures and functions. Oracle 9i introduced the predefined SYS_REFCURSOR type, meaning we no longer have to define our own REF CURSOR types.
Loops can be terminated by using the keyword, or by raising an exception.
Output:0 1 2 3 4 5 6 7 8 9 10 var is null
Cursor FOR loops
Cursor-for loops automatically open a cursor, read in their data and close the cursor again.
As an alternative, the PL/SQL programmer can pre-define the cursor's SELECT-statement in advance to (for example) allow re-use or make the code more understandable (especially useful in the case of long or complex queries).
The concept of the person_code within the FOR-loop gets expressed with dot-notation ("."):
While programmers can readily embed Data Manipulation Language (DML) statements directly into PL/SQL code using straightforward SQL statements, Data Definition Language (DDL) requires more complex "Dynamic SQL" statements in the PL/SQL code. However, DML statements underpin the majority of PL/SQL code in typical software applications.
In the case of PL/SQL dynamic SQL, early versions of the Oracle Database required the use of a complicated Oracle package library. More recent versions have however introduced a simpler "Native Dynamic SQL", along with an associated syntax.
- ^Serge Rielau (email@example.com), SQL Architect, STSM, IBM. "DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows". Ibm.com. Retrieved 2012-07-26.
- ^"SQL PL". Publib.boulder.ibm.com. Retrieved 2012-07-26.
- ^Oracle documentation "Private and public items in PL/SQL"
- ^Scratchlauncher "Oracle Scratchlauncher"
- ^CEITON "MSSQL/Oracle Compiler"
- ^"Twitter / Gregory Burd: @humanications We didn't r ..."
- ^"Official Berkeley DB FAQ". Oracle Corporation. Retrieved March 30, 2010.
- ^Nanda, Arup; Feuerstein, Steven (2005). Oracle PL/SQL for DBAs. O'Reilly Series. O'Reilly Media, Inc. pp. 122,429. ISBN 978-0-596-00587-0. Retrieved 2011-01-11.
- ^Gupta, Saurabh K. (2016) . "5: Using Advanced Interface Methods". Advanced Oracle PL/SQL Developer's Guide. Professional experience distilled (2 ed.). Birmingham: Packt Publishing Ltd. p. 143. ISBN 9781785282522. Retrieved 2017-06-08.
- ^"Literals". Oracle iSQL Reference 10g Release 2 (10.2). Oracle. Retrieved 2009-03-20.
- ^Working with Cursors by Steven Feuerstein