Oracle Pl Sql Variable Assignment Select

Assignment Statement

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".

Syntax

assignment statement ::=


Description of the illustration assignment_statement.gif

Keyword and Parameter Description

attribute_name

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.

collection_name

A nested table, index-by table, or varray previously declared within the current scope.

cursor_variable_name

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.

expression

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.

field_name

A field in a user-defined or record.

host_cursor_variable_name

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.

host_variable_name

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.

index

A numeric expression that must return a value of type , , or a value implicitly convertible to that datatype.

indicator_name

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.

object_name

An instance of an object type previously declared within the current scope.

parameter_name

A formal or parameter of the subprogram in which the assignment statement appears.

record_name

A user-defined or record previously declared within the current scope.

variable_name

A PL/SQL variable previously declared within the current scope.

Usage Notes

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.

Examples

Example 13-1 illustrates various ways to declare and then assign values to variables.

Example 13-1 Declaring and Assigning Values to Variables

DECLARE 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"

Related Topics


"Assigning Values to Variables"
"Constant and Variable Declaration"
"Expression Definition"
"SELECT INTO Statement"

 

 
 

Oracle PL/SQL "select into" clause


Oracle Database Tips by Donald Burleson

The SELECT INTO Clause

The SELECT INTO clause of SQL is used to retrieve one row or set of columns from the Oracle database.  The SELECT INTO is actually a standard SQL query where the SELECT INTO clause is used to place the returned data into predefined variables. 

 

SQL> declare

  2    v_authName author.author_last_name%type;

  3  begin

  4    select

  5      author_last_name into v_authName

  6    from

  7      author

  8    where

  9      author_key = 'A103';

 10

 11    dbms_output.put_line('Name: '||v_authName);

 12  end;

 13  /

 

Name: weaton

 

Here the author_key was used to retrieve one author's last name and place it in the variable called v_authName.  The query can also retrieve an entire row as a record with SELECT INTO. 

 

In the example below a record based on the columns of the author table is declared in line two below.  Because v_author is declared as an author table %rowtype , you can safely use the SELECT * clause to retrieve all the columns.

 

SQL> declare

  2    v_author author%rowtype;

  3  begin

  4    select

  5      * into v_author

  6    from

  7      author

  8    where

  9      author_key = 'A103';

 10

 11    dbms_output.put_line('Name:   

             '||v_author.author_first_name||' '||

                v_author.author_last_name);

 12  end;

 13  /

 

Name: erin weaton

 

If the DBA adds a column to the author table, the query above will still execute.  The record variable v_author contains a record that includes all the columns in the author table.  If the value of a column in the table is NULL, it will also be NULL in the record.  The individual columns are accessed using the dot "." notation with SELECT INTO. 

 

You can see this in line 11 of the listing.  Although it is important to define variables using the database datatype definition of the retrieved data, sometime this is not possible. 

 

This is shown in the example below.

 

SQL> declare

  2    v_totalName varchar2(80);

  3  begin

  4    select

  5      initcap(author_last_name||',

                             '||author_first_name)

           into v_totalName

  6    from

  7      author

  8    where

  9      author_key = 'A105';

 10    dbms_output.put_line('Name: '||

         v_totalName);

 11  end;

 12  /

 

Name: Withers, Lester

 

The query above is returning a string value created from multiple table columns.  The variable v_totalname must be defined as a datatype that can hold the composite string returned by the query.  If the SELECT INTO clause attempts to load a value into a variable that is the wrong datatype, or not large enough to contain the value, an exception is raised.

 

Although a SELECT INTO  can only return one row of data, SELECT INTO can return multiple columns into multiple variables.  In the example below, two columns are selected from a table and loaded into two predefined variables.

 

SQL> declare

  2    v_lname author.author_last_name%type;

  3    v_fname author.author_first_name%type;

  4  begin

  5    select

  6      author_first_name, author_last_name

  7      into v_fname, v_lname

  8    from

  9      author

 10    where author_key = 'A108';

 11

 12    dbms_output.put_line('Name: '||v_fname||'

 '||v_lname);

 13  end;

 14  /

 

Name: minnie mee

 

A single row of column values is loaded into the list of variables when multiple columns are selected.  The order of the columns and the order of the variables must match when using the SELECT INTO clause.

 

In each example so far, the restriction defined in the query's WHERE clause has been based on a primary key.  Using a unique or primary key is important as the SELECT INTO clause can not retrieve more that one row of data.  If the query returns more that one row an exception is thrown.

 

SQL> declare

  2    v_authName author.author_last_name%type;

  3  begin

  4    select

  5      author_last_name into v_authName

  6    from

  7      author

  8    where

  9      author_state = 'MO';

 10    dbms_output.put_line('Name: '||v_authName);

 11  end;

 12  /

 

declare

*

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at line 4

 

The above example retrieved the authors from the author table with an author_state of 'Missouri'.  There are three authors in the author table from Missouri and the SELECT INTO  raised a TOO_MANY_ROWS   exception.

 

Another issue with using SELECT INTO statement is that SELECT INTO throws an exception is it fails to return at least one rows.

 

SQL> declare

  2    v_authName author.author_last_name%type;

  3  begin

  4    select

  5      author_last_name into v_authName

  6    from

  7      author

  8    where

  9      author_state = 'FL';

 10    dbms_output.put_line('Name: '||v_authName);

 11  end;

 12  /

declare

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 4

 

Here the query asks for authors from Florida.  Since there are not any authors in the table from Florida, the SELECT INTO raises a NO_DATA_FOUND exception exception" .  Your PL/SQL code should be written in such a way that it is able to handle these exceptions.  Anytime the SELECT INTO raises an exception, the query will not load a value into the defined variable. 

 

When you try and access the variable, you will either get another exception or worse, use an old or invalid variable value.  So using the SELECT INTO query can be problematic.  However since a SELECT INTO query can return one and only one row of data, it makes a perfect PL/SQL function.  Within the function, your code can catch and handle the possible exceptions. 

 

Below is an example of wrapping the SELECT INTO   query in a function with exception handling.

 

SQL> create or replace function auth_Name

  2    ( v_auth_state IN author.author_state%type)

  3    return varchar2

  4  as

  5    v_authName author.author_last_name%type;

  6  begin

  7    select

  8      author_last_name into v_authName

  9    from

 10      author

 11    where

 12      author_state = v_auth_state;

 13    return v_authName;

 14    exception

 15    when TOO_MANY_ROWS

 16      then return 'Too Many Authors in that

                      State';

 17    when NO_DATA_FOUND

 18      then return 'No Authors in that State';

 19    when others

 20      then raise_application_error(

 21      -20011,'Unknown Exception in authName

                 Function');

 22  end;

 23  /

 

Function created.

 

Notice when the function code catches an exception it must handle it and return something appropriate or pass the exception back to the calling block.  In the above example, the code catches and handles the TOO_MANY_ROWS and NO_DATA_FOUND exception  , which should cover most of the exceptions the function will encounter. 

 

For more information, see the book Easy PL/SQL Programming .

 

 

0 Thoughts to “Oracle Pl Sql Variable Assignment Select

Leave a comment

L'indirizzo email non verrĂ  pubblicato. I campi obbligatori sono contrassegnati *