《Oracle PLSQL 编程精髓》课件

《OraclePLSQL编程精髓》PLSQL是一种面向对象的编程语言，基于SQL语言，用于扩展Oracle数据库的功能。它允许您创建自定义函数、过程、触发器、包和存储过程，以增强数据库的性能和功能。PLSQL入门概述PLSQL简介PLSQL是一种专为Oracle数据库设计的编程语言，它允许您创建自定义函数、过程、触发器和包，以扩展数据库的功能。PLSQL结合了SQL的数据操作能力和过程式编程语言的灵活性和功能，使您能够创建复杂而强大的数据库应用程序。PLSQL的优势PLSQL为您提供以下关键优势：-**扩展数据库功能：**PLSQL允许您创建自定义逻辑，以满足您特定的应用程序需求。-**提高性能：**PLSQL代码存储在数据库服务器中，因此执行效率比在客户端执行SQL语句要高。-**安全性：**PLSQL允许您控制数据访问，并保护敏感信息。-**代码复用性：**PLSQL支持函数、过程和包，使您能够创建可重复使用的代码块。PLSQL开发环境OracleSQLDeveloperOracleSQLDeveloper是一个免费的集成开发环境（IDE），专为Oracle数据库设计。它提供一个用户友好的界面，用于编写、调试和执行PLSQL代码。其他工具除了OracleSQLDeveloper之外，您还可以使用其他工具来开发PLSQL代码，例如：-**ToadforOracle：**一个功能强大的IDE，提供广泛的PLSQL开发功能。-**SQL*Plus：**一个命令行工具，用于连接Oracle数据库并执行SQL和PLSQL语句。基本数据类型NUMBER用于存储数字，包括整数和小数。例如：123,3.14,-10VARCHAR2用于存储可变长度的字符串。例如：'HelloWorld','Oracle'DATE用于存储日期和时间。例如：'2024-03-08','12:00:00'BLOB用于存储二进制大对象，例如图像或音频文件。CLOB用于存储字符大对象，例如文本文件或HTML代码。变量声明与赋值变量声明DECLAREv_nameVARCHAR2(20);v_ageNUMBER(3);BEGIN--代码块END;/变量赋值DECLAREv_nameVARCHAR2(20):='张三';v_ageNUMBER(3):=25;BEGIN--代码块END;/基本操作符算术运算符+加法-减法*乘法/除法**幂运算比较运算符=等于!=不等于>大于<小于>=大于等于<=小于等于逻辑运算符AND逻辑与OR逻辑或NOT逻辑非控制流语句条件语句IF-THEN-ELSE语句用于根据条件执行不同的代码块。循环语句LOOP、WHILE和FOR循环语句用于重复执行代码块。条件语句DECLAREv_ageNUMBER(3):=25;BEGINIFv_age>=18THENDBMS_OUTPUT.PUT_LINE('成年');ELSEDBMS_OUTPUT.PUT_LINE('未成年');ENDIF;END;/循环语句LOOPLOOP--代码块EXITWHEN条件;ENDLOOP;WHILEWHILE条件LOOP--代码块ENDLOOP;FORFORiIN1..10LOOP--代码块ENDLOOP;错误处理错误代码PLSQL中的错误由数字代码表示，例如：-**-20001：**用户自定义错误代码错误处理DECLAREv_error_codeNUMBER;BEGIN--代码块EXCEPTIONWHENOTHERSTHENv_error_code:=SQLCODE;DBMS_OUTPUT.PUT_LINE('错误代码：'||v_error_code);END;/异常处理异常类型PLSQL中的异常类型包括：-**NO_DATA_FOUND：**查询没有找到任何数据-**ZERO_DIVIDE：**除以0-**INVALID_NUMBER：**数字转换错误异常处理DECLAREv_ageNUMBER;BEGINSELECTageINTOv_ageFROMemployeesWHEREid=1;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('没有找到员工信息');END;/函数定义CREATEORREPLACEFUNCTIONget_employee_name(p_idNUMBER)RETURNVARCHAR2ISv_nameVARCHAR2(20);BEGINSELECTnameINTOv_nameFROMemployeesWHEREid=p_id;RETURNv_name;END;/函数参数CREATEORREPLACEFUNCTIONget_employee_name(p_idNUMBER,p_deptVARCHAR2)RETURNVARCHAR2ISv_nameVARCHAR2(20);BEGINSELECTnameINTOv_nameFROMemployeesWHEREid=p_idANDdept=p_dept;RETURNv_name;END;/函数返回值DECLAREv_employee_nameVARCHAR2(20);BEGINv_employee_name:=get_employee_name(1);DBMS_OUTPUT.PUT_LINE(v_employee_name);END;/过程定义CREATEORREPLACEPROCEDUREupdate_employee_age(p_idNUMBER,p_ageNUMBER)ISBEGINUPDATEemployeesSETage=p_ageWHEREid=p_id;END;/过程参数CREATEORREPLACEPROCEDUREupdate_employee_age(p_idNUMBER,p_ageNUMBER,p_deptVARCHAR2)ISBEGINUPDATEemployeesSETage=p_ageWHEREid=p_idANDdept=p_dept;END;/包定义包规范包规范定义包中公开的元素，例如函数、过程和类型。包主体包主体实现包规范中定义的元素。包规范CREATEORREPLACEPACKAGEemployee_pkgISFUNCTIONget_employee_name(p_idNUMBER)RETURNVARCHAR2;PROCEDUREupdate_employee_age(p_idNUMBER,p_ageNUMBER);ENDemployee_pkg;/包主体CREATEORREPLACEPACKAGEBODYemployee_pkgISFUNCTIONget_employee_name(p_idNUMBER)RETURNVARCHAR2ISv_nameVARCHAR2(20);BEGINSELECTnameINTOv_nameFROMemployeesWHEREid=p_id;RETURNv_name;END;PROCEDUREupdate_employee_age(p_idNUMBER,p_ageNUMBER)ISBEGINUPDATEemployeesSETage=p_ageWHEREid=p_id;END;ENDemployee_pkg;/游标基本使用DECLARECURSORemp_cursorISSELECT*FROMemployees;v_emp_recordemp_cursor%ROWTYPE;BEGINOPENemp_cursor;LOOPFETCHemp_cursorINTOv_emp_record;EXITWHENemp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp_);ENDLOOP;CLOSEemp_cursor;END;/游标属性emp_cursor%ROWTYPE用于存储游标结果集中的记录。emp_cursor%FOUND返回布尔值，指示上一次FETCH是否成功。emp_cursor%NOTFOUND返回布尔值，指示上一次FETCH是否失败。emp_cursor%ROWCOUNT返回自游标打开以来FETCH的记录数。游标参数CREATEORREPLACEPROCEDUREget_employees__dept(p_deptVARCHAR2)ISCURSORemp_cursorISSELECT*FROMemployeesWHEREdept=p_dept;v_emp_recordemp_cursor%ROWTYPE;BEGINOPENemp_cursor;LOOPFETCHemp_cursorINTOv_emp_record;EXITWHENemp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp_);ENDLOOP;CLOSEemp_cursor;END;/显式游标显式游标由程序员显式定义和管理。它们提供对游标操作的完全控制，例如打开、FETCH和关闭。隐式游标隐式游标由Oracle数据库自动创建和管理。它们用于执行DML语句，例如INSERT、UPDATE和DELETE。动态SQLDECLAREv_sql_stmtVARCHAR2(200);v_emp_idNUMBER:=1;v_emp_nameVARCHAR2(20);BEGINv_sql_stmt:='SELECTnameFROMemployeesWHEREid='||v_emp_id;EXECUTEIMMEDIATEv_sql_stmtINTOv_emp_name;DBMS_OUTPUT.PUT_LINE(v_emp_name);END;/事务处理事务定义事务是一组逻辑上相关的SQL语句，作为一个整体执行，要么全部成功，要么全部失败。事务属性事务具有以下四个属性（ACID）：-**原子性（Atomicity）：**事务作为一个整体执行，要么全部成功，要么全部失败。-**一致性（Consistency）：**事务执行后，数据库状态必须保持一致性。-**隔离性（Isolation）：**多个事务并发执行时，彼此之间相互隔离。-**持久性（Durability）：**事务一旦成功提交，其更改将永久保存在数据库中。事务语句COMMIT提交事务，使更改永久保存在数据库中。ROLLBACK回滚事务，撤销所有未提交的更改。SAVEPOINT设置一个保存点，允许回滚到该点，而不回滚到事务的开始。事务属性隔离级别隔离级别控制事务之间可见性的程度。读一致性确保事务读取的记录是始终一致的，不受其他事务的影响。锁机制锁类型Oracle数据库支持以下锁类型：-**行锁：**锁定单个数据行。-**表锁：**锁定整个数据表。-**共享锁（S锁）：**多个事务可以获取共享锁，但不能修改数据。-**排他锁（X锁）：**只有一个事务可以获取排他锁，并可以修改数据。锁机制锁机制用于防止多个事务同时修改同一数据，从而确保数据的一致性。触发器定义CREATEORREPLACETRIGGERemp_update_triggerBEFOREUPDATEOFageONemployeesFOREACHROWBEGIN--触发器代码END;/触发器类型BEFORE触发器在数据修改之前触发，例如INSERT、UPDATE或DELETE。AFTER触发器在数据修改之后触发。INSTEADOF触发器用于替换视图上的DML操作。触发时间BEFOREINSERT在INSERT操作之前触发。AFTERUPDATE在UPDATE操作之后触发。INSTEADOFDELETE在DELETE操作之前触发，用于视图。触发语句CREATEORREPLACETRIGGERemp_update_triggerBEFOREUPDATEOFageONemployeesFOREACHROWBEGINIF:NEW.age<18THENRAISE_APPLICATION_ERROR(-20001,'年龄必须大于18岁');ENDIF;END;/序列使用CREATESEQUENCEemp_seqSTARTWITH1INCREMENTBY1MAXVALUE9999999999NOCACHE;--获取序列的下一个值SELECTemp_seq.NEXTVALFROMdual;索引定义CREATEINDEXemp_name_idxONemployees(name);索引类型B树索引最常用的索引类型，用于快速查找数据。位图索引用于快速查找大量数据，但只适用于少量不同值。函数索引基于函