정리

**************************************************************
**Parameter가 포함된 Cursor
**************************************************************

cursor cursor_name.. (parameter name, type *type의 크기는 지정x)

SET serveroutput ON
DECLARE
CURSOR c_emp_cursor (deptid number)
IS
SELECT employee_id, last_name
FROM employees
WHERE department_id=deptid;

v_emp_record c_emp_cursor%ROWTYPE;
BEGIN
OPEN c_emp_cursor(10);
LOOP
  FETCH c_emp_cursor into v_emp_record;
  EXIT
  WHEN c_emp_cursor%notfound;
  DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id|| ' ' ||
  v_emp_record.last_name);
  end loop;
  close c_emp_cursor;
  END;
  /

**************************************************************
**Parameter가 포함된 Cursor( for문으로 변경)
**************************************************************
SET serveroutput ON
DECLARE
  CURSOR c_emp_cursor (deptid NUMBER)
  IS
    SELECT employee_id, last_name FROM employees WHERE department_id=deptid;
  v_emp_record c_emp_cursor%ROWTYPE;
BEGIN
 
  FOR v_emp_record IN c_emp_cursor(10)
  LOOP
  -- WHEN c_emp_cursor%notfound;
   DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id|| ' ' || v_emp_record.last_name);
   END LOOP;
END;
/

**************************************************************
**Parameter가 포함된 Cursor( for문으로 변경)
**************************************************************
****80,60 SA_REP, IT_PROG의 모든 사원을 출력

SET SERVEROUTPUT ON
DECLARE
 CURSOR c_emp_cursor(deptid NUMBER, job_id VARCHAR2)
 IS
 SELECT employee_id,department_id, last_name,job_id
 FROM employees
 WHERE department_id=deptid AND job_id='SA_REP';
 
 v_emp_record c_emp_cursor%ROWTYPE;
 
 BEGIN
 FOR v_emp_record IN c_emp_cursor(80,'SA_REP')
 LOOP
  DBMS_OUTPUT.PUT_LINE(v_emp_record.department_id|| ' ' || v_emp_record.last_name||' '||
  v_emp_record.job_id|| ' ' ||v_emp_record.employee_id);
  END LOOP;
  FOR v_emp_record IN c_emp_cursor(60,'IT_PROG')
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp_record.department_id|| ' ' || v_emp_record.last_name||' '||
  v_emp_record.job_id|| ' ' ||v_emp_record.employee_id);
   END LOOP;
END;
/

**************************************************************
***WHERE CURRENT OF 절
**************************************************************
WHERE CURRENT OF절을 쓰지 않으면 모두가 바뀐다.

** WHERE CURRENT OF 절을 사용한 경우

SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp_cursor
IS
SELECT employee_id, salary
FROM employees
WHERE department_id=20 FOR UPDATE NOWAIT;

BEGIN
FOR emp_record IN c_emp_cursor
LOOP
    UPDATE employees SET salary=10 WHERE CURRENT OF c_emp_cursor;
END LOOP;
END;
/

** WHERE CURRENT OF 절을 사용하지 않은 경우

 

**************************************************************
** 예외 처리
**************************************************************

SET serveroutput ON
DECLARE
  v_lname VARCHAR2(15);
  BEGIN
  SELECT last_name INTO v_lname
  FROM employees
  WHERE department_id=20;
 
   DBMS_OUTPUT.PUT_LINE('John' ' s last_name is : ' || v_lname);

  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE(' Your select statement retrieved
    multiple rows. Consider using a cursor.');
    END;
    /

 

미리 정의된 오라클 오류 트랩
- NO_DATA_FOUND
- TOO_MANY_ROWS
- INVALID_CURSOR
- ZERO_DIVIDE
- DUP_VAL_ON_INDEX

***********************************************************
-- first_name이 'E'로 시작하는 사원 출력
-- 데이터가 없으면 해당사원이 없습니다.
-- 데이터가 두 건 이상이면 사원이 두 명 이상입니다.
***********************************************************

SET serveroutput ON
DECLARE
  v_fname employees.first_name%type;
  BEGIN
        SELECT first_name INTO v_fname FROM employees
        WHERE first_name LIKE 'E%';
       
        DBMS_OUTPUT.PUT_LINE('E인 이름 :' ||v_fname);
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('해당 사원이 없다.');
        WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('사원이 두 명 이상');
        END;
        /

**************************************************************
** 미리 정의되지 않은 오류 트랩
**************************************************************

 

**************************************************************
** 예외 트랩에 대한 함수
**************************************************************
SQLCODE : 오류 코드에 대한 숫자 값을 반환
SQLERRM : 오류 번호와 연관된 메시지를 반환

create table t_pragma(no number primary key, name varchar2(10));

insert into t_pragma values(1,'aaa');

SET serveroutput ON
DECLARE
  new_msg exception;
  pragma exception_init(new_msg, -1);
  BEGIN
  insert into t_pragma values(1,'CCC');
  exception
  WHEN new_msg THEN
  DBMS_OUTPUT.PUT_LINE('존재하는 번호입니다.');
END;
/
*******************************************************************************
** 함수에 다른 값을 넣어버리면
오류 보고 -
ORA-00001: 무결성 제약 조건(EXPERT.SYS_C0011176)에 위배됩니다
ORA-06512:  5행
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
이러한 오류메세지가 뜬다.

SET serveroutput ON
DECLARE
  new_msg exception;
  pragma exception_init(new_msg, -2);
  BEGIN
  insert into t_pragma values(1,'CCC');
  exception
  WHEN new_msg THEN
  DBMS_OUTPUT.PUT_LINE('존재하는 번호입니다.');
END;
/
*********************************************************************************
오라클 레퍼런스 사이트
http://docs.oracle.com/en/database/database.html
-> Oracle Database 11g Release 2(11.2) 선택
-> Administerng 선택
-> Error Message 선택

*********************************************************************************
*** Oracle 서버 오류 01400("cannot insert NULL")을 트랩하려면..
*********************************************************************************

SET serveroutput ON
DECLARE
    e_insert_excep EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_insert_excep, -01400);
   
BEGIN
INSERT INTO departments
(department_id, department_name) VALUES (280, NULL);
EXCEPTION
 WHEN e_insert_excep THEN
    DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED');
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

INSERT OPERATION FAILED
ORA-01400: NULL을 ("EXPERT"."DEPARTMENTS"."DEPARTMENT_NAME") 안에 삽입할 수 없습니다

 

 

 

 

 

*********************************************************************************
*** ERROR 함수 사용하기
*********************************************************************************

create table errors(err_num number(20), err_msg VARCHAR2(100));

SET SERVEROUTPUT ON

DECLARE
    new_msg exception;
    pragma exception_init(new_msg, -1);
    err_num number;
    err_msg varchar2(255);
BEGIN
    INSERT into t_pragma values(1,'CCC');
exception
WHEN new_msg THEN
DBMS_OUTPUT.PUT_LINE('존재하는 번호 입니다.');
err_num := SQLCODE;
err_msg := substr(SQLERRM, 1, 100);
insert into errors values(err_num,err_msg);
END;
/

 

 

9장
SYS @ prod > select owner, object_name, object_type
  2  from dba_objects
  3  where object_type='PROCEDURE';

show error를 입력하면 error를 확인한다.

*********************************************************************************
*** Procedure 생성 IN 파라미터 모드
*********************************************************************************

EXPERT @ prod > CREATE OR REPLACE
  2  PROCEDURE raise_salary
  3  (p_id            IN employees.employee_id%TYPE,
  4  p_percent          IN NUMBER)
  5  IS
  6  BEGIN
  7  UPDATE employees SET salary=salary*(1+p_percent/100) WHERE employee_id=p_id;
  8  END raise_salary;
  9  /

Procedure created.

EXPERT @ prod > excute raise_salary(206,10);
SP2-0734: unknown command beginning "excute rai..." - rest of line ignored.
EXPERT @ prod > execute raise_salary(206,10);

PL/SQL procedure successfully completed.

EXPERT @ prod > select employee_id, salary
  2  from employees
  3  where employee_id=206;

EMPLOYEE_ID     SALARY
----------- ----------
        206       9130

EXPERT @ prod > select text from user_source
  2  where name='RAISE_SALARY'
  3  /
--> 만들어진 구문을 볼 수 있다.
TEXT
--------------------------------------------------------------------------------
PROCEDURE raise_salary
(p_id            IN employees.employee_id%TYPE,
p_percent               IN NUMBER)
IS
BEGIN
UPDATE employees SET salary=salary*(1+p_percent/100) WHERE employee_id=p_id;
END raise_salary;

7 rows selected.

*********************************************************************************
*** Procedure 생성 OUT 파라미터 모드
*********************************************************************************

EXPERT @ prod > CREATE OR REPLACE
  2  PROCEDURE query_emp
  3  (p_id IN employees.employee_id%TYPE,
  4  p_name OUT employees.last_name%TYPE,
  5  p_salary OUT employees.salary%TYPE)
  6  IS
  7  BEGIN
  8  SELECT last_name,
  9  salary INTO p_name, p_salary FROM employees
 10  WHERE employee_id=p_id;
 11  END query_emp;
 12  /

Procedure created.

EXPERT @ prod > SET SERVEROUTPUT ON
EXPERT @ prod > DECLARE
  2  v_emp_name employees.last_name%TYPE;
  3  v_emp_sal employees.salary%TYPE;
  4  BEGIN
  5  query_emp(206,v_emp_name, v_emp_sal);
  6  DBMS_OUTPUT.PUT_LINE(v_emp_name || ' earns ' || to_char(v_emp_sal, '$999,999,00'));
  7  END;
  8  /
Gietz earns       $91,30

PL/SQL procedure successfully completed.

 

*********************************************************************************
*** Procedure 생성 IN OUT 파라미터 모드
*********************************************************************************

EXPERT @ prod > show errors;
No errors.
EXPERT @ prod > CREATE OR REPLACE
  2  PROCEDURE format_phone
  3  (p_phone_no IN OUT VARCHAR2)
  4  IS
  5  BEGIN
  6  p_phone_no :='('||SUBSTR(p_phone_no,1,3)||SUBSTR(p_phone_no,4,4)||'-'||SUBSTR(p_phone_no,8);
  7  END format_phone;
  8  /

Procedure created.

EXPERT @ prod > variable b_phone_no varchar2(15)
EXPERT @ prod > execute :b_phone_no := '01079799292'

PL/SQL procedure successfully completed.

EXPERT @ prod > print b_phone_no

B_PHONE_NO
--------------------------------
01079799292

EXPERT @ prod > execute format_phone (:b_phone_no)

PL/SQL procedure successfully completed.

EXPERT @ prod > print b_phone_no

B_PHONE_NO
--------------------------------
(0107979-9292

EXPERT @ prod >

 

*********************************************************************************
*** FUNCTION 생성 예제1
*********************************************************************************

EXPERT @ prod > CREATE OR REPLACE
  2  FUNCTION get_sal
  3  (p_id employees.employee_id%TYPE)
  4  RETURN NUMBER
  5  IS
  6  v_sal employees.salary%TYPE:=0;
  7  BEGIN
  8  select salary into v_sal from employees where employee_id = p_id;
  9  RETURN v_sal;
 10  END get_sal;
 11  /

Function created.

EXPERT @ prod > set serveroutput ON
EXPERT @ prod > execute DBMS_OUTPUT.PUT_LINE(get_sal(100));
24000

PL/SQL procedure successfully completed.

EXPERT @ prod > set serveroutput ON
EXPERT @ prod > DECLARE
  2  sal employees.salary%TYPE;
  3  BEGIN
  4  sal:=get_sal(100);
  5  DBMS_OUTPUT.PUT_LINE('The salary is: ' || sal);
  6  END;
  7  /
The salary is: 24000

PL/SQL procedure successfully completed.

*********************************************************************************
*** SQL 표현식에 함수 사용
*********************************************************************************

EXPERT @ prod > CREATE OR REPLACE
  2  FUNCTION tax
  3  (p_value IN NUMBER)
  4  RETURN NUMBER
  5  IS
  6  BEGIN
  7  RETURN (p_value*0.08);
  8  END tax;
  9  /

Function created.

EXPERT @ prod > select employee_id,
  2  last_name, salary, tax(salary) from employees
  3  where department_id=50;

EMPLOYEE_ID LAST_NAME                     SALARY TAX(SALARY)
----------- ------------------------- ---------- -----------
        124 Mourgos                         5800         464
        141 Rajs                            4300         344
        142 Davies                          3900         312
        143 Matos                           3400         272
        144 Vargas                          3300         264

 

*********************************************************************************
*** 패키지
*********************************************************************************

- 선언부
EXPERT @ prod > CREATE OR REPLACE
  2  PACKAGE emp_total
  3  AS
  4  PROCEDURE emp_sum;
  5  PROCEDURE emp_avg;
  6  END emp_total;
  7  /

Package created.

- 몸체부
EXPERT @ prod > create or replace package body emp_total
  2  as
  3  procedure emp_sum
  4  is
  5  cursor emp_total_sum
  6  is
  7  select count(*), sum(salary) from employees;
  8  total_num number;
  9  total_sum number;
 10  BEGIN
 11  open emp_total_sum;
 12  fetch emp_total_sum into total_num, total_sum;
 13  DBMS_OUTPUT.PUT_LINE('총 인원수 : '||total_num||',급여합계:'||total_sum);
 14  close emp_total_sum;
 15  end emp_sum;
 16  procedure emp_avg
 17  is
 18  cursor emp_total_avg
 19  is
 20  select count(*), avg(nvl(salary,0)) from employees;
 21  total_num number;
 22  total_avg number;
 23  BEGIN
 24  open emp_total_avg;
 25  fetch emp_total_avg into total_num, total_avg;
 26  DBMS_OUTPUT.PUT_LINE('총인원수:'||total_num||',급여평균:'||total_avg);
 27  close emp_total_avg;
 28  end emp_avg;
 29  end emp_total;
 30  /

Package body created.