Oracle数据库学习总结

Oracle数据库学习总结

时间过的还真快，不知不觉中就在这里呆了半个月了。这段时间里都在学习oracle数据库的编程，毕竟这是家软件外包公司。像我们这样的新员工也就只能接触到些CURD的操作。废话不多说，赶紧来梳理下这半月来学习的知识点.

在来公司之前一直都是使用Sql Server数据库，用Sql Server也开发了3个小型项目。所以对Sql语句以及在数据库中扮演重要作用的存储过程，触发器，视图，主键/外键约束都很熟。但Oracle是一个全新的环境，记得刚装上Oracle的时候，我都不知道在哪查看自己已经建立好的表格。还好有师傅的帮忙，要不然我还真没这么快就能入门Oracle。

学习东西就要学习些能改变自己思维的东西，只有这样才能让自己的眼光比别人更独到，思维比别人更深邃，Oracle就是这样的东西。当然做这样的事是要很大的驱动力的呀，如果公司不是都采用Oracle来写程序的话，我估计也就懒得学啦。

对于一位程序员来说并不需要完全掌握Oracle的所有知识，毕竟自己不是DBA。在日常开发中也用不到那些命令和工具，但是有些知识点我们还是必须得熟练的掌握它们。比如:一些基本的DDL和DML语句，存储过程，函数，视图，触发器，序列，游标，自定义类型和包。

下面我就把这段时间里学习Oracle获得的知识点罗列出来，一是为了方便以后查阅，二是为了和搭档交流学习经验。

要适应的一些细节

从Sql Server转到Oracle进行数据库编程，第一道门槛就是语法问题。很多很多的问题都是因为语法而产生的，现将它们统统集合起来并将它们一网打尽之。

参数赋值

Sql Server

Set @parameter=5 --加不加分号无所谓，这里是用‘=‘号进行赋值的

Oracle

Parameter：=5；--必须加分号，并使用加‘：‘的’=‘号进行赋值的

PL结构。在Sql Server中，采用的是批处理执行任务的方式，所以可以将多条sql语句选中批量执行，而不用顾忌要在专门的地方声明变量，在专门的地方进行逻辑编码。在Oracle中采用的是PL编程方式，必须在专门的地方声明变量，在专门的地方进行流程编码，经典的PL如下：

Declare

--这里是专门用来定义变量的

Begin

--这里是专门用来进行程序编码的

End;--这里必须加分号

If语句

If ***条件 then

Else if ***条件 then

Else

End if；--这里要加分号

Case语句

Case

When ***条件 then

When ***条件 then

When ***条件 then

Else

End Case；--这里要加分号

还可以写成：

Case 常量-- 一个字符变量

When ‘A‘ then

When ‘B‘ then

When ‘C‘ then

Else

End Case；--这里要加分号

循环结构，要达到循环在Oracle中有3种方式，各有各的好处，你懂的。它们分别如下：

第一种

Loop

****

Exit when 退出条件;

End loop;--要加分号

第二种

While 条件 loop

****

End loop；--要加分号

第三种

For I in 1..100 loop

***

End loop; --要加分号

PL结构中的错误处理

就像C#中的Try{} Catch{}语句块能捕获错误。写几个例子:

HelloWorld级别的错误抛出例子

declare

stu_info student%rowtype;

cursor stu_cursor is select * from student;

begin

select * into stu_info from student;

exception

when TOO_MANY_ROWS then

dbms_output.put_line('行太多');

when others then

dbms_output.put_line('未知错误 错误号:'||sqlcode||'错误信息 '||sqlerrm);

end;

手动抛出错误，类似于c#中的throw

declare

stu_info student%rowtype;

cursor stu_cursor is select * from student;

begin

Raise_Application_Error(-20001,'打酱油的错误');--显示抛出错误

exception

when TOO_MANY_ROWS then

dbms_output.put_line('行太多');

when others then

dbms_output.put_line('未知错误 错误号:'||sqlcode||'错误信息 '||sqlerrm);

end;

自定义一个错误，并把它手动抛出

declare

my_error Exception;

pragma Exception_Init(my_error,-29999);--这里很重要哦

begin

raise my_error;--抛出错误

exception

when others then

dbms_output.put_line('未知错误 错误号:'||sqlcode||'错误信息 '||sqlerrm);

end;

Record类型

Oracle中的Record类型类似于c语言中的结构体，主要用来接收Select语句或游标中返回的数据，下面写个例子：

declare

type student_record_type is record(

stu_name student.name%type,

stu_age student.age%type

);

student_record student_record_type;--这里很重要，不能直接在类型上操作

begin

select name,age into student_record from student where id=&id;

dbms_output.put_line(student_record.stu_name||' '||student_record.stu_age);

end;

DDL语句

这里的DDL语言主要是指能完成如下工作的DDL语言：创建表，创建表的主/外 键及级联效果，

建表：

Create Table student(

StuId number(5),

StuName varchar2(20),

StuAge number(2)

)

Create Table class(

StudentId number(5),

TeacherId number(5),

ClassName varchar2(30)

)

Create Table teacher

(

tId number(5),

tName varchar2(30),

tSalary number(5,2)

)

Alter Table class

Add Constraint p_k Primary Key (StudentId,TeacherId)

Alter table student

Add Constraint p_k Primary Key (StuId)

Alter Table class

Add Constraint f_k_1 Foreign Key (StudentId)

references student(id) on delete cascade

Alter Table class

Add Constraint f_k_2 Foreign Key (TeacherId)

references student(tId) on delete cascade

DML语句

Select语句。Oracle中的Select语句的使用方法与Sql Server差不多，但还是有些不同之处。

赋值方式不同：

Sql Server：

Select @peopleNumber=count(*) from people

Oracle：

Select count(*) into peopleNumber from people

内连接

Sql Server

Select s.id,s.name from student s inner join

class c on s.id=c.studentid where c.classname=’***’

Oracle:

Select s.id,s.name from student s inner join

class c on s.id=c.studentid where c.classname=’***’

左连接

Sql Server

Select s.id,s.name from student s left join

class c on s.id=c.studentid where c.classname=’***’

Oracle:

Select s.id,s.name from student s left outer join

class c on s.id=c.studentid where c.classname=’***’

右连接

Sql Server

Select s.id,s.name from student s right join

class c on s.id=c.studentid where c.classname=’***’

Oracle:

Select s.id,s.name from student s right outer join

class c on s.id=c.studentid where c.classname=’***’

全连接

Sql Server

Select s.id,s.name from student s full join

class c on s.id=c.studentid where c.classname=’***’

Oracle:

Select s.id,s.name from student s full outer join

class c on s.id=c.studentid where c.classname=’***’

Insert语句。Oracle中的Insert语句比Sql Server中的强大很多，废话不多说，看例子：

单条数据的插入

Insert into student(id,name,age) values(1,’张三’,22);

插入的数据源来自select语句

Insert into studentfrom select id,name,age from tmp_student;

根据不同的条件，将数据插入到不同的表中

Insert all

when id between 1 and 3 then into x_testtable

when id between 4 and 6 then into x_testtable2

select id,name from studentDelete语句

Update语句。

Update student set name=’new’||name where id=1;

Delete语句。和标准的sql标准一致，没多大变化。

Delete from student where id=1

视图。视图有虚拟视图和物理视图两种，这里不说后者。创建视图的语法如下：

简单的视图：

Create View View_Student as

Select * from Student

复杂的视图:

Create View Teacher_Student as

Select t.name,count(s.id) 学生数 from student s inner join class c on s.id=c.id inner join teacher t on c.teacherid=t.id

Group by t.name

简单视图与复杂视图的区别在于：简单的视图能在视图上对实际存储的数据进行增/删/改 操作而复杂的视图却不能，但如果你实在是要对复杂的视图进行 增/删/改 操作，你可以使用Instead of 类型的Trigger来做。

存储过程

废话不多说，看代码：

HelloWorld级别的存储过程

create or replace procedure x_print_helloworld

as

begin

dbms_output.put_line('Hello World');

end;-- 分号是重要滴

带输入参数的存储过程，而且还支持默认值

create or replace procedure

x_print_something(msg varchar2 default 'helloworld')

as

begin

dbms_output.put_line(msg);

end;

带输出参数的存储过程

create or replace procedure x_getSum(n out number)

as

begin

for i in 1..n loop

n:=n+i;

end loop;

end;

定义了存储过程你得调用呀，看代码：

declare

begin

x_print_helloworld;

x_print_something;

x_print_something('abc');

x_jc(10);

end;

函数

和存储过程查不多，唯一的区别就是有返回值，而且还能嵌套在DML语句中使用。下面写几个简单的函数：

HelloWord级别的函数

create or replace function x_get_helloworld return varchar2

as

begin

return 'Hello World';

end;

统计某些数据的函数

create or replace function x_get_studentinfo_count return number

as

tmp number(5):=0;

begin

select count(*) into tmp from student;

return tmp;

end;

调用方法：

declare

begin

dbms_output.put_line(x_get_helloworld);

dbms_output.put_line(x_get_studentinfo_count);

end;

游标

在Sql中使用的比较少，一直觉得它挺神秘的。最近学习了下，但对它有啥好处还是相知甚少。游标分为以下几类：显示游标，隐式游标。显示游标里面又有匿名游标和非匿名游标，隐式游标是Oracle中提供的某些API接口，通过调用它们能获取某些重要的系统信息，在Sql Server中也有类似的功能如‘@@error‘。

Oracle中的隐式游标：

%notfound游标专用的隐式游标呀，判断游标中是否还有可返回的数据

%isopen判断游标是否打开

%rowtype定义行类型的mycontent student%rowtype表示开辟一个能包含student表中一条元组的变量空间，并将该地址赋予变量mycontent.

%type定义列类型的 mycolumns student.name%type概念同上，开辟一个列。

%rowcount当前返回的数据行数

普通游标的定义及使用

declare

cursor stu_info is select * from student for update;

stu_record student%rowtype;

begin

open stu_info;

loop

fetch stu_info into stu_record;

exit when stu_info%notfound;

if stu_record.name='bank' then

update student set name='new_bank' where current of stu_info;

end if;

if stu_record.id=10 then

delete from class where studentid=stu_record.id;

delete from student where current of stu_info;

end if;

end loop;

close stu_info;

end;

带参数的游标的定义及使用

declare

cursor classInfo(id number) is select * from class where teacherid=id;

class_record class%rowtype;

begin

open classInfo(1);

loop

fetch classInfo into class_record;

exit when classInfo%notfound;

dbms_output.put_line('studentid:'||class_record.studentid||' classname:'||class_record.classname);

end loop;

close classInfo;

end;

简写的游标定义及使用

declare

cursor info is select name from student;

begin

for stuName in info loop

dbms_output.put_line('第'||info%rowcount||'条记录 '||stuName.name);

end loop;

end;

匿名游标的定义及使用

declare

begin

for stuName in (select * from student) loop

dbms_output.put_line(stuName.name);

end loop;

end;

游标变量

游标变量与C语言中的指针函数类似。游标变量又分为指明返回类型的游标变量和不指明返回类型的游标变量。

不声明返回类型的游标变量

declare

type info is ref cursor;

stu_info info;

stu_record student%rowtype;

begin

open stu_info for select * from student;

loop

fetch stu_info into stu_record;

exit when stu_info%notfound;

dbms_output.put_line(stu_record.name);

end loop;

close stu_info;

end;

声明返回类型的游标变量，用这种方式声明的游标不支持%rowtype类型的变量 声明返回类型

declare

type class_record_type is record(classname class.classname%type);

type class_cursor_type is ref cursor return class_record_type;

class_record class_record_type;

class_cursor class_cursor_type;

begin

open class_cursor for select classname from class;

loop

fetch class_cursor into class_record;

exit when class_cursor%notfound;

dbms_output.put_line(class_record.classname);

end loop;

close class_cursor;

end;

还有几种比较高级的游标写法，不知道工作用会不会用到。它们分别是：

嵌套游标：

declare

cursor info(n number) is select c.classname,cursor(select s.name from student s where s.id=c.studentid )

from class c where c.studentid=&n;

type cursor_type is ref cursor;

class_cursor cursor_type;

classname class.classname%type;

tmp varchar2(100);

begin

open info(1);

loop

fetch info into classname,class_cursor;

exit when info%notfound;

dbms_output.put_line(classname||':');

loop

fetch class_cursor into tmp;

exit when class_cursor%notfound;

dbms_output.put_line(tmp);

end loop;

end loop;

close info;

end;

批量返回数据的游标：

declare

cursor student_cursor is select name from student;

type name_table_type is table of varchar(20);

name_table name_table_type;

begin

open student_cursor;

fetch student_cursorbulk collect into name_table;

for i in 1..name_table.count loop

dbms_output.put_line(name_table(i));

end loop;

close student_cursor;

end;

批量返回数据的游标但可以限制每次返回数据的行数的游标

declare

cursor student_cursor is select name from student;

type name_table_type is table of varchar(20);

name_table name_table_type;

rlimit number(2):=10;

vcount number(2):=0;

begin

open student_cursor;

loop

fetch student_cursorbulk collect into name_table limit rlimit;

exit when student_cursor%notfound;

dbms_output.put_line('rowcount:'||student_cursor%rowcount||'vcount:'||vcount);

for i in 1..(student_cursor%rowcount-vcount) loop

dbms_output.put_line(name_table(i));

end loop;

dbms_output.new_line();

vcount:=student_cursor%rowcount;

end loop;

close student_cursor;

end;

触发器

触发器好东西呀。很多通过常规方法很难解决的问题通过使用它都能简单的解决，但它们就是难管理。在Oracle中的触发器分两类：在表级别上的触发器和在数据行上的触发器。

DML Before触发器

create or replace trigger stu_before_trigger

before insert or update or delete on student

begin

case

when inserting then

dbms_output.put_line('添加了一条新纪录 By DML Before触发器');

when updating then

dbms_output.put_line('更新了一条新纪录By DML Before触发器');

when deleting then

dbms_output.put_line('删除了一条新纪录By DML Before触发器');

else

dbms_output.put_line('不知道你干了啥子By DML Before触发器');

end case;

end;

DML After触发器

create or replace trigger stu_after_trigger

after insert or update or delete on student

begin

case

when inserting then

dbms_output.put_line('添加了一条新纪录2By DML After触发器');

when updating then

dbms_output.put_line('更新了一条新纪录2By DML After触发器');

when deleting then

dbms_output.put_line('删除了一条新纪录2By DML After触发器');

else

dbms_output.put_line('不知道你干了啥子2By DML After触发器');

end case;

end;

DML before行触发器

create or replace trigger stu_row_before_trigger

before insert or update or delete on student

for each row

when (old.id between 10 and 20) --约束条件 去掉后就是对所有的行进行触发操作

begin

case

when inserting then

dbms_output.put_line('您添加了一条新记录: By DML Row Before触发器');

dbms_output.new_line();

dbms_output.put_line('ID:'||:new.id||' Name:'||:new.name||' Age:'||:new.age);

when updating then

dbms_output.put_line('您更新了一条记录:By DML Row Before触发器');

dbms_output.new_line();

dbms_output.put_line('更新前的记录:');

dbms_output.put_line('ID:'||:old.id||' Name:'||:old.name||' Age:'||:old.age);

dbms_output.put_line('更新后的记录:');

dbms_output.put_line('ID:'||:new.id||' Name:'||:new.name||' Age:'||:new.age);

when deleting then

dbms_output.put_line('您删除了一条记录:By DML Row Before触发器');

dbms_output.new_line();

dbms_output.put_line('ID:'||:old.id||' Name:'||:old.name||' Age:'||:old.age);

end case;

end;

DML after行触发器

create or replace trigger stu_row_after_trigger

after insert or update or delete on student

for each row

when (old.id between 10 and 20) --约束条件 去掉后就是对所有的行进行触发操作

begin

case

when inserting then

dbms_output.put_line('您添加了一条新记录: By DML Row After触发器');

dbms_output.new_line();

dbms_output.put_line('ID:'||:new.id||' Name:'||:new.name||' Age:'||:new.age);

when updating then

dbms_output.put_line('您更新了一条记录: By DML Row After触发器');

dbms_output.new_line();

dbms_output.put_line('更新前的记录:');

dbms_output.put_line('ID:'||:old.id||' Name:'||:old.name||' Age:'||:old.age);

dbms_output.put_line('更新后的记录:');

dbms_output.put_line('ID:'||:new.id||' Name:'||:new.name||' Age:'||:new.age);

when deleting then

dbms_output.put_line('您删除了一条记录: By DML Row After触发器');

dbms_output.new_line();

dbms_output.put_line('ID:'||:old.id||' Name:'||:old.name||' Age:'||:old.age);

end case;

end;

DML instead of触发器instead of 触发器只能作用于视图

create view view_student_table as select * from student;

create or replace trigger stu_instead_trigger

instead of delete on view_student_table

begin

if :old.id>50 then

delete from student where id=:old.id;

else

dbms_output.put_line('木有操作');

end if;

end;

用来替代级联操作的触发器必须采用行触发器

create or replace trigger stu_delete_cascade_trigger

before delete on student

for each row

begin

delete from class where studentid=:old.id;

end;

级联更新操作的触发器 使用了列表技术来过滤是否更新了自己感兴趣的列

create or replace trigger stu_update_cascade_trigger

before update of id on student-- 只有更新了id列才触发

for each row

begin

update class set studentid=:new.id where studentid=:old.id;

end;

管理触发器的一些命令

alter trigger stu_delete_cascade_trigger disable;--停用触发器

alter trigger stu_delete_cascade_trigger enable;--启用触发器

alter trigger stu_update_cascade_trigger disable;--停用触发器

alter trigger stu_update_cascade_trigger enable;--启用触发器

包

类似与c#中的命名空间。它分为两个部分：包规范和包体。它还支持重载能力，在包中可以包含相同名称的函数或存储过程，只有它们的参数不同就不妨碍它们各自的调用。下面写几个包，看代码：

HelloWorld级别的包

create or replace package mypackage_helloworld

as

procedure HelloWorld;

functionHelloWorld return varchar2;

end;

create or replace package body mypackage_helloworld

as

procedure HelloWorld

as

begin

dbms_output.put_line('Hello World By Procedure!');

end;

functionHelloWorld return varchar2

as

begin

return 'Hello World By Function!';

end;

end;--这个很重要，一定要加上

学生管理系统的包 包重载

create or replace package mypackage_student

as

myVersion nvarchar2(250);

procedure printfCopyRight;

procedure printfCopyRight(message varchar2);

procedure addStudent(stuId number,stuName varchar2,stuAge number);

function getStuNumbersByTeacher(tName varchar2) return number;

function getStuNumbersByTeacherId(tId number) return number;

end; --包规范

create or replace package body mypackage_student

as

procedure printfCopyRight

as

begin

dbms_output.put_line(myVersion);

end;

procedure printfCopyRight(message varchar2)

as

begin

dbms_output.put_line(message);

end;

procedure addStudent(stuId number,stuName varchar2,stuAge number)

as

begin

insert into student values(stuId,stuName,stuAge);

end;

function getStuNumbersByTeacher(tName varchar2) return number

as

tmp number:=0;

begin

select count(id) into tmp from student;

return tmp;

end;

function getStuNumbersByTeacherId(tId number) return number

as

tmp number:=0;

begin

select count(*) into tmp from student s inner join class c on s.id=c.studentid

where c.teacherid=tId;

return tmp;

end;

end mypackage_student;--这个一定要加啊

调用代码：

declare

tmp number;

begin

-- mypackage.HelloWorld;

-- dbms_output.put_line(mypackage.HelloWorld);

mypackage_student.myVersion:='学生管理系统 作者：大熊';

mypackage_student.printfCopyRight;

mypackage_student.printfCopyRight('这个是重载方法哦');

--mypackage_student.addStudent(99,'大熊',24);

--tmp:=mypackage_student.getStuNumbersByTeacher('Mis Lee');

tmp:=mypackage_student.getStuNumbersByTeacher(1);

dbms_output.put_line(tmp);

end;

索引表，嵌套表，可变长的表

索引表，下标可以为负呀：

declare

type nameTable is table of student.name%type index by binary_integer;

stu_name nameTable;

begin

select name into stu_name(-1) from student where id=&id;

dbms_output.put_line(stu_name(-1));

end;

嵌套表

declare

type nameTable is table of varchar2(50);

stu_name nameTable:=nameTable('abc','abc');--用前要初始化

begin

select name into stu_name(1) from student where id=&id;

dbms_output.put_line(stu_name(1));

end;

可变成的表，这里将它们嵌套起来可以做出2维数组的形式。

declare

type a1_varray_type is varray(10) of int;

type na1_varray_type is varray(10) of a1_varray_type;

nv1 na1_varray_type:= na1_varray_type(

a1_varray_type(1,2,3),

a1_varray_type(4,5,6),

a1_varray_type(7,8,9)

);

begin

for i in 1..nv1.count loop

for j in 1..nv1(i).count loop

dbms_output.put_line(nv1(i)(j));

end loop;

end loop;

end;

嵌套表的二维数组形式:

declare

type family is table of varchar2(20);

type familyCollection is table of family;

--MyFamily family:=family('jim','ann','bank');

myFamilyCollection familyCollection:=familyCollection(

family('jim','ann','bank'),

family('jim2','ann2','bank2'),

family('jim3','ann3','bank3')

);

begin

for i in 1..myFamilyCollection.count loop

for j in 1..myFamilyCollection(i).count loop

dbms_output.put_line(myFamilyCollection(i)(j));

end loop;

end loop;

end;

索引表的二维数组形式：

declare

type family is table of varchar2(20) index by binary_integer;

type familyCollection is table of family index by binary_integer;

myFamilyCollection familyCollection;

begin

myFamilyCollection(1)(1):='1';

myFamilyCollection(1)(2):='2';

myFamilyCollection(1)(3):='3';

myFamilyCollection(2)(1):='4';

myFamilyCollection(2)(2):='5';

myFamilyCollection(2)(3):='6';

myFamilyCollection(3)(1):='7';

myFamilyCollection(3)(2):='8';

myFamilyCollection(3)(3):='9';

for i in 1..myFamilyCollection.count loop

for j in 1..myFamilyCollection(i).count loop

dbms_output.put_line(myFamilyCollection(i)(j));

end loop;

end loop;

end;

上面说的这3类表都支持下面这些方法：

Count,limit,first,last,prior,next,extend,trim