日常运维经验分享 - 合理使用存储过程提高运维效率

背景介绍

大部分运维DBA都会认为，存储过程（Procedure）应该是开发人员需要掌握的知识，应该由开发人员编写和维护。但实际上运维 DBA 在日常工作中还是经常会需要使用和维护存储过程的，因为存储过程可与 SQL 一起在数据库内实现较为复杂的逻辑需求，在实现某些功能是特别有用。如果因为存储过程的设计或存储过程某一模块的编写不合理，将会影响数据库或应用系统的正常使用，这时仍然需要由 DBA 来定位和处理问题。因此，作为 DBA，我们仍然应该掌握存储过程，包括存储过程的创建、修改、删除、调用和优化调整。

设计存储过程是 DBA 在设计数据库结构时需要完成的工作之一。良好的设计可以增加系统性能；反之，会对系统产生负面的影响。

而将日常运维工作的常见操作，通过存储过程来实现，也会大大简化 DBA 维护的工作量，可以提高工作效率。

下面首先简单介绍一下存储过程的基本概念。

存储过程简要介绍

存储过程是编译好且存储在数据库服务器中的程序代码，一个存储过程就是一组命名的 PL/SQL 语句。一旦这些对象以编译过的格式存储在数据库中，用户只要获得相应对象的执行权限就可以使用任意 Oracle 工具来执行这些对象。

1 可以进行模块化设计

在数据库中创建一次存储过程，可以在程序中多次调用，而且存储过程与系统的程序源码无直接联系可以单独进行修改调整。

2 加快执行速度

假如操作大量 PL/SQL 代码或者需要大量的重复执行操作时，存储过程将比 PL/SQL 代码执行速度快。Oracle 数据库引擎将在创建存储过程时对其进行分析和优化，在首次执行该存储过程后使用该过程在内存中的版本。但 PL/SQL 语句块，需要客户端重复发送，并且在 Oracle 每次执行这些语句时，都要进行编译。

3 减少网络传输

存储过程直接就在数据库服务器上跑，所有的数据访问都在服务器内部进行，不需要传输数据到其它终端。

合理的设计与应用存储过程，对系统的运行效率会带来提升，也将大大简化运维工作，下面将列举一些典型通过存储过程简化运维工作的例子。

常用运维存储过程举例 

【场景】某公司 TD2 综合业务平台需要对公司历年的多个业务数据进行统一管理。经过调研分析，决定对多个系统按用户进行数据处理，也就是需要建多个用户，然后按用户进行相应数据处理。

1创建用户的存储过程

【设计说明】Oracle 中的用户拥有数据库中的对象，创建对象必须在用户下进行。当创建一下用户时，为了便于管理维护，可以指定用户的属性。用户的密码，用户默认表空间，临时表空间等。成功创建后，然后给该用户授权。本例是使用存储过程创建用户并将用户的属性在一个表中预定义好。

如果仍然采用原来的维护方法，使用 SQL 脚本创建用户,根据不同的要求使用不同的表空间和临时表空间，具体方法如下:

Create_user.sql:

DEFINE cre_user=xf

DEFINE cre_user_pwd=xf_pwd

DEFINE def_tbsp=hfxf_data_02

DEFINE def_temp_tbsp=xf_temp

CREATE USER&&cre_user IDENTIFIED BY &&cre_user_pwd

DEFAULT TABLESPACE&&def_tbsp

TEMPORARY TABLESPACE&&def_temp_tbsp;

GRANT CREATE SESSION TO&&cre_user;

GRANT CREATE TABLE TO&&cre_user;

以上脚本可以创建用户并授权，但是不易于维护，对于多个用户维护工作量大，容易出错。

所以我开始尝试使用存储过程创建用户，创建存储过程的思路也就是将上面的脚本在存储过程中实现并执行。

创建用户的存储过程如下：

/**

*version:v1.0

*author:huangfuxiaofei

*date:2016/03/22

*Description:create user

*/

createor replace procedure sp_create_xf_user (p1 in varchar2) is

--声明变量

var_create_sql    varchar2(1000);

var_grant_sql    varchar2(1000);

var _start_datedate ;

var_end_datedate ;

var_sql_codenumber ;

var_sql_msgVARCHAR2(4000) := '' ;

var_tabspace  varchar2(40);

var_tmpspace   varchar2(40);

cursorcur is

selectt.schema from xf_users t where t.name='p1';

begin

  SELECT SYSDATE INTO v _start_date FROM dual;

for xfnamein cur –使用游标FOR循环，每循环一次，系统自动读取游标当前行，当退出FOR循环时，游标自动关闭（不需要使用CLOSE）

loop  --循环遍历

begin

if p1is not null  then

  select xf.tsb_name into v_tabspace fromxf_users xf  where xf.schema=UPPER(xfname.schema);

  select zf.tmpspace into v_tmpspace fromxf_users xf  where xf.schema=UPPER(xfname.schema);

  v_create_sql:='CREATE USER ' || xfname.schema|| ' identified by '  || LOWER(xfname.schema)||' default tablespace '|| var_tabspace ||' temporary tablespace '||var_tmpspace||'';--拼装创建用户的SQL语句

     dbms_output.put_line(v_create_sql);--输出创建用户的SQL语句

     SELECT SYSDATE INTO v_start_date FROMdual;--获取开始时间，为了计时

     v_sql_msg := 'step 1: Create User'||xfname.schema||'';--记录sql信息

     execute immediate v_create_sql;--执行sql

     v_grant_sql:='grant connect,resource,dbato '  || xfname.schema ||'';--拼装授权SQL语句

     execute immediate v_grant_sql;--执行授权SQL语句

     SELECT SYSDATE INTO var_end_date FROMdual;    --获取结束时间 SP_INSERT_LOG(xfname.schema,'create_user',var_start_date,var_end_date,'',v_sql_code,'0',v_sql_msg,v_create_sql||';'||v_grant_sql);--记录操作日志

 end if;

EXCEPTION     WHEN OTHERS THEN  --异常处理

     v_sql_code := SQLCODE;--错误编号

     v_sql_msg := v_sql_msg || ' ' ||  ' : ' || SQLERRM;--错误信息

PRO_INSERT_LOG(xfname.schema,'CreateUser',var_start_date,var_tend_date,'',v_sql_code,'0',v_sql_msg,'');

end;

endloop;

COMMIT;

end sp_create_xf_user; 

【设计思路】sp_create_xf_user（abbrevin varchar）存储过程传入一个参数，已经在 xf_users 表中 p_name 字段进行了设置。根据参数在 xf_users 表中查出创建用户的信息，由于一个业务系统有多个用户，所以我们预先在自定义的 xf_users 表中对表空间和临时表空间的名称设置好，然后使用 loop...end loop 循环来进行选择和创建。

XF_USERS 表的创建如下：

CREATETABLE XF_USERS

(

SP_IDVARCHAR2(4) NOT NULL,      --idu序号

P_NAMEVARCHAR2(20) NOT NULL,   --系统名称

SCHEMAVARCHAR2(30) ,            -预创建的用户名

TSB_NMAEVARCHAR2(30),           --表空间名称

TMPSPACEVARCHAR2(30),           -临时表空间名称

REMARKVARCHAR2(60)               -备注

)TABLESPACE HFXF_DATA;

创建表并进行数据库初始化。

执行存储过程时报如下错误

PLS_00201:identifier SP_INSERT_LOG’ must be declared

可以使用 show errors 查看具体的错误信息。

该错误是因为 SP_INSERT_LOG 存储过程我们没有定义，我们在创建用户的存储过程中调用了生产日志的存储过程，该存储过程中将在下面展示。创建该存储过程后， sp_create_xf_user 也成功创建。

【小贴士】 Oracle 10g 开始，PL/SQL 编译器有能力针对常见的编译错误提高编译警告，这些警告必须引起我们重视。11g 增强特性-利用编译时警告捕获编程错误，它可以检测使用不当的 WHEN OTHERS。我们在代码编译前应该充分利用 PL/SQL 编译时警告，从而捕获隐含的程序问题。

--调用创建用户的过程

exec sp_create_xf_user(‘hfxf’);

【常见问题及解决办法】

（1）未成功创建用户，提示信息如下:

step 1: Create UserXF : ORA-01031: insufficient privileges

从报错信息看执行存储过程时权不足，我在存储过程定义加入了

AuthidCurrent_User

我们在日常工作编写存储过程时，对于授权必须谨慎对待。ORACLE PLSQL 中提供两种授权方式选择：

--AUTHID DEFINER （定义者权限）：指编译存储对象的所有者。也是默认权限模式。

--AUTHID CURRENT_USER（调用者权限）：指拥有当前会话权限的模式，这可能和当前登录用户相同或不同 (alter session set current_schema 可以改变调用者 Schema)

重新编译存储过程后，可以正常创建用户了。

（2）ORA-01920，用户名与另一用户名或角色名冲突。试图创建的用户已存在。

（3）ORA-00972，标识符过长。这是由于用户名过长导致的报错，在预定义的表 xf_users 中将用户名缩短即可。

（4）ORA-00959，表空间不存在。这是由于默认表空间或临时表空间不存在；在预定义表 xf_users 中的数据有误。

2删除数据库对象的存储过程

【设计说明】根据 TD2 系统对数据处理的业务规则，在自动化调度过程中有时需要删除数据库对象，保留该用户。本例以删除用户 xf 下的所有表为例编写存储过程。

/**

*version:v1.0

*author:huangfuxiaofei

*date:2016/03/22

*Description:drop table

*/

 

create or replace procedure  sp_droptab(p_table in varchar2) is

 v_countnumber(10);

begin

   select count(*)into v_count  from user_objects  where object_name = upper(p_table); --将统计的对象个数赋给v_count

   if v_count >0 then—进行判断

      executeimmediate 'drop table ' || p_table ||' purge';

   end if;

end sp_droptab;

【设计思路】根据传入的对象名，在 user_objects 中进行查询，如果存在就执行删除操作。

【存在问题】sp_droptab 存储过程设计时没有对传入的参数进行规则校验，也未进行异常处理，在调用和执行时，如果传入不合规的参数对象，删除对象的操作没有执行，存储过程也没有报错，不便于我们分析查找问题。

【解决方案】在存储过程中增加逻辑判断和异常处理（后面我会谈到）。

3删除用户的存储过程 

【设计说明】Oracle 使用 DROP USER 语句从数据库中删除用户。如果指定 CASCADE，会将这个用户拥有的所有对象都删除。如果删除的用户拥有很多数据库对象，执行 DROP USER 命令耗时较长。我们可以先删除用户的对象（调用上例中sp_droptab存储过程），然后删除用户。

/**

*version:v1.0

*author:huangfuxiaofei

*date:2016/03/22

*Description:drop user

*/

createor replace procedure sp_drop_user(p1  invarchar2 )Authid Current_User

is

var_start_datedate ;

var_end_datedate ;

var_sqlcodenumber ;

var_sqlmsgVARCHAR2(4000) := '' ;

pre_sqlvarchar2(1000); 

begin

 if p1 is not null then

  SELECT SYSDATE INTO var_start_date FROM dual;

  pro_kill_session(p1);--删除用户前先kill相关session

pre_sql:='DROP  USER ' || p1 ||  ' CASCADE ';

var_sqlmsg:= 'step 1: Drop User: '||p1|| '';

executeimmediate pre_sql;

SELECTSYSDATE INTO var_end_date FROM dual;

SP_INSERT_LOG(p1,’删除用户',var_start_date,v_end_date,'',var_sqlcode,'1',var_sqlmsg,pre_sql);

endif;

 EXCEPTION

     WHEN OTHERS THEN

     var_sqlcode := SQLCODE;

     var_sqlmsg := var_sqlmsg || ' ' ||  ' : ' || SQLERRM;

SP_INSERT_LOG(p1,'删除用户',var_start_date,var_end_date,'',var_sqlcode,'0',var_sqlmsg,'');

endsp_drop_user ;

【常见问题】

（1）如果用户 xf 被删除，hf 用户参考了 xf 用户的对象，那么删除用户 xf 后被参考的对象将变得无效。

（2）调用删除用户的存储过程前一定要再三确认参数和预定的数据，我曾经在生产环境就有误删除的操作，幸好删除的用户是监管报送类系统，有备份且数据可以重新生成。

（3）不允许删除当前用户。

4kill session 的存储过程 

【设计说明】通过 killsession 的方式来终止一个进程，在上例删除用户时我们调用 killsession 的存储过程。

/**

*version:v1.0

*author:huangfuxiaofei

*date:2016/03/22

*Description:kill session

*/

 

create or replace procedure sp_kill_session(p1 invarchar2)Authid Current_User

 is

  v_sid    varchar2(30);

  v_serialvarchar2(30);

  v_sql    varchar2(1000);

  v_rows number;

  v_count_sql    varchar2(1000);

  v_kill_sql    varchar2(1000);

  v_sql_code number;

  TYPE DyData ISREF CURSOR;

  rows DyData;

begin

  v_sql :='select  sid, serial# from v$sessionwhere   STATUS in(''ACTIVE'',''INACTIVE'') and SCHEMANAME=upper(''' || p1 || ''')  ';--组织SQL语句

   dbms_output.put_line(v_sql);--输出SQL语句

  v_count_sql :='select  count(1) from v$session whereSTATUS in(''ACTIVE'', ''INACTIVE'')  andSCHEMANAME=upper(''' || p1 || ''') '; 

execute immediate v_count_sql into v_rows; --统计会话数量if v_rows >0then –进行判断

  OPEN rows FORv_sql;

  LOOP

    FETCH rows    

      into v_sid,v_serial;

      v_kill_sql :='alter system kill session ''' || v_sid || ',' || v_serial || '''';

      executeimmediate v_kill_sql;

       EXIT WHEN rows%NOTFOUND;

  END LOOP;

  end if;

end pro_kill_session;

【常见问题】

（1）在这种情况下，很多时候资源是无法释放的，我们需要查询 spid，在操作系统级来kill 这些进程，获取进程地址，在 v$process 中找到 spid，然后可以使用 Kill 或者 orakill 在系统级来杀掉这些进程。

（2）参数传入不当，有时会杀掉有用的进程。

5记录操作日志的存储过程

【设计说明】本例就是将操作日志存在表中，在执行用户创建，删除等操作时都调用该存储过程。

首先定义一张记录操作信息的表 SP_LOG

 

CREATETABLE  SP_LOG

(

SYSADMIN  VARCHAR2(300), --系统模块名称

JOBNAMEVARCHAR2(400),--任务描述

START_DATEDATE,--开始时间

END_DATE DATE,--结束时间

RUN_DATEVARCHAR2(80),--耗时

SQL_CODEVARCHAR2(30),--sql_code

SP_STATEVARCHAR2(4000),--状态标识

VAR_SQLVARCHAR2(3000)—记录执行的sql语句

)TABLESPACEHFXF DATA;

创建生成操作日志的存储过程

/**

*version:v1.0

*author:huangfuxiaofei

*date:2016/03/22

* Description: insert logs

 

*/

 

create orreplace procedure sp_insert_Log(

  sys_admin        VARCHAR2 ,

  jobname   VARCHAR2 ,

  start_date DATE,

  end_date  DATE,

  run_date  VARCHAR2 ,

  sql_code  VARCHAR2 ,

  pro_status VARCHAR2 ,

  sql_state VARCHAR2 ,

  var_date    VARCHAR2 ) is

  var_sql   varchar2(1000);

begin

  v_sql:='insert into SP_LOG (sys_admin,jobname,start_date,end_date ,run_date , sql_code , pro_status, sql_state , v_sql) 

  values(:p1,:P2,:P3,:p4,:p4,:p6,:p7,:p8,:p9)';--向SP_LOG中插入日志数据

 EXECUTE IMMEDIATE v_sql

 USING sys_admin,jobname,start_date,end_date,run_date , sql_code , pro_status , sql_state , v_date;--该存储过程中使用了USING子句进行变量传值

COMMIT;

end sp_insert_Log;

/

6存储过程中异常处理

为了提高存储过程的健壮性，避免运行错误，在创建存储过程是应该包含异常处理部分，并使用异常处理部分有效解决各种可能的错误。

常见的3中异常包括：

• 预定义异常

• 非预定义异常

• 自定义异常

以上是我将运维工作中常用的几个存储过程进行了梳理，其实大家可以根据日常工作的需求设计出符合自己系统特点和需求的存储过程。存储过程并不是万能的，大家结合工作实际情况看是否采用存储过程进行相应操作。

总结

1. 存储过程可以临活使用和调用，在生产环境修改和执行时要慎之又慎，特别是存储过程涉及到数据库对象操作的功能，必须考虑其使用风险；在每一个维护操作中都要相当谨慎。

2. 注重规范。前辈大师盖神曾指出：在进行数据库维护、编写脚本时，要注意规范，统一编码，避免不必要的麻烦和风险，有时候规范比技术能力本身更为重要。

3. 维护所需的存储过程应增加详细的注释，以便在将来调用时仍然清楚的知道存储过程的逻辑和调用方法，特别是调用时的禁忌，以防时间较长后记不清存储过程的使用方法。

4. 对于不再需要的存储过程，应及时清理，以防止被他人错误（或恶意）调用。

------ The End