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 student from 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_tablelimit 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('添加了一条新纪录2 By DML After触发器');
when updating then
dbms_output.put_line('更新了一条新纪录2 By DML After触发器');
when deleting then
dbms_output.put_line('删除了一条新纪录2 By DML After触发器');
else
dbms_output.put_line('不知道你干了啥子2 By 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;
function HelloWorld return varchar2;
end;
create or replace package body mypackage_helloworld
as
procedure HelloWorld
as
begin
dbms_output.put_line('Hello World By Procedure!');
end;
function HelloWorld 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
扫一扫
2413
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
