Oracle数据库学习总结.docx
《Oracle数据库学习总结.docx》由会员分享，可在线阅读，更多相关《Oracle数据库学习总结.docx（29页珍藏版）》请在冰点文库上搜索。
Oracle数据库学习总结
Oracle数据库学习总结
时间过的还真快，不知不觉中就在这里呆了半个月了。
这段时间里都在学习oracle数据库的编程，毕竟这是家软件外包公司。
像我们这样的新员工也就只能接触到些CURD的操作。
废话不多说，赶紧来梳理下这半月来学习的知识点.
在来公司之前一直都是使用SqlServer数据库，用SqlServer也开发了3个小型项目。
所以对Sql语句以及在数据库中扮演重要作用的存储过程，触发器，视图，主键/外键约束都很熟。
但Oracle是一个全新的环境，记得刚装上Oracle的时候，我都不知道在哪查看自己已经建立好的表格。
还好有师傅的帮忙，要不然我还真没这么快就能入门Oracle。
学习东西就要学习些能改变自己思维的东西，只有这样才能让自己的眼光比别人更独到，思维比别人更深邃，Oracle就是这样的东西。
当然做这样的事是要很大的驱动力的呀，如果公司不是都采用Oracle来写程序的话，我估计也就懒得学啦。
对于一位程序员来说并不需要完全掌握Oracle的所有知识，毕竟自己不是DBA。
在日常开发中也用不到那些命令和工具，但是有些知识点我们还是必须得熟练的掌握它们。
比如:
一些基本的DDL和DML语句，存储过程，函数，视图，触发器，序列，游标，自定义类型和包。
下面我就把这段时间里学习Oracle获得的知识点罗列出来，一是为了方便以后查阅，二是为了和搭档交流学习经验。
要适应的一些细节
从SqlServer转到Oracle进行数据库编程，第一道门槛就是语法问题。
很多很多的问题都是因为语法而产生的，现将它们统统集合起来并将它们一网打尽之。
参数赋值
SqlServer
Set@parameter=5--加不加分号无所谓，这里是用‘=‘号进行赋值的
Oracle
Parameter：
=5；--必须加分号，并使用加‘：
‘的’=‘号进行赋值的
PL结构。
在SqlServer中，采用的是批处理执行任务的方式，所以可以将多条sql语句选中批量执行，而不用顾忌要在专门的地方声明变量，在专门的地方进行逻辑编码。
在Oracle中采用的是PL编程方式，必须在专门的地方声明变量，在专门的地方进行流程编码，经典的PL如下：
Declare
--这里是专门用来定义变量的
Begin
--这里是专门用来进行程序编码的
End;--这里必须加分号
If语句
If***条件then
Elseif***条件then
Else
Endif；--这里要加分号
Case语句
Case
When***条件then
When***条件then
When***条件then
Else
EndCase；--这里要加分号
还可以写成：
Case常量--一个字符变量
When‘A‘then
When‘B‘then
When‘C‘then
Else
EndCase；--这里要加分号
循环结构，要达到循环在Oracle中有3种方式，各有各的好处，你懂的。
它们分别如下：
第一种
Loop
****
Exitwhen退出条件;
Endloop;--要加分号
第二种
While条件loop
****
Endloop；--要加分号
第三种
ForIin1..100loop
***
Endloop;--要加分号
PL结构中的错误处理
就像C#中的Try{}Catch{}语句块能捕获错误。
写几个例子:
HelloWorld级别的错误抛出例子
declare
stu_infostudent%rowtype;
cursorstu_cursorisselect*fromstudent;
begin
select*intostu_infofromstudent;
exception
whenTOO_MANY_ROWSthen
dbms_output.put_line（'行太多'）;
whenothersthen
dbms_output.put_line（'未知错误错误号:
'||sqlcode||'错误信息'||sqlerrm）;
end;
手动抛出错误，类似于c#中的throw
declare
stu_infostudent%rowtype;
cursorstu_cursorisselect*fromstudent;
begin
Raise_Application_Error（-20001,'打酱油的错误'）;--显示抛出错误
exception
whenTOO_MANY_ROWSthen
dbms_output.put_line（'行太多'）;
whenothersthen
dbms_output.put_line（'未知错误错误号:
'||sqlcode||'错误信息'||sqlerrm）;
end;
自定义一个错误，并把它手动抛出
declare
my_errorException;
pragmaException_Init（my_error,-29999）;--这里很重要哦
begin
raisemy_error;--抛出错误
exception
whenothersthen
dbms_output.put_line（'未知错误错误号:
'||sqlcode||'错误信息'||sqlerrm）;
end;
Record类型
Oracle中的Record类型类似于c语言中的结构体，主要用来接收Select语句或游标中返回的数据，下面写个例子：
declare
typestudent_record_typeisrecord（
stu_namestudent.name%type,
stu_agestudent.age%type
）;
student_recordstudent_record_type;--这里很重要，不能直接在类型上操作
begin
selectname,ageintostudent_recordfromstudentwhereid=&id;
dbms_output.put_line（student_record.stu_name||''||student_record.stu_age）;
end;
DDL语句
这里的DDL语言主要是指能完成如下工作的DDL语言：
创建表，创建表的主/外键及级联效果，
建表：
CreateTablestudent（
StuIdnumber（5）,
StuNamevarchar2（20）,
StuAgenumber
（2）
）
CreateTableclass（
StudentIdnumber（5）,
TeacherIdnumber（5）,
ClassNamevarchar2（30）
）
CreateTableteacher
（
tIdnumber（5）,
tNamevarchar2（30）,
tSalarynumber（5,2）
）
AlterTableclass
AddConstraintp_kPrimaryKey（StudentId,TeacherId）
Altertablestudent
AddConstraintp_kPrimaryKey（StuId）
AlterTableclass
AddConstraintf_k_1ForeignKey（StudentId）
referencesstudent（id）ondeletecascade
AlterTableclass
AddConstraintf_k_2ForeignKey（TeacherId）
referencesstudent（tId）ondeletecascade
DML语句
Select语句。
Oracle中的Select语句的使用方法与SqlServer差不多，但还是有些不同之处。
赋值方式不同：
SqlServer：
Select@peopleNumber=count（*）frompeople
Oracle：
Selectcount（*）intopeopleNumberfrompeople
内连接
SqlServer
Selects.id,s.namefromstudentsinnerjoin
classcons.id=c.studentidwherec.classname=’***’
Oracle:
Selects.id,s.namefromstudentsinnerjoin
classcons.id=c.studentidwherec.classname=’***’
左连接
SqlServer
Selects.id,s.namefromstudentsleftjoin
classcons.id=c.studentidwherec.classname=’***’
Oracle:
Selects.id,s.namefromstudentsleftouterjoin
classcons.id=c.studentidwherec.classname=’***’
右连接
SqlServer
Selects.id,s.namefromstudentsrightjoin
classcons.id=c.studentidwherec.classname=’***’
Oracle:
Selects.id,s.namefromstudentsrightouterjoin
classcons.id=c.studentidwherec.classname=’***’
全连接
SqlServer
Selects.id,s.namefromstudentsfulljoin
classcons.id=c.studentidwherec.classname=’***’
Oracle:
Selects.id,s.namefromstudentsfullouterjoin
classcons.id=c.studentidwherec.classname=’***’
Insert语句。
Oracle中的Insert语句比SqlServer中的强大很多，废话不多说，看例子：
单条数据的插入
Insertintostudent（id,name,age）values（1,’张三’,22）;
插入的数据源来自select语句
Insertintostudentfromselectid,name,agefromtmp_student;
根据不同的条件，将数据插入到不同的表中
Insertall
whenidbetween1and3thenintox_testtable
whenidbetween4and6thenintox_testtable2
selectid,namefromstudentDelete语句
Update语句。
Updatestudentsetname=’new’||namewhereid=1;
Delete语句。
和标准的sql标准一致，没多大变化。
Deletefromstudentwhereid=1
视图。
视图有虚拟视图和物理视图两种，这里不说后者。
创建视图的语法如下：
简单的视图：
CreateViewView_Studentas
Select*fromStudent
复杂的视图:
CreateViewTeacher_Studentas
Selectt.name,count（s.id）学生数fromstudentsinnerjoinclasscons.id=c.idinnerjointeachertonc.teacherid=t.id
Groupbyt.name
简单视图与复杂视图的区别在于：
简单的视图能在视图上对实际存储的数据进行增/删/改操作而复杂的视图却不能，但如果你实在是要对复杂的视图进行增/删/改操作，你可以使用Insteadof类型的Trigger来做。
存储过程
废话不多说，看代码：
HelloWorld级别的存储过程
createorreplaceprocedurex_print_helloworld
as
begin
dbms_output.put_line（'HelloWorld'）;
end;--分号是重要滴
带输入参数的存储过程，而且还支持默认值
createorreplaceprocedure
x_print_something（msgvarchar2default'helloworld'）
as
begin
dbms_output.put_line（msg）;
end;
带输出参数的存储过程
createorreplaceprocedurex_getSum（noutnumber）
as
begin
foriin1..nloop
n:
=n+i;
endloop;
end;
定义了存储过程你得调用呀，看代码：
declare
begin
x_print_helloworld;
x_print_something;
x_print_something（'abc'）;
x_jc（10）;
end;
函数,和存储过程查不多，唯一的区别就是有返回值，而且还能嵌套在DML语句中使用。
下面写几个简单的函数：
HelloWord级别的函数
createorreplacefunctionx_get_helloworldreturnvarchar2
as
begin
return'HelloWorld';
end;
统计某些数据的函数
createorreplacefunctionx_get_studentinfo_countreturnnumber
as
tmpnumber（5）:
=0;
begin
selectcount（*）intotmpfromstudent;
returntmp;
end;
调用方法：
declare
begin
dbms_output.put_line（x_get_helloworld）;
dbms_output.put_line（x_get_studentinfo_count）;
end;
游标
在Sql中使用的比较少，一直觉得它挺神秘的。
最近学习了下，但对它有啥好处还是相知甚少。
游标分为以下几类：
显示游标，隐式游标。
显示游标里面又有匿名游标和非匿名游标，隐式游标是Oracle中提供的某些API接口，通过调用它们能获取某些重要的系统信息，在SqlServer中也有类似的功能如‘@@error‘。
Oracle中的隐式游标：
%notfound游标专用的隐式游标呀，判断游标中是否还有可返回的数据
%isopen判断游标是否打开
%rowtype定义行类型的mycontentstudent%rowtype表示开辟一个能包含student表中一条元组的变量空间，并将该地址赋予变量mycontent.
%type定义列类型的mycolumnsstudent.name%type概念同上，开辟一个列。
%rowcount当前返回的数据行数
普通游标的定义及使用
declare
cursorstu_infoisselect*fromstudentforupdate;
stu_recordstudent%rowtype;
begin
openstu_info;
loop
fetchstu_infointostu_record;
exitwhenstu_info%notfound;
ifstu_record.name='bank'then
updatestudentsetname='new_bank'wherecurrentofstu_info;
endif;
ifstu_record.id=10then
deletefromclasswherestudentid=stu_record.id;
deletefromstudentwherecurrentofstu_info;
endif;
endloop;
closestu_info;
end;
带参数的游标的定义及使用
declare
cursorclassInfo（idnumber）isselect*fromclasswhereteacherid=id;
class_recordclass%rowtype;
begin
openclassInfo
（1）;
loop
fetchclassInfointoclass_record;
exitwhenclassInfo%notfound;
dbms_output.put_line（'studentid:
'||class_record.studentid||'classname:
'||class_record.classname）;
endloop;
closeclassInfo;
end;
简写的游标定义及使用
declare
cursorinfoisselectnamefromstudent;
begin
forstuNameininfoloop
dbms_output.put_line（'第'||info%rowcount||'条记录'||stuName.name）;
endloop;
end;
匿名游标的定义及使用
declare
begin
forstuNamein（select*fromstudent）loop
dbms_output.put_line（stuName.name）;
endloop;
end;
游标变量
游标变量与C语言中的指针函数类似。
游标变量又分为指明返回类型的游标变量和不指明返回类型的游标变量。
不声明返回类型的游标变量
declare
typeinfoisrefcursor;
stu_infoinfo;
stu_recordstudent%rowtype;
begin
openstu_infoforselect*fromstudent;
loop
fetchstu_infointostu_record;
exitwhenstu_info%notfound;
dbms_output.put_line（stu_record.name）;
endloop;
closestu_info;
end;
声明返回类型的游标变量
用这种方式声明的游标不支持%rowtype类型的变量声明返回类型
declare
typeclass_record_typeisrecord（classnameclass.classname%type）;
typeclass_cursor_typeisrefcursorreturnclass_record_type;
class_recordclass_record_type;
class_cursorclass_cursor_type;
begin
openclass_cursorforselectclassnamefromclass;
loop
fetchclass_cursorintoclass_record;
exitwhenclass_cursor%notfound;
dbms_output.put_line（class_record.classname）;
endloop;
closeclass_cursor;
end;
还有几种比较高级的游标写法，不知道工作用会不会用到。
它们分别是：
嵌套游标：
declare
cursorinfo（nnumber）isselectc.classname,cursor（selects.namefromstudentswheres.id=c.studentid）
fromclasscwherec.studentid=&n;
typecursor_typeisrefcursor;
class_cursorcursor_type;
classnameclass.classname%type;
tmpvarchar2（100）;
begin
openinfo
（1）;
loop
fetchinfointoclassname,class_cursor;
exitwheninfo%notfound;
dbms_output.put_line（classname||':
'）;
loop
fetchclass_cursorintotmp;
exitwhenclass_cursor%notfound;
dbms_output.put_line（tmp）;
endloop;
endloop;
closeinfo;
end;
批量返回数据的游标：
declare
cursorstudent_cursorisselectnamefromstudent;
typename_table_typeistableofvarchar（20）;
name_tablename_table_type;
begin
openstudent_cursor;
fetchstudent_cursorbulkcollectintoname_table;
foriin1..name_table.countloop
dbms_output.put_line（name_table（i））;
endloop;
closestudent_cursor;
end;
批量返回数据的游标但可以限制每次返回数据的行数的游标
declare
cursorstudent_cursorisselectnamefromstudent;
typename_table_typeistableofvarchar（20）;
name_tablename
升级会员 