Oracle开发基础重点
Oracle一般操作
表空间
--创建表空间
create tablespace 表空间名
datafile 表空间存放路径
size 表空间默认大小
autoextend on 设置自动增长，超量自动扩容
next 10m 每次扩容大小
用户
--创建用户
create user 用户名
identified by 密码
default tablespace 从属于表空间名;
--用户赋权（dba、等等）
grant 权限 tp 用户名;
表
--创建表
create table 表名称(
字段名 类型(长度) primary key,--主键约束
字段名 类型(长度),
...
)
--增加字段语法
alter table 表名称 add(
列名 类型 [可以在此指定默认值],
列名 类型
--eg：new varchar2(200)
)
--修改字段语法
alter table 表名称 modify(
列名 类型 [可以在此指定默认值],
列名 类型
)
--修改字段名
alter table 表名称 rename column 原列名 to 新列名
--eg：alter table t_test rename column old to new
--删除字段
--删除一个字段
alter table 表名称 drop column 列名
--删除多个字段
alter tablr 表名称 drop(列名1,列名2,...)
--删除表
drop table 表名称
表数据
--插入数据
insert into 表名(列名1,列名2,...)values(值1,值2,...);
commit;
--修改数据
update 表名 set 列名1=值1,列名2=值2,...where 条件;
commit;
--删除数据
--删除方式一
delete from 表名 where 删除条件;
commit;
--删除方式二
truncate table 表名称
Oracle 查询操作
单表查询
select
distinct/*去重关键字*/(列名1,列名2,...或者 *)
from
表名,表别名,视图名,...
where
条件:like,and,or,范围,>=,<=,between小and大,...
group by
分组条件列
having
过滤条件
--聚合关键字
count sum max min avg
--伪列
rowid/*一行数据对应的物理地址*/
rownum/*标识结果集的序号*/
多表查询
--消笛卡尔积 主键关联外键
select
表1.列名1, 表2.列名2, ...
from
表1, 表2, ...
where
条件语句
左右外连接关键字
left(right) join --SQL语句
+ --Oracle语句
子查询
select * from 表名 where **>(select * from 表名)
--子查询语句的结果只能是一条记录
几条关键语句
-- 分页查询
select from (select rownum r,t.* from t where rownum <=20 order by **) where r>10
--select子查询
select id,name,
(select name from t_address where id=addressid) addressname
from t_owners
--from后的子查询
(select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id)
where 业主类型='居民'
Oracle对象
视图
--创建视图
create or replace force view v_test
as subquery(一条完整的sql语句)
--只读 read only
--删除视图
drop view v_test
--查询视图
select * from v_test
视图其实是一个虚拟的表，它的数据其实来自于表。如果更改了视图的数据，表的数据也自然会变化，更改了表的数据，视图也自然会变化。一个视图所存储的并不是数据，而是一条 SQL语句。
物化视图
--创建物化视图
create meterialized view v_test as subquery
--创建增量物化视图
--先创建物化视图日志,涉及几个表就创几个
create meterialized view log on 表名 with rowid;
--创建增量物化视图
create meterialized view 视图名
refresh fast
as
subquery
序列
--创建序列
create sequence 序列名
--提取下个值
select 序列名.nextval from dual
--提取当前值
select 序列名.currval from dual
同义词
--创建同义词
create [是否共有加public] synonym 同义词名称 for 表名,视图名,...
索引
--创建索引
create [唯一索引 unique] index 索引名 on 表名(列名,列名.列名,.../*复合索引*/) [反向索引加reverse]
Oracle编程
PL/SQL
--plsql基本语法结构
declare
--声明变量
变量名 类型(长度)
begin
--变量赋值
变量名:= 值
select 列名 into 变量名 from 表名 where 条件/*从表中数据直接赋值*/
代码逻辑
exception
end
声明变量属性类型
--引用型:引用某表某列的字段类型
表名.列名%type引用型
--记录型:标识某个表的行记录类型
表名%rowtype记录型
异常
--记住两个异常
NO_DATA_FOUND:没有查到数据
TOO_MANY_ROWS:查到多行数据
--使用异常
exception
when 异常名 then
业务逻辑
条件判断
--基本语法
if 条件 then
业务逻辑
end if;
--基本语法2
if 条件 then
业务逻辑
else
业务逻辑
end if;
--基本语法
if 语法 then
业务逻辑
elsif 条件 then
业务逻辑
else
业务逻辑
end if;
输出语句
DBMS_OUTPUT.PUT_LINE()
循环
--无条件循环
loop
--循环体
end loop;
--条件循环
while 条件
loop
--循环体
end loop;
--for循环
for 变量 in 起始值..终止值
loop
--循环体
end loop;
游标
--声明游标(无参)
cursor 游标名称 is subquery
--声明游标(有参)
cursor 游标名称(声明变量) is subquery(条件中有控制变量)
--使用游标语法
open 游标名称(有参无参)
loop
fetch 游标名称 into 变量
exit when 游标名称%notfound
end loop;
close 游标名称;
--用for循环使用游标
open 游标名称(有无参)
for 要取变量 in 游标名称(有无参)
loop
end loop;
end;
存储函数
--存储函数可以接收多个参数,返回一个结果
--创建存储函数
create or replace function 函数名
(参数名 类型 , 参数名 类型 , ...)
return 结果变量的数据类型
is
变量声明
begin
逻辑业务
return 结果变量;
异常处理
end;
存储过程
--存储过程可以通过传出参数返回多个值
--存储过程不可以在select语句中使用
--存储过程一般封装一段事务
--创建不带返回参数的存储过程
create or replace procedure 过程名
(参数名 类型 , 参数名 类型 , ...)
is\as --都可以使用
变量声明
begin
逻辑业务
异常处理
end;
存储过程使用
call 过程名(符合定义类型的参数);
JDBC调用不带参数返回的存储过程
java.sql.CallableStatement = java.sql.connection.prepareCall("{call 过程名(?,?,?,?,...);}")
--创建带返回参数的存储过程
--过程参数有:IN 传入参数|OUT 传出参数,主要用于返回程序运行结果|IN OUT 传入传出参数
create or replace procedure
(参数名 类型, 参数名 过程参数 类型,...)
is
begin
业务逻辑
end;
--使用
declare
定义传出参数的变量
begin
过程名(符合定义的参数)
同时可以输出传出参数
end;
JDBC调用带参数返回的存储过程
--要定义传出数据类型
//提取传出参数
数据类型 变量名 = stmt.get(index)
return
触发器
create or replace trigger 触发器名
before | after
[delete]|[insert]|[update] of 列名
on 表名
[for each row] [when(条件)]
declare
begin
plsql语法块
end;
--for each row的作用是标准此触发器是行级触发器，不写的话是语句级触发器
使用前置触发器
create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row
declare
begin
:new.usenum:=:new.num1-:new.num0;
end
使用后置触发器
create trigger tri_owners_log
after
update of name
on t_owners
for each row
declare
begin
insert into t_owners_log values(sysdate,:old.id,:old.name,:new.name);
end;
