12. PL/SQL - SQL*Plus

PL/SQL이란?

- Procedral Language / Stractured Query Language의 약어

- SQL을 확장한 절차적 언어(Procedural Language)로서 SQL의 단점 보완

   * 절차: 어떤 상황을 거치기 위한 단계

- 관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어

- 오라클에서 지원하는 프로그래밍 언어의 특성 수용

 

목적

아래의 단점 보완

- 변수 / 제어문(조건, 반복문) / 예외처리 없음

→ 변수 선언 / 비교 처리 / 반복 처리가 가능해짐.

- 한번에 단 하나의 명령문만 사용 가능해서 명령문이 많아질수록 트래픽이 상대적으로 증가

 

장점

- 성능 항샹: 잘 만들어진(튜닝) PL/SQL이란 가정하에 좋아진다..

- 모듈식 프로그램 개발 가능: 논리적 작업을 진행하는 여러 명령어들을 하나의 블록에 만들 수 있다.

* 모듈식: 재사용성을 높임

- 이식성 좋음

- 예외처리 가능

 

 

PL/SQL의 주요 특징

① 블록 단위의 실행 제공 * 블록: 프로그래밍 기본 단위(자바의 객체와 비슷한 의미)

- 블록은 프로그램 코드와 변수 선언문을 모두 가짐.

- (형식) DECLARE ~ BEGIN ~ EXCEPTION ~ END;

- 기본적으로 실행되지 않고 마지막 라인에 '/ '를 입력해야 해당 블럭이 실행.

* SQL Developer에선 생략 가능..

​

② 변수, 상수 등을 선언

- SQL과 절차형 언어에서 사용

- DECLARE절에서만 사용 가능 * 값의 할당은 BEGIN 섹션에서도 가능

- if문 사용 가능 → 조건에 따라 문장 분기 가능

- for문과 같은 반복문도 사용 가능 → 일련의 문장들 반복 가능

- JDBC의 커서(Cusor) 사용 가능 → 한번에 여러 행 검색 가능(JDBC ResultSet 인터페이스)

- 기본적으로 사용 가능한 SQL: DML, TCL

↔ DDL과 DCL은 사용할 수 없고 사용하려면 동적 SQL을 이용해야 함

- SQL*plus에서는 마지막에 '/'를 입력해야 함(SQL developer는 쓰든 안쓰든 상관 없음)

 

PL/SQL 기본구조

* 주의사항

- DECLARE, BEGIN, EXCEPTION에는 세미콜론 사용하지 않음

- END와 각 문장들( 변수/상수 선언문, 실행문, 예외처리문) 끝에는 세미콜론 반드시 작성

- PL/SQL 블록 내부에서도 주석 사용 가능(단일행: -- / 여러행: /* */)

- sql*plus에서는 PL/SQL 블록이 끝났음을 명시하기 위해 반드시 '/' 작성해야 함.

 

예제: 간단한 메세지 출력

-- ① SERVEROUTPUT
--   오라클에서 제공하는 프로시저를 사용하여 출력하고 싶은 내용을 화면에 보여주도록 하는 환경변수
SET SERVEROUTPUT ON

-- ② 메시지 출력하기 
BEGIN 
 DBMS_OUTPUT.PUT_LINE('안녕하세요?');
END;

​

---

변수 사용하기 

PL/SQL 선언부에서 실행부에서 사용할 변수 선언. 선언 시 변수명 다음에 자료형 기술

 

변수

프로그램(프로시저)에서 사용되는 데이터를 저장하는 공간

→ 변수는 DECLARE 블럭 안에서 아래와 같은 형식으로 선언/초기화

 

1. 변수를 선언하며 동시에 값을 할당(초기화) 하는 경우

- := 연산자: 대입(할당)

-- 형식: 변수이름 데이터타입 := 값;
vempno number := 10;

​

2. 변수를 선언만 하고, 값을 할당(초기화)하지 않는 경우

- 변수이름 데이터타입;

​

* 초기화되지 않은 변수는 BEGIN 블럭 안에서 반드시 초기화 해야 한다.

  PL/SQL의 변수 이름은 대/소문자를 구분하지 않는다.

  변수에 저장된 데이터는 대/소문자를 구분

 

예제2: 변수를 선언, 할당하고 그 변수의 값을 출력

BEGIN
    vempno := 7788;
    vename := 'SCOTT';
    DBMS_OUTPUT.PUT_LINE('사번 / 이름');
    DBMS_OUTPUT.PUT_LINE('*---------');
    DBMS_OUTPUT.PUT_LINE(vempno || ' / ' || vename);
END;

 

---

sql*plus 프로그램 사용 방법1

※ sql developer 프로그램과 동일한 기능을 수행하는 프로그램

  * dos명령창을 사용: 명령어를 직접 입력하여야 한다.

 

① 실행

- 윈도우: 왼쪽 하단에 run sql command 입력

 

 

② conn 입력 후 user, password 입력

 

③ ed test1 입력 → 메모장 생김

 

④ 명령어 입력 후 저장

⑤ dos창에 @파일이름 작성하여 확인

 

 

변수의 데이터형

① 스칼라: 변수를 선언할 때 사용되는 자료형은 SQL에서 사용하던 자료형과 거의 유사.

② 레퍼런스: 이전에 선언된 변수 혹은 데이터베이스 컬럼에 맞추어 변수를 선언하기 위해 %TYPE 속성 사용

     = 그 변수 혹은 컬럼과 같은 타입의 형을 쓰겠다

-- 형식: 변수이름 테이블이름, 컬럼이름;
vempno emp.empno%TYPE;   ---> empno가 갖고 있는 타입과 vempno를 동일하게 함

* 명령어 실행 전

a. SQL> 프롬포트에서 DESC 명령어 작성하고 실행

b. SELECT * FROM user_tables;

c. SELECT * FROM tab

결과

 

※ emp_table의 정보 하나만 간략하게 보기..

 

 

 

 

 

 

 

 

---

SQL*PLUS 명령어

SQL 명령문과 SQL*PLUS의 명령어는 다른 개념

SQL문	SQL*Plus 명령문
데이터베이스 자료 검색, 수정, 삭제하는 데이터베이스언어	툴에서 출력 형식을 지정하는 등의 환경 설정
관계형 데이터베이스 ANSI 표준 언어	SQL문을 실행 시킬 수 있는 오라클 툴
여러 줄 실행	한 줄 실행
종결문자(;) 필요	종결문자(;) 불필요
연결문자(-) 불필요	연결문자(-) 필요
키워드 단축 불가	키워드 단축 가능

자주 사용하는 SQL*Plus 명령어
명령어	기능
LIST, RUN, @, /	편집 명령
SAVE, GET, EDIT, SPOOL	파일 명령
HOST, EXIT	데이터베이스 접속 및 종료
LINE, PAGE	출력 형식

 

1. 편집 명령

- LIST(L): 버퍼에 저장된 모든 SQL문 또는 검색한 라인의 SQL문 나타냄

- / : SQL문을 보여주지 않고 바로 실행

- RUN(R) : 버퍼에 저장된 SQL문 보여주고 실행 = LIST(L) + / → 많이 사용

​

(SQL*Plus 프로그램 실행2)

* 윈도우 하단 실행 입력 후 cmd 들어가기

​

① DBMS에 접속(연결하기)

- sqlplus 사용자이름/비밀번호

 

② 명령어 입력하기 -> 버퍼에 저장됨

SELECT ENAME, SAL * 12 NVL(COMM, 01) FROM emp_table;

 

③ LIST(L) 명령어 실행

③-1. 다른 명령어도 입력해보고 다시 LIST(L) 명령어 실행-> 직전에 입력한 명령어만 나옴(누적되지 않았음)

 

④ / 명령어 실행

 

⑤ RUN(R) 명령어 실행

 

⑥ EDIT(ED) 명령어 실행

  a. 현재 없는 확장자가 sql인 파일 생성

  b. 현재 있는 확장자가 sql인 파일을 수정(변경)

   c. 명령문 입력 후 엔터, 메모장 생성 →  ; 입력 주의..

 

 

SQL Developer에서 변수를 지정하여 사원 정보 불러오기

DECLARE  -- ① 사원 번호를 저장할 변수 선언(세미콜론 주의)
    vempno emp_table.empno%type;
    vename emp_table.ename%type;
    vjob emp_table.job%type;
BEGIN  -- ② 변수를 사용하여 화면에 출력하기
    SELECT empno, ename, job 
    INTO vempno, vename, vjob 
    FROM emp_table 
    WHERE ename = 'SCOTT';

    DBMS_OUTPUT.put_line(vempno);
    DBMS_OUTPUT.put_line(vename);
    DBMS_OUTPUT.put_line(vjob);
END;