第9章PLSQL高级应用

8 FROM scott.emp WHERE deptno=p_deptno;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11
RAISE_APPLICATION_ERROR(-20000,'该部门编号不存在');
12 END;
13 /
过程已创建。
9.1.2 参数模式
SQL> --定义一个PL/SQL块调用存储过程 SQL> DECLARE 2 v_num NUMBER; 3 v_avgsal NUMBER; 4 BEGIN 5 proc_out(10,v_num,v_avgsal); 6 DBMS_OUTPUT.put_line(‘10号部门的总人数为’||v_num||‘,平均工资为||v_avgsal); 7 END; 8/ 10号部门的总人数为3,平均工资为2916.67 PL/SQL 过程已成功完成。
9.1.1 存储过程的创建和调用
存储过程创建后，会以编译的形式存储于数据库服务器端，需要用户 通过命令显示的调用才会执行。
在PL/SQL程序中，存储过程可以作为一个独立的表达式直接通过名 称调用，在SQL*Plus中调用存储过程的语法如下：
EXECUTE|EXEC|CALL 存储过程名称(实参...);
9.1.2 参数模式
在存储过程定义中的参数有3种参数模式：IN、OUT、IN OUT。 IN：默认的参数模式，表示数值传递，可以是常量或表达式，在子程
序中所做的修改不会影响原始参数值； OUT：不带任何数值到子程序中，初始值为NULL，只能是变量，不
能是常量或表达式，子程序可以通过此变量将数值返回给调用处； IN OUT：将值传递到子程序中，同时将子程序中对变量的修改返回
作用 为任意用户创建存储过程的权限 为用户创建存储过程的权限 修改拥有的存储过程权限 执行任意存储过程的权限 执行用户存储过程的权限 删除任意存储过程的权限
9.1.1 存储过程的创建和调用
示例：创建一个名为“qst”的用户，并授予其创建存储过程的权限
SQL> CONN SYSTEM/QSTqst2015; 已连接。 SQL> CREATE USER qst IDENTIFIED BY qst123; 用户已创建。 SQL> GRANT CREATE PROCEDURE TO qst; 授权成功。 SQL> CONN qst/qst123; 已连接。 SQL> CREATE OR REPLACE PROCEDURE proc_demo 2 IS 3 BEGIN 4 dbms_output.put_line('欢迎你 '||USER); 5 dbms_output.put_line('现在是：'||TO_CHAR(sysdate,'YYYY-mm-DD hh:MM:ss')); 6 END; 7/ 过程已创建。
9.1.1 存储过程的创建和调用
存储过程创建的基本语法
CREATE [OR REPLACE] PROCEDURE 过程名称[( 参数名称 [参数模式] 数据类型 [DEFAULT|:=value],...)]
AS|IS 声明部分;
BEGIN 程序部分;
EXCEPTION 异常处理;
END;
CREATE OR REPLACE表示创建或替换存储过程，如果存储过程存在则 替换，否则创建一个新的；
10 WHEN NO_DATA_FOUND THEN
11
DBMS_OUTPUT.PUT_LINE('雇员编号未找到');
12 END;
13 /
过程已创建。
9.1.2 参数模式
SQL> --定义一个PL/SQL块调用存储过程 SQL> BEGIN 2 proc_in(7369); 3 END; 4/ 雇员的姓名是：SMITH 工资是：800 PL/SQL 过程已成功完成。
9.1.1 存储过程的创建和调用
示例：在PL/SQL中直接通过存储过程名称对存储过程的调用
SQL> CREATE OR REPLACE PROCEDURE proc_test(p_str1 VARCHAR2,p_str2 VARCHAR2) 2 AS 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(p_str1||p_str2); 5 END; 6/ 过程已创建。 SQL> --定义一个PL/SQL块调用存储过程 SQL> DECLARE 2 v_var1 VARCHAR2(20):='Hello,'; 3 v_var2 VARCHAR2(20):='Prodcedure!'; 4 BEGIN 5 proc_test(v_var1,v_var2); 6 END; 7/ Hello,Prodcedure! PL/SQL 过程已成功完成。
参数模式表示存储过程的数据接收操作，包括IN、OUT、IN OUT三种， 默认为IN。
9.1.1 存储过程的创建和调用
用户创建及操作存储过程时，需要具有此数据库对象的相关操作权限
权限名称 CREATE ANY PROCEDURE CREATE PROCEDURE ALTER PROCEDURE EXECUTE ANY PROCEDURE EXECUTE PROCEDURE DROP ANY PROCEDURE
9.1.2 参数模式
示例： IN OUT参数模式
SQL> CREATE OR REPLACE PROCEDURE proc_dept_dname_exist(
2 p_io_value IN OUT VARCHAR2)
3 IS
4 v_count NUMBER;
5 BEGIN
6 SELECT COUNT(*) INTO v_count FROM scott.dept WHERE dname=p_io_value;
第9章 PLSQL高级应用
目录
01 存储过程的创建与管理 02 函数的创建与管理 03 包的创建与管理 04 触发器 05 系统触发器
本章重点
掌握存储过程的创建、调用及管理 掌握函数的创建、调用及管理 掌握包的创建、调用及管理 理解触发器的作用及分类 掌握触发器的创建、管理及应用
任务驱动
通过存储过程名称调用存储过程时，实参的数量、顺序、类型要与形 参的数量、顺序、类型相匹配。
9.1.1 存储过程的创建和调用
示例：在SQL*Plus中调用前面所创建的存储过程“proc_demo”。
SQL> SET SERVEROUTPUT ON; SQL> EXEC proc_demo; 欢迎你 QST 现在是：2016-04-06 03:04:59 PL/SQL 过程已成功完成。
9.1 存储过程
面向对象的分析（OOA）：确定需求或者业务的角度，按照面向对 象的思想来分析业务。
不可移植性：每种数据库的内部编程语法都不太相同，当系统需要兼容 多种数据库时，最好不要用存储过程或函数；
业务逻辑多处存在：采用存储过程或函数就意味着系统有一些业务逻辑 不是在应用程序里处理，这种架构会增加一些系统维护和调试成本；
到调用处，参数只能是变量，不能是常量或表达式。
9.1.2 参数模式
示例：IN参数模式。
SQL> CREATE OR REPLACE PROCEDURE proc_in(p_empno IN NUMBER)
2 AS
3 v_ename scott.emp.ename%TYPE;
4 v_sal scott.emp.sal%TYPE;
user_procedures：查看当前用户所有的存储过程、函数信息； user_source：查看当前用户所有对象的源代码； user_errors：查看当前所有的存储过程或函数的错误信息。
9.1.3 存储过程的管理
示例：查看当前用户的子程序信息。
对于复杂的业务逻辑需要多条SQL语句，放在一个存储过程或函数中， 客户机和服务器之间的网络传输会大大减少，从而降低网络负载；
安全性高，存储过程和函数可以屏蔽对底层数据库对象的直接访问，需 要具有相应的权限才能进行调用，无需拥有访问底层数据库对象的显式 权限；
存储过程和函数创建一次便可以重复使用，从而可以减少数据库开发人 员的工作量。
5 BEGIN
6 SELECT ename,sal INTO v_ename,v_sal FROM scott.emp
7
WHERE empno=p_empno;
8 DBMS_OUTPUT.PUT_LINE('雇员的姓名是：'||v_ename||' 工资是：'||v_sal);
9 EXCEPTION
Java
JSP
1
存储过程的创建与管理
9.1 存储过程
在PL/SQL程序中除匿名块外，还有一类被命名的PL/SQL程序块，称 为存储子程序。
存储子程序以编译的形式存储在数据库服务器中，可以在应用程序中 进行多次调用，是PL/SQL程序模块化的一种体现。
PL/SQL存储子程序包括存储过程和（存储）函数两种。
9.1.3 存储过程的管理
修改存储过程 对于存储过程的修改，可以先删除该存储过程，然后重新创建，这样
需要为新创建的存储过程重新进行权限分配，因此通常采用CREATE OR REPLACE PROCEDURE方式重新创建并覆盖原有的存储过程， 这样会保留存储过程原有的权限分配。 查询存储过程及其源码 可以通过以下数据字典查看存储过程及其源码信息：
7 IF(v_count>0) THEN
8
p_io_value:='已存在';
9 ELSE
10
p_io_value:='不存在';
11 END IF;
12 END;
13 /
过程已创建。
9.1.2 参数模式
示例： I/SQL块调用存储过程 SQL> DECLARE 2 v_io_value VARCHAR2(20):='ACCOUNTING'; 3 BEGIN 4 proc_dept_dname_exist(v_io_value); 5 DBMS_OUTPUT.PUT_LINE('部门名称ACCOUNTING'||v_io_value||'!'); 6 END; 7/ 部门名称ACCOUNTING已存在! PL/SQL 过程已成功完成。
本章任务完成“Q_MicroChat”微聊项目通过子程序和触发器进行的 业务处理。具体任务分解如下：
【任务9-1】使用子程序进行业务处理 【任务9-2】使用触发器进行业务处理
本章目标
存储过程 函数 包 DML触发器 替代触发器 系统触发器 触发器的管理
知识点
听 看抄改 写
★ ★★★ ★ ★ ★★★ ★ ★ ★★ ★ ★★★ ★ ★ ★★★ ★ ★ ★★ ★ ★★★ ★
9.1.2 参数模式
示例： OUT参数模式
SQL> CREATE OR REPLACE PROCEDURE proc_out(
2 p_deptno NUMBER,
3 p_num OUT NUMBER,
4 p_avgsal OUT NUMBER)
5 AS
6 BEGIN
7 SELECT COUNT(*) num ,ROUND(AVG(sal),2) avgsal INTO p_num,p_avgsal
可扩展性低：如果存储过程或函数中存在复杂运算，则会增加一些数据 库服务端的处理成本，对于集中式数据库可能会导致系统可扩展性问题；
9.1 存储过程
引用对象的结构变更对高并发数据库性能影响较大。为了提高性能，数 据库会把存储过程或函数代码编译成中间运行代码（类似于Java的class 文件），所以其更像静态语言，当存储过程或函数引用的对象（如表、 视图等）结构改变后，存储过程或函数需要重新编译才能生效，对于一 些高并发应用场景，在线变更结构的瞬间同时编译存储过程或函数，可 能会导致数据库瞬间压力上升而引起故障。
存储过程用于执行特定的操作，不需要返回值； 函数用于返回特定的数据。 在调用时，存储过程可以作为一个独立的表达式被调用，而函数只能作
为表达式的一个组成部分被调用。
9.1 存储过程
存储过程和函数具有以下优点：
存储过程和函数只在创建时进行编译，以后每次执行时都不需要再重新 编译，而一般SQL语句每执行一次就会编译一次，因此使用存储过程和 函数可提高数据库执行速度；