oracle存储过程begin end；

女爷i 2023-10-08 20:12 20阅读 0赞

### begin…end语句简介 ###

begin...end语句用于定义语句块，语句块由一组一起执行的SQL语句组成。 语句块也称为批处理。

begin end形成一个语句块，语句块不会起到事务的作用，也不会起到作用域的作用，它只是有时候看起来更清晰。

begin...exception 用于捕获异常，处理各种异常。

语法如下：

begin
      程序代码块;
    exception
      when <异常1> then
        异常语句1;
      when <异常2> then
        异常语句2;
      when others then
        其它异常;
    end;

创建表test如下：

create table test(
      f1 varchar2(3),
      f2 varchar2(3)
    );
    insert into test(f1,f2) values (1,1);
    insert into test(f1,f2) values (2,2);
    insert into test(f1,f2) values (3,3);

![f19b36168b2a4798f48a019b4b51a988.png][]

创建一个名称为pro\_test 的存储过程

create or replace procedure pro_test(q_time in date) as
      v_count int; --定义一个计数器
      cursor cur_sb is
        select f1, f2 from test; --定义一个游标：cur_sb
      v_row cur_sb%rowtype; --定义游标变量
    
      /*开始运行*/
    begin
    
      --代码块1
      begin
        dbms_output.put_line('代码块1'); --打印日志
        v_count := 0; --变量赋值
        open cur_sb; --打开游标
        -- 使用loop  end loop遍历游标
        loop
          fetch cur_sb
            into v_row; --取出一行数据，放入到cur_sb中
          exit when cur_sb %notfound; --当找不到记录时（最后一行后）退出循环
          update test set f2 = '111' where f1 = v_row.f1; --业务代码
          --insert into test (f1, f2) values (1, 1111111111111111111111111); --业务代码
          dbms_output.put_line('遍历行：' || v_row.f1); --打印日志
        end loop;
        close cur_sb; --关闭游标
        commit; --提交
      exception
        --捕获各种异常
        when dup_val_on_index then
          --捕获唯一索引重复异常
          rollback; --回滚
          dbms_output.put_line('出现唯一索引异常，这里才会执行'); --打印日志
        when others then
          --捕获其他所有异常
          rollback; --回滚
          dbms_output.put_line('其他所有异常'); --打印日志
      end;
    
      --代码块2
      begin
        dbms_output.put_line('代码块2'); --打印日志
      end;
    
      -- 执行一些ddl语句
      execute immediate 'truncate table test';
      dbms_output.put_line('代码块3'); --打印日志
      /*结束运行*/
    end pro_test;

注意不要把代码写到异常后面去了，不然执行不到。

最好使用begin...end代码块，来包裹各种不同的业务，让代码更清晰。

执行ddl语句

/*清空表数据*/
    execute immediate 'truncate table test';

如提示**oracle execute immediate 权限不足ora-01031**

加上 AUTHID CURRENT\_USER 即可。

create or replace procedure PRO_CLEAR_TEMP_TABLESPACE AUTHID CURRENT_USER is
      v_start_time date; --执行开始时间
      v_end_time   date; --执行完成时间
      v_use_time   int; --执行用时，单位：秒
    begin
    
      begin
        -- 清除临时表空间
        v_start_time := sysdate;
        execute immediate 'alter tablespace IRFS_TEMP shrink space';
        dbms_output.put_line(sysdate); --打印
        v_end_time := sysdate;
        -- ceil向上取整
        v_use_time := ceil((v_end_time - v_start_time) * 24 * 60 * 60);
        --日志记录
        insert into clear_temp_tablespace_log
          (create_time, use_time)
        values
          (sysdate, v_use_time);
        commit;
      end;
    
    end PRO_CLEAR_TEMP_TABLESPACE;

虽然加上 AUTHID CURRENT\_USER 可以临时解决执行权限问题，但是在job任务中还是会报执行权限问题。

需要显示地赋权限。

GRANT alter tablespace TO 用户名;

GRANT alter tablespace TO MCQHW;

[f19b36168b2a4798f48a019b4b51a988.png]: https://img-blog.csdnimg.cn/img_convert/f19b36168b2a4798f48a019b4b51a988.png