oracle语句语法大全_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
oracle语句语法大全
&&Oracle语句大全
阅读已结束，下载本文需要
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，同时保存到云知识，更方便管理
还剩19页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢oracle基本语法(增删改查)_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
oracle基本语法(增删改查)
阅读已结束，下载本文需要
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，同时保存到云知识，更方便管理
还剩4页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢Oracle基本语法
基本语法如下所示
CREATE OR REPLACE FUNCTION &function_name& [(&parameters&)]&
RETURN &datatype& IS [declare section]&
BEGIN &www.2cto.com &
& & &[&statement(s)&]
RETURN &expression&;
[EXCEPTION &exception handler(s);]
END [&function_name&];
　　从上面的格式可以看出：
　　1）Function必须要有一个名字&function_name&；
　　2）可能有一个或多个参数；
　　3）必须指定返回值的类型&datatype&；
　　4）函数体用关键字BEGIN和END包含，必须要有RETURN返回值；
一些简单的例子
　　1、一个获取名字的函数，直接返回字符串
CREATE OR REPLACE FUNCTION fun_getMyName
RETURN varchar2 is
& & &RETURN 'Luxh';
　　调用：
select fun_getMyN
select fun_getMyName()
　　2、给定雇员ID，从雇员表中计算税收和保险的扣除函数。这个函数包含一个参数、一个本地变量描述和异常处理。
CREATE OR REPLACE FUNCTION fun_deductions(emp_id NUMBER)--emp_id接收输入参数
RETURN NUMBER IS
& total_deductions NUMBER;--定义返回的变量
BEGIN &www.2cto.com &
& & &SELECT tax + insurance--查出需要的数据
& & &INTO total_deductions--赋值给变量total_deductions
& & &FROM emp where id = emp_
& & &RETURN total_--返回结果
& & &WHEN no_data_found THEN--找不到数据异常
& & & & & RETURN 0;
& & &WHEN OTHERS THEN--其他异常
& & & & & RETURN -1;
　　调用：
select fun_deductions(1)
　　3、一个计算环周长的函数，两个输入参数，一个常量定义
CREATE OR REPLACE FUNCTION fun_circum_perimeter(angle NUMBER,radius NUMBER)--定义两个参数，angle:角度，radius:半径
RETURN NUMBER IS
& & &PI CONSTANT NUMBER := 3.1415926;--定义常量
& & &circum_perimeter NUMBER;--定义返回变量
& & &circum_perimeter := ROUND((angle/360)*2*PI*radius,2);--计算
& & &RETURN circum_
END fun_circum_
& www.2cto.com &
　　调用：
SELECT fun_circum_perimeter(360,2) FROM DUAL;
　　4、选择结果返回
CREATE OR REPLACE FUNCTION fun_if(n INTEGER)
RETURN INTEGER IS
& & &IF n = 0 THEN
& & & & RETURN 1;
& & &ELSIF n = 1 THEN
& & & & RETURN
& & & & RETURN -1;
& & &END IF;
&　　5、阶乘
CREATE OR REPLACE FUNCTION fun_factorial(n POSITIVE)
RETURN POSITIVE IS
& & &IF n = 1 THEN
& & & & RETURN
& & & & &RETURN n*fun_factorial(n-1);
& & &END IF;
　　6、斐波那契
& www.2cto.com &
CREATE OR REPLACE FUNCTION fun_fibonacci(n PLS_INTEGER)
RETURN PLS_INTEGER IS
& & & &fib_1 PLS_INTEGER := 0;
& & & &fib_2 PLS_INTEGER := 1;
& & &IF n = 1 THEN
& & & & RETURN fib_1;
& & &ELSIF n = 2 THEN
& & & & RETURN fib_2;
& & & & RETURN fun_fibonacci(n-2) + fun_fibonacci(n-1);
& & &END IF;
作者 CN.programmer.Luxh当前位置： →
→ ORACLE-根本语法
ORACLE-根本语法
& 作者：佚名 & 来源： 互联网 & 热度：
&收藏到→_→：
摘要: ORACLE - 基本语法(转载) &&&&&&&&&&&&&&&&nbs...
"ORACLE-根本语法"：：
oracle - 基本语法(转载)
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& oracle相关语法一、oracle入门 & 理论知识：&&& oracle的物理组件有三个：&&&&& (1)数据文件&&&& 数据文件是用于存储数据的文件，如表、索引数据。每个oracle有一个或多个物理数据文件，&&&&&&&&&&&&&&&&&&&&& 一个数据文件只能与一个关联。&&&&& (2)日志文件&&&& 用于记录对进行的修改信息，日志文件主要用于在出现故障时实施恢复。&&&&& (3)控制文件&&&& 控制文件是记录物理结构的二进制文件，每个oracle都含有一个控制文件。&&& oracle的逻辑组件：&&&&& 表空间(tablespace) 表空间是最大的逻辑单位，一个至少包含一个表空间，一个表空间包含一个或多个段等等。&&&&& 段(segment)&&&&&&& 段存在于表空间中，分成4类，数据段、索引段、回退段、临时段。&&&&&&&&&&&&& &&&&& 区(extent)&&&&&&&& 区是磁盘空间分配最小单位，由连续的数据块组成，一个或多个区构成段，区只能存在于一个数据文件中。&&&&& 数据块(data block) 数据块是中最小的数据组织单位与管理单位，oracle中的于数据块中，取值范围2k-64k之间。&&&&& 模式(schema)&&&&&& 模式是对用户所创建的对象的总称，又称为用户模式。　　概念：　　　内存　oracle内存结构包含以下两个内存区。　　　　1、系统全局区(sga)　实例启动时分配该内存区，是oracle实例的一个基本组件。　　　　　&& 又称为共享全局区，它用来存储信息，并由多个进程共享。可分为共享池、数据缓冲区及日志缓冲区。　　　　　　 (1)共享池　　　是对sql、pl/sql程序进行语法分析、编译、执行的内存区域。共享池由库缓存和缓存组成。其中，库缓存含有　　　　　　　　　　　　　　最近执行的sql、pl/sql语句的分析码和执行计划；缓存含有从中得到的表、索引、列定义和权限等信息。&&&&& (2)数据缓冲区　数据缓冲区用于存储从磁盘数据文件中读入的数据，所有用户共享。&&&&& (3)日志缓冲区　日志记录的所有修改信息，主要用于恢复数据。　　　　2、程序全局区(pga)& 服务器进程启动时分配该内存区。pga为非共享区，只能单个进程使用，当一个用户会话结束后，pga释放。　　用户进程(pga)发送sql语句到共享全局区(sga)，先在共享池的库缓存中查询是否存在所需的数据块，如果存在就在中读取相应的数据块，如　　果不存在就由服务器进程(dbwr)来io　语法知识：& 创建表空间的语法如下：&&& create tablespace tablespacename datafile 'd:/filename.dbf' [size int [kb|mb]] [autoextend [off|on]]; &&& tablespacename 是需创建的表空间名称。&&& datafile& 指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。&&& filename& 是表空间中数据文件的路径和名称。&&& size&&&&& 指定文件的大小，用k指定千字节大小，用m指定兆字节大小。&&& autoextend子句用来启用或禁用数据文件的自动扩展。& oracle默认用户：&&& 用户名：sys&&&&&& 默认密码：chage_on_install&& 用来管理拥有oracle文件&&& 用户名：system&&& 默认密码：manager&&&&&&&&&&& 用来管理拥有视图对象&&& 用户名：scott&&&& 默认密码：tiger&&&&&&&&&&&&& 示例用户，包括emp、dept等表& 连接oracle:&& 在控制台下输入 sqlplus 用户名/密码& 回车或　sqlplusw 回车& 相关命令：&&& disconn&&&&&&&&&&&&&& //退出当前登录&&& conn 用户名/密码&&&&& //连接oracle&&& alter user 用户名 identified by 密码 //修改用户口令　　drop user 用户名&&&&&&&&&&& //删除用户&&& alter user 用户名&&&&& //给某个用户加锁&&& alter user 用户名&&& //给某个用户解锁&&& ed& 回车:& //打开缓冲区&&& /&& 回车:& //执行缓冲区中的语句&&& create user 用户名 identified by 密码 [password expire]&&&& [default tablespace 表空间名] [temporary tablespace 临时表空间名];& //创建用户& 相关权限：&&& gr&&&&&&&&&& //connect角色将允许用户创建并在中创建表或其他对象　　gra&&&&&&&&& //resource角色将允许用户使用中的空间　　grant crea&& //create sequence权限将允许用户创建序列，此权限包含在connect连接角色中　　grant se&&&& //将emp表的查询权限授予用户scott　　grant update(vencode,venname) on 表名　//将特定列的更新权限授予用户scott&&& grant 权限 on 表名 to 用户名& //接受该权限的用户可以将此权限授予其他用户　　revoke select,update on 表名 from 用户名;　　//收回相应的权限二、sql查询和sql函数　sql支持如下类别的命令：　　数据定义语言：　create(创建)、alter(更改)、drop(删除)和truncate(截断)命令。　　数据操纵语言：　insert(插入)、select(选择)、delete(删除)和update(更新)命令。　　事务控制语言：　commit(提交)、savepoint(保存点)和rollback(回滚)命令。　　数据控制语言：　grant(授予)和revoke(回收)命令。　数据类型：　　char:&&&&&&&&&& 长度在1到2000个字节，声明多少字节在内存中就占用多少字节，输入的值小于指定的长度时用空格填充。　　varchar2:&&&&&& 长度在1到4000个字节，输入的值是多少字节，就占用多少字节。　　long:&&&&&&&&&& 长度在2gb，设置为此类型的列时，要注意：一个表中只有一列可以为long类型，long类型列不能定义为唯一约束或主键约束，　　　　　　　　　　不能建立索引，过程或不能接受long类型的参数。　　number(p,s):&&& 其中p为精度，表示数字的总位数，在1至38之间。s为范围，表示小数点右边数字的位数，在-84至127之间。　　date:&&&&&&&&&& 日期类型，sysdate为当前系统时间。格式为08-9月 -07。　　timestamp:&& 　 用于存储日期的年、月、日以及时间的时、分和秒。其中秒精确到小数点后6位，&&&&&&&&&&&&&&&&&&& systimestamp返回当前日期、时间。格式为08-9月 -07 04.08.30.000000 下午。　　raw:&&&&&&&&&&& 此数据类型用于存储基于字节的数据，如二进制数据或字节串，该类型最多能存储2000个字节，可以建立索引。　　long raw:　　　 此数据类型用于可变长度的二进制数据，最多能存储2gb。long数据类型的所有限制对long raw数据类型也同样有效。　　lob又称为"大对象"数据类型，最多能存储4gb的非信息。包括：　　clob:&&&&&&&&&& clob代表character lob(字符lob)，它能存储大量字符数据。如xml文档。&&& blob:　　　　　 blob代表binary lob(二进制lob)，它能存储较大的二进制对象，如图形、视频剪辑和声音剪辑。&&& bfile:　　　　　bfile代表binary file(二进制文件)，它能够将二进制文件存储在外部的操作系此文来自: 马开东博客
转载请注明出处 网址：
统文件中。　伪列：　　rowid:　　select rowid,ename, from scott.emp where empno='7900';&&& rownum:&& select * from scott.emp where rownum&11;&&& 　　　　　　　　　//限制查询返回的行数& 语法知识：　　desc 表名;　　　　&&&&&&&& //查看表结构　　alter table 表名 modify (列名 varchar2(25));&&& //修改列　　alter table 表名 add (列名 varchar2(12),列名 number(12));　　//添加列　　alter table 表名 drop column 列名;　　//删除列　　truncate table 表名;　　//中删除记录而不删除结构，不使用事务处理，因此无法回滚　　drop table 表名;　　//删除表及其全部数据　　create table 新表名 as select * from 表名 where 1=2;　　//用现有的表创建一个新表　　select deptno*2 "new no",dname,　　//指定一个含有特殊字符(如空格)的列标题&&&&&&&& //提交事务　　savepoint 标记名;　　　//标记事务点　　&&& //回滚整个事务处理　　rollback to [savepoint] 标记名;　　　//回滚到事务中某个特定的保存点& 集合操作符：&&& union(联合):&&&&&&&&&& 此操作符返回两个查询选定的所有不重复的行。&&& 语法：&&&&&&&&&&&&&&&& select orderno from order_master union select orderno from order_&&& union all(联合所有):&& 此操作符合并两个查询选定的所有行，包括重复的行。&&& 语法：&&&&&&&&&&&&&&&& select orderno,ename from order_master union all select orderno,proname from order_detail order by 2;&&& 注意：在两个select语句中指定的列名不必相同，但数据类型必须匹配。也可以对联合查询的结果进行排序，使用order by子句时，它必须放在最后&&&&&&&&& 一个select语句之后，而且必须指定列索引来排序，而不是指定列名，列索引是从1开始的整数。上述语法便是以proname的索引排序&&& intersect(交集):&&&&&& 此操作符只返回两个查询都有的行。&&& 语法：&&&&&&&&&&&&&&&& select orderno from order_master intersect select orderno from order_&&& minus(减集):&&&&&&&&&& 此操作符中返回由第一个查询选定但是第二个查询中没有选定的行，也就是在第一个查询结果中排除第二个查询结果中出现的行。&&& 语法：&&&&&&&&&&&&&&&& select orderno from order_master minus select orderno from order_&&& 查询尚未交付的订单& 连接(||)操作符：&&& 语法：&& select ('供应商'||venname||'的地址是'||venadd1||' '||venadd2||' '||venadd3) 地址 from vendor_master where vencode='v002';&&&&&&&&&&&& 将多个字符串合并为一个字符串　sql函数：　　1、日期函数：&&& add_months:&&&&&& 此函数返回给指定的日期加上指定的月数后的日期值。语法为add_months(d,n),其中d是日期，n表示月数。&&& 示例：select add_months(sysdate,2)&&& 将当前时间加上2个月后的日期值。&&& months_between:&& 此函数返回两个日期之间的月数。语法为months_between(d1,d2),其中d1和d2是日期，如果d1大于d2，则结果为正数；否则为负数。&&& last_day:&&&&&&&& 此函数返回指定日期当月的最后一天的日期值此文来自: 马开东博客
转载请注明出处 网址：
，语法为last_day(d),其中d表示日期。&&& 示例：select last_day(sysdate)&&&&&&& 返回当前日期的月的最后一天，如果是9月就返回30-09月-07&&& round:&&&&&&&&&&& 此函数返回日期值，将日期四舍五入为格式模型指定的单位。语法为round(d,[fmt])。其中d是日期，fmt是格式模型。fmt是一个可&&&&&&&&&&&&&&&&&&&&& 选项，日期默认舍入为最靠近的那一天。如果指定格式为年"year"，则舍入到年的开始，即1月1日;如果格式为月"month"，则舍入到&&&&&&&&&&&&&&&&&&&&& 月的第一日;如果格式为周"day"，则舍入到最靠近的星期日。&&& 示例：select round(sysdate,'month')&& 返回最接近的一个月。&&& next_day:&&&&&&&& 此函数返回指定的下一个星期几的日期。语法为next_day(d,day)。其中d表示日期，而day指周内任何一天。&&& 示例：select next_day(sysdate,'星期日') 返回下一个星期日的日期，也可以用1表示，以此类推，星期一以2表示。&&& trunc:&&&&&&&&&&& 此函数将指定日期截断为由格式模型指定的单位日期，与round函数不同的是它只舍不入，语法为trunc(d,[fmt])，与round格式相同。&&& 示例：select trunc(sysdate,'year')&&& 返回当前年的第一天，也就是1月1日。&&& 示例：select trunc(sysdate,'day')&&&& 返回紧靠前面的星期日。如果为"日"就会返回"日"。&&& extract:&&&&&&&&& 此函数提取日期时间类型中的特定部分。语法为extract(fmt from d)，其中d是日期时间表达式，fmt是要提取的部分的格式。格式&&&&&&&&&&&&&&&&&&&&& 的取值可以是year,month,day,hour,minute,second,注意此处的格式不使用单引号。&&& 示例：select extract(year from sysdate)& 返回当前的年份。&&& 2、字符函数：&&& initcap(char):&&& 首字母大写，示例：select initcap('hello') 输出结果:hello。&&& lower(char):&&&&& 转换为小写，示例：select lower('fun') 输出结果:fun。&&& upper(char):&&&&& 转换为大写，示例：select upper('sun') 输出结果:sun。&&& ltrim(char,set):& 左剪裁，示例：select ltrim('xyzadams','xyz') 输出结果:adams。 &&& rtrim(char,set):& 右剪裁，示例：select rtrim('xyzadams','ams') 输出结果:xyzad。&&& translate(char,from,to):& 按字符翻译，示例：select translate('jack','abcd','1234') 输出结果:j13k。&&& replace(char,search_str,replace_str):& 字符串替换，示例：select replace('jack and jue','j','bl') 输出结果:black and blue。&&& instr(char,substr[,pos1,pos2]):& 查找子字串位置。&&& 示例：select instr('vorldwide','d') 输出结果:5。pos1为可选，表示从第几个位置查找。pos2为可选，表示从第几次出现的位置找。&&& substr(char,pos,len):& 取子字符串，示例：select substr('abcdefg',3,2) 输出结果:cd。&&& concat(char1,char2):& 连接字符串，示例：select concat('hello','world') 输出结果:helloworld。&&& chr:&&&& 此函数根据ascii码返回对应的字符，示例：select chr(45788),chr(53671),chr(50167),chr(65) 输出结果:曹 学 明 a。&&& ascii:&& 此函数返回gbk编码值，示例：select ascii('曹') cao ,ascii('学') xue,ascii('明') 输出结果: 50167。&&& lpad和rpad：& 示例：select lpad('function',15,'=')& 输出结果:=======function。而rpad则相反，字符串填充在右边。&&& trim:&&& 此函数从字符串的开头或结尾(或开头和结尾)剪裁特定的字符，默认剪裁空格。如果加上leading选项时与ltrim函数相似。指定trailing时和&&&&&&&&&&&& rtrim函数相似。&&&&&&&&&&&& 示例： select trim(9 from ) 输出结果:25988。&&&&&&&&&&&& 示例： select trim(leading 9 from ) 输出结果:2598899。&&&&&&&&&&&& 示例： select trim(trailing 9 from ) 输出结果:。&&& length:& 此函数返回字符串的长度，示例：select length('frances') 输出结果:7。&&& decode:& 示例：select deptno,dname,decode(loc,'new york','纽约','boston','波士顿') from scott.&&&&&&&&&&&& 此示例将替换显示loc列的结果,结果为"new york"的替换为"纽约"，"boston"的替换为"波士顿"。&&& greatest/least:& 返回一组表达式中的最大值/最小值,即比较字符的编码大小.&&& 示例：select greatest('aa','ab','ac')& 输出结果:ac。&&&&&&&&& select least('aa','ab','ac')& 输出结果:aa。&&&&&&&&& select greatest('啊','安','天')& 输出结果:天。&&&&&&&&& select least('啊','安','天')& 输出结果:啊。&&& 3、数字函数：&&& abs(n):&&&& 取绝对值，示例：select abs(-15)& 输出结果:15。&&& ceil(n):&&& 向上取整，示例：select ceil(44.778)& 输出结果:45。&&& sign(n):&&& 取符号，示例：select sign(-2)& 输出结果:-1。&&& floor(n):&& 向下取整，示例：select floor(200.88)& 输出结果:200。&&& power(m,n): m的n次幂，示例：select power(5,3)& 输出结果:125。&&& mod(m,n):&& 取余数，示例：select mod(10,3)& 输出结果:1。&&& round(m,n): 四舍五入，示例：select round(100.256,2)& 输出结果:100.26。&&& trunc(m,n): 截断，示例：select trunc(100.256,2)& 输出结果:100.25。&&& sqrt(n):&&& 平方根，示例：select sqrt(4)& 输出结果:2。&&& 4、转换函数：&&& to_char(d|n[,fmt]):&& 其中d是日期，n是数字，fmt指定日期或数字的格式。&&& 示例：select to_char(sysdate,'yyyy"年"fmmm"月"fmdd"日" hh24:mi:ss')& 输出结果:日 20:44:27。&&&&&&&&& select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')&&&&&&&&& select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')&&&&&&&&& select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"')&&& 示例：select to_char(sal,'$99999')& 输出结果:$1600。&&& to_date(char[,fmt]):& 此函数将char或varchar2数据类型转换为日期数据类型。&&& 示例：select to_date('','yyyy-mm-dd')& 输出结果:06-12月-05。&&&&&&&&& select to_date(' 20:08:08','yyyy/mm/dd hh24:mi:ss')&&&&&&&&& select to_date(' 20:08:08','yyyy/mm/dd hh24:mi:ss')&&&&&&&&& select to_date('日 20时08分08秒','yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"')&&& to_number(char):& 此函数将包含数字的字符串转换为number数据类型，通常不用这么做，因为oracle可以对数字字符串进行隐式转换。&&& 示例：select sqrt(to_number('100'))& 输出结果:10。&&& 5、其它函数：&&& nvl(expression1,expression2):&& 如果expression1为null，则nvl返回expression2。&&& nvl2(expression1,expression2,expression3):&& 如果expression1不是null，则nvl2返回expression2，如果expression1是null，则返回expression3。&&& nullif(expr1,expr2):& 此函数比较两个表达式，如果它们相等，则返回空值，否则返回expr1。&&& nullif函数等价于以下的case表达式：&&& case when expr1=expr2 then null else expr1 end&&& 6、分组函数：&&& avg:&& 此函数返回指定列值的平均值，示例：select avg(sal)& 输出结果:。&&& min:&& 此函数返回指定列值的最小值，示例：select min(sal)& 输出结果:800。&&& max:&& 此函数返回指定列值的最大此文来自: 马开东博客
转载请注明出处 网址：
值，示例：select max(sal)& 输出结果:5000。&&& sum:&& 此函数返回指定列值的总和，示例：select sum(sal)& 输出结果:29025。&&& count: 此函数是为了计算行数，它可以接受3种不同的参数&&& 示例：select count(*)& 输出结果:14。&&& 示例：select count(列名) from 表名;& &&& 示例：select count(distinct 列名) from 表名;&&& group by:& 此子句用于将信息表划分为组，按组进行聚合运算。select后面跟的列名只能是分组函数、group by子句中出现的列或表达式。&&& 示例：select deptno,max(sal) from emp group by(deptno); 查出每个部门的最高工资。&&& 示例：select deptno,count(*),sum(sal) from scott.　　//group by主要用来对一组数进行统计&&& having:&&& 此子句用来指定group by子句的检索条件。&&& 示例：select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)&=5;& //having对分组统计再加限制条件&&& 7、分析函数：只能出现在select列表或order by子句中。&&& row_number:& 下面对所有员工的工资进行排名，即使工资相同，其排名也不能相同。&&& 示例：select ename,job,deptno,sal,row_number() over(order by sal desc) as 排名 from scott.&&& 下面对所有员工的工资按部门进行排名，即使工资相同，排名也不同。&&& 示例：select ename,job,deptno,sal,row_number() over(partition by deptno order by sal desc) as 排名 from scott.&&& rank:& 此函数计算一个值在一组值中的排位，排位是以1开头的连续整数，如果两行的序数为1，则没有序数2，下行的序数为3。&&& 下面根据员工的工资和佣金对员工在每个部门中进行排位。相同的工资排位相同，并且排位不连续。&&& 示例：select ename,sal,comm,deptno,rank() over(partition by deptno order by sal desc,comm) 排名 from scott.&&& dense_rank:& 此函数计算一个行在一组有序行中的排位，排位是以1开头的连续整数，具有相同值的排位相同，并且排位是连续的。&&& 下面首先选择所有在accounting或research部门中工作的员工的部门名称、员工姓名和工资，&&& 然后分别计算每个员工的工资在部门中的排位，相等的工次排位相同&&& 示例：select d.dname,e.ename,e.sal,dense_rank() over(partition by e.deptno order by e.sal desc) 排名&&&&&&&&& from emp e,dept d where e.deptno=d.&& &&&&&&&&&&& 三、锁和表分区& 锁定是用来控制共享资源并发访问的机制。& oracle提供以确保在多用户环境下数据的完整性和。& 锁的两种级别：(只有在提交或回滚后才能释放锁定)& (1)行级锁：是一种排他锁，防止其他事务修改此行，但是不会阻止读取此行的操作。在使用insert、update、delete和select...for update&&&& 等语句时，oracle会自动应用行级锁定。&&&& select...for update语法为：&&&& select...for update [of column_list] [wait n | nowait]&&&& 其中：of子句用于指定即将更新的列，即锁定行上的特定列。&&&&&&&&&& wait子句指定等待其他用户释放锁的秒数，防止无限期的等待。nowait为不等待。&&&& 示例1：演示如何锁定deptno值为10的所有行。&&&& select * from dept where deptno=10 for update of dname,& (2)表级锁：将保护表数据，在事务处理过程中，表级锁会限制对整个表的访问。表级锁用来限制对表执行添加、更新和删除等修改操作。&&&& 语法：&&&& lock table &table_name& in &lock_mode& mode [nowait];&&&& 其中：table_name是要被锁定的表的名称。&&&&&&&&&& lock_mode是锁定的模式。&&&& 表级锁的模式：&&&& 1、行共享(row share,rs)：&&& 允许其他用户访问和锁定该表，但是禁止排他锁锁定整个表。&&&& 2、行排他(row exclusive,rx)：与行共享模式相同，同时禁止其他用户在此表上使用共享锁。使用select...for update语句会自动应用行排他。& &&&& 3、共享(share,s)：共享锁将锁定表，仅允许其他用户查询表中的行，但不允许插入、更新或删除行。多个用户可以同时在同一张表中放置共享锁&&&&&&&&&&&&&&&&&&&&&& 即允许资源共享。但是这样极容易造成死锁。&&&& 4、共享行排他(share row exclusive,srx)： 执行比共享表锁更多的限制。防止其他事务在表上应用共享锁、共享行排他锁以及排他锁。&&&& 5、排他(exclusive,x)： 对表执行最大限制。除了允许其他用户查询该表的记录，排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。&&&& 示例2：演示如何以共享模式锁定表。&&&& lock table dept & 表分区的优点：&&&& 改善表的查询性能;表更容易管理;便于备份和恢复;提高。&&&& 注意：要分区的表不能具有long和long raw数据类型的列。&&&& 四种分区方法：&&&& 1、范围分区：根据表的某个列或一组列的值范围，决定将该在哪个分区上。&&&&&&& 语法如下：&&&&&&& 在create table语句后增加&&&&&&& partition by range(column_name)&&&&&&& (&&&&&&&&&& partition part1 value less than (range1) [tablespace tbs1],&&&&&&&&&& partition part2 value less than (range2) [tablespace tbs2],&&&&&&&&&& ....&&&&&&&&&& partition partn value less than (maxvalue) [tablespace tbsn]&&&&&&& );&&&&&&& 其中：column_name是以其为基础创建范围分区的列，特定行的该列值称为分区键。&&&&&&&&&&&&& part1...partn是分区的名称。&&&&&&&&&&&&& range1...maxvalue是分区的边界值。&&&&&&&&&&&&& tbs1...tbsn是分区所在的表空间，tablespace子句是可选项。&&&&&&& 示例3：&&&&&&& create table t_emp &&&&&&& (&&&&&&&&& empno number(4), &&&&&&&&& ename varchar2(30), &&&&&&&&& sal number&&&&&&& ) &&&&&&& partition by range(empno) &&&&&&& (&&&&&&&&& partition e1 values less than (1000) tablespace emp1,&&&&&& 也可以&&&& than (to_date('','yyyy-mm-dd'))&&&&&&&&& partition e2 values less than (2000) tablespace emp2,&&&&&& 也可以&&&& than (to_date('','yyyy-mm-dd'))&&&&&&&&& partition e3 values less than (maxvalue) tablespace emp3&&&&&&& ); &&&& 2、散列分区：语法有两种如下&&&&&&& partition by hash(column_name)&&&&&&& partitions number_of_partitions [store in (tablespace_list)];&&&&&&& 或&&&&&&& partition by hash(column_name)&&&&&&& (&&&&&&&&&& partition part1 [tablespace tbs1],&&&&&&&&&& partition part2 [tablespace tbs2],&&&&&&&&&& ...&&&&&&&&&& partition partn [tablespace tbsn]&&&&&&& );&&&&&&& 其中：column_name是以其为基础创建散列分区的列。&&&&&&&&&&&&& number_of_partitions是散列分区的数目，使用这种方法系统会自动生成分区的名称。&&&&&&&&&&&&& tablespace_list指定分区使用的表空间，如果分区数目比表空间的数目多，分区将会以循环的方式分配到表空间中。&&&&&&&&&&&&& part1...partn是分区的名称。&&&&&&&&&&&&& tbs1...tbsn是分区所在的表空间，tablespace子句是可选项。&&&&&&& 示例4：自动分配4个散列分区，&&&&&&&&&&&&&& 可以使用select partition_name,high_value from user_tab_partitions where table_name=upper('t_emp')查询分区名&&&&&&& create table t_emp &&&&&&& ( &&&&&&&&& empno number(4), &&&&&&&&& ename varchar2(30), &&&&&&&&& sal number&&&&&&& ) &&&&&&& partition by hash (empno) &&&&&&& partitions 4;&&&& 3、复合分区：是范围分区和散列分区的结合。在创建复合分区时，先根据范围对数据进行分区，然后在这些分区内创建散列子分区。&&&&&&& 语法如下：&&&&&&& partition by range(column_name1)&&&&&&& subpartition by hash(column_name2)&&&&&&& subpartitions number_of_partitions [store in (tablespace_list)]&&&&&&& (&&&&&&&&&& partition part1 value less than (range1) [tablespace tbs1],&&&&&&&&&& partition part2 value less than (range2) [tablespace tbs2],&&&&&&&&&& ....&&&&&&&&&& partition partn value less than (maxvalue) [tablespace tbsn]&&&&&&& );&&&&&&& 其中：column_name1是以其为基础创建范围分区的列。&&&&&&&&&&&&& column_name2是以其为基础创建散列分区的列。&&&&&&&&&&&&& number_of_partitions是要创建的子分区的数目。&&&&&&&&&&&&& part1...partn是分区的名称。&&&&&&&&&&&&& range1...maxvalue是范围分区的边界值&&&&&&& 示例5：将雇员表先按照雇佣时间hiredate进行了范围分区，然后再把每个分区分为2个子hash分区，此表一共是6个分区。&&&&&&& create table t_emp &&&&&&& ( &&&&&&&&& empno number(4), &&&&&&&&& ename varchar2(30), &&&&&&&&& hiredate date&&&&&&& ) &&&&&&& partition by range (hiredate) &&&&&&& subpartition by hash (empno) &&&&&&& subpartitions 2 &&&&&&& (&&&&&&&&& partition e1 values less than (to_date('','yyyymmdd')), &&&&&&&&& partition e2 values less than (to_date('','yyyymmdd')), &&&&&&&&& partition e3 values less than (maxvalue)&&&&&&& ); &&&& 4、列表分区：此分区允许用户明确地控制行到分区的映射。&&&&&&& 语法如下：&&&&&&& partition by list(column_name)&&&&&&& (&&&&&&&&&& partition part1 values (values_list1),&&&&&&&&&& partition part2 values (values_list2),&&&&&&&&&& ....&&&&&&&&&& partition partn values (default)&&&&&&& );&&&&&&& 其中：column_name是以其为基础创建列表分区的列。&&&&&&&&&&&&& part1...partn是分区的名称。&&&&&&&&&&&&& values_list是对应分区的分区键值的列表。&&&&&&&&&&&&& default关键字允许存储前面的分区不能存储的记录。&&&&&&& 示例6：&&&&&&& create table t_emp1 &&&&&&& ( &&&&&&&&& empno number(4), &&&&&&&&& ename varchar2(30), &&&&&&&&& location varchar2(30)&&&&&&& ) &&&&&&&&& partition by list (location) &&&&&&& (&&&&&&&&& partition e1 values ('北京'), &&&&&&&&& partition e2 values ('上海','天津','重庆'), &&&&&&&&& partition e3 values ('广东','福建'),&&&&&&& ); & 要查询表分区中的数据行：select * from table_name partition(p1);& 注：p1是分区名。& 分区维护操作：&&& 1、添加分区：alter...add partition语句用于在现有的最后一个分区之后添加新的分区。&&&&&& 示例7：演示如何将名为e4的新分区添加到示例3中创建的t_emp表。&&&&&& alter table t_emp add partition e4 values less than (3000);&&&&&& 在此请注意：上例公适用于已使用特定的键值定义了最后一个分区的表。如果要在表的开始或中间位置添加分区，或者最高分区的分区&&&&&& 边界是maxvalue，则应使用split partition语句。&&& 2、删除分区：使用alter table...drop partition语句。&&&&&& 示例8：演示了如何删除t_emp表的e4分区。删除分区时，分区中的数据也随之删除。&&&&&& alter table t_emp drop partition e4;& &&& 3、截断分区：使用alter table...truncate partition语句来截断分区，只删除表分区中的所有记录。&&&&&& 示例9：演示了如何删除t_emp表中e3的分区的所有记录。&&&&&& alter table t_emp truncate partition e3;&&& 4、合并分区：可以将范围分区或复合分区表的两个相邻分区连接起来。结果分区将继承被合并的两个分区的较高上界。&&&&&& 语法如下：&&&&&& alter table table_name merge partitions partitions_name,partitions_name into partition partition_&&&&&& 示例10：演示了如何将e1和e2合并成一个e2分区。&&&&&& alter table t_emp merge partitions e1,e2 into partition e2;&&& 5、拆分分区：使用split partition语句在表的开头或中间添加分区。拆分分区允许用户将一个分区拆分为两个分区。&&&&&& 语法如下：&&&&&& alter table table_name spliy partition partition_name at (value) into (partition partition1,partition partition2);&&&&&& 示例11：演示了如何将t_emp表中的e3分区拆分为e31和e32两个分区。&&&&&& alter table t_emp split partition e3 at (date '') into (partition e31,partition e32);&&& 6、重新命名拆分后的分区:&&&&&& alter table t_emp rename partition e31 to p3;&&&&&& alter table t_emp rename partition e32 to p4;& 可以查询字典视图user_tab_partitions来查看用户所创建的分区的详细信息& 示例12：select table_name,partition_name,high_value from user_tab_& & 可以查询字典视图dba_tab_subpartitions来查看用户所创建的子分区的详细信息& 示例13：select table_name,partition_name,subpartition_name from dba_tab_四、对象& 表、视图、序列、过程、函数、程序包，甚至其它同义词都可以创建同义词。& & 1、同义词：私有同义词、公有同义词。& 私有同义词只能被当前模式的用户访问。私有同义词名称不可与当前模式的对象名称相同。要在自身的模式创建私有同义词，& 用户必须拥有create synonym系统权限。要在其它用户模式创建私有同义词，用户必须拥有create any synonym系统权限。& 公有同义词可被所有的用户访问。要创建公有同义词，用户必须拥有create public synonym系统权限。& 创建私有同义词语法：& create [or replace] synonym [schema.]synonym_name for [schema.]object_& 其中：or replace表示在同义词存在的情况下替换该同义词。&&&&&&& synonym_name表示要创建的同义词的名称。&&&&&&& object_name指定要为之创建同义词的对象的名称。& 示例1：create synonym s_emp for scott.& 创建公有同义词语法：& create public synonym synonym_name for [schema.]object_& 示例2：create public synonym emp_syn from scott.& 可以查询字典视图user_synonyms来查看用户所创建的同义词的详细信息 & 删除同义词语法：drop synonyms synonym_ 删除公有同义词加上一个public& 此命令只删除同义词，不会删除对应的表。& 2、序列：是用来生成唯一、连续的整数的对象。序列通常用来自动生成主键或唯一键的值。& 创建序列语法如下：& create sequence sequence_name&&& [start with integer]&&& [increment by integer]&&& [maxvalue integer|nomaxvalue]&&& [minvalue integer|nominvalue]&&& [cycle|nocycle]&&& [cache interger|nocache];& 其中：start with是指定要生成的第一个序列号。对于升序序列，其默认值为序列的最小值。对于降序序列，其默认值为序列的最大值。&&&&&&& increment by是用于指定序列号之间的间隔。其默认值为1。如果integer为正值，则生成的序列将按升序排列，否则按降序排列。&&&&&&& maxvalue指定序列可以生成的最大值。&&&&&&& nomaxvalue这是默认选项，将升序序列的最大值设为10的27次幂，将降序序列的最大值设为-1。&&&&&&& minvalue指定序列的最小值。minvalue必须小于或等于start with的值，并且必须小于maxvalue。&&&&&&& nominvalue这是默认选项，将升序序列的最小值设为1，将降序序列的最小值设为-10的26次幂。&&&&&&& cycle指定序列在达到最大值或最小值后，将继续从头开始生成值。&&&&&&& nocycle这是默认选项。指定序列在达到最大值或最小值后，将不能再继续生成值。&&&&&&& cache使用cache选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号。&&&&&&& nocache此项则不会为加快速度而预先分配序列号。如果在创建序列时忽略了cache和nocache选项，oracle将默认缓存20个序列号。& 示例3：create sequence toys_seq&&&&&&&&&& start with 10&&&&&&&&&& increment by 2&&&&&&&&&& maxvalue 2000&&&&&&&&&& minvalue 10&&&&&&&&&& nocycle&&&&&&&&&& cache 30;& 访问序列：可以通过currval和nextval伪列来访问该序列的值。& 示例4：演示从序列toys_seq中选择值插入toys表中的toyid列。执行成功将会在该表的toyid列插入值"p10"和"p12"。&&&&&&&& insert into toys(toyid,toyname,toyprice) values('p'||toys_seq.nextval,'twenty',25);&&&&&&&& insert into toys(toyid,toyname,toyprice) values('p'||toys_seq.nextval,'magic pencil',75);& 示例5：演示如何查看序列当前值&&&&&&&& select toys_seq.& 更改序列：alter sequence命令用于设置或删除minvalue或maxvalue、修改增量值、修改缓存中的序列号的数目。& 修改序列语法如下：注意，不能修改序列的start with参数。在修改序列时，应注意升序序列的最小值应小于最大值。& alter sequence [schema.]sequence_name&&& [increment by integer]&&& [maxvalue integer|nomaxvalue]&&& [minvalue integer|nominvalue]&&& [cycle|nocycle]&&& [cache interger|nocache];& 示例6：演示如何设置一个新的maxvalue，并为toys_seq序列打开了cycle。&&&&&&& alter sequence toys_seq&&&&&&&&& maxvalue 5000&&&&&&&&&& 可以查询字典视图user_sequences来查看用户所创建的序列的详细信息& 删除序列语法：drop sequence toys_& 3、视图& 视图是存储的查询定义。& 创建视图的语法如下：& create [or replace] [force | noforce] view view_name[(alias,alias,...)] as& select_statement [with check option [constraint constraint]] [with read only];& 其中：or replace表示在该视图存在的话，将重新创建该视图。&&&&&&& force使用此关键字，则无论基表是否存在，都将创建视图。&&&&&&& noforce这是默认值。如果使用此关键字，则仅当基表存在时才创建视图。&&&&&&& view_name表示要创建视图的名称。&&&&&&& alias指定在视图里面列的名字，名字数目必须与视图所选择的表达式的数目相匹配。&&&&&&& select_statement表示select语句。&&&&&&& with check option此选项指定只能插入或更新视图可以访问的行。术语constraint表示为check option约束指定的名称。&&&&&&& with read only此选项确保不能在此视图上执行任何修改操作。& 示例7：演示创建一个名为ven_view的视图，该视图与vendor_master表具有相同的结构。&&&&&&& create view ven_view as select * from vendor_& 视图中的order by子句：以便在查询视图时即使不使用order by子句，结果集也会按指定的顺序排列行。& 示例8：create or replace view ven_view(编号,日期) as select orderno,odate from vendor_mas& & 创建带有错误的视图：使用force选项& 在以下情况下，oracle也会创建视图：& 视力定义的查询引用了一个不存在的表;视图定义的查询引用了现有表中无效的列;视图的所有者没有所需的权限。& 示例9：下面创建一个基于venmast的视图。但中并不存在名为"venmast"的表。& create force view ven as select *& 如果稍后创建名为venmast的表，可以使用al & 联接视图：& 示例：演示如何创建联接视图& create or replace view ven_ord_view as select vm.vencode,venname,orderno,odate,ostatus& from vendor_master vm,order_master om where vm.vencode=om.& 键保留表：在联接视图中，如果视图包含了一个表的主键，并且此文来自: 马开东博客
转载请注明出处 网址：
也是这个视图的主键，则这个键被保留，则这个表被称为键保留表。& ven_ord_view视图以vendor_master和order_master这两个表为基表。order_master表被视为键保留表，因为orderno既是order_master表& 的主键，也是视图的主键。而vendor_master表不被视为键保留表，因为vendor_master表的主键vencode是联接字段，不是视图的主键。& 示例10：此示例成功修改了记录，因为odate列属于键保留表order_master。对于非键保留表vendor_master的venname列的更新则是不允许的。& update wen_ord_view odate=odate+1 where vencode='v003';&&& 注意：运行此示例时，必须确保基表的主键已正确创建。& 可以查询字典视图user_updateble_columns来查看联接视图中可更新的列。& 可以查询字典视图user_views来查看用户所创建的视图信息。& 视图中的函数：视图可以使用单行函数(由数字、字符、日期组成)、分组函数和表达式。& 示例11：此示例用upper函数创建视图，注意使用函数或表达式时，应赋予列一个别名。& create view vendor_master_view as select vencode,upper(venname) vendor_name from vendor_ & 删除视图语法：drop view view_& 4、索引：&&&& 查询user_indexes可以获取有关用户已创建的索引的详细信息。&&&& 查询user_ind_partitions可以获取有关用户已创建的分区索引的详细信息。&&&& 查询user_ind_columns可以获取有关列(用户的索引是基于这些列创建的)的详细信息。& &&&& 唯一索引、组合索引、反向键索引、位图索引和基于函数的索引。&&&& 索引在逻辑上物理上都独立于关联表中的数据，在任何时候都可以创建或删除索引，而不会影响基表或其它索引。&&&& 创建普通索引的语法：&&&& create index index_name on table_name(column_list) [tablespace tablespace_name];&&&& 其中：index_name指所创建索引的名称。&&&&&&&&&& table_name表示为之创建索引的表名。&&&&&&&&&& column_list是在其上创建索引的列名列表，可以基于多列创建索引。&&&&&&&&&& tablespace_name为索引指定表空间。&&&& 示例12：演示如何在itemfile表的itemcode列上创建索引&&&& create index item_index on itemfile(itemcode);&&& &&&& alter index语句的rebuild选项可以用来重建现有的索引。该选项提供的性能要优于使用drop index和create index语句重新创建索引。&&&& 示例13：重建索引&&&& alter index item_&&&& 删除索引语法：drop index item_&&&& (1)唯一索引：此索引可以确保在定义索引的列中，表的任意两行的值都不相同。oracle自动为表的主键列创建唯一索引。&&&&&&& 可以使用create unique index命令明确地创建唯一索引。&&&& 示例14：在itemfile表的itemcode列上创建了一个名为item_index的唯一索引。&&&& create unique index item_index on itemfile(itemcode);&&&& (2)组合索引：组合索引是在表中的多个列上创建的索引。组合索引中列的顺序是任意的，不必是表中相邻的列。&&&&&&& 创建组合索引时，应注意定义中使用的列的顺序。通常，最频繁访问的列应放置在列表的最前面。&&&& 示例15：在itemfile表上创建了一个名为comp_index的组合索引，当查询该表的的where子句同时包含这两个列或只包含&&&&&&&&&&&& p_category列时，以下示例语句创建的索引将用于检索数据，但如果单独使用itemrate列，则索引不能用于检索数据。&&&& create index comp_index on itemfile(p_category,itemrate);&&&& (3)反向键索引：通常建立在一些值连续增长的列上，例如列中的值是是由序列产生的情况。&&&& 示例16：在itemfile表上创建了一个名为rev_index的反向键索引。注意使用reverse关键字。&&&& create index rev_index on itemfile(itemcode)& &&&& 示例17：使用关键字noreverse可以将反向键索引重建为标准索引。&&&& alter index rev_ind&&&& 注意：不能将标准索引重建为反向键索引。&&&& (4)位图索引：如果某个列的值重复超过一百次，则可以考虑在该列上创建位图索引。&&&& 示例18：itemcode是order_detail表中的低基数列，因为货物编码在大多数订单中都是重复的，因此适合在该列上创建位图索引。&&&& create bitmap index bit_ind1 on order_detail(itemcode);&&&& 位图索引不应当用在频繁发生的insert,update,delete操作的表上。位图索引最适合于和。&&&& (5)索引组织表：索引组织表与在一个或多个列上建立索引的普通表相似，但它无需为表和索引维护两个单独的存储空间，&&&&&&&&&&&&&&&&&&& 仅维护一个索引，该索引包含相应的已编码键值和与其关联的列值。&&&& 示例19：使用organization index子句来创建索引组织表。&&&& create table ind_org_tab&&&& (&&&&&&& vencode number(4) primary key,&&&&&&&&& 注意：primary key是创建索引组织表所必需的。不允许使用分区。&&&&&&& venname varchar2(20)&&&& )&&&&&&&& 索引组织表适合于通过主键来访问数据。&&&& &&&& (6)基于函数的索引：如果在where子句的算术表达式或函数中已经包含了某个列，则不会使用该列上的索引。不能在表达式包含任何&&&&&&& 聚合函数，lob列、ref列或包含lob或ref的对象类型上创建基于函数的索引。&&&& 示例20：venname是vendor_master表的一个列，用于存储供应商的姓名，假定所有供应商的姓名都以混合大小写的形式存储&&&&&&&&&&&& (如：john smith、dave jones、tony greig等等)，同时假定我们经常需要根据供应商的姓名来查询表的数据。由于&&&&&&&&&&&& 姓名是以混合大小写的形式存储的，因此可能很难给出姓名的正确大小写形式。可以创建如下索引：&&&& create index vn_ind on vendor_master(upper(venname));&&&& 示例21：演示如何使用前面创建的基于函数的索引检索数据。&&&& select * from vendor_master where upper(venname)='small';&&&& &&&& 要创建基于函数或表达式的索引，必须具有query rewrite系统权限。&&&& (7)索引中的分区：与对表进行分区类似，oracle也允许对索引分区。牵引分区可以存储在不同的表空间中。&&&&&&& 局部分区索引：oracle为表的每个分区建立一个独立的索引。&&&&&&& 示例22：先创建分区表&&&&&&& create table order_mast&&&&&&& (&&&&&&&&&& orderno number(4),&&& venname varchar2(20)&&&&&&& )&&&&&&& partition by range(orderno)&&&&&&& (&&& partition oe1 values less than(1000),&&&&&&&&&& partition oe2 values less than(2000),&&& partition oe3 values less than(maxvalue) );&&&&&&& 接着创建局部索引：&&&&&&& create index myind on order_mast(orderno)&&&&&&& &&&&&&& 全局分区索引：是指在分区表或非分区表上创建的索引。&&&&&&& 示例23：在上面创建的分区表上创建全局索引&&&&&&& create index glb_ind on order_mast(orderno) global partition by range(orderno) (&& partition ip1 values less than(1500),&&&& partition ip2 values less than(maxvalue) );&&&&&&& 在有3个分区的表上创建2个分区的索引。注意：不能在散列分区或子分区建立全局索引。&&&&&&& 全局非分区索引：全局分区索引是在分区表上创建的全局索引，它类似于非分区表上的索引，索引的结构不会被分割五、使用pl/sql& 构成pl/sql程序的基本单元是逻辑块(如过程、函数或匿名块)。& pl/sql共分为3个部分，具体如下：& 声明部分：声明块中使用的变量、游标和自定义异常。作用域仅限于它们所在的块。局部子程序也可以在pl/sql块的声明部分中声明。此部分为可选项。& 可执行部分：执行命令并操作在声明部分声明的变量和游标。pl/sql块的可执行部分可以嵌套子块，此部分为必选项。& 异常处理部分：处理执行块时引发的异常。pl/sql块的异常处理部分也可以嵌套子块。此部分为可选。& pl/sql语法如下：& [declare&&&& declarations]& begin&&&& executable statements& [exception&&&& exception handlers]&& 其中：declarations指声明部分。&&&&&&& executable statements指可执行语句。&&&&&&& exception handlers指处理错误的代码块。& & pl/sql中的一些复合符号的含义如下：& :=&&&&&&&&& 赋值操作符& ||&&&&&&&&& 连接操作符& --&&&&&&&&& 单行注释& /*,*/&&&&&& 多行注释& &&,&&&&&&&& 标签分隔符& ..&&&&&&&&& 范围操作符& **&&&&&&&&& 求幂操作符 & 1、声明变量语法如下：& variable_name data_type[(size)] [:=init_value];& 其中：variable_name表示变量的名称。&&&&&&& data_type表示变量的sql或pl/sql数据类型。&&&&&&& size指定变量的范围。&&&&&&& init_value指定变量的初始值。& 示例1：给变量赋值(两种方法)& declare&&& p_catg varchar2(20);&&& rate number(7,2);&&& rate_incr number(7,2);& begin&&& ...&&& rate_incr:=1.10;&&&&&&&&&&&&&&&&&&&&&&&& --第一种&&& select p_category,itemrate*rate_incr&&& &&& into p_catg,rate&&&&&&&&&&&&&&&&&&&&&&&& --第二种&&& from itemfile where itemcode='i201';&&& ...&& 2、声明常量语法如下：& variable_name constant data_type(size) :=& 示例2：声明常量&& declare&&& total constant number:=2000;& begin&&& dbms_output.put_line(total);&& 3、属性类型& %type：引用某个变量或列的数据类型来声明变量。& v_empno emp.empno%& 此代码声明了变量v_empno，它的数据类型与表emp中的empno列数据类型相同。& 赋值方法有2种见示例1。& & %rowtype：提供表示表中一行的记录类型。记录类型可以存储从表中选择或由游标提取的整行数据。& emp_rec emp%& 示例3：给%rowtype类型的赋值(变量名.引用表的列名)& emp_rec.empno=1234;& emp_rec.ename='andy';& emp_rec.job='manager';& ...& 4、条件控制语句& if...then语句：& if 条件 then&&&& 执行语句; && if...then...else语句：& if 条件 then&&&& 执行语句1;& else&&&& 执行语句2;&& if...then...elsif语句：& if 条件1 then&&&& 执行语句1;& elsif 条件2 then&&&& 执行语句2;& else&&&& 执行语句3;&& case语句：& 示例4：grade就是选择器。系统先计算选择器的值，然后匹配when子句的值输出相应的值，如果没有一个匹配，则执行else语句输出的值。& case '&grade'&&&& when 'a' then dbms_output.put_line('优异');&&&& when 'b' then dbms_output.put_line('优秀');&&&& when 'c' then dbms_output.put_line('良好');&&&& when 'd' then dbms_output.put_line('一般');&&&& when 'e' then dbms_output.put_line('较差');&&&& else dbms_output.put_line('没有此成绩');& 4、循环控制& 循环控制包括loop和exit语句，使用exit语句可以立即退出循环，使用exit when语句可以根据条件结束循环。& loop循环：无条件循环，此循环中必须使用exit或exit when语句以免陷入无限循环。& 示例5：要求用户输入学生所得分数。如果分数大于60，代码将显示消息"该生已通过"。& & begin& loop&&& if &marks&60 then&&&&&& dbms_output.put_line('该生已通过');&&&&&&&&&&&& & while循环：此循环根据条件执行语句& 示例6：用户输入一个数，如果大于0，就循环打印信息，直到这个数小于或等于0。&& & declare& num number:=&& i number:=1;& begin& while num&0& loop&&& dbms_output.put_line(i||'、===============^__^===============');&&& num:=num-1;&&& i:=i+1;&&& & for循环：在执行语句前，for循环中的循环次数是已知的。& 示例7：循环打印1到100之间的偶数。&& begin& for anan in 1..100& loop&& if anan mod 2=0 then&&&& dbms_output.put_line(anan);&&&&& 5、顺序控制& goto语句：无条件地将控制权转到标签指定的语句。& 示例8：循环1到100，如果num大于20就转到pro语句来打印相应消息。& begin& for num in 1..100& loop&&& if num&20 then&&&&&&&&&& &&pro&&& dbms_output.put_line('goto语句用法');&& & 6、动态sql语法如下：& execute immediate dynamic_sql_string [into define_variable_list] [using bind_argument_list];& 其中：dynamic_sql_string是动态sql语句字符串。&&&&&&& into子句用于接受select语句选择记录的记录值。&&&&&&& using子句用于绑定输入参数变量。& 示例9：执行一条带参数的select语句。&
& declare& sql_stmt varchar2(200);& emp_id number(4):=7566;& emp_rec emp%& begin&&& sql_stmt:='select * from emp where empno=:id';&&& execute immediate sql_stmt into emp_rec using emp_&&& dbms_output.put_line('职员编号：'||emp_rec.empno||'& 职员工资：'||emp_rec.sal);&& & 注意：execute immediate语句只能用于处理返回单行或没有返回的sql语句，要处理多行的动态sql请使用ref游标的open...for语句。& 7、异常处理& 预定义异常：& access_into_null&&&&&&&&&&& 在未初始化对象时出现& case_not_found&&&&&&&&&&&&& 在case语句中的选项与用户输入数据不匹配时出现& collection_is_null&&&&&&&&& 在给尚未初始化的表或数组赋值时出现& cursor_already_open&&&&&&&& 在用户试图重新打开已经打开的游标时出现。在重新打开游标前必须先将其关闭& dup_val_on_index&&&&&&&&&&& 在用户试图将重复的值存储在使用唯一索引的列中时出现& invalid_cursor&&&&&&&&&&&&& 在执行非法游标运算(如打开一个尚未打开的游标)时出现& invalid_number&&&&&&&&&&&&& 在将字符串转换为数字时出现& login_denied&&&&&&&&&&&&&&& 在输入的用户名或密码无效时出现& no_data_found&&&&&&&&&&&&&& 在表中不存在请求的行时出现。& storage_error&&&&&&&&&&&&&& 在内存损坏或pl/sql耗尽内存时出现& too_many_rows&&&&&&&&&&&&&& 在执行select into语句后返回多行时出现& value_error&&&&&&&&&&&&&&&& 在产生大小限制错误时出现。例如，变量中的列值超出变量的大小& zero_divide&&&&&&&&&&&&&&&& 以零作除数时出现& 示例10：预定义异常处理&& declare&&& name varchar2(5);& begin&&& select enam& exception&&& when too_many_rows then&&&&& dbms_output.put_line('返回多行');&& & 用户定义异常：& 示例11：接受用户输入的数字，如果数字在1到100之间，就打印消息，否则将引发my_exception异常。&&& & declare&&& num number:=&&&& my_& begin&&& if num not between 1 and 100 then&&&&& raise my_&&& else&&&&& dbms_output.put_line('输入的值界于1－100之间');&&&& exception&&& when my_exception then&&&&& dbms_output.put_line('输入的值超出界限');&& 引发错误：& 示例12：接受用户输入的数字，如果数字在1到100之间，就打印消息，否则将引发错误。&&& & declare&&& num number:=&&&& my_& begin&&& if num not between 1 and 100 then&&&&& raise my_&&& else&&&&& dbms_output.put_line('输入的值界于1－100之间');&&&& exception&&& when my_exception then&&&&& raise_application_error(-20001,'输入的值超出界限');&&&& 注意：错误号必须介于-20000和-20999之间的负整数。&六、游标管理& oracle中提供了两种游标类型，它们是：& 静态游标：静态游标是在编译时知道select语句的游标。静态游标又分为两种类型，隐式游标和显式游标。& ref游标：很多时候用户需要为游标使用的查询直到运行的时候才能确定，可以使用ref游标(引用游标)和游标变量满足这个要求。为了使用引用&&&&&&&&&& 游标，必须声明游标变量。有两种类型的ref游标，ref游标和弱类型ref游标。& 1、隐式游标：pl/sql为所有sql数据操纵语句(包括返回一行的select)隐式声明游标。用户不能直接命名和控制此类游标。隐式游标名为sql。& 隐式游标属性：& (1)、%found&&&&&&& 只有在dml语句影响一行或多行时，%found属性才返回true。& 示例1：如果有行被更新，sql%found就返回真并打印相应信息。& begin& update emp set sal=sal+8 where empno=7900;& if sql%found then&&& dbms_output.put_line('表已更新');& else&&& dbms_output.put_line('编号未找到');&&& (2)、%notfound&&&& 此属性与%found属性的作用正好相反。如果dml语句没有影响任何行，则%notfound属性返回true。& (3)、%rowcount&&&& 此属性返回dml语句影响的行数。如果dml语句没有影响任何行，则%rowcount属性将返回0。& 示例2：使用%rowcount属性。& begin& update emp set sal=sal+8 where empno=7900;& if sql%found then&&& dbms_output.put_line('表已更新,更新了'||sql%rowcount||'行');& else&&& dbms_output.put_line('编号未找到');&&& (4)、%isopen&&&&&& 此属性返回游标是否已打开的值。隐式游标的%isopen 属性始终为false。& & 2、显式游标& 使用显式游标的4个步骤是：& (1)、声明游标。& (2)、打开游标。& (3)、从游标中获取记录& (4)、关闭游标& & 声明显式游标语法：& cursor cursor_name [(parameter [,parameter]...)]& [return return_type] is select_& 其中：cursor_name指游标的名称。&&&&&&& parameter用于为游标指定输入参数。&&&&&&& return_type定义游标提取的行的类型。&&&&&&& select_statement指游标定义的查询语句。& 打开游标语法：& open cursor_name [(parameters)];& & 从游标中获取记录语法：& fetch cursor_& 其中：cursor_name指游标的名称。&&&&&&& variables是变量名。& 关闭游标语法：& close cursor_& 显式游标的属性如下：& (1)、%found&&&& 如果执行最后一条fetch语句成功返回行，则%found的值为true。& (2)、%notfound& 如果执行最后一条fetch语句未能提取行时，则%notfound的值为true。& (3)、%rowcount& 返回到目前为止游标提取的行数。在第一次获取之前，%rowcount为零。当fetch语句返回一行时，则该数加1。& (4)、%isopen&&& 如果游标已经打开，则返回true，否则返回false。& 示例3：在声明部分声明cur_emp游标，并带一个输入参数，然后在begin部分打开此游标。loop部分循环执行直到游标中的所有行提取完成，&&&&&&&& 显示所有员工工资大于输入参数值的员工信息。最后关闭游标。& declare& cursor cur_emp(c_sal in number) is&&& select * from emp where sal&c_& emp_rec emp%& e_sal emp.sal%type:=&工资;& begin& open cur_emp(e_sal);& dbms_output.put_line('工资高于'||e_sal||'的员工清单如下：');& loop&&& fetch cur_emp into emp_&&& exit when cur_emp%&&& dbms_output.put_line('员工编号：'||emp_rec.empno||&&&&&&&&&&&&&&&&&&&&&&&& '& 员工姓名：'||emp_rec.ename||&&&&&&&&&&&&&&&&&&&&&&&& '& 员工工资：'||emp_rec.sal);&& close cur_&& 使用显式游标删除或更新& 使用游标时，如果处理过程中需要删除或更新行，在定义游标时必须使用select...for update语句，& 而在执行delete和update时使用where current of子句指定游标的当前行。& 声明更新游标的语法：& cursor cursor_name is select_statement for update [of columns];& 在使用for update子句声明游标之后，可以使用以下语法更新行。& update table_name set column_name=column_value where current of cursor_& 注意：在声明游标时，update命令中使用的列也必须出现在for update of子句中。&&&&&&& 声明游标时，select语句必须只包括一个表，而且delete和update语句只有在打开游标并提取特定行之后才能使用。& 示例4：演示如何使用显式游标更新行& declare& new_& cursor cur_emp is& select sal from emp where sal&1000& begin&&& open cur_&&& loop&&&&& fetch cur_emp into new_&&&&& exit when cur_emp%&&&&& update emp set sal=sal+(1000-sal) where current of cur_&&&& close cur_&&& 3、循环游标& 可以使用循环游标简化显式游标的处理代码。循环游标隐式打开游标，自动从活动集获取行，然后在处理完所有行时关闭游标。& 循环游标自动创建%rowtype类型的变量并将此变量用作记录索引。& 语法如下：& for record_index in cursor_name[(parameters)]& loop&&& executable_statements&& 其中：record_index是pl/sql声明的记录变量，此变量的属性声明为%rowtype类型，作用域在for循环之内。&&&&&&& parameters是用于为游标指定输入参数。& & 循环游标的特性有：&& (1)、在从游标中提取了所有记录之后自动终止。&& (2)、提取和处理游标中的每一条记录。&& (3)、如果在提取记录之后%notfound属性返回true，则终止循环。如果未返回行，则不进入循环。 & 示例5：此示例声明了cur_emp游标，emp_rec是记录索引。在处理完游标中的所有记录之后，循环游标将终止。& declare& cursor cur_emp is&&& select empno,ename,& begin& for emp_rec in cur_emp& loop&&& dbms_output.put_line('员工编号：'||emp_rec.empno||&&&&&&&&&&&&&&&&&&&&&&&& '& 员工姓名：'||emp_rec.ename||&&&&&&&&&&&&&&&&&&&&&&&& '& 员工工资：'||emp_rec.sal);&& & 4、ref游标& 如果用户需要在运行的时候动态决定执行何种查询，可以使用ref游标和游标变量。& 用于声明ref cursor类型的语法为：& type ref_cursor_name is ref& cursor& [return record_type];& 其中：return语句为可选子句，用于指定游标提取结果集的返回类型。包括return语句表示是ref游标，&&&&&&& 否则是弱类型ref游标，可以获取任何结果集。& & 用于打开ref游标的语法如下：& open cursor_name for select_& 用于提取和关闭游标变量的语法与显式游标相似。& 示例6：此示例ref_cur_emp是一个弱类型ref游标类型，rec是自定义记录类型，refcur是游标变量，程序根据用户的输入显示相应的信息。& declare& type ref_cur_& refcur ref_cur_& type rec is record& (&&&& id emp.empno%type,&&&& name emp.ename%type,&&&& salary emp.sal%type& );& rec_& begin&&& if &工资&2000 then&&&&& open refcur for select empno,ename,sal from emp where sal&2000;&&& else&&&&& open refcur for select empno,ename,sal from emp where sal&=2000;&&&&&& loop&&&&& fetch refcur into rec_&&&&& exit when refcur%&&&&& dbms_output.put_line('员工编号：'||rec_emp.id||&&&&&&&&&&&&&&&&&&&&&&&& '& 员工姓名：'||rec_emp.name||&&&&&&&&&&&&&&&&&&&&&&&& '& 员工工资：'||rec_emp.salary);&&&&& & 实现动态sql的ref游标声明方法与普通的ref游标相同，只是在open时指定了动态sql字符串。& 打开动态sql的ref游标的语法如下：& open cursor_name for dynamic_select_string [using bind_argument_list];& 示例7：演示动态sql的用法。(返回结果集的动态sql)& declare&&& r_emp emp%&&& type cur_&&& cur cur_&&& p_salary number:=2500;& begin&&& open cur for 'select * from emp where sal&:1 order by sal desc'&&& using p_&&& dbms_output.put_line('薪水大于'||p_salary||'的员工有：');&&& loop&&&&& fetch cur into r_&&&&& exit when cur%&&&&& dbms_output.put_line('编号：'||r_emp.empno||&&&&&&&&&&&&&&&&&&&&&&&& '& 姓名：'||r_emp.ename||&&&&&&&&&&&&&&&&&&&&&&&& '& 工资：'||r_emp.sal);&&&&&七、子程序和程序包& 总结：&&& 子程序是命名的pl/sql块，可带参数并可在需要时随时调用。&&& pl/sql有两种类型的子程序，即过程和函数。&&& 过程用于执行特定的任务，函数用于执行任务并返回值。&&& 程序包是对相关类型、变量、常量、游标、异常、过程和函数的封装。&&& 程序包由包规范和包主体两部分组成。&&& 包规范是包的接口，包含公用对象及其类型。&&& 包主体实现包规范中的游标和子程序，包主体中的声明仅限于在包内使用。&&& 程序包中游标的定义分为游标规范和游标主体两部分。& 语法及示例：& 1、& 创建的语法：& create [or replace] procedure procedure_name&&& [(parameter_list)]&&& {is|as}&&&& [local_declarations]&&& begin&&&&& executable_statements&&& [exception&&&&& exception_handlers]&&& end [procedure_name];& 其中：procedure_name是过程的名称。&&&&&&& parameter_list是参数列表。&&&&&&& local_declarations是局部声明。&&&&&&& executable_statements是可执行语句。&&&&&&& exception_handlers是异常处理程序。& 示例1：演示创建过程(参数列表中为in参数赋予一个默认值，不能为out、in out参数赋予默认值)& create or replace procedure find_emp(emp_no in number:=7900)& as&&& empname varchar2(20);& begin&&& select ename into empname from emp where empno=emp_&&& dbms_output.put_line('雇员姓名是 '||empname);& exception&&& when no_data_found then&&&&& dbms_output.put_line('雇员编号未找到');& end find_& & 调用过程：execute procudure_name(parameters_list);& 也可以在过程里面调用，直接写上procudure_name而不必写execute。& 示例2：演示创建带out参数的过程& create or replace procedure test(value1 varchar2,value2 out number)& is&&&& begin&&& select sal into identity from emp where empno=value1;&&& if identity&2000 then&&&&&& value2:=1000;&&& else&&&&&& value2:=500;&&&&& 调用带out参数的过程：& declare&&& value2& begin&&& test('7900',value2);&&& dbms_output.put_line(value2);&& 示例3：演示创建带in out参数的过程& create or replace procedure swap(p1 in out number,p2 in out number)& is&&& v_& begin&&& v_temp:=p1;&&& p1:=p2;&&& p2:=v_&& 调用带in out参数的过程：& declare&&& num1 number:=100;&&& num2 number:=200;& begin&&& swap(num1,num2);&&& dbms_output.put_line('num1= '||num1);&&& dbms_output.put_line('num2= '||num2);&& 示例4：将过程的执行权限授予其他用户& grant execute on find_& grant execu& 将find_emp过程的执行权限授予给用户scott，将执行swap过程的权限授予所有用户。& & 删除过程语法：drop procedure procudure_& 2、函数& 定义函数的语法如下：& create [or replace] function function_name&&& [(parameter_list)]&&& return datatype&&& {is|as}&&&& [local_declarations]&&& begin&&&&& executable_statements&&& [exception&&&&& exception_handlers]&&& end [function_name];& 其中：function_name是函数的名称。&&&&&&& parameter_list是参数列表。&&&&&&& local_declarations是局部声明。&&&&&&& executable_statements是可执行语句。&&&&&&& exception_handlers是异常处理程序。& 使用函数时注意：形式参数必须只使用类型，不得使用pl/sql类型。函数的返回类型也必须是类型。& 函数不能单独执行，只能通过sql语句或pl/sql程序块来调用。& 示例5：演示如何创建函数& create or replace function fun_hello& return varchar2 is& begin&&& return '朋友，您好';&& 调用函数：select fun_& 函数的授权：同过和的授权一样具体请看示例4。& 删除函数：drop function function_name&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 过程和函数的差异&&&&&&&&&&&& 过程&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 函数&& 作为pl/sql语句执行&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 作为表达式的一部分调用&& 在规范中不包含return子句&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 必须在规范中包含return子句&& 不返回任何值&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 必须返回单个值&& 可以包含return语句，但是与函数不同，它不能用于返回值&&&&&&&&&& 必须包含至少一条return语句& 3、程序包& 创建包规范的语法：& create [or replace] package package_name& is|as& [public type and item declarations]& [subprogram specifications]& end [package_name];& 其中：package_name是包的名称。&&&&&&& public type and item declarations是声明类型、常量、变量、异常和游标等。&&&&&&& subprogram specifications声明pl/sql子程序。& & 示例6：演示创建程序包规范& create or replace package pack_op is&&& procedure pro_print_ename(id number);&&& procedure pro_print_sal(id number);&&& function fun_re_date(id number)&& 创建包主体的语法：& create [or replace] package body package_name& is|as& [public type and item declarations]& [subprogram bodies]& [begin&&&&& initialization_statements]& end [package_name];& 其中：package_name是包的名称。&&&&&&& public type and item declarations是声明类型、常量、变量、异常和游标等。&&&&&&& subprogram bodies是定义公共和私有pl/sql子程序。& 示例7：演示创建程序包主体& create or replace package body pack_op is&&& procedure pro_print_ename(id number) is&&& name emp.ename%& begin&&& select ename into name from emp where empno=&&& dbms_output.put_line('职员姓名：'||name);& end pro_print_& procedure pro_print_sal(id number) is&&& salary emp.sal%& begin&&& select sal into salary from emp where empno=&&& dbms_output.put_line('职员工资：'||salary);& end pro_print_&&& function fun_re_date(id number) return date is&&& bedate emp.hiredate%& begin&&& select hiredate into bedate from emp where empno=&&&& end fun_re_& end pack_& 示例8：调用程序包中创建的过程和函数& exec pack_op.pro_print_ename(7900);& exec pack_op.pro_print_sal(7900);& select pack_op.fun_re_date(7900)& 示例9：演示程序包中的游标&&& 创建包规范&&& create or replace package pack_emp is&&&&& cursor cur_emp return emp%&&&&& procedure pro_&&& end pack_&&& 创建包主体&&& create or replace package body pack_emp is&&&&& cursor cur_emp return emp%rowtype is&&&&&&& select *&&&&& procedure pro_cur is&&&&& rec_emp emp%&&& begin&&&&& open cur_&&&&& loop&&&&&&& fetch cur_emp into rec_&&&&&&& exit when cur_emp%&&&&&&& if rec_emp.sal&1000 then&&&&&&&&& dbms_output.put_line('员工工资：'||rec_emp.sal||'，需加倍努力争取提高工资');&&&&&&& elsif rec_emp.sal&=1000 and rec_emp.sal&2000 then&&&&&&&&& dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资一般，争取搞个部门经理做做');&&&&&&& else&&&&&&&&& dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资不错，争取搞个总经理做做');&&&&&&&&&&&&&& end pro_&& end pack_&& 调用程序包中的过程以调用程序包中的游标&& exec pack_emp.pro_&& 示例10：返回游标的子程序包(此程序包返回r_cur游标)&& create or replace package scott.pk_wt&& is&&& type &&& procedure p_wt(mycs out mytype);&& && create or replace package body scott.pk_wt&& is&&& procedure p_wt(mycs out mytype)&&& is&&& r_&& begin&&&& open r_cur for select *&&&& mycs:=r_&&&& end p_&& end pk_& 查询有关过程、函数和程序包的信息：user_objects视图& column object_name format a18& select object_name,object_type from user_objects where object_type in ('procedure','function','package','package body');八、和内置程序包& 总结：&&& 是当用户执行特定操作时自动执行的。&&& 由语句、限制和操作3个部分组成。&&& oracle中的分为行级、语句级、instead of、模式和级5种类型。&&& instead of只能应用于视图。允许用户修改不能直接使用dml语句修改的视图。&&& 内置程序包存储在中，用于扩展的功能。&&& dbms_output程序包允许用户从程序包、和中输出信息。&&& dbms_lob程序包用于操作blob、clob和bfile等数据类型。&&& 模式的触发事件语句有：create、alter、drop、grant、revoke和truncate等ddl语句。& 语法及示例：& 1、用于创建的语法：& create [or replace] trigger trigger_name& {before|after|instead of}& [or {insert|delete|update [of column[,column]...]}]& on [schema.]table_or_view_name& [referencing [new as new_row_name] [old as old_row_name]]& [for each row]& [when (condition)]& [declare&&& variable_declation]& begin&&&& [exception&&& exception_handlers]& end [trigger_name];& 其中：before和after 指在事件发生之前或之后激活。&&&&&&& instead of 如果使用此子句，表示可以执行代码来代替导致调用的事件。&&&&&&& insert、delete和update 指定构成事件的数据操纵类型，update还可以指定列的列表。&&&&&&& referencing 指定新行(即将更新)和旧行(更新前)的其他名称，默认为new和old。&&&&&&& table_or_view_name 指要创建的表或视图的名称。&&&&&&& for each row 指定是否对受影响的每行都执行，即行级，如里不使用此子句，则为语句级。&& &&&&&&& when 限制执行的条件，该条件可以包括新旧数据值的检查。&&&&&&& declare...end 是一个标准的pl/sql块。& 示例1：此救命演示在scott模式下创建，如果更新表emp时，comm的值不等于40就激活，将插入或更新值设置为0。& create or replace trigger biu_emp_deptno& before insert or update of deptno&&&&&&&&&&&&& 1& on emp&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 2&& 此3行为语句& for each row&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 3& when (new.deptno&&40)&&&&&&&&&&&&&&&&&&&&&&&&& 4&& 此行为限制& begin&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 6&&& :new.comm:=0;&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 7&& 此3行为操作&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 8& 示例2：先创建一个测试表，接着创建一个序列，然后创建一个自动生成id列的行级，并禁止更新表的id列。& create table test_trg(id number,name varchar2(20)); & create sequence seq_ & create or replace trigger bi_test_trg& before insert or update of id& on test_trg& for each row& begin&&& if inserting then&&&&&& select seq_test.nextval into :new.&&& else&&&&&& raise_application_error(-20020,'不允许更新id值!');&&&&& 示例3：为emp表创建了语句级。如果用户对表执行插入、删除或更新行的操作，将激活此。& create or replace trigger aiud_emp& after insert or update or delete& on emp& begin&&& if updating then&&&&& dbms_output.put_line('emp中的数据已更新');&&& elsif deleting then&&&&& dbms_output.put_line('emp中的数据已删除');&&& elsif inserting then&&&&& dbms_output.put_line('数据已插入emp');&&&&& 模式语法：& create or replace trigger trigger_name& {before|after} trigger_event& on schema& when (trigger_condition)& trigger_& 示例4：先创建一个存储信息的表，然后创建一个模式，在删除当前模式下的对象时激活。& create table table dropped_obj& (&&& obj_name varchar2(30),&&& obj_type varchar2(20),&&& drop_date date& );& create or replace trigger log_drop& after drop on schema& begin&&& insert into dropped_obj values(ora_dict_obj_name,ora_dict_obj_type,sysdate);& end log_& & 2、启用和禁用& alter trigger trigger_name {enable|disable};& 3、删除& drop trigger &trigger_name&;& 4、使用user_triggers视图& select trigger_name,table_name,trigger_type,triggering_event from user_& 5、内置程序包& dbms_output.put|put_line('msg');& dbms_random.random mod 100&& 返回一个8位的随机整数，该整数的正负也是随机的。& dbsm_random.string('x',10)&& 返回一个10位的随机ascii码字符。& chr(abs(dbms_random.value()));& 返回一个gbk码对应的汉字，gbk码介于4之间。& & 示例5：演示dbms_xmlquery和dbms_lob包的用法& declare&&&&&& xmlstr varchar2(32767);&&& line varchar2(2000);& begin&&& result:=dbms_xmlquery.getxml('select empno,ename from emp');&&& xmlstr:=dbms_lob.substr(result,32767);&&& loop&&&&& exit&&&&& line:=substr(xmlstr,1,instr(xmlstr,chr(10))-1);&&&&& dbms_output.put_line(line);&&&&& xmlstr:=substr(xmlstr,instr(xmlstr,chr(10))+1);&&&&& 使用utl_file包必须先将创建目录对象与的目录关联起来。& create directory test_dir as 'c:/develop';& grant read,write on directory test_九、备份与恢复简介& 总结：& 备份是对中数据的复制，它有两种类型：物理备份和逻辑备份。& 导致操作中止的故障包括4种类型：语句故障、用户进程故障、实例故障、和介质故障。& oracle提供了导入和导出实用程序来实现的逻辑备份。& 导出实用程序将中的数据备份成一个二进制文件，即导出转储文件。导入实用程序将备份文件中的数据导入到oracle中。& 导入和导出实用程序通过命令行参数、交互提示符和参数文件等方式来调用。& 导入和导出实用程序均提供4种方式的操作：表方式、用户方式、表空间方式和全部方式。& 可在两种方式下工作：非归档日志方式和归档日志方式。& 1、导出实用程序将中的对象定义和数据备份到一个二进制文件中，该文件称为导出转储文件(export dump files)，其默认扩展名是.dmp。& 导出实用程序语法：& exp username/password [parmeter=value]...& 可以使用以下命令显示导出实用程序的参数说明。& c:/& emp help=y& 2、导入实用程序将导出的转储文件导入。& 导入实用程序语法：& imp username/password [parmeter=value]...& 可以使用以下命令显示导入实用程序的参数说明。& c:/& imp help=y& 导入的方式取决于导出的方式，即如果是表方式导出，则必须以表方式导入。& & 示例1：查看所有有效的归档日志文件存储目录。& select dest_id,dest_name,status,destination from v$archive_dest where status='valid';
后序部分：如何删除表中的重复记录？ 例句： delete from table_name a where rowid & ( select min(rowid) from table_name b where b.pk_column_1 = a.pk_column_1 and b.pk_column_2= a.pk_column_2 ); rem =====================================================================================rem 1.查看所有用户：rem =====================================================================================& select * from dba_--sys和system用户有权限执行& select * from all_& select * from user_rem =====================================================================================rem 2.查看用户系统权限：rem =====================================================================================& select * from dba_sys_--系统权限有643个& select * from all_sys_& select * from user_sys_& rem =====================================================================================rem 3.查看用户对象权限：rem =====================================================================================& select * from dba_tab_& select * from all_tab_& select * from user_tab_rem =====================================================================================rem 4.查看所有角色：rem =====================================================================================& select * from dba_rem =====================================================================================& rem 5.查看用户所拥有的角色：rem =====================================================================================& select * from dba_role_& select * from user_role_rem =====================================================================================rem 6.查看用户有哪些权限rem ======================搜索此文相关文章： -根本语法此文来自: 马开东博客
网址: 站长QQ
上一篇：没有了
ORACLE-根本语法_数据库相关文章
数据库_总排行榜
数据库_最新
数据库_月排行榜
数据库_周排行榜
数据库_日排行榜
马开东博客专栏
企业软件/开发
硬件/嵌入开发
马开东博客专栏
应用服务器
软件工程/管理/测试
马开东博客专栏
Linux/Unix
马开东博客专栏
开发语言/框架
专题开发/技术/项目
马开东博客专栏
高性能开发
马开东博客专栏