1. ORACLE PL/SQL 시작하기

1. ORACLE PL/SQL 시작하기

 

1. PL/SQL이란?

 : ada이라는 언어를 기반으로 오라클에서 제작한 프로그래밍 언어.

 - 기존 SQL보다 반복적이거나 조건을 받아서 처리하는 작업에서 편리하게 사용할 수 있다.

 

BEGIN FOR I IN 1..1000 loop inser into table values (i); end loop; end;   /  슬러쉬치면 실행됨.

 

 

2. PL/SQL의 런타임 구조.

 

  : sql문을 잘짜야 pl/sql이 빠르게 돌 수 있다.

 

 

 : PL/SQL 이 포함된 블록을 실행하면 오라클 서버 내에 메모리에 상주 해있는  PL/SQL엔진이  해당  블록을  받게  된다. 

  (이  PL/SQL  엔진이  상주하는  메모리는  사용하는 Application 의 종류에 따라 오라클 서버에 있기도 하고 Application 서버에 있기도 하다.)
 -> 그 후 해당 블록에 있던 모든  SQL 문장들은 오라클 서버 프로세스에게 전달되어 수행된다.  

  즉  PL/SQL 엔진은 해당  SQL 문장이 수행되어 결과가 돌아 올 때까지 기다렸다가 그 결과를 받고 난 후 나머지 PL/SQL 문장을 실행.

 

 - 데이터베이스에서 처리된 데이터를  PL/SQL 이 저장하기 위해 변수를 선언해야 하며 이 변수를 잘 못 선언 할 경우 데이터베이스에서 처리된 결과를  PL/SQL 엔진이 사용할 수 없게 된다. 
 
-  PL/SQL 엔진이  SQL 을 발견하게 되면  Context 변환 과정을 거친 후 오라클 서버 프로세스에게 전달되고 오라클 서버 프로세스가  SQL 문장 수행과정(Parse  -> Bind  -> Execute -> Fetch (select 인 경우만) ) 을 거쳐서 쿼리를 수행 한 후, 값을 리턴 해야  PL/SQL 의 나
머지 부분이 실행된다.

 ( 예. SQL구문을 냉장고, 변수들을 접시라고 생각하면, 냉장고에서 음식을꺼내서 접시에 덜어서 쓰는 식으로 생각하면 쉽다.)

 

 

3. PL/SQL 기본 구조

 

* 블록의 기본 구성

- 선언부와 예외처리부는 경우에 따라 생략가능.

 

* 블록의 유형

1) Anonymous PL.SQL block(익명블록) : 일회성으로 사용할 경우.

2) Stored PL/SQL Block(저장된 블록) : 서버에 파싱해서 저장해놓고 주기적으로 반복

 

블록내의 각 부분에 포함되는 명령들 중 DECLARE, BEGIN , EXCEPTION 과 같은 예약어들은  ; (세미콜론) 으로 끝나지 않지만,

 나머지 명령어들은  SQL 문장처럼 세미콜론(;)으로 끝이 난다.

 

 

* 참고 *   -PL/SQL 은 기본적으로 결과를 화면에 출력하지 않으므로 결과를 화면에 출력하고 싶으면 미리 사전작업이 필요.  SQL> SET SERVEROUTPUT ON ;  <- 화면 출력기능 활성화   - PL/SQL 은  작성시  오류가  있을  경우  오류에  대한  상세  내용을  보여주지  않는다.  SQL> SHOW ERRORS ;   - pl/sql은 대소문자구분안한다. 소문자로써도 대문자로 인식함.

 

 [연습1]

 : emp  테이블에서  empno  가  7900  번인  사원의  사번과  이름을  화면에  출력. (간단하지만 중요한 부분이니 꼭 직접 입력해 실행해볼것!)
 

 

 

4. PL/SQL 문 내에서의  SQL 문장 사용하기

  - END 키워드 : 트랜잭션의 끝이 아니라  PL/SQL 블록의 끝이다.  
  - PL/SQL은 DDL(데이터 정의어) 문을 직접 지원하지 않습니다.  -> pl/sql안에 select문 안에 쓰는건 가능하지만, pl/sql안에 바로쓸순없다.
 (DDL 문은 동적 SQL문 이므로, 런타임에 문자열로 작성되며 파라미터의 위치 표시자를 포함할 수 있으므로, 동적  SQL을 사용하면  PL/SQL에서 DDL 문을 실행할 수 있다. ) 
  - PL/SQL은 GRANT 또는  REVOKE와 같은 DCL(데이터 제어어) 문을 직접 지원하지 않습니다. 
   (동적  SQL을 사용하여 DCL 문을 실행할 수 있습니다. )

 

 1) PL/SQL 내에서의  SELECT 문장 사용하기

 

[문법]

SELECT select_list INTO {variable_name[, variable_name]...| record_name} FROM table [WHERE condition];

 

 사용 예  1 :
 professor 테이블에서 교수번호가 1001 번인 교수의 교수번호와 급여를 조회 한 후 변수에 저장해서 화면에 출력하세요.

 

SCOTT>DECLARE  pl/sql블록이 시작    2    v_profno  professor.profno%TYPE ;  변수2개 선언    3    v_pay     professor.pay%TYPE ;    4  BEGIN    실행부 시작    5    SELECT profno , pay INTO v_profno ,v_pay    6    FROM professor    7    WHERE profno=1001 ;    8    9  DBMS_OUTPUT.PUT_LINE(v_profno||' 번 교수의 급여는  '||v_pay||' 입니다') ;   화면에 결과를 출력.(c언어의 printf함수와 비슷)   10   11  END ;    pl/sql블록이 종료   12  /    해당블록 수행 1001 번 교수의 급여는  550 입니다   PL/SQL procedure successfully completed.

 

 

사용 예  2:
emp2 테이블을 사용하여 사원번호를 입력 받아서 사원의 사번과 이름, 생일을 출력하세요.

 

SCOTT>DECLARE    2     v_empno      emp2.empno%TYPE ;    3    v_name       emp2.name%TYPE ;    4    v_birth       emp2.birthday%TYPE;    5  BEGIN    6    SELECT empno, name, birthday    7    INTO v_empno ,v_name, v_birth    8    FROM emp2    9    WHERE empno = &empno ;   사용자에게 값을 입력받아 변수에 할당할 때 &기호 사용.   10    DBMS_OUTPUT.PUT_LINE(v_empno||'  '||v_name||'  '||v_birth);   11  END ;   12  / Enter value for empno: 20000102 20000102  김설악   22-MAR-83   PL/SQL procedure successfully completed.

 

 

사용 예  3:
사용자로부터 교수번호를 입력 받은 후 professor 테이블을 조회하여 해당 교수의 교수번호와 교수이름, 부서번호, 입사일을 출력하세요.

SCOTT>DECLARE    2    v_profno   professor.profno%TYPE ;    3    v_name     professor.name%TYPE ;    4    v_deptno   professor.deptno%TYPE ;    5    v_hdate       professor.hiredate%TYPE ;    6                                                     7  BEGIN                                             8    SELECT profno, name, deptno , hiredate    9    INTO v_profno, v_name, v_deptno, v_hdate   10    FROM professor   11    WHERE profno = '&교수번호' ;   12   13    DBMS_OUTPUT.PUT_LINE(v_profno||'  '||v_name||'  '||v_deptno||'  '||v_hdate);   14   15  END;   16  /   Enter value for 교수번호: 1001 1001  조인형   101  23-JUN-80   PL/SQL procedure successfully completed.

 

2) PL/SQL 내에서의 DML 문장 사용하기 (INSERT , UPDATE , DELETE , MERGE)

 

(1) INSERT 문장 수행하기 예 1:

[준비작업] 테스트용 테이블 pl_test 와 시퀀스를 아래와 같이 먼저 생성. 
 

SCOTT>CREATE TABLE pl_test    2  (no  number ,    3   name  varchar2(10)) ;   Table created.   SCOTT>CREATE SEQUENCE pl_seq ; Sequence created.

 
- PL/SQL 에서  INSERT .

 

SCOTT> BEGIN    2    INSERT INTO pl_test    3    VALUES(pl_seq.NEXTVAL,'AAA');    4   END ;    5   /   PL/SQL procedure successfully completed.   SCOTT>/ PL/SQL procedure successfully completed.   SCOTT>SELECT * FROM pl_test ;       NO    NAME ---------- ----------        1     AAA        2    AAA   SCOTT>commit ; Commit complete.

 

(2) INSERT 문장 수행하기 예 2 :
 [준비작업] pl_test2 테이블을 생성하세요.

 

SCOTT>CREATE TABLE pl_test2    2  (no  number ,    3   name  varchar2(10),    4   addr  varchar2(10) ); Table created.

 

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

 

SCOTT> SET VERIFY OFF SCOTT>DECLARE    2  v_no number := '&no';    할당연산자      3  v_name varchar2(10) := '&name' ;    4  v_addr varchar2(10) := '&addr' ;    5    6  BEGIN    7   INSERT INTO pl_test2  VALUES(v_no, v_name, v_addr) ;  변수에 있는 값을 테이블에 입력작업수행.    8   END ;     9   / Enter value for no: 10 Enter value for name: AAA Enter value for addr: 서울   PL/SQL procedure successfully completed.   SCOTT>SELECT * FROM pl_test2;       NO    NAME    ADDR ---------- ---------- ----------      10     AAA       서울

 

 

 

* 할당연산자와 비교연산자 *   a:=20   a에 20을 담아라. (할당 연산자) a=20    a가 20인지 비교해봐라. (비교 연산자)

 

 

(3) PL/SQL 에서 UPDATE 수행.

 

SCOTT>BEGIN    2    UPDATE pl_test    3    SET name='BBB'    4    WHERE no = 2 ;    5  END ;    6  /   PL/SQL procedure successfully completed.   SCOTT>SELECT * FROM pl_test ;       NO    NAME ---------- ----------        1    AAA        2     BBB   SCOTT>commit; Commit complete.

 
(4) PL/SQL 에서 DELETE 를 수행.

SCOTT>BEGIN    2    DELETE FROM pl_test    3    WHERE no=1 ;    4  END ;    5  /   PL/SQL procedure successfully completed.   SCOTT>SELECT * FROM pl_test ;       NO    NAME ---------- ----------        2     BBB   SCOTT>commit ; Commit complete.

 

(5) PL/SQL 에서 MERGE 작업을 수행. 

[준비작업] 연습용 테이블 pl_merge1 과 pl_merge2 테이블을 생성.

SCOTT>CREATE TABLE pl_merge1    2  ( no  number ,    3    name varchar2(10));   Table created.   SCOTT>CREATE TABLE pl_merge2    2  AS SELECT * FROM pl_merge1 ;   Table created.   SCOTT>INSERT INTO pl_merge1 VALUES(1,'AAA'); 1 row created.   SCOTT>INSERT INTO pl_merge1 VALUES(2,'BBB'); 1 row created.   SCOTT>INSERT INTO pl_merge2 VALUES(1,'CCC'); 1 row created.   SCOTT>INSERT INTO pl_merge2 VALUES(3,'DDD'); 1 row created.   SCOTT>commit; Commit complete.   SCOTT>SELECT * FROM pl_merge1 ;        NO   NAME ---------- ----------        1    AAA        2     BBB   SCOTT>SELECT * FROM pl_merge2;       NO    NAME ---------- ----------        1    CCC        3    DDD

 

- PL/SQL에 Merge 수행.

 

SCOTT> BEGIN    2    MERGE INTO pl_merge2 m2  --pl_merge2에 합병하겠다.    3    USING pl_merge1 m1            --pl_merge1을 사용해서.    4    ON(m1.no = m2.no)    5    WHEN MATCHED THEN   --매치되면    6      UPDATE SET                   --update한다.    7       m2.name = m1.name    8    WHEN NOT MATCHED THEN   --매치되지 않으면    9      INSERT VALUES(m1.no , m1.name);   --insert시킨다.   10    END ;   11   /   PL/SQL procedure successfully completed.   SCOTT>SELECT * FROM pl_merge1 ;       NO    NAME ---------- ----------         1    AAA         2    BBB   SCOTT>SELECT * FROM pl_merge2 ;       NO    NAME ---------- ----------         1    AAA     --CCC가 AAA로 update됨.         3    DDD         2   BBB   -- insert됨.

 

 

6. PL/SQL 에서의 렉시칼  (Lexical)

 * PL/SQL 에서의 렉시칼이란?

 :  PL/SQL 안에 사용되는 문자들을 의미하며 식별자  , 구분자  , 리터럴, 주석 등으로 구성.

  1) 식별자 : PL/SQL 객체에게 부여되는 이름. (테이블 이름, 변수명 등)
  - 오라클 키워드는 식별자로 사용할 수 없다.

  * 식별자를 쌍따옴표로 묶어서 사용할 수 있는 경우. (따옴표로 묶인 식별자 사용하는 것은 권장하지 않음!!)

- 식별자의 대소문자 구분이 필요한 경우   - 공백과 같은 문자 포함할 경우   - 예약어를 사용해야 할 경우  이러한 변수를 연이어 사용할 때는 항상 큰 따옴표(쌍 따옴표)로 묶어야 한다.

 

  2) 구분자 : 특별한 의미를 지닌 기호.

 

기호	의미	기호	의미
+	더하기 연산자	< >	부등호 연산자
-	빼기  / 부정 연산자	!=	부등호 연산자
*	곱하기 연산자	|	연결 연산자
/	나누기 연산자	--	단일행 주석 표시자
=	등호 연산자	/*	주석 시작 구분자
@	원격 엑세스 표시자	*/	주석 종료 구분자
;	명령문 종료자	:=	할당 연산자

 

 3) 리터럴: 변수에 할당되는 모든 값은. (식별자가 아닌 모든 문자, 숫자, 부울 또는 날짜 값)    
 * 주요 리터럴의 종류

 

문자 리터럴	모든 문자열 리터럴은 데이터 유형이 CHAR 또는 VARCHAR2이므로 문자 리터럴이라고 한다. (예: abcd 및  12f 등)
숫자 리터럴	숫자 리터럴은 정수 또는 실수 값을 나타낸다. (예: 123 및  1.234 등).
부울 리터럴	부울 변수에 할당된 값은 부울 리터럴입니다.   - TRUE, FALSE 및 NULL 은 부울 리터럴이거나 키워드입니다.

 4) 주석 : 설명 기록할 때.

 

–- (하이픈 2개)	한 줄
/*   */	여러 줄

 

 

7. PL/SQL 에서의 사용되는 주요 연산자들 (우선순위)

 

연산자	의 미
**	제곱 연산자
+ , -	일치  , 부정
* , /	곱하기  , 나누기
+ , - , ||	더하기  , 빼기  , 연결하기
=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN	비교 연산자
NOT	논리 부정 연산자
AND	두 조건 모두 참일 경우 참 을 반환
OR	두 조건 중 한가지만 참 일 경우 참 을 반환

 

 

8. PL/SQL 에서 블록 구문 작성시 주의사항
 
1) 문자 리터럴이나 날짜 리터럴 사용시 반드시 홑 따옴표로 묶어서 표시. 
 
2) 프로시져 내에서는 단일행 함수만을 사용해야 하며 DECODE 함수나 그룹 함수는 사용 할 수 없다.

 -> PL/SQL 내부에 포함되어 있는  SQL 문장에서는 위 함수들을 쓸 수 있지만 그 외의  PL/SQL 문장에서는 사용 할 수 없다. 
 
3) 데이터의 형 변환에 유의.
: 묵시적 형 변환은 문자와 숫자  , 문자와 날짜를 연산 할 때 발생하며 이 부분 때문에 성능에 의도하지 않게 나쁜 영향을 줄 수 있으므로 데이터 형의 일치에 항상 주의.