Oracle基本操作总结.docx
《Oracle基本操作总结.docx》由会员分享，可在线阅读，更多相关《Oracle基本操作总结.docx（26页珍藏版）》请在冰点文库上搜索。
Oracle基本操作总结
第一讲：
1、使用sqlplus查看系统日志文件v$logfile，对控制文件select*fromv$controlfile，数据文件v$datafile还有参数文件。
通过参数文件寻找控制文件。
2、Oracle的内存结构SGA，由数据库高速缓冲区--频繁访问的数据、大的数据共享区、共享池、Redo日志缓冲池、固定的SGA组成。
3、Oracle的逻辑结构，
第二讲：
1、sqlplus数据库实例的启动和关闭，sqlplus"sys/test1234assysdba"；startup启动_启动实例，打开控制文件，打开数据文件，archiveloglist查看是否归档方式。
startupmount不打开数据文件startupnomount连控制文件也不打开。
2、shutdown关闭，很少用。
shutdownimmediate，使每个用户执行完当前sql语句以后关闭。
shutdownabort强制关闭。
3、支持空各行，setsqlbalanklineson；
4、用变量查询，wheredeptno=&tt，tt为变量，每次只能执行一个计划
5、list查看曾经的sql语句。
list简写L。
改写错的字母，c/n/m把n改成m。
删除del行号。
在缓冲区命令后面连接afromdept。
保存到文件中save路径。
@路径表示执行。
读取文件内容get路径。
6、对列操作，col。
如重命名，coldeptnoheaiding“编号”，格式化format999，999，999的形式输出。
7、Ttitle标题和Btitle尾页。
8、很重要的报表制作工具：
break，comp；重复的就显示一条，breakonpub，在pub中的重复的就显示一条。
统计命令compcountlable“报表”of字段名
9、保存查询出来的结果集：
spool语句。
spool文件路径，然后执行查询，然后spooloff。
第三讲：
1、sql语言的分类：
数据定义DDL有creaetalterdrop数据控制DCL有grantrevoke数据操纵DML有selectinsertdeleteupdate；
2、create讲解：
3、授权：
grant，grantselectondepttott，
4、收回权限：
revokeselectonfromtt。
5、数据操纵语言：
updateabcsetb=“ttt”
6、Oracle中的系统函数的应用，length查询字符长度，lengthB查询字节长度。
Ltrim去掉左边的空格。
Rtrim右边。
trim去掉两边的。
7、可变长度和定长度的区别，定长的要用空格来补充。
8、取子串，substr（字符串，从哪个位置，取几个位）；Oracle没有提供左取串，右取串。
9、对日期的操作：
selectnext_day（sysdate,"星期三"）fromdual。
显示下一个星期三是几号。
10、字符转换：
selectto_char（sysdate,"yyyy-mm-ddhh:
mi:
ss"）;
11、字符转换：
selectto_char（sysdate,"yyyy-mm-ddhh24:
mi:
ss"）;24小时制
12、字符转换：
selectto_date（'',"yyyy-mm-ddhh:
mi:
ss"）;
13、字符转换：
selectto_number（'2222'）;
14、聚集函数：
sum、avg、max、min、count；聚集函数不能和where结合使用。
15、user，decode，nvl，selectuserfromdual。
selectsum（decode（sex,'男',1,0））男人数,sum（decode（sex,'女',1,0））女人数frome;Boolean型的判断函数decode，是男就加一，是女人就加一。
单独统计男人数和女人数。
16、对空值进行处理nvl，selecta1，nvl（a2,"未输入"）frome，对a2值为空的就显示未输入。
17、空值的判断未a1isnull或者a1isnotnull。
18、在单独显示一行的数据的时候，里面有重复的数据的话就用distinct，那么重复的就显示一条。
第四讲：
1、分组语句，groupby。
selectidnamefromempgroupbyid，name。
select后面的字段必须在groupby后面出现，groupby后面的字段可以不在select里面显示。
selectnamefromempgroupbyid，name。
2、聚合语句不能作为where条件判断。
wheresum（price）>30是错的。
这个时候要用到having语句了，而且having语句必须在groupby的前面。
3、模糊查询like，select*fromaawherea1like'a_';显示以a为开头的两个位数的字段。
4、模糊查询like，select*fromaawherea1like'a%';显示以a为开头的任意位数的字段。
5、模糊查询like，select*fromaawherea1like'_a';显示以a为结束的两个位数的字段。
6、模糊查询like，select*fromaawherea1like'%a';显示以a为结束的任意位数的字段。
7、模糊查询like，select*fromaawherea1like'%a%';显示包含a的任意位数的字段。
8、表的连接：
有内连接和外连接。
内连接表示将两个表中的匹配的数据都显示出来，其他多出来的没有对应值的将都不显示。
标准写法是froma表joinb表on判断条件是和（froma,bwherea.id=b.id）是一样的效果。
9、外连接：
有左外连接和右外连接。
左外连接无条件的将左边表中的数据全部显示出来，一般在条件右面加（+）。
右外连接无条件的将右边表中的数据全部显示出来，一般在条件左面加（+）。
10、子查询：
无关子查询，相关子查询；无关子查询表示子查询于外部的查询没有必然的联系，select*fromempwhereidIN（selectidfromdept）;相关子查询表示子查询于外部的查询有必然的联系，select*fromempwhereidIN或（notin）（selectidfromdeptwhereid=emp.idandid="3"）;在子查询中不能出现select*;
11、子查询中是否存在select*fromewhereexist（selectidfromd）;是否存在，不存在的话一个都不显示。
select*fromewhereexist或NOTexist（selectidfromdwhereid=e.id），在这里，子查询中可以使用select*.
12、表连接的查询操作比子查询的查询操作效率高。
13、把两个表的行相结合的显示，selecteid,enamefromeunionselectid,deptfromd;合并行数据。
14、运算符Intersect显示两个sql语句中都出现的行。
是Oracle独具的一个运算符其他数据库不见得会兼容。
就是显示两个select中都存在的。
相当于内连接查询。
15、批量的插入信息，如insertintoe（eid,ename）selectid,namefromd;类型要兼容，Union是表面的，但这个是物理的。
16、依据已经存在的表格创建一个新的表，createtabletttas（select*fromd）;创建一个ttt并且把数据拷贝过去。
也是Oracle独具的功能。
第五讲：
1、PL/sql的讲解，就可以和前端和后台相结合调用的功能。
结构化查询设计语言。
Oracle的块结构有如下：
PL/sql块结构，
1声明declare...变量有标准的变量和复合变量又称记录。
必须以字符开头，长度一般不超过30个字符，不区分大小写。
不可以是保留字。
2开始begin...
3异常exception...
4结束end...
2、第一个PL/sql块
DECLARE
xvarchar2（10）;定义变量xvarchar2（10）:
='ABDCS';定义变量并赋值
BEGIN
x:
='thisis..'给变量赋值
DBMS_OUTPUT.PUT_LINE（'X的值为：
'||x）;打印变量的值，拼接字符串要用||操作符。
Oracle提供了很多和外界交互的包，DBMS_OUTPUT就是其中的一个。
end;结束
/执行
setserveroutputonsize10000，打开显示的开关。
DBMS_OUTPUT.PUT_LINE（'X的值为：
'||x）;
行注释--
块注释/**/
3、分支语句if和case（9i之后开始支持的）
if...
then...
endif;
if实例：
declare
anumber;
bvarchar2（10）;
begin
a:
=2;
ifa=1then
b:
='A';
elsifa=2then
b:
='B';
else
b:
='C';
endif;
DBMS_OUTPUT.PUT_LINE（'B的值是：
'||b）;
end
/
Case实例：
declare
anumber;
bvarchar2（10）;
begin
a:
=2;
case
whena=1thenb:
='A';
whena=2thenb:
='B';
whena=3thenb:
='C';
else
b:
='others'
endcase;
DBMS_OUTPUT.PUT_LINE（'B的值是：
'||b）;
end
/
第六讲：
1、循环基本循环Loopwhile循环、for循环。
Loop注意计数器不可以出现死循环。
declare
anumber;
begin
a:
=0;
loop
a:
=a+1;
ifa>=3then
exit;
endif;
DBMS_OUTPUT.PUT_LINE（'循环内：
a='||a）;
endloop;
DBMS_OUTPUT.PUT_LINE（'循环外：
a='||a）;
end
/
Loop注意计数器
declare
anumber;
begin
a:
=0;
loop
a:
=a+1;
exitwhena>=3;跟if判断是一样的
DBMS_OUTPUT.PUT_LINE（'循环内：
a='||a）;
endloop;
DBMS_OUTPUT.PUT_LINE（'循环外：
a='||a）;
end
/
while注意计数器
declare
anumber;
begin
a:
=0;
whilea<=3loop
a:
=a+1
DBMS_OUTPUT.PUT_LINE（'循环内：
a='||a）;
endloop;
DBMS_OUTPUT.PUT_LINE（'循环外：
a='||a）;
end
/
for主要应用在知道具体循环次数的情况。
注意控制
begin
forain1..5loop从小到大
DBMS_OUTPUT.PUT_LINE（'循环内：
a='||a）;
endloop
DBMS_OUTPUT.PUT_LINE（'循环外：
a='||a）;
end
/
for
begin
forainreverse1..5loop从大到小
DBMS_OUTPUT.PUT_LINE（'循环内：
a='||a）;
endloop
DBMS_OUTPUT.PUT_LINE（'循环外：
a='||a）;
end
/
Oracle当中for循环中每次递增量为1，不可以改。
2、定义标记，使用goto跳转。
goto特殊的循环。
要比其他的数据库灵活的多。
declare
anumber;
begin
a:
=0;
<>
a:
=a+1;
DBMS_OUTPUT.PUT_LINE（'循环内：
a='||a）;
ifx<3then
gotorepeat_loop;
endif;
end
/
3、异常处理编译时刻的错误，和运行时刻的错误。
可以使用系统预定义的，也可以自定义。
EXCEPTION
WHENTHEN...
DECLARE
testvarchar2（10）;
begin
selectnameintotestfromdeptwhereid='tt';--这个id是不存在的。
DBMS_OUTPUT.OUT_LINE（'test='||test）；
end
/
注：
因为dept表中没有id为tt的字段，所以会报错。
nodatafound
怎么去捕获异常呢？
DECLARE
testvarchar2（10）;
begin
selectnameintotestfromdeptwhereid='tt';
DBMS_OUTPUT.OUT_LINE（'test='||test）；
exception
whenno_data_foundthen
DBMS_OUTPUT.OUT_LINE（'没有找到数据'）；
end
/
4、自定义异常
DECLARE
tnamevarchar2（10）;
eexception;声明一个异常
begin
selectnameintotnamefromdeptwhereid='01';
iftname<>'B部门'then--当不是B部门的时候就抛出异常e
raisee;--抛出异常。
不可以缺少
endif;
DBMS_OUTPUT.PUT_LINE（TNAME）;
EXCEPTION--捕获异常
WHENETHEN
DBMS_OUTPUT.PUT_LINE（'错误，不是需要的B部门'）;
end
/
5、复合变量，又叫记录。
记录是由几个相关值构成的复合的变量，常用于支持select语句的返回值。
使用记录可以将一行数据看成一个单元进行处理，而不必将每一列单独处理。
declare
typemyrecordisrecord
（
idvarchar2（10）,
namevarchar2（10）
）;
aamyrecord;--声明一个myrecord类型的变量
begin
selectemp_id,emp_nameintoaafromewhereemp_id=’001‘
DBMS_OUTPUT.put_LINE（AA.ID||AA.NAME）;
END
/
6、怎么去保证记录里面的变量的类型和表中的数据类型完全相同呢？
emp.eid%TYPE
declare
typemyrecordisrecord
（
idemp.eid%TYPE,单独一个字段需要一致的时候可以用这种形式。
namevarchar2（10）
）;
aamyrecord;
begin
selectemp_id,emp_nameintoaafromewhereemp_id=’001‘
DBMS_OUTPUT.OUT_LINE（AA.ID||AA.NAME）;
END
/
7、我要想根据一个表去定义一个记录的话，就是希望跟表字段完全一致时
declare
myrecordemp%ROWTYPE;--定义跟emp完全一致的记录。
begin
select*intomyrecordfromewhereemp_id=’001‘
DBMS_OUTPUT.put_LINE（myrecord.eID||myrecord.eNAME）;
END
/
第七讲：
1、游标，是一种PL/SQL控制结构，可以对SQL语句的处理进行显示控制，便于对表中的行数据逐条进行处理。
分为显式和隐式。
游标的属性：
%FOUND
%ISOPEN
%NOTFOUND
%ROWCOUNT
显式游标的方式。
DECLARE
CURSORmycorsorIS内存中创建一个空间，放置查询出来的结果。
select*frombooks;
myrecordbooks%ROWTYPE;创建一个记录。
BEGIN
OPENmycorsor;--打开游标，
FETCHmycorsorINTOmyrecord;--放置数据。
有了这一步才能开始循环。
whilemycorsor%FOUNDLOOP--如果有数据，就继续。
DBMS_OUTPUT.put_LINE（myrecord.id||myrecord.name）;
FETCHmycorsorINTOmyrecord;--放置下一条数据。
ENDLOOP--循环完了。
CLOSEmycorsor;--关闭游标。
end;
/
显式游标的方式。
用参数进行处理。
DECLARE
CURSORcur_para（idvarchar2//不需要指定长度）IS--内存中创建一个空间，放置查询出来的结果。
selectbook_namefrombookswherebooks_id=id;
t_namebooks.book_name%TYPE;根据books表中的book_name字段声明一个变量。
BEGIN
OPENcur_para（'001'）;--打开游标并传递参数，
Loop
FETCHcur_paraINTOt_name--放置数据。
有了这一步才能开始循环。
EXITWHENcur_para%NOTFOUND;
DBMS_OUTPUT.OUT_LINE（t_name）;
ENDLOOP循环完了。
CLOSEcur_para;关闭游标。
end;
/
显式游标，带参数的，利用for循环来处理。
注意不需要打开和关闭游标。
DECLARE
CURSORcur_para（idvarchar2//不需要指定长度）IS内存中创建一个空间，放置查询出来的结果。
selectbook_namefrombookswherebooks_id=id;
BEGIN
DBMS_OUTPUT.OUT_LINE（'********结果集为：
********'）;
FORcurINcur_para（'001'）Loop--会自动在结果集合中循环输出
DBMS_OUTPUT.OUT_LINE（cur.book_name）;
ENDLOOP--循环完了。
end;
/
2、利用属性%ISOPEN判断游标有没有打开；
DECLARE
t_namebooks.book_name%TYPE;
CURSORcur_para（idvarchar2//不需要指定长度）IS内存中创建一个空间，放置查询出来的结果。
selectbook_namefrombookswherebooks_id=id;
BEGIN
IFcur_para%ISOPENTHEN
DBMS_OUTPUT.OUT_LINE（'游标已经被打开！
'）;
ELSE
OPENcur_para（'001'）;
ENDIF;
FETCHcur_paraINTOt_name;
CLOSEcur_para;
DBMS_OUTPUT.OUT_LINE（t_name）;
END
/
注：
select中查询的字段数跟定义的变量数一定要一致。
3、%ROWCOUNT的使用方法显示记录的条数。
每查出一个数据，计数器自动加一。
DECLARE
t_namevarchar2（10）;
CURSORmycurIS
selectnamefromdept;
begin
OPENmycur;
LooP
FETCHmycurINTOt_name;//不加这个的话就没有数据。
EXITWHENmycur%NOTFOUNDORmycur%NOTFOUNDISNULL;
DBMS_OUTPUT.OUT_LINE（'记录数为：
'||mycur%ROWCOUNT）;
ENDLOOP;
CLOSEmycur;
END;
/
4、利用游标修改数据：
DECLARE
t_namevarchar2（10）;
CURSORmycurIS
selectnamefromdeptFORUPDATE;
begin
OPENmycur;
FETCHmycurINTOt_name;//不加这个的话就没有数据。
WHILEmycur%FOUNDLOOP
UPDATEdeptSETname=name||'_t'where（必须加）CURRENTOFmycur;
FETCHmycurINTOt_name;
ENDLOOP;
CLOSEmycur;
END;
/
5、隐式游标就是不需要DECLARE,OPEN,CLOSE等操作。
BEGIN
FORcurIN（SELECTNAMEFROMdept）LOOP
DBMS_OUTPUT.OUT_LINE（cur。
name）;
ENDLOOP;
END;
/
数据量大的时候不提倡使用游标，消耗资源，但是灵活。
第八讲：
PL/SQL匿名块每次运行都需要编译，而且不能存储到数据库中。
别的PL/SQL块无法调用。
Oracle也提供了带名块，如过程，包，函数等。
。
。
1、过程，为了执行一定任务而组合在一起的sql语句和pl/sql语句的一个几何体。
分为声明部分（可选项），执行部分（必须的），异常处理部分。
2、基本写法
CREATEORREPLACEPROCEDU
升级会员 