oracle游标sy.docx
- 文档编号:15028904
- 上传时间:2023-06-29
- 格式:DOCX
- 页数:49
- 大小:33.43KB
oracle游标sy.docx
《oracle游标sy.docx》由会员分享,可在线阅读,更多相关《oracle游标sy.docx(49页珍藏版)》请在冰点文库上搜索。
oracle游标sy
sys
7.1基本语法:
declare-可选
声明各种变量或游标的地方。
begin-必要
开始执行语句。
--单行注释语句用两个连在一起的'-'表示。
/*多行注释语句,
可以换行*/
exception-可选
出错后的处理。
end;-必要(请注意end后面的分号)结束。
提示:
在Toad中,可以按Ctrl键去点代码中的表名,即可快速地查看该表的信息。
7.1.1代码书写风格的规范化:
(1)PL/SQL编程规范之大小写
就像在SQL中一样,PL/SQL中是不区分大小写的。
其一般准则如下:
关键字(BEGIN,EXCEPTION,END,IFTHENELSE,LOOP,ENDLOOP)、数据类型(VARCHAR2,NUMBER)、内部函数(LEAST,SUBSTR)和用户定义的子程序(procedures,functions,packages),使用大写。
变量名以及SQL中的列名和表名,使用小写。
(2)PL/SQL编程规范之空白
空白(空行和空格)在PL/SQL中如同在SQL中一样重要,因为它是提高代码可读性的一个重要因素。
换句话说,可以通过在代码中使用缩进来体现程序的逻辑结构。
以下是一些建议:
在等号或比较操作符的左右各留一个空格;
结构词(DECLARE,BEGIN,EXCEPTION,END,IFandENDIF,LOOPandENDLOOP)居左排列。
另外,结构中的嵌套结构要缩进三个空格(使用空格键,而不是Tab键);
主要代码段之间用空行隔开;
把同一结构的不同逻辑部分分开写在独立的行,即使这个结构很短。
例如,IF和THEN被放在同一行,而ELSE和ENDIF则放在独立的行。
(3)PL/SQL编程规范之命名约定
使用以下前缀对于避免与关键字和表名列名相冲突是很有帮助的:
v_变量名
con_常量名
i_输入参数名,o_输出参数名,io_输入输出参数名
c_游标名或者游标名_cur
rc_RefCursor名
r_Record名或者Record名_rec
FORr_studINc_studLOOP…
FORstud_recINstud_curLOOP
type_名称,名称_type(用户定义的类型)
t_表名,表名_tab(PL/SQL表)
rec_Record名,Record名_rec(Record变量)
e_异常名(用户定义的异常)
包的名称应该描述包内的存储过程和函数主要所完成的功能
存储过程的名称应该描述该存储过程所执行的动作
函数的名称应该描述所返回的变量
例如:
PACKAGEstudent_admin
–admin后缀可能是用于表示管理功能。
PROCEDUREremove_student(i_student_idINstudent.studid%TYPE);
FUNCTIONstudent_enroll_count(i_student_idstudent.studid%TYPE)
RETURNINTEGER;
(4)PL/SQL编程规范之注释
PL/SQL中的注释如同SQL中的注释一样重要。
他们应该解释程序的主要部分和所有关键的逻辑步骤。
使用单行注释(–)而不是多行注释(/*)。
即使PL/SQL对这些注释做同样处理,这样在代码完成后进行调试也会容易些,因为你不能在多行注释中嵌入多行注释。
换句话说,单行注释代码中可以部分取消注释,而在多行注释代码中则不行。
(5)其他的建议
对于PL/SQL中嵌入的SQL声明,使用相同的格式化指南来决定这些声明应该如何在代码块中出现
提供一个头部注释,用于说明代码块的用途并列出创建日期和作者名字。
并且每个修订版都要有一行注释,包含作者名、日期和修订版描述。
例如:
下面的这个示例体现了上述建议。
请注意该示例还使用了等宽字体(CourierNew),因为每个字体占据同等宽度可以使格式化更加简便。
等比例空格字体会隐藏空格使得行间对齐比较困难。
多数文本和程序编辑器默认使用等宽字体。
-----------------------------------------------------------------------------
7.2最简单的语句块
begin
dbms_output.put_line('HelloWorld!
');
end;
setserveroutputon;
7.3简单的PL/SQL语句块
declare
v_namevarchar2(20);
begin
v_name:
='myname';
dbms_output.put_line(v_name);
end;
7.4语句块的组成
declare
v_numnumber:
=0;
begin
v_num:
=2/v_num;
dbms_output.put_line(v_num);
exception
whenothersthen
dbms_output.put_line('error');
end;
7.5Sql/plus中的变量
7.5.1在变量声明时需要遵守一些基本的规则:
1.变量名不能够使用保留字,如from、select等
2.第一个字符必须是字母
3.变量名最多包含30个字符
4.不要与数据库的表或者列同名
5.每一行只能声明一个变量
7.5.2PL/SQL中常用的变量类型:
1.binary_integer:
整数,主要用来计数而不是用来表示字段类型,通常用作下标变量
2.number:
数字类型
3.char:
定长字符串
4.varchar2:
变长字符串
5.date:
日期
6.long:
长字符串,最长2GB
7.boolean:
布尔类型,可以取值为true、false和null值
7.5.3变量声明
declare
v_tempnumber
(1);
v_countbinary_integer:
=0;
v_salnumber(7,2):
=4000.00;
v_datedate:
=sysdate;
v_piconstantnumber(3,2):
=3.14;--相当于java里面的final
v_validboolean:
=false;
v_namevarchar2(20)notnull:
='MyName';
begin
dbms_output.put_line('v_tempvalue:
'||v_temp);
end;
7.5.4简单变量赋值
Oracle中对变量的赋值用:
=来进行赋值。
declare
v_namevarchar2(20);
v_salnumber(7,2);
v_sal2number(7,2);
v_validboolean:
=false;
v_datedate;
begin
v_name:
='MyName';
v_sal:
=23.77;
v_sal2:
=23.77;
v_valid:
=(v_sal=v_sal2);
v_date:
=to_date('1999-08-1212:
23:
38','YYYY-MM-DDHH24:
MI:
SS');
dbms_output.put_line('v_sal=:
'||v_sal);
end;
7.5.5%type属性
属性的一种方式,是在变量声明中使用。
%TYPE是用于取表或游标中的字段类型。
declare
v_empnonumber(4);
v_empno2emp.empno%type;--好处,表的字段类型变了,v_empno2的类型也跟着变
v_empno3v_empno2%type;
begin
dbms_output.put_line('Test');
end;
通过这种方式就不需要明确指定变量的数据类型,从而改善了代码的可维护性,因为表中字段类型的改变会自动反映在PL/SQL变量中。
(1)Table数组变量类型
相当于java里面的数组
declare
--声明了一个数组类型,约定俗成以type_开头
--下标的类型是binary_integer类型
typetype_table_emp_empnoistableofemp.empno%typeindexbybinary_integer;
v_empnostype_table_emp_empno;
begin
v_empnos(0):
=7369;
v_empnos(5);
v_empnos
(2):
=7839;
v_empnos(-1):
=9999;--下标可以取负值
dbms_output.put_line(v_empnos(-1));
dbms_output.put_line(v_empnos(5));
end;
(2)Record变量类型
相当于java里面的类。
declare
typetype_record_deptisrecord
(
deptnodept.deptno%type,
dnamedept.dname%type,
locdept.loc%type
);
v_temptype_record_dept;--定义v_temp为record类型
begin
v_temp.deptno:
=50;
v_temp.dname:
='aaaa';
v_temp.loc:
='bj';
dbms_output.put_line(v_temp.deptno||''||v_temp.dname);
end;
缺点:
当表结构发生改变时(如增加列或删减列),就必须重新定义type_record_dept,为此,在Oracle中引入了%rowtype属性来声明record变量。
7.5.6%rowtype属性
使用%ROWTYPE属性可以声明一个基于表或游标的行对象,也称为记录对象。
declare
v_tempdept%rowtype;
begin
v_temp.deptno:
=50;
v_temp.dname:
='aaaa';
v_temp.loc:
='bj';
dbms_output.put_line(v_temp.deptno||''||v_temp.dname);
end;
%TYPE是跟表中的字段的类型一致,是变量类型,而%ROWTYPE是跟表中的行类型一致,是record类型。
7.5.7SQL语句在PL/SQL中的运用
(1)SELECT语句在PL/SQL中的运用
必须遵守的规则:
select必须返回一条记录并且只能返回一条记录
select必须和into一起用
例:
declare
v_enameemp.ename%type;
v_salemp.sal%type;
begin
selectename,salintov_ename,v_salfromempwhereempno=7369;
dbms_output.put_line(v_ename||''||v_sal);
end;
结果为:
Smith800
declare
v_enameemp.ename%type;
v_salemp.sal%type;
begin
selectename,salintov_ename,v_salfromempwhereempno=9999;
dbms_output.put_line(v_ename||''||v_sal);
end;
错误:
ORA-01403:
未找到数据
例:
declare
v_enameemp.ename%type;
v_salemp.sal%type;
begin
selectename,salintov_ename,v_salfromempwheredeptno=30;
dbms_output.put_line(v_ename||''||v_sal);
end;
错误:
实际返回的行数超出请求的行数
例:
用%rowtype属性来声明的Record型变量可存储一条完整记录。
declare
v_empemp%rowtype;
begin
select*intov_empfromempwhereempno=7369;--v_emp可以存储一条记录
dbms_output.put_line(v_emp.ename);
end;
(2)INSERT语句在PL/SQL中的运用
declare
v_deptnodept.deptno%type:
=60;
v_dnamedept.dname%type:
='业务部';
v_locdept.loc%type:
='北京';
begin
insertintodept(deptno,dname,loc)values(v_deptno,v_dname,v_loc);
commit;
end;
通过以上语句可以将变量中的值插入到表中。
select*fromdept2;
(3)sql%rowcount属性
sql%rowcount中的sql是oracle的内部游标,rowcount的意思是之前的DML语句影响的多少行数据。
如果是delete操作rowcount就是delete的数据,如果是insert,就是插入的数量。
declare
v_deptnoemp.deptno%type:
=20;
v_cntnumber;
begin
updateemp
setsal=sal+100
wheredeptno=v_deptno;
dbms_output.put_line(sql%rowcount||'条记录被更改');
rollback;
end;
结果为:
5条记录被更改
declare
v_deptnoemp2.deptno%type:
=30;
v_countnumber;
begin
--updateemp2setsal=sal/2wheredeptno=v_deptno;
--deletefromemp2wheredeptno=10;
--selectdeptnointov_deptnofromemp2whereempno=7369;
selectcount(*)intov_countfromemp2;
dbms_output.put_line(sql%rowcount||'条记录被影响');
--commit;
end;
rollback;
(4)SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT
当执行一条DML语句后,DML语句的结果保存在四个游标属性中,这些属性用于控制程序流程或者了解程序的状态。
当运行DML语句时,PL/SQL打开一个内建游标并处理结果,游标是维护查询结果的内存中的一个区域,游标在运行DML语句时打开,完成后关闭。
隐式游标只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性.SQL%FOUND,SQL%NOTFOUND是布尔值,SQL%ROWCOUNT是整数值。
SQL%FOUND和SQL%NOTFOUND
在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后,SQL%FOUND的属性值将是:
.TRUE:
INSERT
.TRUE:
DELETE和UPDATE,至少有一行被DELETE或UPDATE.
.TRUE:
SELECTINTO至少返回一行
当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。
SQL%ROWCOUNT
在执行任何DML语句之前,SQL%ROWCOUNT的值都是NULL,对于SELECTINTO语句,如果执行成功,SQL%ROWCOUNT的值为1,如果没有成功或者没有操作(如update、insert、delete为0条),SQL%ROWCOUNT的值为0.
SQL%ISOPEN
SQL%ISOPEN是一个布尔值,如果游标打开,则为TRUE,如果游标关闭,则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE,这是因为隐式游标在DML语句执行时打开,结束时就立即关闭。
(5)DDL语句在PL/SQL使用
--必须写executeimmediate......
begin
executeimmediate'createtableT(nnnvarchar2(20)default''aaa'')';
end;
droptableT;
7.6选择结构
7.6.1格式1:
IF_THEN_ELSE语句
语法格式:
if条件then
语句1;
语句2;
……
[else
语句n;
语句n+1;
……
]
endif;
例--计算部门30的平均工资,如果平均工资大于2000,输出'平均工资大于2000',并输出平均工资,'
--如果平均工资小于或等于2000输出'平均工资小于或等于2000',并输出平均工资
declare
v_deptnoemp.deptno%type:
=30;
v_avg_salemp.sal%type;
begin
selectavg(sal)intov_avg_salfromempwheredeptno=v_deptno;
ifv_avg_sal>2000then
dbms_output.put_line('平均工资大于2000'||'平均工资:
'||v_avg_sal);
else
dbms_output.put_line('平均工资小于或等于2000,'||'平均工资:
'||v_avg_sal);
endif;
end;
7.6.2格式2:
IF_THEN_ELSE_IF语句
语法格式:
IF条件1THEN
语句序列1;
ElSIF条件2THEN
语句序列2;
[
ELSIF条件nTHEN
语句序列n;
]
[
ELSE
语句序列n+1
……
]
ENDIF;
例:
取出7369的薪水,如果薪水<1200,则输出'low',如果<2000则输出'middle',否则'high'
--注意elsif的写法,then后面没有分号
--注意最后一个else后面没有then
--注意endif后面有一个分号
declare
v_salemp.sal%type;
begin
selectsalintov_salfromempwhereempno=7369;
ifv_sal<1200then
dbms_output.put_line('salgradeislow');
elsifv_sal<2000then
dbms_output.put_line('salgradeismiddle');
else
dbms_output.put_line('salgradeishigh');
endif;
end;
7.7循环结构
(1)第一种
直到型循环
特点:
先执行循环体,后判断条件。
语法格式:
Loop
<语句1>
<……>
Exit[when条件]
Endloop;
例:
从1输出到10个自然数。
declare
ibinary_integer:
=1;
begin
loop
dbms_output.put_line(i);
i:
=i+1;
exitwheni>10;
endloop;
end;
(2)第二种
当型循环
特点:
先判断条件,后执行循环体。
语法格式:
While<条件>loop
<语句1>
<……>
Endloop;
例:
从1输出到10个自然数。
declare
ibinary_integer:
=1;
begin
whilei<11loop
dbms_output.put_line(i);
i:
=i+1;
endloop;
end;
(3)第三种
FOR循环
语法格式:
For计数器in[reverse]下界..上界
Loop
语句1;
语句2;
……
ENDLOOP;
注意:
计数器是有于控制循环次数的变量,它不需显式地在变量定义部分进行定义。
系统默认时,计数器从下界往上界递增记数,如果在关键字IN后加上REVERSE则表示计数器从上界到下界递增记数。
计数器变量只能在循环体内部使用,不能在循环体外使用。
例:
计算1+2+3+……+100的值
declare
v_sumnumber(5):
=0;
begin
foriin1..100loop
v_sum:
=v_sum+i;
endloop;
dbms_output.put_line(v_sum);
end;
例:
从10到1倒着输出10个整数
begin
foriinreverse1..10loop
dbms_output.put_line(i);
endloop;
end;
7.8异常处理(了解)
7.8.1异常
(1)程序错误
编译时
运行时
(2)异常
处理运行时错误
7.8.2PL/SQL是如何处理异常的?
- 错误引发异常
- 每当引发异常时,都将控制权传递给异常处理程序
- 异常处理程序处理异常
7.8.3异常中涉及的步骤
- 声明异常
- 引发异常
- 处理异常
7.8.4 异常的类型
- 预定义的异常 --由Oracle服务器维护异常
- 非预定义的异常又称'用户定义异常'
(1) 预定义异常
由Oracle为常见错误预定义
在DBMS_Standard程序包中提供了这些定义
不需要显式声明
例:
当select语句查询没结果,返加多条记录,或算术表达式的除数为0时进行异常处理.
declare
v_jobemp.job%type;
beg
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 游标 sy
![提示](https://static.bingdoc.com/images/bang_tan.gif)