ORACLE_PlSql-甲骨文学习笔记

目录 1 一、创建表 2 创建表一 2 创建表二 2 创建表三 3 二、添加、修改、删除 3 添加 3 修改 4 删除 4 三、ORACLE 10g新增的数据类型 4 说明 4 示例 4 特殊值 4 四、ORACLE PL/SQL简介 5 1 块结构 5 2 变量和类型 6 3 条件逻辑 6 4 循环 7 5 游标 8 №1声明一些变量，用于保存select语句的返回的列值 8 №2声明游标，并指定select语句 8 №3打开游标 8 №4从游标中获取记录 9 №5关闭游标 9 №6完整的示例 9 №7游标与for循环 10 6 异常 10 7 存储过程 11 №1创建过程 11 №2调用过程 12 №3获取过程信息 12 №4删除过程 12 №5查看过程中的错误 13 №6实际应用示例（积分清零） 13 8 函数 14 №1创建函数 14 №2调用函数 15 №3获取函数信息 15 №4删除函数 15 №5实际应用示例（查找表） 16 9 包 19 №1创建包和规范 19 №2创建包体 19 №3调用包中的过程和函数 20 №4获取有关包中的函数和过程的信息 21 №5删除包 21 10触发器 21 №1触发器运行的时机 21 №2设置示例触发器的准备工作 22 №3创建触发器 22 №4激活触发器 23 №5获取有关触发器的信息 23 №6禁用和启用触发器 23 №7删除触发器 24 №8实际应用示例（增加修改积分） 24 ### ORACLE_PlSql-甲骨文学习笔记 #### 一、创建表 ##### 创建表一 ```sql CREATE TABLE TABLE_NAME ( AAA INTEGER CONSTRAINT PK_TABLE_NAME PRIMARY KEY, BBB VARCHAR2(10) NOT NULL, DOB DATE, CCC VARCHAR2(14) ); ``` **说明**: 在此表定义中，`CONSTRAINT PK_TABLE_NAME` 表示了一个名为 `PK_TABLE_NAME` 的主键约束。主键 `PRIMARY KEY` 确保了每一条记录的 `AAA` 字段都必须包含一个唯一的整数值。`NOT NULL` 约束则确保了 `BBB` 字段不能为 `NULL`。 ##### 创建表二 ```sql CREATE TABLE TABLE_NAME ( AAA INTEGER CONSTRAINT PK_TABLE_NAME PRIMARY KEY, BBB INTEGER CONSTRAINT FK_TABLE_NAME FOREIGN KEY REFERENCES TABLE_NAME1 (DDD), BBB VARCHAR2(10) NOT NULL, DOB DATE, CCC VARCHAR2(14) ); ``` **说明**: 在这张表中，`BBB` 字段定义了一个外键 `FOREIGN KEY`，它引用了另一个表 `TABLE_NAME1` 中的主键字段 `DDD`。这种引用关系构成了两个表之间的父子关系，其中 `TABLE_NAME` 是子表，而 `TABLE_NAME1` 是父表。 ##### 创建表三 ```sql CREATE TABLE TABLE_NAME ( AAA INTEGER CONSTRAINT FK_TABLE_NAME1 REFERENCES TABLE_NAME1 (AAA), BBB INTEGER CONSTRAINT FK_TABLE_NAME2 REFERENCES TABLE_NAME1 (BBB), CCC VARCHAR2(10) NOT NULL, DOB DATE, CCC VARCHAR2(14), CONSTRAINT PK_TABLE_NAME PRIMARY KEY (AAA, BBB) ); ``` **说明**: 此表定义了两个外键，分别指向 `TABLE_NAME1` 中的 `AAA` 和 `BBB` 主键字段。此外，还定义了一个复合主键 `PRIMARY KEY (AAA, BBB)`，这意味着 `(AAA, BBB)` 这两列的组合必须在整个表中是唯一的。 #### 二、添加、修改、删除 ##### 添加 插入数据到表中通常使用 `INSERT INTO` 语句。 ```sql INSERT INTO TABLE_NAME (AAA, BBB, CCC, DDD) VALUES (1, 'FIRED', '06-JAN-1990', '800-555-1215'); ``` 或者更复杂的例子： ```sql INSERT INTO gajt_drv_check_34 (SFZMHM, DABH, XM, ZJCX, YZJCX, CCLZRQ, FZRQ, CCFZJG, FZJG, ZT, JBR) VALUES ('342421196608107510', '340100216391', 'tom', 'C1E', 'EC', TO_DATE('2002-10-18', 'yyyy-mm-dd'), TO_DATE('2005-01-07 14:35:43', 'yyyy-mm-dd hh24-mi-ss'), 'AA', 'AA', 'A', '#'); ``` ##### 修改 更新表中的数据使用 `UPDATE` 语句。 ```sql UPDATE TABLE_NAME SET AAA = 'XXX' WHERE BBB = 'YYY'; UPDATE TABLE_NAME SET AAA = 'XX1', BBB = 'ZZZ' WHERE BBB = 'YY1'; ``` ##### 删除 删除表中的记录使用 `DELETE FROM` 语句。 ```sql DELETE FROM TABLE_NAME WHERE BBB = 'YYY'; ``` #### 三、ORACLE 10g新增的数据类型 ##### 说明 Oracle 10g 引入了一些新的数据类型来提高性能和简化开发工作。 ##### 示例 例如 `TIMESTAMP WITH TIME ZONE`，这是一种用于存储带有时区信息的时间戳的数据类型。 ##### 特殊值 这些新类型支持特殊的值处理，如 `NULL` 或 `INFINITY`。 #### 四、ORACLE PL/SQL简介 ##### 1 块结构 PL/SQL 的基本单位是块，它由三个部分组成：声明部分 (`DECLARE`), 执行部分 (`BEGIN`) 和异常处理部分 (`EXCEPTION`). ```plsql DECLARE -- 声明部分 BEGIN -- 执行部分 EXCEPTION -- 异常处理部分 END; ``` ##### 2 变量和类型 在 PL/SQL 中，可以声明各种类型的变量，如 `NUMBER`, `VARCHAR2`, `DATE` 等。 ```plsql DECLARE v_name VARCHAR2(50); v_age NUMBER; BEGIN -- 使用变量 END; ``` ##### 3 条件逻辑 使用 `IF-THEN-ELSE` 语句进行条件判断。 ```plsql DECLARE v_age NUMBER := 25; BEGIN IF v_age > 18 THEN DBMS_OUTPUT.PUT_LINE('成年人'); ELSE DBMS_OUTPUT.PUT_LINE('未成年人'); END IF; END; ``` ##### 4 循环 循环结构用于重复执行一系列操作。 ```plsql DECLARE v_count NUMBER := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE(v_count); v_count := v_count + 1; EXIT WHEN v_count > 10; END LOOP; END; ``` ##### 5 游标 游标允许在 PL/SQL 中遍历查询结果集。 ```plsql DECLARE CURSOR c_employee IS SELECT * FROM employees; v_employee employees%ROWTYPE; BEGIN OPEN c_employee; LOOP FETCH c_employee INTO v_employee; EXIT WHEN c_employee%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_employee.employee_id || ' - ' || v_employee.first_name); END LOOP; CLOSE c_employee; END; ``` ##### 6 异常 PL/SQL 提供了异常处理机制来处理运行时错误。 ```plsql DECLARE v_age NUMBER := 25; BEGIN IF v_age < 0 THEN RAISE_APPLICATION_ERROR(-20001, '年龄不能为负数'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生错误: ' || SQLERRM); END; ``` ##### 7 存储过程 存储过程是在数据库中存储的一组预编译的 SQL 语句和 PL/SQL 块。 ```plsql CREATE OR REPLACE PROCEDURE update_salary(p_employee_id NUMBER, p_new_salary NUMBER) IS BEGIN UPDATE employees SET salary = p_new_salary WHERE employee_id = p_employee_id; COMMIT; END; ``` ##### 8 函数 函数类似于存储过程，但它们返回一个值。 ```plsql CREATE OR REPLACE FUNCTION get_salary(p_employee_id NUMBER) RETURN NUMBER IS BEGIN RETURN (SELECT salary FROM employees WHERE employee_id = p_employee_id); END; ``` ##### 9 包 包是 PL/SQL 中一种重要的封装机制，用于组织和管理相关的程序单元。 ```plsql -- 包头 CREATE OR REPLACE PACKAGE my_package AS FUNCTION get_salary(p_employee_id NUMBER) RETURN NUMBER; END my_package; -- 包体 CREATE OR REPLACE PACKAGE BODY my_package AS FUNCTION get_salary(p_employee_id NUMBER) RETURN NUMBER IS BEGIN RETURN (SELECT salary FROM employees WHERE employee_id = p_employee_id); END; END my_package; ``` ##### 10 触发器 触发器是一种特殊类型的存储过程，当特定事件发生时自动执行。 ```plsql CREATE OR REPLACE TRIGGER update_salary_trigger BEFORE INSERT ON employees FOR EACH ROW BEGIN IF :NEW.salary > 50000 THEN :NEW.salary := 50000; END IF; END; ``` 以上内容涵盖了 Oracle PL/SQL 的基础知识和技术要点，可以帮助初学者快速入门并掌握如何使用 PL/SQL 进行数据库编程。