Oracle 기초 : 실전(15) PL/SQL - Anonymous Block + Conditional Expressions

Introduction: PL/SQL

Oracle's Procedural Language Extension to SQL, 즉 기본 SQL의 절차지향 확장 언어. Oracle에 내장된 절차적 언어로써 기본 SQL의 단점을 보완한 것. 조건문, 반복문, 변수 선언, 변수 사용등이 가능하다.

Types of PL/SQL, Maximum Size of Datatypes in PL/SQL

• Anonymous Block: 익명 블럭. 일회용으로 간단한 블럭 구문 수행
• Procedure: 지정된 절차를 수행하는 서브 프로그램으로 저장된 형태, 언제든지 다른 Procedure, Client Program(sql developer, sqlplus, java app)등에 의해 호출 사용
• Function: procedure의 한 형태이지만, 항상 하나의 결과값을 리턴하며 일반 sql문에서도 사용이 가능하다.

Types of Variables

1. 일반 변수: 기존 SQL 타입과 동일하며, 크기가 확장됨 (아래의 표 참조)
2. 참조 변수: 기존의 Column 혹은 Table의 row타입등을 그대로 가져와 사용할 수 있다.
   

---

PL/SQL: Anonymous Block

Syntax of the print to Console:
set serveroutput on;
--activate the serveroutput
dbms_output.put_line('CONTENTS ON HERE');

Syntax of the Anonymous Block:
declare(add'l)
ArgName1 DataType(Size-add'l)
ArgName2 Tab.Col%type;
...
begin(required)
select Cols
into ArgName,
from Tab
where Condition(add'l)
Args := '&PROMPT-POPUP-MESSAGE ON HERE'
exception(add'l)
when EXCEPTION_NAME then EXECUTE-STATEMENT;
end;
/

--e.g. GET ARGS-VAL FROM TAB.COL
set serveroutput on;
declare
	Arg_no employee.emp_no%type;
    	Arg_name employee.emp_name%type;
begin
	select emp_no, emp_name
    	into Arg_no, Arg_name
        from employee
        where emp_id = '&사번';
   	dbms_output.put_line('주민번호:'||Arg_no);
    dbms_output.put_line('사원명:'||Arg_name);
--exception
end;
/

Remark:

1. (declare) ArgName Tab.Col%type 선언으로 특정 Table.Column의 데이터 타입을 참조할 수 있다.
2. 출력문의 변수를 nvl()처리 할 수 있다. (e.g.dbms_ouput.put_line(nvl(emp_row.phone, 'null'));)
3. Arg := '&PROMPT-POPUP-MSG', 즉 prompt pop-up으로 사용자로부터 Argument를 받아 처리할 수 있다.
4. 대입연산자로 =가 아닌 :=를 사용하며, 기호 =는 동등비교를 위한 Equal, 즉 Single-Row Operator이니 혼용하지 않도록 주의하자.
5. (declare) Arg Tab%rowtype; (begin) dbms_output.put_line('CONTENT'|| Arg.Col); 의 형식으로 해당 Table의 row를 통째로 불러온 후, 출력시에 ArgumentName.ColumnName의 문법으로 Column을 호출할 수 있다.

---

PL/SQL: Anonymous Block + Conditional Expression

Syntax:
if Condition1 then Execute-Statement1
elsif Condition2 then Execute-Statement2
else Execute-Statement3
end if;

declare
	emp_row employee%rowtype;
begin 
	select *
    	into emp_row 
        from employee
        where emp_id = '&사번';
        if emp_row.job_code = 'J1'
        then dbms_output.put_line(emp_row.emp_name||'은 사장님입니다.');
        elsif emp_row.job_code = 'J2'
             dbms_output.put_line(emp_row.emp_name||'은 임원입니다.');
        else
	     dbms_output.put_list(emp_row.emp_name||'은 평사원입니다.');
	end if;
END;
/

Anonymous Block(익명블럭)에 조건절을 대입하여 이상과 같은 판별문을 작성할 수 있다. 코드 작성시에 유의할 점은, else if에 해당하는 명령어가 elsif 이라는 것. 오타가 아니다. 진짜 elsif다. (Oracle 개발자가 졸다가 쳐버린 오타로 명령어를 만들어버린 걸까???) 또한 하나의 Anonymous Block 안에서도 semicolon을 찍는 경우와 찍지 않는 경우를 잘 구분해서 써야한다는 것. 대체적으로 변수의 "선언"후, "실행문"의 작성 후, 블럭의 끝을 알리는 위치인 END의 뒤에 ;을 찍어주면 된다.

---