day11_2 - 오라클 (PL/SQL 기본구조, 익명 블록, 작성 규칙, 주의 사항, 연산자, 변수)

[PL/SQL]

-오라클에서 제공하는 프로그래밍 언어

 

- Procedural Language/SQL 의 약자

 

- 일반 프로그래밍 언어적인 요소를 거의 다 가지고 있다.

- 실무에서 요구 되는 절차적인 데이터 처리를 다 할 수 있음. 특히 SQL 과 연동되어서 막강한 기능을 구현할 수 있음.

- 데이터 트랜잭션 처리능력이나 정보보호, 데이터에 대한 보안, 예외처리 기 능, 객체지향 등 데이터베이스와 관련된 중요한 모든 기능을 지원

 

- 데이터베이스 업무를 처리하기에 최적화된 언어임.

 

 

 

 

[PL/SQL 기본구조]

- 선언부(DECLARE), 실행부(BEGIN), 예외처리부 (EXCEPTION)로 구성됨.

 

- Anonymous PL/SQL Block(익명블록) 과 Stored PL/SQL Block(저장된 블록) 이 있음.

 

- 익명 블록은 이름이 없다는 뜻. 이름이 없다는 뜻이란 불러 쓸 수 없고, 저장할 수 없다는 뜻

- 익명 블록은 주로 일회성으로 사용할 경우 많이 사용이 되고, 저장된 블록은 서 버에 파싱해서 저장해 놓고 주기적으로 반복해서 사용할 경우 사용됨.

- Stored PL/SQL Block 은 다른 용어로 서브프로그램 또는 프로그램 단위라고도 하며, 스키마를 구성하는 오브젝트로서 파싱 된 후 오라클 서버 내부에 저장되 거나 오라클 툴 안에 라이브러리 형태로 저장되어 있음.

 

 

- 명령들 중 DECLARE, BEGIN , EXCEPTION 과 같은 예약어들은 ;(세미콜론) 으로 끝나지 않음

 

- 나머지 명령어들은 SQL 문장처럼 세미콜론(;)으로 끝남

 

 

 

 

 

[PL/SQL 결과 화면 출력]

- PL/SQL 은 기본적으로 처리된 PL/SQL 문장의 결과를 화면에 출력하지 않음

 

- 그래서 결과를 화면에 출력하고 싶으면 아래와 같이 미리 사전작업이 필요

 

SQL> SET SERVEROUTPUT ON ; <- 화면 출력기능을 활성화 문장

 

 

 

 

 

[익명 블록]

DECLARE 

   vi_num NUMBER; -- 이 부분에서 변수 선언

BEGIN 

    vi_num := 100; -- 변수에 100 저장

   DBMS_OUTPUT.PUT_LINE(vi_num); -- 화면에 출력

 

END;

 

 

 

 

 

 

 

[PL/SQL 블록 작성시 기본 규칙과 권장 사항]

 

-문장은 여러 줄에 걸쳐질 수 있으나, 키워드는 분리될 수 없음(SQL 문법과 동일) 

 

-블록의 내용을 읽기 쉽도록 공백문자를 사용하여 키워드 내지는 문장을 적절하게 분리함으로써 의미분석이 되도록 하며, 들여쓰기도 권장.

- 예약어는 식별자명으로 사용될 수 없으나, Alias 로는 사용될 수 있음. 즉 이중부호(“ )를 함께 사용할 수는 있다는 뜻. (예: “ TABLE ” )

-식별자명은 기본 오라클 Naming Rule 을 준수

 

-리터럴(문자,날짜)은 단일인용부호(‘)로 표시해야 하며 널 값은 NULL 상수로 기술

-주석처리를 하고자 할 경우에 단일 행은 -- (하이픈 두 개), 복수 행은 /* 로 시작하고 */ 로 종료함

- PL/SQL 블록내의 명령(수식)에서는 오라클 함수를 사용할 수 있으나 그룹함수와 DECODE 함수는 SQL 문장에 포함되어야만 사용될 수 있다. 만약 다른 경우에 그룹함수와 DECODE 함수를 사용할 경우 에러가 발생하며 PL/SQL 에서는 에러(EXCEPTION)처리와 관련된 함수에는 SQLCODE 함수와 SQLERRM 함수가 별도로 존재

 

 

 

 

 

 

[PL/SQL 주의 사항]

- 일반 sql 문은 문장에 커서를 올려 놓고 ctrl + enter 눌러서 쉽게 실행 시킬 수 있지만 PL/SQL은 문장을 드래그하고 스크리브 실행 버튼을 눌러 실행하는 것이 안전하다.

 

-> 이렇게 하지 않을 경우 error 가 발생할 수 있음

-> 기존 sql문과 PL/SQL은 문은 파일을 따로 분리하는 것 권장한다고 함

 

 

 

 

 

 

[PL/SQL 예제]

--1.

DECLARE a INTEGER := 2**2*3**2;
BEGIN

   DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));

   DBMS_OUTPUT.PUT_LINE('a = ' || a);
END;

 

-> DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a)); 에서 자바의 +로 연결 하는 것 대신 ||로 연결 하는 것 기억할 것

 

 

 

 

 

 

--2.

BEGIN 

   DBMS_OUTPUT.PUT_LINE('3 * 1 = ' || 3*1); 

   DBMS_OUTPUT.PUT_LINE('3 * 2 = ' || 3*2); 

   DBMS_OUTPUT.PUT_LINE('3 * 3 = ' || 3*3); 

   DBMS_OUTPUT.PUT_LINE('3 * 4 = ' || 3*4); 

   DBMS_OUTPUT.PUT_LINE('3 * 5 = ' || 3*5); 

   DBMS_OUTPUT.PUT_LINE('3 * 6 = ' || 3*6); 

   DBMS_OUTPUT.PUT_LINE('3 * 7 = ' || 3*7); 

   DBMS_OUTPUT.PUT_LINE('3 * 8 = ' || 3*8); 

   DBMS_OUTPUT.PUT_LINE('3 * 9 = ' || 3*9);
END;

 

-> 선언이 필요 없는 문장이기 때문에 DECLARE문 없이 실행문만 존재하는 경우

 

 

 

 

 

--3.

 

-> SELECT empno , ename INTO vno , vname 이부분이 기존 sql의 select 와 다른 부분

 

     - INTO 부분이 다른데 기존 sql은 쿼리가 알아서 프린트 해주지만 pl/sql에는 이렇게 받아서 처리해주는 기능이 없           다.

 

     - 따라서 실행부에서 select로 조건절에 맞게 데이터를 가져온 후 순서에 맞게 into로 변수에 보내주는 것

 

     - 이때 변수에는 하나의 값만 들어갈 수 있기 때문에 where로 꼭 조건을 정해서 정해진 하나의 값만 넣을 수 있도록         할 것

 

 

-> 뒤에 주로 커서를 이용해 더 간편하게 할 예정

 

 

 

 

 

--4.

DECLARE 

   v_profno professor.profno%TYPE ;  -- professor.profno의 타입으로 생성

   v_pay professor.pay%TYPE ; -- professor.pay의 타입으로 생성
BEGIN 

   SELECT profno , pay INTO v_profno ,v_pay

   FROM professor

   WHERE profno=1001 ;
   DBMS_OUTPUT.PUT_LINE(v_profno ||' 번 교수의 급여는 '|| v_pay ||' 입니다') ;
END ;

 

 

 

--5.

DECLARE 

   v_profno professor.profno%TYPE ; 

   v_pay professor.pay%TYPE ;
BEGIN 

   SELECT profno , pay

   INTO v_profno ,v_pay

   FROM professor

   WHERE profno=‘&profno’ ;
   DBMS_OUTPUT.PUT_LINE(v_profno || ' 번 교수의 급여는 ' ||v_pay|| ' 입니다') ;
END ;

 

-> WHERE profno=‘&profno’ ; 여기서 profno가 숫자라면 ' '을 굳이 사용하지 않아도 상관 없다.

-> 문자일 경우라면 반드시 ' '로 묶어줄 것

 

 

 

 

 

 

 

 

[PL/SQL INSERT]

-번호(no) , 이름(name) , 주소(addr) 값을 입력 받은 후 pl_test2 테이블에 입력하는 PL/SQL 문장을 작성하기.

 

CREATE TABLE pl_test2 2 (no number , 3
name varchar2(10),
4 addr varchar2(10) );

 

--실습용 테이블 작성

CREATE TABLE pl_test2 

(no number ,
name varchar2(10),
addr varchar2(10) );

 

결과

 

-- PL/SQL INSERT

DECLARE 

   v_no number := '&no'; 

   v_name varchar2(10) := '&name' ; 

   v_addr varchar2(10) := '&addr' ;
BEGIN
   INSERT INTO pl_test2

   VALUES(v_no, v_name, v_addr) ;
END ;

 

 

 

 

[PL/SQL 에서의 렉시칼 - 문자집합]

- 식별자: 식별자는 PL/SQL 객체에게 부여되는 이름.

 

- 즉 테이블 이름이나 변수명 등은 모두 식별자임.

 

- 오라클 키워드는 식별자로 사용할 수 없음.

 

- 식별자 중에서 특별히 아래와 같은 경우 식별자를 따옴표로 묶어서 사용 가능함.

      -> 식별자의 대소문자 구분이 필요한 경우

      -> 공백과 같은 문자 포함할 경우
      -> 예약어를 사용해야 할 경우

 

 

 

- 이러한 변수를 연이어 사용할 때는 항상 큰 따옴표(쌍 따옴표)로 묶어야 하지만 따옴표로 묶인 식별자를 사용하는 것은 권장하지 않음.

 

 

DECLARE 

"Pi" number := 3.14; 

"table" number :=20; 

"select no" number :=999;
BEGIN 

   DBMS_OUTPUT.PUT_LINE("Pi"||' '||"table"||' '||"select no");
END;

 

- 가능은 하지만 굳이 권장하지 않는다.

 

 

-구분자: 구분자는 특별한 의미를 지닌 기호.

- 예를 들어 SQL 문장을 끝낼 때는 끝내는 의미를 가진 ; (세미콜론)을 사용.

 

-리터럴: 엄밀히 말하면 변수에 할당되는 모든 값은 리터럴이다.

 

- 식별자가 아닌 모든 문자, 숫자, 부울 또는 날짜 값은 리터럴 이라는 의미.

- 리터럴은 종류가 여러 가지가 있는데 주요 리터럴은 다음과 같이 분류

 

 

- 문자 리터럴: 모든 문자열 리터럴은 데이터 유형이 CHAR 또는 VARCHAR2이므로 문자 리터럴이라고 한다.

(예: abcd 및 12f 등)

- 숫자 리터럴: 숫자 리터럴은 정수 또는 실수 값을 나타낸다.

(예: 123 및 1.234 등)

- 부울 리터럴: 부울 변수에 할당된 값은 부울 리터럴이다. TRUE, FALSE 및 NULL 은 부울 리터럴이거나 키워드이다.

 

 

 

 

 

 

 

 

[PL/SQL 에서의 블록 구문 작성 지침]

 

1) 문자 리터럴이나 날짜 리터럴 사용시에는 반드시 홑 따옴표로 묶어서 표시해야 한다.

 

2) 문장에서의 주석은 한 줄일 경우 -- (하이픈 두 개)를 써서 표시하고 여러 줄 일 경우 /* ~ */ 기호를 사용해서 표시해야 한다.

3) 프로시져 내에서는 단일행 함수만을 사용해야 하며 DECODE 함수나 그룹 함수는 사용 할 수 없다. 이 말의 의미는 PL/SQL 내부에 포함되어 있는 SQL 문장에서는 위 함수들을 쓸 수 있지만 그 외의 PL/SQL 문장에서는 사용 할 수 없다는 의미.

4) 시퀀스를 사용할 때 - 11g 이전 버전에서는 시퀀스를 사용하기 위해서는 SQL 문장을 이용하여 시퀀스를 변수에 할당 한 후 해당 변수 값을 사용했으나 11g 버전 부터는 PL/SQL 문장에서 바로 시퀀스를 사용.

5) 데이터의 형 변환에 주의. 데이터의 형 변환은 묵시적 형 변환(자동 형 변환) 과 명시적 형 변환 (수동 형 변환) 으로 나눌 수 있다. 이 부분은 SQL 에서의 규칙과 동일. 묵시적 형 변환은 문자와 숫자 , 문자와 날짜를 연산 할 때 발생하며 이 부분 때문에 성능에 의도하지 않게 나쁜 영향을 줄 수 있으므로 데이터 형의 일치에 항상 주의.

 

 

 

[PL/SQL 에서 연산자 사용]

 

 

 

 

[PL/SQL 변수]

[변수 생성 규칙 ]

- 반드시 문자로 시작해야만 한다.

- 문자나 숫자, 특수문자를 포함 가능.

- 변수명은 30 bytes 이하여야 한다.

- 예약어를 포함하면 불가능

- 선언부에서 선언되고 원한다면 특정 값으로 초기화도 가능.

- 실행부에서 실행되면서 값이 할당.

- 서브 프로그램의 파라미터로 전달되기도 하며 서브 프로그램의 출력결과를 저장하기도 한다.

 

 

 

 

 

 

 

 

 

CONSTANT : 수정 불가변수 정의함

 

DECLARE 

   V_EMPNO CONSTANT NUMBER(1) := 3;
BEGIN 

   V_EMPNO:=5;

   DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);
END;

 

 

 

 

DEFAULT 기본값 정의 수정 가능함

 

 

DECLARE 

   V_CODE NUMBER(2) NOT NULL DEFAULT 10;

BEGIN

   V_CODE:=20; DBMS_OUTPUT.PUT_LINE('V_CODE : ' || V_CODE);
END;

 

 

 

 

Reference 변수 %TYPE:COLUMN 타입, %ROWTYPE:ROW TYPE 타입

- Vno emp.empno%TYPE <- emp 테이블의 empno와 동 일한 데이터형으로 선언함.

- Vname emp.ename%TYPE <- emp 테이블의 ename 과 동 일한 데이터형으로 선언함.

- Vrow emp%ROWTYPE <- emp 테이블의 여러 컬럼을 한꺼번에 저장할 변수로 선언함.