使用plsql developer创建存储过程以及调试

2019独角兽企业重金招聘Python工程师标准>>>

前言~

      今天莫名的接到一个任务，需要使用oracle定时任务和oracle存储过程来每日创建一个日志表，由于小编呢尚未接触过存储过程和定时任务，所以今天学习了一番，特定来总结一下。望能给予一些未接触过存储过程的小伙伴一些帮助。

       今入今天的正题，首先要了解一下oracle的存储过程，都有哪些结构，而plsql是一个辅助工具，是能帮助我们更轻松的实现存储过程。

上述就是一个无参的存储过程实例，一个存储过程大体分为这么几个部分：

1）、创建语句：create or replace procedure 存储过程名称 [authid current_user] ；

         “”[]“”中括号的内容是可选的，其表示修改存储过程，加入authid current_user时存储过程可以使用role权限。否则会报ORA-01031权限不足。

       如果没有or replace语句，那只是新建一个存储过程，如果系统中存在相同的存储过程，则会报错，Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。

       存储过程名定义：包括存储过程名和参数列表、参数名和参数类型。参数列表可不写，如例子所示。参数名不能重复，并且每个参数之间用分号“   ；” 隔开， 参数传递方式:IN, OUT, IN OUT。如下面例子所示：

下面说明一下参数传递方式：

        in：表示输入参数，调用存储过程时从外面传进来的，它的值不能修改。 

        out：表示输出参数，当一个参数被指定为OUT类型时，如果还未调用存储过程之前对该参数进行了赋值，那么在存储过程中该参数的值仍然是null，但是如果在调用过程中对该参数进行赋值，那么值不为null。

        in out：表示输入输出参数，它的值可以修改。

参数的数据类型只需要指明类型名即可，不需要指定宽度。参数的宽度由外部调用者决定。过程可以有参数，也可以没有参数。

我们看到例子中存在一个“”as“”，它表示变量声明块，可以理解为plsql中的declare关键字，用于声明变量。除了as外，还有is。变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。

       其中，as和is的区别：在视图(VIEW)中只能用AS不能用IS；

                                        在游标(CURSOR)中只能用IS不能用AS。

过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。

异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选

结束块：由end关键字结果。

2)、下面讲解一下参数列表中参数的默认值

通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时，就可以省略默认值。

值得注意的是：默认值仅仅支持IN传输类型的参数。OUT 和 IN OUT不能指定默认值

上述情况是default关键字修饰的是最后一个参数，如果是修饰第一个参数呢？

如果我们想使用第一个参数的默认值时，exec procdefault2('aa'); 这样是会报错的。

那怎么变呢？可以指定参数的值。

SQL> exec procdefault2(p2 =>'aa');   这样就OK了，指定aa传给参数p2。

3）、继续讲解存储过程内部块

       我们知道了存储过程的结构，语句块由begin开始，以end结束。这些块是可以嵌套。在语句块中可以嵌套任何以下的块：Declare … begin … exception … end;  

需要注意变量的作用域。  

4）、存储过程中的循环

存储过程的循环语句块有：for...in...loop、while和loop循环。下面分别给予相关实例。

（1）、for...in...loop

实例一 循环遍历游标：

create or replace procedure proc_test

as

cursor c1

is

select * from  dat_trade;

begin

for x in c1

loop

dbms_output.put_line (x.id);

end loop;

end proc_test;

实例二 根据数值进行循环：

create or replace procedure proc_test (v_num in NUMBER)

as

begin

for x in 1..100 loop

dbms_output.put_line (x);

end loop;

end proc_test;

实例三 在过程里指定输入参数 v_num. 在调用过程时指定循环次数：

create or replace procedure proc_test (v_num IN NUMBER)

as

begin

for x in 1 .. v_num

loop

dbms_output.put_line (x);

end loop;

end proc_test;

（2）、loop循环

loop

delete from  orders

where senddate < to_char (add_months (sysdate, -3),'yyyy-mm-dd') and rownum < 1000;

exit when SQL%ROWCOUNT < 1;

commit;

end loop;

这里的 SQL%ROWCOUNT 是隐士游标。 除了这个，还有其他几

个：%found，%notfound， %isopen。

（3）while循环：

create or replace procedure proc_test (v_num in number)

as

i number := 1;

begin

while i < v_num

loop

begin

i := i + 1;

dbms_output.put_line (i);

end;

end loop;

end proc_test;

5）、 存储过程中的判断

存储过程的判断语句块有：if 条件语句、case ... when ... end case两种

下面给出实例：

（1）、单if实例（if...then...end if; && if...then...else...end if;）

实例一：

create or replace procedure pro_test is

--逻辑判断变量

  exit_table_data varchar2(40);  --判断表数据是否存在

--sql语句执行变量

  execu_sql varchar2(2000);

begin

  execu_sql := 'select count(*) from user';

  execute immediate execu_sql into exit_table_data;

  if exit_table_data=0 then

  execu_sql := 'insert into user values('大明')';

  execute immediate execu_sql;

  commit;

  end if;

end pro_test;

实例二：

create or replace procedure pro_test is

--逻辑判断变量

  exit_table_data varchar2(40);  --判断表数据是否存在

--sql语句执行变量

  execu_sql varchar2(2000);

begin

  execu_sql := 'select count(*) from user';

  execute immediate execu_sql into exit_table_data;

  if exit_table_data=0 then

  execu_sql := 'insert into user values('大明')';

  execute immediate execu_sql;

  commit;

  else

  execu_sql:= 'update user set username='大华'';

  execute immediate execu_sql;

  commit;

  end if;

end pro_test;

（2）、多if实例（if...then...elseif...then...else...end if;）

create or replace procedure proc_test (v_num in number)

as

begin

if v_num < 10

then

dbms_output.put_line (v_num);

elseif v_num > 10 and v_num < 50

then

dbms_output.put_line (v_num - 10);

else

dbms_output.put_line (v_num - 50);

end if;

end proc_test;

（2）、case ... when ... end case

实例一：

create or replace procedure proc_test (v_num in number)

as

begin

case v_num

when 1 then

dbms_output.put_line (v_num);

when 2 then

dbms_output.put_line (v_num);

when 3 then

dbms_output.put_line (v_num);

else null;

end case;

end proc_test;

6）、给变量赋值

       我们在参数列表定义输出参数、输入输出参数yi，以及在参数名位置定义参数，可能都需要一个赋值操作，让查询sql的结果赋值或者定义输入参数赋值等等，那么我们可以使用什么方法给这些参数赋值呢？

       下面列举出一些常用的为变量赋值的方法：

       1、直接法

使用“  :=  ” 的符号为变量赋值，例如: v_pare := "0"；

        2、select into

假如变量为v_pare,那么为它赋值的语句为：select count(*) into v_pare from user；

        3、execute immediate 变量名（查询sql语句结果赋值给它的变量）into 变量名

例如：

   v_sqlfalg   := 'select count(*) from user_tables where table_name='''||v_tablename || '''';

   execute immediate v_sqlfalg into v_flag;

   其中，v_tablename、v_sqlfalg、v_flag都是变量；

select into和execute immediate的区别：

       1、execute immediate 赋值的变量是通过select语句查询出来的，而select into是直接赋值给变量的。

7）、存储过程跳出循环

oracle存储过程可以使用3种方法跳出循环，分别是return、exit、continue；

它们的区别为：

        1、return是直接跳出存储过程；

        2、如果存在多层循环，exit是直接跳出存储过程的本次循环，而去执行上一级循环的循环条件；

        3、continue是本次循环后面的代码部分不再执行，转而执行本循环的下一次循环。

8）、Oracle存储过程中是否需要写commit的问题

是否需要在存储过程中写commit主要依据需求:

(1) 如果是不需要在存储过程中进行提交，而是由调用程序负责提交或者回滚，那么不需要在存储过程中commit或者rollback。

(2) 如果不想由调用程序负责提交或者回滚，那么就应该在存储过程中进行commit或rollback; 

另外,如果是纯后台数据库开发，一定要写.只是写的时机同样是分为两种,一种是写在过程里面;另一种是写在调用存储过程之后. 而之所以要写commit的原因是,Oracle的默认事务级别是READ COMMITED;默认情况下，Oracle是不会自动提交的,需要手动提交才ok.

9）、使用plsql创建存储过程步骤

1、登录plsql后，在对象框中找到“Procedures”,点击右键，找到新建，如图所示：

2、进入到新建界面，如图所示：

3、最终就进入到存储过程结构中，你要做的就是编写存储过程逻辑。

10）、使用plsql对存储过程进行调试

1、在“Procedures”下拉列表中找到已经编写好的存储过程，点击右键，找到“测试”，如图所示：

2、PL\SQL会打开调试界面，图中位置1的按钮就是开始调试的按钮，在调试之前要填写输入参数的值，位置2就是填写参数的地方，如果有多个参数，会有多行参数框，按参数名填写相应的参数即可，如果没有参数，可以不填。

3、填写完参数，单击开始调试按钮后，调试的界面会发生一些变化。图中位置1的变化，说明存过已经处于执行状态，别人不能再编译或者执行。位置2的按钮就是执行按钮，单击这个按钮存过会执行完成或者遇到bug跳出，否则是不会停下来的，调试时不会用这个按钮的。位置3的按钮才是关键——单步执行，就是让代码一行一行的执行，位置4的按钮是跳出单步执行，等待下一个指令。

今天的课程就讲解到这里，如果有不懂的地方，或者有建议，麻烦下方留言！

转载于:https://my.oschina.net/u/3872757/blog/1848658