1027 (29일차) - sql 9일차

pl/sql 조건문

1.if문 류

-- pl/sql 조건문
--1.if 조건식 then 실행문 end if; (중괄호 없음, 괄호 필수 아님)
--2.if 조건식 then 실행문 else 실행문 end if;
--3.if 조건식1 then 실행문1 elsif 조건식2 then 실행문2 .... end if (조건문 제한 없음)

declare
    n number := &숫자;
begin
    if mod(n,3) = 0 then
        dbms_output.put_line('3의 배수입니다..');
      elsif mod(n,3) = 1 then
        dbms_output.put_line('3으로 나눈 나머지가 1입니다');
    else
        dbms_output.put_line('3으로 나눈 나머지가 2입니다.');
    end if;
end;
/

 

2.case문

/*
문법1.
    case 표현식
        when 값1 then
            실행문1;
        when 값2 then
            실행문2;
        else
            기본실행문;
    end case;

문법2.
    case
        when 조건식1 then
            실행문1;
        when 조건식2 then
            실행문2;
        ..
        else
            기본실행문;
    end case;
*/

declare
    name varchar2(100) :='&이름2';
begin 
    case
        when name='홍길동' then
            dbms_output.put_line('홍길동님 반갑습니다');
        when name='농담곰' then
            dbms_output.put_line('농담곰님 반갑습니다');  
        else
            dbms_output.put_line('누구십니까??');  
    end case;
end;
/

pl/sql 반복문

1.loop

-기본 루프(defalult:무한루프)

-반드시 탈출 조건을 명시할 것.

declare
    n number :=1;
begin 
    loop
        dbms_output.put_line(n);
        n := n+1;
--        if n>10 then
--            exit; --break대신 , 여기에도 continue는 있음
--        end if;

        exit when n>10;
        
    end loop;
end;
/

 

2.while loop

declare--증감변수 꼭 선언..
    i number := 1; 
begin 
    while i <= 10 loop
        dbms_output.put_line(i);
        i := i+1;
    end loop;
    dbms_output.new_line;--인자없는 프로시저
end;
/

 

3.for in loop (별도의 증감 변수가 필요 없음,코드가 아주 간결해짐.)

위의 두가지 루프문과 다르게 declare에 변수 선언도 할 필요가 없다.

--for loop : 별도의 증감변수가 필요없다.
begin 
    --증감변수의 선언
    --증감변수 증감처리 +1로 고정, 마이너스 처리를 위해서는 reverse 키워드 사용. 
    --자동 exit
    for n in 1 .. 5 
    loop 
        dbms_output.put_line(n);
    
    end loop;
end;
/

---

DATABASE OBJECT 2

1.STORED FUNCTION / 함수 in 프로시저

-리턴값이 반드시 존재하는 프로시저
-저장된 함수/프로시저 등은 미리 컴파일되어서 즉시 실행가능한 상태로 대기중...
-요청처리속도가 빠르다는 장점.
-일반 sql문이나 다른 프로시저 객체에서 호출가능

create or replace function fn_함수명(매개변수1 자료형, 매개변수2 자료형,...)
    return 자료형 (크기 지정x)
    is
        [변수선언]
    
    begin
        실행부
        return 리턴값--반드시 리턴값이 존재해야한다.
    [exception
        예외처리부
    ]
    end;
    /

 

STORED PROCEDURE / 프로시저

-일련의 작업절차를 객체에 선언한 후 호출 해서 사용.
-반환값이 없다. 대신 IN/OUT모드 매개변수를 통해 호출부로 값을 전달. (call by reference와 비슷)
-미리 컴파일 된 채로 DB에 저장되어있으므로 효율적인 실행이 가능하다..
-SELECT문에서는 사용 불가. 익명블럭or다른 프로시져에서 호출가능.

-in모드 매개변수:호출부에서 프로시져로 전달 된 값(매개인자)
-out모드 매개변수:호출부에서 프로시져로 전달한 공간, 프로시저에서 이 값을 대입하면 호출부에서 그대로 확인이 가능

create or replace procedure proc_프로시져명 (매개변수 모드 자료형,.......)
    is 
        지역변수 선언
    begin
        실행부
    [exception] 
    end;
    /

1)매개변수가 없는 프로시저 예제

create or replace procedure proc_del_all_emp
is
begin
    delete from employee_ex;
    commit;
end;
/


begin
    proc_del_all_emp; --매개인자 없는 프로시져 호출
end;
/

 

2)매개변수가 존재하는 프로시저 예제

--매개변수가 있는 프로시져 : 사번을 받아서 이름을 조회
create or replace procedure proc_find_emp(
    p_emp_id in employee.emp_id%type, --매개변수역할
    p_emp_name out employee.emp_name%type, --매개인자 역할
    p_email out employee.email%type --매개인자 역할
)
is
begin
    select 
        emp_name,email
    into 
        p_emp_name,p_email
    from 
        employee
    where 
        emp_id = p_emp_id;
end;
/

--익명블럭(호출부)
declare
    v_emp_id employee.emp_id%type :='&사번';
    v_emp_name employee.emp_name%type;
    v_email employee.email%type;
begin
    proc_find_emp(v_emp_id, v_emp_name,v_email); --in[값전달] out[값 반환용 공간]
    dbms_output.put_line('사번 : ' || v_emp_id);
    dbms_output.put_line('사원명 : ' || v_emp_name);
    dbms_output.put_line('이메일 : ' || v_email);
end;
/

 

★CURSOR / 커서

-sql문 처리 결과 ResultSet(dql,dml 모두) 을 가리키는 포인터 타입
-암시적 커서 : 오라클 쿼리 실행 후 자동생성되는 커서 
-명시적 커서 : 여러행 처리를 위한 자료형

-커서의 상태변화
-open
-fetch 한 행씩 접근/다음행으로 포인터가 이동
-close (for loop로 제어시 open,fetch,close필요 x)

-커서 속성
-%rowcount 처리된 행의 수 ex) 1행이 추가되었습니다.
-%notfound open 후 fetch 될 행이 없는경우 true 반환
-%found open 후 fetch될 행이 있음 true 반환
-%isopen 현재 커서가 open되었나 여부 t/f