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