PL/SQL (15) PL/SQL 정리 및 예제

-- 1. SET VERIFY OFF : PL/SQL 실행시 메시지를 출력하지 않는 설정 

-- 2. 변수이름 지정방법

-- 1) 문자로 시작

-- 2) 문자 + 숫자 가능 

-- 3) 특수문자 $,_,# 포함

-- 4) 변수의 길이는 30자 이하 

-- 5) 오라클 예약어는 변수명으로 사용할 수 없음

-- 3. 타입설정 방법

-- 1) 현재까지 PL/SQL 작성의 문제점 : 컬럼길이를 몰라서 임의로 정해버리는 문제점

-- 2) 해결 : %TYPE 속성

-- 위와 같이 데이터 타입을 선언하지 않으면, 추후에 데이터 타입을 변경할때 마다 

수반하는 작업이 많아진다. 

-- 4. NUMBER 

-- 변수 선언 시, NUMBER(7,2)의 의미는 전체 개수 7개, 소수점 개수 2개

-- ex) 12345.12

-- 5. 불린변수 선언방법

-- flaf BOOLEAN := FALSE;

-- 6. 데이터 타입의 종류 두가지 

-- 1) composite datatype

-- 2) scalar datatype

-- 7-1. 변수의 데이터 유형 <scalar datatype> : 단일 값을 보유하며 내부 구성요소가 없음.

-- 1) 문자: varchar2

-- 2) 숫자: number

-- 3) 날짜: date

-- 4) 부울: boolean --> true, false, null

-- 7-2. 변수의 데이터 유형 <cmposite datatype> : 다중 값을 보유하며 내부 구성요소가 있음.

-- 1) 레코드(Record): PL/SQL 레코드

-- 2) 컬렉션(Collection): 테이블 같은 것

-- 8. LOB (대형객체) 데이터 유형 변수

-- LOB 범주의 데이터 유형 (BLOB, CLOB 등)을 사용하여 데이터베이스 블록 크기에따라 

-- 최대 127TB까지 텍스트, 그래픽, 이미지, 비디오 클립등의 구조화되지 않은 

데이터 블록을 저장할 수 있다.

-- 1) CLOB : 대형 문자 데이터 블록을 저장하는데 사용

-- 2) BLOC : 대형 바이너리 객체를 데이터베이스에 저장하는데 사용

-- 3) BFILE : 대형 바이너리 파일을 데이터베이스 외부에 저장하는데 사용

-- 4) NCLOB : 대형 블록의 단일 바이트 또는 고정 너비 멀티바이트 

NCHAR 유니코드 데이터를 데이터베이스에 저장하는데 사용

-- 9. declare 외부에 선언한 변수 : 외부변수 / declare 내부에 선언한 변수 :  내부변수 

-- 10. 오라클 함수 2가지

-- 1. 단일행 함수: 문자, 숫자, 날짜, 변환, 일반함수

-- 2. 복수행 함수 (그룹함수): max, min, avg, sum, count

-- ★ 프로시저문에서 사용할 수 있는 함수는 단일행 함수이며,

-- ★ 프로시저문에서 사용할 수 없는 함수는 decode 및 그룹함수이다. 

-- 11. 시퀀스 생성해보기.

-- ex) 시작문자가 1이고 최대숫자가 1000인 시퀀스를 생성하시오.

 CREATE SEQUENCE SEQ_TEST

 START WITH 1 

 INCREMENT BY 1

 MAXVALUE 1000;

 

-- 결과확인 :  

  SELECT SEQ_TEST.NEXTVAL FROM DUAL;

  

-- 12. TB_LS_EMP867 테이블과 동일한 구조의 테이블 생성하고 

데이터를 이관하시오. (테이블명: TB_EMP_MIG_TEST1)

CREATE TABLE TB_EMP_MIG_TEST1 

AS 

SELECT * FROM TB_LS_EMP867;

DELETE FROM TB_EMP_MIG_TEST1;

SELECT * FROM TB_EMP_MIG_TEST1;

-- 13. 사원번호를 물어보게 하고 사원번호를 입력하면, 해당 사원의 모든 컬럼의 

정보가 TB_EMP_MIG_TEST1에 이관되도록 프로시저를 작성하시오.

SET SERVEROUTPUT ON;

SET TIMING ON; -- 해당 쿼리 및 프로시저 속도 체크

SET VERIFY OFF; 

CREATE OR REPLACE PROCEDURE PR_TEST_INST_1

(P_EMP_NO NUMBER)

IS

      -- 변수를 선언한다.

    V_EMP_NO NUMBER(10) := &P_EMP_NO;

    start_time NUMBER := 0;

    end_time NUMBER := 0;

  BEGIN

    INSERT INTO TB_EMP_MIG_TEST1 

      SELECT * FROM TB_LS_EMP867

      WHERE EMP_NO = V_EMP_NO;  

    

    -- ☆ SQL%rowcount 사용

    DBMS_OUTPUT.PUT_LINE( SQL%rowcount || '건이 갱신되었습니다.');  

  END;

  /

EXEC PR_TEST_INST_1(82);