Oracle中的一些sql语句与函数总结

第一篇：Oracle中的一些sql语句与函数总结

       Oracle中的一些sql语句与函数总结

       1.concat函数

       concat(字串1，字串2)：将字符串

       1、字符串2 连在一起；CONCAT()只允许两个参数；

       换言之，一次只能将两个字串串连起来。不过，在Oracle中，我们可以用'||来一次串连

       多个字串。

       SQL Server中是:select id ’, ’ name from table1 where id=’1’ 结果就是：1,王华。

       2.ltrim与rtrim用法

       ltrim: 也就是按照y中的字符一个一个截掉x中的字符，并且是从左边开始执行的，只要遇

       到y中有的字符, x中的字符都会被截掉, 直到在x的字符中遇到y中没有的字符为止函数命

       令才结束例如ltrim('abcdab','ab')结果并不是按‘ab’字符串来截断'abcdba'，而是分别按照字

       母a和b分别去找，分别按不是a和b的字符处开始截取。ltrim函数是从匹配函数开始之

       后出现在子串中任何字符都被屏蔽掉了;结果将是'cdab'。Rtrim用法与ltrim相反是从右边截

       取。

       3.MONTHS_BETWEEN函 MONTHS_BETWEEN(x, y)用于计算x和y之间有几个月。如果x在日历中比y早，那

       么MONTHS_BETWEEN()就返回一个负数。

第二篇：SQL语句常用函数

       SQL语句常用函数、字符转换函数

       1、ASCII()

       返回字符表达式最左端字符的ASCII 码值。在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。

       2、CHAR()

       将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（）返回NULL。

       3、LOWER()和UPPER()

       LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。

       4、STR()

       把数值型数据转换为字符型数据。

       STR([，length[，]])

       length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10，decimal 缺省值为0。

       当length 或者decimal 为负值时，返回NULL；

       当length 小于小数点左边（包括符号位）的位数时，返回length 个*； 先服从length，再取decimal ；

       当返回的字符串位数小于length，左边补足空格。

       二、去空格函数

       1、LTRIM()把字符串头部的空格去掉。

       2、RTRIM()把字符串尾部的空格去掉。

       三、取子串函数

       1、left()

       LEFT(，)

       返回character_expression 左起 integer_expression 个字符。

       2、RIGHT()

       RIGHT(，)

       返回character_expression 右起 integer_expression 个字符。

       3、SUBSTRING()

       SUBSTRING(，，length)

       返回从字符串左边第starting_ position 个字符起length个字符的部分。

       四、字符串比较函数

       1、CHARINDEX()

       返回字符串中某个指定的子串出现的开始位置。

       CHARINDEX(<’substring_expression’>，)

       其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0 值。

       此函数不能用于TEXT 和IMAGE 数据类型。

       2、PATINDEX()

       返回字符串中某个指定的子串出现的开始位置。

       PATINDEX(<’%substring _expression%’>，)其中子串表达式前后必须有百分号“%”否则返回值为0。

       与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、VARCHAR 和TEXT 数据类型。

       五、字符串操作函数

       1、QUOTENAME()

       返回被特定字符括起来的字符串。

       QUOTENAME(<’character_expression’>[，quote_ character])其中quote_ character 标明括字符串所用的字符，缺省值为“[]”。

       2、REPLICATE()

       返回一个重复character_expression 指定次数的字符串。

       REPLICATE(character_expression integer_expression)如果

       integer_expression 值为负值，则返回NULL。

       3、REVERSE()

       将指定的字符串的字符排列顺序颠倒。

       REVERSE()其中character_expression 可以是字符串、常数或一个列的值。

       4、REPLACE()

       返回被替换了指定子串的字符串。

       REPLACE(，，)用string_expression3 替换在string_expression1 中的子串string_expression2。

       5、SPACE()

       返回一个有指定长度的空白字符串。

       SPACE()如果integer_expression 值为负值，则返回NULL。

       6、STUFF()

       用另一子串替换字符串指定位置、长度的子串。

       STUFF(，，，)

       如果起始位置为负或长度值为负，或者起始位置大于

       character_expression1 的长度，则返回NULL 值。

       如果length 长度大于character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。

       六、数据类型转换函数

       1、CAST()

       CAST( AS [ length ])

       2、CONVERT()

       CONVERT([ length ]， [，style])

       1）data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。

       2）length用于指定数据的长度，缺省值为30。

       3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。

       4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。

       5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。

       6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。

       7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。

       8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“ ”，以标识发生了这种截断。

       9）用CONVERT（）函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。

       七、日期函数

       1、day(date_expression)

       返回date_expression中的日期值

       2、month(date_expression)

       返回date_expression中的月份值

       3、year(date_expression)

       返回date_expression中的年份值

       4、DATEADD()

       DATEADD(，，)

       返回指定日期date 加上指定的额外日期间隔number 产生的新日期。

       5、DATEDIFF()

       DATEDIFF(，，)

       返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。

       6、DATENAME()

       DATENAME(，)

       以字符串的形式返回日期的指定部分此部分。由datepart 来指定。

       7、DATEPART()

       DATEPART(，)

       以整数值的形式返回日期的指定部分。此部分由datepart 来指定。DATEPART(dd，date)等同于DAY(date)

       DATEPART(mm，date)等同于MONTH(date)

       DATEPART(yy，date)等同于YEAR(date)

       8、GETDATE()

       以DATETIME 的缺省格式返回系统当前的日期和时间。

       八、统计函数

       AVG（）-返回的平均价值

       count（）-返回的行数

       first（）-返回第一个值

       last（）-返回最后一个值

       max（）-返回的最大价值

       min（）-返回最小的价值

       total（）-返回的总和

       九、数学函数

       abs(numeric_expr)求绝对值

       ceiling(numeric_expr)取大于等于指定值的最小整数 exp(float_expr)取指数

       floor(numeric_expr)小于等于指定值得最大整数 pi()3.1415926.........power(numeric_expr,power)返回power次方

       rand([int_expr])随机数产生器

       round(numeric_expr,int_expr)安int_expr规定的精度四舍五入 sign(int_expr)根据正数,0,负数，返回 1,0,-1 sqrt(float_expr)平方根

       十、系统函数

       suser_name()用户登录名

       user_name()用户在数据库中的名字 user用户在数据库中的名字 show_role()对当前用户起作用的规则

       db_name()数据库名object_name(obj_id)数据库对象名

       col_name(obj_id,col_id)列名

       col_length(objname,colname)列长度

       valid_name(char_expr)是否是有效标识符

第三篇：oracle维护常用sql语句(主要)

       1、oracle表空间利用率

       SELECT UPPER(F.TABLESPACE_NAME)“表空间名”，D.TOT_GROOTTE_MB “表空间大小(M)”，D.TOT_GROOTTE_MBF.TOTAL_BYTES)D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' “使用比(%)”，F.TOTAL_BYTES “空闲空间(M)”，F.MAX_BYTES “最大块(M)”

       FROM(SELECT TABLESPACE_NAME，ROUND(SUM(BYTES)/(1024 * 1024), 2)TOTAL_BYTES，ROUND(MAX(BYTES)/(1024 * 1024), 2)MAX_BYTES

       FROM SYS.DBA_FREE_SPACE

       GROUP BY TABLESPACE_NAME)F，(SELECT DD.TABLESPACE_NAME，ROUND(SUM(DD.BYTES)/(1024 * 1024), 2)TOT_GROOTTE_MB

       FROM SYS.DBA_DATA_FILES DD

       GROUP BY DD.TABLESPACE_NAME)D

       WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

       ORDER BY F.TABLESPACE_NAME;查询结果显示：

       /

       非系统表空间使用和(M)

       select sum(“已使用空间(M)”)“已使用空间(M)和” from(SELECT UPPER(F.TABLESPACE_NAME)“表空间名”，D.TOT_GROOTTE_MB “表空间大小(M)”，D.TOT_GROOTTE_MBF.TOTAL_BYTES)/ D.TOT_GROOTTE_MB * 100,2),'990.99')||'%' “使用比(%)”，F.TOTAL_BYTES “空闲空间(M)”，F.MAX_BYTES “最大块(M)”

       FROM(SELECT TABLESPACE_NAME，ROUND(SUM(BYTES)/(1024 * 1024), 2)TOTAL_BYTES，ROUND(MAX(BYTES)/(1024 * 1024), 2)MAX_BYTES

       FROM SYS.DBA_FREE_SPACE

       GROUP BY TABLESPACE_NAME)F，(SELECT DD.TABLESPACE_NAME，ROUND(SUM(DD.BYTES)/(1024 * 1024), 2)TOT_GROOTTE_MB

       FROM SYS.DBA_DATA_FILES DD

       GROUP BY DD.TABLESPACE_NAME)D

       WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME and F.TABLESPACE_NAME NOT IN('SYSAUX','USERS','UNDOTBS1','SYSTEM'))

       2、查询当前用户默认表空间的使用情况

       select tablespacename,sum(totalContent),sum(usecontent),sum(sparecontent),avg(sparepercent)from(SELECT b.file_id as id,b.tablespace_name as tablespacename,b.bytes as totalContent,(b.bytes-sum(nvl(a.bytes,0)))as usecontent,sum(nvl(a.bytes,0))as sparecontent,sum(nvl(a.bytes,0))/(b.bytes)*100 as sparepercent FROM dba_free_space a,dba_data_files b WHERE a.file_id=b.file_id and b.tablespace_name =(select default_tablespace from dba_users where username = user)

       group by b.tablespace_name,b.file_name,b.file_id,b.bytes)

       GROUP BY tablespacename;

       3、查询所有用户表使用大小的前三十名

       select * from(select segment_name,bytes from dba_segments where owner = USER order by bytes desc)where rownum <= 30

       4、查询单张表的使用情况 select segment_name,bytes from dba_segments where segment_name = 'RE_STDEVT_FACT_DAY' and owner = USER

       RE_STDEVT_FACT_DAY是您要查询的表名称

       5、计算每个用户占用的磁盘空间 select owner,sum(bytes)/1024/1024/1024 “Space(G)” from dba_segments group by owner order by 2;

       6、计算某个用户占用的磁盘空间

       select owner,sum(bytes)/1024/1024/1024 “Space(G)” from dba_segments where owner='LIAOJL' group by owner;

       7、查看表空间的名称及大小（分配大小）:

       select t.tablespace_name, round(sum(bytes/(1024*1024)),0)ts_size

       from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

       8、查看表空间物理文件的名称及大小（分配大小）:

       select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0)total_space from dba_data_files order by tablespace_name;

       9、查看回滚段名称及大小: select segment_name, tablespace_name, r.status,(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn( )order by segment_name;

       10、如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句: select d.sql_text,a.name from v$rollname a,v$transaction b,v$session c,v$sqltext d where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address=d.address and c.sql_hash_value=d.hash_value and a.usn==2;(备注：你要看哪个，就把usn=?写成几就行了)

       查看控制文件:

       SQL>select * from v$controlfile;

       查看日志文件:

       SQL> col member format a50 SQL>select * from v$logfile;

       11、如何查看当前SQL*PLUS用户的sid和serial#:

       SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid');

       12、怎样识别IO竞争和负载平衡:

       SQL>col 文件名 format a35 SQL>select df.name 文件名, fs.phyrds 读次数, fs.phywrts 写次数,(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds))读时间,(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts))写时间 from v$datafile df, v$filestat fs where df.file#=fs.file# order by df.name

       13、查看有哪些用户连接

       select s.osuser os_user_name, decode(sign(48command), 1, to_char(command), 'Action Code #' || to_char(command))action, status session_status，s.osuser os_user_name,s.sid,p.spid ,s.serial# serial_num,nvl(s.username, '[Oracle process]')user_name,s.terminal terminal,s.program program,st.value criteria_value from v$sesstat st，v$session s , v$process p

       where st.sid = s.sid and

       st.statistic# = to_number('38')and

       ('ALL' = 'ALL' or s.status = 'ALL')and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

       21、监控当前数据库谁在运行什么SQL语句: SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;

       Column type format a8

       22、查询每个用户执行过的sql语句 select B.USER_NAME,C.OSUSER,C.MACHINE,C.PROGRAM,C.MODULE, A.USER_IO_WAIT_TIME,B.HASH_VALUE,C.SERVICE_NAME, A.SQL_TEXT from v$sqlarea a, v$open_cursor b,v$session c where A.ADDRESS=B.ADDRESS and C.USERNAME=B.USER_NAME order by C.OSUSER,B.USER_NAME

第四篇：Oracle SQL精妙SQL语句讲解

       SQL*PLUS界面：

       登录：输入SQLPLUS回车；输入正确的ORACLE用户名并回车；输入用户口令并回车，显示提示符：SQL>

       退出：输入EXIT即可。

       2）命令的编辑与运行：

       在命令提示符后输入SQL命令并运行，以分号结束输入；以斜杠结束输入；以空行结束输入；

       利用SQL缓冲区进行PL/SQL块的编辑和运行；

       利用命令文件进行PL/SQL块的编辑和运行。

       数据库查询

       用SELECT语句从表中提取查询数据。语法为

       SELECT [DISTINCT] {column1,column2,…} FROM tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC];

       说明：SELECT子句用于指定检索数据库的中哪些列，FROM子句用于指定从哪一个表或视图中检索数据。

       SELECT中的操作符及多表查询WHERE子句。(LIKE,IS,…)

       WHERE子句中的条件可以是一个包含等号或不等号的条件表达式，也可以是一个含有IN、NOT IN、BETWEEN、LIKE、IS NOT NULL等比较运算符的条件式，还可以是由单一的条件表达通过逻辑运算符组合成复合条件。

       ORDER BY 子句

       ORDER BY 子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。

       连接查询

       利用SELECT语句进行数据库查询时，可以把多个表、视图的数据结合起来，使得查询结果的每一行中包含来自多个表达式或视图的数据，这种操作被称为连接查询。

       连接查询的方法是在SELECT命令的FROM子句中指定两个或多个将被连接查询的表或视图，并且在WHERE子句告诉ORACLE如何把多个表的数据进行合并。根据WHERE子句中的条件表达式是等还是不等式，可以把连接查询分为等式连接和不等式连接。

       子查询

       如果某一个SELECT命令（查询1）出现在另一个SQL命令（查询2）的一个子句中，则称查询1是查询2的子查询。

       基本数据类型（NUMBER,VARCHAR2,DATE）O

       RACEL支持下列内部数据类型：

       VARCHAR2 变长字符串，最长为2000字符。

       NUMBER 数值型。

       LONG 变长字符数据，最长为2G字节。

       DATE 日期型。

       RAW 二进制数据，最长为255字节。

       LONG RAW 变长二进制数据，最长为2G字节。

       ROWID 二六进制串，表示表的行的唯一地址。

       CHAR 定长字符数据，最长为255。

       常用函数用法：

       一个函数类似于一个算符，它操作数据项，返回一个结果。函数在格式上不同于算符，它个具有变元，可操作0个、一个、二个或多个变元，形式为：

       函数名（变元，变元，…）

       函数具有下列一般类形：

       单行函数

       分组函数

       单行函数对查询的表或视图的每一行返回一个结果行。它有数值函数，字符函数，日期函数，转换函数等。

       分组函数返回的结果是基于行组而不是单行，所以分组函数不同于单行函数。在许多分组函数中可有下列选项：

       DISTRNCT 该选项使分组函数只考虑变元表达式中的不同值。

       ALL该选项使分组函数考虑全部值，包含全部重复。

       全部分组函数（除COUNT（*）外）忽略空值。如果具有分组函数的查询，没有返回行或只有空值（分组函数的变元取值的行），则分组函数返回空值。

       5、数据操纵语言命令：

       数据库操纵语言（DML）命令用于查询和操纵模式对象中的数据，它不隐式地提交当前事务。它包含UPDATE、INSERT、DELETE、EXPLAIN PLAN、SELECT和LOCK TABLE 等命令。下面简单介绍一下：

       1)UPDATE tablename SET {column1=expression1,column2=expression2,…} WHERE {conditions};

       例如：S QL>UPDATE EMP

       SET JOB =’MANAGER’

       WHERE ENAME=’MAPTIN’;

       SQL >SELECT * FROM EMP;

       UPDATE子句指明了要修改的数据库是EMP,并用WHERE子句限制了只对名字(ENAME)为’MARTIN’的职工的数据进行修改,SET子句则说明修改的方式,即把’MARTION’的工作名称(JOB)改为’MARAGER’.2)INSERT INTO tablename {column1,column2,…} VALUES {expression1,expression2,…};

       例如：SQL>SELECT INTO DEPT（DNAME，DEPTNO）

       VALUES（‘ACCOUNTING’，10）

       3)DELETE FROM tablename WHERE {conditions};

       例如：SQL>DELETE FROM EMP

       WHERE EMPNO = 7654;

       DELETE命令删除一条记录,而且DELETE命令只能删除整行,而不能删除某行中的部分数据.4）事务控制命令

       提交命令(COMMIT):可以使数据库的修改永久化.设置AUTOCOMMIT为允许状态:SQL >SET AUTOCOMMIT ON;

       回滚命令(ROLLBACK):消除上一个COMMIT命令后的所做的全部修改,使得数据库的内容恢复到上一个COMMIT执行后的状态.使用方法是:

       SQL>ROLLBACK;

       创建表、视图、索引、同义词、用户。、表是存储用户数据的基本结构。

       建立表主要指定义下列信息：

       列定义

       完整性约束

       表所在表空间

       存储特性

       可选择的聚集

       从一查询获得数据

       语法如下：CREATE TABLE tablename

       (column1 datatype [DEFAULT expression] [constraint], column1 datatype [DEFAULT expression] [constraint], ……）

       [STORAGE子句] [其他子句…]；

       例如：

       SQL>CREATE TABLE NEW_DEPT(DPTNO NUMBER(2), DNAME CHAR(6), LOC CHAR(13);

       更改表作用：

       增加列

       增加完整性约束

       重新定义列（数据类型、长度、缺省值）

       修改存储参数或其它参数

       使能、使不能或删除一完整性约束或触发器

       显式地分配一个范围

       2）、视图

       视图是一个逻辑表，它允许操从其它表或视图存取数据，视图本身不包含数据。视图所基于的表称为基表。

       引入视图有下列作用：

       提供附加的表安全级，限制存取基表的行或/和列集合。

       隐藏数据复杂性。

       为数据提供另一种观点。

       促使ORACLE的某些操作在包含视图的数据库上执行，而不在另一个数据库上执行。

       3）、索引

       索引是种数据库对象。对于在表或聚集的索引列上的每一值将包含一项，为行提供直接的快速存取。在下列情况ORACLE可利用索引改进性能：

       按指定的索引列的值查找行。

       按索引列的顺序存取表。

       建立索引： CREATE [UNIQUE] INDEX indexname ON tablename(column ,。。)；

       例如：SQL>CREAT INDEX IC_EMP

       ON CLUSTER EMPLOYEE

       4）、同义词

       同义词：为表、视图、序列、存储函数、包、快照或其它同义词的另一个名字。使用同义词为了安全和方便。对一对象建立同义词可有下列好处：

       引用对象不需指出对象的持有者。

       引用对象不需指出它所位于的数据库。

       为对象提供另一个名字。

       建立同义词：

       CREATE SYNONYM symnon_name FOR [username.]tablename;

       例如：CREAT PUBLIC SYNONYM EMP

       FOR SCOTT.EMP @SALES

       5）、用户

       CREATE USER username IDENTIFIED BY password;

       例如：SQL>CREATE USER SIDNEY

       IDENTIFIED BY CARTON;

       Oracle扩展PL/SQL简介

       PL/SQL概述。

       PL/SQL是Oracle对SQL规范的扩展，是一种块结构语言，即构成一个PL/SQL程序的基本单位（过程、函数和无名块）是逻辑块，可包含任何数目的嵌套了快。这种程序结构支持逐步求精方法解决问题。一个块（或子块）将逻辑上相关的说明和语句组合在一起，其形式为：

       DECLARE

       ---说明

       BEGIN

       ---语句序列

       EXCEPTION

       ---例外处理程序

       END；

       它有以下优点:

       支持SQL;

       生产率高;

       性能好;

       可称植性;

       与ORACLE集成.PL/SQL体系结构

       PL/SQL运行系统是种技术，不是一种独立产品，可认为这种技术是PL/SQL块和子程序的一种机，它可接收任何有效的PL/SQL块或子程序。如图所示：

       PL/SQL机可执行过程性语句，而将SQL语句发送到ORACLE服务器上的SQL语句执行器。在ORACLE预编译程序或OCI程序中可嵌入无名的PL/SQL块。如果ORACLE具有PROCEDURAL选件，有名的PL/SQL块（子程序）可单独编译，永久地存储在数据库中，准备执行。

       PL/SQL基础：

       PL/SQL有一字符集、保留字、标点、数据类型、严密语法等，它与SQL有相同表示，现重点介绍。

       1）、数据类型：如下表所示

       数据类型 子类型

       纯量类型 数值 BINARY_INTEGER NATURAL,POSITIVE

       NUMBER DEC,DECIMAL,DOUBLE PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT

       字符 CHAR CHARACTER,STRING

       VARCHAR2 VARCHAR

       LONG

       LONG RAW

       RAW

       RAWID

       逻辑 BOOLEAN

       日期 DATE

       组合 类型 记录 RECORD

       表 TABLE

       2）、变量和常量

       在PL/SQL程序中可将值存储在变量和常量中，当程序执行时，变量的值可以改变，而常量的值不能改变。

       3）、程序块式结构：

       DECLARE

       变量说明部分；

       BEGIN

       执行语句部分；

       [EXCEPTION

       例外处理部分；] END;控制语句：

       分支语句：

       IF condition THEN

       Sequence_of_statements;

       END IF;

       IF condition THEN

       Sequence_of_statement1;

       ELSE

       Sequence_of_statement2;

       END IF;

       IF condition1 THEN

       Sequence_of_statement1;

       ELSIF condition2 THEN

       Sequence_of_statement2;

       ELSIF condition3 THEN

       Sequence_of_statement3;

       END IF;

       循环语句：

       LOOP

       Sequence_of_statements;

       IF condition THEN

       EXIT;

       END IF;

       END LOOP;

       WHILE condition LOOP

       Sequence_of_statements;

       END LOOP;

       FOR counter IN lower_bound..higher_bound LOOP

       Sequence_of_statements;

       END LOOP;

       子程序：

       存储过程：

       CREATE PROCEDURE 过程名(参数说明1，参数说明2，[局部说明]

       BEGIN

       执行语句；

       END 过程名；

       。)IS。

       存储函数：

       CREATE FUNCTION 函数名(参数说明1，参数说明2。。)RETURN 类型 IS [局部说明] BEGIN

       执行语句；

       END 函数名

       Oracle SQL精妙SQL语句讲解

       好东西，大家赶紧收藏吧~~~

       --行列转换 行转列

       DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);

       INSERT INTO t_change_lc

       SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4 UNION

       SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4;

       SELECT * FROM t_change_lc;

       SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4

       FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

       --行列转换 列转行

       DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4

       FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

       SELECT * FROM t_change_cl;

       SELECT t.card_code，t.rn q，decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal

       FROM(SELECT a.*, b.rn

       FROM t_change_cl a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4)b)t ORDER BY 1, 2;

       --行列转换 行转列 合并

       DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;

       SELECT * FROM t_change_lc_comma;

       SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q

       FROM(SELECT a.card_code，a.q，row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn

       FROM t_change_lc_comma a)t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code

       AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;

       SELECT * FROM t_change_cl_comma;SELECT t.card_code，substr(t.q，instr(';' || t.q, ';', 1, rn)，instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q

       FROM(SELECT a.card_code, a.q, b.rn

       FROM t_change_cl_comma a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100)b

       WHERE instr(';' || a.q, ';', 1, rn)> 0)t ORDER BY 1, 2;

       --实现一条记录根据条件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10), c VARCHAR2(10));

       SELECT * FROM t_ia_src;SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;

       INSERT ALL WHEN(c1 IN('a1','a3'))THEN

       INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

       INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

       --如果存在就更新，不存在就插入用一个语句实现 DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

       SELECT * FROM t_mg;

       MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN

       UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN

       INSERT(code, NAME)VALUES(b.code, b.NAME);

       --抽取/删除重复记录

       DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;

       INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=2;

       SELECT * FROM t_dup;

       SELECT * FROM t_dup a WHERE a.ROWID <>(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);

       SELECT b.code, b.NAME

       FROM(SELECT a.code，a.NAME，row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn

       FROM t_dup a)b WHERE b.rn > 1;

       --IN/EXISTS的不同适用环境--t_orders.customer_id有索引 SELECT a.*

       FROM t_employees a WHERE a.employee_id IN

       (SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

       SELECT a.*

       FROM t_employees a WHERE EXISTS(SELECT 1

       FROM t_orders b

       WHERE b.customer_id = 12

       AND a.employee_id = b.sales_rep_id);

       --t_employees.department_id有索引 SELECT a.*

       FROM t_employees a WHERE a.department_id = 10

       AND EXISTS(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

       SELECT a.*

       FROM t_employees a WHERE a.department_id = 10

       AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);

       --FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE dbms_random.VALUE * 10 dt FROM dual

       CONNECT BY ROWNUM <=10;

       CREATE INDEX idx_nonfbi ON t_fbi(dt);

       DROP INDEX idx_fbi_1;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));

       SELECT * FROM t_fbi WHERE trunc(dt)= to_date('2022-09-21','yyyy-mm-dd');

       --不建议使用

       SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2022-09-21';

       --LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;

       SELECT * FROM t_loop;

       --逐行提交 DECLARE BEGIN

       FOR cur IN(SELECT * FROM user_objects)LOOP

       INSERT INTO t_loop VALUES cur;

       COMMIT;

       END LOOP;END;

       --模拟批量提交 DECLARE

       v_count NUMBER;BEGIN

       FOR cur IN(SELECT * FROM user_objects)LOOP

       INSERT INTO t_loop VALUES cur;

       v_count := v_count 1;

       IF v_count >= 100 THEN

       COMMIT;

       END IF;

       END LOOP;

       COMMIT;END;

       --真正的批量提交 DECLARE

       CURSOR cur IS

       SELECT * FROM user_objects;

       TYPE rec IS TABLE OF user_objects%ROWTYPE;

       recs rec;BEGIN

       OPEN cur;

       WHILE(TRUE)LOOP

       FETCH cur BULK COLLECT

       INTO recs LIMIT 100;

       --forall 实现批量

       FORALL i IN 1..recs.COUNT

       INSERT INTO t_loop VALUES recs(i);

       COMMIT;

       EXIT WHEN cur%NOTFOUND;

       END LOOP;

       CLOSE cur;END;

       --悲观锁定/乐观锁定

       DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

       SELECT * FROM t_lock;

       --常见的实现逻辑，隐含bug DECLARE

       v_cnt NUMBER;BEGIN

       --这里有并发性的bug

       SELECT MAX(ID)INTO v_cnt FROM t_lock;

       --here for other operation

       v_cnt := v_cnt 1;

       INSERT INTO t_lock(ID)VALUES(v_cnt);

       COMMIT;END;

       --高并发环境下，安全的实现逻辑 DECLARE

       v_cnt NUMBER;BEGIN

       --对指定的行取得lock

       SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;

       --在有lock的情况下继续下面的操作

       SELECT MAX(ID)INTO v_cnt FROM t_lock;

       --here for other operation

       v_cnt := v_cnt 1;

       INSERT INTO t_lock(ID)VALUES(v_cnt);

       COMMIT;--提交并且释放lock END;

       --硬解析/软解析

       DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);

       SELECT * FROM t_hard;

       DECLARE

       sql_1

       VARCHAR2(200);BEGIN

       --hard parse

       --java中的同等语句是 Statement.execute()

       FOR i IN 1..1000 LOOP

       sql_1 := 'insert into t_hard(id)values(' || i || ')';

       EXECUTE IMMEDIATE sql_1;

       END LOOP;

       COMMIT;

       --soft parse

       --java中的同等语句是 PreparedStatement.execute()

       sql_1

       := 'insert into t_hard(id)values(:id)';

       FOR i IN 1..1000 LOOP

       EXECUTE IMMEDIATE sql_1

       USING i;

       END LOOP;

       COMMIT;END;

       --正确的分页算法

       SELECT *

       FROM(SELECT a.*, ROWNUM rn

       FROM(SELECT * FROM t_employees ORDER BY first_name)a

       WHERE ROWNUM <= 500)WHERE rn > 480;

       --分页算法(why not this one)SELECT a.*, ROWNUM rn

       FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500 AND ROWNUM > 480;

       --分页算法(why not this one)SELECT b.*

       FROM(SELECT a.*, ROWNUM rn

       FROM t_employees a

       WHERE ROWNUM < = 500

       ORDER BY first_name)b WHERE b.rn > 480;

       --OLAP--小计合计 SELECT CASE

       WHEN a.deptno IS NULL THEN

       '合计'

       WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN

       '小计'

       ELSE

       '' || a.deptno

       END deptno，a.empno，a.ename，SUM(a.sal)total_sal

       FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

       --分组排序 SELECT a.deptno，a.empno，a.ename，a.sal，--可跳跃的rank

       rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1，--密集型rank

       dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2，--不分组排序

       rank()over(ORDER BY sal DESC)r3

       FROM scott.emp a

       ORDER BY a.deptno,a.sal DESC;

       --当前行数据和前/后n行的数据比较 SELECT a.empno，a.ename，a.sal，--上面一行

       lag(a.sal)over(ORDER BY a.sal DESC)lag_1，--下面三行

       lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3

       FROM scott.emp a ORDER BY a.sal DESC;

       一、数据表设计图

       二、创建语句

       /*================*/ /* DBMS name: ORACLE Version 9i */ /* Created on: 2022-11-10 23:39:24 */ /*================*/

       alter table “emp”

       drop constraint FK_EMP_REFERENCE_DEPT;

       drop table “dept” cascade constraints;

       drop table “emp” cascade constraints;

       drop table “salgrade” cascade constraints;

       /*================*/ /* Table: “dept” */ /*================*/

       create table dept(deptno NUMBER(11)not null, dname VARCHAR2(15)not null, loc VARCHAR2(15)not null, constraint PK_DEPT primary key(deptno));

       /*================*/ /* Table: “emp” */ /*================*/

       create table emp(empno NUMBER(11)not null, deptno NUMBER(11), ename VARCHAR2(15)not null, sal NUMBER(11)not null, job VARCHAR2(15)not null, mgr NUMBER(11)not null, hirdate DATE not null, comm NUMBER(11)not null, constraint PK_EMP primary key(empno));

       /*================*/ /* Table: salgrade */ /*================*/

       create table salgrade(grade NUMBER(11)not null, losal NUMBER(11)not null, hisal NUMBER(11)not null, constraint PK_SALGRADE primary key(grade));

       alter table emp add constraint FK_EMP_REFERENCE_DEPT foreign key(deptno)references dept(deptno);

       三、测试要求及语句

       /** *公司工资最高的员工列表 子查询 */

       select t.ename,t.sal from emp t where t.sal =(select max(sal)from emp)

       /** *查询每一个员工的经理人及自己的名字 */

       select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno)

       /** *查询公司平均薪水的等级 */

       select s.grade from salgrade s where(select avg(t.sal)from emp t)between s.losal and s.hisal

       /** *求部门中那些人的工资最高 */

       select d.dname,ename,sal from(select t.deptno,ename,sal from(select deptno,max(sal)as max_sal from emp group by deptno)e join emp t on(e.deptno = t.deptno and t.sal = max_sal))et join dept d on(d.deptno = et.deptno)

       /** *查询部门平均薪水的等级 */

       select d.dname,avg_sal,grade from(select deptno,avg_sal,grade from(select deptno,avg(sal)as avg_sal from emp group by deptno)e join salgrade s on(e.avg_sal between s.losal and s.hisal))es join dept d on(es.deptno = d.deptno)

       /** *求部门的平均薪水等级 */

       select deptno,avg(grade)from(select deptno,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal))t group by t.deptno

       /** * 求那些人是经理人 */

       select ename from emp e where empno in(select distinct mgr from emp)

       /** *不准用组函数 求薪水的最高值 */

       select ename from emp where empno not in(select distinct e1.empno from emp e1 join emp e2 on(e1.sal

       /** *平均薪水最高的部门编号与名称 */

       select d.deptno,dname from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join dept d on(d.deptno = t1.deptno)where avg_sal =(select max(avg_sal)from(select deptno,avg(sal)avg_sal from emp group by deptno)t2)

       /** *求平均薪水的等级最低的部门名称 */

       select dname from dept d where d.deptno in(select deptno from(select deptno,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join salgrade g on(avg_sal between g.losal and g.hisal))t2 where t2.grade =(select min(grade)from(select deptno,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t1 join salgrade g on(avg_sal between g.losal and g.hisal))t3))

       /** *求部门经理人中平均薪水最低的部门名称 */

       select d.dname,t1.avg_sal from dept d join(select deptno,avg(sal)avg_sal from(select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t group by deptno)t1 on(d.deptno = t1.deptno)where avg_sal =(select min(avg_sal)from(select deptno,avg(sal)avg_sal from(select e2.deptno,e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t group by deptno))/** *求必普通员工的最高薪水还要高的经理人名称 */

       select ename from(select e2.ename,e2.empno,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno))t where t.sal >(select max(e.sal)from emp e where e.empno not in(select e1.mgr from emp e1 join emp e2 on(e1.mgr = e2.empno)))

       /** *求薪水最高的第6名到10名雇员 */

       SELECT * FROM(SELECT A.*, ROWNUM RN FROM(SELECT * FROM(select e1.ename,e1.sal from emp e1 order by e1.sal desc))A WHERE ROWNUM <= 10)WHERE RN >= 6

第五篇：Oracle SQL精妙SQL语句讲解

       好东西，大家赶紧收藏吧~~~ 转自junsansi

       --行列转换 行转列

       DROP TABLE t_change_lc;CREATE TABLE t_change_lc(card_code VARCHAR2(3), q NUMBER, bal NUMBER);

       INSERT INTO t_change_lc

       SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4 UNION

       SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM <= 4;

       SELECT * FROM t_change_lc;

       SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

       --行列转换 列转行

       DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code，SUM(decode(a.q, 1, a.bal, 0))q1，SUM(decode(a.q, 2, a.bal, 0))q2，SUM(decode(a.q, 3, a.bal, 0))q3，SUM(decode(a.q, 4, a.bal, 0))q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

       SELECT * FROM t_change_cl;

       SELECT t.card_code，t.rn q，decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4)bal FROM(SELECT a.*, b.rn

       FROM t_change_cl a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4)b)t ORDER BY 1, 2;

       --行列转换 行转列 合并

       DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;

       SELECT * FROM t_change_lc_comma;

       SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)q FROM(SELECT a.card_code，a.q，row_number()over(PARTITION BY a.card_code ORDER BY a.q)rn

       FROM t_change_lc_comma a)t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code

       AND t1.rn1 = PRIOR t1.rn GROUP BY t1.card_code;

       SELECT * FROM t_change_cl_comma;

       SELECT t.card_code，substr(t.q，instr(';' || t.q, ';', 1, rn)，instr(t.q || ';', ';', 1, rn)-instr(';' || t.q, ';', 1, rn))q FROM(SELECT a.card_code, a.q, b.rn

       FROM t_change_cl_comma a，(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100)b

       WHERE instr(';' || a.q, ';', 1, rn)> 0)t ORDER BY 1, 2;

       --实现一条记录根据条件多表插入 DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10), c VARCHAR2(10));DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10), c VARCHAR2(10));

       SELECT * FROM t_ia_src;SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;

       INSERT ALL WHEN(c1 IN('a1','a3'))THEN

       INTO t_ia_dest_1(flag,c)VALUES(flag1,c2)WHEN(c1 IN('a2','a4'))THEN

       INTO t_ia_dest_2(flag,c)VALUES(flag2,c2)ELSE INTO t_ia_dest_3(flag,c)VALUES(flag1||flag2,c1||c2)SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

       --如果存在就更新，不存在就插入用一个语句实现 DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

       SELECT * FROM t_mg;

       MERGE INTO t_mg a USING(SELECT 'the code' code, 'the name' NAME FROM dual)b ON(a.code = b.code)WHEN MATCHED THEN UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN INSERT(code, NAME)VALUES(b.code, b.NAME);

       --抽取/删除重复记录

       DROP TABLE t_dup;CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=10;INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5)NAME FROM dual CONNECT BY ROWNUM<=2;

       SELECT * FROM t_dup;

       SELECT * FROM t_dup a WHERE a.ROWID <>(SELECT MIN(b.ROWID)FROM t_dup b WHERE a.code=b.code);

       SELECT b.code, b.NAME FROM(SELECT a.code，a.NAME，row_number()over(PARTITION BY a.code ORDER BY a.ROWID)rn

       FROM t_dup a)b WHERE b.rn > 1;

       --IN/EXISTS的不同适用环境--t_orders.customer_id有索引 SELECT a.* FROM t_employees a WHERE a.employee_id IN

       (SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

       SELECT a.* FROM t_employees a WHERE EXISTS(SELECT 1

       FROM t_orders b

       WHERE b.customer_id = 12

       AND a.employee_id = b.sales_rep_id);

       --t_employees.department_id有索引 SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND EXISTS(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

       SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b);--FBI DROP TABLE t_fbi;CREATE TABLE t_fbi AS SELECT ROWNUM rn, dbms_random.STRING('z',10)NAME , SYSDATE dbms_random.VALUE * 10 dt FROM dual CONNECT BY ROWNUM <=10;

       CREATE INDEX idx_nonfbi ON t_fbi(dt);

       DROP INDEX idx_fbi_1;CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));

       SELECT * FROM t_fbi WHERE trunc(dt)= to_date('2022-09-21','yyyy-mm-dd');

       --不建议使用

       SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2022-09-21';

       --LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;

       SELECT * FROM t_loop;

       --逐行提交 DECLARE BEGIN FOR cur IN(SELECT * FROM user_objects)LOOP

       INSERT INTO t_loop VALUES cur;

       COMMIT;END LOOP;END;

       --模拟批量提交 DECLARE v_count NUMBER;BEGIN FOR cur IN(SELECT * FROM user_objects)LOOP

       INSERT INTO t_loop VALUES cur;

       v_count := v_count 1;

       IF v_count >= 100 THEN COMMIT;

       END IF;END LOOP;COMMIT;END;

       --真正的批量提交 DECLARE CURSOR cur IS

       SELECT * FROM user_objects;TYPE rec IS TABLE OF user_objects%ROWTYPE;recs rec;BEGIN OPEN cur;WHILE(TRUE)LOOP

       FETCH cur BULK COLLECT

       INTO recs LIMIT 100;

       --forall 实现批量

       FORALL i IN 1..recs.COUNT

       INSERT INTO t_loop VALUES recs(i);

       COMMIT;

       EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;

       --悲观锁定/乐观锁定

       DROP TABLE t_lock PURGE;CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

       SELECT * FROM t_lock;

       --常见的实现逻辑，隐含bug DECLARE v_cnt NUMBER;BEGIN--这里有并发性的bug SELECT MAX(ID)INTO v_cnt FROM t_lock;

       --here for other operation v_cnt := v_cnt 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;END;

       --高并发环境下，安全的实现逻辑 DECLARE v_cnt NUMBER;BEGIN--对指定的行取得lock SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;--在有lock的情况下继续下面的操作

       SELECT MAX(ID)INTO v_cnt FROM t_lock;

       --here for other operation v_cnt := v_cnt 1;INSERT INTO t_lock(ID)VALUES(v_cnt);COMMIT;--提交并且释放lock END;

       --硬解析/软解析

       DROP TABLE t_hard PURGE;CREATE TABLE t_hard(ID INT);

       SELECT * FROM t_hard;

       DECLARE sql_1 VARCHAR2(200);BEGIN--hard parse--java中的同等语句是 Statement.execute()FOR i IN 1..1000 LOOP

       sql_1 := 'insert into t_hard(id)values(' || i || ')';

       EXECUTE IMMEDIATE sql_1;END LOOP;COMMIT;

       --soft parse--java中的同等语句是 PreparedStatement.execute()sql_1 := 'insert into t_hard(id)values(:id)';FOR i IN 1..1000 LOOP

       EXECUTE IMMEDIATE sql_1

       USING i;END LOOP;COMMIT;END;

       --正确的分页算法

       SELECT * FROM(SELECT a.*, ROWNUM rn

       FROM(SELECT * FROM t_employees ORDER BY first_name)a

       WHERE ROWNUM <= 500)WHERE rn > 480;

       --分页算法(why not this one)SELECT a.*, ROWNUM rn FROM(SELECT * FROM t_employees ORDER BY first_name)a WHERE ROWNUM <= 500 AND ROWNUM > 480;

       --分页算法(why not this one)SELECT b.* FROM(SELECT a.*, ROWNUM rn

       FROM t_employees a

       WHERE ROWNUM < = 500

       ORDER BY first_name)b WHERE b.rn > 480;--OLAP--小计合计 SELECT CASE

       WHEN a.deptno IS NULL THEN

       '合计'

       WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN

       '小计'

       ELSE

       '' || a.deptno

       END deptno，a.empno，a.ename，SUM(a.sal)total_sal FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

       --分组排序 SELECT a.deptno，a.empno，a.ename，a.sal，--可跳跃的rank

       rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r1，--密集型rank

       dense_rank()over(PARTITION BY a.deptno ORDER BY a.sal DESC)r2，--不分组排序

       rank()over(ORDER BY sal DESC)r3 FROM scott.emp a ORDER BY a.deptno,a.sal DESC;--当前行数据和前/后n行的数据比较 SELECT a.empno，a.ename，a.sal，--上面一行

       lag(a.sal)over(ORDER BY a.sal DESC)lag_1，--下面三行

       lead(a.sal, 3)over(ORDER BY a.sal DESC)lead_3 FROM scott.emp a ORDER BY a.sal DESC;