关于ORACLE PL笔记

【Oracle PL/SQ 入门】 Oracle PL/SQ 是 Oracle 数据库的一种过程化语言，它结合了 SQL 的数据查询功能和高级编程语言的特点，用于处理数据库中的复杂业务逻辑。本笔记主要涵盖四个核心概念：存储过程、函数、触发器和包。 **一、存储过程** 1. **命令格式**： 存储过程由 PROCEDURE 关键字定义，包含参数列表（可选），并在 AS 或 IS 后面声明局部变量和执行语句。如： ``` PROCEDURE Name (Parameter[, Parameter,...]) IS | AS [Local Declarations] BEGIN Execute statements; [EXCEPTION Exception Handlers] END [Name]; ``` 2. **调用**： 可通过 EXECUTE 命令直接调用，如 `EXECUTE Proc_Name(par1, par2...)`；或在 PL/SQL 块内调用，如： ```sql DECLARE par1, par2; BEGIN Proc_Name(par1, par2...); END; ``` 3. **释放**： 使用 `DROP PROCEDURE` 删除存储过程，例如：`DROP PROCEDURE Proc_Name;` 4. **实例**： 创建一个名为 `DeptMesg` 的存储过程，用于获取指定员工所在部门的名称和位置： ```sql CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE, pdname OUT dept.dname%TYPE, ploc OUT dept.loc%TYPE) AS BEGIN SELECT dname, loc INTO pdname, ploc FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.ename = pename; END; ``` 调用过程： ```sql VARIABLE vdname VARCHAR2(14); VARIABLE vloc VARCHAR2(13); EXECUTE DeptMesg('SMITH', :vdname, :vloc); PRINT vdname vloc; ``` **二、函数** 1. **命令格式**： 函数的定义使用 FUNCTION 关键字，包含参数列表和返回类型。如： ``` FUNCTION Name [{Parameter[,Parameter,])}] RETURN DataTypes IS [Local Declarations] BEGIN Execute Statements; [EXCEPTION Exception Handlers] END [Name]; ``` 2. **调用**： 函数可直接在表达式中调用，如 `Man_Num := Count_Num('女')`。 3. **释放**： 使用 `DROP FUNCTION` 删除函数，如：`DROP FUNCTION Count_Num;` 4. **实例**： 创建一个名为 `Hire_Day` 的函数，计算员工的工作天数： ```sql CREATE OR REPLACE FUNCTION Hire_Day(no emp.empno%TYPE) RETURN NUMBER AS vhiredate emp.hiredate%TYPE; vday NUMBER; BEGIN SELECT hiredate INTO vhiredate FROM emp WHERE empno = no; vday := CEIL(SYSDATE - vhiredate); RETURN vday; END; ``` **三、触发器** 1. **创建规则**： - 作用范围明确； - 避免重复 Oracle 内置功能； - 控制代码行数； - 避免递归触发器； - 触发器执行应与用户交互和数据库操作无关。 2. **触发时机**： - DML 语句（DELETE、INSERT、UPDATE）； - DDL 语句（CREATE、ALTER、DROP）； - 数据库事件（SERVERERROR、LOGON、LOGOFF、STARTUP、SHUTDOWN）。 触发器允许在特定数据库操作前后执行自定义逻辑，例如，在插入新记录时自动计算员工工龄。 **四、包** 包（Package）是存储过程和函数的集合，可以包含公共常量、变量和游标。包有助于组织代码、提高复用性和隐藏实现细节。创建包的基本结构包括包头（声明部分）和包体（实现部分）。 包头示例： ```sql CREATE OR REPLACE PACKAGE pkg_name AS -- 常量、类型、变量声明 PROCEDURE proc_name (param_list); FUNCTION func_name (param_list) RETURN return_type; END pkg_name; ``` 包体示例： ```sql CREATE OR REPLACE PACKAGE BODY pkg_name AS -- 实现过程和函数 BEGIN PROCEDURE proc_name (param_list) IS BEGIN -- 实现 END; FUNCTION func_name (param_list) RETURN return_type IS BEGIN -- 实现 RETURN result; END; END pkg_name; ``` 包提供了更好的模块化和封装性，使得代码更易于管理和维护。 总结，Oracle PL/SQ 提供了强大的数据库编程能力，通过存储过程、函数、触发器和包，开发者可以构建复杂的业务逻辑，确保数据的一致性和安全性。理解并熟练掌握这些概念对于 Oracle 数据库管理员和开发人员来说至关重要。