第9章Oracle数据查询

1、第第9 9章章 oracle oracle 数据查询数据查询9.1 9.1 pl/sqlpl/sql简介简介9.2 9.2 常用内置函数常用内置函数9.3 9.3 基本查询基本查询9.4 9.4 连接查询连接查询9.5 9.5 嵌套查询嵌套查询9.6 9.6 复合与分组查询复合与分组查询 9.1.1 9.1.1 pl/sqlpl/sql的基本结构的基本结构 9.1.2 9.1.2 常量、变量、表达式常量、变量、表达式 9.1.3 9.1.3 流程控制流程控制 9.1.4 9.1.4 异常处理异常处理9.1 pl/sql9.1 pl/sql简介简介9.1.1 pl/sql9.1.1 pl/sql

2、的基本结构的基本结构1. pl/sql块块pl/sql结构化的语言，其程序结构的基本单位结构化的语言，其程序结构的基本单位是是“块块”(block)。pl/sql块有三个部分：声明块有三个部分：声明部分、执行部分、异常处理部分。部分、执行部分、异常处理部分。declaredeclaration statementsbeginexecutable statementsexceptionexception statementsend pl/sqlpl/sql块中的每一条语句都必须以分号结束，块中的每一条语句都必须以分号结束，sqlsql语句可以多行，但分号表示语句的结束。一语句可以多行，但分号表示

3、语句的结束。一行中可以有多条行中可以有多条sqlsql语句，他们之间以分号分隔。语句，他们之间以分号分隔。每一个每一个pl/sqlpl/sql块由块由beginbegin或或declaredeclare开始，以开始，以endend结束。结束。9.1.1 pl/sql9.1.1 pl/sql的基本结构的基本结构9.1.1 pl/sql9.1.1 pl/sql的基本结构的基本结构2. 2. 块的命名和匿名块的命名和匿名pl/sqlpl/sql程序块可以是一个命名的程序块也可以是一个程序块可以是一个命名的程序块也可以是一个匿名程序块，匿名程序块可以用在服务器端也可以用匿名程序块，匿名程序块可以用在服

4、务器端也可以用在客户端。在客户端。执行部分包含了所有的语句和表达式，执行部分以关执行部分包含了所有的语句和表达式，执行部分以关键字键字beginbegin开始，以关键字开始，以关键字exceptionexception结束，如果结束，如果exceptionexception不存在，那么将以关键字不存在，那么将以关键字endend结束。分号分结束。分号分隔每一条语句，使用赋值操作符隔每一条语句，使用赋值操作符:=:=或或select intoselect into给每给每个变量赋值，执行部分的错误将在异常处理部分解决，个变量赋值，执行部分的错误将在异常处理部分解决，在执行部分中可以使用另一个在执

5、行部分中可以使用另一个pl/sqlpl/sql程序块，这种程程序块，这种程序块被称为嵌套块。序块被称为嵌套块。9.1.1 pl/sql9.1.1 pl/sql的基本结构的基本结构3. 3. 块的执行块的执行sqlsql* *plusplus中匿名的中匿名的pl/sqlpl/sql块的执行是在块的执行是在pl/sqlpl/sql块后输块后输入入“/”/”来执行。来执行。命名的程序与匿名程序的执行不同，执行命名的程序命名的程序与匿名程序的执行不同，执行命名的程序块必须使用块必须使用executeexecute关键字。关键字。9.1.2 9.1.2 常量、变量、表达式常量、变量、表达式1. 1. 变

6、量变量 声明变量声明变量variable_name datatype (width) := default variable_name datatype (width) := default expressionexpression 给变量赋值给变量赋值通常给变量赋值有两种方式：一是直接给变量赋值；二通常给变量赋值有两种方式：一是直接给变量赋值；二是通过是通过sql select intosql select into给变量赋值。给变量赋值。9.1.2 9.1.2 常量、变量、表达式常量、变量、表达式2. 2. 常量常量常量与变量相似，但常量的值在程序内部不能改变，常量与变量相似，但常量的值在

7、程序内部不能改变，常量的值在定义时赋予，声明方式与变量相似，但常量的值在定义时赋予，声明方式与变量相似，但必须包括关键字必须包括关键字constantconstant。9.1.2 9.1.2 常量、变量、表达式常量、变量、表达式3. 3. 有效字符集有效字符集 所有的大写和小写英文字母；所有的大写和小写英文字母； 数字：数字：0-90-9 符号：符号：( ) + - ( ) + - * * / = ! ; : . % , / = ! ; : . % , # & _ ? # & _ ? 9.1.2 9.1.2 常量、变量、表达式常量、变量、表达式pl/sqlpl/sql标识符的最大长度是标识符的

8、最大长度是3030个字符，并且不区分个字符，并且不区分字母的大小写。但是适当地使用大小写，可以提高字母的大小写。但是适当地使用大小写，可以提高程序的可读性。程序的可读性。4. 4. 运算符运算符pl/sqlpl/sql支持的运算符包括算术运算符、和关系运算支持的运算符包括算术运算符、和关系运算符和逻辑操作符。符和逻辑操作符。9.1.2 9.1.2 常量、变量、表达式常量、变量、表达式种类种类运算符运算符说明说明算术操作符算术操作符* * *指数指数* *, /, /乘、除乘、除+, -, |+, -, |加、减、连接加、减、连接关系操作符关系操作符= =等于等于, ! =, ! =不等于不等于

9、, , 大于、小于大于、小于=小于等于、大于等于小于等于、大于等于betweenbetween andand检索两值之间的内容检索两值之间的内容inin检索匹配列表中的值检索匹配列表中的值likelike检索匹配字符样式的数据检索匹配字符样式的数据is nullis null检索空数据检索空数据逻辑操作符逻辑操作符notnot取相反的逻辑值取相反的逻辑值andand两个值为真则结果为真两个值为真则结果为真oror只要有一个值为真结果就为真只要有一个值为真结果就为真9.1.3 9.1.3 流程控制流程控制1. 条件结构条件结构if条件判断逻辑结构条件判断逻辑结构if condition then

10、 statement end ifif condition then statements_1 else statements_2 end if9.1.3 9.1.3 流程控制流程控制if condition1 then statements_1 else if condition2 then statements_2 else statements_3end if9.1.3 9.1.3 流程控制流程控制1. 条件结构条件结构case 表达式表达式case 变量变量 when 表达式表达式1 then 值值1 when 表达式表达式2 then 值值2 when 表达式表达式3 then 值值

11、3 when 表达式表达式4 then 值值4 else 值值5end;9.1.3 9.1.3 流程控制流程控制2. 2. 循环控制循环控制loop exit endloop exit end循环控制语句循环控制语句looploop循环语句是其中最基本的一种。循环语句是其中最基本的一种。looploop语句的格式如下：语句的格式如下：looploop statements statementsend loop;end loop;这种循环语句是没有终止的，如果不人为控制的话，这种循环语句是没有终止的，如果不人为控制的话，其中的其中的statementsstatements将会无限地执行。一般可以

12、通过加将会无限地执行。一般可以通过加入入exitexit语句来终结该循环。语句来终结该循环。9.1.3 9.1.3 流程控制流程控制2. 2. 循环控制循环控制while loopwhile loop循环控制语句循环控制语句while loopwhile loop有一个条件与循环相联系，如果条件为有一个条件与循环相联系，如果条件为truetrue，则执行循环体内的语句，如果结果为，则执行循环体内的语句，如果结果为falsefalse，则，则结束循环。结束循环。 for loopfor loop循环控制语句循环控制语句for counter in reverse start_range end_

13、range for counter in reverse start_range end_range looploop statements; statements;end loop;end loop;9.1.3 9.1.3 流程控制流程控制countercounter是一个隐式声明的是一个隐式声明的变量，初始值是变量，初始值是start_rangestart_range，第二个值是，第二个值是start_range+1start_range+1，直到，直到end_rangeend_range。如果使用了。如果使用了reversereverse关键字，那么范围关键字，那么范围将是一个降序。将是

14、一个降序。9.1.3 9.1.3 流程控制流程控制3. goto3. goto语句语句goto label;goto label;执行执行gotogoto语句时，控制会立即转到由标签标记的语句时，控制会立即转到由标签标记的语句。语句。pl/sqlpl/sql中对中对gotogoto语句有一些限制，即在块、语句有一些限制，即在块、循环和循环和ifif条件结构中使用条件结构中使用gotogoto语句时不能从外层语句时不能从外层跳转到内层。跳转到内层。9.1.3 9.1.3 流程控制流程控制4. 4. 嵌套嵌套程序块的内部可以有另一个程序块这种情况称为嵌程序块的内部可以有另一个程序块这种情况称为嵌套

15、。嵌套要注意的是变量，定义在最外部程序块中套。嵌套要注意的是变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能将使用子块中定义的变量。子块中定义的变量不能被父块引用。同样被父块引用。同样gotogoto语句不能由父块跳转道子块语句不能由父块跳转道子块中，反之则是合法的。中，反之则是合法的。9.1.4 9.1.4 异常处理异常处理1. pl/sql的异常的异常异常处理块中包含了与异常相关的错误发生以及当

16、异常处理块中包含了与异常相关的错误发生以及当错误发生时要进行执行和处理的代码。错误发生时要进行执行和处理的代码。异常部分的语法一般如下：异常部分的语法一般如下：begin exception when excep_name1 then when excep_name2 then when others then end; 9.1.4 9.1.4 异常处理异常处理2. 2. 预定义异常预定义异常预定义异常是由运行系统产生的。例如，出现被预定义异常是由运行系统产生的。例如，出现被0 0除时，除时，pl/sqlpl/sql就会产生一个预定义的就会产生一个预定义的zero_dividezero_div

17、ide异常。异常。3. 3. 自定义异常自定义异常异常不一定必须是异常不一定必须是oracleoracle返回的系统错误，用户可以在返回的系统错误，用户可以在自己的应用程序中创建可触发及可处理的自定义异常，自己的应用程序中创建可触发及可处理的自定义异常，调用异常处理需要使用调用异常处理需要使用raiseraise语句。语句。异常情态的传播指的是当在程序块的声明、执行、异常异常情态的传播指的是当在程序块的声明、执行、异常部分分别出现异常情态时，或在本块中没有相应的异常部分分别出现异常情态时，或在本块中没有相应的异常处理器时会将这个异常情态传播到哪里，会去激发那个处理器时会将这个异常情态传播到哪里

18、，会去激发那个块中的处理器。块中的处理器。 9.2.1 9.2.1 数字函数数字函数 9.2.2 9.2.2 字符函数字符函数 9.2.3 9.2.3 日期函数日期函数 9.2.4 9.2.4 数据类型转换函数数据类型转换函数 9.2.5 9.2.5 集合函数集合函数9.2 9.2 常用内置函数常用内置函数9.2.1 9.2.1 数字函数数字函数函函 数数描描 述述abs(n)exp(n)mod(m,n)ceil(n)floor(n)round(n,m)trunc(n,m)sqrt(n)sign(n)求求n的绝对值的绝对值求求n的指数的指数求求m除以除以n的余数的余数返回大于等于返回大于等于n

19、的最小整数的最小整数返回小于等于返回小于等于n的最大整数的最大整数对对n做四舍五入处理，保留做四舍五入处理，保留m位位对对n做截断处理，保留做截断处理，保留m位位求求n的平方根的平方根n的值为正数、的值为正数、0或负数时分别返回或负数时分别返回1、0和和-19.2.2 9.2.2 字符函数字符函数函函 数数描描 述述lower(char)upper(char)initcap(char)concat(char1,char2)substr(char,start,length)length(char)ltrim(char)ascii(char)char(number)replace(char,str

20、1,str2)instr(char1,char2,start,times)将字符串将字符串charchar中的所有大写字母转换为小写字母中的所有大写字母转换为小写字母将字符串将字符串charchar中的所有小写字母转换为大写字母中的所有小写字母转换为大写字母首字母转换成大写首字母转换成大写连接字符连接字符返回字符串表达式返回字符串表达式charchar中从第中从第startstart开始的开始的lengthlength个字符个字符返回字符串表达式返回字符串表达式charchar的长度的长度去掉字符串表达式去掉字符串表达式charchar后面的空格后面的空格取取charchar的的asciias

21、cii值值取取numbernumber的的asciiascii字符字符将字符串中所有将字符串中所有str1str1换成换成str2str2在在char1char1字符串中搜索字符串中搜索char2char2字符串，字符串，startstart为执行搜索操为执行搜索操作的起始位置，作的起始位置，timestimes为搜索次数为搜索次数9.2.3 9.2.3 日期函数日期函数函函 数数描描 述述sysdatesysdatenext_day(day,char)next_day(day,char)last_day (day)last_day (day)add_months(day,n)add_mont

22、hs(day,n)months_between(day1,damonths_between(day1,day2)y2)round(day ,fmt)round(day ,fmt)trunc(day ,fmt)trunc(day ,fmt)返回系统当前日期和时间返回系统当前日期和时间返回返回dayday指定的日期之后并满足指定的日期之后并满足charchar指定条件的指定条件的第一个日期，第一个日期，charchar所指条件只能为星期几所指条件只能为星期几返回返回dayday日期所指定月份中最后一天所对应的日日期所指定月份中最后一天所对应的日期期返回返回dayday日期在日期在n n个月后个月后

23、(n(n为正数为正数) )或前或前(n(n为负数为负数) )的日期的日期返回返回day1day1日期和日期和day2day2日期之间相差的月份日期之间相差的月份按照按照fmtfmt指定格式对日期数据指定格式对日期数据dayday做舍入处理，默做舍入处理，默认舍入到日认舍入到日按照按照fmtfmt指定格式对日期数据指定格式对日期数据dayday做截断处理，默做截断处理，默认截断到日认截断到日9.2.4 9.2.4 数据类型转换函数数据类型转换函数函函 数数描描 述述to_charto_number to_date convertchartorowidrowidtocharhextorawrawt

24、ohexto_multi_byteto_single_byte将一个数字或日期转换为字符串将一个数字或日期转换为字符串将字符型数据转换为数字型数据将字符型数据转换为数字型数据将字符型数据转换为日期型数据将字符型数据转换为日期型数据将一个字符串从一种字符集转换为另一种字符将一个字符串从一种字符集转换为另一种字符 集集将一个字符串转换为将一个字符串转换为rowid数据类型数据类型将一个将一个rowid数据类型数据转换为字符串数据类型数据转换为字符串将一个十六进制字符串转换为将一个十六进制字符串转换为raw数据类型数据类型将一个将一个raw类型的二进制数据转换为十六进制表类型的二进制数据转换为十六进

25、制表 达的达的字符串字符串将一个单字节字符串转换为多字节字符串将一个单字节字符串转换为多字节字符串将一个多字节字符串转换为单字节字符串将一个多字节字符串转换为单字节字符串9.2.5 9.2.5 集合函数集合函数函函 数数 描描 述述avgcount max min sumstddevvariance计算一列值的平均值计算一列值的平均值统计一列中值的个数统计一列中值的个数求一列值中的最大值求一列值中的最大值求一列值中的最小值求一列值中的最小值计算一列值的总和计算一列值的总和计算一列值的标准差计算一列值的标准差计算一列值的方差计算一列值的方差 9.3.1 9.3.1 查询语句查询语句 9.3.2

26、9.3.2 简单查询简单查询 9.3.3 9.3.3 结果排序结果排序9.3 9.3 基本查询基本查询9.3.1 9.3.1 查询语句查询语句1. select 语句的格式语句的格式select all | distinct*| table_name | view_name | table_alias.*| column_name1,column_name2, . | expression as column_alias| column_alias=expression,.nfrom table_source9.3.1 9.3.1 查询语句查询语句where search_conditiong

27、roup by group_by_expressionhaving search_conditionorder by order_expressionasc | desc9.3.1 9.3.1 查询语句查询语句2. select中的子句描述：中的子句描述： from 子句子句select column_name1, column_name2,.nfrom table_name where条件子句条件子句select column_name1, column_name2,.nfrom table_name where search_condition9.3.1 9.3.1 查询语句查询语句 “*

28、”，all，distinct属性词用法属性词用法 select * | distinct from table_nameall表示输出所有记录，包括重复记录；表示输出所有记录，包括重复记录；distinct表示输出无重复结果的记录。表示输出无重复结果的记录。9.3.1 9.3.1 查询语句查询语句 order by子句子句 select column_name1, column_name2,.nfrom table_namewhere search_conditionorder by column_name1asc | desc, column_nameasc | desc, .9.3.1 9

29、.3.1 查询语句查询语句 group by 条件子句条件子句select column_name1, column_name2,.nfrom table_namewhere search_conditiongroup by group_by_expression9.3.1 9.3.1 查询语句查询语句 having子句子句 select column_name1, column_name2,.nfrom table_namewhere search_conditiongroup by group_by_expressionhaving search_condition9.3.1 9.3.1

30、查询语句查询语句 注意事项注意事项 列名列名(column_name)之间可以进行算术运算，之间可以进行算术运算，例如：例如：(column_name1* column_name2)/3 查询语句可以嵌套，例如：查询语句可以嵌套，例如：select from table_name1 where search_condition1 in (select from table_name1, table_name2, where search_condition2) ;9.3.1 9.3.1 查询语句查询语句 查询结果集的排序操作，默认的排序是升序查询结果集的排序操作，默认的排序是升序asc，降序是

31、，降序是desc。 每个表都有一个隐含的列每个表都有一个隐含的列rowid，它标记着，它标记着记录的唯一性。记录的唯一性。9.3.2 9.3.2 简单查询简单查询1查询指定列查询指定列在很多情况下，用户只对表中的一部分属性列感兴趣，在很多情况下，用户只对表中的一部分属性列感兴趣，这时可以通过在这时可以通过在select子句的目标列选项子句的目标列选项(column_name1,column_name2, .)中指定要查询的中指定要查询的属性。属性。2查询全部列查询全部列将表中的所有属性列都选出来，可以有两种方法。一种将表中的所有属性列都选出来，可以有两种方法。一种方法就是在方法就是在selec

32、t关键字后面列出所有列名。如果列关键字后面列出所有列名。如果列的显示顺序与其在基表中的顺序相同，也可以简单地将的显示顺序与其在基表中的顺序相同，也可以简单地将指定为指定为*。9.3.2 9.3.2 简单查询简单查询3. 查询满足条件的列查询满足条件的列查询满足指定条件的记录可以通过查询满足指定条件的记录可以通过where子句来子句来实现。实现。where子句对应于关系代数中的选择，使子句对应于关系代数中的选择，使where子句中的逻辑表达式返回子句中的逻辑表达式返回true值的记录，值的记录，是符合要求的记录，将被选择出来。是符合要求的记录，将被选择出来。where子句中常用的关键字符有：子句

33、中常用的关键字符有：9.3.2 9.3.2 简单查询简单查询比较包括比较包括、=、=、；范围包括范围包括between a and b与与not between a and b；集合包括集合包括in、not in；字符匹配包括字符匹配包括like、not like；空值主要有空值主要有is null、is not null；多重条件主要有多重条件主要有and、or、not；9.3.2 9.3.2 简单查询简单查询4. 消除结果中的重复行消除结果中的重复行all和和distinct也是也是select语句中的可选关键字，语句中的可选关键字，它们用于在你的查询结果中选择它们用于在你的查询结果中选择

34、all(缺省缺省)或者或者distinct。5. 使用使用select子句更名子句更名使用使用select子句在取出某个列值的同时，还可以用子句在取出某个列值的同时，还可以用汉字标题更改结果集中的列名，这样使得结果集更具汉字标题更改结果集中的列名，这样使得结果集更具可读性。可读性。9.3.3 9.3.3 结果排序结果排序order by是一个可选的子句，它允许你根据指定列是一个可选的子句，它允许你根据指定列的上升或者下降的顺序来显示查询的结果。的上升或者下降的顺序来显示查询的结果。使用使用order by子句来指定结果集中记录的排列次序子句来指定结果集中记录的排列次序时，可以使用表达式、列别名

35、或列序号时，可以使用表达式、列别名或列序号(即列在即列在select语句选择列表中的序号语句选择列表中的序号)等方式。需要注意的等方式。需要注意的是，在由是，在由union、intersect、minus等运算符组等运算符组成的复合查询中，只能使用列序号方式进行指定排序成的复合查询中，只能使用列序号方式进行指定排序列，列，order by子句也只能出现在最后一个复合查询子句也只能出现在最后一个复合查询语句中。语句中。 9.4.1 9.4.1 简单连接简单连接 9.4.2 9.4.2 外连接外连接9.4 9.4 连接查询连接查询9.4.1 9.4.1 简单连接简单连接简单连接的查询操作列出与连接

36、条件匹配的数据行，简单连接的查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。通常有三种：它使用比较运算符比较被连接列的列值。通常有三种： 等值连接：在连接条件中使用等于号等值连接：在连接条件中使用等于号(=)运算符比较运算符比较被连接列的列值，其查询结果中列出被连接表中的所被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。有列，包括其中的重复列。 不等连接：在连接条件中使用除等于运算符以外的不等连接：在连接条件中使用除等于运算符以外的其它比较运算符，来比较被连接的列的列值。这些运其它比较运算符，来比较被连接的列的列值。这些运算符包括算符包括、=、=、

37、!和和。 自然连接：在连接条件中使用等于自然连接：在连接条件中使用等于(=)运算符比较被运算符比较被连接列的列值，但它使用选择列表指出查询结果集合连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。中所包括的列，并删除连接表中的重复列。9.4.2 9.4.2 外连接外连接在简单连接查询中，返回到查询结果集合中的仅是符在简单连接查询中，返回到查询结果集合中的仅是符合连接条件合连接条件(where搜索条件或搜索条件或having条件条件)的数的数据行。与简单连接不同，外连接不仅返回符合连接条据行。与简单连接不同，外连接不仅返回符合连接条件的数据行，而且还返回一个连接

38、表中所包含的所有件的数据行，而且还返回一个连接表中所包含的所有其它数据行。其它数据行。在外连接中，选择一张表作为主表，此表中任何与另在外连接中，选择一张表作为主表，此表中任何与另一张表不匹配的行都被保留在结果中。外连接的运算一张表不匹配的行都被保留在结果中。外连接的运算符为符为(+)，应将该运算符放在非主表一边。在返回结，应将该运算符放在非主表一边。在返回结果中，所有不符合连接条件的数据行中的非主表中的果中，所有不符合连接条件的数据行中的非主表中的列值均为列值均为null。 9.5.1 9.5.1 单值嵌套查询单值嵌套查询 9.5.2 9.5.2 多值嵌套查询多值嵌套查询9.5 9.5 嵌套查

39、询嵌套查询9.5.1 9.5.1 单值嵌套查询单值嵌套查询一个一个select-from-where语句称为一个查询块。语句称为一个查询块。有时一个查询块无法完成查询任务，需要一个子有时一个查询块无法完成查询任务，需要一个子select的结果作为主查询语句的条件。将一个查询的结果作为主查询语句的条件。将一个查询块嵌套在另一个查询块的条件子句中的查询被称为嵌块嵌套在另一个查询块的条件子句中的查询被称为嵌套查询。嵌套查询一般由里向外进行处理。子查询可套查询。嵌套查询一般由里向外进行处理。子查询可以有多层，所存取的表可以是父查询没有存取的表，以有多层，所存取的表可以是父查询没有存取的表，子查询选出的记录不显示。子查询选出的记录不显示。嵌套查询可分为单值嵌套查询和多值嵌套查询。嵌套查询可分为单值嵌套查询和多值嵌套查询。子查询的返回结果是一个值的嵌套查询称为单值嵌套子查询的返回结果是一个值的嵌套查询称为单值嵌套查询。查询。9.5.2 9.5.2 多值嵌套查询多值嵌套查询子查询的返回结果不是一个值，而是一个集合的嵌套子查询的返回结果不是一个值，而是一个集合的嵌套查询称为多值嵌套查询。查询称为多值嵌套查询。1. 带有带有not in的嵌套查询的嵌套查询i