— 变量
g_total_employees NUMBER :=
0;

— 类型
TYPE
employee_rec IS
RECORD
(
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER
);

— 游标
CURSOR c_employees IS
SELECT
employee_id, first_name, last_name, salary
FROM employees;

— 过程
PROCEDURE
hire_employee (
p_employee_id NUMBER,
p_first_name VARCHAR2,
p_last_name VARCHAR2,
p_salary NUMBER
);

— 函数
FUNCTION
get_employee_salary (
p_employee_id NUMBER
) RETURN
NUMBER;

— 函数
FUNCTION
get_total_employees RETURN
NUMBER;
END
fgedu_employee_pkg;
/

— 创建包体
CREATE OR REPLACE
PACKAGE
BODY fgedu_employee_pkg AS
— 私有变量
g_hire_count NUMBER :=
0;

— 私有函数
FUNCTION
validate_salary (
p_salary NUMBER
) RETURN
BOOLEAN IS
BEGIN
RETURN
p_salary > 0 AND p_salary <= c_max_salary;
END
validate_salary;

— 过程实现
PROCEDURE
hire_employee (
p_employee_id NUMBER,
p_first_name VARCHAR2,
p_last_name VARCHAR2,
p_salary NUMBER
) IS
BEGIN
— 验证薪水
IF
NOT validate_salary(p_salary) THEN
RAISE_APPLICATION_ERROR(-20001, ‘薪水必须在0到’ || c_max_salary || ‘之间’);
END IF
;

— 插入员工记录
INSERT
IN
TO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (p_employee_id, p_first_name, p_last_name, LOWER(p_first_name) || ‘.’ || LOWER(p_last_name) || ‘@fgedu.net.cn’,
SYSDATE, ‘IT_PROG’, p_salary);

— 更新统计信息
g_hire_count :=
g_hire_count + 1;
SELECT
COUNT(*) IN
TO g_total_employees
FROM employees;

COMMIT
;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK
;
RAISE;
END
hire_employee;

— 函数实现
FUNCTION
get_employee_salary (
p_employee_id NUMBER
) RETURN
NUMBER IS
v_salary NUMBER;
BEGIN
SELECT
salary IN
TO v_salary
FROM employees
WHERE employee_id = p_employee_id;

RETURN
v_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN
0;
WHEN OTHERS THEN
RAISE;
END
get_employee_salary;

— 函数实现
FUNCTION
get_total_employees RETURN
NUMBER IS
BEGIN
IF
g_total_employees = 0 THEN
SELECT
COUNT(*) IN
TO g_total_employees
FROM employees;
END IF
;

RETURN
g_total_employees;
END
get_total_employees;
END
fgedu_employee_pkg;
/

执行：