Types of Variables
• PL/SQL variables:
– Scalar
– Composite
– Reference
– LOB(large objects)
• Non-PL/SQL variables: Bind and host variables
Declaring PL/SQL Variables
Syntax:
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];
Examples:
DECLARE
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := ‘Atlanta’;
c_comm CONSTANT NUMBER := 1400;
Scalar Data Types
Hold a single value Have no internal components
- CHAR [(maximum_length)]
- VARCHAR2(maximum_length)
- LONG
- LONG RAW
- NUMBER [(precision, scale)]
- BINARY_INTEGER
- PLS_INTEGER
- BOOLEAN
- Date
- Timestamp
Example:
DECLARE
v_job VARCHAR2(9);
v_count BINARY_INTEGER := 0;
v_total_sal NUMBER(9,2) := 0;
v_orderdate DATE := SYSDATE + 7;
c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
v_valid BOOLEAN NOT NULL := TRUE;
v_name employees.last_name%TYPE;
v_balance NUMBER(7,2);
v_min_balance v_balance%TYPE := 10;
Declaring Variables with the %TYPEAttribute
Syntax:
identifier Table.column_name%TYPE;
Examples:
…
v_name employees.last_name%TYPE;
v_balance NUMBER(7,2);
v_min_balance v_balance%TYPE := 10;
Referencing Non-PL/SQL Variables
Store the annual salary into a iSQL*Plus host variable.
• Reference non-PL/SQL variables as host variables.
• Prefix the references with a colon (:).
:g_monthly_sal := v_sal / 12;
DBMS_OUTPUT.PUT_LINE
• An Oracle-supplied packaged procedure
• An alternative for displaying data from a PL/SQL block
• Must be enabled iniSQL*Plus with SET SERVEROUTPUT ON
DECLARE
v_sal NUMBER(9,2) := &p_annual_sal;
BEGIN
v_sal := v_sal/12;
DBMS_OUTPUT.PUT_LINE (’The monthly salary is ‘ ||
TO_CHAR(v_sal));
END;SET SERVEROUTPUT ON
DEFINE p_annual_sal = 60000
• PL/SQL identifiers:
- – Are defined in the declarative section
- – Can be of scalar, composite, reference, or LOBdata type
- – Can be based on the structure of another variable or database object
- – Can be initialized
• Variables declared in an external environment such as iSQL*Plus are called host variables.
• Use DBMS_OUTPUT.PUT_LINEto display data from a PL/SQL block.
