--- title: "오라클 DBMS 정리 09" date: "2018-11-08" tags: ["오라클DBMS"] status: archived verified: false --- ## 오라클 DBMS 정리 09 - PL/SQL 기본 --- ### PL/SQL * **Procedural Language/SQL** * DBMS에서 제공하는 언어적인 요소 * PL/SQL 제공 안하는 DB도 존재 * 구조 * / : 정해진 코드를 수행 * 에러 발생 시 쉽게 오류파악이 힘듦 * DECLARE부터 1행 카운트, 오류 위치 출력 ```sql DECLARE 변수선언 BEGIN 코드작성 END; / ``` * 실행순서 1. 코드작성 2. 파일명.sql로 저장 3. sqlplus에서 @파일명.sql로 실행 * 출력설정 * sqlplus에서 파일실행 전에 실행해줘야 출력설정이 됨 * 매번 옵션이 초기화되기 때문에 보통 DECLARE 위에 명시 * 출력설정을 안하면 PL/SQL은 실행되더라도 콘솔에 출력되지 않는다 ```sql -- 콘솔 출력설정 SET serveroutput ON ``` ![09-01](/md/legacy-assets/oracle/09-01.webp) ![09-02](/md/legacy-assets/oracle/09-02.webp) * 출력함수 **DBMS_OUTPUT** * DBMS_OUTPUT.PUT_LINE - 실행 후 줄바꿈 * DBMS_OUTPUT.PUT - 출력 후 줄바꾸지 않음 * PUT다음에 linefeed가 있는 PUT_LINE이 와야 출력이 됨 ```sql -- 출력함수 -- 출력 후 줄을 변경해줌(/n) DBMS_OUTPUT.PUT_LINE(출력할내용); -- 출력 후 줄을 안바꿈 DBMS_OUTPUT.PUT(출력할내용); -- 출력할 내용은 변수, '문자열', 연산식 등이 올 수 있음 -- '||'를 사용해서 변수나 문자열들을 붙여 출력 가능 ``` ```sql -- 출력설정 SET serveroutput ON DECLARE -- 변수선언 var VARCHAR2(15); BEGIN -- 코드작성 SELECT '안녕 PL/SQL' INTO var FROM DUAL; -- 출력함수 DBMS_OUTPUT.PUT_LINE(var); DBMS_OUTPUT.PUT_LINE('hello PL/SQL'); END; / ``` --- ### 변수(Variable) * 프로그램에서 필요한 값을 메모리에 일시적으로 저장하기 위해 사용 * 값에 대한 별명 * 가독성 향상 * **변수명은 유일해야 한다** * 값할당은 여러번 가능 (이전에 할당된 값은 사라짐) * 쓰지않는 변수는 만들지 않는다..(상식) ```sql DECLARE -- 변수 선언 변수명 테이터형(크기); 변수명 데이터형(크기) := 초기값; BEGIN -- 값 할당 변수명 := 값; -- 값 사용 변수명 := 또다른변수명; DBMS_OUTPUT.PUT_LINE(변수명); DBMS_OUTPUT.PUT_LINE(3 + 변수명); END; ``` * 작성법 1. 변수 선언 (DECLARE ~ BEGIN 사이) 2. 값 할당 (BEGIN ~ END 사이) 3. 값 사용 ```sql SET serveroutput ON DECLARE -- 1. 변수 선언 : 변수명 데이터형(크기); name1 VARCHAR2(60); name2 VARCHAR2(60); temp VARCHAR2(60); BEGIN -- 2. 값 할당 : 변수명 := 값; name1 := '오영영'; name2 := '손솔솔'; temp := name1; name1 := name2; name2 := temp; -- 3. 사용 : 출력, 연산참여, 재할당 DBMS_OUTPUT.PUT_LINE('name1 : '||name1); DBMS_OUTPUT.PUT_LINE('name2 : '||name2); END; ``` --- ### 변수 데이터형(Data Type) ![09-03](/md/legacy-assets/oracle/09-03.webp) * 참조변수(Reference Variable) * 만들어진 테이블의 컬럼데이터형을 참조하여 변수를 만들 수 있음 * **컬럼하나를 참조** * 참조변수명 테이블명.컬럼명%TYPE * **테이블의 모든 컬럼참조 (record type, 레코드형 변수)** * 레코드변수명 테이블명%ROWTYPE; * 사용은 레코드변수명.컬럼명 := 값; * 참조변수의 단점 * 데이터형을 직관적으로 알 수 없다 * 사용도중 데이터형이 변경될 수 있다 ```sql DECLARE -- 컬럼 하나를 참조 참조변수명 테이블명.컬럼명%TYPE := 값; -- 테이블의 모든 컬럼 참조(RECORD TYPE) 레코드변수명 테이블명%ROWTYPE; BEGIN 변수명 := 값; 레코드타입변수명.컬럼명 := 값; ... ``` ```sql DECLARE -- 테이블의 컬럼을 참조하여 변수를 만들 수 있다 e_name emp.ename%TYPE; BEGIN e_name := '이재현'; DBMS_OUTPUT.PUT_LINE(e_name||'씨'); END; / ``` ```sql DECLARE -- 테이블의 모든 컬럼을 참조하여 변수 선언(RECORD TYPE, 레코드변수) row_type cp_emp%ROWTYPE; BEGIN -- cp_emp 테이블은 empno, ename, hiredate, sal 컬럼을 가짐 -- RECORD TYPE 변수 사용 : 변수명.컬럼명 row_type.empno := 1234; row_type.ename := 'young'; row_type.hiredate := '1990-12-06'; row_type.sal := 5000; DBMS_OUTPUT.PUT_LINE(row_type.empno||'번, ' ||row_type.ename||'은 생년월일이 '||row_type.hiredate||'고 ' ||row_type.sal||'정도의 연봉을 받는다.'); END; / ``` --- ### 입력값 받기 * 프로그램을 동적으로 만들 때 사용 * **DECLARE 위에 정의** * 입력값이 문자열인 경우 홑따옴표로 묶어 입력 * 또는 할당할 때 문자열로 변환되도록 '&input변수' 로 처리 * **ACCEPT구문 마지막에 ';' 찍지 않음** * 블럭 내부안에서만 ';'를 찍는다! ```sql ACCEPT 입력될변수명 PROMPT '콘솔창에 출력될 메시지' DECLARE ... BEGIN ... := &입력될변수명 ... END; / ``` ![09-04](/md/legacy-assets/oracle/09-04.webp) * 기존의 코드가 입력된 값으로 변경되는 검증옵션 수행 * 검증옵션 끄기 ```sql SET verify OFF ``` ```sql SET serveroutput ON -- 검증옵션 끄기 SET verify OFF ACCEPT input_name PROMPT '이름을 입력하세요 : ' ACCEPT input_age PROMPT '나이를 입력하세요 : ' DECLARE name VARCHAR2(15); age NUMBER(3); BEGIN -- 입력값이 문자열인 경우 ''로 묶어서 문자열로 변환 name := '&input_name'; age := &input_age; DBMS_OUTPUT.PUT_LINE('이름 : '||name||'님 나이는 '||age||'살입니다.'); END; / ``` ![09-05](/md/legacy-assets/oracle/09-05.webp) * &는 주석안에 있어도 인식이 된다 * PL/SQL에서는 주석안이라도 &는 쓰면 안됨 ```sql BEGIN -- 주석안에 &가 있으면?? &comment END; / ``` ![09-06](/md/legacy-assets/oracle/09-06.webp) ```sql -- 태어난해 출력 : XX살 태어난해 XXXX년생 ACCEPT input_age PROMPT '나이를 입력하세요 : ' DECLARE age NUMBER(3); yob NUMBER(4); BEGIN age := &input_age; yob := TO_CHAR(SYSDATE, 'yyyy')-age+1; DBMS_OUTPUT.PUT_LINE(age||'살 태어난해 : '||yob||'년생'); -- 문자열은 연산이 안된다! -> DBMS가 자동 형변환 -- 아래 PUT_LINE함수는 에러발생 -- '||'는 산술연산자(+,-)보다 우선순위가 높아서 하이라이트한 부분먼저 붙고 산술연산이 시도됨 DBMS_OUTPUT.PUT_LINE(age||'살 태어난해 : '||TO_CHAR(SYSDATE, 'yyyy')-age+1||'년생'); -- || 사이에 있는 값을 연산 시킬려면 ||보다 먼저 수행되도록 ()를 붙인다 -- 산술연산자(*,/)는 안붙여도 된다(우선순위가 높기 때문에) DBMS_OUTPUT.PUT_LINE(age||'살 태어난해 : '|| (TO_CHAR(SYSDATE, 'yyyy')-age+1)||'년생'); END; / ``` --- ### 제어문 * 프로그램의 순차적인 흐름을 바꿔줄 수 있는 문장 * BEGIN ~ END 사이에서 사용 * **조건문, 반복문, 분기문** 3가지 제공 * 조건문 * IF * 반복문 * LOOP * FOR * WHILE * 분기문 * EXIT * RETURN --- ### 조건문 IF * 조건을 부여하여 조건에 맞는 경우 코드를 수행할 때 사용 * **단일 IF, IF ~ ELSE, 다중 IF(ELSIF)** 3가지 형태로 사용 * 단일 IF * 조건에 맞는 경우, 코드를 실행할 때 사용 ```sql ... BEGIN IF 조건식 THEN 조건에 맞을 때 수행할 문장들; END IF; END; / ``` * IF~ELSE * 둘 중 하나의 코드를 실행해야 하는 경우 ```sql BEGIN IF 조건식 THEN 조건에 맞을 때 수행할 문장; ELSE 조건에 맞지 않을 때 수행할 문장; END IF; END; ``` * 다중 IF (ELSIF) * 연관성 있는 여러 조건을 비교할 때 사용 ```sql BEGIN IF 조건1 THEN 조건1에 맞을 때 수행할 문장; ELSIF 조건2 THEN 조건2에 맞을 때 수행할 문장; ELSIF 조건3 THEN 조건3에 맞을 때 수행할 문장; ELSIF ... ... ELSE 모든 조건에 맞지 않았을 때 수행할 문장 END IF; END; ``` --- [숙제풀이1](https://github.com/younggeun0/SSangYoung/blob/master/dev/query/date181101_pl/homework1.sql) [숙제풀이2](https://github.com/younggeun0/SSangYoung/blob/master/dev/query/date181101_pl/homework2.sql) [숙제풀이3](https://github.com/younggeun0/SSangYoung/blob/master/dev/query/date181101_pl/homework3.sql) [숙제풀이4](https://github.com/younggeun0/SSangYoung/blob/master/dev/query/date181101_pl/homework4.sql)