oracle存储过程begin end；

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);

创建一个名称为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;