#8_140624_PL_SQL_ 기본

#8_140624_PL_SQL_ 기본

l PL/SQL(Procedure Language, 절차적 언어)

-       SQL + C언어

l 오류가 나서 부르기전에 2번 확인

-       오류를 스스로 찾아보고

-       짝궁의 도움 or notepad++ 의 비교기능(내가 작성 vs 정답소스)

l 금융권에 쓰는 금융 패키지 – 오라클 PL/SQL(프랑스)

l PL/SQL의 종류

-       익명(1번)블록과 그 외 블록(2,3,4,5)의 차이는 DB에 이름을 가지고 저장 여부이다

1. 익명 블록(anonymous PL/SQL block)

2. procedure

3. function

4. trigger

5. package

문제1. 사원번호를 입력하면 해당 사원의 월급이 출력되는 PL/SQL 프로그램을 작성하시오

set serveroutput on

accept p_empno prompt '사원번호를 입력하시오!'

declare

v_empno number(10) := &p_empno;

v_sal number(10);

begin

select sal into v_sal

from emp

where empno=v_empno;

dbms_output.put_line ('월급은 ' || v_sal);

end;

/

set serveroutput on

// dmbs_output.put_line 을 작동시키기 위한 명령어

accept p_empno prompt ‘사원번호를 입력하시오!’

// promt : 야기시키다. ‘사원번호~’를 출력하도록 야기하시오.

// p_empno 는 변수. 사원번호를 입력하면 p_empno에 입력

declare    // 선언절 : 변수, 상수, 커서, 예외 선언

v_empno number(10) := &p_empno   // := 할당연산자. v_empno에 할당해줌

v_sal number(10);                    // v_sal라는 변수에 숫자를 담아라

begin     // 실행절

select sal into v_sal

from emp

where empno = v_empno;

dbms_output.put_line (‘ 월급은 ‘ || v_sal);

// 출력을 위한 오라클 내장 패키지

end;              // PL SQL 문을 끝내겠다

/

문제2. 사원 이름을 물어보게 하고 사원 이름을 입력하면 해당 사원의 월급이 출력되게 하시오

ed p2.sql

@p2.sql

set serveroutput on

accept p_ename prompt '사원이름를 입력하시오!'

declare

v_ename varchar2(10) := '&p_ename';

v_sal number(10);

begin

select sal into v_sal

from emp

where ename=v_ename;

dbms_output.put_line ('월급은 ' || v_sal);

end;

/

문제3. 직업을 입력하면 해당 직업의 토탈월급이 출력되게 하시오

set serveroutput on

accept p_job prompt '직업이름를 입력하시오! '

declare

v_job varchar2(20) := '&p_job';

v_sumsal number(10);

begin

select SUM(sal) into v_sumsal

from emp

where job=v_job;

dbms_output.put_line ('토탈월급은 ' || v_sumsal);

end;

/

문제4. 위의 소스를 수정하는데 직업을 소문자로 입력하던 대문자로 입력하던 상관없이 결과가 출력되게 하시오

set serveroutput on

accept p_job prompt '직업이름를 입력하시오! '

set verify off

declare

v_job varchar2(20) := '&p_job';

v_sumsal number(10);

begin

select SUM(sal) into v_sumsal

from emp

where job=UPPER(v_job);

dbms_output.put_line ('월급은 ' || v_sumsal);

end;

/

set serverouput off

l set verify off : 지저분한 내용 없앰

문제5. 부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호에서 근무하는 사원들의 인원수를 출려고되게 하시오

set serveroutput on

set verify off

accept p_deptno prompt '부서번호를 입력하시오! '

declare

v_deptno number(10) := &p_deptno;

v_count number(10);

begin

select COUNT(*) into v_count

from emp

where deptno=v_deptno;

dbms_output.put_line ('인원수는 ' || v_count);

end;

/

문제6. 사원번호를 물어보게 하고 사원번호를 입력하면 해당 사원의 이름과 월급과 직업이 아래와 같이 출력되게 하시오

이름 : SCOTT

월급 : 3000

직업 : ANALYST

set serveroutput on

set verify off

accept p_empno prompt '사원번호를 입력하시오! '

declare

v_empno emp.empno%type := &p_empno;

v_ename emp.ename%type;

v_sal emp.sal%type;

v_job emp.job%type;

begin

select ename, sal, job into v_ename, v_sal, v_job

from emp

where empno=v_empno;

dbms_output.put_line ('이름 : ' || v_ename);

dbms_output.put_line ('월급 :  ' || v_sal);

dbms_output.put_line ('직업 :  ' || v_job);

end;

/

문제7. 사원 테이블의 job 데이터 타입의 길이를 varchar2(9) à varchar2(20) 으로 변경하시오

ALTER TABLE EMP

MODIFY job VARCHAR2(20);

l 타입을 변경하면 v_job의 타입도 변경된다

문제8. 부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호의 부서위치와 부서명이 아래와 같이 출력되게 하시오

부서위치 : DALLAS

부서명 : SALES

set serveroutput on

set verify off

accept p_deptno prompt '부서번호를 입력하시오! '

declare

v_deptno dept.deptno%type := &p_deptno;

v_loc dept.loc%type;

v_dname dept.dname%type;

begin

select loc, dname into v_loc, v_dname

from dept

where deptno=v_deptno;

dbms_output.put_line ('이름 : ' || v_loc);

dbms_output.put_line ('부서명 :  ' || v_dname);

end;

/

마지막문제9. 이름을 물어보게 하고 이름을 입력하면 해당 사원의 월급의 순위가 출력되게 하시오

set serveroutput on

set verify off

accept p_ename prompt '이름를 입력하시오! '

declare

v_ename emp.ename%type := upper('&p_ename');

v_rank number(10);

begin

SELECT 순위 INTO v_rank

FROM(SELECT ename, job, sal, RANK() OVER(ORDER BY sal desc) 순위

             FROM emp)

where ename=v_ename;

dbms_output.put_line ('순위 : ' || v_rank);

end;

/

마지막문제10. 부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호에 속한 사원들의 이름을 가로로 출력하시오

set serveroutput on

set verify off

accept p_deptno prompt '부서번호를 입력하시오! '

declare

v_deptno emp.deptno%type := &p_deptno;

v_emp VARCHAR(100);

            

begin

select deptno, listagg(ename,',') within GROUP (ORDER BY ename) into v_deptno, v_emp

from EMP

where deptno=v_deptno

GROUP BY deptno;

dbms_output.put_line ('부서번호 : '|| v_deptno || ' - ' || v_emp);

end;

/

문제70. 사원 테이블에 순위라는 컬럼을 아래와 같이 추가하고 사원이름을 물어보게 하고 사원이름을 입력하면 해당 사원의 순위가 자기의 월급에 대한 순위로 갱신되게 하시오

ALTER TABLE EMP

ADD rnk NUMBER(10);

accept p_ename prompt '이름을 입력하시오 '

declare

 v_ename emp.ename%type := upper('&p_ename');

 v_rank  number(2);

begin

 SELECT 순위 into v_rank

 FROM ( SELECT ename, RANK() OVER(ORDER BY sal desc) 순위 FROM EMP)

 WHERE ename = v_ename;

 UPDATE EMP

 SET rnk = v_rank

 WHERE ename = v_ename;

 dbms_output.put_line('수정된 행의 갯수는 ' || SQL%rowcount);

 dbms_output.put_line(v_ename || ' 의 순위는 ' || v_rank || ' 입니다. ');

end;

/