admin_pkg 패키지 만들기
이 절에서는 admin_pkg 패키지를 만드는 방법, 서브 프로그램의 작동 방식, 패키지를 필요로 하는 유저에게 패키지에 대한 실행 권한을 부여하는 방법 및 해당 유저가 서브 프로그램 중 하나를 호출하는 방법을 보여줍니다.
admin_pkg 패키지를 만들려면 다음을 수행합니다.
-
사용자 app_admin으로 Oracle Database에 접속합니다.
자세한 내용은 "SQL*Plus에서 Oracle Database에 접속" 또는 "SQL Developer에서 Oracle Database에 접속"을 참조하십시오.
-
다음 동의어를 생성합니다.
CREATE SYNONYM departments FOR app_data.departments; CREATE SYNONYM jobs FOR app_data.jobs; CREATE SYNONYM departments_sequence FOR app_data.departments_sequence;SQL*Plus 또는 SQL Developer의 Worksheet에서 CREATE SYNONYM 문을 입력할 수 있습니다. 또는 SQL Developer 툴 Create Synonym을 사용하여 테이블을 생성할 수도 있습니다.
-
Package Spec을 생성합니다.
-
Package Body를 생성합니다.
참조:
-
"패키지 정보"
admin_pkg에 대한 Package Spec 작성
주: app_admin 사용자로 Oracle Database에 접속해야 합니다.
응용 프로그램 관리자를 위한 API인 admin_pkg에 대한 PACKAGE Spec을 생성하려면 다음 CREATE PACKAGE 문을 사용합니다. 명령문은 SQL*Plus 또는 SQL Developer의 Worksheet에서 입력할 수 있습니다. 또는 SQL Developer 도구 Create Package를 사용하여 패키지를 생성할 수도 있습니다.
CREATE OR REPLACE PACKAGE admin_pkg
AS
PROCEDURE update_job
( p_job_id IN jobs.job_id%TYPE,
p_job_title IN jobs.job_title%TYPE := NULL,
p_min_salary IN jobs.min_salary%TYPE := NULL,
p_max_salary IN jobs.max_salary%TYPE := NULL );
PROCEDURE add_job
( p_job_id IN jobs.job_id%TYPE,
p_job_title IN jobs.job_title%TYPE,
p_min_salary IN jobs.min_salary%TYPE,
p_max_salary IN jobs.max_salary%TYPE );
PROCEDURE update_department
( p_department_id IN departments.department_id%TYPE,
p_department_name IN departments.department_name%TYPE := NULL,
p_manager_id IN departments.manager_id%TYPE := NULL,
p_update_manager_id IN BOOLEAN := FALSE );
FUNCTION add_department
( p_department_name IN departments.department_name%TYPE,
p_manager_id IN departments.manager_id%TYPE )
RETURN departments.department_id%TYPE;
END admin_pkg;
/참조:
-
Oracle Database PL/SQL Language Reference - CREATE PACKAGE 문에 대한 자세한 내용
admin_pkg에 대한 Package Body 작성
주: app_admin 사용자로 Oracle Database에 접속해야 합니다.
응용 프로그램 관리자를 위한 API인 admin_pkg에 대한 PACKAGE BODY를 생성하려면 다음 CREATE PACKAGE BODY 문을 사용합니다. 명령문은 SQL*Plus 또는 SQL Developer의 Worksheet에서 입력할 수 있습니다. 또는 SQL Developer 도구 Create Body를 사용하여 패키지를 생성할 수도 있습니다.
CREATE OR REPLACE PACKAGE BODY admin_pkg
AS
PROCEDURE update_job
( p_job_id IN jobs.job_id%TYPE,
p_job_title IN jobs.job_title%TYPE := NULL,
p_min_salary IN jobs.min_salary%TYPE := NULL,
p_max_salary IN jobs.max_salary%TYPE := NULL )
IS
BEGIN
UPDATE jobs
SET job_title = NVL( p_job_title, job_title ),
min_salary = NVL( p_min_salary, min_salary ),
max_salary = NVL( p_max_salary, max_salary )
WHERE job_id = p_job_id;
END update_job;
PROCEDURE add_job
( p_job_id IN jobs.job_id%TYPE,
p_job_title IN jobs.job_title%TYPE,
p_min_salary IN jobs.min_salary%TYPE,
p_max_salary IN jobs.max_salary%TYPE )
IS
BEGIN
INSERT INTO jobs ( job_id, job_title, min_salary, max_salary )
VALUES ( p_job_id, p_job_title, p_min_salary, p_max_salary );
END add_job;
PROCEDURE update_department
( p_department_id IN departments.department_id%TYPE,
p_department_name IN departments.department_name%TYPE := NULL,
p_manager_id IN departments.manager_id%TYPE := NULL,
p_update_manager_id IN BOOLEAN := FALSE )
IS
BEGIN
IF ( p_update_manager_id ) THEN
UPDATE departments
SET department_name = NVL( p_department_name, department_name ),
manager_id = p_manager_id
WHERE department_id = p_department_id;
ELSE
UPDATE departments
SET department_name = NVL( p_department_name, department_name )
WHERE department_id = p_department_id;
END IF;
END update_department;
FUNCTION add_department
( p_department_name IN departments.department_name%TYPE,
p_manager_id IN departments.manager_id%TYPE )
RETURN departments.department_id%TYPE
IS
l_department_id departments.department_id%TYPE;
BEGIN
INSERT INTO departments ( department_id, department_name, manager_id )
VALUES ( departments_sequence.NEXTVAL, p_department_name, p_manager_id )
RETURNING department_id INTO l_department_id;
RETURN l_department_id;
END add_department;
END admin_pkg;
/참조:
-
Oracle Database PL/SQL Language Reference - CREATE PACKAGE BODY 문에 대한 자세한 내용
자습서: admin_pkg 서브 프로그램의 작동 방식 표시
이 자습서에서는 SQL*Plus를 사용하여 admin_pkg 패키지의 서브 프로그램이 작동하는 방식을 보여줍니다. 이 자습서에서는 jobs_aufer 트리거의 작동 방식도 보여줍니다.
주: SQL*Plus에서 app_admin 유저로 Oracle Database에 연결해야 합니다.
admin_pkg 서브 프로그램이 작동하는 방식을 보여주는 단계:
-
ID가 AD_VP인 직무에 대한 정보 표시:
SELECT * FROM jobs WHERE job_id = 'AD_VP';결과:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- AD_VP Administration Vice President 15000 30000 -
이 직무에 대한 최대 급여를 인상하고 해당 직무에 대한 정보를 다시 표시합니다.
EXEC admin_pkg.update_job( 'AD_VP', p_max_salary => 31000 ); SELECT * FROM jobs WHERE job_id = 'AD_VP';결과:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- AD_VP Administration Vice President 15000 31000 -
ID가 IT_PROG인 작업에 대한 정보를 표시합니다.
SELECT * FROM jobs WHERE job_id = 'IT_PROG';결과:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- IT_PROG Programmer 4000 10000 -
이 직무에 대한 최대 급여를 인상해 보십시오.
EXEC admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 );결과(SQL*Plus에서):
SQL> EXEC admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 ); BEGIN admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 ); END; * ERROR at line 1: ORA-20001: Salary update would violate 5 existing employee records ORA-06512: at "APP_DATA.JOBS_AUFER", line 12 ORA-04088: error during execution of trigger 'APP_DATA.JOBS_AUFER' ORA-06512: at "APP_ADMIN.ADMIN_PKG", line 10 ORA-06512: at line 1 -
새 작업을 추가하고 해당 작업에 대한 정보를 표시합니다.
EXEC admin_pkg.add_job( 'AD_CLERK', 'Administrative Clerk', 3000, 7000 ); SELECT * FROM jobs WHERE job_id = 'AD_CLERK';결과:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- AD_CLERK Administrative Clerk 3000 7000 -
부서 100에 대한 정보를 표시합니다.
SELECT * FROM departments WHERE department_id = 100;결과:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 100 Finance 108 -
부서 100의 이름 및 관리자를 변경하고 부서에 대한 정보를 표시합니다.
EXEC admin_pkg.update_department( 100, 'Financial Services' ); EXEC admin_pkg.update_department( 100, p_manager_id => 111, p_update_manager_id => true ); SELECT * FROM departments WHERE department_id = 100;결과:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 100 Financial Services 111
참조: "패키지 만들기 및 관리"
app_admin_user에게 Execute 권한 부여
주: app_admin 사용자로 Oracle Database에 접속해야 합니다.
admin_pkg 패키지에 대한 실행 권한을 app_admin_user(응용 프로그램 관리자)에게 부여하려면 다음 GRANT 문을 사용합니다. 명령문은 SQL*Plus 또는 SQL Developer의 Worksheet에서 입력할 수 있습니다.
GRANT EXECUTE ON admin_pkg TO app_admin_user;참조:
-
Oracle Database SQL Language Reference - GRANT 문에 대한 자세한 내용
자습서: app_admin_user로 add_department 호출
이 자습서에서는 SQL*Plus를 사용하여 app_admin.admin_pkg.add_department 함수를 유저 app_admin_user(응용 프로그램 관리자)로 호출한 다음 새 부서에 대한 정보를 확인하는 방법을 보여줍니다.
admin_pkg.add_department를 app_admin_user로 호출하는 단계:
-
SQL*Plus에서 app_admin_user 유저로 Oracle Database에 연결합니다.
자세한 내용은 "SQL*Plus에서 Oracle Database에 접속"을 참조하십시오.
-
이 동의어를 생성합니다.
CREATE SYNONYM admin_pkg FOR app_admin.admin_pkg; -
함수의 반환 값에 대한 바인드 변수를 선언합니다.
VARIABLE n NUMBER -
관리자 없이 새 부서를 추가합니다.
EXEC :n := admin_pkg.add_department( 'New department', NULL ); -
새 부서의 관리자 ID 표시:
PRINT :n결과:
N ---------- 275
새 부서에 대한 정보를 확인하는 단계
-
사용자 app_admin으로 Oracle Database에 접속합니다.
-
새 부서에 대한 정보 표시:
SELECT * FROM departments WHERE department_name LIKE 'New department%';결과:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 275 New department