Oracle自定义函数与存储过程

一、Oracle自定义函数

1、语法

create or replace function 函数名(参数1 模式 参数类型)  
return 返回值类型  
as  
变量1 变量类型;  
变量2 变量类型;  
begin  
    函数体;  
end 函数名;

2、示例

create or replace function cvt_latlon_single(valstr in varchar2)
return varchar2
as
degree varchar2(35 CHAR);
minute varchar2(35 CHAR);
seconds varchar2(35 CHAR);
result varchar2(35 CHAR);
begin
  degree := substr(valstr,instr(valstr,'°') - 1);
  minute := substr(valstr,'°') + 1,'′') - 1 - instr(valstr,'°'));
  seconds := substr(valstr,'′') + 1,'″') - 1 - instr(valstr,'′'));
  result := degree + trunc(minute * (1/60),10) + trunc(seconds * (1/3600),10) ;
  return result;
end cvt_latlon_single;

二、Oracle存储过程

1、语法

1.基本结构 
CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 存储过程名字

2.SELECT INTO STATEMENT
  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
  记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
  例子： 
  BEGIN
  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;
  ...

3.IF 判断
  IF V_TEST=1 THEN
    BEGIN 
       do something
    END;
  END IF;

4.while 循环
  WHILE V_TEST=1 LOOP
  BEGIN
 XXXX
  END;
  END LOOP;

5.变量赋值
  V_TEST := 123;

6.用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;

7.带参数的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;

8.用pl/sql developer debug
  连接数据库后建立一个Test WINDOW
  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

2、示例

/*不带任何参数存储过程(输出系统日期)*/
create or replace procedure output_date is
begin
dbms_output.put_line(sysdate);
end output_date;

/*带参数in和out的存储过程*/
create or replace procedure get_username(v_id in number,v_username out varchar2)
as
begin
  select username into v_username from tab_user where id = v_id; --变量赋值 
exception
when no_data_found then 
raise_application_error(-20001,'ID不存在!');
end get_username;

三、自定义函数与存储过程

1、函数和存储过程的优点

1、共同使用的代码可以只需要被编写一次，而被需要该代码的任何应用程序调用（.net,c++,java，也可以使DLL库）。

2、这种几种编写、几种维护更新、大家共享的方法，简化了应用程序的开发维护，提高了效率和性能。

3、这种模块化的方法使得一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分，进行分别编写，因此程序的结构更加清晰，简单，也容易实现。

4、可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。

5、节省内存空间。它们以一种压缩的形式被存储在外存中，当被调用时才被放入内存进行处理。而且多个用户在调用同一个存储过程或函数时，只需要加载一次即可。

6、提高数据的安全性和完整性。通过把一些对数据的操作方到存储过程或函数中，就可以通过是否授予用户有执行该语句的权限，来限制某些用户对数据库进行这些操作。

2、函数和存储过程的区别

1、存储过程用户在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据。

2、存储过程声明用procedure，函数用function。

3、存储过程不需要返回类型，函数必须要返回类型。

4、存储过程可作为独立的pl-sql执行，函数不能作为独立的plsql执行，必须作为表达式的一部分。

5、存储过程只能通过out和in/out来返回值，函数除了可以使用out，in/out以外，还可以使用return返回值。

6、sql语句（DML或SELECT)中不可用调用存储过程，而函数可以。

3、适用场合

1、如果需要返回多个值和不返回值，就使用存储过程；如果只需要返回一个值，就使用函数。

2、存储过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

3、可以再SQL内部调用函数来完成复杂的计算问题，但不能调用存储过程。

参考地址：https://www.cnblogs.com/zhengcheng/p/4220924.html

http://blog.csdn.net/u012618337/article/details/41727103

https://www.cnblogs.com/nicholas_f/articles/1526029.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!