employees_pkg 패키지 생성
이 섹션에서는 employees_pkg 패키지를 생성하는 방법, 서브 프로그램의 작동 방식, 패키지를 필요로 하는 유저에게 패키지에 대한 실행 권한을 부여하는 방법, 그리고 이러한 유저가 서브 프로그램 중 하나를 호출하는 방법을 보여줍니다.
employees_pkg 패키지를 생성하는 단계:
-
사용자 app_code로 Oracle Database에 접속합니다.
자세한 내용은 "SQL*Plus에서 Oracle Database에 접속" 또는 "SQL Developer에서 Oracle Database에 접속"을 참조하십시오.
-
다음 동의어를 생성합니다.
CREATE OR REPLACE SYNONYM employees FOR app_data.employees; CREATE OR REPLACE SYNONYM departments FOR app_data.departments; CREATE OR REPLACE SYNONYM jobs FOR app_data.jobs; CREATE OR REPLACE SYNONYM job_history FOR app_data.job_history;SQL*Plus 또는 SQL Developer의 Worksheet에서 CREATE SYNONYM 문을 입력할 수 있습니다. 또는 SQL Developer 도구 Create Synonym을 사용하여 동의어를 생성할 수도 있습니다.
-
Package Spec을 생성합니다.
-
Package Body를 생성합니다.
employees_pkg에 대한 Package Spec 작성
주: 사용자 app_code로 Oracle Database에 접속해야 합니다.
관리자용 API인 employees_pkg에 대한 PACKAGE Spec을 생성하려면 다음 CREATE PACKAGE 문을 사용합니다. 명령문은 SQL*Plus 또는 SQL Developer의 Worksheet에서 입력할 수 있습니다. 또는 SQL Developer 도구 Create Package를 사용하여 패키지를 생성할 수도 있습니다.
CREATE OR REPLACE PACKAGE employees_pkg
AS
PROCEDURE get_employees_in_dept
( p_deptno IN employees.department_id%TYPE,
p_result_set IN OUT SYS_REFCURSOR );
PROCEDURE get_job_history
( p_employee_id IN employees.department_id%TYPE,
p_result_set IN OUT SYS_REFCURSOR );
PROCEDURE show_employee
( p_employee_id IN employees.employee_id%TYPE,
p_result_set IN OUT SYS_REFCURSOR );
PROCEDURE update_salary
( p_employee_id IN employees.employee_id%TYPE,
p_new_salary IN employees.salary%TYPE );
PROCEDURE change_job
( p_employee_id IN employees.employee_id%TYPE,
p_new_job IN employees.job_id%TYPE,
p_new_salary IN employees.salary%TYPE := NULL,
p_new_dept IN employees.department_id%TYPE := NULL );
END employees_pkg;
/참조:
-
Oracle Database PL/SQL Language Reference - CREATE PACKAGE 문에 대한 자세한 내용
employees_pkg의 Package Body 작성
주: 사용자 app_code로 Oracle Database에 접속해야 합니다.
관리자용 API인 employees_pkg의 PACKAGE BODY를 생성하려면 다음 CREATE PACKAGE BODY 문을 사용합니다. 명령문은 SQL*Plus 또는 SQL Developer의 Worksheet에서 입력할 수 있습니다. 또는 SQL Developer 도구 Create Body를 사용하여 패키지를 생성할 수도 있습니다.
CREATE OR REPLACE PACKAGE BODY employees_pkg
AS
PROCEDURE get_employees_in_dept
( p_deptno IN employees.department_id%TYPE,
p_result_set IN OUT SYS_REFCURSOR )
IS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN p_result_set FOR
SELECT e.employee_id,
e.first_name || ' ' || e.last_name name,
TO_CHAR( e.hire_date, 'Dy Mon ddth, yyyy' ) hire_date,
j.job_title,
m.first_name || ' ' || m.last_name manager,
d.department_name
FROM employees e INNER JOIN jobs j ON (e.job_id = j.job_id)
LEFT OUTER JOIN employees m ON (e.manager_id = m.employee_id)
INNER JOIN departments d ON (e.department_id = d.department_id)
WHERE e.department_id = p_deptno ;
END get_employees_in_dept;
PROCEDURE get_job_history
( p_employee_id IN employees.department_id%TYPE,
p_result_set IN OUT SYS_REFCURSOR )
IS
BEGIN
OPEN p_result_set FOR
SELECT e.First_name || ' ' || e.last_name name, j.job_title,
e.job_start_date start_date,
TO_DATE(NULL) end_date
FROM employees e INNER JOIN jobs j ON (e.job_id = j.job_id)
WHERE e.employee_id = p_employee_id
UNION ALL
SELECT e.First_name || ' ' || e.last_name name,
j.job_title,
jh.start_date,
jh.end_date
FROM employees e INNER JOIN job_history jh
ON (e.employee_id = jh.employee_id)
INNER JOIN jobs j ON (jh.job_id = j.job_id)
WHERE e.employee_id = p_employee_id
ORDER BY start_date DESC;
END get_job_history;
PROCEDURE show_employee
( p_employee_id IN employees.employee_id%TYPE,
p_result_set IN OUT sys_refcursor )
IS
BEGIN
OPEN p_result_set FOR
SELECT *
FROM (SELECT TO_CHAR(e.employee_id) employee_id,
e.first_name || ' ' || e.last_name name,
e.email_addr,
TO_CHAR(e.hire_date,'dd-mon-yyyy') hire_date,
e.country_code,
e.phone_number,
j.job_title,
TO_CHAR(e.job_start_date,'dd-mon-yyyy') job_start_date,
to_char(e.salary) salary,
m.first_name || ' ' || m.last_name manager,
d.department_name
FROM employees e INNER JOIN jobs j on (e.job_id = j.job_id)
RIGHT OUTER JOIN employees m ON (m.employee_id = e.manager_id)
INNER JOIN departments d ON (e.department_id = d.department_id)
WHERE e.employee_id = p_employee_id)
UNPIVOT (VALUE FOR ATTRIBUTE IN (employee_id, name, email_addr, hire_date,
country_code, phone_number, job_title, job_start_date, salary, manager,
department_name) );
END show_employee;
PROCEDURE update_salary
( p_employee_id IN employees.employee_id%type,
p_new_salary IN employees.salary%type )
IS
BEGIN
UPDATE employees
SET salary = p_new_salary
WHERE employee_id = p_employee_id;
END update_salary;
PROCEDURE change_job
( p_employee_id IN employees.employee_id%TYPE,
p_new_job IN employees.job_id%TYPE,
p_new_salary IN employees.salary%TYPE := NULL,
p_new_dept IN employees.department_id%TYPE := NULL )
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date, job_id,
department_id)
SELECT employee_id, job_start_date, TRUNC(SYSDATE), job_id, department_id
FROM employees
WHERE employee_id = p_employee_id;
UPDATE employees
SET job_id = p_new_job,
department_id = NVL( p_new_dept, department_id ),
salary = NVL( p_new_salary, salary ),
job_start_date = TRUNC(SYSDATE)
WHERE employee_id = p_employee_id;
END change_job;
END employees_pkg;
/참조:
-
Oracle Database PL/SQL Language Reference - CREATE PACKAGE BODY 문에 대한 자세한 내용
자습서: employees_pkg 서브 프로그램의 작동 방식 표시
이 자습서에서는 SQL*Plus를 사용하여 employees_pkg 패키지의 서브 프로그램이 작동하는 방식을 보여줍니다. 이 자습서에서는 employees_aiufer 및 CHECK 제약 조건 job_history_date_check 트리거의 작동 방식을 보여줍니다.
주: SQL*Plus에서 app_code 유저로 Oracle Database에 연결해야 합니다.
SQL*Plus를 사용하여 employees_pkg 서브 프로그램의 작동 방식을 표시하려면 다음과 같이 하십시오.
-
형식 지정 명령을 사용하여 출력의 가독성을 높입니다. 예:
SET LINESIZE 80 SET RECSEP WRAPPED SET RECSEPCHAR "=" COLUMN NAME FORMAT A15 WORD_WRAPPED COLUMN HIRE_DATE FORMAT A20 WORD_WRAPPED COLUMN DEPARTMENT_NAME FORMAT A10 WORD_WRAPPED COLUMN JOB_TITLE FORMAT A29 WORD_WRAPPED COLUMN MANAGER FORMAT A11 WORD_WRAPPED -
서브 프로그램 파라미터 p_result_set의 값에 대해 바인드 변수를 선언합니다.
VARIABLE c REFCURSOR -
department 90에 있는 사원을 표시합니다.
EXEC employees_pkg.get_employees_in_dept( 90, :c ); PRINT c결과:
EMPLOYEE_ID NAME HIRE_DATE JOB_TITLE ----------- --------------- -------------------- -------------------------- MANAGER DEPARTMENT ----------- ---------- 100 Steven King Tue Jun 17th, 2003 President Executive =========================================================================== 102 Lex De Haan Sat Jan 13th, 2001 Administration Vice President Steven King Executive =========================================================================== 101 Neena Kochhar Wed Sep 21st, 2005 Administration Vice President Steven King Executive =========================================================================== -
사원 101의 직무 기록을 표시합니다.
EXEC employees_pkg.get_job_history( 101, :c ); PRINT c결과:
NAME JOB_TITLE START_DAT END_DATE --------------- ----------------------------- --------- --------- Neena Kochhar Administration Vice President 16-MAR-05 Neena Kochhar Accounting Manager 28-OCT-01 15-MAR-05 Neena Kochhar Public Accountant 21-SEP-97 27-OCT-01 -
사원 101에 대한 일반 정보를 표시합니다.
EXEC employees_pkg.show_employee( 101, :c ); PRINT c결과:
ATTRIBUTE VALUE --------------- ---------------------------------------------- EMPLOYEE_ID 101 NAME Neena Kochhar EMAIL_ADDR NKOCHHAR HIRE_DATE 21-sep-2005 COUNTRY_CODE +1 PHONE_NUMBER 515.123.4568 JOB_TITLE Administration Vice President JOB_START_DATE 16-mar-05 SALARY 17000 MANAGER Steven King DEPARTMENT_NAME Executive 11 rows selected. -
직무 관리 부사장에 대한 정보를 표시합니다.
SELECT * FROM jobs WHERE job_title = 'Administration Vice President';결과:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------- ---------- ---------- AD_VP Administration Vice President 15000 30000 -
사원 101에게 직무 범위를 벗어난 새 급여를 제공해 봅니다.
EXEC employees_pkg.update_salary( 101, 30001 );결과:
SQL> EXEC employees_pkg.update_salary( 101, 30001 ); BEGIN employees_pkg.update_salary( 101, 30001 ); END; * ERROR at line 1: ORA-20002: Salary modification invalid ORA-06512: at "APP_DATA.EMPLOYEES_AIUFER", line 13 ORA-04088: error during execution of trigger 'APP_DATA.EMPLOYEES_AIUFER' ORA-06512: at "APP_CODE.EMPLOYEES_PKG", line 77 ORA-06512: at line 1 -
사원 101에게 직무 범위 내에 새 급여를 주고 여기에 대한 일반 정보를 다시 표시합니다.
EXEC employees_pkg.update_salary( 101, 18000 ); EXEC employees_pkg.show_employee( 101, :c ); PRINT c결과:
ATTRIBUTE VALUE --------------- ---------------------------------------------- EMPLOYEE_ID 101 NAME Neena Kochhar EMAIL_ADDR NKOCHHAR HIRE_DATE 21-sep-2005 COUNTRY_CODE +1 PHONE_NUMBER 515.123.4568 JOB_TITLE Administration Vice President JOB_START_DATE 16-mar-05 SALARY 18000 MANAGER Steven King DEPARTMENT_NAME Executive 11 rows selected. -
사원 101의 직무를 급여가 더 낮은 현재 직무로 변경합니다.
EXEC employees_pkg.change_job( 101, 'AD_VP', 17500, 90 );결과:
SQL> exec employees_pkg.change_job( 101, 'AD_VP', 17500, 90 ); BEGIN employees_pkg.change_job( 101, 'AD_VP', 17500, 80 ); END; * ERROR at line 1: ORA-02290: check constraint (APP_DATA.JOB_HISTORY_DATE_CHECK) violated ORA-06512: at "APP_CODE.EMPLOYEES_PKG", line 101 ORA-06512: at line 1 -
사원에 대한 정보를 표시합니다. (이전 단계의 명령문에 의해 급여가 변경된 것은 아니며 17500이 아니라 18000입니다.)
exec employees_pkg.show_employee( 101, :c ); print c결과:
ATTRIBUTE VALUE --------------- ---------------------------------------------- EMPLOYEE_ID 101 NAME Neena Kochhar EMAIL_ADDR NKOCHHAR HIRE_DATE 21-sep-2005 COUNTRY_CODE +1 PHONE_NUMBER 515.123.4568 JOB_TITLE Administration Vice President JOB_START_DATE 10-mar-2015 SALARY 18000 MANAGER Steven King DEPARTMENT_NAME Executive 11 rows selected.
참조:
-
SQL*Plus 명령에 대한 자세한 내용은 SQL*Plus 사용 설명서 및 참조를 참조하십시오.
app_user 및 app_admin_user에게 실행 권한 부여
주: 사용자 app_code로 Oracle Database에 접속해야 합니다.
employees_pkg 패키지에 대한 실행 권한을 app_user(일반적으로 관리자) 및 app_admin_user(응용 프로그램 관리자)에게 부여하려면 다음 GRANT 문을 순서대로 사용하십시오. 명령문은 SQL*Plus 또는 SQL Developer의 Worksheet에 입력할 수 있습니다.
GRANT EXECUTE ON employees_pkg TO app_user;
GRANT EXECUTE ON employees_pkg TO app_admin_user;참조:
-
Oracle Database SQL Language Reference - GRANT 문에 대한 자세한 내용
자습서: app_user 또는 app_admin_user로 get_job_history 호출
이 자습서에서는 SQL*Plus를 사용하여 app_user(일반적으로 관리자) 또는 app_admin_user(응용 프로그램 관리자)로 서브 프로그램 app_code.employees_pkg.get_job_history를 호출하는 방법을 보여줍니다.
app_user 또는 app_admin_user로 employees_pkg.get_job_history를 호출하는 단계:
-
SQL*Plus에서 app_user 또는 app_admin_user 유저로 Oracle Database에 연결합니다.
자세한 내용은 "SQL*Plus에서 Oracle Database에 접속"을 참조하십시오.
-
이 동의어를 생성합니다.
CREATE SYNONYM employees_pkg FOR app_code.employees_pkg; -
사원 101의 직무 기록을 표시합니다.
EXEC employees_pkg.get_job_history( 101, :c ); PRINT c결과:
NAME JOB_TITLE START_DAT END_DATE --------------- ----------------------------- --------- --------- Neena Kochhar Administration Vice President 16-MAR-05 15-MAY-12 Neena Kochhar Accounting Manager 28-OCT-01 15-MAR-05 Neena Kochhar Public Accountant 21-SEP-97 27-OCT-01