Declare a Variable
In general, all variables that are used in a block of code must be declared in the declaration section. A variable declaration consists of the variable name and its data type (see Section 2.2 for a discussion of data types). A variable can be initialized with a default value at the same time as the variable declaration.
The syntax rules for variable declarations are as follows.
name type [ { := | DEFAULT } { expression | NULL } ];
name is the identifier assigned to the variable. type is the data type assigned to the variable. If [ := expression ] occurs, then when the process enters the block, an initialized value is assigned to the variable, otherwise, the variable is automatically initialized to a null value in SQL.
The default value is calculated each time the block is entered. For example, when assigning SYSDATE to a variable of type DATE, the value of this variable is the current time, not the time value after the procedure or function has been precompiled and completed.
The following procedure demonstrates a number of variable declarations that use default values consisting of strings and numeric expressions.
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_salary_rpt (p_deptno NUMBER)
IS
todays_date DATE := SYSDATE;
rpt_title VARCHAR2(60) := 'Report For Department # ' || p_deptno|| ' on ' || todays_date;
base_sal INTEGER := 35525;
base_comm_rate NUMBER := 1.33333;
base_annual NUMBER := ROUND(base_sal * base_comm_rate, 2);
BEGIN
DBMS_OUTPUT.PUT_LINE(rpt_title);
DBMS_OUTPUT.PUT_LINE('Base Annual Salary: ' || base_annual);
END;
/
\set PLSQL_MODE off
The output of the procedure above shows that the default value used in the variable declaration has been assigned to the variable.
postgres=# select dept_salary_rpt(20);
NOTICE: Report For Department # 20 on 2020-09-01 10:31:00
NOTICE: Base Annual Salary: 47366.55
DEPT_SALARY_RPT
-----------------
(1 row)
Top comments (0)