四、存储过程与函数
4.1 存储过程
-- 创建存储过程
CREATE OR REPLACE PROCEDURE update_employee_salary(
p_emp_id IN employees.emp_id%TYPE,
p_percent IN NUMBER,
p_success OUT BOOLEAN
) IS
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE emp_id = p_emp_id
FOR UPDATE;
UPDATE employees
SET salary = salary * (1 + p_percent/100)
WHERE emp_id = p_emp_id;
p_success := TRUE;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_success := FALSE;
DBMS_OUTPUT.PUT_LINE('员工不存在');
WHEN OTHERS THEN
ROLLBACK;
p_success := FALSE;
DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END update_employee_salary;
/
-- 调用存储过程
DECLARE
v_result BOOLEAN;
BEGIN
update_employee_salary(1001, 10, v_result);
IF v_result THEN
DBMS_OUTPUT.PUT_LINE('更新成功');
ELSE
DBMS_OUTPUT.PUT_LINE('更新失败');
END IF;
END;
/
-- 查看存储过程
SELECT object_name, status, created, last_ddl_time
FROM user_objects
WHERE object_type = 'PROCEDURE';
-- 查看源码
SELECT text FROM user_source
WHERE name = 'UPDATE_EMPLOYEE_SALARY'
ORDER BY line;
4.2 函数
-- 创建函数
CREATE OR REPLACE FUNCTION get_employee_annual_salary(
p_emp_id IN employees.emp_id%TYPE,
p_bonus_percent IN NUMBER DEFAULT 10
) RETURN NUMBER
IS
v_monthly_salary employees.salary%TYPE;
v_annual_salary NUMBER;
BEGIN
SELECT salary INTO v_monthly_salary
FROM employees
WHERE emp_id = p_emp_id;
v_annual_salary := v_monthly_salary * 12 * (1 + p_bonus_percent/100);
RETURN v_annual_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END get_employee_annual_salary;
/
-- 调用函数
SELECT
emp_name,
salary,
get_employee_annual_salary(emp_id, 15) AS annual_salary
FROM employees
WHERE dept_id = 10;
-- SQL中使用函数
SELECT get_employee_annual_salary(1001) FROM dual;
-- 函数限制：不能有DML操作（除非是自治事务）
CREATE OR REPLACE FUNCTION get_employee_count(
p_dept_id IN NUMBER
) RETURN NUMBER
IS
v_count NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION; -- 自治事务
BEGIN
SELECT COUNT(*) INTO v_count
FROM employees
WHERE dept_id = p_dept_id;
-- 可以在此执行DML（自治事务）
INSERT INTO access_log (access_time, function_name)
VALUES (SYSDATE, 'GET_EMPLOYEE_COUNT');
COMMIT;
RETURN v_count;
END get_employee_count;
/
4.3 包（Package）
-- 包规范
CREATE OR REPLACE PACKAGE emp_pkg IS
-- 常量
c_max_salary CONSTANT NUMBER := 100000;
c_min_salary CONSTANT NUMBER := 2000;
-- 类型定义
TYPE emp_cursor_type IS REF CURSOR;
-- 公共变量
g_dept_id NUMBER;
-- 函数和过程声明
FUNCTION get_employee_count(p_dept_id NUMBER) RETURN NUMBER;
FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER;
PROCEDURE add_employee(
p_emp_name VARCHAR2,
p_dept_id NUMBER,
p_salary NUMBER
);
PROCEDURE update_salary(
p_emp_id NUMBER,
p_new_salary NUMBER
);
PROCEDURE delete_employee(p_emp_id NUMBER);
-- 游标函数
FUNCTION get_employees_by_dept(p_dept_id NUMBER) RETURN emp_cursor_type;
END emp_pkg;
/
-- 包体
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
-- 私有变量
v_last_error VARCHAR2(4000);
-- 私有函数
FUNCTION validate_salary(p_salary NUMBER) RETURN BOOLEAN IS
BEGIN
RETURN p_salary BETWEEN c_min_salary AND c_max_salary;
END validate_salary;
-- 公共函数实现
FUNCTION get_employee_count(p_dept_id NUMBER) RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM employees
WHERE dept_id = p_dept_id;
RETURN v_count;
EXCEPTION
WHEN OTHERS THEN
v_last_error := SQLERRM;
RETURN 0;
END get_employee_count;
FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE emp_id = p_emp_id;
RETURN v_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END get_employee_salary;
PROCEDURE add_employee(
p_emp_name VARCHAR2,
p_dept_id NUMBER,
p_salary NUMBER
) IS
v_emp_id NUMBER;
BEGIN
IF NOT validate_salary(p_salary) THEN
RAISE_APPLICATION_ERROR(-20001, '工资超出范围');
END IF;
SELECT emp_seq.NEXTVAL INTO v_emp_id FROM dual;
INSERT INTO employees (emp_id, emp_name, dept_id, salary, hire_date)
VALUES (v_emp_id, p_emp_name, p_dept_id, p_salary, SYSDATE);
COMMIT;
END add_employee;
PROCEDURE update_salary(
p_emp_id NUMBER,
p_new_salary NUMBER
) IS
BEGIN
IF NOT validate_salary(p_new_salary) THEN
RAISE_APPLICATION_ERROR(-20001, '工资超出范围');
END IF;
UPDATE employees
SET salary = p_new_salary
WHERE emp_id = p_emp_id;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20002, '员工不存在');
END IF;
COMMIT;
END update_salary;
PROCEDURE delete_employee(p_emp_id NUMBER) IS
BEGIN
DELETE FROM employees WHERE emp_id = p_emp_id;
COMMIT;
END delete_employee;
FUNCTION get_employees_by_dept(p_dept_id NUMBER) RETURN emp_cursor_type IS
v_cursor emp_cursor_type;
BEGIN
OPEN v_cursor FOR
SELECT emp_id, emp_name, salary, hire_date
FROM employees
WHERE dept_id = p_dept_id
ORDER BY emp_name;
RETURN v_cursor;
END get_employees_by_dept;
BEGIN
-- 包初始化代码
DBMS_OUTPUT.PUT_LINE('Emp Package Initialized');
END emp_pkg;
/
-- 调用包
BEGIN
emp_pkg.add_employee('张三', 10, 5000);
emp_pkg.update_salary(1001, 5500);
DBMS_OUTPUT.PUT_LINE(emp_pkg.get_employee_count(10));
END;
/
五、触发器
5.1 DML触发器
-- 行级触发器
CREATE OR REPLACE TRIGGER trg_employees_audit
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
v_action VARCHAR2(10);
BEGIN
IF INSERTING THEN
v_action := 'INSERT';
-- 记录新值
INSERT INTO emp_audit_log (emp_id, action, old_data, new_data, action_date)
VALUES (:NEW.emp_id, v_action, NULL,
'Name:' || :NEW.emp_name || ',Salary:' || :NEW.salary,
SYSDATE);
ELSIF UPDATING THEN
v_action := 'UPDATE';
-- 记录变更
INSERT INTO emp_audit_log (emp_id, action, old_data, new_data, action_date)
VALUES (:OLD.emp_id, v_action,
'Name:' || :OLD.emp_name || ',Salary:' || :OLD.salary,
'Name:' || :NEW.emp_name || ',Salary:' || :NEW.salary,
SYSDATE);
ELSIF DELETING THEN
v_action := 'DELETE';
-- 记录旧值
INSERT INTO emp_audit_log (emp_id, action, old_data, new_data, action_date)
VALUES (:OLD.emp_id, v_action,
'Name:' || :OLD.emp_name || ',Salary:' || :OLD.salary,
NULL,
SYSDATE);
END IF;
END trg_employees_audit;
/
-- 语句级触发器
CREATE OR REPLACE TRIGGER trg_employees_stmt
AFTER INSERT OR UPDATE OR DELETE ON employees
DECLARE
v_action VARCHAR2(20);
BEGIN
IF INSERTING THEN
v_action := '批量插入';
ELSIF UPDATING THEN
v_action := '批量更新';
ELSE
v_action := '批量删除';
END IF;
-- 记录操作统计
INSERT INTO operation_stats (action, action_date, sql_count)
VALUES (v_action, SYSDATE, SQL%ROWCOUNT);
END trg_employees_stmt;
/
-- INSTEAD OF触发器（用于视图）
CREATE OR REPLACE TRIGGER trg_emp_view
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
-- 插入员工表
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (emp_seq.NEXTVAL, :NEW.emp_name,
(SELECT dept_id FROM dept WHERE dept_name = :NEW.dept_name));
END trg_emp_view;
/
5.2 DDL触发器
-- 数据库级DDL触发器
CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER CREATE OR ALTER OR DROP ON SCHEMA
DECLARE
v_sql_text ORA_NAME_LIST_T;
v_sql VARCHAR2(4000);
BEGIN
-- 获取DDL语句
FOR i IN 1..ORA_SQL_TXT(v_sql_text) LOOP
v_sql := v_sql || v_sql_text(i);
END LOOP;
-- 记录DDL操作
INSERT INTO ddl_audit_log (username, object_type, object_name, ddl_date, sql_text)
VALUES (ORA_LOGIN_USER, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, SYSDATE, v_sql);
-- 阻止删除重要表
IF ORA_DICT_OBJ_NAME IN ('EMPLOYEES', 'DEPT') AND ORA_DICT_OBJ_TYPE = 'TABLE'
AND ORA_SYSEVENT = 'DROP' THEN
RAISE_APPLICATION_ERROR(-20000, '禁止删除重要表');
END IF;
END trg_ddl_audit;
/
5.3 系统事件触发器
-- 数据库启动/关闭触发器
CREATE OR REPLACE TRIGGER trg_db_startup
AFTER STARTUP ON DATABASE
BEGIN
INSERT INTO db_event_log (event_name, event_date, instance_name)
VALUES ('DATABASE STARTUP', SYSDATE, SYS_CONTEXT('USERENV', 'INSTANCE_NAME'));
COMMIT;
END trg_db_startup;
/
-- 用户登录/注销触发器
CREATE OR REPLACE TRIGGER trg_user_login
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO user_login_log (username, login_time, ip_address, program)
VALUES (USER, SYSDATE, SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT('USERENV', 'MODULE'));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL; -- 避免影响用户登录
END trg_user_login;
/
-- 限制登录时间
CREATE OR REPLACE TRIGGER trg_limit_login
BEFORE LOGON ON DATABASE
BEGIN
IF USER = 'APP_USER' AND TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) NOT BETWEEN 8 AND 18 THEN
RAISE_APPLICATION_ERROR(-20000, '只能在8:00-18:00之间登录');
END IF;
END trg_limit_login;
/
-- 查看触发器
SELECT trigger_name, trigger_type, status, table_name
FROM user_triggers;
-- 禁用/启用触发器
ALTER TRIGGER trg_employees_audit DISABLE;
ALTER TRIGGER trg_employees_audit ENABLE;
六、事务与锁
6.1 事务管理
-- 事务边界
-- 隐式开始（第一个DML语句）
UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;
-- 显式提交
COMMIT;
-- 回滚
ROLLBACK;
-- 保存点
SAVEPOINT before_update;
UPDATE employees SET salary = salary * 1.2 WHERE dept_id = 20;
ROLLBACK TO SAVEPOINT before_update;
-- 设置只读事务
SET TRANSACTION READ ONLY;
-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 查看事务信息
SELECT * FROM v$transaction;
SELECT * FROM v$session WHERE status = 'ACTIVE';
6.2 锁机制
-- 显式锁表
LOCK TABLE employees IN SHARE MODE; -- 共享锁
LOCK TABLE employees IN EXCLUSIVE MODE; -- 排他锁
-- 行级锁（通过SELECT FOR UPDATE）
SELECT * FROM employees
WHERE dept_id = 10
FOR UPDATE; -- 锁定所选行
-- 等待超时设置
SELECT * FROM employees
WHERE emp_id = 1001
FOR UPDATE WAIT 5; -- 等待5秒
-- 不等待
SELECT * FROM employees
WHERE emp_id = 1001
FOR UPDATE NOWAIT;
-- 跳过锁定行
SELECT * FROM employees
WHERE dept_id = 10
FOR UPDATE SKIP LOCKED;
-- 查看锁信息
SELECT
l.session_id,
l.locked_mode,
o.object_name,
l.os_user_name,
l.process
FROM v$locked_object l
JOIN dba_objects o ON l.object_id = o.object_id;
-- 查看阻塞会话
SELECT
blocking_session,
sid,
serial#,
username,
status,
sql_id
FROM v$session
WHERE blocking_session IS NOT NULL;
6.3 死锁处理
-- 查询死锁
SELECT
DECODE(block, 0, 'Waiting', 'Blocking') AS status,
sid,
serial#,
username,
osuser,
machine,
program
FROM v$session
WHERE sid IN (
SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL
) OR sid IN (
SELECT sid FROM v$session WHERE blocking_session IS NOT NULL
);
-- 杀死阻塞会话
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
-- 查询被锁的对象和SQL
SELECT
l.sid,
s.username,
s.machine,
l.type,
l.lmode,
l.request,
q.sql_text
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE l.type IN ('TM', 'TX');
