003 - [Oracle PL/SQL] Declaring PL/SQL Variables

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

A variable name:

•   Must start with a letter
•   Can include letters or numbers
•   Can include special characters (such as $, _, and # )
•   Must contain no more than 30 characters - Starting with Oracle Database 12c Release 2 (12.2), the maximum length of identifier names for most types of database objects has been increased to 128 bytes.
•   Must not include reserved words

Variables are:

•   Declared and initialized in the declarative section (between declare & begin)
•   Used and assigned new values in the executable section (between begin & end)
•   Passed as parameters to PL/SQL subprograms ( like procedure and function)
•   Used to hold the output of a PL/SQL subprogram v_sal=get_emp_sal (100)

 

 

Declaring and Initializing PL/SQL Variables

 

identifier [CONSTANT] datatype [NOT NULL]
                [:= | DEFAULT expr];

 

 

DECLARE

 

v_hiredate 			DATE;
v_deptno 			NUMBER (2) NOT NULL : = 10;
v_location			VARCHAR2 (13) := 'Atlanta' ;
c_comin				CONSTANT NUMBER := 1400;

 

 

 

Sample code 

 

declare
    v_date date:=sysdate;
    v_no number:=10*2;
    v_pi constant number:= 3.14;
begin
    dbms_output.put_line(v_date);
    dbms_output.put_line(v_no);
    dbms_output.put_line(v_pi);
    v_date:=v_date+10;
    dbms_output.put_line(v_date);
    --v_pi:=10;  if you try to do this then you will get error;
end;

-----------------------------------------------------------------

02-FEB-24
20
3.14
12-FEB-24

 

 

 

DECLARE
    v_myName1 VARCHAR2(20);
    v_myName2 VARCHAR2(20) := 'John';
    v_myName3 VARCHAR2(20) DEFAULT 'John';
BEGIN
    dbms_output.put_line('my name1 is [' ||v_myName1 ||']');
    dbms_output.put_line('my name2 is [' ||v_myName2 ||']');
    dbms_output.put_line('my name3 is [' ||v_myName3 ||']');
    
END;

------------------------------------------------------------

my name1 is []
my name2 is [John]
my name3 is [John]

 

 

 

Bind variables are:

•   Created in the environment
•   Also called host variables
•   Created with the VARIABLE keyword
•   Used in SQL statements and PL/SQL blocks
•   Accessed even after the PL/SQL block is executed
•   Referenced with a preceding colon

 

variable v_sal number
var v_name varchar2(100);
set autoprint on;
set serveroutput on;

declare
    MAXSALARY NUMBER(7,2) := 5000;
begin
    select first_name, salary 
    into :v_name, :v_sal
    from employees where employee_id=100;

    dbms_output.put_line('MAXSALARY: '||MAXSALARY);
end;

------------------------------------------

MAXSALARY: 5000 # put_line

PL/SQL procedure successfully completed.

V_NAME # "set autoprint on" 명령어를 사용하지 않으면, "print v_name" 명령어를 추가해야 한다.
------
Steven
V_SAL # "set autoprint on" 명령어를 사용하지 않으면, "print v_sal" 명령어를 추가해야 한다.
-----
24000

 

 

 

variable v_sal number;

begin
    select salary into :v_sal
    from employees
    where employee_id=100;
end;

-- 위의 블럭을 먼저 실행 시키고 아래 명령어를 실행해야 한다.

print v_sal;