oracle数据库PLSQL序列(组图)

序列是Oracle i提供的用于按照设定的规则自动产生数据的方案对象 在某些数据表的结构中 有些字段需要这种特性 比如 对于某个学生数据表的学号关键字段 用户可以希望在录入数据时 能够自动在上一个记录的学号字段上自动加 等 由于Oracle i提供的 种基本数据类型并没有这样的功能 可以通过序列方案对象来实现 序列的创建 下面介绍在企业管理器中如何创建序列 （ ）在企业管理器中选择myoracle mynet/方案/序列选项 单击鼠标右键 在出现的快捷菜单里选择创建选项 如图 所示 （ ）出现如图 所示的创建序列的一般信息选项卡 在名称文本框里输入待定义的序列的名称 TEMPSEQUENCE 在方案下拉列表框里选择序列所属的用户名 SCOTT 序列类型参数有两个选项 若选择升序单选钮 则表示将创建从初始值向最大值递增的序列 这是创建序列时的默认设置 若选择降序单选钮 则表示将创建从初始值向最小值递减的序列 对值可以进行设置的参数如下 在最小值文本框里设置序列允许的最小值 创建序列时该字段最初为空 如果单击创建按钮时该字段为空 则对升序序列使用默认值 而对降序序列使用默认值 在最大值文本框里设置序列允许的最大值 创建序列时该字段最初为空 如果单击创建按钮后该字段为空 则将对升序序列使用默认值 而对降序序列使用默认值 在时间间隔文本框里设置递增序列递增的间隔数值（升序序列）或递减序列递减的间隔数值（降序序列） 创建序列时该字段最初为空 如果单击创建按钮后该字段为空 将使用默认值 该字段只能为正整数 在初始值文本框里设置序列的起始值 如果单击创建按钮后该字段为空 对升序序列将使用该序列默认的最小值 对降序序列将使用该序列默认的最大值 对选项可以设置的参数如下 若选择循环值复选框 则表示指定在达到序列最小值或最大值之后 序列应继续生成值 对升序序列来说 在达到最大值后将生成最小值 对降序序列来说 在达到最小值后将生成最大值 如果未选择该复选框 序列将在达到最小值或最大值后停止生成任何值 默认情况下是未选择状态 若选择排序值复选框 则指定序列号要按请求次序生成 默认情况下是未选择状态 在高速缓存中设置由数据库预分配并存储的值的数目参数 若选择默认值单选钮 则表示将设置默认值为 默认情况下选择此选项 若选择无高速缓存单选钮 则表示指定不预分配序列值 若选择大小单选钮 则表示在文本框里输入可接受的值 最小值为 对循环序列来说 该值必须小于循环中值的个数 如果序列能够生成的值数的上限小于高速缓存大小 则高速缓存大小将自动改换为该上限数 完成设置后单击创建按钮 （ ）成功创建序列后 出现如图 所示界面 单击 确定 按钮 （ ）读者也可以在SQLPlus Worksheet中执行下列SQL程序创建序列 ―――――――――――――――――――――――――――――――――――――CREATE SEQUENCE SCOTT TEMPSEQUENCE INCREMENT BY START WITH MAXVALUE E MINVALUE NOCYCLE CACHE NOORDER―――――――――――――――――――――――――――――――――――――配套程序位置 第 章\ createsequence sql 序列的使用 下面介绍在向数据表中插入数据时如何使用序列 （ ）首先为实例建立一个数据表 SCOTT SEQUENCE_TABLE 为简化起见 该数据表仅包含一个类型为 NUMBER 的数据列 NO 在如图 所示的创建表的一般信息选项卡中进行如下设置 在名称文本框中输入 SEQUENCE_TABLE 在方案下拉列表框中选择 SCOTT 在表空间下拉列表框中选择 USERS 在名称单元格中输入 NO 在数据类型下拉列表框单元格中选择 NUMBER 完成设置后单击创建按钮 （ ）读者也可以在SQLPlus Worksheet中执行下列SQL代码创建数据表 SCOTT SEQUENCE_TABLE ―――――――――――――――――――――――――――――――――――――CREATE TABLE SCOTT SEQUENCE_TABLE ( NO NUMBER( ) NOT NULL)TABLESPACE USERS ―――――――――――――――――――――――――――――――――――――配套程序位置 第 章\ createsequencetable sql （ ）在插入新的记录时 使用刚创建的 TEMPSEQUENCE 序列来自动产生 NO 数据列的值 在SQLPlus Worksheet里执行下面的SQL代码 执行的结果如图 所示 ―――――――――――――――――――――――――――――――――――――INSERT INTO SCOTT SEQUENCE_TABLE(NO)VALUES(SCOTT TEMPSEQUENCE NEXTVAL);―――――――――――――――――――――――――――――――――――――配套程序位置 第 章\ insertsequencetable sql SCOTT TEMPSEQUENCE NEXTVAL 表分配下一个惟一的 可用的序列号 执行 SCOTT TEMPSEQUENCE NEXTVAL 后 可以使用 SCOTT TEMPSEQUENCE CURRVAL 来标识上一个已经存储的序列值 （ ）在SQLPlus Worksheet中可以执行查询数据表 SCOTT SEQUENCE_TABLE 数据的语句 执行结果如图 所示 表明序列 SCOTT SEQUENCE 产生的值已经成功录入数据表中 ―――――――――――――――――――――――――――――――――――――select from scott sequence_table;―――――――――――――――――――――――――――――――――――――配套程序位置 第 章\ selectsequencetable sql 序列的删除 下面介绍在企业管理器中如何删除序列 （ ）在创建好的序列 SEQUENCE 上用单击鼠标右键 在出现的快捷菜单里选择移去选项 如图 所示 （ ）出现如图 所示的删除序列确认界面 单击是按钮 lishixinzhi/Article/program/Oracle/201311/17301

Oracle主要数据类型

概述 各种数据库所支持的数据类型大同小异 与标准SQL语言中的数据类型可能略有出入

类型 varchar 可以在声明字段时设置它的长度上限 而且使用它之后 就不必再考虑空格的存在

若插入的字符串的长度低于长度上限 系统就会自动将其缩减为字符串的真实长度

number(m n) 既可以用来表示整型 也可以表示浮点型 但m不可以超过 如果n为 或者省略n 就代表它是整数

date 用来存放日期和时间

blob 通常是在应用程序中使用到它 而不是在数据库中利用SQL指令直接使用

比如通过JDBC技术访问数据库 读写blob或clob类型的字段 即读写长的字符串信息等等

char 它是一种定长的字符类型 在Oracle数据库不区分字符和字符串 它们被统称为字符型或文本型

所谓定长的字符型是指 插入的字符串若没有达到约定的字段长度 系统就会在字符串尾部自动补空格

同样 读取时的字段长度永远是声明时的字段长度 而且在比较字符串内容的时候 也需要考虑到空格的过滤

nchar 它也是定长的字符串类型 它是SQL语言标准中规定的 通常采用Unicode编码来保存不同国家或不同语言的字符

varchar SQL标准在定义varchar时并没有保证能够向前和向后兼容 即有可能随着语言标准的修改而产生不兼容的问题

所以Oracle定义了同varchar型类似的varchar 型 就是为了在Oracle以后的版本中 都永远支持varchar 类型

Oracle这么做就是为了确保此类型向前后兼容 以达到能够在Oracle系列数据库中进行数据的导入和导出的目的

long 它和varchar 的差别在于 它不支持对字符串内容进行检索 即查询时不可以对它的内容进行条件查询

而varchar 和char nchar型等等都可以在查询的时候直接检索字符串的内容

补充 select from v$nls_parameters;数据库的配置信息以数据表的形式存在 通常称其为关于数据的数据或数据字典

实际上它查询的是数据字典中的一个视图 其中NLS_CHARACTERSET对应的是当前的数据库字符集

缺省均为使用数据库字符集 教程中使用的是安装时默认的ZHS GBK字符集 即汉字占 个字节 英文占 个字节

而NLS_NCHAR_CHARACTERSET对应的是nchar或nvarchar 类型所采用的辅助字符集 即AL UTF 字符集

实际上AL UTF 是一种 位定长的Unicode编码的字符集 而数据库字符集以及这种国家字符集都可以修改

但数据库字符集修改后可能会面临很严重的后果 除非是数据库管理员 普通用户不必对这方面进行深究

数据库中的数据导入导出的时候 如果源数据库和目标数据库所采用的字符集不同 也很容易出问题

函数

概述 函数可以认为是能够完成相对独立的功能的一段代码的集合 Oracle函数相当于其它语言中的方法或过程

Oracle函数可以分为单行函数和多行函数两大类 Oracle函数都是有返回值的

所谓的单行函数是针对查询结果中的每一行都起作用 都会返回一个结果

多行函数也就是所谓分组函数 是针对一组查询的记录 或者说多行 返回一个结果

单行 *** 作数据项 接受参数并返回处理结果 对每一返回行均起作用 可修改数据类型 可嵌套使用

单行函数分为字符函数 数值函数 日期函数 转换函数 通用函数

多行 也称分组函数 即对一组数据进行运算 针对一组数据(多行记录)只能返回一个结果

多行函数包括avg() count() max() min() sum()等

比如select avg(sal) max(sal) min(sal) sum(sal) max(hiredate) min(hiredate) from emp;

续一 使用Oracle的系统函数中的单行函数可实现诸多功能 如对数据进行计算 控制数据的输出格式

设置和改变日期的显示格式 进行数据类型转换 使用NVL等函数处理空值 实现IF THEN ELSE多路分支逻辑等等

续二 转换函数不会改变表中数据的字段类型和值 它就相当于将数据复制了一份 所转换的是复制之后的数据

数据类型转换包括隐含转换和显式转换两种方式 建议使用显式的数据类型转换 确保SQL语句的可靠性

续三 通用函数适用于包括空值在内的任何类型数据 通常用来实现空值的处理 空值的过滤或设置缺省值等

通用函数包括nvl() nvl () nullif() coalesce() case表达式 decode()等

嵌套 单行函数可以嵌套使用 嵌套层次无限制 分组函数最多可嵌套两层 嵌套函数的执行顺序是由内到外

单行比如select empno lpad(initcap(trim(ename)) ) 姓名 job sal from emp;

多行比如select max(avg(sal)) from emp group by deptno; 其实这里再使用分组函数就没有意义了

说明 通常数据库层面提供的函数 只是进行数据的简单的处理 或者说是只能实现极为常规的功能

所以就不应该 或者说是不要指望在数据库查询的层面来实现特别复杂的业务逻辑

如果应用程序的逻辑跟数据库混在一起的话 会不利于代码的维护和更新

而且也不利于数据库的管理 包括数据移植 数据库导入导出等等

日期类型

概述 在计算机 *** 作系统或者各种高级编程语言中 日期通常会被保存成一个长整数 通常记录的是毫秒

Oracle内部以数字格式存储日期和时间信息 世纪 年 月 日 小时 分钟 秒

缺省的日期格式是DD—MON—YY 可使用sysdata函数获取当前系统日期和时间

运算 日期型数据可以直接加或减一个数值 结果认为日期 约定的该数值代表的是相加减的天数

两个日期型数据可以相减 结果为二者相差多少天 二者不能 因为日期相加是没有意义的

NVL()函数

概述 它用于将空值null替换为指定的缺省值 适用于字符 数字 日期等类型数据

格式 NVL(exp exp ) 如果表达式exp 值为null 则返回exp 值 否则返回exp 值

举例 select empno ename sal m sal+nvl(m ) from emp;

select empno ename job nvl(job No job yet ) from emp;

NVL ()函数

概述 它用于实现条件表达式功能

格式 NVL (exp exp exp ) 如果表达式exp 值不为null 则返回exp 值 否则返回exp 值

举例 select empno ename sal m nvl (m sal+m sal) 总收入 from emp;

NULLIF()函数

概述 它用于数据等价性比较并根据比较结果返回null或其中一个被比较的数值 实际开发中应用并不是很多

格式 nullif(exp exp ) 如果表达式exp 与exp 的值相等 则返回null 否则返回exp 的值

举例 select name 原名 nullif(pen_name name) 化名 from author;

COALESCE()函数

概述 它用于实现数据“接合”功能

格式 coalesec(exp exp ) 依次考察各参数表达式 遇到非null值即停止并返回该值

若表达式均为null值 则返回null 通常最后一个表达式都是能确保不是空值的字段

举例 select empno ename sal m coalesec(sal+m sal ) 总收入 from emp;

CASE表达式

概述 它用于实现多路分支结构

格式 case exp when parison_exp then return_exp

[when parison_exp then return_exp

when parison_expn then return_expn

else else_exp]

end

举例 select empno ename sal

case deptno when then 财务部

when then 研发部

when then 销售部

else 未知部门

end 部门

from emp;

说明 CASE中的每一个表达式(如deptno 财务部等)都可以是复合而成的

这种对齐方式的书写是为了增加可读性 当然也可以把代码写在同一行上

其中case到end之间的整体就相当于普通查询中的一个字段 end后面的“部门”是别名

DEDODE()函数

概述 和case表达式类似 它也用于实现多路分支结构

格式 decode(col|expression search result

[ search result ]

[ default])

举例 select empno ename sal

decode(deptno 财务部

研发部

销售部

未知部门 )

部门

from emp;

COUNT()函数

格式 count()返回组中总记录数目

count(exp)返回表达式exp值非空的记录

count(distinct(exp))返回表达式exp值不重复的 非空的记录数目

举例 select count() from emp; 缺省的情况下 整个表就是一组

select count(m) from emp; 返回emp表中m字段不为空的记录(行)数目

select count(distinct(deptno)) from emp; 查找deptno值为非空且不重复的记录数目

分组函数与空值

概述 分组函数省略列中的空值 可使用NVL()函数强制分组函数处理空值

举例 select avg(m) from emp; 等价于sum(m)/count(m)

select sum(m) from emp; 计算表中非空的m值的总和

select avg(nvl(m )) from emp; 等价于avg(nvl(m ))/count()

GROUP BY子句

概述 它用于将表中数据分成若干小组

格式 select column group_function(column)

from table

[where condition]

[group by group_by_expression]

[order by column];

举例 select deptno avg(sal) from emp group by deptno;

说明 出现在SELECT列表中的字段 如果不是包含在组函数中 那么该字段必须同时在GROUP BY子句中出现

包含在GROPY BY子句中的字段则不必须出现在SELECT列表中 子句执行顺序是where→group by→order by缺省按升序排列

补充 select deptno job avg(sal) from emp group by deptno job order by deptno desc; 基于多个字段的分组

select deptno avg(sal) from emp; 非法

注意 如果没有GROUP BY子句 SELECT列表中不允许出现字段(单行函数)与分组函数混用的情况

WHERE中不允许使用分组函数 如select deptno avg(sal) from emp where avg(sal)> group by deptno; 非法

这跟子句执行的顺序有关 where子句最先执行 在执行where子句的时候还没有执行过group by子句

于是程序不知道这是在分组 也不曾计算过avg(sal)的组内平均工资 所以在where子句中不允许使用分组函数

由于还没有执行过group by子句 所以此时就不确定如何怎么分组以及分多少个组

所以where子句中只能进行初级过滤 此时可以使用HAVING子句实现对平均工资的过滤

HAVING子句

概述 它用于过滤分组

格式 select column group_function(column)

from table

[where condition]

[group by group_by_expression]

[having group_condition]

[order by column];

举例 select deptno job avg(sal)

from emp

where hiredate >= to_date( yyyy mm dd )

group by by deptno job

having avg(sal) >

lishixinzhi/Article/program/Oracle/201311/19087

Application Development>>SQL Plus

有个情况要告诉你他和ms sql server是不一样的，他的客户端是控制台方式的，不是可视化的，就是说要打命令

*** 作顺序如下：

1创建实例启动用的参数文件。在程序默认的参数文件里修改即可：重命名规则 initSIDora

/u01/oracle/product/1120/db_1/dbs/ 下，目录尽量使用绝对路径，采用ORACLE_BASE等环境变量有可能报错。

例 initcrmora

db_name='crm' --修改

#memory_target=500m --注释掉

sga_target=200m --添加

pga_aggregate_target=60m --添加

processes = 150

audit_file_dest='/u01/oracle/admin/crm/adump' --建立目录

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/oracle/flash_recovery_area' --建立目录

db_recovery_file_dest_size=2G

diagnostic_dest='/u01/oracle' ---修改

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

2创建相应目录：

mkdir -p /u01/oracle/admin/ora11g/adump

mkdir -p /u01/oracle/admin/ora11g/dpdump

mkdir -p /u01/oracle/flash_recovery_area

3创建密码文件：命名规则 orapwSID，目录依旧是 /u01/oracle/product/1120/db_1/dbs/

Linux下orapw+实例名

Windows下pwd+实例名

$ orapwd file=orapwora11g password=oracle

4创建数据库实例

设置环境变量 export ORACLE_SID=crm

执行以下命令

sqlplus /nolog

conn / as sysdba

注意：本处可能报错需要可能的原因： a）glibc-devel-25-58el5_64、libaio-03106-5包是否已安装，命令分别为rpm -q glibc-devel、rpm -q libaio

b）oracle目录权限不足或属组不对，chmod 6751 oracle 授权、chown -R oracleoinstall /u01/oracle 修改属组

执行建库脚本：

startup nomount pfile="/u01/oracle/product/11201/db1/dbs/initcrmora";

CREATE DATABASE "crm"

MAXINSTANCES 8

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

DATAFILE '/u01/oracle/oradata/system01dbf' SIZE 1024M REUSE

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/u01/oracle/oradata/sysaux01dbf' SIZE 500M REUSE

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oracle/oradata/temp01dbf' SIZE 20M REUSE

SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/oracle/oradata/undo01dbf' SIZE 300M REUSE

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16

LOGFILE GROUP 1 ('/u01/oracle/oradata/log01dbf') SIZE 50M,

GROUP 2 ('/u01/oracle/oradata/log02dbf') SIZE 50M,

GROUP 3 ('/u01/oracle/oradata/log03dbf') SIZE 50M;

创建USERS表空间

CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/u01/app/oradata/user01dbf' SIZE 1000M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ALTER DATABASE DEFAULT TABLESPACE "USERS";

------------------------------------------

SQL> create spfile from pfile;

File created

-----------------------------------------

sqlplus / as sysdba

SQL> show user;

@/u01/oracle/product/11201/db1/rdbms/admin/catalogsql;

@/u01/oracle/product/11201/db1/rdbms/admin/catblocksql;

@/u01/oracle/product/11201/db1/rdbms/admin/catprocsql;

@/u01/oracle/product/11201/db1/rdbms/admin/catoctksql;

@/u01/oracle/product/11201/db1/rdbms/admin/owminstplb;

使用system用户编译

@/u01/oracle/product/11201/db1/sqlplus/admin/pupbldsql;

@/u01/oracle/product/11201/db1/sqlplus/admin/help/hlpbldsql helpussql;

使用sys用户编译

@/u01/oracle/product/11201/db1/javavm/install/initjvmsql;

@/u01/oracle/product/11201/db1/xdk/admin/initxmlsql;

@/u01/oracle/product/11201/db1/xdk/admin/xmljasql;

@/u01/oracle/product/11201/db1/rdbms/admin/catjavasql;

@/u01/oracle/product/11201/db1/rdbms/admin/catexfsql;

@/u01/oracle/product/11201/db1/rdbms/admin/catqmsql change_on_install SYSAUX TEMP YES;

@/u01/oracle/product/11201/db1/rdbms/admin/catxdbjsql;

@/u01/oracle/product/11201/db1/rdbms/admin/catrulsql;

spool /oracle/admin/edidb/scripts/ordinstlog append

@/u01/oracle/product/11201/db1/ord/admin/ordinstsql SYSAUX SYSAUX;

spool off

spool /u01/oracle/admin/ora11g/scripts/interMedialog append

@/u01/oracle/product/11201/db1/ord/im/admin/iminstsql;

spool off

set echo on

spool /oracle/admin/edidb/scripts/lockAccountlog append

BEGIN

FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (

'SYS','SYSTEM') )

LOOP

dbms_outputput_line('Locking and Expiring: ' || itemUSERNAME);

execute immediate 'alter user ' ||

sysdbms_assertenquote_name(

sysdbms_assertschema_name(

itemUSERNAME),false) || ' password expire account lock' ;

END LOOP;

END;

/

spool off

到此，数据库实例建立完毕。

以上就是关于oracle数据库PL/SQL序列(组图)全部的内容，包括:oracle数据库PL/SQL序列(组图)、Oracle数据库入门之函数/类型、如何打开oracle数据库等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！