oracle PS/SQL_제어문

 

1. 조건문

  1) if문

     

        if(조건) then

            실행명령;

        end if;

declare
    emp_id employees.employee_id%type;
    emp_name employees.last_name%type;
    emp_dept employees.department_id%type;
    dept_name varchar2(20);
    
begin
    select employee_id, last_name,department_id
    into emp_id, emp_name, emp_dept
    from employees
    where employee_id = 103;
    
    if( emp_dept = 50 ) then dept_name :='shipping';
    end if;
    if( emp_dept = 60 ) then dept_name :='IT';
    end if;
    if( emp_dept = 70 ) then dept_name :='P/L';
    end if;
    if( emp_dept = 80 ) then dept_name :='sales';
    end if;
    
    dbms_output.put_line(emp_id||' '|| emp_name ||' '|| emp_dept||' '|| dept_name);
    
end;
 
/

 

        if (조건) then

           실행명령;

        elsif(조건) then  (elseif 아니라 elsif)

           실행명령

        end if

 
declare
    emp_id employees.employee_id%type;
    emp_name employees.last_name%type;
    emp_dept employees.department_id%type;
    dept_name varchar2(20);
    
begin
    select employee_id, last_name,department_id
    into emp_id, emp_name, emp_dept
    from employees
    where employee_id = 103;
 
    if( emp_dept = 50 ) then dept_name :='shipping';
    elsif( emp_dept = 60 ) then dept_name :='IT';    --else if 가 아니다!!elsif다 와웅
    elsif( emp_dept = 70 ) then dept_name :='P/L';
    elsif( emp_dept = 80 ) then dept_name :='sales';
    end if;
    
    dbms_output.put_line(emp_id||' '|| emp_name ||' '|| emp_dept||' '|| dept_name);
end;
 
/

 

        if (조건) then 

           실행명령;

        else

           실행명령

        end if;

declare
    emp_id employees.employee_id%type;
    emp_name employees.last_name%type;
    emp_comm employees.COMMISSION_PCT%type := null; -- 선언하면서 초기화
begin
    select employee_id, last_name, commission_pct
    into emp_id, emp_name, emp_comm
    from employees
    where employee_id = 155;
    
    -- 이때는 그냥 else
    if (emp_comm > 0) then 
    dbms_output.put_line(emp_name || '의 보너스는 '|| emp_comm);
    else
    dbms_output.put_line(emp_name || '의 보너스는 없습니다');
    end if;
end;
 
/

 

   2) case문

 

        case 제어대상

           when 조건 then 실행명령

           when 조건 then 실행명령

        end;

declare
    emp_id employees.employee_id%type;
    emp_name employees.last_name%type;
    emp_dept employees.department_id%type;
    dept_name varchar2(20);
    
begin
    select employee_id, last_name, department_id
    into emp_id, emp_name, emp_dept
    from employees
    where employee_id = &empno;
    
    dept_name := case emp_dept
                            when 50 then 'shipping'
                            when 60 then 'IT'
                            when 70 then 'P/L'
                            when 80 then 'sales'
                            end;
dbms_output.put_line(emp_id||' '|| emp_name ||' '|| emp_dept||' '|| dept_name);
end;
 
/

 

2. 반복문

 

   1) basic loop문

 

        loop

           pl/sql문장 ; 

           exit 조건 ; 

        end loop;

** 조건에 해당되면 빠져나가겠다 / 조건이 만족안되면 pl/sql 다시 실행(만족할 때 까지)

** 조건을 나중에 검사. 일단 loop실행

declare
    num number :=0;
 
 begin 
    loop
        dbms_output.put_line(num);
        num := num+1;
        exit when num > 10;
    end loop;
 
end;
 
/

declare
    num number := 10;
 
begin
    loop
        dbms_output.put_line(num);
        num := num-1;
        exit when num = -1;
    end loop;
end;
/

 

   2) while문

       

        while 조건 loop

           실행문장 ; 

        end loop ;        

** 조건을 먼저 검사

declare
    num number:=0;
    
begin
    while num <11 loop
        dbms_output.put_line(num);
        num := num+1;
    end loop;
end;
 
/

declare
    num number := 10;
 
begin
    while num >=0 loop
        dbms_output.put_line(num);
    num := num -1;
    end loop;
end;
/
 

 

   3) for문

 

       FOR i IN start..end LOOP

          실행문장 ;

       END LOOP ;

begin
    FOR n IN 0..10 loop
        dbms_output.put_line( n );    
    end loop;
end;
/
 
-- 역순
begin
    for n in reverse 0..10 loop
        dbms_output.put_line( n );    
    end loop;
end;
/

 

-- 사원테이블에서 사원 id를 입력받아서 사원 이름의 문자 수만큼 ##을 찍는 pl/sql

declare
    cnt number; 
    no number := &employee_id;
    sign varchar2(20);
    realname varchar2(20);
begin
    select length(first_name ), first_name into cnt, realname 
    from employees
    where employee_id = no;
    
    sign := '';
    
    for n in 1..cnt loop
        sign := sign || '#';
    end loop;    
    
    dbms_output.put_line (realname);
    dbms_output.put_line (sign);
end;
 
/