Orcale 存储过程

1.oracle存储过程结构 

CREATE OR REPLACE PROCEDURE   oracle存储过程名字

(

    参数1 IN NUMBER,----参数是存储过程中的所需要传递的值，如java的中的方法参数

    参数2 IN NUMBER

) AS

变量1 INTEGER :=0;  ----变量赋值的方式：变量名 := 值; 

变量2 DATE;  --声明变量(变量名 变量类型) 

BEGIN

------存储过程的执行体 

END oracle存储过程名字

存储过程的基本写法如上，此与建表语句类似，存储过程中变量的声明，不声明变量，在下面使用时会出现编译错误。

2.无返回值的oracle存储过程

create or replace procedure 存储过程的名字 as

begin

  insert into xuesheng values (3, 'wangwu', 90, 90);---其中xuesheng是一个表的名字即数据库的插入语句

  commit;--提交事务

end 存储过程的名字;

3.有单个数据值返回的oracle存储过程

create or replace procedure xs_proc(temp_name in varchar2,

                                    temp_num  out number) is

  num_1 number;

  num_2 number;

begin

  select yu_wen, shu_xue

    into num_1, num_2

    from xuesheng

   where xing_ming = temp_name;

  --dbms_output.put_line(num_1 + num_2);

  temp_num := num_1 + num_2;

end;

output是在存储过程中的参数的返回值(输出参数)，而ReturnValue是存储过程返回的值(使用return关键字)，一个存储过程可以有任意多个依靠参数返回的值，但只有一个ReturnValue。

其中，以上两种与sql server基本类似，而对于返回数据集时，上述方法则不能满足我们的要求。在Oracle中，一般使用ref cursor来返回数据集。

4.有返回值的oracle存储过程(列表返回)

首先，建立我们自己的包。并定义包中的一个自定义ref cursor

create or replace package mypackage as

  type my_cursor is ref cursor;

end mypackage;

在定义了ref cursor后，可以书写我们的程序代码

create or replace procedure xs_proc_list(shuxue   in number,

                                         p_cursor out mypackage.my_cursor) is

begin

  open p_cursor for

    select * from xuesheng where shu_xue > shuxue;

end xs_proc_list;

5.SELECT INTO STATEMENT

  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条

  记录，否则抛出异常(若没有记录则抛出NO_DATA_FOUND)

  例子： 

  BEGIN

  SELECT name,age into 变量1,变量2 FROM user where xxx;

  EXCEPTION

  WHEN NO_DATA_FOUND THEN

      xxxx;

  END;

  ...

6.IF 判断

  IF V_TEST=1 THEN

    BEGIN 

       do something

    END;

  END IF;

此事例有些简单，看以下比较容易明白

if 比较式 then begin end; end if; 
E.g 
create or replace procedure test(x in number) is 
begin 
if x >0 then 
begin 
x := 0 - x; 
end; 
end if; 
if x = 0 then 
begin 
x: = 1; 
end; 
end if; 
end test; ------if语句中需要注意的判断条件不用加()每个if语句结束之后需要加endif来结束否则将语法错误

 

7.WHILE 循环

  WHILE V_TEST=1 LOOP

  BEGIN

 XXXX

  END;

  END LOOP;

while 条件语句 LOOP 
begin 
end; 
end LOOP; 
E.g 
create or replace procedure test(i in number) as 
begin 
while i < 10 LOOP 
begin 
i:= i + 1; 
end; 
end LOOP; 
end test; 

 

8.变量赋值

  V_TEST := 123;

 

9.用FOR IN 使用cursor

  ...

  IS

  CURSOR cur IS SELECT * FROM xxx;

  BEGIN

 FOR cur_result in cur LOOP

  BEGIN

   V_SUM :=cur_result.列名1+cur_result.列名2

  END;

 END LOOP;

  END;

(1)循环遍历游标 
create or replace procedure test() as 
Cursor cursor is select name from student; name varchar(20); 
begin 
for name in cursor LOOP 
begin 
dbms_output.putline(name); 
end; 
end LOOP; 
end test; 
(2)循环遍历数组 
create or replace procedure test(varArray in myPackage.TestArray) as 
--(输入参数varArray 是自定义的数组类型，定义方式见标题6) 
i number; 
begin 
i := 1; --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张 
--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历 
for i in 1..varArray.count LOOP 
dbms_output.putline(&apos;The No.&apos;|| i || &apos;record in varArray is:&apos;||varArray(i)); 
end LOOP; 
end test; 

 

10.带参数的CURSOR

  CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

  OPEN C_USER(变量值);

  LOOP

 FETCH C_USER INTO V_NAME;

 EXIT FETCH C_USER%NOTFOUND;

    do something

  END LOOP;

  CLOSE C_USER;

 

11.用pl/sql developer debug

  连接数据库后建立一个Test WINDOW

  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试