oracle使用.docx

oracle使用.docx

《oracle使用.docx》由会员分享，可在线阅读，更多相关《oracle使用.docx（31页珍藏版）》请在冰豆网上搜索。

oracle使用

一,安装启动登陆部分

1,数据库类型<主要>:

通用

事物处理:

适用于电子交易

数据仓库:

历史性数据进行优化

2,全局数据库名与SID

全局数据库一般与SID一样

　　3,启动必须服务：

OracleService（SID名）

Oracle（SID名）TNSListener

　　4,登陆ＳＱＬ／ＰＬＵＳ，主机字符串，服务器是本机的话不用输入，否则需要配置

二，示例及常识部分

１，ｅｍｐ表为示例员工表

2，sql/plus中使用setlinesize200

setpagesize200

3，dual算是名义表如：

selecttrunc（45.926,2）fromdual;

4,ed命令：

写入缓存

5，save'd:

\empsql.txt'创建文件

6，clearscreen:

清屏

7，connectscott/tiger连接命令

8，@d:

\empsql.txt，导入文件

9，run或者r，引用上一行命令

10，getd:

\empsql.txt导入文件中命令（不执行）

11，/重复上一次执行命令

12,showuser显示用户

13，setsqlprompttom>结果命令由sql>变为tom>

三，Oracle基本命令

1，启动：

sqlpluswusername/password:

本机启动

sqlpluswusername/password@connect_string

2,Sqlplus基本命令

desc:

显示表结构

list:

列出Sql缓冲区中一行或多行命令语句

Exit:

退出

四,Oracle数据词典,常用的队象（表等）

1,Oracle数据词典一般以user,all,dba为前缀

如:

user_tables用户所有表

user_views试图

user_constraints约束

user_dependencies表之间关系

user_tab_columns表列信息

user_tab_patitions表分区表

user_ind_patitions表分区表

user_triggers触发器

user_types:

用户自定义类型

user_sequences:

序列

user_sources:

用户定义的子程序，类型等的源程序

user_synonyms同义词

user_sys_privs用户权限

2，名词

SGA系统全局区<内存中>（包括数据缓存等）

3，Oracle数据文件

数据文件*.dbf

控制文件*.ctl

日志文件*.log

路径：

oracle9i/oradata/oracle9i

4,数据库逻辑结构

表空间数据库〉tablespaces〉〉表s〉段s〉>数据库区间s〉块s

5，主要系统表空间：

System

Users

Temp

tools

四，ＳＱＬ语言及示例

１，分类：

１，ＤＤＬ

２，ＤＭＬ

**insert

1，适用null填充表中值

例：

insertintot（id,name,zhiye）values（70,null,'laoshi'）

2,使用绑定变量

例:

insertintot（id,name）values（&id,'&name'）;

输入id值：

50

输入name值：

'zhangsan'

3,select子查询

insertintoa（id,name）selectid,namefrombwhere

**update

1,updatetablenamesetcolumn=value[,column=value,...]where

**delete

1,delete[from]tablenamewhere

**事务控制语句：

事务是一组DML语句的逻辑组合

1,单个DDL构成事务

2，单个DCL语句构成事务

3，n个DML语句构成事务

4，事务的开始：

当第一条Sql被执行时就开始了

5，事务的结束：

四种情况

*commitorrollback

*DDL或DCL语句执行（自动）

*用户退出

*系统终止

6,事务控制----

定义事务控制点:

Savepointa

返回事务控制点:

rollbacktoSavepointa

**Select

1,语法:

select[distinct/all]*/columns表达式asdaitifromtablename

2,使用列别名:

column/表达式as别名

3,连接操作符:

||

4,比较

5,betweenand

6,in（a,b,c）

7,is[not]null

8,notandor优先级依次下降

9,日期的默认格式为'dd-month-yy'

10,'%'任意多个，'_'任意一个字符

**由select生成新表

createtableaaasselect*frombb;

３，ＤＣＬ

2,

五，函数

1，日期：

系统时间：

sysdate

插入：

insertintott（id,date）values（10,to_date（'1999-02-0410:

02:

01','yyyy-mm-ddhh:

mi:

ss'））

修改会话日期格式：

altersessionsetnls_date_format='yyyy-mm-ddhh:

mi:

ss';

2,函数分类：

*单行函数

**Character（oracle中的字符串从1开始）

lower（'SqDD'）结果:

sqdd,

upper（'ddds'）结果:

DDDS,

initcap（'mi''ssi'）结果MiSsi,

concat（'ff','ss'）结果ffss,

substr（'string',1,3）结果str注：

从第1字符取道第3字符（如果第一个字符是0和1一样）,

length（'ss'）结果2,

instr（'srd','d'）结果3,

lpad（'sss',12,'#'）结果#########sss,

**Number

round（45.926,2）结果：

45.93《四舍五入》

round（45.926,0）或者round（45.926）结果：

46

round（45.926,-1）结果：

50

trunc（45.926,2）结果：

45.92《截取小数》

trunc（45.926,0）或者trunc（45.926）结果：

45

trunc（45.926,-1）结果：

40

mod（1600,300）结果：

100《取余》

**General

**Conversion

**Date

sysdate返回系统时间

默认时间格式为dd-mon-yy

months_between（'01-sep-95','11-jan-94'）结果:

19.25返回两个日期的月份差值

add_months（'11-jan-94',6）结果:

11-jul-94在日期上加上月份数

next_day（'16-jul-07','friday'）结果:

2007-7-20（注:

今天是星期一,到星期五,显示星期五的日期）指定日期的后一天

last_day（'16-jul-07'）结果:

2007-7-31月份中的最后一天

round（）结果:

四舍五入日期:

注试验没成功

trunc（）结果:

截断日期注试验没成功

**隐式类型转换

1,varchar2orchar--->number

2,varchar2orchar--->date

3,number----->varchar2

4,date------>varchar2

**显式类型转换

1,varchar2orchar--->numberto_number

2,varchar2orchar--->dateto_date（'1988-10-10','yyyy-mm-dd'）

3,number----->varchar2to_char（id）

4,date------>varchar2to_char（sysdate,'日期格式'）

5，NVL函数，将null值转换为替代的值：

varchar2,date,number都可以

如：

nvl（id,0）

6,decode（column/expression,）像switch-case结构

selectjob,sal,decode（job,'ANALYST',sal*2,

'CLERK',sal*3,

'MANAGER',sal*4,

sal）

fromemp;

*多行函数

七,锁及数据并行访问

1,锁的类型

**表级锁

1，保护表的数据

2，在多个用户同时访问数据时确保数据的完整性

3，可以设置三种模式：

共享，共享更新和排他

******语法

Locktablein

sharemode:

特点：

仅允许其他用户执行select操作

多个用户可以对同一表中放置此锁

shareupdatemode特点：

锁定被更新的行

允许其他用户同时，查询插入更新未被锁定的行

selectforupdate可以强制使用

允许不同用户锁定表的不同行

exclusivemode（排他锁）特点：

限制性最强

仅允许其他用户查询

同一时间只允许一位用户在表上放置排他锁

nowait作用：

请求的资源被锁定则不等待，立即抱错

locktabletestinexclusivemodenowait

**行级锁

1，行被锁定,在释放前其它用户不能更改,使用commitorrollback释放锁

2，获取行级锁

方法一，insert,update语句

方法二，select...forupdate:

可以在一行或多行设置，可以执行除update之外

的其它操作

forupdatewait10（时间）子句

六，PL/SQL

语法结构：

声明

执行语句

异常处理部分

格式：

declare

empnumber;

namevarchar2;

begin

语句……；

语句……；

exception

语句……

end

变量：

数据的临时存储

变量的赋值：

1，赋值符号，2用select语句

类型：

类型还有更细的划分，如number还有很多子类型

标量：

容纳单值，没有内部组成，分为4类number,varchar,date,boolean

复合：

如可变数组，RECORDvarray,nestedtable

引用：

ref操作符，refcursor

LOB:

bolb二进制大型对象，clob，字符大型队形

nclob，国际字符大型对象，bfile，指向外部文件

注释：

命令setserveroutputon可以使服务器上的信息回显到sqlplus

在sqlplus中输出用DBMS_Output.put_line（varcharv）;

变量赋值

赋值符号为":

="可以在定义变量时直接初始化如：

declareenovarchar（13）:

='sdfsf';

使用默认default关键字如：

isExsitbooleandefaultfalse;

使用常量constant关键字如：

nameconstantvarchar（34）:

='mike';

　注释：

常量必须在声明时赋值

用数据库数据赋值：

上面的赋值属于赋静态值

用数据库赋值用select字段名　into　变量　from表来赋值，注意如果变量是单值，只能用where来限制查询出来的是一条记录

代码注释：

单行注释：

用＂－－＂

多行注释：

用/**/

属性：

引用数据库中的数据类型和对象

PL/SQL变量和常量可以具有属性

属性的类型：

%type　引用数据库列举例：

declareenoemp.empno%type;注释：

表名.字段名%type

%rowtype　代表表中的行　举例：

declareemprowemp%rowtype在执行部分用select*intoemprowfromempwhereempno='7710';然后就可以引用行级属性的属性了，如：

emprow.empno

注意，在定义的时候必须是　表名%rowtype　在执行部分，必须是select*

逻辑比较：

注意：

一个=号表示等于

控制流语句：

条件控制

ifthen

ifthenelse:

if没有括号的条件表达式then语句　else　语句　endif;endif后的必须有;

ifthenelsif　thenendif;

case

迭代控制

简单循环

loop

icount:

=icount+1;

exitwhenicount>100;--exitwhen必须有退出条件

isum:

=isum+icount;

endloop

while循环

whileicount<100loop

icount:

=icount+1;

isum:

=isum+icount;

endloop

for循环当标志符是一个连续的整形

foriin1..100loop--i为隐式定义一个整数值，当然在游标时，可以为行级变量

isum:

=isum+i

endloop

顺序控制

goto可以定义标签，无条件跳转

null空语句，可以代替某些地方暂时没有写语句，而以后可能要写，先用null替换

异常处理：

异常类型：

预定义的：

违反oracle规则将隐式引发它

用户定义的：

可以由raise语句显式引发

编码错误，被称为语法错误，编译器会停止编译让我们来修改

在程序运行期出现的条件不满足，条件超出了预显，出现的错误，我们称为异常

异常处理涉及到的步骤：

声明异常：

引发异常：

处理异常：

异常类型：

预定义：

由oracle为常见错误预定义，在DBMS_STANDARD程序报中提供了这些定义

非预定义：

oracle没有定义的，指定

用户定义：

声明异常：

declare

Dup_ValueException;--声明异常Dup_Value

icountint:

=0;

begin

selectcount（*）intoicountfromplacewhereplaceid=1;

ificount>0then

raiseDup_Value;--抛出异常或者说引发异常

endif;

Exception

whenDup_Valuethen--处理用户自定义异常

DBMS_Output.putline（'记录已经存在'）;--字符串必须用单引号'

others关键字：

其他所有异常whenothersthen放在最后

预条件编译：

关键指令pragmaException_init（自定义异常，-1）;其中-1为编号--编译指令

什么叫预条件编译当出现系统预定义异常时，错误信息不好理解，可以让出现此类异常时用自己

定义的异常来完成，如：

当违反系统主键唯一性这种异常

如：

declare

Dup_ValueException;--声明异常Dup_Value

pragmaException_init（Dup_Value,-1）;--覆盖系统指令

begin

insertintotable1values（12,'sfsdf'）;--不用用raise手工抛出异常或者说引发异常

Exception

whenDup_Valuethen--处理用户自定义异常

DBMS_Output.putline（'fsdfs'）;--字符串必须用单引号'

一般实例：

Exception

whenNO_Data_Foundthen－－没有记录

语句；

whenToo_Many_rowsthen--不止一行

语句；

whenDup_Val_On_Indexthen--唯一约束被破坏

语句；

whenothersthen

语句；

自定义异常中Raise_Application_Error方法的用法：

可以显示给客户端，其它用户都可以看到,如果上例的用法则只能在pl/sql块中看到（以交互方式传达预定义的异常）

既可以在执行块使用，也可以在异常块使用

错误编号必须在-20000到-20999之间

错误消息的长度可长达2048个字节

如上例中whenDup_Valuethen

Raise_Application_Error（-20001,'表中已经有这条记录了'）;

7游标cursor:

指向上下文区域的句柄或指针,他是在服务器上存储的,我们用游标实际上是使用服务器的资源（所以要考虑性能）

上下文区域-用于sql处理的内存区

上下文区域的内容

语句处理的行数

指向语句的语法分析表示的指针

使用游标的好处:

1减少了网络的数据流量

2可以实现遍历（如对一个结果集的逐行检索）

3使用游标可以直接更新表中的行（可以直接引用行）

游标类型:

静态游标的名字不能当作参数传递，引用游标则可以

静态游标:

由用户定义,经过定义,打开,关闭就结术了

特点:

对应的结果集是固定的

隐式游标：

服务器自动管理，（自动开启，sql语句执行后就自动关闭）如执行，插，删，改

用于处理：

DML语句，

返回单行查询

显式游标：

由用户显示声明

游标将指向活动集中的当前行

控制显式游标

Open:

不打开则无法读取

Fetch

Close：

不关闭，则内存中将始终保留

声明：

declarecursorcursor_nameisselect*fromemp

游标只是个标示，不能给其赋值，提取的结果集应该在游标区域内

举例：

declare

--在opencursor之前，只是分配了一个内存区域来存储游标内容，而没有结果集

cursorcursor_nameisselect*fromemp;--游标不是数据类型所以不用后置修饰

emprowemp%rowtype;

begin

--激活后会分配一个游标区域，并执行查询，返回结果集，然后并有一个指针指向第一条记录

opencursor_name;--打开游标

loop--循环

fetchcursor_nameintoemprow;--提取游标指示的数据

DBMS_Output.put_line（'employeeno:

'||emprow.empno）;

--%rowcount表示提出了多少行，从一开始

DBMS_Output.put_line（'提取了'||cursor_name%rowcount||'行'）;

exitwhencursor_name%notfound;

endloop

closecursor_name;--关闭游标

end

改为while循环，必须先提取一次

declare

cursorcursor_naemisselect*fromemp;

emprowemp%rowtype;

opencursor_name;--打开游标

fetchcursor_nameintoemprow;--提取游标指示的数据

whilecursor_name%foundloop

DBMS_Output.put_line（'employeeno:

'||emprow.empno）;

--%rowcount表示提出了多少行，从一开始

DBMS_Output.put_line（'提取了'||cursor_name%rowcount||'行'）;

fetchcursor_nameintoemprow;

endloop

closecursor_name;--关闭游标

end

改为for循环，比较经典,自动打开游标，自动提取指示数据，自动关闭游标

declare

cursorcursor_nameisselect*fromemp;

begin

foremprow（隐式声明）incursor_nameloop--被自动打开，并自动的提取数据给行级变量

end

for循环的缺点，就是必须把数据全部提出，不像其它，可以由条件限制

REF游标（引用游标,也可以叫做动态游标）

在运行时使不同的语句与之关联

ref游标使用游标变量

游标变量：

一种引用类型

可以在运行时指向不同的存储位置

close语句关闭游标并释放用于查询的资源

游标变量的类型：

具有约束的游标变量

具有返回类型的游标变量

也成为‘强游标’

无约束的游标变量

没有返回类型

也成为‘弱游标’

声明：

用type来生成引用游标

declare

--就像是一种类型，不过有效区域就在pl/sql块中

typerefcursor_nameisrefcursor;--弱游标

--typerefcursor_nameisrefcursorreturnemp%rowtype;--强游标

empcurrefcursor;--定义游标变量

openempcurforselect*fromemp;－－重点记忆

/*因为for循环会自动打开游标，所以在引用游标中不能使用*/

游标的属性：

%NOTFOUND--返回一个boolean型

%FOUND--返回一个boolean型

%ROWCOUNT

%ISOPEN--返回一个boolean型：

隐式游标永远为false

举例：

begin

insertintoplacevalues（2,'beijing'）;

DBMS_Output.putline（'游标影响的行数'||SQL%rowcount）;--所有游标的统称为SQL（游标的名字）

end

注释：

命令savefdfd.txt可以将sqlplus中的记录到txt文件

使用绑定变量来实现交互性：

（仅限于sqlplus中使用）

用法：

&变量名--整形

&'变量名'--字符等

如在declare中使用，当然也可以在执行块中使用（赋值符号后用）

declarecursorempcurissele