오라클 PL/SQL

오라클 PL/SQL은 처음 배울 땐 어렵게 느껴지지만 좀 시간이 지나서 한번 정리해 보면
베이직 문법과 비슷한 게 , 조금 귀찮을 뿐 그리 어렵지는 않습니다.
이 글을 보았다면 뜬금없이 정리 한번 해봅니다. 기본은 항상 별거없이 별거 있습니다.

1. 시작

   Table 맹글기 귀찮으니 HR계정으로 로그인

-- 일단 출력이 기본값으로 off 되어 있는데, 열어줌
set serveroutput on;
 
-- 기본적으로 출력문 앞의 스페이스이들이 잘려 나가는데 막고 싶다면 아래 중에 선택!
-- SET SERVEROUTPUT ON FORMAT TRUNCATED
-- SET SERVEROUTPUT ON FORMAT WRAPPED
-- set trimspool on

 

2. 구조  (사실 이런 걸 대략 기억하는 게 빠른 습득에 중요)

declare   영역-> 선언부로  생략가능

executable -영역 > 실행부로 필수

exception  영역 -> 예외부로 생략가능

 

초 심플예제로 시작

begin
	dbms_output.put_line('안넝 좌00');
end;

 

3. 변수 선언

declare 영역에 한당, 대소문자 구분 안구분

변수명 타입   값할당(선택)  식으로 선언( 아래 4개  타입 정도는 머리에 담는당) 

             number, varchar2,  테이블명.컬럼명%type, 테이블명%rowtype

값 할당은 := 을 이용

 

변수 타입에 number와 varchar2를 괘니 사용해 봄

declare
    v_testno number(4) := 272;
    v_teststr VARCHAR2(30); -- 자릿수 부족할 때 에러 주의
    
begin
    dbms_output.put_line('안넝 좌00');
    dbms_output.put_line(v_testno);
    dbms_output.put_line(v_teststr);
    
    v_teststr := '좌00 만만세';
    dbms_output.put_line(v_teststr);
    
end;

 

변수타입에 컬럼타입 %type 와 한줄 row를 나타내는 %rowtype (record라 부름)도 괘니 사용해 봄

declare
    v_testid number(4) := 111;
    v_firstname employees.first_name%type;
    r_onerow  employees%rowtype;
    
begin
    select first_name into v_firstname  from employees where employee_id = v_testid;
    select * into r_onerow from employees where employee_id = v_testid;
    dbms_output.put_line(r_onerow.employee_id || ' ' || r_onerow.phone_number || '  ' || r_onerow.salary);
 
    dbms_output.put_line('안넝 좌00');
    dbms_output.put_line(v_testid);
    dbms_output.put_line(v_firstname);
       
end;

 

4. 조건문(if 와 case)

초 심플 (else 안 사용);

declare 
    v_mystar varchar2(50) := '좌oo';
begin
    if v_mystar = '좌oo' then
        dbms_output.put_line(' 좌oo 완전 짱!');
    end if;
    dbms_output.put_line(' 오늘도 행복하삼');
end;

 

elseif else 사용

declare 
    v_mystar varchar2(50) := 'e7e';
begin
    if v_mystar = '좌oo' then
        dbms_output.put_line('좌oo 완전 짱!');
    elsif v_mystar = 'e7e' then
        dbms_output.put_line('자화자찬 안돼용!'); 
    else
        dbms_output.put_line('누구세욤?');   
    end if;
    dbms_output.put_line('오늘도 행복하삼');
end;

 

case 사용 예

declare
    v_testCase varchar2(10) := '좌00';
    v_outStr varchar2(100);
begin
    dbms_output.put_line('그냥 출력해보는 거얌');
    case v_testCase
        when  'e7e' then v_outStr := v_testCase || ' 안농';
        when  '좌00' then v_outStr := v_testCase || ' 보고팡';
        else v_outStr := ' 계속 보고팡';
    end case;
    dbms_output.put_line('aaa'|| v_outStr);
    exception
        when others then
        begin
            dbms_output.put_line('Exception Occur');
        end;
end;

 

5. 반복문

loop 사용

declare
    v_cnt number;
begin
    dbms_output.put_line('merong');
    v_cnt := 1;
    loop
        dbms_output.put_line('v_cnt=' || v_cnt);
        v_cnt := v_cnt +1;
        exit when (v_cnt > 10);
    end loop;
end;

 

for  loop 사용

declare
    v_exitCond varchar2(10) := '좌00';
begin
    for findex in 1..10 loop
        if( findex = 7 ) then
            dbms_output.put_line(v_exitCond || ' ' || findex);
            exit;  -- 종료
        end if;
        dbms_output.put_line('그냥 돌깅: ' || findex);        
    end loop;
end;

 

while 사용

declare
    v_cknum number := 1;
begin
    while v_cknum <= 10 loop 
        dbms_output.put_line('check ' || v_cknum ); 
        v_cknum := v_cknum +1;
    end loop;
end;

 

6. 예외(Exception)

declare
begin
    dbms_output.put_line('그냥 출력');
    --raise no_data_found;
    raise value_error;
    exception
        when no_data_found then
        begin
            dbms_output.put_line('data not found' || ' 예외 발생');
        end;
        
        when others then
        begin
            dbms_output.put_line('기타 ' || ' 예외 발생');        
        end;
end;

https://docs.oracle.com/cd/B14117_01/appdev.101/b10807/07_errs.htm

 

7. 커서(cursor)

기본

declare
    cursor empcursor is select * from employees;
    v_emprow employees%rowtype;
begin
    open empcursor;
    loop
        fetch empcursor into v_emprow;
        exit when empcursor%notfound;
        dbms_output.put_line('email: ' || v_emprow.email );
    end loop;
    close empcursor;
end;

for loop를 이용 open/close  커서 생략

declare
    cursor empcursor is select * from employees;
    v_emprow employees%rowtype;
begin
     for v_emprow in empcursor loop
         dbms_output.put_line('id: ' || v_emprow.employee_id || '      email:' || v_emprow.email );
    end loop;
end;

 

8. 저장 프로시저(stored procedure)

시작  일단 구성을 파악할 것

create or replace procedure firstproc
    -- 매개변수 선언
is
    -- 지역변수 선언
begin
    dbms_output.put_line('left line state');   
end;
 
-- 실행
execute firstproc;

 

매개변수를 1개 넘겨받는 예

-- 테이블 복사
create table emp
as select * from employees;
 
-- 
create or replace procedure del_emp(
    v_empid emp.employee_id%type
)
is
begin
    dbms_output.put_line('넘겨받은 값: ' || v_empid);
    delete from emp where employee_id = v_empid;
    commit;
end;
 
execute del_emp('199');

매개변수, bind변수 (in,out, in out) 사용 예

create or replace procedure sel_emp(
    v_empid in emp.employee_id%type,
    v_empemail out emp.email%type,
    v_empsalary out  emp.salary%type,
    v_empphone out emp.phone_number%type    
)
is
begin
   select email, salary, phone_number into v_empemail, v_empsalary, v_empphone 
   from emp
   where employee_id = v_empid;
   --dbms_output.put_line('out: ' || v_empid);
end;
 
var v_email varchar2(25);
-- out number에는 (자릿수)를 지정하지 않아용
var v_salary number; 
var v_phone varchar2(20);
 
execute  sel_emp(104, :v_email, :v_salary, :v_phone); 
 
print v_email 
print v_salary
print v_phone

 

9. 저장 함수(stored function)

기본 구조

create or replace function firstfunc(
    v_num in number
)
-- return 데이터타입
return number  --  주의 ;붙이면 안됨
is
--지역변수
begin
    return v_num*4; 
end;
 
-- execute :변수명 := 함수명(매개변수 리스트)
var retnum number;
execute  :retnum := firstfunc(100);
print retnum;

매개변수 사용

create or replace function annualincome(
    v_empid emp.employee_id%type
)
return number 
is
    v_annualincome number;
begin
    select salary* 12 into v_annualincome 
    from emp
    where employee_id = v_empid;
    return v_annualincome;
end;
 
var yearincome number;
execute :yearincome := annualincome(100);
print yearincome