企业官网建设流程全解析

企业网站报备,从零开始学习网站开发计划,张家界做网站,举报网站建设工作总结Oracle 过程、函数、触发器和包详解 本章系统讲解 Oracle 数据库中 存储过程#xff08;Procedure#xff09;、函数#xff08;Function#xff09;、触发器#xff08;Trigger#xff09; 和 程序包#xff08;Package#xff09; 的语法、使用方法及最佳实践。这些是…Oracle 过程、函数、触发器和包详解本章系统讲解 Oracle 数据库中存储过程Procedure、函数Function、触发器Trigger和程序包Package的语法、使用方法及最佳实践。这些是 PL/SQL 高级编程的核心组件广泛用于业务逻辑封装、数据完整性控制和模块化开发。一、环境准备与安装说明1. 前提条件已安装Oracle Database如 19c、21c 或免费的Oracle XE已启用SCOTT 用户用于练习2. 启用 SCOTT 用户若未启用-- 以 sysdba 登录CONNECT/ASSYSDBA-- 解锁并设置密码ALTERUSERscott ACCOUNTUNLOCKIDENTIFIEDBYtiger;-- 连接 scottCONNECTscott/tiger3. 开启输出用于调试SETSERVEROUTPUTON;✅ 推荐工具SQL*Plus、Oracle SQL Developer二、存储过程Stored Procedure1. 什么是存储过程存储在数据库中的命名 PL/SQL 块可接受参数、执行 DML/DDL、返回结果通过 OUT 参数不返回值与函数区别可被应用程序或其它 PL/SQL 调用2. 创建存储过程语法CREATE [OR REPLACE] PROCEDURE procedure_name ( parameter1 [IN | OUT | IN OUT] datatype [DEFAULT value], parameter2 ... ) IS -- 声明变量可选 BEGIN -- 执行逻辑 [EXCEPTION ...] END [procedure_name]; /3. 存储过程的参数模式模式说明IN默认模式输入参数只读OUT输出参数调用后返回值IN OUT输入输出参数可修改4. 示例创建带参数的存储过程案例1根据员工编号查询姓名和工资使用 OUT 参数CREATE OR REPLACE PROCEDURE get_emp_info ( p_empno IN emp.empno%TYPE, -- 输入员工编号 p_ename OUT emp.ename%TYPE, -- 输出姓名 p_sal OUT emp.sal%TYPE -- 输出工资 ) IS BEGIN SELECT ename, sal INTO p_ename, p_sal FROM emp WHERE empno p_empno; EXCEPTION WHEN NO_DATA_FOUND THEN p_ename : NULL; p_sal : NULL; DBMS_OUTPUT.PUT_LINE(Error: Employee || p_empno || not found.); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(Unexpected error: || SQLERRM); END get_emp_info; /调用存储过程在匿名块中DECLARE v_name emp.ename%type; v_sal emp.sal%type; BEGIN get_emp_info(7369, v_name, v_sal); -- 调用过程 DBMS_OUTPUT.PUT_LINE(Name: || v_name || , Salary: || v_sal); END; /5. IN 参数的默认值-- 创建带默认值的过程 CREATE OR REPLACE PROCEDURE raise_salary ( p_empno IN emp.empno%TYPE, p_percent IN NUMBER DEFAULT 10 -- 默认加薪 10% ) IS BEGIN UPDATE emp SET sal sal * (1 p_percent / 100) WHERE empno p_empno; IF SQL%ROWCOUNT 0 THEN RAISE_APPLICATION_ERROR(-20001, Employee not found); END IF; COMMIT; DBMS_OUTPUT.PUT_LINE(Salary raised by || p_percent || % for employee || p_empno); END raise_salary; /调用方式两种-- 使用默认值 EXEC raise_salary(7369); -- 显式指定 EXEC raise_salary(7369, 15);EXEC是 SQL*Plus 快捷命令等价于BEGIN proc; END;6. 删除存储过程DROPPROCEDUREget_emp_info;三、函数Function1. 什么是函数与存储过程类似但必须返回一个值可在 SQL 语句中直接调用如SELECT my_func() FROM dual不能执行 DML除非使用自治事务2. 创建函数语法CREATE [OR REPLACE] FUNCTION function_name ( parameter_list ) RETURN return_datatype IS -- 声明 BEGIN -- 逻辑 RETURN expression; END; /3. 示例计算员工年薪含奖金CREATE OR REPLACE FUNCTION calc_annual_salary ( p_empno IN emp.empno%TYPE ) RETURN NUMBER IS v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; BEGIN SELECT sal, NVL(comm, 0) INTO v_sal, v_comm FROM emp WHERE empno p_empno; -- 年薪 月薪*12 奖金 RETURN (v_sal * 12) v_comm; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END calc_annual_salary; /4. 调用函数方式1在 SQL 中调用SELECTename,calc_annual_salary(empno)ASannual_salFROMempWHEREdeptno20;方式2在 PL/SQL 中调用DECLARE v_annual NUMBER; BEGIN v_annual : calc_annual_salary(7369); DBMS_OUTPUT.PUT_LINE(Annual salary: || v_annual); END; /5. 删除函数DROPFUNCTIONcalc_annual_salary;⚠️ 注意若函数被其他对象依赖需先删除依赖项。四、触发器Trigger1. 触发器简介在特定DML、DDL 或数据库事件发生时自动执行的 PL/SQL 块用于实现数据完整性、审计、日志、业务规则2. 触发器类型类型触发时机说明语句级每条 DML 语句触发一次无法访问 :OLD/:NEW行级每行受影响时触发一次可用 :OLD旧值、:NEW新值替换触发器INSTEAD OF用于视图替代 DML 操作用户事件触发器LOGON、LOGOFF、DDL 等用于审计3. 语句级触发器示例-- 在 emp 表更新后记录操作时间 CREATE OR REPLACE TRIGGER trg_emp_update_stmt AFTER UPDATE ON emp DECLARE BEGIN DBMS_OUTPUT.PUT_LINE(Employee table updated at || TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS)); END; /此触发器对整个 UPDATE 语句触发一次。4. 行级触发器示例最常用-- 禁止工资降低 CREATE OR REPLACE TRIGGER trg_emp_sal_check BEFORE UPDATE OF sal ON emp FOR EACH ROW -- 关键行级触发 BEGIN IF :NEW.sal :OLD.sal THEN RAISE_APPLICATION_ERROR(-20002, Error: Salary cannot be decreased!); END IF; END; /:OLD.sal是更新前的值:NEW.sal是更新后的新值。测试UPDATEempSETsal1000WHEREempno7369;-- 若原工资 1000将报错5. 替换触发器INSTEAD OF用于对不可直接更新的视图执行 DML。-- 创建视图CREATEVIEWemp_dept_viewASSELECTe.empno,e.ename,d.dnameFROMemp eJOINdept dONe.deptnod.deptno;-- 创建 INSTEAD OF 触发器CREATEORREPLACETRIGGERtrg_instead_of_insert INSTEADOFINSERTONemp_dept_viewFOR EACH ROWDECLAREv_deptno dept.deptno%TYPE;BEGIN-- 根据部门名查 deptnoSELECTdeptnoINTOv_deptnoFROMdeptWHEREdname:NEW.dname;-- 插入到 emp 表INSERTINTOemp(empno,ename,deptno)VALUES(:NEW.empno,:NEW.ename,v_deptno);EXCEPTIONWHENNO_DATA_FOUNDTHENRAISE_APPLICATION_ERROR(-20003,Department not found: ||:NEW.dname);END;/测试插入INSERTINTOemp_dept_view(empno,ename,dname)VALUES(8001,ALICE,SALES);6. 用户事件触发器审计登录-- 记录用户登录信息需在 sys 下创建 CREATE TABLE login_audit ( username VARCHAR2(30), logon_time DATE, ip_address VARCHAR2(40) ); CREATE OR REPLACE TRIGGER trg_logon_audit AFTER LOGON ON DATABASE BEGIN INSERT INTO login_audit (username, logon_time, ip_address) VALUES (USER, SYSDATE, SYS_CONTEXT(USERENV, IP_ADDRESS)); END; /此触发器需 DBA 权限通常用于安全审计。7. 删除触发器DROPTRIGGERtrg_emp_sal_check;五、程序包Package1. 什么是程序包规范Specification声明公共接口过程、函数、变量主体Body实现细节支持封装、重载、全局变量、初始化块2. 程序包的优势模块化设计提高性能一次性加载支持函数/过程重载隐藏实现细节3. 创建程序包规范-- 包规范定义公共接口 CREATE OR REPLACE PACKAGE emp_pkg IS -- 公共常量 c_max_salary CONSTANT NUMBER : 10000; -- 过程声明 PROCEDURE hire_employee ( p_empno IN emp.empno%TYPE, p_ename IN emp.ename%TYPE, p_job IN emp.job%TYPE, p_mgr IN emp.mgr%TYPE DEFAULT NULL, p_hiredate IN DATE DEFAULT SYSDATE, p_sal IN emp.sal%TYPE, p_comm IN emp.comm%TYPE DEFAULT NULL, p_deptno IN emp.deptno%TYPE ); -- 函数声明 FUNCTION get_dept_name(p_deptno IN dept.deptno%TYPE) RETURN VARCHAR2; -- 重载函数根据员工编号或姓名查工资 FUNCTION get_salary(p_empno IN emp.empno%TYPE) RETURN NUMBER; FUNCTION get_salary(p_ename IN emp.ename%TYPE) RETURN NUMBER; END emp_pkg; /4. 创建程序包主体CREATE OR REPLACE PACKAGE BODY emp_pkg IS -- 实现 hire_employee PROCEDURE hire_employee ( p_empno IN emp.empno%TYPE, p_ename IN emp.ename%TYPE, p_job IN emp.job%TYPE, p_mgr IN emp.mgr%TYPE DEFAULT NULL, p_hiredate IN DATE DEFAULT SYSDATE, p_sal IN emp.sal%TYPE, p_comm IN emp.comm%TYPE DEFAULT NULL, p_deptno IN emp.deptno%TYPE ) IS BEGIN IF p_sal c_max_salary THEN RAISE_APPLICATION_ERROR(-20004, Salary exceeds maximum allowed.); END IF; INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (p_empno, p_ename, p_job, p_mgr, p_hiredate, p_sal, p_comm, p_deptno); COMMIT; DBMS_OUTPUT.PUT_LINE(Employee || p_ename || hired successfully.); END hire_employee; -- 实现 get_dept_name FUNCTION get_dept_name(p_deptno IN dept.deptno%TYPE) RETURN VARCHAR2 IS v_dname dept.dname%TYPE; BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN Unknown Dept; END get_dept_name; -- 重载函数1按编号查工资 FUNCTION get_salary(p_empno IN emp.empno%TYPE) RETURN NUMBER IS v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno p_empno; RETURN v_sal; END get_salary; -- 重载函数2按姓名查工资 FUNCTION get_salary(p_ename IN emp.ename%TYPE) RETURN NUMBER IS v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE ename UPPER(p_ename); RETURN v_sal; END get_salary; -- 初始化块可选 BEGIN DBMS_OUTPUT.PUT_LINE(emp_pkg initialized at || SYSDATE); END emp_pkg; /5. 调用程序包中的成员-- 调用过程 BEGIN emp_pkg.hire_employee( p_empno 8002, p_ename BOB, p_job ANALYST, p_sal 4000, p_deptno 20 ); END; / -- 调用函数 SELECT emp_pkg.get_dept_name(20) FROM dual; DECLARE v_sal NUMBER; BEGIN v_sal : emp_pkg.get_salary(7369); -- 按编号 DBMS_OUTPUT.PUT_LINE(Salary by ID: || v_sal); v_sal : emp_pkg.get_salary(KING); -- 按姓名重载 DBMS_OUTPUT.PUT_LINE(Salary by Name: || v_sal); END; /6. 删除程序包-- 先删主体再删规范或直接删规范DROPPACKAGE emp_pkg;删除规范会自动删除主体。六、综合性实战案例案例员工管理系统含过程、函数、触发器、包需求创建包hr_mgmt管理员工提供入职、加薪、查询功能自动记录工资变更日志禁止非法操作如工资为负步骤1创建日志表CREATETABLEsalary_change_log(log_id NUMBER GENERATED ALWAYSASIDENTITY,empno NUMBER(4),old_sal NUMBER(7,2),new_sal NUMBER(7,2),changed_by VARCHAR2(30)DEFAULTUSER,change_tsDATEDEFAULTSYSDATE);步骤2创建触发器自动记录工资变更CREATE OR REPLACE TRIGGER trg_sal_change_log AFTER UPDATE OF sal ON emp FOR EACH ROW BEGIN IF :OLD.sal ! :NEW.sal THEN INSERT INTO salary_change_log (empno, old_sal, new_sal) VALUES (:OLD.empno, :OLD.sal, :NEW.sal); END IF; END; /步骤3创建程序包规范CREATE OR REPLACE PACKAGE hr_mgmt IS PROCEDURE add_employee( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_deptno NUMBER ); PROCEDURE give_raise(p_empno NUMBER, p_amount NUMBER); FUNCTION get_employee_info(p_empno NUMBER) RETURN VARCHAR2; END hr_mgmt; /步骤4创建程序包主体CREATE OR REPLACE PACKAGE BODY hr_mgmt IS PROCEDURE add_employee( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_deptno NUMBER ) IS BEGIN IF p_sal 0 THEN RAISE_APPLICATION_ERROR(-20005, Salary must be positive.); END IF; INSERT INTO emp (empno, ename, job, sal, deptno, hiredate) VALUES (p_empno, UPPER(p_ename), UPPER(p_job), p_sal, p_deptno, SYSDATE); COMMIT; END; PROCEDURE give_raise(p_empno NUMBER, p_amount NUMBER) IS v_current_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_current_sal FROM emp WHERE empno p_empno; IF v_current_sal p_amount 20000 THEN RAISE_APPLICATION_ERROR(-20006, New salary exceeds limit.); END IF; UPDATE emp SET sal sal p_amount WHERE empno p_empno; COMMIT; END; FUNCTION get_employee_info(p_empno NUMBER) RETURN VARCHAR2 IS v_info VARCHAR2(200); BEGIN SELECT ename || ( || job || ) - $ || sal INTO v_info FROM emp WHERE empno p_empno; RETURN v_info; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN Employee not found; END; END hr_mgmt; /步骤5测试-- 添加员工 EXEC hr_mgmt.add_employee(8003, Carol, Manager, 6000, 10); -- 加薪 EXEC hr_mgmt.give_raise(8003, 500); -- 查询 DECLARE v_info VARCHAR2(200); BEGIN v_info : hr_mgmt.get_employee_info(8003); DBMS_OUTPUT.PUT_LINE(v_info); END; / -- 查看日志 SELECT * FROM salary_change_log;七、总结组件特点使用场景存储过程无返回值可执行 DML业务操作如批量处理函数有返回值可在 SQL 中调用计算、转换触发器自动执行审计、约束、日志程序包封装多个过程/函数模块化、重用、重载最佳实践优先使用包组织代码触发器逻辑尽量简单函数避免 DML除非自治事务所有 DML 操作考虑异常处理和事务控制掌握本章内容即可构建健壮、可维护的 Oracle 数据库应用逻辑。