1. 触发器的运用
校验数据的合法性
自动增长
数据库同步 (after)
周三不能插入员工信息
--技术预演
select to_char(sysdate,'D') from dual; --返回1-7的数值, 1代表星期天
select to_char(sysdate-2,'day') from dual;--返回英文字符串(跟国际化有关), 空格问题
--创建语句级触发器
create or replace trigger tri_test3
before
insert
on emp
declare
begin
--当前是星期几的判断
if to_char(sysdate,'D')=4 then
dbms_output.put_line('星期三不能插入员工信息');
RAISE_APPLICATION_ERROR(-20527,'星期三不能插入员工信息');
end if;
end;
--示例
insert into emp (empno,ename) values(9527,'HuaAn');
模拟mysql的主键自动增长
--建表
create table tbtest2(
tid number(10),
tname varchar2(30)
)
--创建序列
create sequence seq_tbtest2;
--触发器
create or replace trigger tri_test4
before
insert
on tbtest2
for each row
declare
idx number(10);
begin
--判断要插入的数据中的主键是否有值
if :new.tid is null then
--没有值，取序列赋值
select seq_tbtest2.nextval into idx from dual;
--赋值即将要插入的数据
:new.tid:=idx;
dbms_output.put_line('编号:' || idx);
end if;
end;
--测试
insert into tbtest2 (tname) values('HuaAn');
2 高效的删除重复的数据
构建重的数据
insert into tbtest2 values(2,'HuaAn');
insert into tbtest2 values(2,'HuaAn');
insert into tbtest2 values(2,'HuaAn');
insert into tbtest2 values(3,'唐寅');
insert into tbtest2 values(3,'唐寅');
insert into tbtest2 values(3,'唐寅');
--删除前备份
select t.*,rowid from tbtest2 t where rowid>(select min(rowid) from tbtest2 where tname=t.tname and tid=t.tid)
--删除重复数据
delete from tbtest2 t where rowid>(select min(rowid) from tbtest2 where tname=t.tname and tid=t.tid);
3 jdbc调用存储过程
3.1 返回单个数值
数据库中创建获取指定员工年薪的存储过程
create or replace procedure proc_getyearsal(inempno in emp.empno%type, outyearsal out emp.sal%type)
is
vempno emp.empno%type;
vyearsal emp.sal%type;
begin
--要查询的员工编号
vempno:= inempno;
--获取年薪
select sal * 12 + nvl(comm,0) * 12 into vyearsal from emp where empno=vempno;
--赋值输出参数
outyearsal:=vyearsal;
end;
创建java普通工程
引入 资料\jar包\oracle\ojdbc14.jar 并加入build path
编写测试类SPCTest.java
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;
public class SPCTest {
private static String url="jdbc:oracle:thin:@192.168.220.10:1521:orcl";
private static String user = "tbtest";
private static String pwd = "tbtest";
private static String driverClass="oracle.jdbc.driver.OracleDriver";
public static void main(String[] args) throws Exception {
spc1();
}
private static void spc1() throws Exception {
Class.forName(driverClass);
Connection con = DriverManager.getConnection(url, user, pwd);
//创建调用储存过程的执行
CallableStatement call = con.prepareCall("{call proc_getyearsal(?,?)}");
call.setInt(1, 7788);//查询7788这个员工的年薪
//注册输出参数
call.registerOutParameter(2, OracleTypes.NUMBER);
//执行
call.execute();
//获取输出参数的值, 序号要一致
Object object = call.getObject(2);
System.out.println(object);
call.close();
con.close();
}
}
3.2 返回游标
创建存储过程
--返回游标create or replace procedure proc_allemps(outemps out sys_refcursor)isbeginopen outemps for select * from emp;end;
编写方法spc2
private static void spc2() throws Exception {Class.forName(driverClass);ResultSet rs = null;Connection con = DriverManager.getConnection(url, user, pwd);//创建调用储存过程的执行CallableStatement call = con.prepareCall("{call proc_allemps(?)}");//OracleTypes.CURSOR: 输出参数的类型为游标，call.registerOutParameter(1, OracleTypes.CURSOR);call.execute();//转成oracle的方法OracleCallableStatement ocall = (OracleCallableStatement)call;rs = ocall.getCursor(1);//jdbc方式//转成ResultSet//rs = (ResultSet)call.getObject(1);//遍历while(rs.next()){System.out.println(rs.getString("ename") + ":" + rs.getString("sal"));}rs.close();call.close();con.close();}
4 hibernate调用存储过程
4.1 返回单个数值
创建hibernate工程且引入hibernate的jar包
添加hibernate.cfg.xml配置文件，连接数据库
复制 day05\资料\HibernateUtils.java到工程中
创建测试用例SPCTest并添加测试方法,如下
public void tt(){//获取指定员工的年薪Session session = HibernateUtils.openSession();Transaction transaction = session.getTransaction();transaction.begin();//创建调用存储过程对象ProcedureCall call = session.createStoredProcedureCall("proc_getyearsal");//参数1： proc_getyearsal中的参数的序号//参数2： proc_getyearsal中的参数的类型//参数3： proc_getyearsal中的参数的输入出类型call.registerParameter(1, int.class, ParameterMode.IN).bindValue(7788);call.registerParameter(2, double.class, ParameterMode.OUT);//执行存储过程ProcedureOutputs outputs = call.getOutputs();//获取返回值Object obj = outputs.getOutputParameterValue(2);transaction.commit();System.out.println(obj);}
4.2 返回游标
创建Emp实体类
public class Emp {private Long empno;private String ename;private double sal;public Long getEmpno() {return empno;}public void setEmpno(Long empno) {this.empno = empno;}public String getEname() {return ename;}public void setEname(String ename) {this.ename = ename;}public double getSal() {return sal;}public void setSal(double sal) {this.sal = sal;}public String toString() {return "Emp [empno=" + empno + ", ename=" + ename + ", sal=" + sal + "]";}}编写映射文件
```xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<class name="com.itheima.domain.Emp" table="emp">
<id name="empno"></id>
<property name="ename"></property>
<property name="sal"></property>
</class>
</hibernate-mapping>
```
3. 在映射文件中添加sql-query, 注：sql-query与class同级
```xml
<!-- name 查询语句的ID， callable 调用存储过程 -->
<sql-query name="proctest" callable="true">
<!-- 返回类型 -->
<return class="com.itheima.domain.Emp"></return>
{call proc_allemps(?)}
</sql-query>
```
4. 编写测试方法
```java
@Test
public void ttCursor(){
//获取指定员工的年薪
Session session = HibernateUtils.openSession();
Transaction transaction = session.getTransaction();
transaction.begin();
Query query = session.getNamedQuery("proctest");
List<Emp> list = (List<Emp>)query.list();
for(Object obj : list){
System.out.println(obj);
}
transaction.commit();
}
```
5 其它扩展
5.1 自定义类型
类似java中的自定义pojo类
语法
declare
type 名称 is record (名称1 类型, 名称2 类型...);
begin
end;
示例：
declare
type mytype is record (eno emp.empno%type, en emp.ename%type, salary emp.sal%type);
vrow mytype;
begin
select empno, ename, sal into vrow from emp where empno=7369;
dbms_output.put_line('编号：=' || vrow.eno || ' 名称：' || vrow.en || ' 工资:' || vrow.salary);
end;
5.2 数组
语法
Type 数组类型名称 is table of integer index by binary_integer;
示例
declare
type myarray is table of integer index by binary_integer;
varr myarray;
begin
for i in -5..5 loop
--括号代表下标
varr(i) :=i;
end loop;
for i in -5..5 loop
dbms_output.put_line(varr(i));
end loop;
end;
特点：
binary_integer => 下标为正负整数
下标的长度没有限制
没有赋值的下标是不能取值的
5.3 程序包与包体
程序包=java interface 接口
包体=java implements 实现
语法
create or replace package 名称
is
--申明，类型的定义
--过程名称或函数的定义
end;
6. 数据库表设计
6.1 三大范式
作用：衡量数据库设计是否完善
每一列都是不可分割的基本数据项, 列中的内容不能出现重复。（以下是违反的示例)
| 学生信息 | |||||
|---|---|---|---|---|---|
| sid | sname | 住址 | 省份 | 市 | 区 |
| 广东省深圳市宝安区 | 广东省 | 深圳 | 宝安区 |
所有的非主键列都完全依赖主键列
选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)
姓名与年龄是依赖于学号， 而成绩和学分依赖于课程名称。
解决方案：拆成2张表
学生表(学号，姓名，年龄)， 课程表(课程编号，课程名称，成绩，学分)， 选课表(学号，课程编号)
非主键列之间不存在传递依赖 (经常被违反的原因：牺牲空间换取时间, 增加冗余换取效率)
学生关系表为Student(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)
(学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话) 学生信息与学校信息
存在的传递
学号 -> 学院->地点->电话
解决方案：
Student(学号, 姓名, 年龄,学院的编号)
学院(学院地点, 学院电话)
违反的例子 ： 订单（总金额）与订单明细(单价, 数量，金额)
6.2 设计原则
每张表描述一个实体（对象）
给每张表创建代理主键
建立表之间关系（一对一 一对多， 多对多）
5445
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
