首页 / ORACLE / Oracle存储过程语法
Oracle存储过程语法
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle存储过程语法,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9467字,纯文字阅读大概需要14分钟。
内容图文
Oracle的存储过程语法如下:
create
procedure
存储过程名称(随便取)
is
在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量
begin
执行部分
end
;
(2)带参数的存储过程语法:
create
procedure
存储过程名称(随便取) (变量1 数据类型,变量2 数据类型,...,变量n 数据类型)
is
在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量
begin
执行部分
end
;
(3)带输入、输出参数的存储过程语法:
create
procedure
存储过程名称(随便取) (变量1
in
(或
out
) 数据类型,变量2
in
(或
out
) 数据类型,...,变量n
in
(或
out
) 数据类型)
is
在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量
begin
执行部分
end
;
注意:用上面的语法创建存储过程时可能会碰到数据库中已经有了同名的存储过程,这样Oracle就会弹框报错,说名字已被现有对象使用。解决方法有两种:
方法一:换个存储过程名
方法二:在最开头的create procedure 之间加上 or replace 关键字,例如:create or replace procedure 存储过程名称。但是这种方法不建议使用,因为这种方法会把之前同名的存储过程替换为你当前写的这个
存储过程案例一:没参数的存储过程
create
replace
procedure
procedure_1
is
begin
dbms_output.put_line(
‘procedure_1.......‘
);
end
;
存储过程案例二:带参数的的存储过程
create
procedure
procedure_2(v_i number,v_j number)
is
v_m number(5);
begin
dbms_output.put_line(
‘procedure_2.......‘
);
v_m := v_i + v_j;
dbms_output.put_line(v_i||
‘ + ‘
||v_j||
‘ = ‘
||v_m);
end
;
存储过程案例三:带输入、输出参数的存储过程
存储过程的参数分为输入参数和输出参数,
输入参数:输入参数一般会在变量名和数据类型之间加in来表示该参数是输入参数
输出参数:输出参数一般会在变量名和数据类型之间加out来表示该变量是输出参数
不写in和out的话,默认为输入参数
create
procedure
procedure_3(v_i
in
number,v_j
in
number ,v_m
out
number)
is
begin
dbms_output.put_line(
‘procedure_3.......‘
);
v_m:=v_i - v_j;
dbms_output.put_line(v_i||
‘ - ‘
||v_j||
‘ = ‘
||v_m);
end
;
PL/SQL块中调用存储过程
下面以调用上面三个存储过程为例
declare
v_param1 number(
5
):=
2
;
v_param2 number(
5
):=
8
;
v_result number(
5
);
begin
--调用上面案例一的存储过程
procedure_1();
--调用上面案例二的存储过程
procedure_2(v_param1,v_param2);
--调用上面案例三的存储过程
procedure_3(v_param1,v_param2,v_result);
dbms_output.put_line(v_result);
end;
/*执行结果:*/
procedure_1.......
procedure_2.......
2
+
8
=
10
procedure_3.......
2
-
8
= -
6
10
java调用存储过程
案例一:java调用没有返回值的存储过程
要求:编写一个像数据库emp表插入一条编号为6666,姓名为张三,职位为MANAGER的记录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
/*存储过程*/
create procedure procedure_4(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type )
is
begin
insert into emp (empno,ename,job) values (v_empno,v_ename,v_job);
end;
//java调用存储过程
public
static
void
main(String[] args) {
Connection conn=
null
;
CallableStatement cs=
null
;
ResultSet rs=
null
;
//java调用存储过程
try
{
Class.forName(
"oracle.jdbc.OracleDriver"
);
conn=DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.01:1521:orcl"
,
"scott"
,
"tiger"
);
cs=conn.prepareCall(
"{call procedure_4(?,?,?)}"
);
//给输入参数赋值
cs.setInt(
1
,
6666
);
cs.setString(
2
,
"张三"
);
cs.setString(
3
,
"MANAGER"
);
cs.execute();
//执行
}
catch
(Exception e) {
e.printStackTrace();
}
finally
{
closeResource(conn,cs,rs);
//关闭资源
}
}
//执行后就会向数据库的emp表中插入一条编号为6666,姓名为张三,职位为MANAGER的记录
|
案例二:java调用返回单列单行的存储过程
要求:编写一个根据员工编号查找员工姓名的存储过程,并用java调用该存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
/*存储过程*/
create procedure procedure_5(v_empno in emp.empno%type,v_ename out emp.ename%type)
is
begin
select ename into v_ename from emp where empno=v_empno;
end;
//java调用存储过程
public static void main(String[] args) {
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");
cs=conn.prepareCall("{call procedure_5(?,?)}");
cs.setInt(1, 6666);//给输入参数赋值
/*指定输出参数的数据类型
语法:oracle.jdbc.OracleTypes.输出参数的数据类型
此例输出参数的数据类型是varchar,所以是oracle.jdbc.OracleTypes.VARCHAR*/
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();//执行
//获取输出参数的值,位置要和输出参数对应?的位置对应起来,该例输出参数对应第2个问号,而且输出参数的数据类型为字符型,所以是cs.getString(2)
String a=cs.getString(2);
System.out.println("员工姓名:"+a);
} catch (Exception e) {
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//关闭资源
}
}
/*执行结果,控制台打印:*/
结果:员工姓名:张三
|
案例三:java调用返回单行多列的存储过程
要求:编写一个根据员工编号查找员工姓名、职位和工资的存储过程,并用java调用该存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
/*存储过程*/
create procedure procedure_6(v_empno in emp.empno%type,v_ename out emp.ename%type,v_job out emp.job%type,v_sal out emp.sal%type)
is
begin
select ename,job,sal into v_ename,v_job,v_sal from emp where empno=v_empno;
end;
//java调用存储过程
public static void main(String[] args) {
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");
cs=conn.prepareCall("{call procedure_6(?,?,?,?)}");
cs.setInt(1, 7788);
//指定输出参数的数据类型,注意:顺序要对应起来
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DOUBLE);
cs.execute();//执行
//获取返回值
String ename=cs.getString(2);//获取姓名
String job=cs.getString(3);//获取职位
double sal=cs.getDouble(4);//获取薪水
System.out.println("员工编号为7788的姓名为:"+ename+" 职位是:"+job+" 薪水是:"+sal);
} catch (Exception e) {
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//关闭资源
}
}
/*执行结果,控制台打印:*/
员工编号为
7788
的姓名为:SCOTT 职位是:ANALYST 薪水是:
3000.0
|
案例四:java调用返回多行多列(返回列表)的存储过程
要求:编写一个根据部门编号查找部门所有员工信息的存储过程,并用java调用该存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
/*定义游标*/
create package my_package as
type emp_cursor is ref cursor;
end my_package;
/*存储过程*/
create procedure procedure_7(v_deptno in emp.deptno%type,emp_cursor out my_package.emp_cursor)
is
begin
open emp_cursor
for
select * from emp where deptno=v_deptno;
end;
//java调用存储过程
public
static
void
main(String[] args) {
Connection conn=
null
;
CallableStatement cs=
null
;
ResultSet rs=
null
;
try
{
Class.forName(
"oracle.jdbc.OracleDriver"
);
conn=DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.01:1521:orcl"
,
"scott"
,
"tiger"
);
cs=conn.prepareCall(
"{call procedure_7(?,?)}"
);
cs.setInt(
1
,
20
);
//给输入参数赋值
cs.registerOutParameter(
2
, oracle.jdbc.OracleTypes.CURSOR );
//指定输出参数的数据类型
cs.execute();
rs=(ResultSet) cs.getObject(
2
);
//获取输出参数的值
while
(rs.next()){
//顺序为数据库中字段前后顺序,例如数据库emp表中第5列为hiredate,数据类型为Date,所以获取第5列值时就应该用rs.getDate(5)
System.out.println(rs.getInt(
1
)+
" "
+rs.getString(
2
)+
" "
+rs.getDate(
5
));
}
}
catch
(Exception e) {
e.printStackTrace();
}
finally
{
closeResource(conn,cs,rs);
//关闭资源
}
}
|
/*以下就是20号部门所有员工的信息,这里为方便我们只打印了编号、姓名和入职时间
运行结果,控制台打印:*/
1
2
3
4
5
|
7369
SMITH
1980
-
12
-
17
7566
JONES
1981
-
04
-
02
7788
SCOTT
1987
-
04
-
19
7876
ADAMS
1987
-
05
-
23
7902
FORD
1981
-
12
-
03
|
这是上面java调用存储过程代码中关闭资源方法的代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
public
static
void
closeResource(Connection conn,CallableStatement cs,ResultSet rs){
if
(rs!=
null
){
try
{
rs.close();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
if
(cs!=
null
){
try
{
cs.close();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
if
(conn!=
null
){
try
{
conn.close();
}
catch
(SQLException e) {
e.printStackTrace();
}
}
}
|
最后给个应用,分页的存储过程
分页存储过程:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
/*定义游标*/
create package page_package as
type page_cursor is ref cursor;
end page_package;
/*存储过程*/
create procedure pro_paging (
v_page_size in number,--每页显示多少条
v_page_count out number,--总页数
v_current_page in number,--当前页
v_total_count out number,--记录总条数
emp_cursor out page_package.page_cursor--返回查询结果集的游标
)
is
v_begin number(5):=v_page_size*(v_current_page-1)+1;--查询起始位置
v_end number(5):=v_page_size*v_current_page;--查询结束位置
v_sql varchar2(1000):=‘select empno,ename from
(select a.empno,a.ename,rownum rn from
(select empno,ename from emp) a
where rownum<=‘|| v_end ||‘) b
where b.rn>=‘||v_begin;
/*不能像下面这么写,不然调用该存储过程时会报类型不一致的错,因为最里面查的只有empno,ename,因此外面也要和里面保持一致
v_sql varchar2(1000):=\‘select * from
(select a.*,rownum rn from
(select empno,ename from emp) a
where rownum<=\‘|| v_end ||\‘) b
where b.rn>=‘||v_begin;*/
v_ename varchar2(
10
);
v_empno number(
4
);
begin
open emp_cursor
for
v_sql;
loop
fetch emp_cursor into v_empno,v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line(v_empno||
‘ ‘
||v_ename);
end loop;
v_sql:=
‘select count(empno) from emp‘
;
execute immediate v_sql into v_total_count;
if
(mod(v_total_count,v_page_size)=
0
) then
v_page_count:=v_total_count/v_page_size;
else
v_page_count:=trunc(v_total_count/v_page_size)+
1
;
end
if
;
dbms_output.put_line(
‘共 ‘
||v_total_count||
‘ 条记录‘
);
dbms_output.put_line(
‘共 ‘
||v_page_count||
‘ 页‘
);
dbms_output.put_line(
‘当前页: ‘
||v_current_page);
dbms_output.put_line(
‘每页显示 ‘
||v_page_size||
‘ 条‘
);
end;
|
Java调用的话和上面java调用存储过程的例子一样。这里为了方便 ,就直接在pl/sql中调用了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
/*调用分页存储过程*/
declare
v_page_count number(5);
v_cursor page_package.page_cursor;
v_total_count number(5);
begin
dbms_output.put_line(‘第一页数据。。。。。。。。。‘);
pro_paging(5,--每页显示5条
v_page_count,--总页数
1,--当前页
v_total_count,--记录总条数
v_cursor--游标
);
dbms_output.put_line(‘--------------------------‘);
dbms_output.put_line(‘第二页数据。。。。。。。。。‘);
--显示第二页数据
pro_paging(5,--每页显示5条
v_page_count,--总页数
2,--当前页
v_total_count,--记录总条数
v_cursor--游标
);
end;
/*运行结果:*/
第一页数据。。。。。。。。。
6666
张三
20
empSu2
19
empSave2
7369
SMITH
7499
ALLEN
共
17
条记录
共
4
页
当前页:
1
每页显示
5
条
--------------------------
第二页数据。。。。。。。。。
7521
WARD
7566
JONES
7654
MARTIN
7698
BLAKE
7782
CLARK
共
17
条记录
共
4
页
当前页:
2
每页显示
5
条
|
以上所述是小编给大家介绍的Oracle存储过程及调用,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!
来源:http://www.jb51.net/article/116017.htm
原文:https://www.cnblogs.com/kongxc/p/8685839.html
内容总结
以上是互联网集市为您收集整理的Oracle存储过程语法全部内容,希望文章能够帮你解决Oracle存储过程语法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。