[ SQLD ] Oracle SQL (13) - PL/SQL 기초 및 개념

▶ PL/SQL (Procedural Language extension to SQL)

 

•  SQL을 확장한 절차적 언어(Procedural Language)이다. 
  
• 관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합한다.
  
• 유저 프로세스가 PL/SQL 블록을 보내면, 서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고 SQL과 Procedural를 나눠서 SQL은 SQL Statement Executer로 보낸다.
  
• PL/SQL 프로그램의 종류는 크게 Procedure, Function, Trigger 로 나뉘어 진다.
  
  • Procedure : 리턴 값을 하나 이상 가질 수 있는 프로그램을 말한다.
  • Function : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음을 말한다.
  • Trigger : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록이다.
• 오라클에서 지원하는 프로그래밍 언어의 특성을 수용하여 SQL에서는 사용할수없는 절차적 프로그래밍 기능을 가지고 있어 SQL의 단점을 보완하였다.
• 형식

DECLARE -- Declarative part (optional) -- Declarations of local types, variables, & subprograms

BEGIN -- Executable part (required) // Statements (which can use items declared in declarative part)

[EXCEPTION] -- Exception-handling part (optional)
	    -- Exception handlers for exceptions (errors) raised in executable part

END;

 

▶ 오라클 환경에서 PL/SQL을 학습하는 이유

• 오라클 개발 도구를 수행하는 모든 프로그래밍의 기초
• 클라이언트가 아닌 서버 상에서 프로세스를 수행하는데 PL/SQL을 사용함
• PL/SQL을 사용하면 업무 규칙이나 복잡한 로직을 캡슐화(Encapsulation)할 수 있어, 모듈화(Modularity)와 추상화(Abstraction)가 가능
• 데이터베이스 트리거를 통하여 데이터베이스 무결성을 제약하는 복잡한 규칙의 코딩과 변경 내역, 데이터를 복사
• PL/SQL은 독립적인 플랫폼 수준을 제공

 

▶ PL/SQL에서 제공하는 명령문은

• 모든 SQL문
• 변수 및 상수 등의 선언문
• 대입문
• 조건 판단문
• 제어 흐름문
• 반복 처리문

 

▶ PL/SQL로 작성할 수 있는 것은?

• SQL*PLUS 스크립트(SCRIPTS)
• 프로시저(PROCEDURE), 함수(FUNCTION) 서브 프로그램
• 패키지(PACKAGE)
• 데이터베이스 트리거(DATEBASE TRIGGER)
• 어플리케이션 로직 (APPLICATION LOGIC)

 

 

▶ 권한에 대한 5가지 대표적인 Role

• connect : 접속 권한
• resource : 객체 생성, 변경, 삭제 등 시스템에 대한 기본 권한
• dba: 데이터베이스 관리에 대한 권한
• sysdba : 데이터베이스 시작과 종료 및 관리 권한
• sysoper : sysdba + 데이터베이스 생성에 관한 권한

SQL> SELECT * FROM DBA_SYS_PRIVS; # SYS 권한
SQL> SELECT * FROM ROLE_SYS_PRIVS; # ROLE 확인

 

Q1. 기본 문장을 출력하자.

SQL> SET SERVEROUTPUT ON
SQL> BEGIN 
  2  DBMS_OUTPUT.ENABLE;
  3  DBMS_OUTPUT.PUT_LINE('1.HELLO ORACLE');
  4  DBMS_OUTPUT.DISABLE;
  5  DBMS_OUTPUT.PUT_LINE('2.HELLO JAVA');
  6  END;
  7  /

PL/SQL procedure successfully completed.

 

 

Q2. i라는 변수를 선언하고 20을 대입한 후 출력해보자.

SQL> DECLARE
  2     i integer := 20;    #변수 대입 연산자 -->  :=
  3  BEGIN
  4  DBMS_OUTPUT.PUT_LINE('i의 값은 '|| i); 
  5  END;
  6  /

PL/SQL procedure successfully completed.

 

 

Q3. 사칙 연산을 출력해보자.

SQL> DECLARE
  2  i int := 100; j int := 200; hap int := 0;
  3  sub int := 0; mul int :=0; div int := 0;
  4  BEGIN
  5  hap := i+j; sub := j-i; mul := i*j; div := j/i;
  6  DBMS_OUTPUT.PUT_LINE(i||'+'||j||'='||hap);
  7  DBMS_OUTPUT.PUT_LINE(i||'-'||j||'='||sub);
  8  DBMS_OUTPUT.PUT_LINE(i||'*'||j||'='||mul);
  9  DBMS_OUTPUT.PUT_LINE(i||'/'||j||'='||div);
 10  END;
 11  /

PL/SQL procedure successfully completed.

 

 

Q4. 테이블을 생성하자.

SQL> CREATE TABLE TEST01(
  2  NO NUMBER(3),
  3  IRUM VARCHAR2(20));

Table created.

SQL> BEGIN
  2  FOR I IN 1..10 LOOP
  3  INSERT INTO TEST01 VALUES(I,'홍길동'||I);
  4  END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

 

 

Q5. 사원테이블의 10번 부서의 평균 월급을 구하는 구문을 작성하자.

SQL> DECLARE
  2  avg01 number(7,3) := 0;
  3  deptno01 number(7) := 10;
  4  BEGIN
  5  SELECT AVG(SAL) INTO avg01
  6  FROM EMP
  7  WHERE DEPTNO = deptno01
  8  GROUP BY DEPTNO;
  9  DBMS_OUTPUT.PUT_LINE(deptno01 ||'번 부서평균은 ['||avg01 ||']');
 10  END;
 11  /

PL/SQL procedure successfully completed.

 

 

 

 

 

Database PL/SQL Language Reference - Contents