美文网首页
【工作笔记】Oracle存储过程实战

【工作笔记】Oracle存储过程实战

作者: 高桥my | 来源:发表于2020-02-18 13:03 被阅读0次

    本篇围绕几种模式:无参存储过程、带参存储过程、带参数存储过程含赋值方式、存储过程中游标定义使用、异常使用分别举例使用。

    存储过程使用分为三步:编写存储过程->执行编译->调用使用

    一、无参存储过程语法

    (1)编写存储过程

    create or replace procedure print_Time

    IS

    BEGIN

      DBMS_OUTPUT.PUT_LINE(SYSDATE);

    END print_Time;

    (2)运行编译

    运行编译

    (3)调用使用

    A)SQL窗口执行:

    DECLARE

    BEGIN

      print_Time();

    END;

    B)SQL命令窗口执行:exec print_Time;

    exec print_Time

    二、带参存储过程实例

    (1)编写存储过程

    create or replace procedure print_Time2(in_callDate in varchar2)

    IS

    BEGIN

      DBMS_OUTPUT.PUT_LINE(to_date(in_callDate, 'yyyy-MM-dd HH24:mi'));

      --insert Log values(in_callDate);

    END print_Time2;

    (2)调用:

    DECLARE

      p_outval number;

      p_inoutval VARCHAR2(10) := '~Hi~';

    BEGIN

      print_Time2('2018-10-22');

    END;

    三、带参数存储过程含赋值方式

    (1)创建两张表:一张用户表(并插入数据),一张用于统计用户注册(脚本见附件)

    创建两张表

    (2)添加几条数据到用户表

    添加几条数据到用户表

    (3)现在写一个存储过程,完成每天凌晨统计前一天不同城市用户注册数量

    首先按照常规写出查询语句:

    SELECT u.city as cityNme, count(*) as userCount

    FROM z_test_user u

    WHERE u.create_time >= to_date('2018-10-25', 'yyyy-MM-dd HH24:mi')

    AND u.create_time < to_date('2018-10-25', 'yyyy-MM-dd HH24:mi')+1

    GROUP BY u.city;

    按照语法创建存储过程:

    create or replace procedure PROC_STATISTICS_USER(inDate in varchar2)

    IS

    cityName          VARCHAR2(20);

    userCount        NUMBER(10);

    BEGIN

    for cur_row in (

    SELECT u.city as city, count(*)as tatal FROM z_test_user u

        WHERE u.create_time >= to_date(inDate, 'yyyy-MM-dd HH24:mi')

        AND u.create_time < to_date(inDate, 'yyyy-MM-dd HH24:mi')+1

    GROUP BY u.city ) loop

      cityName := cur_row.city;

      userCount:= cur_row.tatal;

      insert into Z_TEST_USER_LOG values (SQL_Z_TEST_USER_LOG.NEXTVAL, cityName, userCount, SYSDATE);

      commit;

    end loop;

    END PROC_STATISTICS_USER;

    说明:a)黑色字体为创建存储过程规范格式(如不明天请看第一部分理论学习)。

    b) 红色字体分为三步,第一定义变量包括类型,第二赋值,使用“:=”,第三插入使用变量

        c) BEGIN之后是查询,并进行循环,格式:for 变量名 in() loop ... end loop;

      d)查询语句按照时间条件并按城市名称进行分组查询城市名称,总数,然后赋值给变量cur_row,下文使用cur_row.city、cur_row.tatal进行取值。

      e) insert into... 进行插入。

    (4) 编译

    编译

    (5)调用执行

    DECLARE

    BEGIN

      PROC_STATISTICS_USER('2018-10-25');

    END;

    结果如下:

    结果

    注:实际存储调用一般放在定时任务调用。

    四、存储过程中游标定义使用

    注:依然使用上面表数据作为演示,完成每天凌晨统计前一天不同城市用户注册数量

    (1)创建存储过程

    create or replace procedure PROC_STATISTICS_USER_CUR(inDate in varchar2)

    IS

      cityName          VARCHAR2(20); --定义变量

      userCount        NUMBER(10);  --定义变量

      -- storeName      VARCHAR2(20); --1定义变量

      Cursor cur_row IS

        SELECT u.city as city, count(*)as tatal FROM z_test_user u

        WHERE u.create_time >= to_date(inDate, 'yyyy-MM-dd HH24:mi')

        AND u.create_time < to_date(inDate, 'yyyy-MM-dd HH24:mi')+1

        GROUP BY u.city;

    BEGIN

      FOR c_row IN cur_row LOOP

      cityName := c_row.city;  --赋值

      userCount:= c_row.tatal; --赋值

      -- 有些时候,有其他参数需要从其他表中查询下面给出一个举例

      -- select storeName

      -- into storeName    --2赋值

      -- from STORE_INFO t

      -- where t.STORE_NAME =c_row.city;

      insert into Z_TEST_USER_LOG values (SQL_Z_TEST_USER_LOG.NEXTVAL, cityName, userCount, SYSDATE);

      --storeName      -- 3 使用 

      commit;

    END loop;

    END PROC_STATISTICS_USER_CUR;

    说明:a)首先看一下红色字体,storeName需要从其他表中查询,使用举例

        b) 游标使用格式:在is后Cursor 变量名 IS ... ;

        c) begin中使用:FOR c_row IN cur_row LOOP... END loop;赋值,插入等操作,此时for中in是直接使用游标进行。可以理解cur_row为父亲游标,c_row为子游标,就如数据和数组下标关系。

    (2)编译执行及执行结果如下:

    结果

    五、异常使用

    有时候执行存储过程会报错,调用者需要知道执行结果是否报错,此时需要定义传出参数,并在异常块进行赋值。捕获到异常之后:

    1.记录错误相关信息  放入相关日志表  SQLCODE  SQLERRM

    2.如果有事务相关的操作 一般是要rollback

    (1)创建存储过程

    create or replace procedure PROC_STATISTICS_USER_EX(inDate in varchar2, out_code out int, out_msg out varchar2)

    IS

      val int;  --定义一个整数变量

    BEGIN

      out_code := 0;

      out_msg  := 'success';

      val := 0/0;  -- 赋值,使用0做为除数,执行报异常

    EXCEPTION

      when others then

        out_code := '-1';

        out_msg  := '统计发生异常' || substr(sqlerrm, 1, 100);

        dbms_output.put_line(out_code || '::'||out_msg);

        rollback ;

    END PROC_STATISTICS_USER_EX;

    (2)使用:使用命令窗口或者SQL窗口

    a)先执行打开调试:set serverout on;

    b) 再执行:

    DECLARE

    out_code number;

    out_msg VARCHAR2(100);

    BEGIN

      PROC_STATISTICS_USER_EX('2018-10-23', out_code, out_msg);

      dbms_output.put_line(out_code || '::'||out_msg);

    END;

    /

    结果如下:

    结果

    SQL窗口:

    SQL窗口 SQL窗口

    留下一个问题:循环里面错误处理(oracle只支持begin...end中放错误捕获)

    附件:

    1,用户表创建及添加数据:

    drop table Z_TEST_USER cascade constraints;

    create table Z_TEST_USER

    (

      ID          NUMBER not null,

      USERNAME    VARCHAR2(20),

      PASSWORD    VARCHAR2(100),

      CITY        VARCHAR2(50),

      CREATE_TIME DATE

    )

    tablespace USERS

      pctfree 10

      initrans 1

      maxtrans 255

      storage

      (

        initial 64K

        next 8K

        minextents 1

        maxextents unlimited

      );

    comment on column Z_TEST_USER.ID

      is '用户ID';

    comment on column Z_TEST_USER.USERNAME

      is '用户姓名(电话)';

    comment on column Z_TEST_USER.PASSWORD

      is '密码';

    comment on column Z_TEST_USER.CITY

      is '城市';

    comment on column Z_TEST_USER.CREATE_TIME

      is '创建日期';

    insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)

    values (5, 'eee', 'eee', '北京', to_date('25-10-2018 00:02:00', 'dd-mm-yyyy hh24:mi:ss'));

    insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)

    values (1, 'aaa', 'aaa', '上海', to_date('25-10-2018 02:08:00', 'dd-mm-yyyy hh24:mi:ss'));

    insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)

    values (2, 'bbb', 'bbb', '上海', to_date('25-10-2018 05:00:00', 'dd-mm-yyyy hh24:mi:ss'));

    insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)

    values (3, 'ccc', 'ccc', '合肥', to_date('25-10-2018 04:02:01', 'dd-mm-yyyy hh24:mi:ss'));

    insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)

    values (4, 'ddd', 'ddd', '合肥', to_date('25-10-2018 09:00:01', 'dd-mm-yyyy hh24:mi:ss'));

    commit;

    2,日志表创建

    drop table Z_TEST_USER_LOG cascade constraints;

    create table Z_TEST_USER_LOG

    (

      ID        NUMBER not null,

      CITY      VARCHAR2(20),

      COUNT      NUMBER,

      COUNT_TIME DATE

    )

    tablespace USERS

      pctfree 10

      initrans 1

      maxtrans 255

      storage

      (

        initial 64K

        next 8K

        minextents 1

        maxextents unlimited

      );

    comment on column Z_TEST_USER_LOG.ID

      is '用户ID';

    comment on column Z_TEST_USER_LOG.CITY

      is '城市';

    comment on column Z_TEST_USER_LOG.COUNT

      is '注册个数';

    comment on column Z_TEST_USER_LOG.COUNT_TIME

      is '统计时间';

    commit;

    参考资料:

    http://blog.csdn.net/u013057786/article/details/17165623

    https://www.cnblogs.com/liangyihui/p/5886760.html

    https://bbs.csdn.net/topics/391912626?page=1

    来源:讯飞技术 https://mp.weixin.qq.com/s/aymp9_40eqKR8IiV9U2ABQ

    相关文章

      网友评论

          本文标题:【工作笔记】Oracle存储过程实战

          本文链接:https://www.haomeiwen.com/subject/syrgfhtx.html