oracle存储过程Word文档下载推荐.docx
- 文档编号:16837967
- 上传时间:2022-11-26
- 格式:DOCX
- 页数:13
- 大小:25.30KB
oracle存储过程Word文档下载推荐.docx
《oracle存储过程Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《oracle存储过程Word文档下载推荐.docx(13页珍藏版)》请在冰豆网上搜索。
p2VARCHAR2(100);
p3VARCHAR2(100);
BEGIN
p1:
='
dd'
;
p2:
3'
testprocedure(p1,p2,'
'
);
END;
Oracle存储过程的基本语法
基本结构
CREATEORREPLACEPROCEDURE存储过程名字
(
参数1INNUMBER,
参数2OUTNUMBER
)IS
变量1INTEGER:
=0;
//变量的生命并初始化
变量2DATE;
END存储过程名字
说明:
(1)存储过程参数不带取值范围,in表示传入,out表示输出,inout表示输入输出参数。
(存储过程的返回值是以参数形式返回的)
(2)变量带可以取值范围,后面接分号
(3)IS可以用AS代替
(4)当存储过程没有参数时,可以不带后面的括号,调用时候也可以不带。
(5)oracle声明变量时,变量名称在前面,变量类型在后面
(6)变量只能在声明块定义,不能在plsql块中定义。
(plsql块就是begin和endprocedurename之间的部分)
SELECT...INTOvarnameFROM...给变量赋值
将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子:
BEGIN
SELECTcol1,col2into变量1,变量2FROMtypestructwherexxx;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
xxxx;
END;
...
通配类型操作符
1、tab.fieldname%type:
通配表的某个字段的类型,如
mynametab1.name%type;
变量myname的类型就是tab1中的name的类型。
2、tab%rowtype:
通配表的所有字段类型(实际上相对于一个对象类型)
usetab1%rowtype;
use就是一个对象(它里面的属性就是tab1表的所有字段)
IF判断
IFV_TEST=1THEN
BEGIN
dosomething
ENDIF;
while循环
WHILEV_TEST=1LOOP
XXXX
ENDLOOP;
变量赋值
V_TEST:
=123;
打印信息
使用内置对象DBMS_OUTPUT的put_line方法打印信息:
DBMS_OUTPUT.put_line('
xx'
字符串
Oracle存储过程中字符串只能用单引号
for..in..循环遍历数组
在Oracle中本是没有数组的概念的,数组类型需要我们自己去定义(用Table定义),每个数组元素就是表中的一个记录,所以遍历数组时就相当于从表中的第一条记录开始遍历。
//array是myPackage下定义的TestArray类型(该类型是用Table定义的)
createorreplaceproceduretest(myarrayinmyPackage.TestArray)as
inumber;
begin
i:
=1;
foriin1..myarray.countLOOP
dbms_output.putline(i);
endLOOP;
endtest;
立即执行sql语句
EXECUTEIMMEDIATEsqlclause
用pl/sqldeveloperdebug
连接数据库后建立一个TestWINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
游标(cursor)
Oracle中Cursor是非常有用的,它是从表中检索出结果集,一般用于遍历临时表中的查询结果。
游标分类(类型):
1、隐示游标;
1).对于Select…INTO…语句,一次只能从数据库中获取到一条数据,对于这种类型的DMLSql语句,就是隐式Cursor。
例如:
Select/Update/Insert/Delete操作。
2)作用:
可以通过隐式Cusor的属性来了解操作的状态和结果,从而达到流程的控制。
Cursor的属性包含:
SQL%ROWCOUNT整型代表DML语句成功执行的数据行数
SQL%FOUND
布尔型
值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND布尔型与SQL%FOUND属性返回值相反
SQL%ISOPEN布尔型DML执行过程中为真,结束后为假
3)隐式Cursor是系统自动打开和关闭Cursor.
4)可以用作参数被传递
Begin
UpdateempSetSAL=SAL+0.1WhereJOB='
CLERK'
IfSQL%FoundThen
DBMS_OUTPUT.PUT_LINE('
已经更新!
Else
更新失败!
EndIf;
End;
2、显示游标-CURSOR
对于从数据库中提取多行数据,就需要使用显式Cursor。
显式Cursor的属性包含:
游标的属性
返回值类型
意
义
%ROWCOUNT
整型
获得FETCH语句返回的数据行数
%FOUND
布尔型最近的FETCH语句返回一行数据则为真,否则为假
%NOTFOUND
布尔型与%FOUND属性返回值相反
%ISOPEN布尔型游标已经打开时值为真,否则为假
不能用作参数被传递
3.REF游标(动态Cursor)
显式游标的使用
1、声明游标:
cursor
游标名(参数名类型)
is
select语句;
参数是可选部分,所定义的参数可以出现在SELECT语句的WHERE子句中。
如果定义了参数,则必须在打开(open)游标时传递相应的实际参数。
2、打开游标:
open
游标名(实参);
//参数可选
3、移动游标,并获取游标所在行数据到变量中:
fetch
游标名
into
变量;
//注意变量的类型要自己定义
4、游标语法:
close
游标名;
遍历循环游标
1、For循环游标
循环自动打开游标,自动滚动获取一条记录,并自动创建临时记录类型变量存储记录。
处理完后自动关闭游标。
For变量名In游标名--此处的变量可以是隐式变量(不需要定义)
Loop
数据处理语句;
EndLoop;
---以下是遍历person表的例子
createorreplaceprocedureaais
Cursormycurisselect*fromperson;
pperson%rowtype;
forpinmycurLoop
DBMS_OUTPUT.PUT_LINE(p.name);
endloop;
endaa;
2、Loop循环显示游标
Fatch游标名InTo变量;
ExitWhen游标名%NotFound;
自定义定义游标类型:
TYPE游标类型名REFCURSOR;
游标使用例子
实例1
createorreplaceproceduretest()as
Cursorcurisselectnamefromstudent;
namevarchar(20);
begin
fornameincursorLOOP
dbms_output.putline(name);
end;
endLOOP;
实例2
v_rowtab1%rowtype;
--
匹配tab1表中一行所有的数据类型
v_cur
select
*
from
t_test;
声明游标
v_cur;
打开游标
loop
v_row;
将游标所在行的数据转存到v_row中
exit
when
v_cur%notfound;
当游标到最后一行时跳出
dbms_output.put_line('
id='
||v_row.t_id||'
name='
||v_row.t_name||'
msg='
||v_row.t_msg);
end
loop;
关闭游标
exception
others
then
throwexception:
others'
关于oracle存储过程的若干问题备忘
1.在oracle中,数据表别名不能加as,如:
a.appname
appinfo
a;
--正确
as
--错误
也许,是怕和oracle中的存储过程中的关键字as冲突的问题吧
2.在存储过程中,select某一字段时,后面必须紧跟into,如果select整个记录,利用游标的话就另当别论了。
af.keynode
kn
APPFOUNDATION
af
where
af.appid=aid
and
af.foundationid=fid;
有into,正确编译
没有into,编译报错,提示:
Compilation
Error:
PLS-00428:
an
INTO
clause
expected
in
this
SELECT
statement
3.在利用select...into...语法时,必须先确保数据库中有该条记录,否则会报出"
nodatafound"
异常。
可以在该语法之前,先利用selectcount(*)from查看数据库中是否存在该记录,如果存在,再利用select...into...
4.在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错
keynode
appid=aid
foundationid=fid;
正确运行
af.appid=appid
af.foundationid=foundationid;
运行阶段报错,提示
ORA-01422:
exact
returns
more
than
requested
number
of
rows
5.在存储过程中,关于出现null的问题
假设有一个表A,定义如下:
create
table
A(
id
varchar2(50)
primary
key
not
null,
vcount
number(8)
bid
null
外键
如果在存储过程中,使用如下语句:
sum(vcount)
fcount
A
bid='
xxxxxx'
如果A表中不存在bid="
xxxxxx"
的记录,则fcount=null(即使fcount定义时设置了默认值,如:
fcountnumber(8):
=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里最好先判断一下:
if
then
fcount:
if;
这样就一切ok了。
6.Hibernate调用oracle存储过程
this.pnumberManager.getHibernateTemplate().execute(
new
HibernateCallback()
{
public
Object
doInHibernate(Session
session)
throws
HibernateException,
SQLException
CallableStatement
cs
=
session
.connection()
.prepareCall("
{call
modifyapppnumber_remain(?
)}"
cs.setString(1,
foundationid);
cs.execute();
return
null;
}
});
Oracle的包-package
封装过程(procedure)、函数(function)和变量。
(包的创建在包视图下创建-跟存储过程不是同一个树节点)
注意,在包中声明的过程(procedure)和函数(function)必须在包体中定义实现。
基本语法:
createpackage包名
as
变量声明
存储过程声明
自定义函数声明
end包名;
/
存储过程的代码实现
自定义函数的代码实现
包的调用
包名.方法名(参数);
包名.变量
Oracle的函数-function
function
函数名(参数)
返回值类型
变量声明、初始化
........
返回的值;
.......
函数名;
参数:
入参
只有入参的类型。
在存储过程和自定义函数中的参数的传递(入参和出参)不能使用%type或%rowtype匹配,不能使用空值null,但是存储过程可以返回空值。
Oracle的类型-type
Oracle提供自定义类型功能,用户可以在plsql的浏览器窗口的type下面或者在存储过程或者函数下为oracle定义数据类型。
用户可以定义记录数据类型(Record)和记录表类型(Table)
//Record--->
ObjectTable--->
List<
Object>
自定义类型有两种写法:
TYPE...IS和CREATETYPE,两种定义方式的区别是:
前者一般在存储过程和函数中定义,使用范围也限于所在过程或函数,后者方式声明对象类型,对象类型则是作为一个方案对象(像表、索引、视图、触发器一样,是一个方案对象),可以过程或函数中使用,还可以在定义表时,作为字段的类型。
TYPEIS定义类型(常在procedure和function下使用)
1.定义数据记录类型
TYPEtypeNameISRECORD(
字段1类型1,
字段2类型2,
字段n类型n
);
2.定义表记录
语法:
TYPEtypeNameISTABLEOFelement_type
INDEXBY[BINARY_INTEGER|PLS_INTEGER|VARRAY2];
1)typeName:
类型名称。
2)element_type:
相当于List中元素的类型。
可以是基本类型(如varchar2,Ingeger,number等)、记录数据类型、%ROWTYPE。
3)INDEXBY:
该语句的作用是使Number类型的下标自增长,自动初始化,并分配空间,有了该语句,向表记录插入元素时,不需要显示初始化,也不需要通过extend分配空间。
Binary_Integer与Pls_Integer都是整型类型.
Binary_Integer类型变量值计算是由Oracle来执行,不会出现溢出,但是执行速度较慢,因为它是由Oracle模拟执行。
而Pls_Integer的执行是由硬件即直接由CPU来运算,因而会出现溢出,但其执行速度较前者快许多。
如果没有使用这个语句,又没有使用extend就会报错:
ORA-06531:
Referencetouninitialized
CREATETYPE定义类型(常在type下定义)
1.定义对象类型
CREATEORREPLACETYPEtype_nameASOBJECT(
CREATEORREPLACETYPEtype_nameASTABLEOFelement_type;
1)type_name:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 存储 过程