Oracle存储过程的调用和执行
1、什么是存储过程:
用于在数据库中完成特定的操作或者任务。是一个PLSQL程序块,可以永久的保存在数据库中以供其他程序调用。
2、无参存储过程的使用:
Normal
0
7.8 磅
0
2
false
false
false
EN-US
ZH-CN
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:11.0pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
1. CREATE OR REPLACE PROCEDURE 过程名 [(parameter,...)]
2. IS
3. 定义变量
4. Begin
5. Plsql程序
6. End;
Normal
0
7.8 磅
0
2
false
false
false
EN-US
ZH-CN
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:11.0pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
1. 例:创建一个存储过程,用于向数据库中插入一条记录。
2. 第一步:创建
3. CREATE OR REPLACE PROCEDURE pro_1
4. IS
5. Begin
6. insert into person values (11,'aa','aav');
7. End;
8.
9. 第二步:在sql*plus中执行该过程
10.exec pro_1;
11.
12.第三步:通过JDBC使用存储过程。
13. private Connection conn = null;
14. private ResultSet rs = null;
15. private CallableStatement state = null;
16. //调用一个无参数的存储过程
17. public void testPro()
18. {
19. conn = Tools.getConnection();
20. try {
21. state = conn.prepareCall("{call pro_1}");
22. state.execute();
23. } catch (Exception e) {
24. // TODO Auto-generated catch block
25. e.printStackTrace();
26. }
27. }
3、带有IN类型参数的存储过程的使用
Normal
0
7.8 磅
0
2
false
false
false
EN-US
ZH-CN
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:11.0pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
1. 例:创建一个存储过程,用于向数据库中插入一条记录。
2. 第一步:创建
3. CREATE OR REPLACE PROCEDURE pro_2(id number,name varchar2,email varchar2)
4. IS
5. Begin
6. insert into person values (id,name,email);
7. End;
8. 第二步:在sql*plus中执行该过程
9. exec pro_2(12,'aaa','aaa');
10.
11.第三步:通过JDBC使用存储过程。
12. //使用一个带有 IN 类型参数的存储过程
13. public void testPro_in(int id,String name,String email)
14. {
15. conn = Tools.getConnection();
16. try {
17. state = conn.prepareCall("{call pro_2(?,?,?)}");
18. state.setLong(1, id);
19. state.setString(2, name);
20. state.setString(3, email);
21. state.execute();
22. } catch (Exception e) {
23. // TODO Auto-generated catch block
24. e.printStackTrace();
25. }
26. }
4、带有OUT类型参数的存储过程的使用
Normal
0
7.8 磅
0
2
false
false
false
EN-US
ZH-CN
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:11.0pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
1. 例:创建一个存储过程,用于返回数据库中的Person表的总行数。
2.
3. 第一步:创建
4. CREATE OR REPLACE PROCEDURE pro_3(num out number)
5. IS
6. mynum number;
7. Begin
8. select count(*) into mynum from person;
9. num := mynum;
10.End;
11.或者
12.CREATE OR REPLACE PROCEDURE pro_3(num out number)
13.IS
14.Begin
15. select count(*) into num from person;
16.End;
17.
18.第二步:在sql*plus中执行该过程
19.declare
20.a number;
21.begin
22. pro_3(a);
23. dbms_output.put_line(a);
24.end;
25.
26.第三步:通过JDBC使用存储过程。
27.public void testPro_out()
28. {
29. conn = Tools.getConnection();
30. try {
31. state = conn.prepareCall("{call pro_3(?)}");
32. state.registerOutParameter(1, Types.NUMERIC);
33. state.execute();
34. int num = state.getInt(1);
35. System.out.println(num);
36. } catch (Exception e) {
37. // TODO Auto-generated catch block
38. e.printStackTrace();
39. }
40. }
Normal
0
7.8 磅
0
2
false
false
false
EN-US
ZH-CN
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:11.0pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
5、带有IN-OUT类型参数的存储过程的使用
Normal
0
7.8 磅
0
2
false
false
false
EN-US
ZH-CN
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:11.0pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
1. 创建:
2. CREATE OR REPLACE PROCEDURE pro_4(num in out number)
3. IS
4. a number := 100;
5. Begin
6. num := a*num;
7. End;
8.
9. 在sql*plus中执行该过程
10.declare
11. a number := 12;
12.begin
13. pro_4(a);
14. dbms_output.put_line(a);
15.end;
Normal
0
7.8 磅
0
2
false
false
false
EN-US
ZH-CN
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:11.0pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
最新文章
- 设计模式:Context模式
- Linux内核中的fastcall和asmlinkage宏
- Insert Function before and after main function
- jQuery.Validate验证库详解
- bzoj 1588营业额统计(HNOI 2002)
- Java 文件IO
- Android实现SharePreferences和AutoCompletedTextView
- HTML5验证及日期显示
- Windows 系统版本判断
- 全面解读Java NIO工作原理(4)
- Go语言打造以太坊智能合约测试框架(level1)
- 有关 Azure 机器学习的 Net# 神经网络规范语言的指南
- 通过$broadcast或$emit在子级和父级controller之间进行值传递
- MySQL报错: Character set ‘utf8mb4‘ is not a compiled character set and is not specified in the ‘/usr/share/mysql/charsets/Index.xml‘ file
- Json 简记
- JavaScript -基础- 函数与对象(三)Date对象
- npm是什么
- Django的rest_framework的视图之Mixin类编写视图源码解析
- shell之三大文本处理工具grep、sed及awk
- ASP.NET 网站超时跳转到登录界面