Oracle使用手册(10页).doc

《Oracle使用手册(10页).doc》由会员分享，可在线阅读，更多相关《Oracle使用手册(10页).doc（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-Oracle使用手册-第 9 页 基本介绍1. 列左对齐，字符右对齐。2. 包含空值的数学表达式的值都为空值3. DESCRIBE +表明用在MANAGE中用来描述表结构。SELECT 语句详解ORACLE 中一个语句里面包含多个操作，这些操作并不是顺序执行的，因此，我们可以将Oracle语句拆分，逐步分析它的执行构成。下面以SELECT语句为例。表1SELECT 列名FROM 表名INTO 变量JOIN 表名ON 条件WHERE 条件表2GROUP BY 列名HAVINGORDER BY 列名SELECT 语句由于执行顺序不同，因此对数据的处理方式不同。我们可以把他们分为单行操作符，多行操

2、作符，表操作符。（不知道怎么称呼，姑且这么叫。）表一中除了SELECT外的所有函数都是单行操作符，因此后面必须和单行数据使用。表二中的函数+select是多行操作符，因此可以和单行数据，多行数据和分组函数一块儿使用。ORDER BY 是对整个表进行操作，因此可以称为表操作符。 因此WHERE 后面不能加分组函数，多行数值。由于SELECT 语句在执行过中有先后顺序，而且每一个函数都有相应的格式，因此SELECT 语句在写法上有一些要求。如上表，在SELECT 语句中，子查询的优先级别最高因此，先调用子查询，子查询的执行顺序与一般查询一样，但是子查询的返回值被不同函数调用时，应根据函数要求返回不

3、同的值。如在WHERE 语句中返回单行值，如果有多行，用ANY, ALL函数一一拆分。1SELECT与句中先执行子查询，2 在SELECT 语句顺序执行2.1 GROUP BY 和分组函数将原表内容进行分区，浓缩。由于使用的是原表，因此可以调用新表中没有出现的列。GROUP BY DEPTNO HAVING AVG(SAL)1000,是先按DEPTNO分组，再选择条件，条件中执行分组函数，因此HAVING 中可以有分组函数3执行SELECT语句，先执行3.1 join on WHERE逐条连接，进行条件删选，因此ON与WHERE 的条件可以互换位置。这样将两个表连接成一个表。同样是调用原表，因

4、此可以调用新表中没有出现的列。然后执行SELECT然后执行表函数ORDER BY 将表排序。（最后将单行数据赋值：into。）ORACLE单词及出处调试权限：GRANT DEBUG CONNECT SESSION TO SCOTT;DDL 数据定义语句 用来定义数据库对象（SESSION 会话, TABLE,VIEW,SEQUENCE,INDEX,PROCEDUE, SYNONYMUSER）的语句。包含CREATE, ALTER, DROP, COMMENT,TRUNCATE.直接提交，不可回滚，在pl/sql块中要用动态SQL :EXECUTE IMMEDIATE表示。如EXECUTE IM

5、MEDIATE CREATE TABLE bonus1 (id NUMBER, amt NUMBER);CREATE 创建 对数据库的对象创建表和视图 TABLE, VIEWCREATE TABEL CREATE OR REPLACE VIEW 表名( 列名 数据类型 DEFAULT 值 constraint 名字 约束类型， CONSTRAINT 名字 约束类型（类名）；用户 USERCREATE USER IDENTIFIED BY PASSWORDS;序列 SEQUENCE CREATE SEQUENCE 序名 INCREMENT BY 跳跃值 START WITH 初始值 MAXVAL

6、UE 最大值 NOCACHE/CACHE 存入内存数 NOCYCLE/CYCLE 是否循环索引 INDEXCREATE INDEX ON 表名（列名，可以多列）过程 PROCEDUECREATE PROCEDUE 过程名(参数)*参数在定义时不能指定长度。IS AS 定义内容；BEGIN 执行块；END;同义词 SYNONYMCREATE SYNONYM FOR 对象名。 ALTER 修改对数据库的对象进行修改，包括增加列，删除列，改名列，修改类中的数据类型。ALTER TABLE 表名 ADDDROPRENAMEMODIFY COLUMN；ALTER 也可以对用户进行修改ALTER USER

7、 IDENTIFIED BY NEW PASSWORD;FUNCTION 函数。单行函数 只对表的一行数据进行数据操纵，返回一个值的语句字符函数 UPPER/ LOWER/ INT(ION)CAP(字符串)CONCAT（字符A,字符B）等价于 字符A|字符 B ：连接SUBSTR （CHAR A，NUM M,NUM N） ： 截取 B如果不写默认为 返回从A中截取索引从M到N的字符串 * 在ORACLE中索引从1开始，且前后都是闭区间。LENGTH （字符串 A）返回字符串 A的长度INSTR（CHAR A，CHAR B ,NUM M，NUM N）后面两个默认为1。 返回从 A中从左边第M个字

8、符开始索引B字符在A中出现的第N次的索引。LPAD | RPAD 左|右填充 （CHAR A，NUM M,CHAR B）返回：填充了M长度字符，长度不足则用B补充，长度长了截取。TRIM （CHAR A FROM CHAR B）|（字符：表示修剪空格）：修剪 TRIM 从两端开始连续修剪B直到下个不是A。 RTRIM（CHAR A，CHAR B） 从右边连续修剪A直到下个不是B，不写默认是空格。单个字符有效 LTRIM（CHAR A，CHAR B）从左边连续修剪A直到下个不是B，不写默认是空格。单个字符有效REPLACE（CHAR A，CHAR B，CHRA C）：在A中将B替换成C *全部替

9、换TRANSLATE (CHAR A，CHAR B，CHRA C) ：变换将A的B每一个字符换成相应的C的索引相同的字符，C如果没有相应的，用NULL代替。数字函数ROUND (数字M，整数N) ：将M在小数点后N位处四舍五入TRUNC (数字M，整数N) ：将M在小数点后N位处截短N=小数点后第几位，如果N是复数，代表小数点左边相应的位数。Mod 取模（除数，被除数） 与java中的%一样，返回值的符号与除数保持一至。CEIL (数字M) ：取比M大的最小整数FLOOR（数字M）：取比M小的最大整数ABS(数字M) ：取M的绝对值SIN(数字M) ：取M的余弦；COS(数字M) ：取M的余弦

10、；POWER(数字M,数字N): 返回M的N次方。SQRT(数字M) 返回M的开方。SIGN(数字M) 返回M的符号，正数返回1，复数返回-1，0返回0。CASE WHEN A THEN B ELSE CEND CASE;选择。DECODE(A,B,CD,EF)选择。*DUAL 伪表。在验证函数时可以使用伪表。SELECT ROUND(4.3,1) FROM DUAL;DATE 日期日期函数DATE A-DATE B =A与B相差的天数。浮点型MONTHS_BETWEEN(DATE A,DATE B) A与B 相差的月份。返回浮点型整数。ADD_MONTHS(DATE A,NUM M) 往日期

11、A 中添加M个月份，返回新日期。NEXT_DAY(DATE A,DAY/DY/1.7)返回下个周几，可用1234567代表星期的第几天。中文ORACLE 中用星期X来代替DAY/DD;因此不能用DAY/DD;LAST_DAY(DATE A) 返回A 所在月的最后一天的日期；ROUND(DATE A，FIELD B) 返回A在B出的四舍五入后的日期。FIELD 默认是DDTRUNC(DATE A，FIELD B) 返回A在B出的截断后的日期。FIELD 默认是DD数据类型转化函数NUMBER 可以自动转化成字符串。TO_NUMBER(CHAR R)将R转化成数字，R必须是纯数字型，不能有空格TO

12、_CHAR(NUMBER,格式）格式中用0代表0.9（一定会显示），9代表数值（0在开头或者末尾不显示），可用美元符号$，用L代表当地货币符号TO_CHAR(DATE,格式,nls_date_language=AMERICAN）可以选择语言。TO_DATE(CHAR A,格式)日期格式 默认格式是 DD-MON-RR,一般自定义格式，不用默认的。YYYY代表四位数年份 ；YY代表后两个年份数；YEAR代表年份的英文拼写。1945=1945; 1945=45; 1945= NINETEEN FORTY-FIVEMM 代表月份数； MON 代表英文月份前三个字母；MONTH代表英文中月份09=09

13、； 09=9月（中文版）=SEP 09=9月（中文版）=SEPTEMBERDD代表天数； DY 代表英文周的前三个字母；DAY代表英文09=09； 09=MON; 09=MONDAYHH代表小时后面可以加24表示24小时制；MI代表分钟；SS代表秒.*用FM可以去掉日期前面的0；* 时间格式对大小写不敏感（MM和MI区分），在使用时必须放到中；*“”可以让oracle强制略过不和给定的字符，因此可以用YYYY”ANY ”DD”ANY”MM来表示时间空值转化函数 属于单行函数NVL（A,B）如果A 为空输出B，否则输出ANVL2(A,B,C) 如果A 为空输出B，否则输出CNVLIF(A,BF)

14、连续判断，直到输出非空。全为空还是输出空分组函数 将数行数值浓缩成一行数值输出，忽略空值。如果空值必须作为一列就必须用到去空函数。AVG(列名) 返回该列的平均值。MAX(列名) ：返回该列最大值。MIN（列名）；返回该列最小值COUNT(列名) ： 该列的数量SUM(列名)： 返回该列的总和GROUP BY(列，列。)HAVING 条件；*HAVING 中能使用组函数。因为GROUP BY 是对整个表的操纵，因此可以用分组函数。*WHERE 中不能使用组函数，SELECT WHERE 操纵单行数据，因此不能使用分组函数。*分组函数将数行数据浓缩成一行，因此如果存在分组函数，选出的类必须都浓缩

15、，因此没有组函数的列必须出现在group by语句中。表函数对整个表的数据DISTINCT.连接笛卡尔连接，等值连接，非等值连接，自连接，外连接（左连接，右连接，FULL连接）等值连接也可以用 表A JOIN 表B USING (列)，列不能用表点出,如果出现在SELECT 语句中也不能点出。因为在SELECT 语句中首先计算表的联合，该等值只有一个列，因此不能被点出，而使用where 列=列有两个列，因此可以点出。通用连接 A （LEFT/RIGHT） JOIN B ON +条件*由于分组函数的限制，要完全显示列，必须使用连接。子查询子查询要包含在括号内，将子查询放在比较条件的右侧子查询可以

16、返回一个值，一列值，多列值，多行值，甚至临时表。具体使用根据调用子查询的函数有关。例如 在WHERE 下调用子查询，由于WHERE 是单行函数，因此返回值只能有一行。因此 返回值中有多行时必须用其他函数筛选出一行。如ANY,ALLANY是所有行的任意一行，ALL是所有行的每一行。CONSTRAINT 约束（NOT NULL, PRIMARY KEY, FOREIGN KEY,UNIQUE,CHECK） 列级约束 在类的后面 CONSTRAINT 名字 约束类型 表级约束 在表的后面 CONSTRAINT 名字 约束类型（类名）NOT NULL 非空 只能作为列级约束；PRIMARY KEY 主

17、键约束 表名该类值唯一且不为空UNIQUE 唯一的 用在唯一约束时表示这列的值唯一；FOREIGN KEY 外键FOREIGN KEY (列名) REFERENCES 表名（列名）；外键约束必须连到主键上。REFERENCES 关联用在外键约束中，CONSTRAINT 名字 FOREIGN KEY (列名) REFERENCES 表名（列名）；外键约束必须连到主键上。CHECK 检查 用在where 语句中，WHERE CHECK (条件表达式)表示满足条件才输出。 用在CONSTRAINT 中列+CONSTRAINT 名字 CHECK(表达式) 表示该列值满足一定条件才能输入。如果是列约束其

18、中表达式中不能出现其他列的值（包括伪列和索引）。如果是表约束则能现出先列值CASCADE 级联 在外键约束中，ON DELETE CASCADE表示级联删除， ON DELETE SET NULL表示删除后子列设置为空（级联删除，与触发器同时运行是可能会造成死循环。）权限权限包括系统权限和对象权限。语法：GRANT/REVOKE;系统权限CREATE TABLE/VIEW/SESSION/PROCEDUR/SEQUENCE/INDEX/COMMENT调试权限。对象权限SELECT /UPDATE/EXECUTE/ DEBUG CONNECT SESSION /ON 表名ROLE 角色。建立一个

19、全体，可以为该全体整体赋予权限。GRANT/REVOKE 权限 TO用户名WITH ADMIN OPTION级联给予系统权限GRANT/REVOKE权限 ON 表名FROM 用户名WITH GRANT OPTION;级联收回对象权限。TCP/IP结构体组成体：Declare 申明Begin： 开始结构体；EXCEPTION:End；1.DECLARE 声明DECLARE 声明变量 变量名 数据类型： NUM NUMBER(8,0); 如果没有制定类型的长度，使用默认长度。PL/SQL 块中可以使用使用%TYPE,%ROWTYPE; 如：EMP. NUMBER%TYPE PL?SQL还支持布尔类

20、型的数据。DECLARE 中赋值变量名 数据类型：= 值 NUM NUMBER（8，0）=60；变量名 数据类型 DEFAULT值 NUM NUMBER（8，0）DEFAULT 60；BEGIN END;执行块赋值：在执行块中可以为变量赋值： 变量名 ：=值；SELECT 列，列 INTO变量名, 变量名WHERE 条件。*加粗部分不能改变顺序。返回值只能有一行。使用USING为绑定变量赋值：salUSING 值BEGIN +执行块执行块中的执行与java的执行很相似，但也有很多不同。1. 相似点：顺序执行：PL/SQL 也是顺序执行每一条语句。也从 选择语句，循环语句，跳转语句进行讲解。不同

21、点：不能在执行语句中声明变量（for除外），要使用的变量必须在declare中声明。pl/sql中没有来表示域，因此每个语句都是以关键字开头，以END;结束形成一个域，for循环域内声明的局部变量同样不能在域外面使用。2. 选择语句：case 。 else；IF ELSIF ELSE; IF 条件 case 判断体 THEN WHEN THENELSIF WHEN.THEN THEN ELSEELSE END CASE;ENDIF;循环语句，loop，while，for Loop 无条件循环LOOP循环体；END LOOP;为了避免死循环，会与exit when或者goto等跳转语句联用。FO

22、R循环 ：有限次数的循环FOR循环结构体： FOR 变量 IN 赋值范围 LOOP循环体；END LOOP;FOR 后面的条件可以申明一个变量，变量的数据类型与赋值范围一致，不需要显性给予数据类型。给变量逐一赋值in里面的值，当in里面的值用完后结束循环。For循环在游标遍历中最常见，其他由于条件限制，很少用。注意域对变量的限制。WHILE 循环 ：条件判断循环WHILE循环体 WHILE 条件 LOOP循环体；END LOOP;While循环中，条件如果为真则执行循环体，与java最相似的语句，功能也最强大。3. 跳转语句：EXIT WHEN 满足条件退出循环;GOTO 跳转到跳转点。4.

23、事务控制语句（TCL）SAVEPOINT ; ROLLBACK ; ROLLBACT TO SAVEPOINT 保存点名。EXCEPTION 块：相当于java中的catch块EXCEPTION WHEN 要捕获的异常名称THEN 执行操作。 RAISE_APPLICATION_ERROR(-20000到-20999，字符串)；同样，我们可以在DECLARE 中申明异常，在执行块中用RAISE 抛出异常。动态SQL语句。执行时才会生成，并生效的sql语句。一般用在执行是才能确定的SQL语句，或者pl/sql块中不能使用的语句，如DDL语句。 一： CREATE TABLE bonus1 (id

24、 NUMBER, amt NUMBER); Pl/SQL语句中不能使用DDL语句，所以用动态SQL； 二： sql_stmt := SELECT * FROM emp WHERE empno = :id; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id；：变量名，表示绑定变量。需要使用Using赋值，只有执行的时候，才能知道赋的值是多少，因此使用动态sql。游标CURSOR隐式游标系统在执行DML 语句是自动生成的游标，我们不能打开或者关闭，也不能查看。SQL%ROWCOUNT;SQL%FOUND;SQL%NOTFOUND;显式游标我们

25、声明并使用的游标。游标可以申明return值，确定游标中的数据类型。CURSOR 在DECLARE 中申明并赋值。 CURSOR游标名 IS 多列数值。在执行块中，用OPEN 游标名 打开游标,并将游标指向第一个值。用FETCH 游标名 INTO 变量，来将游标中的当前值取出，并使游标指向下一值。用CLESE 游标名 关闭游标。游标名%FOUND，游标名%NOTFOUND显示反映游标上一个指向值是否存在。因此，必须放在FETCH INTO 语句后面。FOR循环在游标中的使用 FOR 变量 IN 游标名FOR 简写了游标，不需要OPEN 和CLOSE 游标，也不需要FETCH其中变量与游标的数据

26、类型一致，不能给变量确定数据类型必须用变量.列名返回值。如：CURSOR CS IS SELECT DEPTNO FORM DEPT;FOR NUM1 IN CS LOOPLINE();END LOOP;END;REF 游标由于ORACLE支持动态sql，因此有时候，在程序执行前并不能给游标具体赋值，这个时候我们可以用动态游标 REN CURSOR;DECLARE中，TYPE 类型名 is REF CURSOR; 对象名 类型名；并不需要赋值。BEGIN 赋值并指向第一个值：OPEN 对象名 FOR 多行值代码。 其余用法与游标一直。存储过程，函数。PROCEDURE,FUNCTION.存储在数据库中的代码块。记住格式就可以了触发器TRIGGER;TRIGGER 也是存储在数据库中的代码块，只能自动调用，不能显性调用。触发器触发器不能有参数，