oracle基础知识整理.docx

oracle基础知识整理.docx

《oracle基础知识整理.docx》由会员分享，可在线阅读，更多相关《oracle基础知识整理.docx（18页珍藏版）》请在冰豆网上搜索。

oracle基础知识整理

基础

Sql*plus

setlinesize设置行宽度

setpagesize设置页面长度

edit路径打开路径所在文档

spool路径select……spooloff将查询结果保存在指定路径

clear清屏

showerror查看错误

setserveroutputon打开输出

dbms_output.put_line（“”）;输出数据

desc表名查看表结构

用户管理

conn用户名/密码连接数据库

disc断开连接

showuser查看用户

password用户名修改密码

createuser用户名indetifiedby密码创建用户

createrole角色notindetifid/indetifiedby密码创建角色（不需要验证/验证）

角色是一组权限（系统权限、对象权限）的集合

grant系统权限to用户名/角色[withadminoption]

将权限授予用户或角色[可以将权限授予别人，当回收本级权限时不会级联回收]

grant对象权限to用户名/角色[withgrantoption]

将对象权限授予用户或角色[可以将权限授予别人，当回收本级时会级联回收]

revoke权限/角色from用户名/角色回收权限

alteruser用户名accountlock锁定用户（该用户无法使用，当用户所属的对象可以使用）

alteruser用户名accountunlock解除锁定

dropuser用户名[cascade]删除用户[将用户所属对象一并删除]

droprole角色删除角色，拥有该角色的用户将不再有该角色下的权限

conn用户名/密码assysdba以sys用户权限登陆（前提该操作系统用户属于dba组）

基本语法

ddl

数据定义语言（createalterdrop）

●create

createtable表名（

字段1数据类型（长度），

……

字段n数据类型（长度）

）

createtable表名asselect……

select*into新表from旧表

●alter

altertable表名

add字段数据类型（长度）增加字段

modifycolumn字段字段类型（长度）修改字段

drop字段删除字段

rename旧字段名to新字段名

rename旧表名to新表名

●drop

droptable表名删除表

dml

数据操纵语言（insertupdatedeleteselect）

●insert

insertinto表名values（字段1，……字段n）;按表的字段依次顺序插入

insertinto表名（字段1，字段2）values（值1，值2）按字段插入

insertinto表名（字段……）select……将查询结果插入

●update

update表名set字段=值[where条件]

●delete

deletefrom表名[where条件]

trunctable表名（不可回退的删除速度快）

●select

select[distinct]字段

from表名

where条件

groupby分组

having分组后筛选条件

orderby排序

进阶

复杂查询

子查询

●单列查询

select*from表名where字段=（select字段from表名where条件）

select*from表名where字段in（select字段from表名where条件）

●多列查询

select*from表名where（字段1，字段2）=（select字段1，字段2from表名where条件）

●all

select*from表名where字段表达式all（select……）

●any

select*from表名where字段表达式any（select……）

●子表

select*

fromt1,（select……）t2

连接查询

●自然连接

select*fromt1,t2wheret1.z=t2.z

select*fromt1innerjoint2ont1.z=t2.z

●左连接（左表全部加右表符合条件的）

select*fromt1leftjoint2ont1.z=t2.z

select*fromt1,t2wheret1.z=t2.z（+）

●右连接

select*fromt1rightt2ont1.z=t2.z

select*fromt1,t2wheret1.z（+）=t2.z

●全连接

select*fromt1fulloutjoint2ont1.z=t2.z

分页查询

select*from（selectt1.*,rownumnumfrom（select语句）t1wherenum<=最大值）twherenum>=最小值

合并查询

union去重复联合查询unionall不去重复intersect取交集minus取左表有右表没有的值

函数（常用）

单行函数:

查多行只出一行（多用于分组）

多行函数：

查几行出几行

●聚合函数

sum（）avg（）max（）min（）count（）avg会忽略null值

●转换函数

convert（数据类型，字段，格式）cast（字段as数据类型）

●数值函数

abs（）绝对值floor（）小于或等于的最大正式ceil（）大于等于的最小整数

round（）四舍五入trunc（）截取小数mod（值1，值2）取余

●日期函数

add_mouths（）

●字符串函数

substr（字段，起始位置，长度）

lower（）将英文换为小写

upper（）将英文换为大写

length（）查看长度

replace（字段，原值，替换为值）

●其他函数

case字段when值then…when值then…else…end

casewhen字段=？

then…else…end

decode（字段，条件1，值1，条件2.值2……）

dump（字段）查看字段类型

nvl（字段，值）若字段为null则值为值

事务

数据操纵的一组集合

savepoint保存点名字设置保存点

……dml

rollbackto保存点名字回退到保存点，取消设置保存点之后的操作

commit提交

rollback回退

☞可以设置多个保存点，但保存点只可以回退一次，提交之后不可回退

完整性

●基本

notnull非空只能在行级定义

unique唯一不可以重复，但可以为null并且可以多处为null

check检查约束

default默认

primarykey主键非空的唯一标示可以自动创建索引

foreignkey外键跟主表的主键或者唯一约束所在列对应

建表的时候先建主表再建从表，删除的时候先删从表再删主表

●定义

行级定义：

在字段名之后直接定义

表级定义:

建完字段后再用constraint定义

constraint约束名约束类型字段

●修改

altertable表名

addconstraint约束名约束类型字段

modifyconstraint约束名约束类型字段

dropconstraint约束名

序列

createsequence序列名

startwith起始值

incrementby步长

maxval最大值

minval最小值

cycle/nocycle循环、不循环

nocache/cache缓存

序列名.currval当前值

序列名.nextval下一个值

视图

createview视图名asselect……

索引

createindex索引名on表名（字段名）

高级

数据类型

●基本

数值型：

number（长度，精度），decimal、int

字符串：

char,varchar,nvarchar,nchar,clob,blob

日期：

date,timestram

●表

变量名表名.字段名%type表的某个字段类型

变量名表名%rowtype某个表的记录类型

●复合类型

type类型名isrecord（

变量名数据类型，

……

）

变量名类型名

●游标

type游标名isrefcursor定义游标类型

游标变量游标名

open游标变量forselect……打开游标

fech游标变量into循环取值

close游标变量关闭游标

游标变量%notdate

控制结构

●判断

·if条件then……endif

·if条件then……else……endif

·if条件then……elsif条件then……else……endif

●循环

·when条件loop……endloop

·loop……exitwhen条件endloop

存储过程

●语法

createprocedure过程名（变量名in数据类型，变量名out数据类型）

as

declare变量名数据类型--定义变量，可以用：

=赋初值

begin

处理语句

exception--异常处理

when异常处理then……

whenothersthen……

end

●分页存储过程

·createpackagemy_package

is

typemy_cursorisrefcursor

end

·createprocedurecut（v_in_tableinvarchar,v_in_sizeinnumber,v_in_pageinnumber,

v_out_countoutnumber,v_out_yeoutnumber,v_out_rsoutmy_package.my_cursor）

as

declarev_sqlvarchar（2000）;

declarev_startnumber;

declarev_endnumber;

begin

executeimmeteate‘selectcount（*）into’||v_out_count||’from’||v_in_table;

ifmod（v_out_count,v_in_size）=0then

v_out_ye:

=v_out_count/v_in_size;

else

v_out_ye:

=v_out_count/v_in_size+1;

endif;

v_start:

=v_in_size*（v_in_page-1）+1;

v_end:

=v_in_size*v_in_page

v_sql:

=’select*from（selectt1.*,rownumnumfrom（select*from‘||v_in_table||’））twherenum’>=v_start;

openv_out_rsforv_sql;

end

函数

createfunction函数名（参数列表）

return返回值类型

as

declare变量名数据类型；

begin

dml……

return变量名；

end

包

包用于把同一对象的操作放一块，方便管理

●创建包（用于定义类型）

createpackage包名

is

type类型名isrecord;

type游标名isrefcursor;

function函数名（参数列表）return返回值类型；

procedure过程名（参数列表）；

end

●创建包体

createpackagebody包名

is

变量名类型名；

变量名游标名；

function函数名（参数列表）return返回值类型

is

begin

……

end;

procedure过程名（参数列表）

is

begin

……

end;

end

触发器

●dml

createtrigger触发器名

before/after--在操作之前/之后执行之前的值为：

new.字段名之后的值为:

old.字段名

delete[orupdateorinsertof字段名]

on表名

[foreachrow]--加上此句为行级触发，每dml一行都会触发，不加为表级触发，不考虑多少行，只执行一次

begin

--处理语句（可以抛异常调用函数raise_application_error（错误号，‘错误信息’）

错误号在-20001到-29000之间；

可以用deletingupdatinginserting判断操作类型）

end;

●ddl

createtrigger触发器名

after事件

on用户名.shame

begin

end

●系统

createtrigger触发器名

before/after

事件（logonlogoffstartupshutdown）

ondatabase

begin

end

●管理触发器

altertrigger触发器名diable锁定触发器

altertrigger触发器名enable解锁触发器

droptrigger触发器名删除触发器

Java调用数据库

jdbc

●直接调用

publicstaticvoidmain（String[]args）throwsException{

//TODOAuto-generatedmethodstub

Connectionconn=null;

PreparedStatementps=null;

Statementstmt=null;

ResultSetrs=null;

try{

Class.forName（"oracle.jdbc.driver.OracleDriver"）;

conn=DriverManager.getConnection（"jdbc:

oracle:

thin:

@127.0.0.1:

1521:

orcl","test","test"）;

//预定义类型

ps=conn.prepareStatement（""）;

ps.setString（1,""）;

ps.executeUpdate（）;//执行增删改

rs=ps.executeQuery（）;//执行查询，需要定义结果集来接收结果

while（rs.next（））{

System.out.println（rs.getString

（1））;

}

//普通类型

stmt=conn.createStatement（）;

stmt.executeUpdate（"sql"）;//执行增删改

rs=stmt.executeQuery（"sql"）;//执行查询，需要定义结果集来接收结果

while（rs.next（））{

System.out.println（rs.getString

（1））;

}

}catch（Exceptione）{

//TODOAuto-generatedcatchblock

e.printStackTrace（）;

}finally{

if（rs!

=null）{

rs.close（）;

}

if（stmt!

=null）{

stmt.close（）;

}

if（ps!

=null）{

ps.close（）;

}

if（conn!

=null）{

conn.close（）;

}

}

}

●从外部文件获取连接信息

Propertiespp=newProperties（）;

FileInputStreamfis=null;

try{

fis=newFileInputStream（"jdbc.properties"）;

pp.load（fis）;

Stringurl=pp.getProperty（"url"）;

System.out.println（url）;

}catch（FileNotFoundExceptione）{

//TODOAuto-generatedcatchblock

e.printStackTrace（）;

}

事务处理

Connectionconn=null;

PreparedStatementps=null;

try{

Class.forName（"oracle.jdbc.driver.OracleDriver"）;

conn=DriverManager.getConnection（"jdbc:

oracle:

thin:

@127.0.0.1:

1521:

orcl","test","test"）;

//设置为不自动提交

conn.setAutoCommit（false）;

ps=conn.prepareStatement（""）;

ps.executeUpdate（）;//执行增删改

mit（）;

}catch（Exceptione）{

//TODOAuto-generatedcatchblock

conn.rollback（）;

e.printStackTrace（）;

}finally{

//关闭资源

}

调用无返回值过程

Connectionconn=null;

CallableStatementcs=null;

try{

Class.forName（"oracle.jdbc.driver.OracleDriver"）;

conn=DriverManager.getConnection（"jdbc:

oracle:

thin:

@127.0.0.1:

1521:

orcl","test","test"）;

cs=conn.prepareCall（"{call过程名（?

）}"）;

cs.setString（1,"值"）;

cs.execute（）;

}catch（Exceptione）{

//TODOAuto-generatedcatchblock

e.printStackTrace（）;

}finally{

//关闭资源

}

有非列表返回值过程

//1?

输入参数2?

输出参数

cs=conn.prepareCall（"{call过程名（?

，?

）}"）;

cs.setString（1,"值"）;

cs.registerOutParameter（2,oracle.jdbc.OracleTypes.VARCHAR）;

cs.execute（）;

Strings=cs.getString

（2）;

System.out.println（s）;

有列表型返回参数

//1?

输入参数2?

输出参数（游标）

cs=conn.prepareCall（"{call过程名（?

，?

）}"）;

cs.setString（1,"值"）;

cs.registerOutParameter（2,oracle.jdbc.OracleTypes.CURSOR）;

cs.execute（）;

rs=（ResultSet）cs.getObject

（2）;

while（rs.next（））{

System.out.println（rs.getString（""））;

}

数据库的备份与恢复

●备份

expuserid=用户名/密码@数据库实例table=（t1,t2）file=”地址.dmp”[rows=n]

expuserid=用户名/密码@数据库实例owner=用户名file=”地址.dmp”

expuserid=用户名/密码@数据库实例full=yfile=”地址.dmp”

●恢复

impuserid=用户名/密码@数据库实例table=（t1,t2）file=”地址.dmp”[touser=其他用户]

impuserid=用户名/密码@数据库实例file=”地址.dmp”[fromuser=用户1touser=用户2]

impuserid=用户名/密码@数据库实例table=（t1,t2）file=”地址.dmp”