Oracle数据库基础知识
|
对于一位程序员来说并不需要完全掌握Oracle的所有知识，毕竟自己不是DBA。在日常开发中也用不到那些命令和工具，但是有些知识点我们还是必须得熟练的掌握它们。比如:一些基本的DDL和DML语句，存储过程，函数，视图，触发器，序列，游标，自定义类型和包。下面罗列下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_recordstudent_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=’***’ 左连接 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=’***’ 右连接 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=’***’ 全连接 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,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 collectinto 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 collectinto name_tablelimitrlimit; 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 beforeupdate 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,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','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
一、关系数据库的操作语言：...........；1.数据定义语言DDL.............；2.数据操纵语言DML.............；3.数据控制语言DCL.............；4.简单的sql语句：.............；二、SQL查询语句：..............；1.单表查询：.................；2.多表查 一、关系数据库的操作语言： ....................................................................................................... 1.数据定义语言DDL .............................................................................................................. 2 2.数据操纵语言DML .............................................................................................................. 2 3.数据控制语言DCL ............................................................................................................... 2 4.简单的sql语句： ................................................................................................................. 2 二、SQL查询语句： ...................................................................................................................... 3 1.单表查询： ............................................................................................................................ 4 2.多表查询： ............................................................................................................................ 4 3.嵌套查询: .............................................................................................................................. 5 4.函数查询： ............................................................................................................................ 7 详细的oracle常用函数 .......................................................................................................... 8 5.外连接outer join ................................................................................................................... 8 三、SQL删除数据 ........................................................................................................................ 10 四、PL/SQL ................................................................................................................................... 11 1.PL/SQL结构： ................................................................................................................... 11 2.PL/SQL中常用的基本数据类型 ： ................................................................................. 12 3.复合数据类型变量： .......................................................................................................... 12 4.表达式 .................................................................................................................................. 15 5.数据转换函数 ...................................................................................................................... 16 6.流程控制 .............................................................................................................................. 16 7.事务处理 .............................................................................................................................. 16 8.游标...................................................................................................................................... 17 9.存储过程 .............................................................................................................................. 21 10. java中调用存储过程或函数 ........................................................................................... 26 11.触发器 ................................................................................................................................ 27 12. 异常处理 .......................................................................................................................... 32 五、数据库范式 ............................................................................................................................. 33 六、视图......................................................................................................................................... 33 一、关系数据库的操作语言： 1.数据定义语言DDL 数据定义语言包括：数据库的定义（创建、修改、删除）；表的定义（创建、修改、删除）；索引的定义（创建、修改、删除）；触发器的定义（创建、修改、删除） 创建表： Create table user( ) 修改表： 添加新列：alter table user add u_qq varchar2(10) 更改列名：alter table user rename column u_name to u_realname 删除一列：alter table user drop column u_salary 删除表：Drop table user Create sequence u_seq 2.数据操纵语言DML 数据操作语言包括：数据查询(select)、数据插入(insert)、数据修改(update)、数据删除(delete) Select * from user where id=? Insert into user values(u_seq.nextval,’dwj’,’male’,4000) Insert into user(u_id,u_name) values(u_seq.nextval,’dgy’) Update table user set sex=’female’ where name=’dwj’ Delete from user where name=’dwj’ 3.数据控制语言DCL 数据控制语言：用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等） Grant select on user to zwm Grant insert,delete on user to dwj 4.简单的sql语句： create table departments( --主键，表示一条记录的唯一性 department_id number(6) primary key,U_id number(10) primary key,U_name Varchar2(20),U_sex Varchar2(10),U_salary number(10) default 1000 ) department_name varchar2(20),salary number(6) default 100,location_id number(6),--约束，dep_salary_min表示约束名称 constraint dep_salary_min check(salary>0) --primary key(department_id) --创建一个序列来自增 create sequence dep_seq; --插入一条记录,主键是通过调用sequence的nextval方法来不断自增，保证id永远唯一 insert into departments values (dep_seq.nextval,'management',-6,2) --表示提交事务，让数据在数据库中持久化 commit; --查询数据，可以同过关键字where来做为条件查询 select * from departments where salary >2500 --修改一条记录 update departments set salary = 1800 where department_name='logistics' --删除一条记录 delete from departments where department_name='logistics' delete from departments where department_id=1 数据约束： 主键（主码）（primary key）：能够唯一的表示数据表中每条记录的字段或字段的组合。 外键（外码）（foreign key）：一个表中的某列引用的是另个表中的某列，我们就说这个表的这个列是另个表中列的外键。通常一个表的外键是另个表的主键。 外键的作用：检查完整性，级联删除 二、SQL查询语句： （1）SELECT子句用于指定检索数据库的中哪些列，FROM子句用于指定从哪一个表或视图中检索数据。 （2） SELECT中的操作符及多表查询WHERE子句中的条件可以是一个包含等号或不等号的条件表达式，也可以是一个含有IN、NOT IN、BETWEEN、LIKE、IS NOT NULL等比较运算符的条件式，还可以是由单一的条件表达通过逻辑运算符组合成复合条件。 （3） ORDER BY 子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。升序ASC（默认）,降序DESC。 （4）连接查询。利用SELECT语句进行数据库查询时，把多个表、视图的数据结合起来，使得查询结果的每一行中包含来自多个表达式或视图的数据，这种操作被称为连接查询。 连接查询的方法是在SELECT命令的FROM子句中指定两个或多个将被连接查询的表或视图，并且在WHERE子句告诉ORACLE如何把多个表的数据进行合并。根据WHERE子句中的条件表达式是等还是不等式，可以把连接查询分为等式连接和不等式连接。 （5）子查询。如果某一个SELECT命令（查询1）出现在另一个SQL命令（查询2）的一个子句中，则称查询1是查询2的子查询。 1.单表查询： (1) 查询所有的记录 Select * from emp； 这里的*代表表里所有的字段。 查询第一条记录：rownum是伪列 Select * from emp where rownum=1; (2) 查询表里的某些字段 Select empno,ename,job from emp Select 字段名1，字段名2…..from 数据表，将显示某些特定的字段，注意这里字段名之间的逗号是英文状态下的逗号。 (3) 查询某一个字段的不同记录（某个字段下的值可能有相同的） Select distinct 字段名 from 数据表，这里的distinct指在显示时去除相同的记录，默认的是”all”,保留相同的记录。 (4) 单条件的查询 Select empno,job from emp where job=’manager’ Select empno,sal from emp where sal<=2500 WHERE中功能操作符： 2.多表查询： (1) 无条件多表查询：将表的记录以“笛卡尔积”的方式组合起来。 Dept表中有4条记录，emp表中有10条记录，其“笛卡尔积”将有4*10=10条记录。 Select emp.empno,emp.ename,emp.deptno,dept.dname,dept.loc from emp,dept; (2) 等值多表查询：按照等值的条件查询多个数据表中关联的数据，要求关联的多个数据表比较：=、>、〈、〉=、〈=、〈〉、!=、!<、!> 范围：BETWEEN、 NOT BETWEEN 列表：IN、NOT IN 字符串匹配：LIKE、 NOT LIKE 未知值判断：IS NULL、IS NOT NULL 组合条件：AND、OR 取反：NOT 条件中的通配符：% 代表任意多个字符， _（下划线） 代表单个字符。Like ‘m%’代表m开头的任意长度的字符串，like’m__’代表m开头的长度为3的字符串。 的某些字段具有相同的属性，即具有相同的数据类型、宽度和取值范围。 Select emp.empno,dept.dname.dept.loc from emp,dept where emp.deptno=dept.deptno; (3) 非等值多表查询 Select emp.empno,dept.loc 3.嵌套查询: (1)简单的嵌套查询 Select emp.empno emp.ename,emp.sal from emp where sal>=( ); select sal from emp where ename=’ward’ from emp,dept where emp.deptno!=dept.deptno and emp.deptno=10; 这里的查询条件可以使用比较运算符来组合。 先执行括号里的查询，再执行外面的查询。 (2)带in的嵌套查询 Select emp.empno emp.ename,emp.sal from emp where sal in ( ); select sal from emp where ename=’ward’ 在集合里取数据使用in，若集合里只有1条记录，可以将in换为’=’; (3)带any的嵌套查询 Select emp.empno emp.ename,emp.sal from emp where sal > any ( ); select sal from emp where job=’manager’ 子查询的结果有多个值，>any是大于任意一个，分解后条件之间是or。 (4)带some的嵌套查询 Select emp.empno emp.ename,emp.sal from emp where sal =some( ); select sal from emp where job=’manager’ 子查询的结果有多个值，=somey是等于任意一个，分解后条件之间是or。 所以带any的嵌套查询和带some的嵌套查询功能是一样的。 (5)带all的嵌套查询 Selectemp.empnoemp.ename；wheresal>all();select；子查询的结果有多个值，>all是大于所有的；(6)带exists的嵌套查询；selectemp.empno,emp.enam；whereexists();select*fro；Exists后面的子查询，表示只产生逻辑值，不返；exists:存在，后面一般 Select emp.empno emp.ename,emp.sal from emp where sal >all ( ); select sal from emp where job=’manager’ 子查询的结果有多个值，>all是大于所有的，分解后条件之间是and。 (6)带exists的嵌套查询 select emp.empno,emp.job,emp.sal from emp,dept where exists ( ); select * from emp where emp.deptno=dept.deptno Exists后面的子查询，表示只产生逻辑值，不返回数据。子查询存在数据，则执行父查询。 in和exists的区别： exists:存在，后面一般都是子查询，当子查询返回行数时，exists返回true。 select * from class where exists (select'x"form stu where stu.cid=class.cid) 当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率。 exists(xxxxx)后面的子查询被称做相关子查询,它是不返回列表的值的。只是返回一个ture或false的结果(这也是为什么子查询里是select 'x'的原因，也可以select任何东西) 也就是它只在乎括号里的数据能不能查找出来，是否存在这样的记录。 其运行方式是先运行主查询一次，再去子查询里查询与其对应的结果，如果存在，返回ture则输出,反之返回false则不输出,再根据主查询中的每一行去子查询里去查询。 执行顺序如下： 1.首先执行一次外部查询 2.对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。 3.使用子查询的结果来确定外部查询的结果集。 如果外部查询返回100行，SQL就将执行101次查询，一次执行外部查询，然后为外部查询返回的每一行执行一次子查询。 in：包含 查询和所有女生年龄相同的男生 select * from stu where sex='男' and age in(select age from stu where sex='女') in()后面的子查询是返回结果集的,换句话说执行次序和exists()不一样。子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出. not in和not exists的区别： not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in。 例如:查询那些班级中没有学生的 select * from class where cid not in(select distinct cid from stu) 当表中cid存在null值，not in 不对空值进行处理 解决:select * from class where cid not in(select distinct cid from stu where cid is not null) not in的执行顺序是：是在表中一条记录一条记录的查询(查询每条记录）符合要求的就返回结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完。也就是说为了证明找不到，所以只能查询全部记录才能证明。并没有用到索引。 not exists：如果主查询表中记录少，子查询表中记录多，并有索引。 例如:查询那些班级中没有学生的， select * from class2 where not exists (select * from stu1 where stu1.cid =class2.cid) not exists的执行顺序是：在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。之所以要多用not exists，而不用not in，也就是not exists查询的效率远远高与not in查询的效率。 (7)并操作的嵌套查询 并操作就是集合中并集的概念。属于集合A或集合B的元素总和就是并集。 (select deptno from emp) union (select deptno from dept); (8) 交操作的嵌套查询 交操作就是集合中交集的概念。属于集合A且属于集合B的元素总和就是交集。 (select deptno from emp) intersect (select deptno from dept); (9) 差操作的嵌套查询 差操作就是集合中差集的概念。属于集合A且不属于集合B的元素总和就是差集。 (select deptno from dept) minus (select deptno from emp); 并、交、差操作的嵌套查询要求属性具有相同的定义，包括类型和取值范围。 4.函数查询： (1) ceil：ceil（n），取大于等于数值n的最小整数。 (2)floor：floor（n），取小于等于数值n的最大整数。 (3)mod：mod（m,n），取m整除n后的余数。 (4) power：power（m,n），取m的n次方。 (5) round：round（m,n），四舍五入，保留n位。 (6)sign：sign（n）。n>0，取1；n=0，取0；n<0，取-1。 (7) avg：avg（字段名），求平均值。要求字段为数值型。 如果表中对应的字段为null，则求平均值不计算在内 (8) count：count（字段名）或count（*），统计总数。 select count(*) 记录总数 from emp; select count(distinct job ) 工作类别总数 from emp;不重复的查询 (9) min：min（字段名），计算数值型字段最小数。 (10) max：max（字段名），计算数值型字段最大数。 max()中可以使用count(): 被选修最多人数的课程号： select c_id from class group by c_id having count(c_id)=( ) max()中也可以使用avg(): select c_id from class group by c_id having avg(cl_grade)=( select max(avg(cl_grade)) from class group by c_id ) (11) sum：sum（字段名），计算数值型字段总和。 (12)查看系统时间：select sysdate from dual 详细的oracle常用函数 5.外连接outer join 外连接分为左外连接、右外连接、全外连接。对应sql语句是:table 1 left/right/full outer join table2,通常我们省略outer。 利用一个例子说明数据库的外连接问题： 首先建2张表，outj1和outj2,分别只有2个字段 : select max(count(c_id)) from class group by c_id 插入一些数据： outj1的数据为： outj2的数据为： outj1为左表，outj2为右表： (+)的用法： 1.（+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。 2. 当使用（+）操作符执行外连接时，如果在where子句中包含多个条件，必须在所有 条件中包含（+）操作符 3.（+）操作符只适用于列，而不能用在表达式上。 4.（+）操作符不能与or和in操作符一起使用。 5.（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。 三、SQL删除数据 使用delete命令可以删除数据，使用truncate命令可以删除整表数据但保留结构。 删除记录的语法：delete from 数据表 where 条件。 整表数据删除：truncate table test ; truncate table命令将快速删除数据表中的所有记录，但保留数据表结构。这种快速删除与delete from 数据表的删除全部数据表记录不一样，delete命令删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复，而truncate命令删除的数据是不可以恢复的。 通过PL/SQL循环分段删除：对delete方法进行优化改进的。这种方法通过一段PL/SQL程序循环分段删除数据，逐步提交事务，达到缩小事务规模，安全删除数据的目的。 例如有一个数据表tem_employee，我们将对其中字段tem_employee.salary满足小于2000的记录进行删除，可以采用以下的PL/SQL程序。 四、PL/SQL；PLSQL将变量,控制结构,过程和函数等结构化程；1.PL/SQL结构：；declare；---变量名称mm，constant表示常量，i；mmconstantint:=100;；iint:=1;；begin；foriin1..mmloop；---连接符号是||；insertintotable(sno,sdat；endloop;；dbms_ 四、PL/SQL PLSQL将变量,过程和函数等结构化程序设计的要素引入了SQL语言中,这样就能够编制比较复杂的SQL程序了,利用PL/SQL语言编写的程序也称为PL/SQL程序块. 1.PL/SQL结构： declare ---变量名称mm，constant表示常量，int变量类型，赋值符号:=，初值100 mm constant int:=100; i int :=1; begin for i in 1..mm loop ---连接符号是|| insert into table(sno,sdate) values(i,sysdate); end loop; dbms_output.put_line('成功录入数据!'); --- Commit 是数据持久化。 commit; end; 一个完整的PL/SQL程序的总体结构如下： delacre 定义语句段 begin 执行语句段 exception 异常处理语句段 end 2.PL/SQL中常用的基本数据类型 ： Number 数字型 Int 整数型 Pls_integer 整数型,产生溢出时出现错误 Binary_integer 整数型,表示带符号的整数 Char 定长字符型,最大255个字符 Varchar2 变长字符型,最大2000个字符 Long 变长字符型,最长2GB Date 日期型 Boolean 布尔型(TRUE,FALSE,NULL三者取一) 3.复合数据类型变量： （1）使用%type定义变量。变量的数据类型和数据表中的字段的数据类型一致。 declare （2）定义记录类型变量。 定义自己需要的记录类型，这个类型里面有参数，参数的名称和类型也是自己定义的。 declare ---先定义（使用type 。。 is record）自己的记录类型名myrecord， myrecord的组成是type myrecord is record(myrecordnumber int,mycurrentdate date); ---变量名是srecord，变量类型是myrecord（自己定义的类型） srecord myrecord; begin ---从testtable中将recordnumber是68的记录放在srecord中 select * into srecord from testtable where recordnumber=68; dbms_output.put_line(srecord.mycurrentdate); mydate testtable.currentdate%type; begin commit; end; 定义mydate变量的类型和testtable数据表中的currentdate字段类型是一致的。 有两个参数，int类型的和date类型的 end; 在PL/SQL程序中,select语句总是和into配合使用,into子句后面就是要被 赋值的变量。 （3）使用%rowtype定义变量 使用%type是变量获得字段的数据类型,使用%rowtype是变量获得整个记录的数据类型。 比较两者定义的不同： 变量名 数据表.列名%type //定义普通变量，类型和某列一致 变量名 数据表%rowtype //定义复合类型变量，类型与表结构相同 declare ---复合类型变量名mytable，它与数据表testtable的结构一样 mytable testtable%rowtype; begin select * into mytable from testtable where recordnumber=88; dbms_output.put_line(mytable.currentdate); end; （4）定义一维表类型变量 （相当于高级语言中的一维数组） 定义表类型变量的语法如下： type 表类型 is table of 类型 index by binary_integer; 表变量名 表类型; 类型可以是前面的类型定义,index by binary_integer子句代表以符号整数为索引,这样访问表类型变量中的数据方法就是"表变量名(索引符号整数)"。 Declare ---两个一维表类型tabletype1和tabletype2,相当于一维数组 type tabletype1 is table of varchar2(4) index by binary_integer; type tabletype2 is table of testtable.recordnumber%type index by binary_integer; --- table1和table2分别是两种表类型变量名 table1 tabletype1; table2 tabletype2; begin ---分别给table1和table2赋值 table1(1):='大学'; table1(2):='大专'; table2(1):=88; table2(2):=55; dbms_output.put_line(table1(1)||table2(1)); dbms_output.put_line(table1(2)||table2(2)); end; declare ---多维表类型tabletype1,相当于多维数组,table1是多维表类型变量 （5）定义多维表类型变量 （相当于多维数组） type tabletype1 is table of testtable%rowtype index by binary_integer; table1 tabletype1; begin ---将数据表testtable中recordnumber为60的记录提取出来存放在table1中 select * into table1(60) from testtable where recordnumber=60; dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate); end; 在定义好的表类型变量里,可以使用count,delete,exists和prior等属性进行操作,使用方法为"表变量名.属性",返回的是数字。 下列PL/SQL程序,该程序定义了名为tabletype1的一维表类型,table1是一维表类型变量,变量中插入3个数据,综合使用了表变量属性。 declare type tabletype1 is table of varchar2(9) index by binary_integer; table1 tabletype1; begin table1(1):='成都市'; table1(2):='北京市'; table1(3):='青岛市'; dbms_output.put_line('总记录数:'||to_char(table1.count)); dbms_output.put_line('第一条记录:'||table1.first); dbms_output.put_line('最后条记录:'||table1.last); dbms_output.put_line('第二条的前一条记录:'||table1.prior(2)); dbms_output.put_line('第二条的后一条记录:'||table1.next(2)); if(table1.exists(2)) then dbms_output.put_line('第二条记录存在！'); else dbms_output.put_line('第二条记录不存在！'); end if; table1.delete(2); dbms_output.put_line('====================='); dbms_output.put_line('总记录数:'||to_char(table1.count)); dbms_output.put_line('第一条记录:'||table1.first); dbms_output.put_line('最后条记录:'||table1.last); dbms_output.put_line('第二条的前一条记录:'||table1.prior(2)); dbms_output.put_line('第二条的后一条记录:'||table1.next(2)); if(table1.exists(2)) then dbms_output.put_line('第二条记录存在！'); else dbms_output.put_line('第二条记录不存在！'); end if; end; 4.表达式 （1）数值表达式 数值表达式是由数值型常数,变量,函数和算术运算符组成的,可以使用的算术运算符包括+(加法),-(减法),*(乘法),/(除法)和**(乘方)等。 declare result integer; begin result:=10+3*4-20+5**2; dbms_output.put_line('运算结果是:'||to_char(result)); end; dbms_output.put_line函数输出只能是字符串,因此用to_char函数将数值型转换为字符型。 （2）字符表达式 字符表达式由字符型常数,函数和字符运算符组成,唯一可以使用的字符运算符就是连接运算符"||"。 （3）关系表达式 关系表达式由字符表达式或数值表达式与关系运算符组成,包括以下9种： <小于 >大于 = 等于(不是赋值运算符:=) like 类似于 in 在??之中 <= 小于等于 >= 大于等于 != 不等于 between 在??之间 关系型表达式运算符两边的表达式的数据类型必须一致。 （4）逻辑表达式 逻辑表达式由逻辑常数,函数和逻辑运算符组成,常见的逻辑运算符包括以下3种： NOT:逻辑非 OR:逻辑或 AND:逻辑与 运算的优先次序为NOT,AND和OR；5.数据转换函数；最常见的数据类型转换函数有以下3个：；To_char:将其他类型数据转换为字符型；To_date:将其他类型数据转换为日期型；To_number:将其他类型数据转换为数值型；declare；inuminteger;；cddate;；ccharvarchar2(5);；begin；inum:=to_num 运算的优先次序为NOT,AND和OR。 5.数据转换函数 最常见的数据类型转换函数有以下3个： To_char:将其他类型数据转换为字符型。 To_date:将其他类型数据转换为日期型 To_number:将其他类型数据转换为数值型。 declare inum integer cd date; cchar varchar2(5); begin inum := to_number('30'); cd:=to_date('2007-4-4','yyyy-mm-dd'); cchar :=to_char(30); dbms_output.put_line(inum); dbms_output.put_line(cd); dbms_output.put_line(cchar); end; 6.流程控制 （1）条件控制 if..then..end if条件控制 if..then..else..end if条件控制 if嵌套条件控制 （2）循环控制 loop..exit..end loop循环控制 loop..exit..when..end loop循环控制 while..loop..end loop循环控制 for..in..loop..end循环控制 7.事务处理 事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。例如：在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。 事务是恢复和并发控制的基本单位。 事务具有4个属性：原子性、一致性、隔离性、持续性。通常称为ACID特性。 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。 持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。 （1）commit命令 事务提交命令，为了保证数据的一致性,在内存中将为每个客户机建立工作区,客户机对数据库进行操作处理的事务都在工作区内完成,只有在输入commit命令后,工作区内的修改内容才写入到数据库上,称为物理写入,这样可以保证在任意的客户机没有物理提交修改以前,别的客户机读取的后台数据库中的数据是完整的,一致的 。 打开事务自动提交：set auto on; 关闭事务自动提交：set auto off; （2）rollback命令 rollback是事务回滚命令,在尚未提交commit命令之前,如果发现delete,insert和update等操作需要恢复的话,可以使用rollback命令回滚到上次commit时的状态。 （3）savepoint命令 保存点命令，事务通常由数条命令组成,可以将每个事务划分成若干个部分进行保存,这样每次可以回滚每个保存点,而不必回滚整个事务。语法格式如下： 创建保存点:savepoint 保存点名; 回滚保存点:rollback to 保存点名; ①向scott.emp数据表中插入一条记录 insert into emp(empno,sal) values(9000,'wang',2500); ②创建保存点 savepoint insertpoint; ③执行其他的SQL语句 insert into emp(empno,sal) values(9001,'zhang',2510); insert into emp(empno,sal) values(9002,'li',2520); ④回滚到指定的保存点"insertpoint". 8.游标 游标是将数据表中提取出来的数据,以临时表的形式存放在内存中,游标中有一个数据指rollback to insertpoint; 针,在初始状态下指向的是首记录,利用fetch语句可以移动该指针,从而对游标中的数据进行各种操作,然后将操作结果写回数据表中。 （1）定义游标： cursor 游标名 is select 语句; cursor是定义游标的关键词,select是建立游标的数据表查询命令。 declare tempsal emp.sal%type; cursor mycursor is select * from emp where sal>tempsal; begin tempsal:=800; open mycursor; end; （2）打开游标： open 游标名; 打开游标的过程有以下两个步骤: ①将符合条件的记录送入内存 ②将指针指向第一条记录 （3）提取游标数据 要提取游标中的数据,使用fetch命令,语法形式如下： fetch 游标名 into 变量名1,变量名2,??; 或 fetch 游标名 into 记录型变量名; 以下是定义cursorrecord变量是游标mycursor的记录行变量,在游标mycursor的结果中找到sal字段大于800的第一个记录,显示deptno字段的内容. declare tempsal emp.sal%type; cursor mycursor is select * from emp where sal>tempsal; cursorrecord mycursor%rowtype; begin tempsal:=800; open mycursor; fetch mycursor into cursorrecord; dbms_output.put_line(to_char(cursorrecord.deptno)); end; （4）关闭游标 close 游标名; （5）游标的属性 %isopen属性。测试游标是否打开,如果没有打开游标就使用fetch语句将提示错误。 %found属性。测试前一个fetch语句是否有值,有值将返回true,否则为false。 %notfound属性。该属性是%found属性的反逻辑,常被用于退出循环。 %rowcount属性。该属性用于返回游标的数据行数。若返回值为0,表明游标已经打开,但没有提取出数据。 （6）游标的运用：首先建立一个表employee，包含以下字段，往表里插入一些数据; ①取表里满足条件的第一条记录： ②取满足条件的所有记录需要用到循环，让游标往下移动取值: ③取满足条件的前面几条记录，循环时用到for i in 1..n loop(n自己确定 ): ④利用mycursor%rowcount确定跳出循环的时间，%rowcount的值要<=满足要求的总记录数 ⑤带参数的游标；⑥存储过程中使用游标；9.存储过程；前面编写执行的PL/SQL程序,共同的特点是没有；特点：；（1）存储过程是预编译过的，并且经优化后存储于S；（2）存储过程的代码直接存放于数据库中，一般由客；用，减少了网络流量，加快了系统执行速度，例如在进；（3）使用存储过程可以减少SQL注入式攻击，提高；（4）在同时进行主、从表及多表间的数据维护及有效； ⑤带参数的游标 ⑥存储过程中使用游标 9.存储过程 前面编写执行的PL/SQL程序,共同的特点是没有名称,只能存储为文件,然后通过执行文件的方式执行,因此称为无名块。与此对应的是在PL/SQL中也引入了高级程序设计的一些概念,其中最重要的就是过程。 特点： （1）存储过程是预编译过的，并且经优化后存储于SQL内存中，使用时无需再次编译，提高了工作效率； （2）存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调 用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多； （3）使用存储过程可以减少SQL注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据; （4）在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制； （5）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可； （6）但使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。 完整的过程结构如下: create or replace procedure 过程名 as 声明语句段; begin 执行语句段; exception 异常处理语句段; end; 过程是有名称的程序块,as关键词代替了无名块的declare。 以下是根据例子说明各种存储过程的调用： create table student( sno number(6) primary key, sname varchar2(25) constraint s_sname_nn not null, ssex varchar2(6), sage number(6), sdept varchar2(25) ) create sequence stu_seq; insert into student values(stu_seq.nextval,'dengwenjun','male',24,'Information'); select * from student --1.无参数存储过程 create or replace procedure stu_pro p_name student.sname%type; --p_name是申明的一个变量，它的类型是与表student的sname字段类型一致 begin select sname into p_name from student where sno =28; dbms_output.put_line(p_name); end; -- 不能忘记from table(student) call stu_pro(); --无论是有参数还是无参数，调用时"()"是不可少的 --2.仅有输入参数的过程 create or replace procedure stu_pro1(pno in student.sno%type) as p_name student.sname%type; begin select sname into p_name from student where sno =pno; dbms_output.put_line(p_name); end; --in表示pno为输入参数，pno是参数名称 call stu_pro1(30); --pno是30，sql语句的意思是把表中sno=30的sname赋给p_name --3.仅有输出参数的存储过程 create or replace procedure stu_pro2(pname out student.sname%type) as begin select sname into pname from student where sno = 31; dbms_output.put_line(pname); end; --这里有一个输出参数，as后不用再申明了 --这里调用存储过程时不能直接用call来调用，call stu_pro2(?)是错误的，针对有输入参数的。这种情况的调用将在下面oracle函数调用中说明 --4.同时有输入/输出参数的存储过程 create or replace procedure stu_pro3 (pno in student.sno%type,pname out student.sname%type) begin select sname into pname from student where sno=pno; dbms_output.put_line(pname); end; --这里的存储过程也不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明 --Oracle函数调用存储过程 --1.对无参数过程的调用 --函数 create or replace function get_pname return varchar2 --is相当于过程中的as，申明返回类型return varchar2 is pname varchar2(25); begin stu_pro; --调用存储过程stu_pro select sname into pname from student where sno=32; return pname; --返回结果 end; --调用函数 declare begin dbms_output.put_line('在PL/SQL中打印的结果:'||get_pname); end; --2.对有输入参数过程的调用 create or replace function get_pname1(pno in student.sno%type) return varchar2 is pname varchar2(25); begin stu_pro1(pno); --调用有参数的存储过程，直接写入输入参数名 select sname into pname from student where sno=pno; return pname; end; --调用函数 declare begin dbms_output.put_line('在PL/SQL中打印的结果:'||get_pname1(25)); end; --3.对有输出参数过程的调用 create or replace function get_pname2(pname out varchar2) return varchar2 is begin stu_pro2(pname); return pname; end; --调用函数 declare p_name student.sname%type; begin dbms_output.put_line('PL/SQL中打印的结果:'||get_pname2(p_name)); end; --4.对有输入/输出参数过程的调用 create or replace function get_pname3(pno in number,pname out varchar2)return varchar2 ----这里pno,pname的类型不能选定范围 is begin stu_pro3(pno,pname); return pname; end; --调用函数 declare pname varchar2(25); begin dbms_output.put_line('结果是：'||get_pname3(25,pname)); end; --注意最后的end; 10.java中调用存储过程或函数；①调用函数；CallableStatementcs=con.；第一个？表示返回的值，后面的？可以是输入参数，也；第一个？是返回参数，所以必须有语句：；connection.registerOutPa；后面的？如果是输出参数，应该也加上registe；connection.registerOutPa；最后打印输出的结果：；S 10. java中调用存储过程或函数 ①调用函数 CallableStatement cs=con.prepareCall("{?=call get_pname(?,?,?)}"); 第一个？表示返回的值，后面的？可以是输入参数，也可以是输出参数。 第一个？是返回参数，所以必须有语句： connection.registerOutParameter(1,Types.VARCHAR);（Types.varchar是类型） 后面的？如果是输出参数，应该也加上registerOutParameter语句： connection.registerOutParameter(2,Types.VARCHAR);（2是第2个占位符，Types.varchar是类型） 最后打印输出的结果： System.out.println(cs.getString(1)); （1是对应输出参数，第一个输出参数） ②调用存储过程 CallableStatement cs=con.prepareCall("{call stu_pro(?,?)}");（与函数的区别是：没有？=） java程序中要打印调用过程获得的值，需调用有输出参数的存储过程，用法和调用函数一样。 ③简单例子 11.触发器 触发器是指隐含执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert，update，delete语句，而触发操作实际上就是一个pl/sql块。 功能： 允许/限制对表的修改 自动生成派生列，例如自增字段 强制数据一致性 提供审计和日志记录 防止无效的事务处理 启用复杂的业务逻辑 语法： ①Create or replace 其中create是创建的意思，是必须的，replace是替换的意思，是可选项。Replace的作用是，当数据库中存在相同名称的触发器时，就替换掉以前的触发器。 ②after/before 是表示在事件之后或之前执行触发器 ③insert/update/delete 触发器监控的事件 ④on 触发器监控的表 ⑤for each row 行级触发器，每操作一行就触发一次，例如：delete from table 删除100行，就触发100次(一行一次)。 for each statement 语句级触发器，例如： delete from table 删除100行，则只触发一次(一条语句一次)。 ⑥Referencing 设置参照变量， old_value 行变量，表示事件发生前旧数据所在的行，可通过:old_value.字段名 来调用相对应的字段值； new_value 行变量，表示事件发生后新数据所在的行，可通过:new_value.字段名 来调用相对应的字段值； 触发器类型： (1)语句触发器 (2)行触发器 (3)INSTEAD OF触发器 (4)系统条件触发器 (5)用户事件触发器 利用例子实现触发器： （1）语句触发器。针对update，delete，insert等操作，执行一条语句触发器执行一次，与更新多少条数据无关。 首先创建一个表student： 然后创建另一张日志表记录用户操作时间student_log： 在student表上创建语句触发器stu_trig1，通过触发器记录操作的用户以及时间： 向student表中插入一条数据： insert into student values('1','dwj','23','male'); 日志表student_log中多出一条记录：当前用户名，当前时间 。 自己多加入几条记录，利用一条语句更新多条记录，发现日志student_log中只增加一条记录，这就是语句级触发器 这里没有for each row和for each statement，默认的是for each statement(语句级触发器)。 （2）行级触发器。语句中包含for each row子句；在before...for each row中，用户可以引用受到影响的行值。 定义一个触发器stu_trig2，当插入或更新一个学生的学号信息时，如果学号不等于8，就将其年龄设置为20。 这里执行插入语句： 1. insert into student values('8','xxx','female'); 2. insert into student values('9','zzz','male'); 3. update student set ... where stu_no=? 执行上面第1条语句时，由于stu_no=8,没有触动触发器stu_trig2，这条数据正常插入；执行第二条语句时，stu_no<>8,触动触发器stu_trig2,则这条数据对应的age字段变为20，这时数据库中插入的是'20'而不是'23'。 注意： referencing子句：执行DML语句之前的值的默认名称是:old,之后的值是:new insert操作只有:new delete操作只有:old update操作两者都有 referencing子句只是将new和old重命名为new_value和old_value，目的是避免混淆。 （3）instead of 触发器 主要被用于更新那些没有办法通过正常方法更新的视图。例如，通常不能再一个基于连接的视图上进行delete操作。基于多个表的视图必须使用instead of触发器，因为不用触发器不能正常删除、更新、插入。不能在带有with check option定义的视图中创建instead of 触发器。 简单例子： 通过对学生表（stu）和成绩表（cour）视图sc_view的更新，实现对数据表的插入操作。 （4）用户事件触发器；用户事件触发器（DDL触发器），当发出creat；（5）系统事件触发器；当startup、shutdown、logon、；12.异常处理；定义异常处理；Declare异常名exception；；触发异常处理：；Raise异常名；；处理异常：；Exception；When异常名1then；异常处理语句段1；；When异常名1then；异常 （4）用户事件触发器 用户事件触发器（DDL触发器），当发出create、drop、alter、truncate命令时会触发已定义好的DDL触发器，这种触发器可以用来监控某个用户或整个数据库的所有对象的结构变化。 （5）系统事件触发器 当startup、shutdown、logon、logoff数据库时就会触发系统事件触发器，这种触发器可以用来监控数据库是什么时候关闭/打开，或用户logon/logoff情况。 12. 异常处理 定义异常处理 Declare 异常名 exception； 触发异常处理 Raise 异常名 处理异常： Exception When 异常名1 then 异常处理语句段1； When 异常名1 then 异常处理语句段2； 下面的PL/SQL程序包含了完整的异常处理定义,触发,处理的过程.定义名为salaryerror的异常,在emp数据表中查找empno=28的记录,将其值放入变量tempsal中,判断tempsal值若不在900和2600之间,说明该员工的薪水有问题,将激活异常处理,提示信息。 --exception实例 declare -- 声明一个异常 salary_exception exception; v_salary number(10); begin select salary into v_salary from departments where department_name='RMO'; if v_salary>2600 then raise salary_exception; end if; exception when salary_exception then dbms_output.put_line('对不起，薪水异常了！'); end; declare salaryerror exception; tempsal emp.sal%type; begin select sal into tempsal from emp where empno=7566; if tempsal>2600 then raise salaryerror; end if; exception when salaryerror then dbms_output.put_line('薪水超出范围'); end; 五、数据库范式 第一范式：数据库中的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中不能有重复的属性或某个属性不能有多个值。 第二范式：必须先满足第一范式，要求数据库表中的每个实例或行必须可以被委以的区分。就是完全依赖于主键，消除非主属性对主码的部分函数依赖。 完全依赖：指不能存在仅依赖主关键字一部分的属性，如果存在，这个属性和主关键字的这部分应该分离出形成一个新的实体。 第三范式：不依赖其他非主属性（消除传递依赖） 候选码：一个或一组可以唯一标识一条记录且不含多条属性的属性 主属性：所有包含在候选码里的属性 非主属性：不包含在侯选码的属性 函数依赖：表中属性x的值可以唯一确定y的值，则说：x确定y，或y依赖于x(记作x->y)。 传递依赖：x->y,y->z，则可以说z传递依赖于x 多值依赖：一个属性的值可以确定一组属性。（函数依赖是一种特殊的多值依赖） 六、视图 视图是虚表，是从一个或几个基本表（或视图）中导出的表，在系统的数据字典中仅存放了视图的定义，不存放视图对应的数据。 视图是原始数据库数据的一种变换，是查看表中数据的另外一种方式。可以将视图看成是一个移动的窗口，通过它可以看到感兴趣的数据。视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。 视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。 视图看上去非常像数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些视图可以修改对应的基表，有些则不能（仅仅能查询）。 创建视图语句： create view 视图的名字 as select * from 表名; --写需要用到的SQL查询语句 有两张表，department（系表），student（学生表），两表的字段和内容如下： 我每次都只想取学生的姓名以及所在的系名，为方便以后查询，直接创建一个视图： create view stu_department_view as select s_name,d_name from student,department where student.d_id =department.d_id 查看视图： select * from stu_department_view 有条件的查看： select * from stu_department_view where d_name='信息' 删除视图： drop view stu_department_view 七、索引 在日常生活中，我们经常会用到索引，如图书的目录、词典的索引等。利用索引，我们可以很快地找到需要找的东西。 在对数据库进行操作时，用到索引可以提高数据存取的性能及操作的速度，从而使用户能够较快地查询并准确地得到希望的数据。 1.使用索引的优点： (1) 创建唯一索引，可以保证表中的数据记录不重复。 (2) 加快数据检索速度。 (3) 加速表与表之间的连接。 (4) 在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询 中分组和排序的时间。 (5) 可以在检索数据的过程中使用优化隐藏器，从而提高系统的性能。 2.创建索引的原则 (1) 主键列上一定要建立索引。 (2) 在连接中频繁使用的列，比如外键。 (3) 在频繁查询的列上最好建立索引。 (4) 对于text、image和bit数据类型的列不要建立索引。 (5) 对于具有重复值较多的列不要建立索引。 3.索引并非越多越好 (1) 创建索引要花费时间并占用存储空间，聚集索引更严重。 (2) 维护索引也要花费时间。 (3) 当对表进行修改时，需要维护索引，插入、更新和删除的数据越多，维护的开销就越大。 使用CREATE INDEX语句创建索引的语法格式如下： CREATE[UNIQUE][CLUSTERED；[WITH；(PAD_INDEX={ON|OFF}；|FILLFACTOR=filefactor；|IGNORE_DUP_KEY={ON|OFF}；|DROP_EXISTING={ON|OFF}；|STATISTICS_NORECOMPUTE=；|SORT_IN_TEMPDB={ON|OFF}；[ONfile CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [,…n ] ) [ WITH ( PAD_INDEX={ ON | OFF } | FILLFACTOR=filefactor | IGNORE_DUP_KEY={ ON | OFF } | DROP_EXISTING={ ON | OFF } | STATISTICS_NORECOMPUTE={ ON | OFF } | SORT_IN_TEMPDB={ ON | OFF } ) [ ON filegroup ] 其中： UNIQUE：为表或视图创建唯一索引，即不允许两行具有相同的索引键值。省略UNIQUE时，创建的索引是非唯一索引。 CLUSTERED：指定创建的索引为聚集索引。 NOCLUSTERED：指定创建的索引为非聚集索引。省略CLUSTERED | NOCLUSTERED，则建立的是非聚集索引。 index_name：指定创建的索引的名称。 table | view：用于创建索引的表或视图的名称。 column：索引所基于的一列或多列。指定两个或多个列名，可为指定列的组合值创建组合索引。一个组合索引中最多可组合16列。组合索引键中的所有列必须在同一个表或视图中。 ASC | DESC：确定特定索引列的升序或降序排序方向。默认值为ASC。 PAD_INDEX：指定填充索引的内部节点的行数至少应大于等于两行。PAD_INDEX选项只有在FILLFACTOR选项指定后才起作用，因为PAD_INDEX使用与FILLFACTOR相同的百分比。 FILLFACTOR=filefactor：指定一个百分比，表示在索引创建或重新生成过程中数据库引擎应使每个索引页的叶级别达到的填充程度。fillfactor必须为介于1至100之间的整数值。默认值为0。如果fillfactor为100或0，数据库引擎将创建叶级页达到其填充容量的索引。 IGNORE_DUP_KEY：指定对唯一聚集索引或唯一非聚集索引执行多行插入操作时出现重复键值的错误响应。默认值为OFF。当为ON时，发出一条警告信息，但只有违反了唯一索引的行才会失败；为OFF时，发出错误信息，并回滚整个INSERT事务。IGNORE_DUP_KEY设置仅适用于创建或重新生成索引后发生的插入操作。 DROP_EXISTING：指定应删除并重新生成已命名的先前存在的聚集或非聚集索引。默认值为OFF。当为ON时，删除并重新生成现有索引。指定的索引名称必须与当前的现有索引相同；但可以修改索引定义；当为OFF时，如果指定的索引名已存在，则会显示一条错误。使用DROP_EXESTING不能更改索引类型。 STATISTICS_NORECOMPUTE：指定是否重新计算分发统计信息。默认值为OFF。当为ON时，不会自动重新计算过时的统计信息；为OFF时，启用统计信息自动更新功能。 SORT_IN_TEMPDB：指定是否在tempdb中存储临时排序结果。默认值为OFF。当为ON时，在tempdb中存储用于生成索引的中间排序结果。为OFF时，中间排序结果与索引存储在同一个数据库中。 ON filegroup：为指定文件组创建指定索引。如果未指定位置且表或视图尚未分区，则索引将与基本表或视图使用相同的文件组。该文件组必须已存在。 创建索引 Create index stu_index1 on student(sname); Create index stu_index2 on student(sid,sname); Create index stu_index3 on student(sname,sid); 一般情况下，需要经常访问的字段放在组合字段的前面。 删除索引 Drop index stu_index1； （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |