PL/SQL

형식

• DECLARE 변수이름 데이터타입 : optional

• begin 실행 end : 필수

DECLARE 
    NAME varchar2(10) DEFAULT 'blue';
    begin 
        DBMS_OUTPUT.PUT_LINE(NAME);
    end;

프로시저는 스크립트 출력이 아니라, 보기>DBMS 출력>에서 해당 내용을 확인할 수 있다.

변수 선언

• 테이블에서 몇 개의 필드의 타입과 같은 변수를 만들 수 있다.

DECLARE
 e_name emp.ename%type;
 e_sal emp.sal%type;
BEGIN
    select sal, ename into e_sal , e_name from emp where empno=7839;
    DBMS_OUTPUT.put_line('7839님의 이름은 ' || e_name || ' 급여는 ' || e_sal);
END;

• 테이블의 레코드 전체를 통째로 복사하여 저장할 수 있다.

DECLARE data emp%ROWTYPE;
BEGIN
    SELECT * INTO data FROM emp where empno = 7839;
    DBMS_OUTPUT.PUT_LINE(data.ename ||',' || data.job ||','||data.mgr||','||data.deptno);
END;

• &연산자를 사용해서 입력을 받을 수도 있다.

DECLARE 
    vno number(4);
    vname varchar2(20);
BEGIN
    SELECT empno, ename into vno, vname
        from emp
        where empno = &empno;
    DBMS_OUTPUT.PUT_LINE(vno);
END;

제어문

• IF 문

DECLARE 
    su number := &su;
    grade varchar2(2);
begin
    IF (su >= 90) THEN grade := 'A'; 
    ELSIF ( su >= 80) THEN grade :='B';
    ELSIF ( su >= 70) THEN grade :='C';
    ELSIF ( su >= 60) THEN grade :='D';
    ELSE grade := 'F';
    END IF;
    DBMS_OUTPUT.PUT_LINE('당신의 점수는 '|| su ||', '|| '학점은 '|| grade);
END;

• for문

FOR 변수 이름 IN 시작...끝 LOOP
	실행문장;
END LOOP;

예시

declare
	i number := 0;
begin
	FOR i in 1..10 loop
        IF MOD(i, 2) =0 THEN dbms_output.put_line('i 값은' || i);
        END IF;
	end loop;
end;

• 형식

  while 조건식 LOOP 
  		실행문장
  		증감식;
  END LOOP;

  declare
  	su number := 0;
  begin
  	while su < 50 loop
  		su:= su + 1;
  		dbms_output.put_line('현재 su 값은' || su);
  	end loop;
  end;
  

응용

• 변수 선언, IF문 응용

DECLARE 
    t_ename emp.ename%TYPE;
    t_ecomm emp.comm%TYPE;
BEGIN
    SELECT ename, comm into t_ename, t_ecomm
        from emp
        where empno = 7499;
    IF t_ecomm > 0 then dbms_output.put_line(t_ename||'의 커미션은 ' || t_ecomm
    || '입니다.');
    ELSE dbms_output.put_line(t_ename||'은 커미션을 받지 않습니다.');
    END IF;
END;

DECLARE 
   t_deptno emp.deptno%TYPE;
   dname varchar2(20);
BEGIN
    SELECT deptno into t_deptno
        from emp
        where empno = 7499;
    IF t_deptno = 10 then dname := 'Accounting';
    ELSIF t_deptno = 20 then dname := 'sales';
    ELSIF t_deptno = 30 then dname := 'sales';
    ELSIF t_deptno = 40 then dname := 'operation';
    END IF;
    dbms_output.put_line('부서이름 '|| dname);
END;

DECLARE 
   t_sal emp.sal%TYPE;
   salgrade varchar2(20);
BEGIN
    SELECT sal into t_sal
        from emp
        where empno = 7839;
    IF t_sal >= 4000 then salgrade := '고액연봉';
    ELSIF t_sal >= 3000 then salgrade := '일반연봉';
    ELSIF t_sal >= 2000 then salgrade := '저소득연봉';
    ELSE salgrade := '소외계층';
    END IF;
    dbms_output.put_line(t_sal ||' , '|| salgrade);
END;

프로시저 저장과 생성

• 생성

create  procedure p_test
is 
    begin
        DBMS_OUTPUT.PUT('정보처리 시험 잘 보세요');
        DBMS_OUTPUT.put_line('happy');
    end;

• 실행

exec p_test;

• 덮어쓰기(or replace)와 변수 입력받기(name in varchar2)

create or replace  procedure p_test(name in varchar2)
is 
    begin
        DBMS_OUTPUT.PUT('정보처리 시험 잘 보세요');
        DBMS_OUTPUT.put_line('happy');
    end;

• 실행

create or replace  procedure p_test(name in varchar2)
is 
   begin
       DBMS_OUTPUT.PUT(name ||' '||'정보처리 시험 합격입니다.');
      end; 
exec p_test('김연아');

create or replace procedure p_userlist(
        id in userlist.id%type := 'kingsmile',
        name in userlist.name%TYPE default '도연이',
        age in userlist.age%type :=10 ,
        addr in userlist.addr%type := null 
		)
is
    begin
        insert into userlist values(id, name, age, addr);
        dbms_output.put_line('insert 정보는 '|| id || name || age || addr);
    end;
    
-- 이미 컴파일된 것을 실행
EXEC p_userlist('yuna', '김연아', 26, '군포');
EXEC p_userlist;

-- 필요에 의해 원하는 필드값만 지정 가능
exec p_userlist(name=> '박보검', age=> 50);

-- 문제 1 p_gift 저장 프로시저
select * from gift;

create sequence g_seq 
start with 10
nocycle ;

create or replace procedure p_gift(
        no in gift.gno%type,
        name in gift.gname%TYPE := null,
        pstart in gift.g_start%type :=null ,
        pend in gift.g_end%type := null )
is
    begin
        insert into gift values(no, name, pstart, pend);
        dbms_output.put_line('insert 정보는 '|| no || name || pstart || pend);
    end;
    
exec p_gift(g_seq.nextval, '갤럭시폴드');
select * from gift;