Pl Sql Variable Assignment

In Sql Server, often times when I'm testing the body of a stored procedure, I copy the body into SSMS, DECLARE the variables at the top of the page, set them to some sample values, and execute the body as-is.

For Example, if my proc is

Then my test sql would be

What is the Oracle PL/SQL equivalent to this?

This is the closest that I've come up with, but I'm getting "PLS-00428: an INTO clause is expected in this SELECT statement"

This is a better example of what I'm really trying to do:

But again, it wants an 'INTO' when really I just want the records printed on the screen, not stored in another table....

RESOLVED:

Thanks to @Allan, I've got it working well enough. Oracle SQL Developer apparently remembers the parameter values you supply it with. PL/SQL Developer, however, wants nothing to do with this....

If you "Run As Script", it will abide by your defaults, but it will only return results as ASCI text, not in a grid/spreadsheet

Summary: in this tutorial, you will learn about PL/SQL variables that help you manipulate data in PL/SQL programs.

In PL/SQL, a variable is a meaningful name of a temporary storage location that supports a particular data type in a program. Before using a variable, you need to declare it first in the declaration section of a PL/SQL block.

PL/SQL variables naming rules

Like other programming languages, a variable in PL/SQL must follow the naming rules as follows:

  • The variable name must be less than 31 characters. Try to make it as meaningful as possible within 31 characters.
  • The variable name must begin with an ASCII letter. It can be either lowercase or uppercase. Notice that PL/SQL is case-insensitive, which means  and refer to the same variable.
  • Followed by the first character are any number, underscore ( ), and dollar sign ( ) characters. Once again, do not make your variables hard to read and difficult to understand.

PL/SQL variables naming convention

It is highly recommended that you should follow the naming conventions listed in the following table to make the variables obvious in PL/SQL programs:

PrefixData Type
v_VARCHAR2
n_NUMBER
t_TABLE
r_ROW
d_DATE
b_BOOLEAN

Each organization has its own development naming convention guidelines. Make sure that you comply with your organization’s naming convention guidelines.

For example, if you want to declare a variable that holds the first name of the employee with the data type, the variable name should be .

PL/SQL Variables Declaration

To declare a variable, you use a variable name followed by the data type and terminated by a semicolon ( ). You can also explicitly add a length constraint to the data type within parentheses. The following illustrates some examples of declaring variables in a PL/SQL anonymous block:

PL/SQL variable anchors

In PL/SQL program, one of the most common tasks is to select values from columns in a table into a set of variables. In case the data types of columns of the table changes, you have to change the PL/SQL program to make the types of the variables compatible with the new changes.

PL/SQL provides you with a very useful feature called variable anchors. It refers to the use of the    keyword to declare a variable with the data type is associated with a column’s data type of a particular column in a table.

Let’s take a look at the table in sample database provided by Oracle:

The  variable has a data type that is the same as the data type of the column in the  tableIn case the data type of the column changes, the type of the variable automatically inherits the new data type of the column.

PL/SQL variable assignment

In PL/SQL, to assign a value or a variable to another, you use the assignment operator (  ) which is a colon( ) followed by the equal sign( ).

Please see the code listing below to get a better understanding:

In the example above, we assigned  to variable, to variable, and result of the function to variable.

You can use of the  statement to assign a value to a variable. The clause moves the values from the query’s column list into corresponding PL/SQL variables.

Initializing variables

When you declare a variable, its value is uninitialized and hence is . You can initialize variable a value in declaration section by using variable assignment.

See the following example:

In PL/SQL, means an unknown value so it has some special characteristics as follows:

  • is not equal to anything, even itself .
  • is not greater than or less than anything else, even .
  • You cannot use logical operator equal ( ) or ( ) with . You must use the SQL IS NULL or IS NOT NULL to test the NULL values.

In this tutorial, we have shown you how to declare, assign and initialize PL/SQL variables. We also walked you through how to declare PL/SQL variables using variable anchors to make your code more flexible and adaptable to the changes in columns of the database tables.

   v_first_namevarchar2(20);
   v_last_namevarchar2(20);
   n_employee_idnumber;
   d_hire_datedate;
  v_first_name  EMPLOYEES.FIRST_NAME%TYPE;
  v_last_name   EMPLOYEES.LAST_NAME%TYPE;
  n_employee_idEMPLOYEES.EMPLOYEE_ID%TYPE;
  d_hire_date   EMPLOYEES.HIRE_DATE%TYPE;
   v_first_nameEMPLOYEES.FIRST_NAME%TYPE;
   v_last_nameEMPLOYEES.LAST_NAME%TYPE;
   n_employee_idEMPLOYEES.EMPLOYEE_ID%TYPE;
   d_hire_dateEMPLOYEES.HIRE_DATE%TYPE;
   v_first_name:='Mary';
   v_last_name:='Jane';
   d_hire_date:=to_date('19700101','YYYYMMDD');
SETSERVEROUTPUTONSIZE1000000;
   v_first_nameEMPLOYEES.FIRST_NAME%TYPE;
   v_last_nameEMPLOYEES.LAST_NAME%TYPE;
   n_employee_idEMPLOYEES.EMPLOYEE_ID%TYPE;
   d_hire_dateEMPLOYEES.HIRE_DATE%TYPE;
   SELECTemployee_id,
          first_name,
          last_name,
          hire_date
   INTOn_employee_id,
        v_first_name,
        v_last_name,
        d_hire_date
   FROMemployees
   WHEREemployee_id=200;
   DBMS_OUTPUT.PUT_LINE(v_first_name);
   DBMS_OUTPUT.PUT_LINE(v_last_name);
   DBMS_OUTPUT.PUT_LINE(d_hire_date);
  n_employee_idEMPLOYEES.EMPLOYEE_ID%TYPE:=200;
  d_hire_dateEMPLOYEES.HIRE_DATE%TYPE:=to_date('19700101','YYYYMMDD');

0 Replies to “Pl Sql Variable Assignment”

Lascia un Commento

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