【数据库_06】Oracle

二、数据库操作

1. 创建表空间

CREATE TABLESPACE "FIRSTDEMO"
DATAFILE  "C:/oracle/firstdemo" 
SIZE 100M 
AUTOEXTEND ON 
NEXT 10M

2. 创建用户

CREATE USER "scott" 
IDENTIFIED BY "tiger" 
DEFAULT TABLESPACE "EXAMPLE"

# 授权
GRANT dba TO "scott"    --- 开发人员一般为 resource

3. 建表

CREATE TABLE FIRST (
    ID NUMBER(6,2) ,
    NAME VARCHAR2(10)
)  

oralce 的数据类型
- number
  er(n):一个长度为n的整数
              number(n,m):整数部分为  n - m, 小数部分为 m
- varchar、varchar2
  varchar2长度可变
- date
- clob
  据类型，4G
- blob
  据，4G

4. CRUD

insert
delete
update
select

5. 序列

- oracle 无法想 MySQL 一样字节实现自增，需要使用序列
- create sequence s_xxxx;   -- 一般前缀为 s_ 或者 seq_

- currval  : 获取当前值
- nextval : 获取下一个值

- 自增
- insert into emp values(s_emp.nextval,"name","money");

- 虚拟表
- oracle 中一些方法不写 from dual 将会报错，故引入虚拟表 dual
  elect s_xxx.currval from dual;

# 补充 
- 获取系统时间
- select sysdate from dual;

三、单行函数

1. 字符函数

① 转小写
    lower("XXX")
② 转大写
    upper("xxx")

2. 数值函数

* round(xxx.xxx) : 四舍五入，取整
        round(xxx.xxx,n) : 四舍五入，取指定位数

3. 转换函数

- to_char() : 转为字符  to_char(date,"yyyy-MM-dd HH:mm:ss") 指定格式将日期转换为字符串
- to_date(): 转为日期

4. 通用函数

- nvl(m,n) 当 m 为 null 时，m 取 n
- 判断    
select ename,
    case
           when i = "Y" then "及格"
    case
        "不及格"
    end
from emp;

//Oracle特有
select ename,
decode( 
    i,
    "Y"  "及格",
    "不及格"
) from emp
        
- 多行函数 --- 聚合函数

5. 分页函数

select b.* from (
    select rownum rn,a.* from (
           select * from emp order by sal desc
    ) a
    where rownum < 10
) b where rn > 5

select 查询后产生 roenum；且不能跳，所以 rownum 只支持小于不支持大于（使用别名）

四、视图

1.概述

* 视图封装了一组复杂的查询语句。

2. 创建

create [or replace] view viewdemo
as
select * from emp where sal > 1000
with read only

# 视图可以进行修改，其实质时修改原始数据，一般设置为只读(with read only)

五、索引

就是在表的列上构建一个二叉树，提高查找效率但是会降低增删改效率。

1.单列索引

- create index index_ename on emp(ename)
- 单列索引触发条件为，必须是索引列中的原始值  
- 如： select * from emp where ename = "SCOTT";

2. 复合索引

- create index index_ename_deptno on emp(ename，deptno)
- 符合索引第一列为优先检索条件，要想触发复合索引必须包含符合索引原始值
    * 如：select * from emp where ename = "SCOTT";  -- 触发单列索引
          select * from emp where ename = "SCOTT" and job = ""abc;  -- 触发复合索引
          select * from emp where ename = "SCOTT" or job = "abc";  -- 不触发索引

六、PL/SQL

1. 语法

declare
    //声明
begin
    //执行
end;


# 例如
declare
    i number(3) := 10;
begin
    dbms_output.put_line(i);
end;

2. 常量与变量

① 常量定义
    name varchar2(3) := "张无忌"；
    id number(3) := 11;
② 变量定义
    name varchar2(3);
    id number(3);
③ 引用变量
    name emp.ename%type;  // name 的类型与 emp 表的 ename 字段类型一致
④ 记录型变量
    erow emp%rowtype;    //emp 表的一行数据

3. if

# 示例【从控制台输入分数，打印等级】
declare
    i number(3) := &i;
begin
    if i > 85 then "及格";
           elsif i > 60 then "优秀";
    else
        "不及格";
    end if;
end;

4. 循环

# 示例【第一种】
declare
    i number(3) := 1;
begin
    while i <= 10 loop
        dbms_output.put_line(i);
        i := i + 1;
    end loop;
end;
    

# 示例【第二种】
declare
    i number(3) := 1;
begin
    loop
        exit when i > 10;
            dbms_output.put_line(i);
        i := i + 1;
    end loop;
end;
    

# 示例【第三种】
declare
    i number(3) := 1;
begin
    for i in 1 .. 10 loop
        dbms_output.put_line(i);
    end loop;
end;

5. 游标

- 游标类似于集合，用于存储返回的多条数据
- 示例
declare
    cursor c1 is
        select * from emp;
    c_emp emp%rowtype;
begin
    open c1;        //打开游标
        loop
            fetch c1 into c_emp;     //取出一行数据
            exit when c1%notfound;    //游标结束条件
            dbms_output.put_line(c_emp.ename || " 工资 " || c_emp.sal);
        end loop;
    close c1;        //关闭游标
end;

七、存储过程与存储函数

1. 存储过程

将一组完成特定功能的SQL语句集。经过编译后存储到数据库中。

//创建一个打印 Hello World 的存储过程
create or replace procedure p1 is
    string varchar2(20) := "Hello World";
begin
    dbms_output.put_line(string);
end;    

//有参数的存储过程
create or replace procedure p1(str1 varchar2) is
    str2 varchar2(20) := "传入的数据为";
begin
    dbms_output.put_line(str2 || str1);
end;

//有返回的存储过程
create or replace procedure p1(str1 varchar2,s out varchar2) is
    str2 varchar2(20) := "传入的数据为";
begin
    s := str1 || str2;
end;

2. 存储函数

//创建一个存储函数
create or replace function fun1(str varchar2) 
return varchar2 
as  // is 也可以
        s varchar2(20) := "Hello";
begin
    dbms_output.put_line(s);
    return str;
end;

3. 存储过程与存储函数的区别

- 存储函数有一个返回值而存储过程没有返回值
- 二者都可以通过 out 指定多个输出参数

八、触发器

1. 语法

//语句级触发器 不论这条语句影响多少行，在触发后执行一次
//执行 emp 表 插入操作时触发
create tirgger first 
before     // 执行时机 before 之前   after 之后
insert on     //触发的操作
emp            //哪张表
declare

begin
  dbms_output.put_line("将要执行插入操作");
end;

//行级触发器  语句作用的每一条记录都会被触发，使用 old 和 new 伪记录变量
//更新 emp 表 sal 字段时触发
create or replace trigger tt 
before update of sal on emp
for each row
begin
    if :old.sal > :new.sal then        -- 涨薪前 大于 涨薪后
        raise_application_error(-20002,"涨薪幅度不能为负");
    end if;
end;

2. 伪记录变量

:old   --->  代表旧数据
:new   --->  代表新数据

九、java 使用 oracle

1. 普通操作

//注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取 connection
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "scott", "tiger");
//定义 sql
String sql = "select * from emp where empno = ?";
//预编译sql
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//赋值
preparedStatement.setObject(1,7788);
//执行sql
ResultSet resultSet = preparedStatement.executeQuery();

2. 使用存储过程

//注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取 connection
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "scott", "tiger");
//定义 sql
String sql = "{call p1(?,?)}";
//编译
CallableStatement callableStatement = connection.prepareCall(sql);
//赋值，
callableStatement.setObject(1,7788);
//输出参数 out
callableStatement.registerOutParameter(2, OracleTypes.NUMBER);
//执行
callableStatement.execute();
//获取输出参数的值
callableStatement.getInt(2);

3. 使用存储方法

//注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取 connection
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "scott", "tiger");
//定义 sql
String sql = ""{? = call f1(?)}"";
//编译
CallableStatement callableStatement = connection.prepareCall(sql);
//输出参数 out
callableStatement.registerOutParameter(1, OracleTypes.NUMBER);
//赋值，
callableStatement.setObject(2,7788);
//执行
callableStatement.execute();
//获取输出参数的值
callableStatement.getInt(1);

4. jar包坐标

    
        com.oracle
        ojdbc14
        10.2.0.4.0