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

前言~

      今天莫名的接到一个任务,需要使用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

  • 1
    点赞
  • 30
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值