【杏彩】SQL Server存款和储蓄进度

作者:计算机专家

下边的意见是在应用游标的进度中做的日志。作者也是率先次采纳,假诺有何样难堪的地点请争论指正,大家一块儿全力。

oracle存款和储蓄进度常用技术

  一,定义:Sql Server的存款和储蓄过程是二个被取名的积存在服务器上的Transacation-Sql语句集结,是包装重复性职业的一种方法.

1.

我们在扩充pl/sql编制程序时打交道最多的正是积累进度了。存款和储蓄进程的结构是极其的简易的,大家在那边除了学习存款和储蓄进度的主干组织外,还恐怕会学习编写存款和储蓄进度时有关的一部分实用的学识。如:游标的管理,非常的拍卖,集合的选项等等

  二,存款和储蓄进度的助益:

  消息 16951,级别 16,状态 1,过程 usp_proc,第 16 行
      变量 '@myref' 无法用作参数,因为在实施该进度前,不得为 CUTucsonSOPAJERO OUTPUT 参数分配游标。

1.仓库储存进程结构

   1,重复使用。存储进度能够重复使用,从而可以减掉数据库开拓人士的工作量。

  那一个标题是自身在调用二个递归的、输出cursor output 的蕴藏进度

1.1 第二个存款和储蓄进度

   2,升高品质。存款和储蓄进度在创立的时候就开展了编写翻译,以后使用的时候绝不再重复编写翻译。日常的SQL语句每施行贰次就供给编写翻译三回,所以选拔存款和储蓄进程升高了频率。

create proc usp_proc(
@level int
@myref cursor varying output
)
as
begin
    if @level=3
        begin
             set @myref=cursor local static for
            select * from table
            open @myref
        end
     if @level<3
        begin
        declare @cur cursor
        exec usp_proc 2 @cur output --递归
        --
        --对输出游标@cur做一些操作
        --
        --使用完游标
        close @cur  --关闭游标
        deallocate @cur --删除游标
        end
end            

create or replace procedure proc1(  

     3,减弱互连网流量。存款和储蓄进度位于服务器上,调用的时候只要求传递存储进程的名目以及参数就能够了,因而下落了网络传输的数据量。

假定未有对出口的游标做close、deallocate管理就能产出上面错误。

 p_para1 varchar2,  

   4,安全性。参数化的仓储进程能够幸免SQL注入式的攻击,并且能够将Grant、Deny以及Revoke权限应用于积存进度。

2.

 p_para2 out varchar2,  

  三,语法,成立存款和储蓄进程:  

  没有为@cur,分配游标

 p_para3 in out varchar2  

语法

  那些标题是自身在应用存储进度重临的游标 cursor output 爆发的

)as    

CREATE PROC[ EDURE ] [ owner**. ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ] [ WITH     { RECOMPILE | ENCRYPTION | RECOMPILE ,* ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n* ]

参数

owner

    具有存款和储蓄进程的客商 ID 的称谓。owner 必须是时下客户的称呼或当前客户所属的角色的名称。

procedure_name

    新存款和储蓄过程的称谓。进度名务必相符标记符法规,且对于数据库及其主人必需独一。

;*number*

    是可选的板寸,用来对同名的长河分组,以便用一条 DROP PROCEDURE 语句就可以将同组的进程一齐除去。举例,名称叫 orders 的应用程序使用的历程能够命名称为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除了整个组。假设名称中含有定界标志符,则数字不应包括在标记符中,只应在 procedure_name 前后使用方便的定界符。

@parameter

    进度中的参数。在 CREATE PROCEDURE 语句中得以声惠氏(WYETH)个或七个参数。顾客必需在实行进度时提供种种所注脚参数的值(除非定义了该参数的暗中同意值,可能该值设置为等于另一个参数)。存款和储蓄进度最多能够有 2.100 个参数。

选拔 @ 符号作为第3个字符来钦定参数名称。参数名称必得相符标志符的平整。各类进度的参数仅用于该进程自身;同样的参数名称能够用在其余过程中。私下认可情状下,参数只好代替常量,而不能够用来代替表名、列名或其余数据库对象的名目。

data_type

    参数的数据类型。除 table 之外的任何具有数据类型均能够作为存款和储蓄进程的参数。不过,cursor 数据类型只好用来 OUTPUT 参数。若是钦赐 cursor 数据类型,则还非得钦点VAHavalYING 和 OUTPUT 关键字。对于能够是 cursor 数据类型的输出参数,没有最大数目标限量。

VARYING

    钦点作为出口参数援助的结果集(由存储进度动态构造,内容能够转移)。仅适用于游标参数。

default

    参数的私下认可值。假使定义了暗中同意值,不必钦赐该参数的值就可以实行进程。私下认可值必需是常量或 NULL。假设经过将对该参数使用 LIKE 关键字,那么暗中认可值中能够分包通配符(%、_、[] 和 [^])。

OUTPUT

    评释参数是回到参数。该选用的值能够重返给 EXEC[UTE]。使用 OUTPUT 参数可将消息重临给调用进程。Textntextimage 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数能够是游标占位符。

n

    表示最多可以内定 2.100 个参数的占位符。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

    RECOMPILE 评释 SQL Server 不会缓存该进度的布署,该进度就要运维时再次编译。在应用非规范值或有时值而不指望覆盖缓存在内部存款和储蓄器中的施行安排时,请使用 RECOMPILE 选项。

ENCRYPTION 表示 SQL Server 加密 syscomments 表中含有 CREATE PROCEDURE 语句文本的条约。使用 ENC福特ExplorerYPTION 可防止将经过作为 SQL Server 复制的一片段发布。

FOR REPLICATION

    钦命无法在订阅服务器上施行为复制创造的仓库储存进程。.使用 FO宝马X3 REPLICATION 选项成立的蕴藏进度可用作存款和储蓄进度筛选,且不得不在复制进度中实施。本选项不能和 WITH RECOMPILE 选项一同使用。

AS

   钦定进度要举办的操作。

sql_statement

   进度中要含有的轻巧数目和类别的 Transact-SQL 语句。但有点限量。

n

   是意味着此进度能够包涵多条 Transact-SQL 语句的占位符。

  四,使用办法:

  

**********************************************

注:*所包围部分出自MS的联手丛书.

 

                           几个实例

                        (AjaxCity表中剧情)

     ID        CityName   Short

             1         苏州市     SZ  

             2     无锡市     WX

             3         常州市     CZ

1.选取表中兼有内容并回到贰个数据集

        CREATE PROCEDURE mysp_All
        AS
           select * from AjaxCity
        GO

实行结果

        杏彩 1

2.基于传入的参数实行查询并回到三个数据集

       CREATE PROCEDURE mysp_para
            @CityName varchar(255),

            @Short    varchar(255)
       AS
         select * from AjaxCity where CityName=@CityName And Short=@Short
       GO

进行结果

        杏彩 2

3.包含输出参数的存款和储蓄进程(再次来到前两条记下的ID的和)

CREATE PROCEDURE mysp_output
       @SUM int  output
 AS
       select @SUM=sum([ID]) from (select top 2 * from AjaxCity) as tmpTable
GO

推行结果

         杏彩 3

4.在积攒进度中应用游标

  有这样两个表,存款和储蓄的是各超阶级市上面包车型地铁县级市的新闻.如图:

   杏彩 4

   以后想总结出各种地级市上面包车型大巴县级市的个数,并结合八个字符串.结果应当是"5,2,2".

 

CREATE PROCEDURE mysp_Cursor
    @Result varchar(255) output//证明输出变量
AS
    declare city_cursor cursor for//评释游标变量
    select [ID] from AjaxCity

set @Result=''
declare @Field int//注脚不经常存放CityID的变量
open city_cursor //展开游标
fetch next from city_cursor into @Field//将实际ID赋给变量
while(@@fetch_status=0)//循环起来
begin
       if @Result = ''
           select @Result = convert(nvarchar(2),count(*))  from AjaxCounty where CityID=@Field
       else
           select @Result = @Result + ',' + convert(nvarchar(2),count(*)) from AjaxCounty where CityID=@Field
      
       fetch next from city_cursor into @Field//下一个CityID
end
close city_cursor//关闭游标
deallocate city_cursor//释放游标援引
GO

 

进行理并了结果

       杏彩 5

 

    好了,关于存款和储蓄进程先写到这里.以上多少个例子基本上完毕了平日所用到的大部成效.至于复杂的存储进程,所用到的明白根本是SQL的语法,以及SQL中贮存函数的使用.已不属于本文所要切磋的界定了.

  

v_name varchar2(20);  

create proc myproc(
@mycur cursor varying output
)
as
begin
set @mycur=cursor local static  for
select * from table

open @mycur --打开游标
end

--调用myproc
declare @cur cursor
exec myproc @cur output
fetch next from @cur
while @@fetch_status=0
    begin
    --使用游标
    fetch next from @cur
    end 

begin  

并发上述错的来由就是概念游标后需求开拓 open @mycur

 v_name := '张三丰';  

 p_para3 := v_name;  

 dbms_output.put_line('p_para3:'||p_para3);  

end;  

地点正是二个最简便易行的寄放进程。二个仓库储存进程大概分为这么多少个部分:

开创语句:create or replace procedure 存款和储蓄进度名

如若未有or replace语句,则仅仅是新建二个囤积过程。假诺系统存在该存款和储蓄过程,则会报错。Create or replace procedure 假诺系统中绝非此存款和储蓄进度就新建一个,如果系统中有此存款和储蓄进度则把原先删除掉,重新创立三个积攒进程。

积攒进度名定义:包罗存款和储蓄进度名和参数列表。参数名和参数类型。参数名无法再度, 参数字传送递情势:IN, OUT, IN OUT

IN 代表输入参数,按值传递格局。

OUT 代表输出参数,能够领略为按援用传递格局。能够看成存款和储蓄进程的输出结果,供外界调用者使用。

IN OUT 就可以作输入参数,也可作输出参数。

参数的数据类型只要求指明类型名就能够,不须求钦点宽度。

参数的幅度由外界调用者决定。

经过能够有参数,也能够未有参数

变量申明块:紧跟着的as (is )关键字,能够领略为pl/sql的declare关键字,用于注明变量。

变量注明块用于注解该存款和储蓄进程供给选取的变量,它的成效域为该存款和储蓄进度。别的这里评释的变量必需钦定宽度。遵守PL/SQL的变量表明标准。

经过语句块:从begin 关键字发轫为经过的语句块。存款和储蓄进度的切实可行逻辑在此地来贯彻。

极度管理块:关键字为exception ,为处理语句产生的特别。该有的为可选

终止块:由end关键字结果。

1.2 存款和储蓄进度的参数字传送递格局

积存进程的参数字传送递有三种艺术:IN,OUT,IN OUT .

IN 按值传递,何况它不容许在仓库储存进程中被再度赋值。假若存款和储蓄进度的参数未有一点名存参数字传送递类型,默以为IN

create or replace procedure proc1(  

 p_para1 varchar2,  

 p_para2 out varchar2,  

 p_para3 in out varchar2  

)as    

v_name varchar2(20);  

begin  

 p_para1 :='aaa';  

 p_para2 :='bbb';  

 v_name := '张三丰';  

 p_para3 := v_name;  

 dbms_output.put_line('p_para3:'||p_para3);  

 null;  

end;  

     

Warning: Procedure created with compilation errors  

 

SQL> show error;  

Errors for PROCEDURE LIFEMAN.PROC1:  

 

LINE/COL ERROR  


----------------------------------------------------------------------  

8/3      PLS-00363: expression 'P_PARA1' cannot be used as an assignment target  

8/3      PL/SQL: Statement ignored  

那点与任何高档语言都不可同日而语。它也正是java在参数前面加上final关键字。

OUT 参数:作为出口参数,须要介怀,当三个参数被内定为OUT类型时,即便在调用存款和储蓄进度此前对该参数进行了赋值,在仓库储存进程中该参数的值依旧是null.

create or replace procedure proc1(  

 p_para1 varchar2,  

 p_para2 out varchar2,  

 p_para3 in out varchar2  

)as    

v_name varchar2(20);  

begin  

 v_name := '张三丰';  

 p_para3 := v_name;  

 dbms_output.put_line('p_para1:'||p_para1);  

 dbms_output.put_line('p_para2:'||p_para2);  

 dbms_output.put_line('p_para3:'||p_para3);  

end;  

 

SQL> var p1 varchar2(10);  

SQL> var p2 varchar2(10);  

SQL> var p3 varchar2(10);  

SQL> exec :p1 :='aaaa';  

SQL> exec :p2 :='bbbb';  

SQL> exec :p3 :='cccc';  

SQL> exec proc1(:p1,:p2,:p3);  

p_para1:aaaa  

p_para2:  

p_para3:张三丰  

SQL> exec dbms_output.put_line(:p2);  

 

 

PL/SQL procedure successfully completed  

p2  

---------  

INOUT 是的确的按引用传递参数。就可以作为传播参数也得以用作传播参数。

1.3 存款和储蓄进程参数宽度  

create or replace procedure proc1(  

 p_para1 varchar2,  

 p_para2 out varchar2,  

 p_para3 in out varchar2  

)as    

v_name varchar2(2);  

begin  

 v_name := p_para1;  

end;  

 

SQL> var p1 varchar2(10);  

SQL> var p2 varchar2(20);  

SQL> var p3 varchar2(30);  

SQL> exec :p1 :='aaaaaa';  

SQL> exec proc1(:p1,:p2,:p3);  

     

     

ORA-06502: PL/SQL: numeric or value error: character string buffer too small  

ORA-06512: at "LIFEMAN.PROC1", line 8  

ORA-06512: at line 1  

率先,大家要明白,大家无可奈何在蕴藏进程的概念中内定期存款储参数的肥瘦,也就招致了小编们鞭长莫及在存储进度中央调控制传入变量的上升的幅度。那一个幅度是截然由外界传入时间调节制的。

咱俩再来看看OUT类型的参数的大幅度。

create or replace procedure proc1(  

 p_para1 varchar2,  

 p_para2 out varchar2,  

 p_para3 in out varchar2  

)as    

v_name varchar2(2);  

begin  

 p_para2 :='aaaaaaaaaaaaaaaaaaaa';  

end;  

SQL> var p1 varchar2(1);  

SQL> var p2 varchar2(1);  

SQL> var p3 varchar2(1);  

SQL> exec :p2 :='a';  

SQL> exec proc1(:p1,:p2,:p3);  

在该进程中,p_para2被予以了十八个字符a.

而在表面包车型客车调用进度中,p2那一个参数仅仅被定义为varchar2(1).

而把p2作为参数调用这么些进度,却并未报错。并且它的真实值便是21个a

SQL> select dump(:p2) from dual;  

DUMP(:P2)  


 

Typ=1 Len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97  

p2  

---------  

aaaaaaaaaaaaaaaaaaaa  

     

   再来看看IN OUT参数的宽窄  

create or replace procedure proc1(  

 p_para1 varchar2,  

 p_para2 out varchar2,  

 p_para3 in out varchar2  

)as    

v_name varchar2(2);  

begin  

 p_para3 :='aaaaaaaaaaaaaaaaaaaa';  

end;  

 

SQL> var p1 varchar2(1);  

SQL> var p2 varchar2(1);  

SQL> var p3 varchar2(1);  

SQL> exec proc1(:p1,:p2,:p3);  

试行那么些进度,仍旧正确施行。

足见,对于IN参数,其调幅是由外界调控。

对于OUT 和IN OUT 参数,其调幅是由存款和储蓄进程之中央调节制。

因此,在写存款和储蓄进度时,对参数的增幅进行验证是老大有不可或缺的,最明智的章程便是参数的数据类型使用%type。那样两边就达到了一样。

1.3 参数的默许值

储存进程的参数能够安装私下认可值

create or replace procedure procdefault(p1 varchar2,  

                                       p2 varchar2 default 'mark')  

as    

begin  

 dbms_output.put_line(p2);  

end;  

 

SQL> set serveroutput on;  

SQL> exec procdefault('a');  

mark

能够透过default 关键字为存款和储蓄进程的参数钦点暗中同意值。在对存款和储蓄进程调用时,就足以省略默许值。

急需小心的是:暗许值仅仅扶助IN传输类型的参数。OUT 和 IN OUT无法钦赐暗许值

对此有暗许值的参数不是排在最终的情形。

create or replace procedure procdefault2(p1 varchar2 default 'remark',  

                                       p2 varchar2 )  

as    

begin  

 dbms_output.put_line(p1);  

end;  

先是个参数有私下认可值,第一个参数没有。要是大家想利用第三个参数的私下认可值时

exec procdefault2('aa');

如此是会报错的。

这怎么变吗?能够钦命参数的值。

SQL> exec procdefault2(p2 =>'aa');  

remark

那般就OK了,内定aa传给参数p2

  1. 存款和储蓄进度之中块

2.1 内部块

我们清楚了积累进度的组织,语句块由begin开端,以end甘休。那个块是能够嵌套。在语句块中得以嵌套任何以下的块。

Declare … begin … exception … end;  

create or replace procedure innerBlock(p1 varchar2)  

as    

 o1 varchar2(10) := 'out1';  

begin  

 dbms_output.put_line(o1);  

 declare    

   inner1 varchar2(20);  

 begin  

   inner1 :='inner1';  

   dbms_output.put_line(inner1);  

 

   declare    

     inner2 varchar2(20);  

   begin  

本文由杏彩发布,转载请注明来源

关键词: