#9_140625_PL_SQL_변수의 데이터 유형

# 변수의 데이터 유형

l 변수의 데이터 유형(scalar datatype)

1.     문자 : varchar2, blob, clob

2.     숫자 : number

3.     날짜 : date

4.     부울(Boolean) : boolean – true, false, null

l composite datetype

1.     레코드(Record)

2.     컬렉션(Collection)

l declare 와 end 사이에 쓴 것은 내부변수이고 나머지는 외부변수

l 오라클 함수 2가지

1.     단일행 함수 : 문자, 숫자, 날짜, 변환, 일반

2.     복수행 함수(=GROUP 함수) : max, min, sum, avg, count

l PL/SQL내에서 쓸 수 있는 함수

- 단일행 함수 : upper, month_between

l PL/SQL내에서 쓸 수 없는 함수

- decode

- 그룹함수 : max, min, avg

l begin 안에 select문에는 실행절. 단일행, decode, 그룹함수 전부 이용가능

set  serveroutput  on

declare

outer_variable  varchar2(20)  := '난 바깥쪽이다';

BEGIN

 declare

  inner_variable  varchar2(20) := '난 안쪽이다';

 begin

  dbms_output.put_line(inner_variable);

  dbms_output.put_line(outer_variable);

 end;

dbms_output.put_line(outer_variable);

dbms_output.put_line(inner_variable);

end;

/

l 바깥에서 안쪽의 변수는 수행할 수 있지만 안쪽 변수는 바깥에서 수행못함

l dbms_output.put_line(inner_variable) – 이부분을 주석처리하면 수행가능

문제14. 문제13번 소스를 수정하는데 직업을 물어보게 해서 직업을 입력할 떄 직업을 소문자로 입력하던 대문자로 입력하던 상관없이 결과가 출력되게 하시오

accept p_job prompt '직업을 입력하시오! '

VARIABLE g_job VARCHAR2

declare

v_job emp.job%type := UPPER('&p_job');

begin

select COUNT(*) INTO :g_job

from emp

where job=v_job;

end;

/

문제15. 시작숫자가 1이고 최대숫자가 1000인 시퀀스를 생성하시오

CREATE SEQUENCE seq7

START WITH 1

INCREMENT BY 1

MAXVALUE 1000;

SELECT SEQ7.NEXTVAL FROM dual;

문제16. 위의 시퀀스를 이용해서 익명 PL/SQL 블록을 생성하는데 아래와 같이 실행을 하면 시퀀스의 다음값이 출력되게 하시오

set serveroutput on

declare

v_seq number(10);

begin

SELECT SEQ7.NEXTVAL INTO v_seq

FROM dual;

dbms_output.put_line ('다음 시퀀스값은 ' || v_seq);

end;

/

문제17. 책 p3-10을 보고 위의 PL/SQL 을 11g 이상 스타일로 변경하시오

set serveroutput on

declare

v_seq number(10);

begin

v_seq := seq7.nextval;

dbms_output.put_line ('다음 시퀀스값은 ' || v_seq);

end;

/

문제19. 날짜를 물어보게하고 날짜를 입력하면 해당 날짜에 입사한 사원의 이름을 출력하는PL/SQL을 작성하시오

set serveroutput on

set verify off

accept p_hire prompt '날짜를 입력하시오! '

declare

v_hire emp.hiredate%type := '&p_hire';

v_ename emp.ename%type;

begin

select ename into v_ename

from emp

where hiredate = v_hire;

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

end;

/

l data format이 일치해야 암시적 형변환이 일어남(hiredate = ‘1981/11/17’)

문제20. 사원이름을 물어보게 사원이름을 입력하면 해당 사원의 월급을 자신의 월급의 2배로 갱신하는 PL/SQL을 작성하시오

set serveroutput on

set verify off

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

declare

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

begin

 UPDATE EMP

 SET sal = sal*2

 WHERE ename = v_ename;

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

end;

/

l SQL%rowcount : 가장 최근에 SQL 문으로 인해서 영향을 받은 행의 수

문제22. 이름을 물어보게 이름을 입력하면 해당 사원의 월급을 2배로 갱신되게 하는 프로그램을 작성하는데 결과가 아래와 같이 출력되게 하시오

수정되기 전 월급 : 3000

1개의 행이 갱신됨

수정된후의 월급 : 6000

set serveroutput on

set verify off

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

declare

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

 v_besal emp.sal%TYPE;

 v_afsal emp.sal%TYPE;

BEGIN

 SELECT sal, sal*2 INTO v_besal, v_afsal

 FROM EMP

 WHERE ename = v_ename;

 UPDATE EMP

 SET sal = sal*2

 WHERE ename = v_ename;

dbms_output.put_line ('수정되기전 월급 ' || v_besal);

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

dbms_output.put_line ('수정된후 월급 ' || v_afsal);

end;

/

문제24.  emp783으로 해서 생성. 사원번호를 물어보게 하고 사원번호를 입력하면 해당 사원의 모든 컬럼의 정보가 emp783 테이블에 입력될 수 있도록 하시오. 몇 건이 입력되었습니다. 라는 메시지도 출력되게 하시오.

CREATE TABLE emp783

AS

SELECT * FROM EMP;

TRUNCATE TABLE emp783;

set serveroutput on

set verify off

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

declare

 v_empno emp.empno%type := &p_empno;

begin

 INSERT INTO emp783

 SELECT * FROM EMP

 WHERE empno = v_empno;

dbms_output.put_line (SQL%ROWCOUNT || ' 건이 입력되었습니다');

end;

/

문제26. 사원번호를 물어보게 하고 사원번호를 입력하면 해당 사원의 사원번호, 이름, 월급만 입력되는데 월급이 입력될 때 월급의 30%만 입력되도록 위의 소스를 수정하시오

set serveroutput on

set verify off

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

declare

 v_empno emp.empno%type := &p_empno;

begin

 INSERT INTO emp783(empno, ename, sal)

 SELECT empno, ename, sal*0.3 FROM EMP

 WHERE empno = v_empno;

dbms_output.put_line (SQL%ROWCOUNT || ' 건이 입력되었습니다');

end;

/

문제27. 아래의 테이블을 생성하고 생성한 테이블에 data를 입력하는 PL/SQL 을 작성하는데 부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호의 부서번호, 토탈월급, 최대월급, 최소월급이 입력되게 하시오

set serveroutput on

set verify off

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

declare

 v_deptno emp.deptno%type := &p_deptno;

begin

 INSERT INTO dept_sal(deptno, sumsal, maxsal, minsal)

 SELECT deptno, SUM(sal), MAX(sal), MIN(sal) FROM EMP

 WHERE deptno = v_deptno

 GROUP BY deptno;

dbms_output.put_line (SQL%ROWCOUNT || ' 건이 입력되었습니다');

end;

/

문제28. 사원번호를 물어보게 하고 사원번호를 입력하면 해당 사원의 정보가 지워지게 하시오

set serveroutput on

set verify off

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

declare

 v_empno emp.empno%type := &p_empno;

begin

 DELETE EMP

 WHERE empno=v_empno;

dbms_output.put_line (SQL%ROWCOUNT || ' 건이 지워졌습니다');

end;

/