Oracle数据库应用

1、Oracle 数据库应用-Oracle PL/SQL编程SQLSQL及及PL/SQLPL/SQL简介简介SQLSQL语言基础语言基础 PL/SQLPL/SQL编程编程21.1 SQL简介SQL是Structure Query Language（结构化查询语言）的简称，是用户与数据库交流所需要的标准语言1986年，ANSI（American National Standards Institute）数据库标准委员会推出第一个SQL语言标准SQL-86SQL-89，SQL-92，SQL-99SQL-2003，SQL-2006，SQL-2008 XML、window函数、Merge语句 Oracle

2、完全遵从ANSI的SQL标准，Oracle9i/Oracle10g集成了SQL-99标准SQL是应用程序与数据库进行交互操作的接口。它将数据查询（Data Query）、数据操纵（Data Manipulation）、数据定义（Data Definition）和数据控制（Data Control）功能集于一体，从而使得应用开发人员、数据库管理员、最终用户都可以通过SQL语言访问数据库，并执行相应操作31.1.1 SQL语言特点 SQL采用集合操作方式，对数据的处理是成组进行的。通过使用集合操作方式，可以加快数据的处理速度。 执行SQL语句时，每次只能发送并处理一条语句。 执行SQL语句时，用户

3、只需要知道其逻辑含义，而不需要知道SQL语句的具体执行步骤。例如，在使用WHERE子句检索数据时，用户可以取得所需要的记录，而这些记录如何存储、如何检索不需要用户进行干预。 使用SQL语句时，既可以采用交互方式（例如SQL*Plus），也可以将SQL语句嵌入到高级语言中执行（例如C，C+，Java） 41.1.2 SQL语言分类 数据查询语言（SELECT）语句：用于检索数据 数据操纵语言（Data Manipulation Language，DML）：用于改变数据库数据，包括INSERT,UPDATE,DELETE 数据定义语言（Data Definition Language，DDL）：用

4、于建立、修改和删除数据库对象，包括CREATE，ALTER，DROP等语句 数据控制语言（Data Control Language，DCL）：用于执行权限授予和权限收回操作，包括GRANT和REVOKE语句 事务控制语言（Transactional Control Language，TCL）：用于维护数据的一致性，包括COMMIT,ROLLBACK和SAVEPOINT语句51.1.3 SQL语言编写规则 SQL关键字不区分大小写 对象名和列名不区分大小写 字符值和日期值区分大小写 一条SQL语句可以单行或多行编写61.2 PL/SQL简介 PL/SQL是Oracle在标准SQL语言上的过程性

5、扩展 允许嵌入SQL语句 允许定义变量和常量 允许过程语言结构（条件分支语句和循环语句等） 一个PL/SQL块中可包含多条SQL语句和PL/SQL语句 允许使用例外处理Oracle错误等71.2.1 PL/SQL优点和特征提高应用程序的运行性能提供模块化的程序设计功能允许定义标识符具有过程语言控制结构支持面向对象编程支持异常处理功能8SQLSQL及及PL/SQLPL/SQL简介简介SQLSQL语言基础语言基础 PL/SQLPL/SQL编程编程9SQL语言基础 基本查询基本查询 常用函数常用函数 DMLDML语句语句 DDLDDL语句语句 DCLDCL语句和事务语句和事务102.1 基本查询 2

6、.1.1使用FROM子句指定表SELECT语句使用FROM子句指定查询中包含的行和列所在的表。在查询其他角色对应的方案中的表时，需要指定这个方案的名字可以在FROM子句中指定多个表，表与表之间用（,）隔开可以为表指定别名select * from gram pr, gramcontent pcwhere gramid=gramid112.1.2 使用SELECT指定列可以指定查询的表中所有列，在SELECT关键字后指定（*）。应尽可能少用通配符（*）。可以指定查询的表中的某些列而不是全部。这些列名跟在SELECT关键词后，列名与列名之

7、间用（,）隔开。可以使用列别名。如果要使用列别名，那么列别名应在列或表达式之后，在二者之间可以加AS关键字或空格。如果列别名有大小写之分，或者含特殊字符或空格，那么别名必须用双引号引住select gramid, gramcodefromicsp as programcode, p.price*0.001, p.price*0.001 price , p.price*0.001 价格, p.price*0.001 价 格(元) from program pwhere price0122.1.3 使用WHERE子句指定行运算符运算符比较：、=、=、!=、=确定范围：Betwe

8、en A and B、Not Between A and B 确定集合：IN、NOT IN字符匹配：LIKE、NOT LIKE空值：IS NULL、IS NOT NULL多重条件：AND、OR、NOT 132.1.3 使用WHERE子句指定行like字符匹配：Like、Not Like通配符： % 匹配任意字符串 _ 匹配任意一个字符大小写敏感列出姓张的学生的学号、姓名Select sno，sname From Student Where sname LIKE 张%列出张姓且单名的学生的学号、姓名 ?转义符转义符 escapeselect table_name,tablespace_name,

9、num_rows from user_tableswhere table_name like %_% escape 142.1.4 使用ORDER BY子句 ORDER BY子句对SELECT语句检索到的数据进行排序SELECT column_name_1, , column_name_nFROM table_name_1, , table_name_nWHERE ORDER BY column_name_1, , column_name_n 使用该语句，首先根据column_name_1列进行排序，如果出现该列中有多个相同的数据，则根据column_name_2列进行排序。 默认根据指定的列

10、进行升序排序。要按降序排序，则可以在列名后加上关键词DESC（descend缩写）。升序在列名后加关键词ASC（ascend缩写），升序是默认的排序法，一般不必加关键词。152.1.5 使用DISTINCT 使用DISTINCT检索唯一的表列值，即去掉了重复值。select distinct arealevel from area;2.1.6 使用算术运算符 SQL语句中使用算术运算符(+, -, *, /)。与平常使用算术表达式的方式一样，SQL语句中带有运算符的表达式同样是从左到右按照乘除优先加减的顺序进行运算 162.1.7 处理NULL值NULL表示未知值，它既不是空格也不是表示未知值

11、，它既不是空格也不是0。需注意的是，当表达式。需注意的是，当表达式(算术运算表达式算术运算表达式)包包含含NULL时，其结果也是时，其结果也是NULLselect mod(18,4), mod(18,null), 3+null, 3-null,3*null,3/null from dual; MOD(18,4) MOD(18,NULL) 3+NULL 3-NULL 3*NULL 3/NULL- - - - - - 2 使用使用NVL函数函数NVL函数用于将函数用于将NULL转变为实际值，其语法格式为转变为实际值，其语法格式为NVL(expr1,expr2)。如果如果expr1是是null，则返

12、回，则返回expr2；如果；如果expr1不是不是null，则返回，则返回expr1。参。参数数expr1和和expr2可以是任意数据类型，但二者的数据类型必须匹配可以是任意数据类型，但二者的数据类型必须匹配select ename,sal,comm, sal+comm, sal+nvl(comm,0) 月收入 from emp; ENAME SAL COMM SAL+COMM 月收入- - - - -SMITH 800.00 800ALLEN 1600.00 300.00 1900 1900MARTIN 1250.00 1400.00 2650 2650172.2 常用函数2.2.1字符函数

13、字符函数ASCII(c1): c1为字符串，该函数返回第一个字符的ASCII码，它的逆函数是CHR()CHR(i): i是一个数字，函数返回十进制表示的字符CONCAT(c1,c2): c1,c2都为字符串，函数将c2连接到c1的后面，如果c1为null，将返回c2，如果c2为null，则返回c1，如果c1、c2都为null，则返回null。和操作符|相同。INITCAP(c1): c1为一个字符串。函数将每个单词的第一个字母大写其他字母小写返回。单词由空格，控制字符，标点符号限制。182.2.1 字符函数INSTR(c1,c2,i,j)其中，c1,c2均为字符串，i,j为整数。函数返回c2在

14、c1中第j次出现的位置，搜索从c1的第i个字符开始。当没有发现需要的字符时返回0，如果i为负数，那么搜索将从右到左进行，但是位置的计算还是从左到右，i和j的默认值为1。INSTRB(c1,c2,i,j)与INSTR()函数一样，只是它返回的是字节，对于单字节INSTRB()等于INSTR()。LENGTH(c1)其中，c1为字符串，函数返回c1的字符长度，如果c1为null，将返回null值。LENGTHB(c1): 返回字符串c1的字节长度。192.2.1 字符函数LPAD(c1,i,c2): c1，c2均为字符串，i为整数。在c1的左侧用c2字符串补足至长度i，可多次重复，如果i小于c1的

15、长度，那么只返回i那么长的c1字符，其他的将被截去。c2的默认值为单空格。RPAD(c1,i,c2): c1，c2均为字符串，i为整数。在c1的右侧用c2字符 串补足至长度i，可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的默认值为单空格。LTRIM(c1,c2): 用于去掉字符串c1左端所包含的c2中的任何字符。c2默认为空格符RTRIM(c1,c2): 把c1中最右边的字符去掉，使其最后一个字符不在c2中TRIM(c1 from c2): 该函数用于从字符串的头部、尾部或两端截断特定字符，参数c1为要截去的字符，c2是源字符串。如果c1为空格，则可以省

16、略c1参数和from关键字。202.2.1 字符函数SUBSTR(c1,i,j)c1为一个字符串，i,j为整数，从c1的第i位开始返回长度为j的子字符串，如果j为空，则直到字符串的末尾。SUBSTRB(c1,i,j)与SUBSTR大致相同，只是i,j是以字节计算的。REPLACE(c1,c2,c3)c1,c2,c3都是字符串,函数用c3代替出现在c1中的c2后返回TRANSLATE(c1,c2,c3)将c1中与c2中相同的字符以c3代替。LOWER(c1): 返回c1的小写字符。UPPER(c1): 返回c1的大写字符。212.2.2 数字函数ABS(n): 返回n的绝对值。CEIL(n):

17、返回大于或等于n的最小整数select ceil(20.22222),ceil(20.88888),ceil(21.000) from dual; CEIL(20.22222) CEIL(20.88888) CEIL(21.000)- - - 21 21 21FLOOR(n): 返回小于等于n的最大整数select floor(20.22222),floor(20.88888),floor(21.000) from dual; FLOOR(20.22222) FLOOR(20.88888) FLOOR(21.000)- - - 20 20 21222.2.2 数字函数MOD(n1,n2): 返

18、回n1除以n2的余数POWER(n1,n2): 返回n1的n2次方ROUND(n1,n2): 返回舍入小数点右边n2位的n1的值， n2的默认值为0select round(20.22222,2),round(20.88888,2),round(21.000,2) from dual; ROUND(20.22222,2) ROUND(20.88888,2) ROUND(21.000,2)- - - 20.22 20.89 21232.2.2 数字函数SIGN(n)如果n为正数返回1，为负数返回-1，为0返回0TRUNC(n1,n2)该函数用于截取数字。如果省略n2，则将数字n1的小数部分截去；

19、如果n2是正数，则将数字n1截取至小数点后的第n2位；如果n2是负数，则将数字n1截取至小数点的前n2位例如：select trunc(22.889), trunc(22.889,2), trunc(22.889,-1) from dual; TRUNC(22.889) TRUNC(22.889,2) TRUNC(22.889,-1)- - - 22 22.88 20242.2.2 数字函数SIN(n)、SINH(n)、COS(n)、COSH(n)、 TAN(n)、 TANH(n)正弦函数、双曲正弦函数、余弦函数、双曲余弦函数、正切函数、双曲正切函数ASIN(n) 、 ACOS(n)、ATAN

20、(n)反正弦函数、反余弦函数、反正切函数EXP(n): 返回e的n次幂，e=2.71828183LN(n): 返回n的自然对数，n必须大于0LOG(n1,n2): 返回以n1为底n2的对数SQRT(n): 返回n的平方根252.2.3日期函数SYSDATE返回当前日期和时间 LAST_DAY(d)返回包含日期d的月份的最后一天。TRUNC(d,fmt)返回由fmt指定的单位的日期dselect sysdate,trunc(sysdate,yyyy), trunc(sysdate,mm), trunc(sysdate,dd) from dual;262.2.3日期函数ADD_MONTHS(d,i

21、)返回日期d加上i个月后的结果。i可以是任意整数。MONTHS_BETWEEN(d1,d2)返回d1和d2之间月的数目。select MONTHS_BETWEEN(to_date(2011-04-02,yyyy-mm-dd),to_date(2011-04-20,yyyy-mm-dd) MONTHS_BETWEEN1,from dual; MONTHS_BETWEEN1 -0.580645161290NEW_TIME(d1,tz1,tz2)该函数返回时区一的日期时间所对应的时区二的日期时间272.2.4 转换函数TO_CHAR(character)该函数用于将NCHAR,NVARCHAR2,C

22、LOB和NCLOB数据转变为数据库字符集数据，当用于NCHAR, NVARCHAR2和NCLOB类型时，字符串用单引号括起来，前面加上n。TO_CHAR(date,fmt,nlsparam)该函数用于将日期值转变为字符串，fmt用于指定日期格式，nls_param用于指定NLS参数。TO_CHAR(n,fmt,nlsparam)该函数用于将数字值转变为VARCHAR2数据类型。282.2.4 转换函数TO_CLOB(char)将字符串转变为CLOB类型。TO_DATE(c,fmt,nlsparam)将符合特定日期格式的字符串转变为DATE类型的值。TO_NUMBER(c,fmt,nlspara

23、m)将符合特定数字格式的字符串值转变为数字值。TO_MULTI_BYTE(c)将单字节字符串转变为多字节字符串。 TO_SINGLE_BYTE(c)将多字节字符串转变为单字节字符串。select to_multi_byte(abcd), to_single_byte() from dual; TO_MULTI_BYTE(ABCD) TO_SINGLE_BYTE()- - abcd292.2.4 转换函数CONVERT(c,dset,sset)c是字符串，dset、sset是两个字符集，函数将字符串c由sset字符集转换为dset字符集。HEXTORAW(x)x为16进制字符串，函数将16进制的

24、x转换为RAW数据类型。RAWTOHEX(x)用于将RAW数值转换为VARCHAR2的16进制字符串。ROWIDTOCHAR(rowid)用于将ROWID数值转换为VARCHAR2数据类型。302.2.5组函数AVG(ALL|DISTINCTexpr)该函数用于计算平均值。COUNT(ALL|DISTINCTexpr)该函数用于返回总计行数。MAX(ALL|DISTINCTexpr)该函数用于取得列或表达式的最大值。MIN(ALL|DISTINCTexpr)该函数用于取得列或表达式的最小值。 SUM(ALL|DISTINCTexpr)计算列或表达式的总和。STDDEV(ALL|DISTINCT

25、expr)取得标准偏差，标准偏差是方差的平方根。VARIANCE(ALL|DISTINCTexpr)返回列或表达式的方差。312.2.6 使用GROUP BY组函数用于对数据进行分组或分类，当在SELECT字句中使用组函数时，必须把分组列或者非“常数类”放在GROUP BY字句中GROUP BY字句中使用字句中使用HAVINGGROUP BY字句中使用字句中使用GROUP_ID()该函数用于区分分组结果中的重复行。GROUP BY字句中使用字句中使用GROUPING(expr)GROUP BY字句中使用字句中使用GROUPING_ID(expr1,exp2,)322.3 DML语句2.3.1

26、INSERT语句插入表数据语句插入表数据一般一般INSERT语句的使用语句的使用语法如下INSERT INTO owner.tabledb_link(column1,column2)Values(express1,express2)INSERT INTO owner.tabledb_link(column1,column2)subquery 332.3.2 UPDATE语句更新表数据UPDATE语句语法如下：UPDATE owner.table SET column1= express1 ,column2= express2WHERE condition342.3.3 DELETE语句删除表数

27、据DELETE语句语法如下：DELETE FROM owner.tableWHERE condition352.3.4 MERGE语句修改表数据使用MERGE语句能够在一个SQL语句中对表同时执行插入和更新操作。MERGE命令从一个或多个数据源中选择行来更新或插入一个或多个表。MERGE INTO products pUSING newproducts npON (duct_id=duct_id)WHEN MATCHED THENUPDATESET duct_name=duct_name; 362.3.5 使用TRUNCATE语句TRUNCATE T

28、ABLE与不含有WHERE子句的DELETE语句在功能上相同。但TRUNCATE语句具有以下优点：所用的事务日志空间较少 DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放用于存储表数据的数据页来删除数据，并且在事务日志中只记录页释放。使用的锁通常较少 当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。TRUNCATE TABLE 始终锁定表和页，而不是锁定各行。如无例外，在表中不会留有任何空页 执行 DELETE 语句后，表、索引会留下一些空页。372.4 DDL语句2.4.1 常用数据类型常用数据类型字符类型CHAR、V

29、ARCHAR2、NCHAR、NVARCHAR2、RAW数值类型NUMBER(P,S)、INTEGERP精度(138) S刻度(-84127)日期时间类型DATE、TIMESTAMPLOB类型CLOB、 NCLOB、 BLOB、BFILEROWID 类型ROWID、NROWIDLONG类型(已弃用)LONG、LONG RAW382.4.2 表1. 创建表创建表使用使用CREATE TABLE语句创建表语句创建表使用CREATE TABLE语句创建表基本语法如下：CREATE TABLE owner.table_name(column_name datatype default expr not

30、null| null constraint,column_name datatype default expr not null| null constraint)tablespace tablespace_name 392.4.2 表通过子查询建表通过子查询建表可以使用查询从基于一个或多个表中建立表，表的列的数据类型和大小由查询结果决定。可以选择其他表中所有列或部分列。在CREATE TABLE语句中使用关键字ASCREATE TABLE program_temp1 asselect * from program; 子查询建表的注意事项： 可以关连多个表及用集合函数生成新表,注意选择出来的字

31、段必须有合法的字段名称,且不能重复。 用子查询方式建立的表，只有NOT NULL的约束条件能继承过来, 其它的约束条件、索引和默认值都不会继承过来。 根据需要，可以用alter table add constraint 再建立其它的约束条件，如primary key等。 402.4.2 表2. 修改表修改表增加列增加列语法：ALTER TABLE owner. table_name ADD column_definition例如：ALTER TABLE orders ADD orderdate DATE;删除列删除列语法：ALTER TABLE owner. table_name DROP C

32、OLUMN column_names | (column_names)CASCADE CONSTRAINS例如：alter table emp drop column col_test;412.4.2 表更改列更改列语法：ALTER TABLE owner. table_name MODIFY column_name new_attributes;例如：ALTER TABLE orders MODIFY (quantity number(10,3),status varchar2(15);或ALTER TABLE orders MODIFY quantity number(10,3);ALTE

33、R TABLE orders MODIFY status varchar2(15);修改列的规则如下： 可以增加字符串数据类型的列的长度，数字数据类型列的精度 减少列的长度时，该列应该不包含任何值，所有数据行都为NULL 改变数据类型时，该列的值必须是NULL 对于十进制数字，可以增加或减少但不能降低他的精度422.4.2 表3. 更改表名更改表名语法：RENAME old_tablename TO new_tablename;例如：rename emp to emp11; 4. 删除表删除表语法：DROP TABLE owner. table_name CASCADE CONSTRAINTS

34、例如：drop table emp_temp1;432.4.3 主键创建表时定义主键创建表时定义主键例如：create table DEPT_TEMP(DEPTNO NUMBER(2) not null,DNAME VARCHAR2(14),constraint PK_DEPT_TEMP primary key (DEPTNO)using index tablespace USERS) tablespace USERS; create table DEPT_TEMP(DEPTNO NUMBER(2) constraint PK_DEPT_TEMP primary keyusing index

35、tablespace USERS,DNAME VARCHAR2(14) tablespace USERS;442.4.3 主键使用使用alter table语句定义主键语句定义主键语法：ALTER TABLE owner. tablenameADD CONSTRAINT constraint_name PRIMARY KEY (column1 ,column2,)例如：create table DEPT_TEMP (DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14) tablespace USERS;alter table DEPT_TEMPadd co

36、nstraint PK_DEPT_TEMP primary key (DEPTNO)using index tablespace USERS; 452.4.3 主键使用使用alter table语句修改主键状态语句修改主键状态语法：ALTER TABLE owner. tablename DISABLE CONSTRAINT constraint_name;ALTER TABLE owner. tablename ENABLE CONSTRAINT constraint_name;ALTER TABLE owner. tablename DISABLE PRIMARY KEY;ALTER TA

37、BLE owner. tablename ENABLE PRIMARY KEY;删除主键删除主键语法：ALTER TABLE owner. tablenameDROP CONSTRAINT constraint_name CASCADE462.4.4 外键创建表时定义外键创建表时定义外键语法：CREATE TABLE owner.table_name(/省略建表部分CONSTRAINT constraint_name FOREIGN KEY (column1 ,column2,)REFERENCES owner.table_name (column1 ,column2,)ON DELETE C

38、ASCADE | SET NULL ); 使用使用alter table语句定义外键语句定义外键语法：ALTER TABLE owner.table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column1 ,column2,)REFERENCES owner.table_name (column1 ,column2,)ON DELETE CASCADE | SET NULL472.4.4 外键使用使用alter table语句修改外键状态语句修改外键状态语法：ALTER TABLE owner. tablename DISABLE CO

39、NSTRAINT constraint_name;ALTER TABLE owner. tablename ENABLE CONSTRAINT constraint_name;删除外键删除外键语法：ALTER TABLE owner. tablename DROP CONSTRAINT constraint_name; 482.4.5 约束约束，也称为完整性约束，Oracle系统的完整性约束包括主键、外键以及CHECK,NOT NULL,Unique Index共5种类型Check约束约束CHECK约束可以为列级别上的约束，也可为一个表级别上的约束语法：CONSTRAINT constrain

40、t_name CHECK(condition)Not null约束约束Not null约束应用在单一的数据列上。唯一性约束唯一性约束唯一性约束要求一列或一组列中的每一个值都是唯一的，唯一性约束允许空值。使用CREATE UNIQUE INDEX语句创建唯一性索引492.4.6 索引索引是关系数据库中用于存放表中每一条记录位置的一种对象，主要目的是加快数据的读取速度和完整性检查创建索引创建索引创建索引语法如下CREATE UNIQUE INDEX owner. index_nameON owner. tablename (column1 ASC | DESC ,column2 ASC | DES

41、C,)PCTFREE nINITRANS nMAXTRANS nSTORAGE TABLESPACE tablespace_nameNOSORTONLINENOLOGGINGCOMPUTE STATISTICSPARALLEL 502.4.6 索引关键参数说明。PCTFREE：索引数据块空闲空间的百分比；INITRANS、MAXTRANS：指定初始和最大的事务表大小；STORAGE：存储参数；TABLESPACE：索引存储的表空间；NOSORT：不排序。默认情况下，在表中创建索引的时候，会对表中的记录进行排序，排序成功后再创建索引。但是当记录比较多时，这个排序作业会占用比较多的时间。特殊情况下

42、，可以使用该参数加快建索引的速度。 512.4.6 索引ONLINE：在线建索引。默认情况下，数据库系统是不允许DML操作与创建索引的操作同时进行的。使用ONLINE选项，DML操作与创建索引操作是可以同时进行，但是代价就是建索引的时间会延长。NOLOGGING: 是否需要记录日志信息。就是建索引时记不记日志，一般用在在大型表上建索引，使用该参数，默认是记日志。 COMPUTE STATISTICS: 是否更新统计信息。数据库在选择执行计划时，依据就是表、索引的统计信息，该参数会提示数据库建索引的同时，更新对应的统计信息。PARALLEL: 多服务进程创建索引。数据库服务器若是多CPU情况下，

43、使用该参数会增加并发，提高效率。522.4.6 索引创建索引例子：create index INDX_PROGRAM_SSP on PROGRAM (SHORTSPELL) tablespace MEDIADATA_INDX; create unique index UI_PROGRAM_CODE on PROGRAM (ICSPID, PROGRAMCODEFROMICSP) tablespace MEDIADATA_INDX; 删除索引删除索引语法：DROP INDEX owner. index_name532.4.7 视图创建视图创建视图语法：CREATE OR REPLACE VIEW

44、 view_name ASSELECT(一组语句)删除视图删除视图语法：DROP VIEW view_name542.4.8 序列创建序列创建序列语法：CREATE SEQUENCE owner.sequence_nameMINVALUE n |NOMINVALUEMAXVALUE n |NOMAXVALUESTART WITH nINCREMENT BY nCACHE n | NOCACHEORDER | NOORDER 552.4.8 序列修改序列修改序列语法：ALTER SEQUENCE owner.sequence_nameMAXVALUE n |NOMAXVALUEINCREMENT

45、 BY nCACHE n | NOCACHEORDER | NOORDER删除序列删除序列语法：DROP SEQUENCE owner.sequence_name562.4.9 同义词 对另一个数据对象而言同义词是一个别名。 public同义词是针对所有用户的，相对而言private同义词则只针对对象拥有者或被授予权限的账户。 在本地数据库中同义词可以表示表、视图、序列、过程、函数或包等数据对象，也可以通过链接表示另一个数据库的对象。 创建同义词创建同义词语法：CREATE OR REPLACE PUBLIC SYNONYM synonym_name FOR owner.objectdb_li

46、nk 删除同义词删除同义词语法：DROP PUBLIC SYNONYM synonym_name572.5 DCL语句和事务2.5.1 使用使用GRANT语句授权语句授权在Oracle 中，根据系统管理方式不同，将权限分为系统权限与实体权限。系统权限是指是否被授权用户可以连接到数据库上，在数据库中可以进行哪些系统操作。实体权限是指用户对具体的模式实体 (schema)所拥有的权限。例如：select any table是系统权限，它表示可以查看任何表。而select on table1是实体权限，表示对表table1的查询权限。 582.5.1 使用GRANT语句授权系统权限授权命令的使用系统

47、权限授权命令的使用GRANT 权限名 TO用户|角色|PUBLIC 其中，PUBLIC表示将权限赋给数据库中所有的用户 例如：赋给用户HITV权限CREATE TABLE的授权命令如下： GRANT CREATE ANY TABLE TO HITV; 实体权限授权命令的使用实体权限授权命令的使用GRANT 实体权限名|ALL TO 用户|角色|PUBLIC 其中，ALL表示实体的所有实体权限。 例如： GRANT SELECT ON PROGRAM TO HITV;592.5.2使用REVOKE语句回收授权 系统权限回收授权命令REVOKE 权限名 FROM用户|角色|PUBLICREVOKE

48、 CREATE ANY TABLE FROM HITV;实体权限回收授权命令REVOKE 实体权限名|ALL FROM 用户|角色|PUBLIC例如：REVOKE SELECT ON PROGRAM FROM HITV; 602.5.3 事务和锁 当执行事务操作（DML语句）时，Oracle会在被作用表上加表锁，以防止其他用户改变表结构；同时会在被作用行上加行锁，以防止其他事务在相应行上执行DML操作。在Oracle数据库中，为了确保数据库数据的读一致性，不允许其他用户读取脏数据（其他会话未提交事务）。 提交事务：使用COMMIT语句可以提交事务。执行DDL语句、 DCL语句自动提交事务 回退

49、事务： 使用SQL命令SAVEPOINT savepoint_name设置保存点设置保存点 使用命令ROLLBACK to savepoint_name回退到保存点 使用ROLLBACK回退事务61SQLSQL及及PL/SQLPL/SQL简介简介SQLSQL语言基础语言基础 PL/SQLPL/SQL编程编程62PL/SQL编程 PL/SQLPL/SQL编程基础编程基础 过程和函数过程和函数 触发器触发器 异常处理异常处理633.1 PL/SQL编程基础3.1.1 PL/SQL块结构块结构块是PL/SQL的基本程序单元，编写PL/SQL程序实际就是编写PL/SQL块。一个PL/SQL块中可以嵌套

50、其他PL/SQL块。编写PL/SQL应用模块，块的嵌套层次没有限制。PL/SQL块由三个部分组成：定义部分、执行部分、例外处理部分。其中，定义部分用于定义常量、变量、游标、例外、复杂数据类型等；执行部分用于实现应用模块功能，该部分包含了要执行的PL/SQL语句和SQL语句;例外处理部分用于处理执行部分可能出现的运行错误。643.1.1 PL/SQL块结构PL/SQL块的基本结构如下所示：DECLARE/*定义部分。 定义常量、变量、游标、例外、复杂数据类型等*/BEGIN/*执行部分。SQL语句和PL/SQL语句*/EXCEPTION/*例外处理部分。*/END; /*块结束标记*/ 653.

51、1.2 PL/SQL块分类匿名块命名块(以在块的开始和结束标示)子程序过程函数包触发器 663.1.3 PL/SQL词汇单元PL/SQL的词汇单元有分隔符、标识符、文字串和注释等四种。分隔符分隔符单分隔符 + % . / ( ) : , * “ = ; -组合分隔符:= = | /* */ . != = = - 673.1.3 PL/SQL词汇单元标识符标识符标识符用于指定PL/SQL程序单元和程序项的名称。通过使用标识符，可以定义常量、变量、例外、显示游标、游标变量、参数、子程序以及包的名称。标识符使用必须满足以下规则：标识符定义变量、常量时，每行只能定义一个标识符。标识符名称必须要以（AZ

52、,az）开始，并且最大长度为30个字符。如果以其他字符开始，则必须要使用双引号引住。标识符名称只能使用AZ，az，09，_，$和#。如果要使用其他字符，则必须要使用双引号引住。标识符名称不能使用Oracle关键字。683.1.3 PL/SQL词汇单元文本文本文本是指数字、字符、字符串、日期值或布尔值，而不是标识符。文本包括数字文本、字符文本、字符串文本、布尔文本、日期时间文本注释注释注释用于解释单行代码或多行代码的作用，从而提高了PL/SQL程序的可读性。注释包括单行注释和多行注释单行注释：使用-符号编写单行注释多行注释：使用/*/来编写多行注释693.1.4 变量和常量变量和常量声明变量和常

53、量声明变量就是由程序读取或赋值的存储单元。每个变量都有一个特定的类型与其关联，变量的类型定义了变量可以存放的信息类别。PL/SQL还支持用户自定义的数据类型，如记录类型、表类型等。变量名称需遵循以下规则：变量名必须以字母（az,AZ）开头；其后跟可选的一个或多个字母、数字（09）或特殊字符$、#或_；变量长度不超过30个字符；变量名中不能有空格。声明常量与声明变量一样，只是需要在前面加入CONSTANT，同时，将值赋给常量。例如：credit_limit CONSTANT number(10,2) := 5000.00;703.1.4 变量和常量变量属性变量属性使用%type属性定义变量当使用

54、%type属性定义变量时，它会按照数据库列或其他变量来确定新变量的类型和长度。示例如下：my_title books.title%type;使用%type声明变量my_title有两个优点，第一，不必知道title确切的数据类型；第二，如果改变了title的数据库定义，my_title的数据类型在运行时也会自动修改。使用%ROWTYPE属性声明描述表的行数据的记录示例如下：declaredept_rec dept%ROWTYPE; 713.1.5 表达式和运算符表达式表达式表达式是一个变量和文字的序列，用运算符进行分隔。表达式的数值由组成它的变量和文字的取值及运算符的定义来决定。例如：9+2*

55、5 (9+2)*5运算符运算符算数运算符+ - + - * /关系运算符 != = = = =逻辑运算符NOT AND OR其他符号 /* */ - | := . ; () 723.1.6 流程控制IF-THEN-ELSE语法如下：IF boolean_expression1 THENsequence_of_statements;ELSIF boolean_expression2 THENsequence_of_statements;ELSE sequence_of_statements;END IF; 733.1.6 流程控制CASE语法如下：CASE test_varWHEN value1

56、 THEN sequence_of_statements1;WHEN valuen THEN sequence_of_statementsn;ELSE else_sequence;END CASE;另一种形式语法：CASEWHEN boolean_expression1 THEN sequence_of_statements1;WHEN boolean_expressionn THEN sequence_of_statementsn;ELSE else_sequence;END CASE;743.1.6 流程控制LOOP语法如下：LOOP -sequence of statementsEND

57、LOOP;一般通过加入exit语句来终结循环。FOR-LOOP语法如下：FOR loop_counter IN low_bound.high_bound LOOP sequence_of_statements;END LOOP;loop_counter为循环变量，low_bound和high_bound为循环的最小、最大值，其中low_bound和high_bound可以动态定义。 753.1.6 流程控制WHILE-LOOP语法如下：WHILE condition LOOPsequence_of_statements;END LOOP;GOTO语法如下：GOTO label;其中，label

58、是用双箭头括号括起来的标记。GOTO不便于代码的理解和维护，尽量不用GOTO语句763.2 过程和函数3.2.1 过程过程创建过程创建过程创建过程的基本语法为：CREATE OR REPLACE PROCEDURE procedure_name ( argument IN | OUT | IN OUT type,argument IN | OUT | IN OUT type ) IS | AS procedure_body删除过程删除过程使用DROP PROCEDURE procedure_name 命令删除过程。773.2.2 函数1. 创建函数创建函数创建函数的基本语法为：CREATE O

59、R REPLACE FUNCTION function_name ( argument IN | OUT | IN OUT type,argument IN | OUT | IN OUT type ) RETURN datatype IS | AS function_body783.2.2 函数2. 函数调用及限制函数调用及限制函数可通过以下形式调用：作为表达式的一部分调用；SELECT命令的选择列表；WHERE和HAVING子句中；START WITH，ORDER BY和GROUP BY；INSERT命令的VALUES子句中；UPDATE命令的SET子句中。SQL语句中函数调用的限制：函数只

60、能带有输入参数，不能带输出参数和输入输出参数；函数只能使用SQL所支持的标准数据类型，而不能使用PL/SQL的特有数据类型（例如TABLE和RECORD等）；函数不能包含INSERT,UPDATE和DELETE语句。 3. 删除函数删除函数使用DROP FUNCTION function_name 命令删除函数793.3 触发器 触发器是指存放在数据库中，并被隐含执行的存储过程。 在Oracle8i之前，只允许基于表或视图的DML操作（INSERT，UPDATE和DELETE）建立触发器；而从Oracle8i开始，不仅支持DML触发器，也允许基于系统事件（启动数据库、关闭数据库、登录）和DDL