精华内容
下载资源
问答
  • oracle存储过程语法

    千次阅读 2018-10-29 16:39:33
    前两天无意见看见了一个非常适合学习Oracle附上链接:...Oracle存储过程基本语法 存储过程   1 CREATE OR REPLACE PROCEDURE 存储过程名   2 IS   3 BEGIN   4 NULL;   5 END;  行1:   CREAT...

    前两天无意见看见了一个非常适合学习Oracle附上链接:https://blog.csdn.net/yucaifu1989/article/details/15813793

    Oracle存储过程基本语法 存储过程 
      1 CREATE OR REPLACE PROCEDURE 存储过程名 
      2 IS 
      3 BEGIN 
      4 NULL; 
      5 END; 

    行1: 
      CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 
    行2: 
      IS关键词表明后面将跟随一个PL/SQL体。 
    行3: 
      BEGIN关键词表明PL/SQL体的开始。 
    行4: 
      NULL PL/SQL语句表明什么事都不做,这句不能删去,因为PL/SQL体中至少需要有一句; 
    行5: 
      END关键词表明PL/SQL体的结束 
    存储过程创建语法: 
    create or replace procedure 存储过程名(param1 in type,param2 out type) 
    as 
    变量1 类型(值范围); --vs_msg VARCHAR2(4000); 
    变量2 类型(值范围); 

    代码如下:
    Begin 
    Select count(*) into 变量1 from 表A where列名=param1; 

    If (判断条件) then 
    Select 列名 into 变量2 from 表A where列名=param1; 
    Dbms_output。Put_line(‘打印信息'); 
    Elsif (判断条件) then 
    Dbms_output。Put_line(‘打印信息'); 
    Else 
    Raise 异常名(NO_DATA_FOUND); 
    End if; 
    Exception 
    When others then 
    Rollback; 
    End; 


    注意事项: 
    1, 存储过程参数不带取值范围,in表示传入,out表示输出 
    类型可以使用任意Oracle中的合法类型。 
    2, 变量带取值范围,后面接分号 
    3, 在判断语句前最好先用count(*)函数判断是否存在该条操作记录 
    4, 用select 。。。into。。。给变量赋值 
    5, 在代码中抛异常用 raise+异常名 

    代码如下:
    CREATE OR REPLACE PROCEDURE存储过程名 

    --定义参数 
    is_ym IN CHAR(6) , 
    the_count OUT NUMBER, 

    AS 
    --定义变量 
    vs_msg VARCHAR2(4000); --错误信息变量 
    vs_ym_beg CHAR(6); --起始月份 
    vs_ym_end CHAR(6); --终止月份 
    vs_ym_sn_beg CHAR(6); --同期起始月份 
    vs_ym_sn_end CHAR(6); --同期终止月份 
    --定义游标(简单的说就是一个可以遍历的结果集) 

    CURSOR cur_1 IS 
    SELECT 。。。 
    FROM 。。。 
    WHERE 。。。 
    GROUP BY 。。。; 
    BEGIN 

    --用输入参数给变量赋初值,用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS 

    TO_DATE 等很常用的函数。 
    vs_ym_beg := SUBSTR(is_ym,1,6); 
    vs_ym_end := SUBSTR(is_ym,7,6); 
    vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm'); 
    vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm'); 

    --先删除表中特定条件的数据。 

    DELETE FROM 表名 WHERE ym = is_ym; 

    --然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数,其中用到一个系统变量SQL%rowcount 

    DBMS_OUTPUT.put_line('del上月记录='||SQL%rowcount||'条'); 
    INSERT INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt) 
    SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000 
    FROM BGD_AREA_CM_M_BASE_T 
    WHERE ym >= vs_ym_beg 
    AND ym <= vs_ym_end 
    GROUP BY area_code,CMCODE; 
    DBMS_OUTPUT.put_line('ins当月记录='||SQL%rowcount||'条'); 
    --遍历游标处理后更新到表。遍历游标有几种方法,用for语句是其中比较直观的一种。 

    FOR rec IN cur_1 LOOP 
    UPDATE 表名 
    SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn 
    WHERE area_code = rec.area_code 
    AND CMCODE = rec.CMCODE 
    AND ym = is_ym; 
    END LOOP; 
    COMMIT; 

    --错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。 

    EXCEPTION 

    WHEN OTHERS THEN 
    vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500); 

    ROLLBACK; 

    --把当前错误记录进日志表。 

    INSERT INTO LOG_INFO(proc_name,error_info,op_date) 
    VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE); 
    COMMIT; 
    RETURN; 

    END; 


    oracle存储过程语法 
    1 、判断语句: 
    if 比较式 then begin end; end if; 

    代码如下:
    create or replace procedure test(x in number) is 
    begin 
    if x >0 then 
    begin 
    x := 0 - x; 
    end; 
    end if; 
    if x = 0 then 
    begin 
    x: = 1; 
    end; 
    end if; 
    end test; 


    2 、For 循环 
    For ... in ... LOOP 
    -- 执行语句 
    end LOOP; 
    (1) 循环遍历游标 

    代码如下:
    create or replace procedure test() as 
    Cursor cursor is select name from student; name varchar(20); 
    begin 
    for name in cursor LOOP 
    begin 
    dbms_output.putline(name); 
    end; 
    end LOOP; 
    end test; 


    (2) 循环遍历数组 

    代码如下:
    create or replace procedure test(varArray in myPackage.TestArray) as 
    --( 输入参数varArray 是自定义的数组类型,定义方式见标题6) 
    i number; 
    begin 
    i := 1; -- 存储过程数组是起始位置是从1 开始的,与java 、C 、C++ 等语言不同。因为在Oracle 中本是没有数组的概念的,数组其实就是一张 
    -- 表(Table), 每个数组元素就是表中的一个记录,所以遍历数组时就相当于从表中的第一条记录开始遍历 
    for i in 1..varArray.count LOOP 
    dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i)); 
    end LOOP; 
    end test; 


    3 、While 循环 
    while 条件语句 LOOP 

    代码如下:
    begin 
    end; 
    end LOOP; 
    E.g 
    create or replace procedure test(i in number) as 
    begin 
    while i < 10 LOOP 
    begin 
    i:= i + 1; 
    end; 
    end LOOP; 
    end test; 


    4 、数组 
    首先明确一个概念:Oracle 中本是没有数组的概念的,数组其实就是一张表(Table), 每个数组元素就是表中的一个记录。 
    使用数组时,用户可以使用Oracle 已经定义好的数组类型,或可根据自己的需要定义数组类型。 
    (1) 使用Oracle 自带的数组类型 
    x array; -- 使用时需要需要进行初始化 
    e.g: 
    create or replace procedure test(y out array) is 
    x array; 
    begin 
    x := new array(); 
    y := x; 
    end test; 
    (2) 自定义的数组类型 ( 自定义数据类型时,建议通过创建Package 的方式实现,以便于管理) 
    create or replace package myPackage is 
    Public type declarations type info is record( name varchar(20), y number); 
    type TestArray is table of info index by binary_integer; 
    -- 此处声明了一个TestArray 的类型数据,其实其为一张存储Info 数据类型的Table 而已,及TestArray 就是一张表,有两个字段,一个是name ,一个是y 。需要注意的是此处使用了Index by binary_integer 编制该Table 的索引项,也可以不写,直接写成:type TestArray is 
    table of info ,如果不写的话使用数组时就需要进行初始化:varArray myPackage.TestArray; varArray := new myPackage.TestArray(); 
    end TestArray; 
    5. 游标的使用 Oracle 中Cursor 是非常有用的,用于遍历临时表中的查询结果。其相关方法和属性也很多,现仅就常用的用法做一二介绍: 
    (1)Cursor 型游标( 不能用于参数传递) 

    代码如下:
    create or replace procedure test() is 
    cusor_1 Cursor is select std_name from student where ...; --Cursor 的使用方式1 cursor_2 Cursor; 
    begin 
    select class_name into cursor_2 from class where ...; --Cursor 的使用方式2 
    可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor 的遍历 
    end test; 
    (2)SYS_REFCURSOR 型游标,该游标是Oracle 以预先定义的游标,可作出参数进行传递 
    create or replace procedure test(rsCursor out SYS_REFCURSOR) is 
    cursor SYS_REFCURSOR; 
    name varhcar(20); 
    begin 
    OPEN cursor FOR select name from student where ... --SYS_REFCURSOR 只能通过OPEN 方法来打开和赋值 
    LOOP 
    fetch cursor into name --SYS_REFCURSOR 只能通过fetch into 来打开和遍历 exit when cursor%NOTFOUND; --SYS_REFCURSOR 中可使用三个状态属性: ---%NOTFOUND( 未找到记录信息) %FOUND( 找到记录信息) ---%ROWCOUNT( 然后当前游标所指向的行位置) 
    dbms_output.putline(name); 
    end LOOP; 
    rsCursor := cursor; 
    end test; 


    实例 
    下面写一个简单的例子来对以上所说的存储过程的用法做一个应用: 
    现假设存在两张表,一张是学生成绩表(studnet) ,字段为:stdId,math,article,language,music,sport,total,average,step 
    一张是学生课外成绩表(out_school), 字段为:stdId,parctice,comment 
    通过存储过程自动计算出每位学生的总成绩和平均成绩,同时,如果学生在课外课程中获得的评价为A ,就在总成绩上加20 分。 

    代码如下:
    create or replace procedure autocomputer(step in number) is 
    rsCursor SYS_REFCURSOR; 
    commentArray myPackage.myArray; 
    math number; 
    article number; 
    language number; 
    music number; 
    sport number; 
    total number; 
    average number; 
    stdId varchar(30); 
    record myPackage.stdInfo; 
    i number; 
    begin 
    i := 1; 
    get_comment(commentArray); -- 调用名为get_comment() 的存储过程获取学生课外评分信息 
    OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step; 
    LOOP 
    fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND; 
    total := math + article + language + music + sport; 
    for i in 1..commentArray.count LOOP 
    record := commentArray(i); 
    if stdId = record.stdId then 
    begin 
    if record.comment = 'A' then 
    begin 
    total := total + 20; 
    go to next; -- 使用go to 跳出for 循环 
    end; 
    end if; 
    end; 
    end if; 
    end LOOP; 
    <<continue>> average := total / 5; 
    update student t set t.total=total and t.average = average where t.stdId = stdId; 
    end LOOP; 
    end; 
    end autocomputer; 
    -- 取得学生评论信息的存储过程 
    create or replace procedure get_comment(commentArray out myPackage.myArray) is 
    rs SYS_REFCURSOR ; 
    record myPackage.stdInfo; 
    stdId varchar(30); 
    comment varchar(1); 
    i number; 
    begin 
    open rs for select stdId,comment from out_school 
    i := 1; 
    LOOP 
    fetch rs into stdId,comment; exit when rs%NOTFOUND; 
    record.stdId := stdId; 
    record.comment := comment; 
    recommentArray(i) := record; 
    i:=i + 1; 
    end LOOP; 
    end get_comment; 
    -- 定义数组类型myArray 
    create or replace package myPackage is begin 
    type stdInfo is record(stdId varchar(30),comment varchar(1)); 
    type myArray is table of stdInfo index by binary_integer; 
    end myPackage;

    展开全文
  • ORACLE存储过程

    万次阅读 多人点赞 2018-11-02 18:14:48
    oracle存储过程 目录 一.什么是存储过程 二.为什么要写存储过程 三.存储过程基础 1.存储过程结构 2.存储过程语法 3.pl/sql处理存储过程 四.存储过程进阶 1.BUIK COLLECT 2.FORALL 3.pl/sql调试存储过程 ...

                                                  oracle存储过程

    目录

             一.什么是存储过程

    二.为什么要写存储过程

    三.存储过程基础

    1.存储过程结构

    2.存储过程语法

    3.pl/sql处理存储过程

    四.存储过程进阶

    1.BUIK COLLECT

    2.FORALL

    3.pl/sql调试存储过程

    4.案例实战

    附.参考资料


    一.什么是存储过程

    存储过程,百度百科上是这样解释的,存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来调用存储过程。

    简单的说就是专门干一件事一段sql语句。

    可以由数据库自己去调用,也可以由java程序去调用。

    在oracle数据库中存储过程是procedure。

    二.为什么要写存储过程

    1.效率高

      存储过程编译一次后,就会存到数据库,每次调用时都直接执行。而普通的sql语句我们要保存到其他地方(例如:记事本  上),都要先分析编译才会执行。所以想对而言存储过程效率更高。

    2.降低网络流量

    存储过程编译好会放在数据库,我们在远程调用时,不会传输大量的字符串类型的sql语句。

    3.复用性高

    存储过程往往是针对一个特定的功能编写的,当再需要完成这个特定的功能时,可以再次调用该存储过程。

    4.可维护性高

    当功能要求发生小的变化时,修改之前的存储过程比较容易,花费精力少。

    5.安全性高

    完成某个特定功能的存储过程一般只有特定的用户可以使用,具有使用身份限制,更安全。

    三.存储过程基础

    1.存储过程结构

    (1).基本结构

    Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常(可写可不写,要增强脚本的容错性和调试的方便性那就写上异常处理)

    (2).无参存储过程

    CREATE OR REPLACE PROCEDURE demo AS/IS
    	变量2 DATE;
    	变量3 NUMBER;
    BEGIN
    	--要处理的业务逻辑
    	EXCEPTION    --存储过程异常
    END 
    

    这里的as和is一样任选一个,在这里没有区别,其中demo是存储过程名称。

    (3).有参存储过程

    a.带参数的存储过程

    CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
    AS/IS
    name student.name%TYPE;
    age number :=20;
    BEGIN
      --业务处理.....
    END

    上面脚本中,

    第1行:param1 是参数,类型和student表id字段的类型一样。

    第3行:声明变量name,类型是student表name字段的类型(同上)。

    第4行:声明变量age,类型数数字,初始化为20

     

    b.带参数的存储过程并且进行赋值

    CREATE OR REPLACE PROCEDURE 存储过程名称(
           s_no in varchar,
           s_name out varchar,
           s_age number) AS
    total NUMBER := 0;
    BEGIN
      SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
      dbms_output.put_line('符合该年龄的学生有'||total||'人');
      EXCEPTION
        WHEN too_many_rows THEN 
        DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
    END

    上面脚本中:

    其中参数IN表示输入参数,是参数的默认模式。
    OUT表示返回值参数,类型可以使用任意Oracle中的合法类型。
    OUT模式定义的参数只能在过程体内部赋值,表示该参数可以将某个值传递回调用他的过程
    IN OUT表示该参数可以向该过程中传递值,也可以将某个值传出去

    第7行:查询语句,把参数s_age作为过滤条件,INTO关键字,把查到的结果赋给total变量。

    第8行:输出查询结果,在数据库中“||”用来连接字符串

    第9—11行:做异常处理

    2.存储过程语法

    (1).运算符

    这里s,m,n是变量,类型是number;

    分类

    运算符

    含义

    示例表达式

     

     

     

    算术运算符

    +

    s := 2 + 2;

    -

    s := 3 – 1;

    *

    s := 2 * 3;

    /

    s := 6 / 2;

    mod(,)

    取模,取余

    m : = mod(3,2)

    **

    乘方

    10**2 =100

     

     

     

    关系运算符

    =

    等于

    s = 2

    <>或!=或~=

    不等于

    s != 2

    <

    小于

    s < 3

    >

    大于

    s > 0

    <=

    小于等于

    s <= 9

    >=

    大于等于

    s >= 1

     

     

    比较运算符

    LIKE

    满足匹配为true

    ‘li’ like ‘%i’返回true

    BETWEEN

    是否处于一个范围中

    2 between 1 and 3 返回true

    IN

    是否处于一个集合中

    ‘x’ in (‘x’,’y’) 返回true

    IS NULL

    判断变量是否为空

    若:n:=3,n is null,返回false

     

    逻辑运算符

    AND

    逻辑与

    s=3 and c is null

    OR

    逻辑或

    s=3 or c is null

    NOT

    逻辑非

    not c is null

     

    其他

    :=

    赋值

    s := 0;

    ..

    范围

    1..9,即1至9范围

    ||

    字符串连接

    ‘hello’||’world’

     

    (2).SELECT INTO STATEMENT语句

    该语句将select到的结果赋值给一个或多个变量,例如:

    CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
    s_name VARCHAR2;   --学生名称
    s_age NUMBER;      --学生年龄
    s_address VARCHAR2; --学生籍贯
    BEGIN
      --给单个变量赋值
      SELECT student_address INTO s_address
      FROM student where student_grade=100;
       --给多个变量赋值
      SELECT student_name,student_age INTO s_name,s_age
      FROM student where student_grade=100;
      --输出成绩为100分的那个学生信息
      dbms_output.put_line('姓名:'||s_name||',年龄:'||s_age||',籍贯:'||s_address);
    END

    上面脚本中:

    存储过程名称:DEMO_CDD1, student是学生表,要求查出成绩为100分的那个学生的姓名,年龄,籍贯

    (3).选择语句

    a.IF..END IF

    学生表的sex字段:1-男生;0-女生

    IF s_sex=1 THEN
      dbms_output.put_line('这个学生是男生');
    END IF

    b.IF..ELSE..END IF

    IF s_sex=1 THEN
      dbms_output.put_line('这个学生是男生');
    ELSE
      dbms_output.put_line('这个学生是女生');
    END IF

    (4).循环语句

    a.基本循环

    LOOP
      IF 表达式 THEN
        EXIT;
      END IF
    END LOOP;

    b.while循环

    WHILE 表达式 LOOP
      dbms_output.put_line('haha');
    END LOOP;

    c.for循环

    FOR a in 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
    END LOOP;

    (5).游标

        Oracle会创建一个存储区域,被称为上下文区域,用于处理SQL语句,其中包含需要处理的语句,例如所有的信息,行数处理,等等。

        游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)SQL语句返回。行集合光标保持的被称为活动集合。

    a.下表是常用的游标属性

    属性

    描述

    %FOUND

    如果DML语句执行后影响有数据被更新或DQL查到了结果,返回true。否则,返回false。

    %NOTFOUND

    如果DML语句执行后影响有数据被更新或DQL查到了结果,返回false。否则,返回true。

    %ISOPEN

    游标打开时返回true,反之,返回false。

    %ROWCOUNT

    返回DML执行后影响的行数。

    b.使用游标

    声明游标定义游标的名称和相关的SELECT语句:

    CURSOR cur_cdd IS SELECT s_id, s_name FROM student;

    打开游标游标分配内存,使得它准备取的SQL语句转换成它返回的行:

    OPEN cur_cdd;

    抓取游标中的数据,可用LIMIT关键字来限制条数,如果没有默认每次抓取一条:

    FETCH cur_cdd INTO id, name ;

    关闭游标来释放分配的内存:

    CLOSE cur_cdd;

    3.pl/sql处理存储过程

    (1).新建存储过程:右键procedures,点击new,弹出PROCEDURE框,再点击OK,如下图:

     

    (2).在下面的编辑区,编写存储过程脚本

     

    (3).在这里我们编写一个demo_cdd存储过程,要求输出“hello world”,如下图:

     

    (4).右键刚才新建的存储过程名称,点击“Test”,在点击执行按钮

     

    4.案例实战

    场景:

    有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。

    这条sql,写出来如下:

    update student set s_grade=s_grade+1

    分析:

    如果我们直接运行运行这条sql,因数据量太大会把数据库undo表空间撑爆,从而发生异常。那我们来写个存储过程,进行批量更新,我们每10万条提交一次。

    CREATE OR REPLACE PROCEDURE process_student is
    total NUMBER := 0;
    i NUMBER := 0;
    BEGIN
      SELECT COUNT(1) INTO total FROM student;
      WHILE i<=total LOOP
        UPDATE student SET grade=grade+1 WHERE s_no=i;
        i := i + 1;
        IF i >= 100000 THEN
          COMMIT;
        END IF;
      END LOOP;
      dbms_output.put_line('finished!');
    END;
    

    四.存储过程进阶

           在上面的案例中,我们的存储过程处理完所有数据要多长时间呢?事实我没有等到它执行完,在我可接受的时间范围内它没有完成。那么对于处理这种千万级数据量的情况,存储过程是不是束手无策呢?答案是否定的,接下来我们看看其他绝招。

           我们先来分析下执行过程的执行过程:一个存储过程编译后,在一条语句一条语句的执行时,如果遇到pl/sql语句就拿去给pl/sql引擎执行,如果遇到sql语句就送到sql引擎执行,然后把执行结果再返回给pl/sql引擎。遇到一个大数据量的更新,则执行焦点(正在执行的,状态处于ACTIVE)会不断的来回切换。

           Pl/SQL与SQL引擎之间的通信则称之为上下文切换,过多的上下文切换将带来过量的性能负载。最终导致效率降低,处理速度缓慢。

           从Oracle8i开始PL/SQL引入了两个新的数据操纵语句:FORALLBUIK COLLECT,这些语句大大滴减少了上下文切换次数(一次切换多次执行),同时提高DML性能,因此运用了这些语句的存储过程在处理大量数据时速度简直和飞一样。

    1.BUIK COLLECT

        Oracle8i中首次引入了Bulk Collect特性,Bulk Collect会能进行批量检索,会将检索结果结果一次性绑定到一个集合变量中,而不是通过游标cursor一条一条的检索处理。可以在SELECT INTO、FETCH INTO、RETURNING INTO语句中使用BULK COLLECT,接下来我们一起看看这些语句中是如何使用BULK COLLECT的。

    (1).SELECT INTO

    查出来一个结果集合赋值给一个集合变量。

    语法结构是:

    SELECT field BULK COLLECT INTO var_conllect FROM table where colStatement;

    说明:

           field:要查询的字段,可以是一个或多个(要保证和后面的集合变量要向对应)。

           var_collect:集合变量(联合数组等),用来存放查到的结果。

           table:表名,要查询的表。

           colStatement:后面过滤条件语句。比如s_age < 10;

    例子:查出年龄小于10岁的学生姓名赋值给数组arr_name变量

    SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;

    (2).FETCH INTO

    从一个集合中抓取一部分数据赋值给一个集合变量。

    语法结构如下:

    FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]

    说明:

            cur1:是个数据集合,例如是个游标。

            var_collect:含义同上。

            [LIMIT rows]:可有可无,限制每次抓取的数据量。不写的话,默认每次一条数据。

    例子:给年龄小于10岁的学生的年级降一级。

    --查询年龄小于10岁的学生的学号放在游标cur_no里
    CURSOR cur_no IS 
    		SELECT s_no FROM student WHERE s_age < 10;
    
    --声明了一个联合数组类型,元素类型和游标cur_no每个元素的类型一致
    TYPE ARR_NO IS VARRAY(10) OF cur_no%ROWTYPE;
    
    --声明一个该数组类型的变量no
    no ARR_NO;
    BEGIN
      FETCH cur_no BULK COLLECT INTO no LIMIT 100;
      FORALL i IN 1..no.count SAVE EXCEPTONS
    	UPDATE student SET s_grade=s_grade-1 WHERE no(i);
    END;
    

    说明:先查出年龄小于10岁的学生的学号放在游标里,再每次从游标里拿出100个学号,进行更新,给他们的年级降一级。

    (3).RETURNING

    BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用,可以返回这些DML语句执行后所影响的记录内容(某些字段)。

    再看一眼学生表的字段情况:student(s_no, s_name, s_age, s_grade)

    语法结构如下:

    DMLStatement
           RETURNING field BULK COLLECT INTO var_field;

    说明:

            DMLStatement:是一个DML语句。

            field:是这个表的某个字段,当然也可以写多个逗号隔开(field1,field2, field3)。

            var_field:一个类型为该字段类型的集合,多个的话用逗号隔开,如下:

            (var_field1, var_field2, var_field3)

     

    例子:获取那些因为年龄小于10岁而年级被将一级的学生的姓名集合。

    TYPE NAME_COLLECT IS TABLE OF student.s_name%TYPE;
    names NAME_COLLECT;
    BEGIN
      UPDATE student SET s_grade=s_grade-1 WHERE s_age < 10
      RETURNING s_name BULK COLLECT INTO names;
    END;

    说明:

           NAME_COLLECT:是一个集合类型,类型是student表的name字段的类型。

           names:定义了一个NAME_COLLECT类型的变量。

    (4).注意事项

    a.不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。

    b.只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。

    c.BULK COLLECT INTO 的目标对象必须是集合类型。

    d.复合目标(如对象类型)不能在RETURNING INTO 子句中使用。

    e.如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO 子句中使用。

    f.如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。

    2.FORALL

    (1).语法

    FORALL index IN bounds [SAVE EXCEPTIONS]     
         sqlStatement;

    说明:

            index是指下标;

            bounds是一个边界,形式是start..end

            [SAVE EXCEPTIONS] 可写可不写,这个下面介绍;

            sqlStatement是一个DML语句,这里有且仅有一个sql语句;

    例子:

    --例子1:移除年级是5到10之间的学生
    FORALL i IN 5..10
           DELETE FROM student where s_grade=i;
    --例子:2,arr是一个数组,存着要升高一年级的学生名称
    FORALL s IN 1..arr.count SAVE EXCEPTIONS
           UPDATE student SET s_grade=s_grade+1 WHERE s_name=arr(i);

    (2).SAVE EXCEPTIONS

    通常情况写我们在执行DML语句时,可能会遇到异常,可能致使某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句,那么DML语句会在执行到一半的时候停下来。

           如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句,当在执行过程中如果遇到异常,数据处理会继续向下进行,发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中,该游标属性是个记录集合,每条记录有两个字段,例如:(1, 02300);

           ERROR_INDEX:该字段会存储发生异常的FORALL语句的迭代编号;

           ERROR_CODE:存储对应异常的,oracle错误代码;

    SQL%BULK_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。而这个异常记录集合异常的个数则由它的COUNT属性表示,即:

           SQL%BULK_EXCEPTONS.COUNT,SQL%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。

    (3). INDICES OF

    在Oracle数据库10g之前有一个重要的限制,该数据库从IN范围子句中的第一行到最后一行,依次读取集合的内容,如果在该范围内遇到一个未定义的行,Oracle数据库将引发ORA-22160异常事件:ORA-22160: element at index [N] does not exist。针对这一问题,Oracle后续又提供了两个新语句:INDICES OF 和 VALUES OF。

    接下来我们来看看这个INDICES OF语句,用于处理稀疏数组或包含有间隙的数组(例如:一个集合的某些元素被删除了)。

    该语句语法结构是:

    FORALL i INDICES OF collection [SAVE EXCEPTIONS]
    
           sqlStatement;

    说明:

    i:集合(嵌套表或联合数组)下标。

    collection:是这个集合。

    [SAVE EXCEPTIONS]和sqlStatement上面已经解释过。

    例子:arr_std是一个联合数组,每个元素包含(name,age,grade),现在要向student表插入数据。

    FORALL i IN INDICES OF arr_stu
           INSERT INTO student VALUES(
               arr_stu(i).name,
                  arr_stu(i).age,
                  arr_stu(i).grade
           );

    (4). VALUES OF

    VALUES OF适用情况:绑定数组可以是稀疏数组,也可以不是,但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是,VALUES OF在使用时有一些限制:

           如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引,VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER;

           当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常;

    该语句的语法结构是:

    FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
           sqlStatement;

    说明:i和collection含义如上

    联合数组请看文章(或自行百度):https://blog.csdn.net/leshami/article/details/7372061

    3.pl/sql调试存储过程

    首先,当前这个用户得有能调试存储过程的权限,如果没有的话,以数据库管理员身份给你这个用户授权:

    --userName是你要拿到调试存储过程权限的用户名
    GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO username;

    (1).右键一个存储过程名称,点击测试,如下图:

    这里我用的pl/sql是12.0.4版本的,下面截图中与低版本的pl/sql按钮位置都相同,只是图标不一样。

     

    (2).点击两次step into按钮,进入语句调试,如下图:

     

    (3).每点击一次step into按钮,会想下执行一条语句,也可以查看变量和表达式的值,如下图:

     

    查看变量值:在查看变量区域,在Variable列输入变量i,在Value列点击下,该变量的值就显示出来了。

    4.案例实战

    场景和上面的案例实战是同一个,如下:

    有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。

    这条sql,写出来如下:

    update student set s_grade=s_grade+1

    编写存储过程:

    (1).存储过程1

    名称为:process_student1,student表的s_no字段类型为varchar2(16)。

    CREATE OR REPLACE PROCEDURE process_student1 AS
        CURSOR CUR_STUDENT IS SELECT s_no FROM student;
        TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
        students REC_STUDENT;
    BEGIN
      OPEN CUR_STUDENT;
      WHILE (TRUE) LOOP
        FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
        FORALL i IN 1..students.count SAVE EXCEPTIONS
          UPDATE student SET s_grade=s_grade+1 WHERE s_no=students(i);
        COMMIT;
        EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
      END LOO;
      dbms_output.put_line('finished');
    END;

    说明:

            把student表中要更新的记录的学号拿出来放在游标CUR_STUDENT,每次从这个游标里抓取10万条数据赋值给数组students,每次更新这10万条记录。循环进行直到游标里的数据全部抓取完。

            FETCH .. BULK COLLECT INTO .. LIMIT rows语句中:这个rows我测试目前最大可以为10万条。

    (2).存储过程2(ROWID)

           如果我们这个student表没有主键,也没有索引呢,该怎么来做呢?

    分析下:

           ROWNUM是伪列,每次获取结果后,然后在结果集里会产生一列,从1开始排,每次都是从1开始排。

            ROWID在每个表中,每条记录的ROWID都是唯一的。在这种情况下,我们可以用ROWID。但要注意的是,ROWID是一个类型,注意它和VARCHAR2之间的转换。有两个方法:ROWIDTOCHAR()是把ROWID类型转换为CHAR类型;CHARTOROWID()是把CAHR类型转换为ROWID类型。

    接下来我们编写存储过程process_student2,脚本如下:

    CREATE OR REPLACE PROCEDURE process_student1 AS
        CURSOR CUR_STUDENT IS SELECT ROWIDTOCHAR(ROWID) FROM student;
        TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
        students REC_STUDENT;
    BEGIN
      OPEN CUR_STUDENT;
      WHILE (TRUE) LOOP
        FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
        FORALL i IN 1..students.count SAVE EXCEPTIONS
          UPDATE student SET s_grade=s_grade+1 WHERE ROWID=CHARTOROWID(students(i));
        COMMIT;
        EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
      END LOO;
      dbms_output.put_line('finished');
    END;

    说明:

           我们首先查到记录的ROWID并把它转换为CHAR类型,存放到游标CUR_STUDENT里,

    再每次抓取10万条数据赋值给数组进行更新,更新语句的WHERE条件时,又把数组元素是CAHR类型的rowid串转换为ROWID类型。

    附.参考资料

    存储过程基础:

           https://www.yiibai.com/plsql/plsql_basic_syntax.html

    存储过程进阶之FORALL:

           https://blog.csdn.net/leshami/article/details/7536926

           https://blog.csdn.net/jie1336950707/article/details/49966753

    存储过程进阶之BUIL COLLECT:

           https://blog.csdn.net/leeboy_wang/article/details/7991021

           https://blog.csdn.net/leshami/article/details/7545597

    联合数组:

           https://blog.csdn.net/leshami/article/details/7372061

    展开全文
  • Oracle 存储过程语法及使用

    千次阅读 2019-07-10 17:40:09
    写在前面 本来这种东西作为一个写Java的我以为应该是基本用不上的,以前基本就是听过而已的一个概念,但是实际接到的一...Oracle存储过程的基本语法 create [or replace] procedure 过程名 ( p1 in|out datatype, ...

    写在前面

    本来这种东西作为一个写Java的我以为应该是基本用不上的,以前基本就是听过而已的一个概念,但是实际接到的一个需求就是需要数据库去定期执行存储过程,本着打好基础的目的,还是稍微学习了一下,本篇并不学术、系统、严谨,就是随笔写个使用经验防遗忘。

    Oracle存储过程的基本语法

    create [or replace] procedure 过程名
    ( p1 in|out datatype,
      p2 in|out datatype,
      ...
      pn in|out datatype
         
    ) is 
         
        变量1 类型(值范围);
    	变量2 类型(值范围);....--声明部分
     
        begin
         
        ....--过程体
     
        end;
    

    敝司将不同模块需要用到的存储过程放到对应的package中,因此,可以粗暴地将package理解为一个class,而procedure就是类对应的function,这样并不十分严谨,Oracle有它自己的Function,并且可以与procedure一起置于package下。
    其中in表示入参,out就是出参或者说运行结果了。
    在上面的“声明部分”,可以声明我们需要用到的变量及其类型和范围,变量可以是调用其他存储过程来赋值。
    过程体就是具体的方法体实现,入参以及声明体中的变量可以在这个部分使用。

    关于游标Cursor

    Cursor可以用来作为一个中间TEMP值使用,依然可以在上述声明部分使用。例如这样一个场景,我们需要先行查询一个结果集,并对其中元素遍历,那么CURSOR结合LOOP体就十分有用,事实上敝司的CURSOR也是这么用的。
    其语法如下:

    -- 声明游标
    declare cursor cursor_name(游标名)
    is select_statement(查询语句);
    
    --使用游标
    open cursor_name;
    --关闭游标 请注意使用完后一定要关闭
    close cursor_name;
    --从游标中获取数据
    fetch cursor_name into v_record--变量
    

    遍历CURSOR中数据的实例

    open cur_xsjbxx;--打开游标
      loop
        FETCH cur_name
          INTO ls_curinfo;--获取记录值
        EXIT WHEN cur_name%NOTFOUND;
       
        dbms_output.put_line('游标信息ID:' || ls_curinfo.id || ',名称:' ||
                             ls_curinfo.name);
      end loop;
      close cur_xsjbxx;--关闭游标
    
    展开全文
  • 存储过程 包含三部分: 声明,执行部分,异常。 可以有无参数程序和带参数存储过程。 无参程序语法 1 create or replace procedure NoParPro 2 as ; 3 begin 4 ; 5 exception 6 ; 7 end; 8 带参存储...
  • mysql 、oracle存储过程语法区别

    千次阅读 2015-05-28 08:12:01
    项目需要把oracle存储过程转成mysql,一些语法不同,做些记录,供下次参考。

    mysql 、oracle存储过程语法区别

     

    1、  条件语句:mysql使用elseif关键字,oracle是elsif关键字;

    oracle:

    if表达式 then 

    表达式;

    elsif

    表达式;

           endif;

    mysql:

    if表达式then 

    表达式;

    elseif

    表达式;

                  endif;

    2、 字符串连接

           oracle使用 || ;

           mysql 使用concat函数;

     

    3、 日期计算(年月日数)

    mysql:

    函数TimeStampDiff()是MySQL本身提供的可以计算两个时间间隔的函数,语法为:TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2),其中unit单位有如下几种,分别是:SECOND, MINUTE, HOUR, DAY,WEEK, MONTH, QUARTER, or YEAR。

    当前时间:sysdate() 
    字符转日期:str_to_date() 分隔符一致,年月日要一致;示例:

    select str_to_date('2008-4-2 15:3:28','%Y-%m-%d%H:%i:%s');

    日期转字符:DATE_FORMAT(date,format) 
    SELECT DATE_FORMAT(sysdate(), '%Y-%m-%d %H:%i:%s');
    数字转字符:concat(num,’’)

    oracle:

           months_between 求日期间隔月份,除以12即为间隔年份;

           天数,只需要日期直接相减;

           当前时间:sysdate

           字符转日期:to_date()

    日期转字符:to_char(date,format) to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
    数字转字符:to_char(num)

     

    4、 定义游标

           oracel:

           CURSOR curPlanIndex is

        SELECT a.INDEX_SCORE

              ,c.enum_value,c.dn_value,c.up_value,c.score,c.score_desc

        FROM eval_plan_index  a

             JOIN eval_index_score c onc.index_id=a.index_id and a.plan_id = c.plan_id

        WHERE a.plan_id = V_PLAN_ID and a.index_id= V_INDEX_ID

    order by dn_value;

    MYSQL:

    declare  curPlanIndex cursor for

        SELECT a.INDEX_SCORE

              ,c.enum_value,c.dn_value,c.up_value,c.score,c.score_desc

        FROM eval_plan_index  a

             JOIN eval_index_score c onc.index_id=a.index_id and a.plan_id = c.plan_id

        WHERE a.plan_id = V_PLAN_ID and a.index_id= V_INDEX_ID

    order by dn_value;

     

    5、 selectinto 赋值

    oracle 有exception错误处理

     begin

        select value_name into vc_num_unit fromsys_dict

          where dict_code = 'szdw' and value_code =v_num_unit  and rownum <=1 ;

     exception

        when no_data_found then

          vc_num_unit := '';

     end;

    mysql 如果select 没有数据,则不执行into操作,变量值保持为上次结果,需要手工重置。最好能limit 1;只返回一条数据;

    展开全文
  • Oracle存储过程基本语法格式,基本存储过程的使用
  • 一、Oracle创建存储过程执行存储过程   这和sql的创建和执行是不一样的。   CREATE OR REPLACE PROCEDURE P_CREATE_T_PM_BILLS_CHECK ( RESULT IN OUT VARCHAR2 ) AS BEGIN RESULT := 1 || RESULT;...
  • Oracle存储过程基本语法

    万次阅读 2018-08-03 15:12:23
    oracle 存储过程基本语法 存储过程如同一门程序设计语言,同样包含了数据类型、流程控制、输入和输出和它自己的函数库。 --------------------基本语法-------------------- 一.创建存储过程 create procedure ...
  • oracle 存储过程语法例子

    千次阅读 2007-04-24 00:53:00
    包的定义: create or replace package myTest is type out_cur is ref cursor; procedure writeCount(codeid in nvarchar2); procedure testSandyInSert(codeid in nvarchar2,counts out number);... 存储过程的定义:
  • 存储过程创建语法: create [or replace] procedure 存储过程名(param1 in type,param2 out type) as 变量1 类型(值范围); 变量2 类型(值范围); Begin Select count(*) into 变量1 from 表A where列名=param...
  • Oracle存储过程基本语法介绍

    千次阅读 2016-05-31 18:49:27
    Oracle存储过程基本语法 存储过程   1 CREATE OR REPLACE PROCEDURE 存储过程名   2 IS   3 BEGIN   4 NULL;   5 END;  行1:   CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去...
  • 1.oracle存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT ...
  • Oracle存储过程及调用

    2020-12-16 01:18:05
    Oracle存储过程语法 Oracle的存储过程语法如下: create procedure 存储过程名称(随便取) is 在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量 begin 执行部分 end; (2)带参数的存储...
  • oracle 存储过程循环执行update语句

    万次阅读 2016-05-10 18:26:24
    select column1 from table1 ;...请问在存储过程里如何实现? 请写一个较为完成的存储过程。我会适当再加分的。谢谢! 2012-06-28 13:43 提问者采纳其实二楼写的最简单,但对于新手,最好别那么
  • create or replace procedure [()] as|is  --创建过程,可指定运行过程需传递的参数 begin  --包括在过程中要执行的语句  [exception  ] --处理异常 end;
  • Oracle存储过程

    万次阅读 多人点赞 2019-07-01 14:52:17
    Oracle存储过程详解 procedure 1.创建Oracle存储过程 prodedure create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as --声明变量(变量名 变量类型) begin --存储过程执行体 end ...
  • 此篇博客主要为了记录有returning into的语法,方便以后查看。 在动态SQL中调用函数 注意点:函数有返回值。 create or replace function f_call(p1 in varchar2) return varchar2 as begin return p1||'is ...
  • Oracle存储过程基本语法和基础教程

    千次阅读 2014-12-04 17:13:38
    CREATE OR REPLACE PROCEDURE 存储过程名字 (  参数1 IN NUMBER,  参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT INTO STATEMENT  将select查询的结果存入...
  • 1. top order by 转换成 rownum order by 的问题 (子查询实现)同级情况下的... oracle: 先rownum 再 order by 2. 已有数据的字段类型不匹配,通过下列语句修改。 alter table css_sed rename column action to myacti
  • Oracle 存储过程执行 shell 命令

    千次阅读 2019-11-10 17:35:41
    通过调用存储过程传入一个shell命令,然后执行命令。 存储过程调用java代码,java代码执行shell命令 1.编写 java 代码 ,文件名 A.java import java.io.BufferedReader; import java.io.BufferedReader; ...
  • plsql 定时执行Oracle存储过程

    千次阅读 2018-08-28 14:02:17
    这几天项目任务中需要建立一个存储过程接口,以方便后续的调用,由于之前没怎么在Oracle PL/SQL中新建过存储过程,所以花了些时间去看,结果发现通过百度到的都是些存储过程的知识而非PL/SQL中的新建-查看结果的过程...
  • oracle存储过程

    2013-03-24 13:52:25
    oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT INTO ...
  • 存储过程  1 CREATE OR REPLACE PROCEDURE 存储过程名 ... CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 行2:
  • Oracle存储过程实例

    千次阅读 2019-05-14 14:10:11
    Oracle存储过程 存储过程 存储过程语法 例子 计算信用积分的存储过程: --记录存储过程log的存储过程(方法函数) sp_exception_log create or replace procedure sp_exception_log(v_date in date, v_proc_nam...
  • oracle 存储过程

    千次阅读 2011-07-27 00:33:55
    Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常。Oracle存储过程可以有无参数存储过程和带参数存储过程。一、无参数存储过程语法: create or replace procedure noPramePro as ...; begin .....
  • Oracle procedure 基本语法 (存储过程)

    万次阅读 2019-05-31 11:05:46
    Oracle procedure 基本语法 转自:http://lorry1113.javaeye.com/blog/513851 关键字: oracle 存储过程 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 83,663
精华内容 33,465
关键字:

oracle存储过程语法执行