[Oracle] PL/SQL 문법정리

PL/SQL 문법정리

 

pl/sql - Procedural Language/sql

- 서버에서 절차적인 처리를 위해 표준 sql을 확장한 절차적 언어

- 블록 구조로 여러 sql문을 한번에 실행

- 모듈화 및 캡슐화, 비교, 반복, 예외처리 가능

- 서버에 저장되어 빠른 실행

- 단점은 문법에 대한 표준이 없고 각 DBMS에 종속적

 

declare(옵션)

begin(필수)

exception(옵션)

end;(필수)

 

pl/sql의 분기문

--------------------------------------------------------

if ~~

elsif ~~ -- else의 'e'가 빠짐

end if

 

DECLARE
  v_num number;
begin
  dbms_output.enable;
  v_num := 77;
  if v_num > 90 then
    dbms_output.put_line('수');
  elsif v_num > 80 then
    dbms_output.put_line('우');
  else
    dbms_output.put_line('빨리');
  end if;
end;

--------------------------------------------------------

case ~~

end case

 

DECLARE
  v_num number := 88;
begin
  case when v_num > 90 then

              dbms_output.put_line('Good');
           when v_num > 80 then

              dbms_output.put_line('normal');
           when v_num in (79,78,77) then

              dbms_output.put_line('star');
            else dbms_output.put_line('열심히');
  end case;
end;

--------------------------------------------------------

 

pl/sql의 반복문

--------------------------------------------------------

while 조건 loop~~

end loop

 

accept p_dan prompt '원하는 단은 :'
declare
  v_i number := 1;
  v_dan number := &p_dan;
begin
  while v_i < 10 loop
    dbms_output.put_line(v_dan || '*' || v_i || ' = ' || (v_dan * v_i));
    v_i := v_i + 1;
  end loop;
end;

--------------------------------------------------------

loop ~~

end loop

 

declare
  v_sum number :=0;
  v_var number :=1;
begin
  loop
    v_sum := v_sum + v_var;
    v_var := v_var + 1;
    if v_var > 10 then
      exit;

   -- (exit는 조건과 같이 쓰는 것이 일반적

         위의 문장을 exit when if v_var > 10;

         으로 쓸 수 있다.)
    end if;
  end loop;
  dbms_output.put_line('1부터 10까지의 합 = '||v_sum);
end;

--------------------------------------------------------

for ~~

end loop

 

begin
  for i in reverse 1..10 loop

   -- i는 자동으로 생성되는 변수 이므로 선언할 필요 없음
    -- reverse 옵션을 사용할 경우 1씩 감소, reverse를 사용해도

    -- 최소값..최대값의 순서로 적용

 

    dbms_output.put_line(rpad(' ',10-i) || rpad('*',i*2-1,'*'));
  end loop;
end;

 

declare
  type mem_table is table of varchar2(50) index by pls_integer;
  mem_tab mem_table;
  i pls_integer := 0;
begin
  for m_rec in (select mem_name from member) loop

  -- in 안에 꼭 최소값..최대값 형태로 사용해야 되는 것은 아님
    i := i+1;
    mem_tab(i) := m_rec.mem_name;
  end loop;
  dbms_output.put_line('i= '|| i);
  for j in 1..i loop
    dbms_output.put_line(j || '번째 :' || mem_tab(j));
  end loop;
end;

--------------------------------------------------------

 

CREATE OR REPLACE FUNCTION HMS_BO.fn_month_name(v_month IN varchar2)
RETURN varchar2 IS v_monthname varchar2(10);
lv_month varchar2(10); /* month */
BEGIN
IF v_month = '01' THEN lv_month := 'January' ;
ELSIF v_month = '02' THEN lv_month := 'Febury' ;
ELSIF v_month = '03' THEN lv_month := 'March' ;
ELSIF v_month = '04' THEN lv_month := 'April' ;
ELSIF v_month = '05' THEN lv_month := 'May' ;
ELSIF v_month = '06' THEN lv_month := 'June' ;
ELSIF v_month = '07' THEN lv_month := 'July' ;
ELSIF v_month = '08' THEN lv_month := 'August' ;
ELSIF v_month = '09' THEN lv_month := 'September' ;
ELSIF v_month = '10' THEN lv_month := 'October' ;
ELSIF v_month = '11' THEN lv_month := 'November' ;
ELSIF v_month = '12' THEN lv_month := 'December' ;
END IF;
RETURN lv_month;
END FN_MONTH_NAME;

 

// FK disable시키는 PL-SQL
BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'ENABLED'
AND c.constraint_type = 'R'
ORDER BY c.constraint_type DESC)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
END LOOP;
END;