231025 PL/SQL( Procedure Language Structured Query Language )

Data Base/PL SQL

231025 PL/SQL( Procedure Language Structured Query Language )

잇꼬 2023. 10. 25. 15:05

728x90

SMALL

■ PL/SQL ( Procedure Language Structured Query Language )

SELECT * FROM hr.employees WHERE employee_id = 100; 
SELECT * FROM hr.employees WHERE employee_id = 101;
SELECT * FROM hr.employees WHERE employee_id = 102;
...
SELECT * FROM hr.employees WHERE employee_id = 변수;

1. 프로시저 생성자를 제공한다.
1) 변수, 상수, 데이터 유형(레코드, 배열)
2) 반복문, 조건문 사용가능
3) 한 번 작성하면 여러번 실행 할 수 있는 재사용 가능한 프로그램 단위
ex) 함수

2. PL/SQL 이점
1) 모듈식 프로그램 개발
ex) 각 회사에 있는 프로그램의 언어에 맞게 사용가능하게 개발함
2) ORACLE 도구와 통합 (ERP, HR, SCM, CRM, ... )
3) 이식성
4) 예외처리

- PL/SQL 프로그램은 블록 구조 형식
# 익명블록 구조 (기본)
A. 객체 프로그램이 아니다.(DB 저장되어 있지 않다.)

DECLARE (선택)
	선언부분 ( 변수, 상수, 명시적인 커서, 사용자 정의 예의사항 )
BEGIN (필수)
	실행부분 ( SQL, 로직구현 )
EXCEPTION (선택)
	예외사항: 실행부분 발생한 오류에 대한 처리방법
END; (필수)
/

BEGIN
DBMS_OUTPUT.PUT_LINE('오늘 하루도 행복하자!'); --출력하는 프로그램
DBMS_OUTPUT.PUT_LINE('2주동안에는 PLSQL 개발자 마음으로 출근하세요!');
END;
/

해당 창은 Run SQL Command Line 에서도 확인이 가능하다.

처음에 hr session으로 로그인

SQL> conn hr/hr

Connected.

그 다음에는 위에서 썼던 BEGIN - END; 문장을 그대로 복사 붙여놓기를 한다.

SQL> SET SERVEROUTPUT ON

SQL> L

  1  BEGIN
  2     DBMS_OUTPUT.PUT_LINE('오늘 하루도 행복하자!'); -- 프로시저문
  3     DBMS_OUTPUT.PUT_LINE('2주동안에는 PLSQL 개발자 마음으로 출근하세요!');
  4* END;
SQL> /

한 번 더 써준 다음에 / 로 마무리 해주면,

출력하기를 원하는 문장만 나온다!

# 오늘, 내일 날짜를 출력해주세요.

BEGIN 
	DBMS_OUTPUT.PUT_LINE('TODAY''S : '||to_char(sysdate, 'yyyy-mm-dd'));
	DBMS_OUTPUT.PUT_LINE('TOMORROW''S : '||to_char(sysdate+1, 'yyyy-mm-dd'));
END;
/

# q 연산자

BEGIN 
	DBMS_OUTPUT.PUT_LINE(q'[TODAY'S : ]'||to_char(sysdate, 'yyyy-mm-dd'));
	DBMS_OUTPUT.PUT_LINE(q'[TOMORROW'S : ]'||to_char(sysdate+1, 'yyyy-mm-dd'));
END;
/

# PL/SQL에서 SQL 함수 사용시 주의사항
A. 프로시저문에서 사용할 수 있는 함수 : 단일행 함수
B. 프로시저문에서 사용할 수 없는 함수 : 그룹 함수 + DECODE 함수

# 오류발생: 프로시저문에서는 그룹함수 불가능

BEGIN 
v_sum := sum(v_sum);
END;
/

■ 변수
    1. 데이터를 임시로 저장하는 메모리 영역
    2. 규칙: 문자로 시작 가능.
    3. 문자, 숫자, 특수문자(_, $, #) 포함 가능.
    4. 변수 이름의 길이는 30자 이하만 가능.
    5. 예약어는 사용불가. (DBA SESSION 에서 확인가능)

<dba session>
SELECT * FROM v$reserved_words;

6. 변수 선언시에 NOT NULL, CONSTANT(상수) 로 지정된 변수에는 꼭 초기값을 할당해야 한다.
    7. 변수에 값을 할당하는 연산자는 :=, DEFAULT 로 사용한다.
    8. 변수는 변화는 값을 계속 입력할 수 있다.
    ★ 9. CONSTANT(상수) : 한번 받은 값만 계속 사용해야 한다.

# DECLARE 변수값 지정

DECLARE 
	v_name varchar2(20);	
BEGIN
	DBMS_OUTPUT.PUT_LINE('My name is : '|| v_name);
	v_name := '홍길동';
	DBMS_OUTPUT.PUT_LINE('My name is : '|| v_name);
	v_name := '박찬호';
	DBMS_OUTPUT.PUT_LINE('My name is : '|| v_name);
END;
/

# DECLARE 변수 지정 후에 바로 값을 지정 가능

DECLARE 
	v_event varchar2(20) := q'[Father's day!]';
BEGIN
	DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is : '|| v_event);
	v_event := q'[Mother's day!]';
	DBMS_OUTPUT.PUT_LINE('2rd Sunday in May is : '|| v_event);
END;
/

# SCALAR DATA TYPE : 단일값만 보유하는 변수

DECLARE 
	v_a number(7);
	v_b number(3) := 100;
	v_c varchar2(10) NOT NULL := 'orcale'; /* NOT NULL 제약조건은 꼭 초기값을 입력해야 한다. */
	v_d constant date default sysdate; /* 상수는 꼭 초기값을 입력해야 한다. */
	v_e constant number(3) := 10;
BEGIN 
	v_a := 200;
	DBMS_OUTPUT.PUT_LINE(v_a);
	DBMS_OUTPUT.PUT_LINE(v_b);
	DBMS_OUTPUT.PUT_LINE(v_c);
	DBMS_OUTPUT.PUT_LINE(v_d);
	DBMS_OUTPUT.PUT_LINE(v_e);
	-- v_d := sysdate + 1; --오류발생: 상수에는 새로운값을 입력할 수 없다.
	DBMS_OUTPUT.PUT_LINE(v_d);
END;
/

# local variable : 선언된 블록 프로그램에서만 수행하는 변수

DECLARE 
	/* salar data type */
	/* local variable : 선언된 블록 프로그램에서만 수행하는 변수 */
	v_sal number := 1000; 
	v_comm number := 100;
	v_total number;
BEGIN 
	v_total := v_sal + v_comm;
	DBMS_OUTPUT.PUT_LINE(v_total);
END;
/

# 오류발생 : ORA-00904: "V_TOTAL": invalid identifier
- 이유) v_total 변수 는 local variable 이기 때문에 블록 외 사용할때에는 오류발생한다.

SELECT * 
FROM hr.employees
WHERE salary > v_total;

■ 바인드 변수(bind variable)
     1. 호스트 환경에서 생성한다.
     2. 호스트 변수라고도 한다.
     3. variable(var) 키워드를 이용해서 생성한다.
     4. 바인드 변수는 SQL문과 PL/SQL 블록내에서도 사용가능하다.
     5. 바인드변수를 사용할 때는 콜론(:) 키워드를 바인드변수 이름 앞에 입력해야 한다.
     6. global variable 처럼 쓰고 싶을 때 사용한다.
     7. global variable : 프로그램 어디서든지 사용하는 변수
     8. 바인드변수는 익명 블록구조에서만 사용한다.

# 바인드변수 선언 ⓐ

variable b_total number

DECLARE 
	/* salar data type */
	/* local variable : 선언된 블록 프로그램에서만 수행하는 변수 */
	v_sal number := 1000; 
	v_comm number := 100;
BEGIN 
	:b_total := v_sal + v_comm;
	DBMS_OUTPUT.PUT_LINE(:b_total);
END;
/

SELECT * 
FROM hr.employees
WHERE salary > :b_total;

Run SQL Command Line 에서 확인하자! SQL developer 에서 확인이 가능했으나 19c에서는 확인이 되지 않는당..

# 바인드변수 선언 ⓑ

var b_total number
var b_sal number
var b_comm number

execute :b_sal := 1000
execute :b_comm := 100

DECLARE 
	v_sal number := :b_sal;
	v_comm number := :b_comm;
BEGIN 
	:b_total := v_sal + v_comm;
	DBMS_OUTPUT.PUT_LINE(:b_total);
END;
/

# 바인드변수 선언 ⓒ

var b_total number
var b_sal number
var b_comm number

BEGIN 
	:b_sal := 2000;
	:b_comm :=10;
END;
/

# 바인드변수 출력

SQL> print :b_sal

1# sub block 은 sub block에서만 이용

/* main block, outer block */
DECLARE
    v_outer_variable VARCHAR2(20) := 'global variable';
BEGIN 
    /* sub block, inner block*/
    DECLARE
        v_inner_variable VARCHAR2(20) := 'local variable';
    BEGIN
        dbms_output.put_line(v_inner_variable);
        dbms_output.put_line(v_outer_variable);
    END;
    dbms_output.put_line(v_inner_variable); --오류발생
    -- sub block 에서 선언한 local variable 은 자기 자신 블록에서만 사용해야만 한다.
END;
/

2# main과 sub 변수명이 동일할 경우, 자기 자신 block에 있는 변수명이 우선권이 있다.

/* main block, outer block */
DECLARE
    v_father_name varchar2(20) := 'DH';
    v_date_of_birth date := to_date('1962-05-25', 'yyyy-mm-dd');
BEGIN 
/* sub block, inner block*/
    DECLARE
        v_child_name varchar2(20) := 'SH';
        v_date_of_birth date := to_date('1993-03-09', 'yyyy-mm-dd');
    BEGIN
        dbms_output.put_line('Father''s Name : ' ||v_father_name);
        dbms_output.put_line('Date of Birth : '||v_date_of_birth);
        dbms_output.put_line('Child''s Name : '||v_child_name);
    END;
        DBMS_OUTPUT.PUT_LINE('Date of Birth : '||v_date_of_birth);
END;
/

3# main과 sub 변수명이 동일할 경우, <<outer>> + outer.변수명으로 main block 우선권을 준다.

/* main block, outer block */
<<outer>>
DECLARE
    v_father_name varchar2(20) := 'DH';
    v_date_of_birth date := to_date('1962-05-25', 'yyyy-mm-dd');
BEGIN 
/* sub block, inner block*/
    DECLARE
        v_child_name varchar2(20) := 'SH';
        v_date_of_birth date := to_date('1993-03-09', 'yyyy-mm-dd');
    BEGIN
        dbms_output.put_line('Father''s Name : ' ||v_father_name);
        dbms_output.put_line('Date of Birth : '||v_date_of_birth);
        dbms_output.put_line('Child''s Name : '||v_child_name);
    END;
        DBMS_OUTPUT.PUT_LINE('Date of Birth : '||v_date_of_birth);
END;
/

4# BEGIN <<outer>> - END outer;/ 으로 마무리

BEGIN <<outer>>
    DECLARE
        v_father_name varchar2(20) := 'DH';
        v_date_of_birth date := to_date('1962-05-25', 'yyyy-mm-dd');
    BEGIN 
        /* sub block, inner block*/
        DECLARE
            v_child_name varchar2(20) := 'SH';
            v_date_of_birth date := to_date('1993-03-09', 'yyyy-mm-dd');
        BEGIN
            dbms_output.put_line('Father''s Name : ' ||v_father_name);
            dbms_output.put_line('Date of Birth : '||outer.v_date_of_birth);
            dbms_output.put_line('Child''s Name : '||v_child_name);
            DBMS_OUTPUT.PUT_LINE('Date of Birth : '||v_date_of_birth);
        END;
            DBMS_OUTPUT.PUT_LINE('Date of Birth : '||v_date_of_birth);
    END;
END outer;
/

4# sub block 먼저 실행 되고 main block 실행된다.

(단, 동일한 변수명이 있고 sub block 에서 outer을 사용하게 된다면 main block 존재하는 동일한 변수값은 sub block 에 있는 변수값으로 수정되면서 기존 변수값은 없어진다.)

<<outer>>
DECLARE 
    v_sal number(8,2) := 60000;
    v_comm number(8,2) := v_sal*0.20;
    v_message varchar2(50) := 'eligible for commission';
BEGIN 
    DECLARE 
        v_sal number(8,2) := 50000;
        v_comm number(8,2) :=0;
        v_total number(8,2) := v_sal + v_comm;
    BEGIN 
        v_message := 'Clerk not '||v_message;
        outer.v_comm := v_sal*0.30; /* main block 으로 변경 */
        dbms_output.put_line('---- sub block ----');
        dbms_output.put_line(v_sal);
        dbms_output.put_line(v_comm);
        dbms_output.put_line(v_total);
        dbms_output.put_line(v_message);
    END;
        dbms_output.put_line('--- main block ---');
        dbms_output.put_line(v_sal);
        dbms_output.put_line(v_comm); /* sub block 에서 오버라이트되어서 기존값 사라짐. */
        dbms_output.put_line('Salesman '||v_message);
END;
/

728x90

LIST

저작자표시 비영리 동일조건

'Data Base > PL SQL' 카테고리의 다른 글

231030 PL/SQL CURSOR, 명시적 CURSOR (0)	2023.10.30
231030 PL/SQL 중첩테이블(NESTED TABLE) , VARRAY(VARIABL-SIZE ARRAY) 가변길이의 배열방식, 2차원 배열 (1)	2023.10.30
231027 PL/SQL 조합 데이터 _ 1차원 배열, 레코드 변수, 배열 변수 (1)	2023.10.30
231026 PL/SQL 반복문(CONTINUE문, WHILE LOOP문, FOR) (0)	2023.10.26
231025 PL/SQL 조건제어문 (1)	2023.10.25

현재글231025 PL/SQL( Procedure Language Structured Query Language )

250x250

일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

안녕? 나야 :)