[oracle] 存储过程基础

古城微笑少年丶 2022-05-15 11:36 202阅读 0赞

转载：[/images/20220515/76992159bd694678b980d5573be8d17a.png][http_wen866595.iteye.com_blog_1733887]

差不多一年没写过存储过程，最近要写，发现基本忘了，google一番之后，觉得很有必要把基础的东西写下来备忘。

*  语句块定义：

Sql代码 [![收藏代码][icon_star.png]][http_wen866595.iteye.com_blog_1733887]

1.  decalre 
2.  \-- 变量声明
3.  var1 number(2); \-- 仅声明
4.  var2 **char**(2) := '11'; \-- 在声明的同时初始化
5.  
6.  **begin**
7.  \-- 语句
8.  **end**; \-- 语句块结束

*  if 语句

Sql代码 [![收藏代码][icon_star.png]][http_wen866595.iteye.com_blog_1733887]

1.  if a = 1 or b = 2 **then**
2.  
3.  elsif c = 3 **then**
4.  
5.  **else**
6.  
7.  **end** if;

*  **for 循环**

for循环主要有两个用处。

1、 循环一个范围  
格式：for i in \[start .. end\] loop ... end loop;

Sql代码 [![收藏代码][icon_star.png]][http_wen866595.iteye.com_blog_1733887]

1.  **for** i in 0..9 loop 
2.   dbms\_output.put\_line('i:' || i); 
3.  **end** loop;

2、遍历隐式游标  
  
隐式游标的好处是不需要手动关闭，方便

Sql代码 [![收藏代码][icon_star.png]][http_wen866595.iteye.com_blog_1733887]

1.  **for** currow in ( 
2.  **select** t.col1, t.col2 
3.  **from** tableName t 
4.  **where** ... 
5.  ) loop 
6.   if currow.col1 = 0 **then**
7.  **return**; \-- 中止sp，返回
8.  **end** if; 
9.  **end** loop;

*  while 循环

Sql代码 [![收藏代码][icon_star.png]][http_wen866595.iteye.com_blog_1733887]

1.  isok := 9; 
2.  while isok >= 0 loop 
3.   isok := isok - 1; 
4.  
5.   if isok = 8 **then**
6.  **continue**; \-- 与编程语言的 continue 语义一样，跳过当前循环的剩余语句，回到循环开始
7.  **end** if; 
8.  
9.   if isok = 4 **then**
10.  exit; \-- 与编程语言的 break 语义一样，跳出循环
11. **end** if; 
12. 
13.  dbms\_output.put\_line('isok:' || isok); 
14. **end** loop; 
15. 
16. dbms\_output.put\_line('outside while loop .');

*  存储过程定义

Sql代码 [![收藏代码][icon_star.png]][http_wen866595.iteye.com_blog_1733887]

1.  **create** or replace **procedure** sp\_name ( 
2.  \-- 入参、出参列表， 逗号分隔。
3.   uid in varchar2, \-- 不能带长度信息
4.   startDate in **date**, \-- 第二个输入参数
5.   defaultVar in varchar2 **default** "", \-- 默认参数，如果不传，要注意参数的顺序
6.   isok **out** number, \-- 输出参数
7.   result **out** varchar2 \-- 第二个输出参数
8.  ) 
9.  **as**
10. \-- 变量声明，每个声明用分号结束。可以在声明的同时初始化
11. var1 varchar2(11); 
12. var2 number(2) := 123; 
13. 
14. **begin**
15. \-- 字符串拼接用 ||
16.  dbms\_output.put\_line('isok:' || 'abc'); 
17. 
18. \-- 调用其他存储过程
19.  sub\_sp\_name(param1, prarm2, outParam1, outParam2); 
20. 
21. **end**; \-- 存储过程结束

*  函数定义

Sql代码 [![收藏代码][icon_star.png]][http_wen866595.iteye.com_blog_1733887]

1.  **create** or replace **function** func ( 
2.  \-- 入参、出参列表， 逗号分隔。
3.   uid in varchar2, \-- 不能带长度信息
4.   startDate in **date**, \-- 第二个输入参数
5.   defaultVar in varchar2 **default** "", \-- 默认参数，如果不传，要注意参数的顺序
6.   isok **out** number, \-- 输出参数
7.   result **out** varchar2 \-- 第二个输出参数
8.  ) 
9.  **return** number \-- 定义返回类型
10. **as**
11. \-- 变量声明，每个声明用分号结束。可以在声明的同时初始化
12. var1 varchar2(11); 
13. var2 number(2) := 123; 
14. 
15. **begin**
16. \-- 字符串拼接用 ||
17.  dbms\_output.put\_line('isok:' || 'abc'); 
18. 
19. 
20. **return** ret\_val; 
21. **end**;

*  存储过程与函数异同

1、两者定义类似，都可以带输入输出参数。  
2、函数有返回值，存储过程没有。  
3、函数的调用要在select语句里；而存储过程不用，可以独立调用。

*  游标

**隐式游标**  
隐式游标的好处是不需要手动关闭，方便

Sql代码 [![收藏代码][icon_star.png]][http_wen866595.iteye.com_blog_1733887]

**显式游标**

Sql代码 [![收藏代码][icon_star.png]][http_wen866595.iteye.com_blog_1733887]

1.  **declare**
2.  isok **integer**; 
3.  v\_event\_id number(10); 
4.  v\_isagain number(2); 
5.  v\_rate number(2); 
6.  
7.  v\_sender **char**(11) := '13800138000'; 
8.  
9.  **cursor** cursorVar **is** **select** event\_id, isagain, rate **from** call\_event **where** sender = v\_sender; \-- 声明游标
10. 
11. 
12. **begin**
13. **open** cursorVar; \-- 打开游标
14.  loop 
15. **fetch** cursorVar **into** v\_event\_id, v\_isagain, v\_rate; \-- 取值
16.  exit **when** cursorVar%notfound; \--当没有记录时退出循环
17.  dbms\_output.put\_line(v\_event\_id || ', ' || v\_isagain || ', ' || v\_rate); 
18. **end** loop; 
19. 
20. **close** cursorVar; \-- 关闭游标
21. 
22. \--游标的属性有：%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT; 
23. \--%FOUND:已检索到记录时，返回true 
24. \--%NOTFOUNRD：检索不到记录时，返回true 
25. \--%ISOPEN:游标已打开时返回true 
26. \--%ROWCOUNT:代表检索的记录数，从1开始 
27. **end**;

**带参数游标**

Sql代码 [![收藏代码][icon_star.png]][http_wen866595.iteye.com_blog_1733887]

1.  **declare**
2.  isok **integer**; 
3.  v\_event\_id number(10); 
4.  v\_isagain number(2); 
5.  v\_rate number(2); 
6.  
7.  v\_sender **char**(11) := '13800138000'; 
8.  
9.  **cursor** cursorVar(p\_sender varchar2) **is** **select** event\_id, isagain, rate **from** call\_event **where** sender = p\_sender; \-- 声明游标
10. 
11. **begin**
12. **open** cursorVar(v\_sender); \-- 打开游标，在括号里传参。
13.  loop 
14. **fetch** cursorVar **into** v\_event\_id, v\_isagain, v\_rate; \-- 取值
15.  exit **when** cursorVar%notfound; \--当没有记录时退出循环
16.  dbms\_output.put\_line(v\_event\_id || ', ' || v\_isagain || ', ' || v\_rate); 
17. **end** loop; 
18. 
19. **close** cursorVar; \-- 关闭游标
20. **end**;

[http_wen866595.iteye.com_blog_1733887]: /images/20220515/76992159bd694678b980d5573be8d17a.png
[icon_star.png]: http://wen866595.iteye.com/images/icon_star.png