Oracle数据库学习总结.doc

Oracle数据库学习总结对于一位程序员来说并不需要完全掌握Oracle的所有知识，毕竟自己不是DBA。在日常开发中也用不到那些命令和工具，但是有些知识点我们还是必须得熟练的掌握它们。比如:一些基本的DDL和DML语句，存储过程，函数，视图，触发器，序列，游标，自定义类型和包。下面我就把这段时间里学习Oracle获得的知识点罗列出来，一是为了方便以后查阅，二是为了和搭档交流学习经验。要适应的一些细节从Sql Server转到Oracle进行数据库编程，第一道门槛就是语法问题。很多很多的问题都是因为语法而产生的，现将它们统统集合起来并将它们一网打尽之。参数赋值Sql ServerSet parameter=5 -加不加分号无所谓，这里是用=号进行赋值的OracleParameter：=5；-必须加分号，并使用加：的=号进行赋值的PL结构。在Sql Server中，采用的是批处理执行任务的方式，所以可以将多条sql语句选中批量执行，而不用顾忌要在专门的地方声明变量，在专门的地方进行逻辑编码。在Oracle中采用的是PL编程方式，必须在专门的地方声明变量，在专门的地方进行流程编码，经典的PL如下：Declare -这里是专门用来定义变量的Begin-这里是专门用来进行程序编码的End;-这里必须加分号If语句If *条件 thenElse if *条件 thenElseEnd if；-这里要加分号Case语句Case When *条件 then When *条件 then When *条件 then ElseEnd Case；-这里要加分号还可以写成：Case 常量 - 一个字符变量 When A then When B then When C then ElseEnd 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#中的throwdeclare stu_info student%rowtype; cursor stu_cursor is select * from student;beginRaise_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 %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 classAdd Constraint p_k Primary Key (StudentId,TeacherId)Alter table studentAdd Constraint p_k Primary Key (StuId)Alter Table classAdd Constraint f_k_1 Foreign Key (StudentId) references student(id) on delete cascadeAlter Table classAdd Constraint f_k_2 Foreign Key (TeacherId) references student(tId) on delete cascadeDML语句Select语句。Oracle中的Select语句的使用方法与Sql Server差不多，但还是有些不同之处。赋值方式不同： Sql Server： Select peopleNumber=count(*) from people Oracle： Select count(*) into peopleNumber from people内连接 Sql Server Select s.id, from student s inner join class c on s.id=c.studentid where c.classname=* Oracle: Select s.id, from student s inner join class c on s.id=c.studentid where c.classname=* 左连接 Sql Server Select s.id, from student s left join class c on s.id=c.studentid where c.classname=* Oracle: Select s.id, from student s left outer join class c on s.id=c.studentid where c.classname=*右连接 Sql Server Select s.id, from student s right join class c on s.id=c.studentid where c.classname=* Oracle: Select s.id, from student s right outer join class c on s.id=c.studentid where c.classname=*全连接 Sql Server Select s.id, from student s full join class c on s.id=c.studentid where c.classname=* Oracle: Select s.id, 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 allwhen id between 1 and 3 then into x_testtablewhen 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 asSelect ,count(s.id) 学生数 from student s inner join class c on s.id=c.id inner join teacher t on c.teacherid=t.idGroup by 简单视图与复杂视图的区别在于：简单的视图能在视图上对实际存储的数据进行增/删/改 操作而复杂的视图却不能，但如果你实在是要对复杂的视图进行 增/删/改 操作，你可以使用Instead of 类型的Trigger来做。存储过程废话不多说，看代码：HelloWorld级别的存储过程create or replace procedure x_print_helloworldasbegin dbms_output.put_line(Hello World);end;- 分号是重要滴带输入参数的存储过程，而且还支持默认值create or replace procedure x_print_something(msg varchar2 default helloworld)asbegin dbms_output.put_line(msg);end;带输出参数的存储过程create or replace procedure x_getSum(n out number)asbegin for i in 1.n loop n:=n+i; end loop;end;定义了存储过程你得调用呀，看代码：declarebegin x_print_helloworld; x_print_something; x_print_something(abc); x_jc(10);end;函数,和存储过程查不多，唯一的区别就是有返回值，而且还能嵌套在DML语句中使用。下面写几个简单的函数：HelloWord级别的函数create or replace function x_get_helloworld return varchar2asbegin return Hello World;end;统计某些数据的函数create or replace function x_get_studentinfo_count return numberastmp number(5):=0;begin select count(*) into tmp from student; return tmp;end;调用方法：declarebegin 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 %type 概念同上，开辟一个列。%rowcount 当前返回的数据行数普通游标的定义及使用declarecursor 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_=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;简写的游标定义及使用declarecursor info is select name from student;begin for stuName in info loop dbms_output.put_line(第|info%rowcount|条记录 |stuN); end loop; end;匿名游标的定义及使用declare begin for stuName in (select * from student) loop dbms_output.put_line(stuN); 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_); 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 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_cursor bulk 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_cursor bulk 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_triggerbefore insert or update or delete on studentbegin 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_triggerafter insert or update or delete on studentbegin 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_triggerbefore insert or update or delete on studentfor each rowwhen (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:|:| 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:|:| Age:|:old.age); dbms_output.put_line(更新后的记录:); dbms_output.put_line(ID:|:new.id| 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:|:| Age:|:old.age); end case;end;DML after 行触发器create or replace trigger stu_row_after_triggerafter insert or update or delete on studentfor each rowwhen (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:|:| 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:|:| Age:|:old.age); dbms_output.put_line(更新后的记录:); dbms_output.put_line(ID:|:new.id| 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:|:| 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_triggerinstead of delete on view_student_tablebegin if :old.id50 then delete from student where id=:old.id; else dbms_output.put_line(木有操作); end if;end;用来替代级联操作的触发器 必须采用行触发器create or replace trigger stu_delete_cascade_triggerbefore delete on studentfor each rowbegin delete from class where studentid=:old.id;end;级联更新操作的触发器 使用了列表技术来过滤是否更新了自己感兴趣的列create or replace trigger stu_update_cascade_triggerbefore update of id on studentfor each rowbegin 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_studentasmyVersion 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_studentasprocedure printfCopyRightasbegin dbms_output.put_line(myVersion);end;procedure printfCopyRight(message varchar2)asbegin dbms_output.put_line(message);end;procedure addStudent(stuId number,stuName varchar2,stuAge number)asbegin insert into student values(stuId,stuName,stuAge);end;function getStuNumbersByTeacher(tName varchar2) return numberastmp number:=0;begin select count(id) into tmp from student; return tmp;end;function getStuNumbersByTeacherId(tId number) return numberastmp 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;-这个一定要加啊调用代码：declaretmp 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;索引表，嵌套表，可变长的表索引表，下标可以为负呀：declaretype nameTable is table of %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 t