ORACLE笔记07Word文档下载推荐.docx
- 文档编号:18035044
- 上传时间:2022-12-13
- 格式:DOCX
- 页数:21
- 大小:454.39KB
ORACLE笔记07Word文档下载推荐.docx
《ORACLE笔记07Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《ORACLE笔记07Word文档下载推荐.docx(21页珍藏版)》请在冰豆网上搜索。
importjava.sql.*;
publicclassTestProcedure2{
//调用pro1过程,该过程有结果集返回
publicstaticvoidmain(String[]args){
//定义需要的变量
Connectionct=null;
CallableStatementcs=null;
ResultSetrs=null;
PreparedStatementps=null;
try{
//加载驱动
Class.forName("
oracle.jdbc.driver.OracleDriver"
);
//得到链接
ct=DriverManager.getConnection("
jdbc:
oracle:
thin:
@127.0.0.1:
1521:
orclydd"
"
scott"
tiger"
//创建CallStatement对象
cs=ct.prepareCall("
{callpro1(?
?
)}"
//给问号赋值
cs.setInt(1,10);
//给第二个问号注册
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//执行
cs.execute();
//获取结果,这里是关键
rs=(ResultSet)cs.getObject
(2);
while(rs.next())
{
System.out.println(rs.getString("
ename"
)+"
"
+rs.getString("
sal"
));
}
}catch(Exceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
}
4、完成一个分页存储过程:
需求:
请大家编写一个存储过程,要求可以输入表名,每页显示记录数,当前页,返回返回的结果集。
--建包。
使用游标类型
createorreplacepackagepack1is
--编写过程
createorreplaceprocedurefenyePro
(v_in_tableinvarchar2,v_in_pagesizeinnumber,
v_in_pagenowinnumber,v_out_resultoutpack1.my_cursor)is
--定义变量
v_sqlvarchar2(2000);
v_startnumber;
v_endnumber;
--执行代码
v_start:
=v_in_pagesize*(v_in_pagenow-1)+1;
v_end:
=v_in_pagesize*v_in_pagenow;
v_sql:
='
selectt2.*from(selectt1.*,rownumrnfrom(select*from'
||v_in_table
||'
)t1whererownum<
||v_end||'
)t2wherern>
||v_start;
--打开游标,让游标指向结果集
openv_out_resultforv_sql;
在Java中调用:
{callfenyePro(?
cs.setString(1,"
emp"
cs.setInt(2,3);
cs.setInt(3,1);
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CURSOR);
rs=(ResultSet)cs.getObject(4);
●对分页过程进行扩展,使之更加完善
?
请大家编写一个存储过程,要求可以输入表名,每页显示记录数,当前页,返回返回的结果集,返回共有多少页,共有多少条?
新的分页过程:
createorreplaceprocedurefenyePro2
v_in_pagenowinnumber,v_out_resultoutpack1.my_cursor,v_out_rowsoutnumber,v_out_pagecountoutnumber)is
--查询共有多少条记录
selectcount(*)intov_out_rowsfromemp;
ifmod(v_out_rows,v_in_pagesize)=0then
v_out_pagecount:
=v_out_rows/v_in_pagesize;
else
=v_out_rows/v_in_pagesize+1;
endif;
新的Java程序调用是:
{callfenyePro2(?
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.INTEGER);
introwCount=cs.getInt(5);
intpageCount=cs.getInt(6);
System.out.println("
记录数是:
"
+rowCount);
总页数是:
+pageCount);
思考题:
①、增加一个输入参数,可以按照某列排序?
②、
●视图
视图是oracle又一种数据对象,视图主要的用处是简化操作,提高安全性,满足不同用户的查询需求,视图不是一个真正存在物理表,它是根据别的表动态生成的。
请看图解:
我们来创建一个视图:
创建和emp表(empno,ename,job)完全一致的视图,看看带withreadonly和不带的区别:
基本语法:
Createview视图名asselect语句[withreadonly]
如果我们创建视图的时候,我们带[withreadonly],说明该视图只能读,而不能进行其他操作。
如果没有带[withreadonly],则可以进行其他操作。
实例:
Createviewempviewasselectempno,ename,jobfromemp;
、
如果创建视图的时候,不希望用户通过视图对源表进行操作,则建议带withreadonly。
Createviewempviewasselectempno,ename,jobfromempwithreadonly;
视图可以简化我们的操作:
比如:
我们希望查询雇员的名字和部门的编号和部门的名称:
传统的方法:
需要两张表进行多表查询;
使用视图:
(1)、先创建一个视图:
createorreplaceviewmyviewas
selectemp.ename,emp.deptno,dept.dnamefromdept,empwheredept.deptno=emp.deptno
withreadonly;
(2)、在视图中查询:
select*frommyview;
●视图的管理:
创建视图和修改视图:
Createorreplaceview视图名assql语句[可以是多表][withreadonly]
删除视图:
dropview试图名;
视图和表的区别:
1、表占用空间,视图不需要
2、视图不能加索引,但是表可以
3、视图可以简化我们的操作
4、视图可以提高安全性,比如:
给不同的用户创建不同的视图
。
触发器
①、提出问题:
当用户登录的时候,自动的记录该用户的名字,登录时间,ip等等
当用户在星期天对某张表进行删除操作的时候,我们提示不能这样做;
当用户删除某条记录的时候,自动将该记录保存到另外一张表去;
2、解决之道:
---触发器
原理:
3、触发器的分类
DML触发器(insert,delete,update);
DDL触发器(createtable,createview,drop等);
系统触发器:
(与系统相关的触发器,比如用户登录,退出,启动数据库,关闭数据);
4、触发器的快速入门
在某张表(my_emp)添加一天数据的时候,提示‘添加了一条数据’
createtablemy_emp(idnumber,namevarchar2(32));
createorreplacetriggertri1
afterinsertonscott.my_emp
dbms_output.put_line('
添加一条记录'
?
在某张表(my_emp)修改多条数据的时候,提示多次“修改了数据”
(讲解行级和语句级触发器)
createorreplacetriggertri2
afterupdateon
scott.empforeachrow–表示行级触发器
添加一天记录'
●DML触发器:
为了禁止工作人员在休息日改变员工信息,开发人员可以建立before语句触发器,从而实现数据安全
createorreplacetriggertri3
beforedeleteon
scott.emp
ifto_char(sysdate,'
day'
)in('
星期六'
'
星期日'
)then
休息日不能修改信息'
RAISE_APPLICATION_ERROR(-20001,'
特别说明:
RAISE_APPLICATION_ERROR是oracle提供的一个过程;
可以传入两个参数,第一个是自定义的错误号:
-20000~-29999,第二个是提示信息。
●使用条件谓词来精确提示,用户的操作:
为了禁止工作人员在休息日改变员工信息,开发人员可以建立before语句触发器,从而实现数据安全,在给出提示时,明确提示用户是进行行的insert,update,还是delete操作:
createorreplacetriggertri4
before
insertordeleteorupdate
onscott.emp
case
wheninsertingthen
请不要增加数据'
RAISE_APPLICATION_ERROR(-20002,'
whendeletingthen
请不要增加删除'
RAISE_APPLICATION_ERROR(-20003,'
请不要删除数据'
whenupdatingthen
请不要增加修改'
RAISE_APPLICATION_ERROR(-20004,'
请不要修改数据'
endcase;
●使用:
old和:
new
①、在修改emp表雇员薪水时,显示雇员工资修改前和修改后的值;
②、如何确保在修改员工工资不能低于原有工资;
createorreplacetriggertri5
beforeupdateon
scott.empforeachrow
if:
new.sal<
:
old.salthen
新工资不能低于旧工资'
raise_application_error(-20006,'
else
旧工资'
||:
old.sal||'
新工资'
new.sal);
触发器的笑练习:
要求:
编写一个触发器,保证当用户再删除一张表(emp)记录的时候,自动把删除的记录备份到另外一张表(emp_bak)中。
createtableemp_bak(idnumber,namevarchar2(2000));
createorreplacetriggertri6
insertintoemp_bak(id,name)values(:
old.empno,:
old.ename);
编写一个新的触发器,如何控制员工的新工资不能低于原来的工资,同时也不能高出原来工资的20%,使用约束显然无法实现该规则:
createorreplacetriggertri7
beforeupdateon
old.salor:
new.sal>
old.sal*1.2then
修改信息不符合要求'
raise_application_error(-20010,'
阻止把小于18岁的用户增加到数据库emp表中,请编写一个触发器完成上述任务
1、createtableemp2(idnumberprimarykey,namevarchar2(32),birthdaydate);
2、
createorreplacetriggertri8
beforeinserton
scott.emp2foreachrow
ifadd_months(:
new.birthday,18*12)>
sysdatethen
年龄太小'
raise_application_error(-20011,'
思考:
有一张stu表[id,name,classid],class表【id,name,nums】;
如果在学生表中删除了一个学生,则在班级的nums字段(记录学生数)的减1;
●系统触发器
系统触发器,主要针对oracle事件的触发器,比如用户登录,数据库启动或者关闭等,一般是由系统管理员来完成操作的。
常用的属性函数有:
createorreplacetrigger触发器名
after[before]logon[logoff]ondatabase
--执行语句
应用案例:
我们一起完成登录和退出触发器
1、创建一张用于保存用户登录情况或者退出情况的表
createtablelog_table(usernamevarchar2(20),logon_timedate,
logoff_timedate,addressvarchar2(20)
--创建一个登录触发器
createorreplacetriggerlogtri
afterlogonondatabase
insertintolog_table(username,logon_time,address)values(ora_login_user,sysdate,ora_clien
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 笔记 07