时间：2021-07-01 10:21:17 帮助过：18人阅读
6、在Oracle软件中，启动数据库，需要以管理员身份启动
7、查询当前系统时间
select sysdate from dual;
8、导入数据文件（sql语句表数据之类的）
因为数据文件默认语言环境为英语，当前系统是中文，中英文时间显示不一致，导致倒入失败，所以 需要先修改语言环境
alter session set nls_date_language=english;
alter session set nls_language=english;
然后开始导入文件使用：@ 文件全路径名 或者 start 文件全路径名
9、查看当前用户的所有表
select table_name from user_tables;
10、查看当前表结构
desc 表名字;
11、oracle服务意思 
1.OracleDBConsoleorcl oem控制台的服务进程
2.OracleJobSchedulerXE 定时器的服务进程
3.OracleOraDb10g_home1iSQL*Plus isql*plus的服务进程
4.OracleXETNSListener 监听器的服务进程
5.OracleServiceORCL 数据库服务进程
1、distinct关键字
distinct跟在select后面，代表去除重复的，这个重复是整体重复的。
select 子句后面指定要查询的列
from 后面跟要查询的表
2、select语句可以对指定的列的所有值进行算术运算。
语法：
select 要计算列的名字 运算符 数字from 表名;
3、取别名
select 列名 要修改的名字 from 表明
select 列名 as 要修改的名字 from 表明
4、使用||合并多个列之间添加字符串
要添加字符靠近列名的那一边需要加||，并且要添加的字符串需要用单引号
select ‘找到的id为：’||id||’ 找到的名字为’||last_name as Name from s_emp;
5、对空的值进行替换（替换的值一定是要同类型的）
select nvl(要判断是否有空值的列名,要替换列的同类型的值) from 表名
select nvl(要判断是否有空值的列名,不为空显示的值,为空显示的值) from 表名
1、在当前操作的命令行追加内容
a 追加的内容
2、在当前操作的命令行修改内容
c /命令行存在的内容/要被修改的内容
3、清空当前缓存的命令
clear buffer 清空当前缓存咋命令
4、删除当前操作行
del 要删除的行号
5、当前操作命令的下一行插入内容
i 直接i回车就可以在下一行添加内容
6、查看缓存命令
l 行号 ：查看指定行号的缓存内容
直接输入l显示的是缓存中所有的
7、输入系统终端命令
Linux环境下使用 ！后面跟着终端命令
Window环境下使用 后面跟终端命令例如调用window下的清屏使用cls
8、执行缓存命令
使用/就可以执行sql语句
9、修改对应行的内容
输入要修改行号，后面直接跟要修改内容就可以
1 要修改的内容
10、退出缓存输入
直接在要准备添加行回车就好
11、保存你缓存的命令到文件中
save 要保存的文件路径 append(这个代表追加到当前要保存的文件路径里面)
12、从文件提取到缓存
get 文件路径
13、执行文件中的sql命令
start 文件路径 或者 @ 文件路径
14、编辑文档
edit 文件路径 用系统默认的东西打开文件
15、保存你的sql语句和执行结果保存到文件（是追加保存的）
你从开始执行spool开始就开始记录了，知道spool off 关闭为止，所输入的所有东西在文件都有记录
spool 文件路径
sql1
result1
sql2
result2
…
spool off 关闭spool功能
1、定义要格式化的列或者给列起的名字
使用column关键字
colu 要修改的列名 format a15 //代表列显示的时候最多为15个子节长
2、colu first_name heading ‘Employ|Name’ format a5
给first_name 取了个名字叫做Employ|Name，|表示换行，a5表示5个字节
3、column salary JUSTIFY LEFT FORMAT $99,990.00 
Justify left代表列名向左对其
999,999.999代表每一位为0−9之间的数“，”是代表分割符，千分位000,000.000代表每一位为0-9之间的数，不足的用0补齐
4、column start_date format a8 null ‘not date’; 
代表列名如果为空，就用not date文本代替
format后面不能直接跟null 在a8后面写
这里的和替换空和nvl是有区别的，nvl必须要求类型匹配才可以替换
5、column显示所有对列格式的设置
6、显示指定列的设置情况
column 列名
7、删除指定列的设置和删除全部列的设置
column 列名 clear
clear column //删除全部
8、出错的时候
出错的时候不能显示就只显示#######
set linesize(line) 设置sqlplus输出的最大行宽 –
set pagesize 设置页面的最大行数
set newpage 设置页面之间的空行数
spool sqlplus屏幕的文件输入输出命令
edit 使用自定义的编辑器编辑指定文件 其实也可以使用ed.修改后直接使用/即可执行
save 保存当前session最近的sql语句至指定的文件中
host 返回到操作系统环境，类似!
start或@ 执行文件中的命令
edit 使用自定义的编辑器编辑指定文件
define_editor 自定义sqlplus里的编辑器
exit或quit 退出sqlplus
column
column是sqlplus里最实用的一个命令，很多时候sql语句输出的列宽度不合适而影响查看，都需要
用到这个命令来更改select语句中指定列的宽度和标题。大部分时候，我们可以简写column为col即
可，主要有以下两种用法：
?修改列宽度
column c1 format a20 –将列c1（字符型）显示最大宽度调整为20个字符
column c1 format 9999999 –将列c1（num型）显示最大宽度调整为7个字符
?修改列标题
column c1 heading c2 –将c1的列名输出为c2
Where子句的使用（如果报from找不到，就要注意from是不是和某个列写到一起了！）
语法：
select 要查询的列名
from 表名
where 列名或别名
逻辑操作符
表达式 。。。
1、作用：对SQL语句返回的数据集进行筛选;
2、位置：紧跟在from子句后
3、 内容：由一至多个限定条件组成，限定条件由表达式, 比较符, 字面值组成。
4、 所有字符串和日期要用单引号括起来，数值不需要单引号。
日期在Oracle里有特定的格式，’DD-MON-YY’（具体看日期的显示格式），
否则作为一个字符串。
5、 几种常见的操作符：
1》逻辑比较操作符
= > < >= <= != <> ^=（后面三种都是不等于）
需求：查找工资大于1000的所有员工的last_name和工资。
需求：查找不在45号部门工作的所有员工的id，last_name和dept_id,并且按照dept_id 升序进行排序
select id,last_name,dept_id
from s_emp
where dept_id<>45
order by dept_id;
2》sql比较操作符（在where后面写，order by 前面）
between and：在什么范围之内
in(xx,xx,xx):在一个列表中的值
like:模糊查询，即值不是精确的值的时候使用
通配符，即可以代替任何内容的符号
%：通配0到多个字符
_: 当且仅当通配一个字符
转义字符：
默认为,可以指定 指定的时候用escape 符号指明即可,转义字符只能转义后面的一 个字符 ,下面这个代表是以_开头后面任意字符的last_name 
between 1 and 4: 包括起止值。限定内容为1到4。 
in (1,2,4): 限定内容为1,2,4。
is null：对null值操作特定义的操作符，不能使用=否定的Is not nul，not in ,not like ,not butween
3》逻辑操作符
当条件有多个的时候使用
and：且逻辑
or：或逻辑
注意：and逻辑比or逻辑要高
not：非逻辑
需求：
1.查找员工id在[5,20]之间的所有员工的id和last_name 
or 
2.查找员工id不在[5,20]之间的所有员工的id和last_name 
or
这个需要注意的是not匹配的是最近的一样，优先级相对于and是高的，所以加括号
3.查找在43或者44号部门的员工的id和last_name;
or
需求：查看员工名字以C字母开头的员工的id，工资。
练习：查看员工名字长度不小于5，且第四个字母为n字母的员工id和工资
需求：查看员工名字中包含一个_的员工id和工资 
需求：查看员工提成为为空的员工的id和名字
需求：查看员工部门id为41且职位名称为Stock Clerk（存库管理员）的员工id和名字
练习：查看员工部门为41 或者 44号部门 且工资大于1000的员工id和名字
查看员工部门为41且工资大于1000 或者 44号部门的员工id和名字
函数：这里的函数相当于Java中写好的一些方法，有名字，可以传递参数，实现某一项具体功能。
函数分为:
1.单行函数
1.字符函数
2.日期函数
3.数字函数
4.转换函数
2.分组函数(后面的章节再做学习)
学前须知：
哑表dual
dual是一个虚拟表，辅助查找和运算。通常用在select语句中，作为查询的目标表结构，oracle保证dual里面永远只有一条记录。
例如:
显示1+1的结果,可以看出,dual很多时候是为了构成select的标准语法
select 1+1 from dual;
1、字符函数
1) LOWER:转换成小写
2) UPPER:转换成大写
3) INITCAP:首字母变成大写，其余都小写
4) CONCAT:字符串的连接
5) SUBSTR(str,start,length)或者SUBSTR(str,start):字符串的截取
6) LENGTH:求字符串的长度
7) NVL : 转换null的值。(前边已经用过)
nvl2:
8) DECODE：
**LOWER 把字符转为小写**
例如:把‘HELLO‘转换为小写
select lower(‘HELLO‘)
from dual;
例如:把s_emp表中的last_name列的值转换为小写
select lower(last_name)
from s_emp;
upper 把字符转换为大写
例如:把’world’转换为大写
select upper(‘world’)
from dual;
例如:把s_emp表中的last_name列的值转换为大写
select upper(last_name)
from s_emp;
例如:查询s_emp表中名字为Ngao的人信息
这样是查不到:
select last_name,salary,dept_id
from s_emp
where last_name=‘NGAO‘;
这样就可以查询到了:
select last_name,salary,dept_id
from s_emp
where upper(last_name)=‘NGAO‘;
initcap 把字符串首字母大写
例如:把’hELLO’转换为首字母大写,其余字母小写
select initcap(‘hELLO’)
from dual;
concat 把俩个字符串连接在一起(类似之前的||的作用)
例如:把’hello’和’world’俩个字符串连接到一起,并且起个别名为msg
select concat(‘hello’,’world’) msg
from dual;
例如:把first_name和last_name俩个列的值连接到一起
select concat(first_name,last_name)
from s_emp;
substr 截取字符串
例如:截取’hello’字符串,从第2个字符开始,截取后面的3个字符
select substr(‘hello’,2,3)
from dual;
可以找到第二个字母开头的是什么字母，其他同理
length 获得字符串长度
例如:获得’world’字符串的长度
select length(‘world’)
from dual;
例如:获得s_emp表中last_name列的每个值的字符长度
select length(last_name)
from s_emp;
nvl 替换列中为null的值
在前面的章节已经使用过了
nvl(要输出的列名，为空的时候要被替换的值) //要替换的值类型必须要和之前保持一致 
例子：
1.查找last_name全小写的值为velasquez的员工的lastname 
2.查找last_name的长度>10的所有员工的last_name
instr查找字符串
instr(‘1234;5678’,’;’,1,1)-1
解释：1. ‘1234;5677’==>可以是表达式，也可以是具体数据
2. ‘;’==>为分离的标志，这里为两组数据中的“;”号
3. 第一个1为从左边开始，如果为-1，则从右边开始。
4. 第二个1为“;”出现的第几次。
2、数字函数
1) ROUND:四舍五入
2) TRUNC:截取，不进行四舍五入
3) MOD:取余
**round 四舍五入**
（切记-1代表保存小数点后一位，0保留到各位，1保留到10
round(arg1,arg2)第一个参数表示要进行四舍五入操作的数字
第二个参数表示保留到哪一位(负数代表小数点之前，0，正数代表小数点之后)0代表保留到个位！！-1代表保存到十位
select round(45.67) from dual; 46
select round(45.67,1) from dual; 45.7
select round(45.67,2) from dual; 45.67
select round(45.67,-1) from dual; 50
select round(45.67,-2) from dual; 0
select round(55.67,-2) from dual; 100
例如
保留到小数点后面2位
select round(45.923,2)
from dual;
保留到个位 (个十百千万...)
select round(45.923,0)
from dual;
保留到十位 (个十百千万...)
select round(45.923,-1)
from dual;
**trunc 截取到某一位**
trunc(arg1,arg2)
和round的用法一样,但是trunc只舍去不进位
select trunc(45.67) from dual; 45
select trunc(45.67,1) from dual; 45.6
select trunc(45.67,2) from dual; 45.67
select trunc(45.67,-1) from dual; 40
select trunc(45.67,-2) from dual; 0
select trunc(55.67,-2) from dual; 0
例如:
截取到小数点后面2位
select trunc(45.923,2)
from dual;
截取到个位 (个十百千万...)
select trunc(45.923,0)
from dual;
截取到十位 (个十百千万...)
select trunc(45.923,-1)
from dual;
mod 取余
mod(arg1,arg2)
第一个参数表示要进行取余操作的数字
第二个参数表示参数1和谁取余
“`
例如:
把10和3进行取余 (10除以3然后获取余数)
select mod(10,3)
from dual;
3、日期函数
1) MONTHS_BETWEEN:两个日期之间的月数，如果是正数前面的值大于后面的值
2) ADD_MONTHS:在指定日期上增加月数
3) NEXT_DAY:指定日期的下一个星期几是哪天
4) LAST_DAY:指定日期的最后一天
5) ROUND:对指定日期进行四舍五入
6) TRUNC:对指定日期进行截取
sysdate关键字
表示系统的当前时间
例如:
显示时间:当前时间
注意:sysdate进行加减操作的时候,单位是天
例如:
显示时间:明天的这个时候
例如:
显示时间:昨天的这个时候
例如:
显示时间:1小时之后的时候
months_between 俩个时间点之间相差多少个月(单位是月)
例如:
30天之后和现在相差多少个月
add_months 返回一个日期数据:表示一个时间点,往后推x月的日期
例如:
‘01-2月-2016’往后推2个月
例如:
当前时间往后推4个月
next_day 返回一个日期数据:表示一个时间点后的下一个星期几在哪一天
例如:
当前时间的下一个星期5是哪一个天 
注意:
如果要使用’FRIDAY’,那么需要把当前会话的语言环境修改为英文
last_day 返回一个日期数据:表示一个日期所在月份的最后一天
例如:
当前日期所在月份的最后一天
round 对日期进四舍五入,返回操作后的日期数据。逢16日往月份进一,逢7月往年份进一
round(sysdate,’year/y/yy/yyy/yyyy’) 年 7月节点
round(sysdate,’mm/month’) 月 16号节点
round(sysdate,’d/day’) 星期 星期四节点
round(sysdate,’dd’) : 天 -》 12点节点
例如:
把当前日期四舍五入到月（年月日.时 分 秒 把这个看错数字就可以了）
今天2016年9月5日四舍五入到月，就要看日是否大于16？大于进一，不大于不进一，同时舍弃为1 
把当前日期四舍五入到年
大致算一下，今天已经9月了，所以满足大于节点7进一位，同时舍弃年前面的值
trunc 对日期进行截取 和round类似,但是只舍弃不进位
trunc（sysdate,‘yyyy/year‘） --返回当年第一天。
trunc（sysdate,‘mm/month‘） --返回当月第一天。
trunc（sysdate,‘d/day‘） --返回当前星期的第一天。
trunc（sysdate,‘dd‘）--返回当前年月日
截取和round基本是一样的只是，不进位而已。
4、类型转换函数
1).TO_CHAR 将日期或者数值转换成字符串
2).TO_NUMBER 将字符串转换成数字
3).TO_DATE 将日期字符串转换成日期
to_char 把日期转换为字符
例如:
把当前日期按照指定格式转换为字符串
日期格式：
yyyy/YYYY：四位数的年份
rrrr：四位数的年份
yy：两位数的年份
rr：两位数的年份
mm：两位数的月份（数字）
D：一周的第几天
DD：一月的第几天
DDD ：一年的第几天
YEAR：英文的年份
MONTH：英文全称的月份
mon：英文简写的月份
ddsp：英文的第几天
ddspth：英文序列数的第几天
DAY：全英文的星期
DY：简写的英文星期
hh：小时
mi：分钟
ss：秒
AM:上午
PM:下午
练习：显示当前时间，查询当前时间是这年的第几天？是这个星期的第几天？是这个月的第几天？
select ‘这年的第一几天：‘||to_char(sysdate,‘DDD‘)||‘ 这个星期的第几天：‘ ||to_char(sysdate,‘D‘)||‘ 这个月的第几天：‘||to_char(sysdate,‘DD‘) as 结果from dual;
(注意拼接字符串，只有一个别名，是一个整体)
例如:
测试常见的一些日期数据转换为字符串的格式
select to_char(sysdate,‘yyyy MM D DD DDD YEAR MONTH ddsp ddspth DAY DY‘) from dual;
select to_char(sysdate,‘dd-mm-yy‘)
from dual;
select to_char(sysdate,‘dd-mm-yy HH24:MI:SS AM‘)
from dual;
在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个情况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对年份的计算就会出现问题。这个事情当时被称为千年虫
数据库中表示日期中年份的有俩种: yy和rr
之前一直使用的时候yy格式,后来才有的rr格式
yy表示使用一个俩位数表示当前年份:
1990 ---yy数据库格式---> 90
1968 ---yy数据库格式---> 68
1979 ---yy数据库格式---> 79
如果日期中的年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下：
规则1 如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。
规则2 如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。
规则3 如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。
规则4 如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。
注意:rr格式并没有完全的解决俩位数年份保存的问题,思考里面还有哪些问题存在。
to_char 把数字转换为字符
L : 本地货币符号
:
. : 小数点
, : 千分符
9 : 0-9
0 : 0-9, 如果位数不足，强制补0
例如:
select to_char(salary,‘$999,999.00‘)
from s_emp;
fm表示去除结果显示中的开始的空格
L表示系统本地的货币符号
select to_char(salary,‘fmL999,999.00‘)
from s_emp;
to_number 把字符转换为数字
例如:
select to_number(‘1000‘)
from dual;
//这个写法是错的 abc不能转换为数字
select to_number(‘abc’)
from dual;
to_date 把字符转换为日期
.TO_DATE(char, [‘fmt’]):例如
select TO_DATE (‘10-September-1992’,’dd-Month-YYYY’) from dual
.使用format的元素格式 
例如:
select to_date(‘10-12-2016’,’dd-mm-yyyy’)
from dual;
select to_date(‘25-5月-95‘,‘dd-month-yy‘)
from dual;
//session语言环境设置为英文下面可以运行
select to_date(‘25-MAY-95‘,‘dd-MONTH-yy‘)
from dual;
这个是不能改用什么连接的
oracle数据库中表示一个日期数据的几种方式
1.sysdate
2.oracle默认的日期格式 例如:‘25-MAY-95‘
3.to_date函数转换
oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分
//语法如下：
EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR } FROM { date_value | interval_value } )
//我们只可以从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd);
//我们只可以从一个 timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE;
select extract(year from date‘2011-05-17‘) year from dual;
YEAR
----------
2011
select extract(month from date‘2011-05-17‘) month from dual;
MONTH
----------
5
select extract(day from date‘2011-05-17‘) day from dual;
DAY
----------
17
//获取两个日期之间的具体时间间隔，extract函数是最好的选择
select extract(day from dt2-dt1) day
,extract(hour from dt2-dt1) hour
,extract(minute from dt2-dt1) minute
,extract(second from dt2-dt1) second
from (
select to_timestamp(‘2011-02-04 15:07:00‘,‘yyyy-mm-dd hh24:mi:ss‘) dt1
,to_timestamp(‘2011-05-17 19:08:46‘,‘yyyy-mm-dd hh24:mi:ss‘) dt2
from dual)
/
DAY HOUR MINUTE SECOND
102 4 1 46
–
select extract(year from systimestamp) year
,extract(month from systimestamp) month
,extract(day from systimestamp) day
,extract(minute from systimestamp) minute
,extract(second from systimestamp) second
,extract(timezone_hour from systimestamp) th
,extract(timezone_minute from systimestamp) tm
,extract(timezone_region from systimestamp) tr
,extract(timezone_abbr from systimestamp) ta
from dual
/
YEAR MONTH DAY MINUTE SECOND TH TM TR TA
2011 5 17 7 14.843 8 0 UNKNOWN UNK
//
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT … FROM…)， 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。
一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。
Exists是子查询的一种条件形式，通过判断Exists的选择表达式（括号内的部分）的结果，如果存在一行或多行结果记录，则Exists整个子查询结果为真，否则为假。由于我们采用Exists来实现子查询，只需要关心是否存在满足条件的记录，所以选择表达式的选择列表采用*来实现，当然，你也可以在选择列表指明具体的某些列，但这些列将在整个搜索过程中被忽略。
Exists实例
SQL 代码
select Resc_id from dbo.Res_Coach
where EXISTS (select * from Res_Coach where Resc_id is null)
查询原理：
遍历dbo.Res_Coach每一条，同时处理where条件（EXISTS (select * from Res_Coach where Resc_id=0) 判断结果为true或者false），为true时拿出该条，false时，放弃该条记录。
SQL 代码
– 1、 where条件中的子查询和主查询没关系
select Resc_id
from dbo.Res_Coach
where EXISTS (select Rese_id from dbo.Res_Excellent where Rese_id Is null )
– 2、 where条件中得子查询和主查询有关系
select Resc_id
from dbo.Res_Coach
where EXISTS (select Resc_id from dbo.Res_Coach where Resc_id Is null )
实例备注：不管where条件中得子查询和主查询有没有关系，遍历主查询中得每一条时，判断where条件，exists结果为真，where条件返回true，拿出该条记录，where条件返回false， 不返回该记录。
Exists 和 In 的选择
如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in
运行project后后台报错：
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select wmsys.wm_concat(RoleID) from dlsys.tcrole a,dlsys.tcUnit b,dlsys.tcHuman c where a.UnitID = b.UnitID and b.UnitID = c.UnitID and c.HumanID = 161 and RoleID not in(152)]; nested exception isJava.sql.SQLException: ORA-00904: “WMSYS”.”WM_CONCAT”: 标识符无效
原因：
11gr2和12C上已经摒弃了wm_concat函数，当时我们很多程序员在程序中确使用了该函数，导致程序出现错误，为了减轻程序员修改程序的工作量，只有通过手工创建个wm_concat函数，来临时解决该问题，但是注意，及时创建了该函数，在使用的过程中，也需要用to_char(wm_concat())方式，才能完全替代之前的应用。
解决办法：
一.解锁sys用户
alter user sys account unlock;
二.创建包、包体和函数
以sys用户登录数据库，执行下面的命令
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
/
–定义类型body:
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ‘,‘ || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ‘,‘ || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
--自定义行变列函数:
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;
/
三.创建同义词并授权
create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPL
/
create public synonym wm_concat for sys.wm_concat
/
grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/
已经实践过证明可行再使用该函数的时候不能带用户名
create table test(id number,name varchar2(20));
insert into test values(1,‘a‘);
insert into test values(1,‘b‘);
insert into test values(1,‘c‘);
insert into test values(2,‘d‘);
insert into test values(2,‘e‘);
insert into test values(3,‘f‘);
insert into test values(3,‘g‘);
四.使用子定义的wm_concat
select id,wm_concat(name) from test group by id;
使用方法：
1、比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
其实decode第一参数是if判断语句的第一个参数，第二个参数是if判断语句的第二个参数
If(12-12==-1)else
12
if else(12-12==1)else
1….
1、插入测试数据
insert into student_score (name,subject,score)values(‘zhang san‘,‘Chinese‘,90);
insert into student_score (name,subject,score)values(‘zhang san‘,‘Mathematics‘,80);
insert into student_score (name,subject,score)values(‘zhang san‘,‘English‘,79);
测试一：
select name,subject,decode(subject, ‘Chinese‘,score,0) from student_score;
结果如下：
如果是中文课程的话， 显示分数， 其他课程分数为零。
这条SQL 看上去使用意义不大。
测试二：
select name,sum(decode(subject, ‘Chinese‘,score,0)) as CHINESE from student_score group by name;
统计中文课程的分数。看上去有点意义。
总体看来, decode 的使用看上去和case when 有点类似。如果只是用作以上两种状况，看上去意义不大。
select name,sum(decode(subject, ‘Chinese’,score,0)) as CHINESE from student_score group by name;
select name,score as CHINESE from student_score;
使用的两句使用后的效果一样，看上去使用decode 多此一举。
2、行转列-有意义的使用
往以上table 再插入一些其他学生的成绩：
insert into student_score (name,subject,score)values(‘li shi‘,‘Chinese‘,96);
insert into student_score (name,subject,score)values(‘li shi‘,‘Mathematics‘,86);
insert into student_score (name,subject,score)values(‘li shi‘,‘English‘,76);
insert into student_score (name,subject,score)values(‘wang wu‘,‘Chinese‘,92);
insert into student_score (name,subject,score)values(‘wang wu‘,‘Mathematics‘,82);
insert into student_score (name,subject,score)values(‘wang wu‘,‘English‘,72);
使用以下SQL:
select name,
sum(decode(subject, ‘Chinese‘, nvl(score, 0), 0)) "Chinese",
sum(decode(subject, ‘Mathematics‘, nvl(score, 0), 0)) "Mathematics",
sum(decode(subject, ‘English‘, nvl(score, 0), 0)) "English"
from student_score
group by name;
将行的数据转化为列， 是不是很有意义了。
使用case then 也可以达到相同的效果。
select name,
sum(case when subject=‘Chinese‘
then nvl(score,0)
else 0
end) "Chinese",
sum(case when subject=‘Mathematics‘
then nvl(score,0)
else 0
end) "Mathematics",
sum(case when subject=‘English‘
then nvl(score,0)
else 0
end) "English"
from student_score
group by name;
概念：所谓多表查询，又称表联合查询，即一条语句涉及到的表有多张，数据通过特定的连接进行联合显示。
语法： select column_name,....
from table1,table2
条件。。。。
学前须知：
笛卡尔积
在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y.
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
在数据库中,如果直接查询俩张表,那么其查询结果就会产生笛卡尔积
例如:
select *from 表1,表2;
链接查询分类：
为了避免笛卡尔基的产生，我们可以使用连接查询来解决这个问题。
连接查询分为：
1、等值连接
2、不等值连接
3、外连接
a)左外连接
b)右外连接
c)全链接
4、自链接
1、等值连接：
利用一张表中的某个列的值和另外一张表中的某个列的值相等的关系，把两张表连接起来
语法：
select tb_name.col_name,tb_name.col_name,...
from tb_name,tb_name,...
where tb_name.col_name = tb_name.col_name
and
tb_name.col_name = tb_name.col_name
...
需求：查看每个员工的id，last_name以及所属部门名称
（要用别名，就用表的别名，在用列明名的话会有问题的） 
需求：查找员工工资所属的等级名称
如果想使用关键字进行连接查询在from后面写join on(on想当与where)
2、不等值连接
使用的是除=以外的操作符号的多表查询
例如：使用between and
select t1.col_name,t2.col_name
from t1,t2
where t1.col_name between t2.col_name
and t2.col_name;
3、外连接
Outer是可以省略的。加号放在值少的一方，就可以让少的一方值显示，一般来说+所在列的位置，的相反位置就是当前的连接是什么连接！反过来
当使用一个表的记录在另外一张表中不存在的时候，我们仍旧需要显示，使用外链接就可以了
平常规律：加号写法跟在
外连接分为：
右外连接（right join/rigth outer join）
左外连接（right join/left outer join）
全外连接（full join /full outer join）
右外连接的语法：
Select
这是是Join right的写法，join left是反过来的，就不写了
这个是+的写法 
加号只能写一个，写两个不代表全部外连接； 
全外连接：就是两个表中不存在的都不会显示
4、自链接
自己和自己连
5、集合连接
:对查询结果集的操作。
union:将上下结果取并集，去除掉重复的记录（重复的只显示一次）
union all：将上下结果全部显示
minus：取差集 A-B
可以用来做分页查询