Oracle自定义函数与存储过程

前端之家收集整理的这篇文章主要介绍了Oracle自定义函数与存储过程前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

一、Oracle自定义函数

1、语法

  1. create or replace function 函数名(参数1 模式 参数类型)
  2. return 返回值类型
  3. as
  4. 变量1 变量类型;
  5. 变量2 变量类型;
  6. begin
  7. 函数体;
  8. end 函数名;

2、示例

  1. create or replace function cvt_latlon_single(valstr in varchar2)
  2. return varchar2
  3. as
  4. degree varchar2(35 CHAR);
  5. minute varchar2(35 CHAR);
  6. seconds varchar2(35 CHAR);
  7. result varchar2(35 CHAR);
  8. begin
  9. degree := substr(valstr,instr(valstr,'°') - 1);
  10. minute := substr(valstr,'°') + 1,'′') - 1 - instr(valstr,'°'));
  11. seconds := substr(valstr,'′') + 1,'″') - 1 - instr(valstr,'′'));
  12. result := degree + trunc(minute * (1/60),10) + trunc(seconds * (1/3600),10) ;
  13. return result;
  14. end cvt_latlon_single;

二、Oracle存储过程

1、语法

  1. 1.基本结构
  2. CREATE OR REPLACE PROCEDURE 存储过程名字
  3. (
  4. 参数1 IN NUMBER,参数2 IN NUMBER
  5. ) IS
  6. 变量1 INTEGER :=0;
  7. 变量2 DATE;
  8. BEGIN
  9. END 存储过程名字
  10.  
  11. 2.SELECT INTO STATEMENT
  12. select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条
  13. 记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
  14. 例子:
  15. BEGIN
  16. SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
  17. EXCEPTION
  18. WHEN NO_DATA_FOUND THEN
  19. xxxx;
  20. END;
  21. ...
  22.  
  23. 3.IF 判断
  24. IF V_TEST=1 THEN
  25. BEGIN
  26. do something
  27. END;
  28. END IF;
  29.  
  30. 4.while 循环
  31. WHILE V_TEST=1 LOOP
  32. BEGIN
  33. XXXX
  34. END;
  35. END LOOP;
  36.  
  37. 5.变量赋值
  38. V_TEST := 123;
  39.  
  40. 6.for in 使用cursor
  41. ...
  42. IS
  43. CURSOR cur IS SELECT * FROM xxx;
  44. BEGIN
  45. FOR cur_result in cur LOOP
  46. BEGIN
  47. V_SUM :=cur_result.列名1+cur_result.列名2
  48. END;
  49. END LOOP;
  50. END;
  51.  
  52. 7.带参数的cursor
  53. CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
  54. OPEN C_USER(变量值);
  55. LOOP
  56. FETCH C_USER INTO V_NAME;
  57. EXIT FETCH C_USER%NOTFOUND;
  58. do something
  59. END LOOP;
  60. CLOSE C_USER;
  61.  
  62. 8.pl/sql developer debug
  63. 连接数据库后建立一个Test WINDOW
  64. 在窗口输入调用SP代码,F9开始debug,CTRL+N单步调试

2、示例

  1. /*不带任何参数存储过程(输出系统日期)*/
  2. create or replace procedure output_date is
  3. begin
  4. dbms_output.put_line(sysdate);
  5. end output_date;
  6.  
  7. /*带参数in和out的存储过程*/
  8. create or replace procedure get_username(v_id in number,v_username out varchar2)
  9. as
  10. begin
  11. select username into v_username from tab_user where id = v_id; --变量赋值
  12. exception
  13. when no_data_found then
  14. raise_application_error(-20001,'ID不存在!');
  15. end get_username;

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

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

  1. 1、共同使用的代码可以只需要被编写一次,而被需要该代码的任何应用程序调用(.net,c++,java,也可以使DLL库)。
  2.  
  3. 2、这种几种编写、几种维护更新、大家共享的方法,简化了应用程序的开发维护,提高了效率和性能
  4.  
  5. 3、这种模块化的方法使得一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分,进行分别编写,因此程序的结构更加清晰,简单,也容易实现。
  6.  
  7. 4、可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。
  8.  
  9. 5、节省内存空间。它们以一种压缩的形式被存储在外存中,当被调用时才被放入内存进行处理。而且多个用户调用同一个存储过程或函数时,只需要加载一次即可。
  10.  
  11. 6、提高数据的安全性和完整性。通过把一些对数据的操作方到存储过程或函数中,就可以通过是否授予用户有执行该语句的权限,来限制某些用户数据库进行这些操作。

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

  1. 1、存储过程用户数据库中完成特定操作或者任务(如插入,删除等),函数用于返回特定的数据。
  2.  
  3. 2、存储过程声明用procedure函数function
  4.  
  5. 3、存储过程不需要返回类型,函数必须要返回类型。
  6.  
  7. 4、存储过程可作为独立的pl-sql执行,函数不能作为独立的plsql执行,必须作为表达式的一部分。
  8.  
  9. 5、存储过程只能通过outin/out来返回值,函数除了可以使用outin/out以外,还可以使用return返回值。
  10.  
  11. 6sql语句(DMLSELECT)中不可用调用存储过程,而函数可以。

3、适用场合

  1. 1、如果需要返回多个值和不返回值,就使用存储过程;如果只需要返回一个值,就使用函数
  2.  
  3. 2、存储过程一般用于执行一个指定的动作,函数一般用于计算和返回一个值。
  4.  
  5. 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

猜你在找的Oracle相关文章