二、数据库操作
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
