存储过程详解

存储过程定义：

      将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来,那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。

存储过程的优点：

     1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

     2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

      3.存储过程可以重复使用,可减少数据库开发人员的工作量

      4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权

存储过程的种类：

    1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,

         如sp_help就是取得指定对象的相关信息

   2.扩展存储过程  以XP_开头,用来调用操作系统提供的功能

      exec master..xp_cmdshell 'ping 10.8.16.1'

   3.用户自定义的存储过程,这是我们所指的存储过程

常用格式

   Create PRocedure procedue_name

   [@parameter data_type][output]

   [with]{recompile|encryption}

   as

        sql_statement

解释: 

output：表示此参数是可传回的

with{recompile|encryption}

recompile:表示每次执行此存储过程时都重新编译一次

encryption:所创建的存储过程的内容会被加密

 

存储过程的调用：

1.在sql命令窗口中，使用命令exec存储过程名称：exectest

如果存储过程有输出，而这时也需要输出，这先设置：set serveroutput on;设置显示输出

2.在网上还有一种call命令，也可以执行存储过程，但是没弄明白怎么写。

 

存储过程调试：

存储过程如果需要调试，需要先为用户设置调试存储过程的权限。

步骤：1.登陆sysdba，用户名：sys 密码：sys

2.赋予权限：grant debug any procedure to用户名;

grantdebug connect session to用户名;

这样就可以设置成功了。开始调试就可以了。

 

存储过程创建表:

   Oracle8i以前的版本，所有已编译存储对象,包括packages,procedures, functions, triggers,views等，只能以定义者(Definer)身份解析运行；而Oracle8i及其后的版本，Oracle引入调用者(invoker)权限，使得对象可以以调用者身份和权限执行。遇到存储过程中带有CreateTable的这种情况，通常解决方法是进行显式的系统权限: grant create table tousername;但是，此方法太笨，因为有可能执行一个存储过程，需要很多不同权限（oracle对权限划分粒度越来越细）。最好的方法是，利用 oracle提供的方法，在创建存储过程时，加入 Authid Current_User 条件进行权限分配。

示例：

create or replaceprocedure create_table Authid Current_User

as

Pstringvarchar2(2000);

begin

Pstring:=' CREATETABLE tmp_lxq_tp (id int,name varchar2(20))' ;--创建表的sql语句

execute immediatePstring;--创建表

end;

 

存储过程示例：

--1.创建表示例：

create or replaceprocedure create_table Authid Current_User

as

Pstringvarchar2(2000);

begin

Pstring:=' CREATETABLE tmp_lxq_tp (id int,name varchar2(20))' ;--创建表的sql语句

execute immediatePstring;--创建表

end;

--2.不带参数

create or replaceprocedure test is

begin

/*dbms_output.putline('HelloWorld');*/

dbms_output.put_line('HelloWorld');

end test;

输出基本信息：HelloWorld.