数据库重点知识掌握.doc

1：pl/sql是什么
一组sql语句的组合（dml/ddl/transaction）,以便完成单条sql语句无法解决的复杂的数据库操作。
ddl:create/dorp/alter对数据库的对象进行定义删除，修改等操作的sql语句。
Dml：insert。Update。select，delete 对数据库数据进行操作的sql语句。
Transaction：数据库事务管理语句的控制sql语句。
Pl/sql本事是使用perl语句为基础的一种语法格式。
2:oracle 数据类型：varchar2，nvarchar2,number,char,
Date,blob,clob,Boolean,long,
3:
Declare
Begin
End;
Create or replace producer 过程名(参数 参数类型 数据类型) is
变量的定义语句
Begin
Pl/sql语句
End；
4：语句的语法：
1：分支语句写法：
If
Case、when（3种用法），并且这种写法可用于sql语句中。
2：循环结构：
Loop
While
For
基本与java循环类似，但是要注意：loop必须要关闭
3：exception，异常
在程序的结尾使用exception标记来捕获程序运行期间所发生的异常情况，
并且利用when来确定异常所发生的具体内容，如果需要获取发生的异常的提示
需要使用sqlerrm进行输出（返回，打印都可以）。
4：赋值：
：=来进行
5:判断空值或者非空使用is null/is not null
5：游标：
隐式游标，其实就是into语句，但是要注意其中有几个隐含的rowcount，found，isopen。
显示游标：由开发人员自定义的一种游标格式，其中分为两种，预定义游标，动态游标
预定义游标：
sql语句固定的情况下，可以使用这种游标格式，并且在游标名的
的后面可以进行参数的定义。
使用的时候，首先必须open并且如果有参数的情况下，传入参数。
在loop后面使用fetch语句循环每一行获取其中的值into到变量自己中。
并且在使用过程中要注明退出条件：exit when 游标名%notfount.
最后。在使用完游标之后，记得close，循环结束，end loop;
动态游标：
Sql语句不固定的情况下，例如：需要根据传入的参数进行判断是否添加 查询从句的时候，就需要用到动态游标。并且以字符串的形式定义sql语句
使用的时候，必须进行open for sql语句来打开游标。
后面的使用过程和预定义游标的使用过程是一致的。

6：存储过程：
存储过程其实就是将语句块写入数据的一种技术。不需要再以文件的方式进行存储。使用的时候通过语句块的形式进行调用。
其语法和语句块类似，不过可以传入参数，以提高的程序的灵活性。并且便于调试，
特别注意，存储过程的调试技术需要掌握。
其参数可以定义为输入和输出参数，输入参数在程序运行过程中是不可变更的。这个与java不同。输出参数如果业务不要求，可以不予理会，并且，可以有多个返回值，相对灵活。
create or replace procedure huiguPro(nInparam1 in number,sOutParam2 out varchar2)
is
--参数，类型定义部分
nParam1 number(10);
begin
Dbms_Output.put_line('hello world');
end;
7：函数：
函数的写法与存储过程类似，
但是其参数没有in/out的区别，直接全部都是in类型，但是其具有明确的定义返回值，但返回值的个数只限定一个，不如存储过程灵活。
create or replace function huigufun(nInparam1 number) return varchar2
is
--参数，类型定义部分
nParam1 number(10);
begin
Dbms_Output.put_line('hello world');
return('hello world');
end;

8：触发器：
触发器是一种特殊的存储过程，它的作用是让开发人员可以控制数据库的单点动作（包括dml和ddl操作）。
主要用法是dml操作的用法，可以极大程度上解决数据冗余时的更新问题。
主要语法是，写定义语句create or replace trigger 触发器名称
并且使用before或者after定义此触发器是再对表的操作之前执行还是对表操作之
后执行。然后，使用insert/update/delete来定义此触发器是在被触发的表的插入/修改/
删除动作的时候执行。最后，使用on关键字来描述本触发器是作用于哪张表。接下来
需要使用fro each row来定义是行级触发器还是语句级触发器，如果不写这句就是语句级触发器，否则就是行级触发器。
使用declare来定义其中需要使用的变量，接下来的操作与存储过程一致，但是要注意几种内置的变量，:old/:new.和操作状态描述符：inserting/updating/deleting,表述当前动作是对作用表的插入动作还是更新动作还是删除动作。

create or replace trigger scoreTri

after insert or update or delete on score
for each row
declare
nAllScore number(10, 2);
begin
--查询出现在该学生的总成绩，
if INSERTING then
select t.allscore
into nAllScore
from student t
where t.stuid = :new.stuid;
else
select t.allscore
into nAllScore
from student t
where t.stuid = :old.stuid;
end if;

if INSERTING then
nAllScore := nAllScore + :new.score;

end if;

if updating then

--将现有的成绩+新的修改过的单科成绩-旧的修改之前的单科成绩
nAllScore := nAllScore + :new.score - :old.score;
end if;

if deleting then

nAllScore := nAllScore - :old.score;
end if;

update student t set t.allscore = nAllScore where t.stuid = :old.stuid;
end scoreTri;

9：包
将存储过程和函数进行按功能或者按类型封装在一个整体之内，并且隐藏其具体实现。其主要作用体现在封装。
其主要内容有包头。和包体两个部分组成。包头部分作用是公共变量的定义，自定义数据类型的定义，常量的定义，也包括预定义游标的定义。还有函数名称/存储过程名称。参数返回值的定义。
包头部分如下：
create or replace package scorepackages is

--定义一个常量
staticParam constant varchar2(20):='这是常量';
/****************************************************************
packageProcedure:此存储过程是用于包的测试的
author：baojl
date：2011-10-13
param：
nInParam1 测试输入参数
sOutParam2 测试输出参数
modify：
2011-10-13 baojl 修改了什么内容
*****************************************************************/
procedure packageProcedure(nInParam1 in number, sOutParam2 out varchar2);

/***************************************************************
packageFunction:此函数是用于包的测试函数
author:baojl
date: 2011-10-13
param:
nInParam1 测试输入参数
return
返回值测试
modify：
2011-10-13 baojl 修改了什么内容

****************************************************************/
function packageFunction(nInParam1 number) return varchar2;

end scorepackages;

包头的定义必须要使用包体来实现其功能，其中，其名称写法与包头部分类似，只不过将 package换成 package body。接下来的关于存储过程的实现和函数的实现与去掉定义部分的单个存储过程和函数的实现方式一致。
create or replace package body scorepackages is
/****************************************************************
packageProcedure:此存储过程是用于包的测试的
author：baojl
date：2011-10-13
param：
nInParam1 测试输入参数
sOutParam2 测试输出参数
modify：
2011-10-13 baojl 修改了什么内容
*****************************************************************/
procedure packageProcedure(nInParam1 in number, sOutParam2 out varchar2) is
nParam1 number(5);
begin
sOutParam2 := '输入参数有值：';
nParam1 := nInParam1;
if nInParam1 is null then
nParam1 := 0;
sOutParam2 := '输入参数没有值,取默认值：';
end if;
Dbms_Output.put_line(sOutParam2 || nParam1||staticParam);
end packageProcedure;

/***************************************
packageFunction:此函数是用于包的测试函数
author:baojl
date: 2011-10-13
param:
nInParam1 测试输入参数
return
返回值测试
modify：
2011-10-13 baojl 修改了什么内容

****************************************************************/
function packageFunction(nInParam1 number) return varchar2 is
nParam1 number(10);
sMessage varchar2(1000);
begin
sMessage := '输入值不为空';
nParam1 := nInParam1;
if nParam1 is null then
nParam1 := 0;
sMessage := '输入值为空，则取默认值：';
end if;
return(sMessage || nParam1);
end packageFunction;

end scorepackages;

用法：
其用法与存储过程调用一致，但需要在过程名称前面加上包名。

create or replace procedure showCursor(outSParam3 out varchar2) is

testProInParam number(10);
testProOutParam2 varchar2(100);
begin

--存储过程的调用
testProInParam := 111;
scorepackages.packageProcedure(ninparam1 => testProInParam,
soutparam2 => testProOutParam2);

outSParam3 := testProOutParam2;
end;

函数的用法有两种方式，见例子：
create or replace procedure showFunction(outSParam3 out varchar2) is

testProInParam number(10);
testProOutParam2 varchar2(100);
begin

--函数的调用有两种方式：
testProInParam := 111;
-- 第一种，可以将函数的返回值返回给某个变量
outSParam3:= scorepackages.packageFunction(ninparam1 => testProInParam);/**/
--第二种：直接使用select语句进行调用将返回值into到一个变量中
select scorepackages.packageFunction(111) into outSParam3 from dual;

end;

10：序列：
序列是一种解决字段需要自增的数据类型。设置的过程中，需要设置序列名称，最小值，最大值，起始位置。如下所示：
create sequence SEQ_STUID—-名称
minvalue 1500—-最小值
maxvalue 9999—-最大值
start with 1540—-起始位置
increment by 1—-步长
cache 20;--1长度

其用法就是：序列名称.nextval取下一个值
select seq_stuid.nextval from dual;