存储过程 number 类型 java 传 number 空值_2009-12-08传智播客 数据库——Oracle触发器/存储过程/视图等 - 充满生活的味道！ - BlogJava...

一、Merge语句

根据条件在表中执行修改或插入数据的功能，如果插入的数据行在目的表中存在就执行UPDATE，如果是不存在则执行INSERT:

-避免了单独的修改

-增强性能和应用性

它的功能就是将，一个表中的数据加入到另一个表中。

格式：

MERGEINTO目标表

USING源表

ON(目标表.FIELD=源表.FIELD)

WHENMATCHEDTHEN

UPDATESET

目标表.FIELD1=源表.FIELD1,

目标表.FIELD2=源表.FIELD2,

...

WHENNOTMATCHEDTHEN

INSERT(目标表.FIELD1,目标表.FIELD2,...)

VALUES(FV1,FV2,...)

如果不需要，insert() vale()。可以不写WHEN

NOT MATCHED THEN这

一部分。

例，在“EMP”表

中添加一个“LOC”字段，将“DEPT”表中的“LOC”字

段值根据员工的部门编号拷贝到“EMP”表中的“LOC”字段。如果有部门编号不存在的员工，则什么也不做。

ALTERTABLEEMPADDLOCVARCHAR2(20)

MERGEINTOEMP E

USINGDEPT D

ON(E.DEPTNO=D.DEPTNO)

WHENMATCHEDTHEN

UPDATESET

E.LOC=D.LOC

数据库事务处理，在此就不复习了。

二、数据库的主要对象

1.数据

库的对象：

对象名称

描述

表

基本的数据存储对象，以行和列的形式存在，列是字段，行是记录

数据字典

也就是系统表，存放数据库相关信息的表

约束条件

执行数据校验，保证了数据完整性的

视图

一个或者多个表数据的逻辑显示

索引

用于提高查询的性能

同义词

对象的别名

2.Oracle中常

用字段类型：

数据类型

说明

char(size)

定长字符，≤2000个字节

varchar2(size)

变长字符，≤4000个字节

date

日期数据，默认的格式是dd-mm-yy：如11-6月-06

timestamp

日期数据，要比date数据更确切，包含时分秒。

integer

整型数据

number[(s,p)]

数字型，可存放实型和整型,精度(p)和范

围(s)

long

可变字符，≤2GB个字符

float,real

是NUMBER的子类型

blob

存放图形、声音和影像，大二进制对象，≤4GB

clob

存放大文本文件，≤4GB

3.使用

子查询创建表：

以前直接使用Create Tabel tab_name…创建表，如果想创建表的同时，将另一张表中的某些数据导入到新表怎

么办？此时，子查询派上了用场：

例，创建一个“EMPNEW”

表，它与“EMP”表具有相同的字段和值：

CREATETABLEEMPNEW

ASSELECT*FROMEMPWHEREDEPTNOISNOTNULLANDDEPTNO>0

例，创建一个“EMPNEW2”表，包含“员工编号”和“员工

姓名”两个字段。它只需要“EMP”表中的“EMPNO”和“ENAME”

字段和值：

CREATETABLEEMPNEW2(

"员工编号",

"员工姓名"

)ASSELECTEMPNO,ENAMEFROMEMP

--这两条语句实现的功能相同

CREATETABLEEMPNEW2

ASSELECTEMPNO "员工编号",ENAME "员工姓名"FROMEMP

三、对表的字段和表的操作

1.在表中增加字段：

例，在“EMP2”表中添加一个“EMAIL”字段：

ALTERTABLEEMPNEW2ADDEMAILVARCHAR2(30)

2.在表中修改字段：

例，修改“EMPNEW2”表中字段“EMAIL”的默认值为：

ALTERTABLEEMPNEW2MODIFYEMAILVARCHAR2(30)DEFAULT'NULL@SQL.COM'

不是任

何情况都可以修改的，当字段只包含空值时，

类型、

大小都可以修改，否则修改可能不能成功。

3.在表中删除字段：

例，删除“EMPNEW2”表中的“EMAIL”字段：

ALTERTABLEEMPNEW2DROPCOLUMNEMAIL

删除大

表中的字段将需要比较长的时间。

4.删除表的内容：

例，删除“EMPNEW2”表中的内容：

TRUNCATETABLEEMPNEW2

与delete语句相比：

Truncate语句清空表中所有的内容，而delete可以根据where条件选择性删除数据。

Truncate是DDL语句，如果在事件中使用它，它是不可以回滚的。

Truncate释放表空间，但delete不会。

5.删除表：

例，删除表“EMPNEW2”：

DROPTABLEEMPNEW2

1、表中所有数据将被删除

2、没有完成的事务被提交

3、所有相关的索引被删除

4、这个删除操作不能回滚

6.修改对象名称：

例，：将表“EMPNEW2”名称修改为“EMPTWO”：

RENAMEEMPNEW2TOEMPTWO

使用RENAME语句改变对象名称，可以修改表、视图、序列或者同义词的名称，必须

是对象的所有者。

四、Oracle数据

库中的表

1.查询数

据字典：

例，查看被用户拥有有的表(我使用的是Oracle自带的SCOTT用户)：

SELECTTABLE_NAMEFROMUSER_TABLES

例，查看用户拥有的所有对象类型：

SELECTDISTINCTOBJECT_TYPEFROMUSER_OBJECTS

例，查看所有用户拥有的表(必须是DBA用户

登录)：

SELECT*FROMDBA_TABLES

五、约束

1.什么是约束：

约束是

在表上强制执行的数据校验规则.

当表中

数据有相互依赖性时,可以保护相关的数据不被删除.

Oracle支持下面五类完整性约束:

1、NOT NULL非空

2、UNIQUE Key唯一键

3、PRIMARY KEY主键

4、FOREIGN KEY外键

5、CHECK检察

2.非空约束：

约束字

段值不能为空。

与其他

约束相比，非空约束只能定义的字段级。

例，创

建表“USERTABLE”并设置“NAME”字段非空：

--非空约束

CREATETABLEUSERTABLE(

IDINT,

NAMEVARCHAR2(10)NOTNULL--字段级

)

--或：ALTER TABLE USERTABLE MODIFY NAME NOT NULL—表外级

--查看用户的约束

SELECT*FROMUSER_CONSTRAINTS

--查看表的约束

SELECT*FROMUSER_CONSTRAINTSWHERETABLE_NAME='EMP'

3.唯一性约束：

约束字

段不准出现重复的记录，但可以出现一个或多个空值。

例，创建表“USERTABLE”并设置“EMAIL”字段唯一：

--唯一性约束

CREATETABLEUSERTABLE(

IDINT,

NAMEVARCHAR(10)NOTNULL,

EMAILVARCHAR(30)UNIQUE--字段级

--或：CONSTRAINT

EMAIL_UN1 UNIQUE(EMAIL)--表级

)

--或：ALTER TABLE

USERTABLE ADD CONSTRAINT EMAIL_UN1 UNIQUE(EMAIL)--表外级

注意：

这种可以在表外定义的约束，一般都在表外定义。这样易于调整和管理。

4.主键盘约束：

主键从

功能上看相当于非空且唯一

一个表

中只允许一个主键

主键是

表中能够唯一确定一个行数据的字段

主键字

段可以是单字段或者是多字段的组合

例，创

建表“USERTABLE”并设置“ID”字段为主键：

--主键约束

CREATETABLEUSERTABLE(

IDINTPRIMARYKEY,--字段级

NAMEVARCHAR2(10)NOTNULL,

EMAILVARCHAR(30)UNIQUE

--或：CONSTRAINT ID_PRI

PRIMARY KEY(ID)--表级

)

--或：ALTER TABLE

USERTABLE ADD CONSTRAINT ID_PRI PRIMARY KEY(ID)--表外级

注意：

这种可以在表外定义的约束，一般都在表外定义。这样易于调整和管理。

5.外键约束：

外键是

构建于一个表的两个字段或者两个表的两个字段之间的关系

外键确保了相关的两个字段的关系：

子表外键列的值

必须在主表参照列值的范围内，或者为空

主表主

键值被子表参照时，主表记录不允许被删除

外键约

束条件参照的是主表的一个或者多个字段的值，通常被外键参照的是主表的主键或者唯一键

例，创建一个“ROLETABLE”角色表，在“USERTABLE”表中添加一个外键，指向角色ID：

--外键约束

CREATETABLEROLETABLE(

IDINTPRIMARYKEY,

MODIFYTABNUMBER(4)

)

--

CREATETABLEUSERTABLE(

IDNUMBER(4)PRIMARYKEY,

NAMEVARCHAR2(10)NOTNULL,

EMAILVARCHAR(30)UNIQUE,

ROLE_IDNUMBER(4),

CONSTRAINTFK_ROLFOREIGNKEY(ROLE_ID)REFERENCESROLETABLE(ID)--表级

)

--或：ALTER TABLE

USERTABLE ADD CONSTRAINT FK_ROL FOREIGN KEY(ROLE_ID) REFERENCES

ROLETABLE(ID)--表外级

6.CHECK约束：

Check约束条件是一种比较特殊的约束条件，通过check定义，

强制定义在字段

上的每一记录都要满足check中定义的条件。

在check中定义检查的条件表达式，进入表中的数据必须符合。

check中设置的条件

条件表达式不允

许使用：

1、SYSDATE, USER等函数

2、参照其他记录的值

例，为“EMP”表中的“SAL”字段添加约束：

ALTERTABLEEMPADDCONSTRAINTCK_SALCHECK(SAL>6000)

7.删除约束：

删除约

束条件对于表和数据不会产生影响

--删除“EMP”表中的“SAL”字段的CHECK约束

ALTERTABLEEMPDROPCONSTRAINTCK_SAL

--删除“ROLETABLE”中

的“ID”字段的主键约束，同时“USERTABLE”

的外键约束也被删除

ALTERTABLEROLETABLEDROPPRIMARYKEYCASCADE

六、索引

1.什么时索引：

方案(schema)中的一个数据库对象

在Oracle数据库中用来加速对表的查询

通过使用快速路

径访问方法快速定位数据,减少了磁盘的I/O

与表独立存放

由Oracle数据库自动维护

2.索引的创建方法：

自动：

当在表上定义一个PRIMARY KEY或者UNIQUE约束条件时，Oracle数据库自动创建一个对应的唯一索引。

手动：用户可以

创建索引以加速查询。

3.创建索引：

例，创

建一个“USERTABLE”，为“ID”字段创建索引：

--创建索引

CREATETABLEUSERTABLE(

IDNUMBER(4),

NAMEVARCHAR2(10)

)

CREATEINDEXUSERTABLE_INDEXONUSERTABLE(ID)

4.删除索引：

例，删

除“USERTABLE”中“ID”字段的索引：

--删除索引

DROPINDEXUSERTABLE_INDEX

删除索

引，必须拥有索引或者拥有DROP ANY

INDEX权限。

七、视图

1.什么是视图：

视图也

就是虚表，实际上视图就是一个命名的查询，用于改变基表数据的显示。

可以限制对数据的访问

可以使复杂的查

询变的简单

提供了

数据的独立性

提供了

对相同数据的不同显示

2.创建视图：

例，查

询“EMP”表中薪金大于8000的员工。

SELECT*FROMEMPWHERESAL>8000

如果上

面的查询语句是我们经常要使用到的呢？每次都要编写这个语句吗？视图在此发挥了作用：

--创建视图

CREATEORREPLACEVIEWSAL8000

ASSELECT*FROMEMPWHERESAL>8000

3.从视图中查询数据：

--从视图中查询数据

SELECT*FROMSAL8000

就像是

定义了一个宏！

如果创

建视图“SELECT*FROM…”中的“*”，是某几个字段。则查询视图中也仅能查询创建视图时指定的字段。

查询视图结构，

在命令窗口中输入：DESC视图名称

4.修改视图：

例，修

改上面创建的视图“SAL8000”，为每个列指定列名：

--修改视图

CREATEORREPLACEVIEWSAL8000

ASSELECTEMPNO工号,ENAME姓名FROMEMPWHERESAL>8000

这视图

应该是可以被覆盖的，没发现这里有修改的关键字。

5.创建复杂视图：

例，创

建一个“EMP”表与“DEPT”表能过“DEPTNO”

字段关联的视图：(联表视图)

--创建复杂视图

CREATEORREPLACEVIEWEMPANDDEPT

ASSELECTE.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC

FROMEMP E,DEPT D

WHEREE.DEPTNO=D.DEPTNO

6.禁止视图进行DML操作：

例，修

改上边的视图，使其只能查询，不能进行DML操作：

--使用WITH READ ONLY禁

止视图进行DML操作

CREATEORREPLACEVIEWEMPANDDEPT

ASSELECTE.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC

FROMEMP E,DEPT D

WHEREE.DEPTNO=D.DEPTNO

WITHREADONLY

7.删除视图：

例，删

除上面创建的“DEMANDDEPT”视图：

--删除视图

DROPVIEWEMPANDDEPT

8.行内视图：

行内视

图是在SQL语句中使用的一个带有别名的子查询.

在主查询FROM子句中的子查询就是行内视图.

行内视图不是数

据库的对象,所以不需要显式的创建.

八、TOP-N分析

1．什么是TOP-N：

使用Top-N分析法可以得到某列上最什么的前n个值.例如:

薪水最高的前5个员工?

人员最多的3个部门?

最大的几个值和

最小的几个值都可以通过Top-N分析的方法得到.

2.使用TOP-N：

例，查

询“EMP”表中工资最高的前3位员工：

--TON-N分析

SELECTEMPNO,ENAME,JOB,SAL

FROM(

SELECTROWNUM,EMPNO,ENAME,JOB,SALFROMEMPORDERBYSALDESC

)WHEREROWNUM<4

1、使用了rownum这个伪列，这个伪列将会返回行号，可以作为返回记录的序列号显示。

2、在from后面使用了子查询，这是标准的行内视图的使用。

3、在主查询中通过where条件中的rownum伪

列定义过滤条件，只返回最什么的前几行数据。

3.数据库中的记录分布技术：

正是使

用TOP-N分析实现的。

例，查询工资排

行的第4-7位的员工信息：

--TOP-N分页

SELECTEMPNO,ENAME,JOB,SAL

FROM(

SELECTROWNUMMR,EMPNO,ENAME,JOB,SALFROM(

SELECTROWNUM,EMPNO,ENAME,JOB,SALFROMEMPORDERBYSALDESC

)WHEREROWNUM<7

)WHEREMR>3

上面我

们是取页面的中间部分，可以将7换为3，3换为0。这样取的就是页面首部(前三位)！

九、同义词

1.什么是

同义词：

同义词是数据库中一个对象的别名,可以简化对对象的访问。

通过使用同义词，可以：

1、简化了引用另一个用户对象的方法

2、缩短了对象名称的长度

2.创建同义词：

例，为

“EMP”表创建一个同义词“E”：

--同义词

CREATESYNONYMEFOREMP

3.使用同义词：

例，使

用同义词查询“EMP”所有记录：

--使用同义词(默认为私有同义词)

SELECT*FROME

4.公开与私有同义词：

--公开同义词

CREATEPUBLICSYNONYMEFOREMP

公开同

义词任何用户都可以访问，私有同义词只有创建它的用户才可以访问。

5.删除同义词：

例，删

除上面创建的同义词“E”：

--删除同义词

DROPSYNONYME

十、序列

1.什么是

序列：

是一数据库对象，利用它可生成唯一的整数。

一般使用序列自

动地生成表主键值或唯一键值

不直接

连接到数据库中的任何表

2.定义序列的语法：

CREATE

SEQUENCE [user.]sequence_name

[increment by n]

[start with n]

[maxvalue

n|]

[minvalue n ];

[cache]

[NOCYCLE|CYCLE]

INCREMENT

BY：指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。

START

WITH：指定生成的第一个序列号。在

升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。

MAXVALUE：指定序列可生成的最大值。

MINVALUE：指定序列的最小值

NOCYCLE：一直累加，不循环

CYCLE

CACHE：默认值是20

序列里的缓冲是

大量并发提取序列值时提升性能的一种手段。

由于序

列操作是原子操作，如果没有缓冲，序列的访问将只能由一个事务独占，独占期间阻塞其它事务的访问，且一定会涉及硬盘操作。这样如果有对一个序列的大量并发

操作的话，系统性能将会下降。

缓冲是

指事先成批的提取序列值，缓冲在内存中，供多个事务同时访问(每个事务占一个缓冲的序列值)，且访问期间如果没有用尽缓冲是不用写硬盘的，可大幅度提高并

发访问序列的性能。但带来的问题是序列值可能不连续(但肯定保证唯一)，如并发访问序列值后有些事务回滚等操作，都会带来缓冲序列值的不连续或丢失。

3.创建序列：

例，创

建一个“SEQ_EMP”序列：

--创建序列

CREATESEQUENCESEQ_EMP

INCREMENTBY1

STARTWITH1

MAXVALUE100

MINVALUE1

CYCLE

4.查看序列：

例，查

看“SEQ_EMP”序列：

--查看序列

SELECT*FROMUSER_SEQUENCES

--查看初始化序列，NEXTVAL将

引起序列值增加“INCREMENT BY”

SELECTSEQ_EMP.NEXTVALFROMDUAL

--查看序列当前值

SELECTSEQ_EMP.CURRVALFROMDUAL

5.使用序列：

例，向

“EMP”表中插入新记录。“EMPNO”字段使用上面创建的“SEQ_EMP”序列的值：

--使用序列

INSERTINTOEMP(EMPNO,ENAME)VALUES(SEQ_EMP.NEXTVAL,'XLTEST')

如果某

个字段使用“SEQ_EMP”序列的值，就应该一直使用这个序列，否则可能会造成字段值重

复。我理解序列就是自定义的“auto_increment”，可能还有些其他用途。

6.修改序列：

例，修

改上面创建的“SEQ_EMP”序列：

--修改序列

ALTERSEQUENCESEQ_EMP

INCREMENTBY2

MAXVALUE200

MINVALUE2

修改序

列可以：

修改未来序列值的增量。

设置或撤消最小值或最大值。

改变缓冲序列的数目。

指定序列号是否是有序。(这应该就是循环或不循环)

注意：

1．第一次NEXTVAL返回的是初始值

2．可以修改除start以外的所有sequence的参数。如果想要改变start值，必须删除序列再重新创建。

7.删除序列：

例，删

除序列“SEQ_EMP”：

--删除序列

DROPSEQUENCESEQ_EMP

十一、匿名块

1.什么是匿名块：

匿名块

是只使用一次的PL/SQL程序块

匿名块没有名

称，也不被存储在数据库中

由PL/SQL的四个基本组成部分构成

不能被重复使用

2.匿名块语法：

DECLARE

变量、常量声明;

BEGIN

pl/sql_block;

EXCEPTIONS

异常捕捉;

END;

十二、存储过程

1.什么是存储过程：

用于在

数据库中完成特定的操作或者任务。其实，它就是数据库中的自定义函数！

语法：

CreatePROCEDUREname

parameter_name [IN|OUT|INOUT] datatype

[{:=|DEFAULT} expr]

IS

pl/sql_block;

在Sql*Plus中使用CREATE OR REPLACE子句创建存储程序单元

在头部定义所有

参数

IS表示PL/SQL块的开始。

在IS之后，声明本地变量

2.存储过程的参数模式：

IN

OUT

IN OUT

默认

值：

传递给

子程序

手动指定

返回到

调用环境

手动指定

传递给

子程序，返回到调用环境

参数形式：

常量

未初始

化的变量

初始化

的变量

实参：可以是表达式，常量，或者是初始化的变量。

必须是

一个变量

必须是

一个变量

上面一

年便知：

IN，就是传入参数。

OUT，就是接收返回值。

INOUT，就是传入参数后，也接收返回值。

3.创建存储过程：

例，创

建一个根据“EMPNO”字段更新“EMP”表中“SAL”字

段值，并返回“OK!”：

--创建存储过程

CREATEORREPLACEPROCEDUREUPEMPFUN(

ARG_EMPNOINNUMBER,

ARG_SALINNUMBER,

ARG_RESOUTVARCHAR

)ISBEGIN

UPDATEEMPSETSAL=ARG_SALWHEREEMPNO=ARG_EMPNO;

COMMIT;

ARG_RES:='OK!';

END;

4.调用存储过程：

例，调

用上面创建的存储过程：

--调用存储过程

DECLARE

ARG_RESVARCHAR(20);

BEGIN

UPEMPFUN(7369,99999,ARG_RES);

DBMS_OUTPUT.PUT_LINE(ARG_RES);

END;