ICT블로그

(1)Oracle Database Administrator's Guide

10/11(일) 47P ~ 51P

이렇게 수행 시 에러 발생함.

select a.employee_id, a.first_name||' '||a.last_name names, (select b.department_name

from employees a, departments b

where a.department_id = b.department_id) dep_names

from employees a

where a.department_id = 100;

ORA-01427: single-row subquery returns more than one row

01427. 00000 -  "single-row subquery returns more than one row"

*Cause:    

*Action:

--> 요렇게 고쳐서 수행해야 함

select a.employee_id, a.first_name||' '||a.last_name names, (select b.department_name

from departments b

where a.department_id = b.department_id) dep_names

from employees a

where a.department_id = 100;

- 묻지마 블록(Anonymous block)의 경우 실행 시 마다 항상 컴파일을 수행해야 함. 이런 불편함을 줄이기 위해 1회 컴파일 후 반복해서 사용할 수 있는 블록을 오라클 객체로 제공함. --> 이것이 PL/SQL 서브 프로그램임.

- PL/SQL 서브프로그램 (PL/SQL subprogram)

  * 파리미터 고유의 이름을 가진 PL/SQL 블록

  * 내장 프로시져 및 함수를 통칭

  * 함수는 결과값을 반환하므로 SELECT나 WHERE 절 등에 위치 할 수 있음.

-- 함수 (내장 함수, 사용자 정의 함수, User Defined Function)

(예제1)

CREATE OR REPLACE FUNCTION hr.emp_salaries (emp_id number)

           RETURN NUMBER IS

    nSalaries NUMBER(9);        -- 여기까지 함수 명세부(specification)

BEGIN                           -- 여기서부터 구현부(body)

  nSalaries := 0;

   SELECT salary

     INTO nSalaries

     FROM hr.employees

    WHERE employee_id = emp_id;

    RETURN nSalaries;

  END;

(예제2)

SELECT 절에 함수 사용하기

select employee_id, first_name, emp_salaries(employee_id)

  from employees

 where department_id = 100;

-- 함수 활용하기

employees 테이블에서 각 사원의 부서명을 알고 싶을 경우

1) employees 테이블, departments 테이블 조인

2) select 절에 서브쿼리 사용 (서브쿼리 내에서는 조인이 사용됨)

3) FUNCTION 생성 후 사용

1. 함수만들기

create or replace function get_dep_name (dep_id number)

                 return varchar2 is

               sDepName varchar2(30);

          begin

              select department_name

                into sDepName

                from departments

               where DEPARTMENT_ID = dep_id;

          RETURN sDepName;

END;

2. 사용하기

select employee_id, first_name||' '||last_name names, get_dep_name(department_id) dep_names

from employees

where department_id = 100;

--> 함수를 사용할 경우 조인이 발생하지 않는다!

이처럼 함수를 사용할 경우 성능상 이점이 발생하나, 함수 개수가 많아지는 등 관리상의 문제가 발생할 수 있으므로 경우에 따라 조인, 서브쿼리, 함수중에서 적절한 것을 택해 사용한다.

참고. 서브쿼리 사용시

select a.employee_id, a.first_name||' '||a.last_name names, (select b.department_name

from departments b

where a.department_id = b.department_id) dep_names

from employees a

where a.department_id = 100;

-- 프로시져 (내장 프로시져, Stored procedure)

(예제) 신입사원 등록 프로시져 만들기

create or replace procedure register_emp (

                                         f_name VARCHAR2, -- *자리수 명시 X

                                         l_name VARCHAR2,

                                         e_acct VARCHAR2,

                                         j_id   VARCHAR2)  IS

begin

  insert_into employees (employee_id, first_name, last_name, email, hire_date, job_id)

                 values (employee_seq.nextval, f_name, l_name, e_acct, sysdate, j_id);

commit;

exception when others then  

  dbms_output.put_line(f_name||' '||l_name||' register is failed!');

rollback;

end;

* 프로시저의 파라미터로 명시할 때는 VARCHAR2만 명시한다. 구체적인 자리수(BYTES)까지 명시하게 되면 오류가 발생한다.

create or replace procedure register_emp (

                                         f_name VARCHAR2,

                                         l_name VARCHAR2,

                                         e_acct VARCHAR2,

                                         j_id   VARCHAR2)  IS

begin

  insert into employees (employee_id, first_name, last_name, email, hire_date, job_id)

                 values (employees_seq.nextval, f_name, l_name, e_acct, sysdate, j_id);

commit;

exception when others then  

  dbms_output.put_line(f_name||' '||l_name||' register is failed!');

rollback;

end;

(예제) 프로시져 사용하기

exec register_emp ('조영', '대', 'djoyoung', 'IT_PROG');