下边包车型客车眼光是在选拔游标的历程中做的日志。作者也是率先次接受,要是有如何狼狈的地点请探究指正,大家一起尽力。

oracle存款和储蓄进程常用技巧

  生龙活虎,定义:Sql
Server的存款和储蓄进度是贰个被命名的蕴藏在服务器上的Transacation-Sql语句集合,是包裹重复性专门的学问的黄金年代种方法.

1.

咱俩在进展pl/sql编制程序时打交道最多的便是积存进度了。存款和储蓄进度的组织是可怜的精简的,我们在那除了读书存款和储蓄进程的主题构造外,还有恐怕会学习编写存款和储蓄进度时有关的片段实用的学问。如:游标的拍卖,万分的拍卖,群集的筛选等等

  二,存款和储蓄进度的亮点:

  消息 16951,级别 16,状态 1,过程
usp_proc,第 16 行
      变量 ‘@myref’
不能够用作参数,因为在实行该进程前,不得为 CU迈凯伦720SSOPRADO 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
语句中可以声贝拉米(Bellamy)个或四个参数。用户必得在实践进度时提供各个所申明参数的值(除非定义了该参数的暗中认可值,可能该值设置为等于另三个参数)。存款和储蓄进程最多能够有
2.100 个参数。

运用 @
符号作为第4个字符来钦赐参数名称。参数名称必需切合标记符的规规矩矩。每种进程的参数仅用于该进程本人;相符的参数名称能够用在别的进程中。暗许情状下,参数只好取代常量,而不能够用来代替表名、列名或此外数据库对象的名目。

data_type

   
参数的数据类型。除 table
之外的其余具有数据类型均能够看成存款和储蓄进度的参数。不过,cursor
数据类型只可以用于 OUTPUT 参数。假使钦命 cursor 数据类型,则还必需钦点VAENVISIONYING 和 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 语句文本的条文。使用 ENCEnclaveYPTION 可防范将经过作为 SQL
Server 复制的后生可畏部分颁布。

FOR
REPLICATION

   
钦点不可能在订阅服务器上试行为复制创立的蕴藏进程。.使用 FOTucson 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作为参数调用那些进度,却并不曾报错。何况它的真实值正是18个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  

admin

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注