Oracle存储过程
来源:互联网 发布:自学电脑编程入门书 编辑:程序博客网 时间:2024/06/18 18:11
先来一个简单的例子,有个感性认识.
create or replace procedure test is
aa number;
begin
select count(*) into aa from t_server_server;
DBMS_OUTPUT.PUT_LINE(aa);
insert into test_test values(1,'aaa');
commit;
end test;
aa number;
begin
select count(*) into aa from t_server_server;
DBMS_OUTPUT.PUT_LINE(aa);
insert into test_test values(1,'aaa');
commit;
end test;
主要是查询,打印,插入.
以下是转载
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
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.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单步调试
关于oracle存储过程的若干问题备忘
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
可以在该语法之前,先利用select count(*) from 查看数据库中是否存在该记录,如果存在,再利用select...into...
4.在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
假设有一个表A,定义如下:
create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外键
);如果在存储过程中,使用如下语句:
select sum(vcount) into fcount from A where bid='xxxxxx';如果A表中不存在bid="xxxxxx"的记录,则fcount=null(即使fcount定义时设置了默认值,如:fcount number(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里最好先判断一下:
if fcount is null then
fcount:=0;
end if;这样就一切ok了。
6.Hibernate调用oracle存储过程![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockEnd.gif)
Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常。
Oracle存储过程可以有无参数存储过程和带参数存储过程。
一、无参程序过程语法
一、无参程序过程语法
1 create or replace procedure NoParPro
2 as
;
3 begin
4
;
5 exception //存储过程异常
6
;
7 end;
8
2 as
![](http://www.blogjava.net/Images/dot.gif)
3 begin
4
![](http://www.blogjava.net/Images/dot.gif)
5 exception //存储过程异常
6
![](http://www.blogjava.net/Images/dot.gif)
7 end;
8
二、带参存储过程实例
1 create or replace procedure queryempname(sfindno emp.empno%type) as
2 sName emp.ename%type;
3 sjob emp.job%type;
4 begin
5 ....
7 exception
....
14 end;
15
2 sName emp.ename%type;
3 sjob emp.job%type;
4 begin
5 ....
7 exception
....
14 end;
15
三、 带参数存储过程含赋值方式
1 create or replace procedure runbyparmeters (isal in emp.sal%type,
sname out varchar,sjob in out varchar)
2 as icount number;
3 begin
4 select count(*) into icount from emp where sal>isal and job=sjob;
5 if icount=1 then
6 ....
9 else
10 ....
12 end if;
13 exception
14 when too_many_rows then
15 DBMS_OUTPUT.PUT_LINE('返回值多于1行');
16 when others then
17 DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错!');
18 end;
19
sname out varchar,sjob in out varchar)
2 as icount number;
3 begin
4 select count(*) into icount from emp where sal>isal and job=sjob;
5 if icount=1 then
6 ....
9 else
10 ....
12 end if;
13 exception
14 when too_many_rows then
15 DBMS_OUTPUT.PUT_LINE('返回值多于1行');
16 when others then
17 DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错!');
18 end;
19
四、在Oracle中对存储过程的调用
过程调用方式一
1 declare
2 realsal emp.sal%type;
3 realname varchar(40);
4 realjob varchar(40);
5 begin //存储过程调用开始
6 realsal:=1100;
7 realname:='';
8 realjob:='CLERK';
9 runbyparmeters(realsal,realname,realjob); --必须按顺序
10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
11 END; //过程调用结束
12
2 realsal emp.sal%type;
3 realname varchar(40);
4 realjob varchar(40);
5 begin //存储过程调用开始
6 realsal:=1100;
7 realname:='';
8 realjob:='CLERK';
9 runbyparmeters(realsal,realname,realjob); --必须按顺序
10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
11 END; //过程调用结束
12
过程调用方式二
1 declare
2 realsal emp.sal%type;
3 realname varchar(40);
4 realjob varchar(40);
5 begin //过程调用开始
6 realsal:=1100;
7 realname:='';
8 realjob:='CLERK';
9 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); --指定值对应变量顺序可变
10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
11 END; //过程调用结束
12
2 realsal emp.sal%type;
3 realname varchar(40);
4 realjob varchar(40);
5 begin //过程调用开始
6 realsal:=1100;
7 realname:='';
8 realjob:='CLERK';
9 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); --指定值对应变量顺序可变
10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
11 END; //过程调用结束
12
至此,有关ORACLE的基本存储过程以及对Oracle存储过程的调用方式介绍完毕。
- Oracle:存储过程,存储函数
- Oracle-存储过程 存储函数
- Oracle存储过程、存储函数
- oracle 存储函数,存储过程
- Oracle存储过程
- 调试oracle存储过程。
- ORACLE 存储过程实战
- Oracle 存储过程
- oracle存储过程使用
- Oracle 创建存储过程
- 调试oracle存储过程
- oracle java存储过程
- 创建Oracle存储过程
- 加密oracle存储过程
- oracle存储过程分页
- oracle存储过程笔记
- ORACLE存储过程--注意事项
- oracle procedure 存储过程
- 自绘按钮响应单击消息
- MyEclipse 8.5 官方下载地址及其注册码
- 关于在对话框OnInitDialog 使用MoveWindow
- Tomcat 5/6 GBK 编码下完美解决中文表单问题的过滤器
- Good luck 2011!!
- Oracle存储过程
- InStr([start, ]string1, string2[, compare])
- JAVA八荣八耻
- AfxBeginThread 传参
- VC++程序从OpenCV2.1迁移到OpenCV2.2(vs2010+OpenCV2.2+windows7设置)
- 使用一个CWnd空闲池创建一个动态用户界面
- C#.NET 消息机制
- RRDtool 简介
- 转:开荒者Oracle 10g ORA-12154: TNS: could not resolve the connect identifier specified 问题解决!