오라클 강의자료 2011.09.27 (쌍용교육센터)

-- 2011.9.27

 저장 프로시져 : 페이지 나누기, 삭제, 수정, 저장
 1. 생성방법
    CREATE PROCEDURE 프로시져명(매개변수) : 생성(명칭의 중복)
    CREATE OR REPLACE PROCEDURE 프로시져명(매개변수)
    : 생성, 수정
 2. 삭제
    DROP PROCEDURE 프로시져명
 3. 매개변수의 종류
    IN     : 입력값
    OUT    : 출력값
    INOUT  : 입출력값
   
 4. 형식
    CREATE PROCEDURE 프로시져명(매개변수)
    IS
       변수선언위치
    BEGIN
       쿼리문장이 작성
    END;
    /
 5. 실행방법
    오라클 ESECUTE 프로시져명; == 자체에서 처리
    update,insert,delete
    select : 값을 출력시
    받는 변수선언
    VARIABLE 변수명;
    ESCUTE 프로시져명(;변수명)
    값을 출력
    PRINT 변수명
   
    자바  CallableStatement

    DECLARE
  vempno NUMBER(4);
  vename emp%TYPE : 같은 데이터형
  vemp emp%ROWTYPE : RECORD단위
    vemp.empno
    vemp.sal

-- SELECT 문장
CREATE OR REPLACE PROCEDURE emp_print(vempno IN emp.empno%TYPE,
    vename OUT emp.ename%TYPE,
    vjop OUT emp.job%TYPE,
    vsal OUT emp.sal%TYPE)
IS
BEGIN
  SELECT ename,job,sal INTO vename,vjop,vsal FROM emp WHERE empno = vempno;
  commit;
END;
/

 

VARIABLE var_name VARCHAR2(20);
VARIABLE var_job VARCHAR2(20);
VARIABLE var_sal NUMBER;
EXECUTE emp_print(7900,:var_name,:var_job,:var_sal);

PRINT var_name;
PRINT var_job;
PRINT var_sal;

 

   PS/SQL 구조 :  절차적 언어
                  C언어 = (PRO C)
   형식)
        선언문 (DECLARE) : 변수, 상수선언
        실행부
               BEGIN
                  쿼리문장(제어문이용) : 반복문, 조건문
               END;
               /
        예외 처리부
    예)
        DECLARE
          vempno NUMBER(4);
          vename VARCHAR2(20);
        BEGIN
          SELECT empno, ename INTO vempno, vename
          FROM emp
          WHERE empno=7788;
        END;
        /

--PS/SQL의 기초

DECLARE
  vempno NUMBER(4);
  vename VARCHAR2(20);
BEGIN
  SELECT empno, ename INTO vempno, vename
  FROM emp
  WHERE empno=7900;
  dbms_output.put_line('*****결과값*****');
  dbms_output.put_line(TO_CHAR(vempno)||' '||vename);
END;
/

DECLARE
 vemp emp%ROWTYPE;
BEGIN
 SELECT * INTO vemp FROM emp WHERE empno=7900;
 dbms_output.put_line('사번 이름 직업 고용일');
 dbms_output.put_line(vemp.empno||'  '||vemp.ename||'  '||vemp.job||'

'||vemp.hiredate);
END;
/

 

-- 선택문(제어문)

 IF-THEN-END IF

DECLARE
 vempno NUMBER(4);
 vename VARCHAR2(20);
 vdeptno emp.deptno%TYPE;
 vdname VARCHAR2(20):=NULL;
BEGIN
 SELECT empno,ename,deptno INTO
vempno,vename,vdeptno
 FROM emp
 WHERE empno=7900;

 IF(vdeptno=10) THEN
  vdname:='영업부';
 END IF;
 IF(vdeptno=20) THEN
  vdname:='자재부';
 END IF;
 IF(vdeptno=30) THEN
  vdname:='개발부';
 END IF;

 dbms_output.put_line('사번 이름 부서명');
 dbms_output.put_line(vempno||'  '||vename||' '||vdname);
END;
/


--다중 IF(if else if~)
DECLARE
  vdeptno NUMBER(2);
  vename VARCHAR2(20);
  vdname VARCHAR2(20):= NULL;
BEGIN
  SELECT ename,deptno INTO vename,vdeptno FROM emp WHERE empno=7900;
  IF(vdeptno = 10) THEN
    vdname:='영업부';
  ELSIF(vdeptno=20) THEN
    vdname:='개발부';
  ELSIF(vdeptno=30) THEN
    vdname:='기획부';
  END IF;

  dbms_output.put_line('*****결과*****');
  dbms_output.put_line(vdeptno||' '||vename||' '||vdname);
END;
/

--IF THEN-ELSE-END IF (if~else)

DECLARE
 vename VARCHAR2(20);
 vcomm emp.comm%TYPE;
BEGIN
 SELECT ename,comm INTO vename,vcomm FROM emp WHERE empno=7900;
 IF vcomm>0 THEN
   dbms_output.put_line(vename||'은 커미션이 있다');
 ELSE
   dbms_output.put_line(vename||'은 커미션이 없다.');
 END IF;
END;
/

--반복문 FOR-LOOP

ACCEPT pdan PROMPT '단 입력:';
DECLARE
 vdan NUMBER(2):=&pdan;
 i NUMBER(2) DEFAULT 0;
 tot NUMBER:=0;
BEGIN
 FOR i IN 1..9 LOOP
 tot:=vdan*i;
 dbms_output.put_line(vdan||'*'||i||'='||tot);
 END LOOP;
END;
/

 사번을 입력받아서
 이름, 직위를 출력하는 PS/SQL작성

ACCEPT pempno PROMPT '사번 입력 : ';
DECLARE
 vempno NUMBER := &pempno;
 vename VARCHAR2(10);
 vjob VARCHAR2(10);
BEGIN
 SELECT ename, job into vename , vjob FROM emp WHERE empno = vempno;
 dbms_output.put_line(vename||' '||vjob);
END;
/ 

 
--레코드 설정

DECLARE
  TYPE MY_RECORD IS RECORD
  (
      deptno NUMBER(2),
      ename VARCHAR2(20),
      loc VARCHAR2(20)
  );
  REC1 MY_RECORD;
BEGIN
 SELECT * INTO REC1 FROM dept WHERE deptno=10;

 dbms_output.put_line('****결과값****');
 dbms_output.put_line(REC1.deptno||' '||REC1.ename||' '||REC1.loc);
END;
/

  커서 : 사용자가 실행한 sql문장의 단위(실행결과값을 저장하고 있는 메모리)
  1. 커서를 선언 (CURSOR cur_name)
  2. 커서를 오픈 (OPEN cur_name)
  3. 커서를 통해서 결과값을 가지고 온다
     (FATCH cur_name) 커서로부터 받을 변수값
  4. 커서를 닫는다
     (CLOSE cur_name)

  커서의 상태
  %NOTFOUND : 커서의 영역에 자료가 모두 인출되었는지?
  %FOUND  : 커서영역에 자료가 있는지
  %ISOPEN : 커서가 OPEN여부 확인
  %ROWCOUNT : 현재 인출된 자료가 몇개인지?

  숙제
  급여 sal/100 5000=>50

  KING ****************(5000)
  SCOTT ***********(3000)
  c ********(1500)

--커서 사용
DECLARE
  vempno NUMBER(4);
  vename VARCHAR2(20);
  vsal NUMBER(7,2);
  CURSOR c IS
  SELECT empno, ename, sal FROM emp;
 
BEGIN
  OPEN c;
    dbms_output.put_line('사번  이름  급여');
    LOOP
      FETCH c INTO vempno,vename,vsal;
      EXIT WHEN c%NOTFOUND;
      dbms_output.put_line(vempno||' '||vename||' '||vsal||'--->'||c%

ROWCOUNT);
    END LOOP;
    CLOSE c;
 END;
/

-- 이름 급여
DECLARE
  tot NUMBER := 0;
  CURSOR emp_cursor IS
  SELECT ename,sal FROM emp;
BEGIN
 dbms_output.put_line('이름         급여');
 dbms_output.put_line('-----------------');

 FOR cur_var IN emp_cursor LOOP
 tot:=tot+cur_var.sal;
 dbms_output.put_line(cur_var.ename||'                   '||cur_var.sal);
 END LOOP;
 dbms_output.put_line('---------------------');
 dbms_output.put_line('-------------'||tot);
END;
/


-- FOR 레코드명 IN 커서명 LOOP
DECLARE
   vemp emp%ROWTYPE;
   CURSOR c
   IS
   SELECT * FROM emp;
BEGIN
   FOR vemp IN c LOOP
   EXIT WHEN c%NOTFOUND;
   dbms_output.put_line(vemp.empno||' '||vemp.ename||' '||vemp.hiredate);
   END LOOP;
END;
/

  1.PS/SQL 형식
    DECLARE
      선언문(변수, 레코드 선언)
    BEGIN
      출력
    END;
    /
  2.RECORD변수 설정 TYPE 명칭 IS RECORD()
  3.변수 : vename VARCHAR2(20)
           vsal emp.sal%TYPE
           vemp emp%ROWTYPE
  4.여러개의 레코드를 출력
    CURSOR선언
    CURSOR 사용법
    1) OPEN ~ FETCH ~ CLOSE
    2) FOR 레코드명 IN 커서명 LOOP
  5. 응용 : 저장 프로시져
  6. 패키지 : (클래스)