第13章 Oracle高级阶段编程.docx
- 文档编号:10792327
- 上传时间:2023-02-22
- 格式:DOCX
- 页数:31
- 大小:198.02KB
第13章 Oracle高级阶段编程.docx
《第13章 Oracle高级阶段编程.docx》由会员分享,可在线阅读,更多相关《第13章 Oracle高级阶段编程.docx(31页珍藏版)》请在冰豆网上搜索。
第13章Oracle高级阶段编程
Oracle高级阶段编程
1、PLSQL编程
PLSQL块是结构化设计程序语言,是在SQL语言上发展的一种应用,可以集中的处理各种复杂的SQL操作。
Ø组成
declare
inumber;
--变量的定义
begin
--SQL语句操作处理
exception
--异常处理
end;
Ø简单PL/SQL编写
1 变量定义
变量的定义,Oracle语言采用declare进行声明
数据类型可以采用Oracle内置的数据类型。
2 变量赋值
变量的赋值采用【:
=】
3 结果输出
dbms_output.put_line('i的值'||i);
4 异常处理
declare
inumber;
--变量的定义
begin
--SQL语句操作处理
i:
=1/0;
DBMS_OUTPUT.put_line('除数能为0');
exception
whenZERO_DIVIDETHEN
DBMS_OUTPUT.put_line('除数不能为0');
--异常处理
end;
相当于JAVA中
try{
i:
=1/0;
DBMS_OUTPUT.put_line('除数能为0');
}catch(Exceptione){
DBMS_OUTPUT.put_line('除数不能为0');
}
5 变量绑定
Oracle自身提供的工具采用的方式是&a,但是采用PL/SQLDEV工具变量绑定的方式:
a.
a表示变量名。
对于数据库操作变量采用方式:
selectenameintoename_cpfromemptwheret.empno=(:
b);
作业:
输入一个雇员编号,请将部门名称以及所在地点进行输出
DECLARE
iINTEGER;
DnameVARCHAR2(20);
LocVARCHAR2(20);
BEGIN
i:
=(:
empno);
SELECTd.Dname,d.LocINTODname,LocFROMEmpt,DeptdWHEREt.Deptno=d.DeptnoANDt.empno=i;
Dbms_Output.Put_Line('部门编号:
'||Dname||'部门地点:
'||Loc);
END;
6 字段数据类型定义
1.如果直接想利用数据库表中字段的类型,采用的方式:
Dnamedept.dname%type;表示dname变量数据类型与dept表中的dname字段类型一致。
2.如果要输出多列的值,采用into后面直接跟上需要输出的变量名。
3.如果直接代表一行数据,采用方式【emp_cpemp%rowtype;】表示变量emp_cp代表一行数据。
declare
--Localvariableshere
iinteger;
emp_cpemp%rowtype;
begin
select*intoemp_cpfromempwhereempno=16;
Dbms_Output.Put_Line('员工编号:
'||emp_cp.empno||'员工姓名:
'||emp_cp.ename);
end;
4.如果检索的数据无记录,采用异常处理即可。
exception
whenNO_DATA_FOUNDthen
dbms_output.put_line('无记录');
作业:
打印emp表中某行的数据,要求没有数据时打印无数据,有数据时,对奖金如果为空时输出0.
疑问:
如果查询的结果没有对应的数据了?
7 循环
✧While
declare
iinteger;
begin
--Teststatementshere
i:
=1;
while(i<10)loop
dbms_output.put_line('count='||i);
i:
=i+1;
endloop;
end;
While([条件表达式])loop
数据库业务处理
Endloop;
✧Loop【相当于do。
。
。
。
while】
declare
--Localvariableshere
iinteger;
begin
--Teststatementshere
i:
=1;
loop
dbms_output.put_line('count='||i);
i:
=i+1;
exitwheni>10;
endloop;
end;
Loop
数据库业务处理
exitwhen[条件表达式];
Endloop;
【作业:
】
新建一张表,test表,字段为id,name。
要求采用plsql编程,动态添加10000行数据。
declare
iinteger;
begin
i:
=1;
while(i<)loop
insertintotestvalues(i,'test');
i:
=i+1;
endloop;
commit;
end;
对于大数据量的处理,需要调整缓冲区大小。
✧For
格式:
for变量名称in变量的初始值..结束值loop
循环语句
Endloop;
declare
--Localvariableshere
iinteger;
begin
foriin1..10loop
dbms_output.put_line(i);
endloop;
end;
8 分支
✧IF
declare
--Localvariableshere
iinteger;
begin
i:
=10;
ifi<20then
dbms_output.put_line('小于20');
endif;
end;
✧IF....ELSE
declare
--Localvariableshere
iinteger;
begin
i:
=30;
ifi<20then
dbms_output.put_line('小于20');
else
dbms_output.put_line('大于20');
endif;
end;
✧IF.....ELSIF....ELSE
declare
iinteger;
begin
i:
=25;
ifi<20then
dbms_output.put_line('小于20');
elsifi>20andi<30then
dbms_output.put_line('在范围内');
else
dbms_output.put_line('大于20');
endif;
end;
作业:
输入一个雇员的编号,如果其工资高于3500,则显示高工资,工资大于2000,则显示中等工资,工资小于2000的则认为是低等工资。
9 GOTO
declare
--Localvariableshere
iinteger;
begin
i:
=35;
ifi<20then
gotogo1;
elsifi>20andi<30then
gotogo2;
else
gotogo3;
endif;
<
dbms_output.put_line('小于20');
<
dbms_output.put_line('大于20');
<
dbms_output.put_line('在范围内');
end;
Ø作业
1.输入一个雇员编号,根据它所在的部门涨工资,规则:
•10部门上涨10%
•20部门上涨20%
•30部门上涨30%
所有部门的上涨工资,最高不能超过5000,如果超过5000,则工资就为5000。
DECLARE
iINTEGER;
SalsEmp.Sal%TYPE;
DempnoDept.Deptno%TYPE;
noemp.empno%type;
BEGIN
no:
=(:
No);
SELECTt.Sal,t.DeptnoINTOSals,DempnoFROMEmptWHEREt.Empno=no;--查找出薪水和部门编号
IFDempno=10THEN
Sals:
=Sals*1.1;
ELSIFDempno=20THEN
Sals:
=Sals*1.2;
ELSIFDempno=30THEN
Sals:
=Sals*1.3;
ENDIF;
--
IFSals>5000THEN
Sals:
=5000;
ENDIF;
dbms_output.put_line('薪水:
'||Sals);
updateemptsett.sal=Salswheret.empno=no;
commit;
END;
2、游标
游标是一种PL/SQL控制结构,可以对SQL的处理进行显示的控制,便于对表的行数据逐条的进行处理。
游标不是一个数据库对象,仅仅保存在内存中。
Ø操作步骤
1.声明游标
2.打开游标
3.取出结果,逐行进行处理
4.关闭游标
Ø疑问
哪种类型把一行数据装载:
%rowtype
Ø游标属性
%FOUND:
%NOTFOUND:
%ROWCOUNT:
%ISOPEN:
Ø游标结构
declare
cursormycurisselect*fromemp;--定义游标,表示emp表中结果集数据
iinteger;
empsemp%rowtype;--表示一行数据
begin
ifmycur%ISOPENthen
null;--表示不做任何处理
else
openmycur;
endif;
--使用游标进行对应的处理
closemycur;
end;
ØWhile循环
declare
cursormycurisselect*fromemp;--定义游标,表示emp表中结果集数据
iinteger;
empsemp%rowtype;--表示一行数据
begin
ifmycur%ISOPENthen
null;--表示不做任何处理
else
openmycur;
endif;
--使用游标进行对应的处理
fetchmycurintoemps;--使游标向下执行一行
whilemycur%FOUNDloop
dbms_output.put_line('员工编号:
'||emps.empno||'员工名称:
'||emps.ename);
fetchmycurintoemps;
endloop;
closemycur;
end;
ØDo...while
作业:
exitwhenmycur%notfound
ØFor
作业:
For不需要打开关闭游标。
declare
cursormycurisselect*fromemp;--定义游标,表示emp表中结果集数据
iinteger;
empsemp%rowtype;--表示一行数据
begin
forempsinmycurloop
dbms_output.put_line('员工编号:
'||emps.empno||'员工名称:
'||emps.ename);
endloop;
--closemycur;
end;
处理多个表的情况?
--Createdon2013-05-23byADMINISTRATOR
DECLARE
iINTEGER;
EnoEmp.Empno%TYPE;
EnameEmp.Ename%TYPE;
DnameDept.Dname%TYPE;
CURSORMycurIS
SELECTt.Empno,t.Ename,s.Dname
INTOEno,Ename,Dname
FROMEmpt,Depts
WHEREt.Deptno=s.Deptno;--定义游标,表示emp表中结果集数据
BEGIN
IFMycur%ISOPENTHEN
NULL;--表示不做任何处理
ELSE
OPENMycur;
ENDIF;
--使用游标进行对应的处理
--fetchmycurintoemps,depts;--使游标向下执行一行
FETCHMycur
INTOEno,Ename,Dname;
WHILEMycur%FOUNDLOOP
Dbms_Output.Put_Line('员工编号:
'||Eno||'员工名称:
'||Ename||'部门名称'||Dname);
FETCHMycur
INTOEno,Ename,Dname;
ENDLOOP;
CLOSEMycur;
END;
Ø作业
1.打印emp表和dept表中的数据,显示雇员的信息以及雇员所在部门信息。
2.一次性上涨全部雇员的工资。
根据它所在的部门涨工资,规则:
•10部门上涨10%
•20部门上涨20%
•30部门上涨30%
所有部门的上涨工资,最不能超过5000,如果超过5000,则工资就为5000。
declare
cursormycurisselect*fromemp_cp;
salsemp_cp.sal%type;
empsemp_cp%rowtype;
begin
forempsinmycurloop
ifemps.deptno=10then
sals:
=emps.sal*1.1;
elsifemps.deptno=20then
sals:
=emps.sal*1.2;
elsifemps.deptno=30then
sals:
=emps.sal*1.3;
endif;
ifsals>5000then
sals:
=5000;
endif;
updateemp_cptsett.sal=salswheret.empno=emps.empno;
endloop;
commit;
end;
3、函数
Ø概念
Ø创建函数
createorreplacefunctionhello(msgnvarchar2)returnnvarchar2
as
rsalnvarchar2(20);--变量定义
begin
rsal:
='你好';
returnrsal||','||msg;
end;
Ø调用函数
Ø作业
1.编写一个函数,以deptno为标准,返回此部门所有雇员的整体薪水
2.写一个函数,传入时间,返回入职时间比这个时间早的所有员工的平均工资
3.利用JDBC,传入参数,获取函数对应的结果。
publicstaticvoidmain(String[]args){
Connectionconn=null;//?
?
?
?
?
?
?
PreparedStatementps=null;//?
?
?
?
?
?
?
ResultSetrs=null;//?
?
?
?
?
try{
Class.forName("oracle.jdbc.driver.OracleDriver");//?
?
?
?
?
?
?
conn=DriverManager.getConnection(
"jdbc:
oracle:
thin:
@172.16.10.210:
1521:
orcl","scott",
"tiger");
Stringsql="selectt.deptno,t.dname,Sumsalsbyid(t.deptno)sumsalsfromdeptt";
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
System.out.println(rs.getString
(2)+""
+rs.getString("sumsals"));
}
}catch(ClassNotFoundExceptione){
e.printStackTrace();
}catch(SQLExceptione){
e.printStackTrace();
}finally{
try{
if(rs!
=null){
rs.close();
}
if(ps!
=null){
ps.close();
}
if(conn!
=null){
conn.close();
}
}catch(SQLExceptione){
e.printStackTrace();
}
}
}
函数结构:
CREATEORREPLACEFUNCTIONSumsalsbyid(NoDept.Deptno%TYPE)
RETURNEmp.Sal%TYPEIS
RESULTsEmp.Sal%TYPE;
BEGIN
SELECTSUM(t.Sal)INTORESULTsFROMEmptWHEREt.Deptno=No;
RETURN(RESULTs);
ENDSumsalsbyid;
4、存储过程
Ø存储过程优势
1.存储过程只有在创建该存储过程时进行了一次编译,编译通过之后,每次调用就不需要重新编译。
但是一般SQL再执行一次时均需要重新编译一次,所以执行存储过程能够提高数据库的执行速度。
2.存储过程可以重复使用。
3.当数据库进行复杂的操作时,采用存储过程进行处理。
而采用JDBC方式处理的话,则每次执行一次操作需要请求数据库进行执行,发出一条一条SQL语句。
但是对于简单的SQL操作,不建议直接采用存储过程。
存储过程一般用于复杂的SQL
语句操作。
Ø存储过程使用
createorreplaceprocedurehello(【参数】)is
【变量的定义】
begin
endhello;
createorreplaceprocedurehi(msgnvarchar2)is
begin
dbms_output.put_line(msg);
endhi;
疑问:
1.如果程序块中需要使用多个变量,如何处理?
2.存储过程调用方式?
1 在测试窗口,写pl/SQL块
2 在命令窗口中使用exechi('aasss')方式调用。
Ø存储过程的参数类型
In:
传入,默认的方式
Inout:
带值进,带值出
Out:
不带值进,带值出
CREATEORREPLACEPROCEDUREHi(MsgINNVARCHAR2,OutputOUTNVARCHAR2)IS
MessNVARCHAR2(20);
BEGIN
Mess:
='测试';
Dbms_Output.Put_Line(Msg||Mess);
Output:
='测试你好啊';
ENDHi;
作业:
1.新建一张表,登录表:
ID(序列号),name(唯一),密码。
注册时,首先判断用户名是否存在,如果不存在的话,获取序列号的值,达成添加功能。
如果存在的话,则需要输出提示信息。
【用户名已存在】
CREATEORREPLACEPROCEDUREValidatelogin(UsernameINStudent.Name%TYPE,
PasswordINStudent.Password%TYPE,
FlagOUTBOOLEAN)IS
CntNUMBER;
BEGIN
SELECTCOUNT(*)INTOCntFROMStudentWHERENAME=Username;
IFCnt>0THEN
Dbms_Output.Put_Line('用户名已存在');
Flag:
=FALSE;
ELSE
INSERTINTOStudent(Id,NAME,Password)VALUES(Myseq.Nextval,Username,Password);
Flag:
=TRUE;
ENDIF;
EXCEPTION
WHENOTHERSTHEN
Dbms_Output.Put_Line('用户名已存在');
Flag:
=FALSE;
ENDValidatelogin;
Ø存储过程的返回类型
1.无返回结果
2.有返回结果值
3.有返回结果集
ØJava调用
调用的类:
CallableStatement:
调用存储过程类
publicstaticvoidmain(String[]args){
Connectionconn=null;//?
?
?
?
?
?
?
CallableStatementcs=null;//?
?
?
?
?
?
?
?
?
?
?
ResultSetrs=null;//?
?
?
?
?
Stringresult="";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");//?
?
?
?
?
?
?
conn=DriverManager.getConnection(
"jdbc:
oracle:
thin:
@172.16.10.210:
1521:
orcl","scott",
"tiger");
cs=conn.prepareCall("{callHi(?
?
)}");//?
?
?
?
?
?
cs.setString(
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第13章 Oracle高级阶段编程 13 Oracle 高级 阶段 编程