第一篇：Oracle-SQL精妙SQL语句讲解

Oracle: SQL精妙SQL语句讲解

一、重复操作查询

--where条件得distinct systemdicid作为唯一标识 select *

from dmis_zd_systemdic t WHERE typeid = '06012'

and t.systemdicid in(select min(systemdicid)from dmis_zd_systemdic where typeid = '06012'

group by name)order by orderno;

二、检查表是否存在

select count(tname)from tab where tname = upper('表名');

三、日期函数

--返回当前日期年度的第一天

select trunc(sysdate,'year')from dual;--返回当前日期月份的第一天

select trunc(sysdate,'month')from dual;--上月最后一天

select last_day(add_months(sysdate,-1))from dual;--给定日期后最近星期几得日期

select next_day(to_date('2009-12-01', 'yyyy-mm-dd'), '星期一')next_day from dual;

四、同一张表中，根据一个字段更新另一个字段

update(select t.fgenerationtime as ftime, t.fgeneratedateall as str from dmis_fs_approvebook t where t.fgenerationtime is not null)set str = TO_CHAR(ftime, 'yyyy-mm-dd')where str is null;

五、重复数据查询 select * FROM EMP E WHERE E.ROWID >(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

六、合并不同表的数据（merge into）merge into student s using(select id, name, tel from test001)x on(s.s_id = x.id)when matched then

update set s_name = x.name when not matched then

insert(s_id, s_name, s_age)values(x.id, x.name, x.tel);commit;

七、查询执行sql（v$sql）

select t.module, t.first_load_time, t.sql_text from v$sql t order by first_load_time desc;

2、数据库精度修改处理--Create table /*drop table temp_data;*/ create table temp_data(FID VARCHAR2(40)not null, USEHOURS NUMBER(10)default 0, FVOLTAGE NUMBER(10)default 0, INVOLTAGE NUMBER(10)default 0);alter table TEMP_DATA add constraint tempfid primary key(FID);

insert into temp_data select a.fid, a.usehours, a.fvoltage, a.involtage from dmis_fs_factorymonthdetail a;

update dmis_fs_factorymonthdetail t set t.usehours = '', t.fvoltage = '', t.involtage = '';

alter table DMIS_FS_FACTORYMONTHDETAIL modify USEHOURS NUMBER(10,1);alter table DMIS_FS_FACTORYMONTHDETAIL modify FVOLTAGE NUMBER(10,1);alter table DMIS_FS_FACTORYMONTHDETAIL modify INVOLTAGE NUMBER(10,1);

update(select a.usehours as tusehours, b.usehours as fusehours, a.fvoltage as tfvoltage, b.fvoltage as ffvoltage, a.involtage as tinvoltage, b.involtage as finvoltage, a.fid as ffid, b.fid as tfid from dmis_fs_factorymonthdetail a, temp_data b where a.fid = b.fid)tt set tt.tusehours = tt.fusehours, tt.tfvoltage = tt.ffvoltage, tt.tinvoltage = tt.finvoltage where ffid = tfid;drop table temp_data;commit;

3、恢复drop掉的存储过程 用sys用户登陆，执行如下的查询：

SQL> select text from dba_source as of timestamp to_timestamp('2009-03-06 09:45:00', 'YYYY-MM-DD

HH24:MI:SS')

where

owner='IPRA'

and

name=

'P_IPACCHECK_NC' order by line;

4、删除某个用户下的对象--删除某个用户下的对象 set heading off;set feedback off;spool c:dropobj.sql;prompt--Drop constraint select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R';prompt--Drop tables select 'drop table '||table_name ||';' from user_tables;

prompt--Drop view select 'drop view ' ||view_name||';' from user_views;

prompt--Drop sequence select

--行列转换 行转列

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('2006-09-21','yyyy-mm-dd');--不建议使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-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;

第二篇：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('2006-09-21','yyyy-mm-dd');

--不建议使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-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: 2008-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('2006-09-21','yyyy-mm-dd');

--不建议使用

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd')= '2006-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;

第四篇：精妙SQL语句

精妙SQL语句

 说明：复制表(只复制结构,源表名：a 新表名：b)select * into b from a where 1<>1

 说明：拷贝表(拷贝数据,源表名：a 目标表名：b)insert into b(a, b, c)select d,e,f from b; 说明：显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate)adddate from table where table.title=a.title)b

 说明：外连接查询(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c  说明：日程安排提前五分钟提醒

select * from 日程安排 where datediff('minute',f开始时间,getdate())>5  说明：两张关联表，删除主表中已经在副表中没有的信息

delete from info where not exists(select * from infobz where info.infid=infobz.infid)

 说明：--

SQL:

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE

FROM TABLE1，(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE

FROM(SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM')= TO_CHAR(SYSDATE, 'YYYY/MM'))X，(SELECT NUM, UPD_DATE, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM')=

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM')|| '/01','YYYY/MM/DD')1 FROM Handle a)

 一个SQL语句的问题:行列转换 select * from v_temp 上面的视图结果如下: user_name role_name-------------------------系统管理员 管理员

feng 管理员

feng 一般用户

test 一般用户

想把结果变成这样: user_name role_name---------------------------系统管理员 管理员

feng 管理员,一般用户

test 一般用户

=================== create table a_test(name varchar(20),role2 varchar(20))insert into a_test values('李','管理员')insert into a_test values('张','管理员')insert into a_test values('张','一般用户')insert into a_test values('常','一般用户')

create function join_str(@content varchar(100))returns varchar(2000)as begin declare @str varchar(2000)set @str='' select @str=@str+','+rtrim(role2)from a_test where [name]=@content select @str=right(@str,len(@str)-1)return @str end go

--调用：

select [name],dbo.join_str([name])role2 from a_test group by [name]

--select distinct name,dbo.uf_test(name)from a_test  快速比较结构相同的两表

结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？

============================ 给你一个测试方法，从northwind中的orders表取数据。select * into n1 from orders select * into n2 from orders

select * from n1 select * from n2

--添加主键，然后修改n1中若干字段的若干条

alter table n1 add constraint pk_n1_id primary key(OrderID)alter table n2 add constraint pk_n2_id primary key(OrderID)

select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*)> 1

应该可以，而且将不同的记录的ID显示出来。下面的适用于双方记录一样的情况，select * from n1 where orderid in(select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*)> 1)至于双方互不存在的记录是比较好处理的--删除n1,n2中若干条记录

delete from n1 where orderID in('10728','10730')delete from n2 where orderID in('11000','11001')

--*************************************************************--双方都有该记录却不完全相同

select * from n1 where orderid in(select OrderID from(select * from n1 union select * from n2)a group by OrderID having count(*)> 1)union--n2中存在但在n1中不存的在10728,10730 select * from n1 where OrderID not in(select OrderID from n2)union--n1中存在但在n2中不存的在11000,11001 select * from n2 where OrderID not in(select OrderID from n1) 四种方法取表里n到m条纪录：

1.select top m * into 临时表(或表变量)from tablename order by columnname--将top m笔插入 set rowcount n select * from 表变量 order by columnname desc

2.select top n * from(select top m * from tablename order by columnname)a order by columnname desc

3.如果tablename里没有其他identity列，那么： select identity(int)id0,* into #temp from tablename

取n到m条的语句为：

select * from #temp where id0 >=n and id0 <= m

如果你在执行select identity(int)id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行： exec sp_dboption 你的DB名字,'select into/bulkcopy',true

4.如果表里有identity属性，那么简单：

select * from tablename where identitycol between n and m  如何删除一个表中重复的记录？

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))--f_key表示是分组字段﹐即主键字段 as begin declare @max integer,@id varchar(30),@sql varchar(7999),@type integer select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*)from ' +@t_name +' group by ' +@f_key +' having count(*)> 1' exec(@sql)open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin

select @max = @max-1 set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name)and name=@f_key if @type=56 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id if @type=167 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' exec(@sql)fetch cur_rows into @id,@max end

close cur_rows deallocate cur_rows set rowcount 0 end

select * from systypes select * from syscolumns where id = object_id('a_dist') 查询数据的最大排序问题（只能用一条语句写）

CREATE TABLE hard(qu char(11),co char(11),je numeric(3, 0))

insert into hard values('A','1',3)insert into hard values('A','2',4)insert into hard values('A','4',2)insert into hard values('A','6',9)insert into hard values('B','1',4)insert into hard values('B','2',5)insert into hard values('B','3',6)insert into hard values('C','3',4)insert into hard values('C','6',7)insert into hard values('C','2',3)

要求查询出来的结果如下：

qu co je

---------------------------A 6 9 A 2 4 B 3 6 B 2 5 C 6 7 C 3 4

就是要按qu分组，每组中取je最大的前2位！而且只能用一句sql语句！！

select * from hard a where je in(select top 2 je from hard b where a.qu=b.qu order by je)

 求删除重复记录的sql语句？

怎样把具有相同字段的纪录删除，只留下一条。例如，表test里有id,name字段

如果有name相同的记录 只留下一条，其余的删除。name的内容不定，相同的记录数不定。有没有这样的sql语句？

============================== A:一个完整的解决方案：

将重复的记录记入temp1表: select [标志字段id],count(*)into temp1 from [表名] group by [标志字段id] having count(*)>1

2、将不重复的记录记入temp1表: insert temp1 select [标志字段id],count(*)from [表名] group by [标志字段id] having count(*)=1

3、作一个包含所有不重复记录的表：

select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1)

4、删除重复表: delete [表名]

5、恢复表：

insert [表名] select * from temp2

6、删除临时表: drop table temp1 drop table temp2 ================================ B: create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))--f_key表示是分组字段﹐即主键字段 as begin declare @max integer,@id varchar(30),@sql varchar(7999),@type integer select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*)from ' +@t_name +' group by ' +@f_key +' having count(*)> 1' exec(@sql)open cur_rows

fetch cur_rows into @id,@max while @@fetch_status=0 begin

select @max = @max-1 set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name)and name=@f_key if @type=56 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id if @type=167 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' exec(@sql)fetch cur_rows into @id,@max end

close cur_rows deallocate cur_rows set rowcount 0 end

select * from systypes select * from syscolumns where id = object_id('a_dist') 行列转换--普通

假设有张学生成绩表(CJ)如下

Name Subject Result 张三 语文 80 张三 数学 90 张三 物理 85 李四 语文 85 李四 数学 92 李四 物理 82

想变成

姓名 语文 数学 物理

张三 80 90 85 李四 85 92 82

declare @sql varchar(4000)set @sql = 'select Name'

select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end)['+Subject+']'

from(select distinct Subject from CJ)as a

select @sql = @sql+' from test group by name' exec(@sql)

行列转换--合并

有表A, id pid 1 1 1 2 1 3 2 1 2 2 3 1

如何化成表B: id pid 1 1,2,3 2 1,2 3 1

创建一个合并的函数

create function fmerg(@id int)returns varchar(8000)as begin

declare @str varchar(8000)set @str=''

select @str=@str+','+cast(pid as varchar)from 表A where id=@id set @str=right(@str,len(@str)-1)return(@str)End go

--调用自定义函数得到结果

select distinct id,dbo.fmerg(id)from 表A  如何取得一个数据表的所有列名

方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。

SQL语句如下：

declare @objid int,@objname char(40)set @objname = 'tablename'

select @objid = id from sysobjects where id = object_id(@objname)

select 'Column_name' = name from syscolumns where id = @objid order by colid

或

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'

 通过SQL语句来更改用户的密码

修改别人的,需要sysadmin role

EXEC sp_password NULL, 'newpassword', 'User'

如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa  怎么判断出一个表的哪些字段不允许为空？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename  如何在数据库里找到含有相同字段的表？

a.查已知列名的情况

SELECT b.name as TableName,a.name as columnname From syscolumns a INNER JOIN sysobjects b ON a.id=b.id AND b.type='U'

AND a.name='你的字段名字'

 未知列名查所有在不同表出现过的列名

Select o.name As tablename,s1.name As columnname From syscolumns s1, sysobjects o Where s1.id = o.id And o.type = 'U' And Exists（Select 1 From syscolumns s2 Where s1.name = s2.name And s1.id <> s2.id)

 查询第xxx行数据

假设id是主键：

select * from(select top xxx * from yourtable)aa where not exists(select 1 from(select top xxx-1 * from yourtable)bb where aa.id=bb.id)

如果使用游标也是可以的

fetch absolute [number] from [cursor_name] 行数为绝对行数

 SQL Server日期计算

a.一个月的第一天

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)b.本周的星期一

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)c.一年的第一天

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)d.季度的第一天

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)e.上个月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))f.去年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))g.本月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))h.本月的第一个星期一

select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

i.本年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。

 获取表结构[把 'sysobjects' 替换 成 'tablename' 即可]

SELECT CASE IsNull(I.name, '')When '' Then '' Else '*'

End as IsPK，Object_Name(A.id)as t_name, A.name as c_name，IsNull(SubString(M.text, 1, 254), '')as pbc_init, T.name as F_DataType，CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')WHEN '' Then Cast(A.prec as varchar)

ELSE Cast(A.prec as varchar)+ ',' + Cast(A.scale as varchar)END as F_Scale，A.isnullable as F_isNullAble FROM Syscolumns as A JOIN Systypes as T

ON(A.xType = T.xUserType AND A.Id = Object_id('sysobjects'))LEFT JOIN(SysIndexes as I JOIN Syscolumns as A1

ON(I.id = A1.id and A1.id = object_id('sysobjects')and(I.status & 0x800)= 0x800 AND A1.colid <= I.keycnt))

ON(A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid))LEFT JOIN SysComments as M

ON(M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint')= 1)ORDER BY A.Colid ASC

 提取数据库内所有表的字段详细说明的SQL语句

SELECT

(case when a.colorder=1 then d.name else '' end)N'表名', a.colorder N'字段序号', a.name N'字段名'，(case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then ''else '' end)N'标识'，(case when(SELECT count(*)FROM sysobjects WHERE(name in(SELECT name FROM sysindexes

WHERE(id = a.id)AND(indid in(SELECT indid

FROM sysindexkeys

WHERE(id = a.id)AND(colid in(SELECT colid FROM syscolumns

WHERE(id = a.id)AND(name = a.name)))))))AND(xtype = 'PK'))>0 then '' else '' end)N'主键', b.name N'类型'，a.length N'占用字节数'，COLUMNPROPERTY(a.id,a.name,'PRECISION')as N'长度'，isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)as N'小数位数',(case when a.isnullable=1 then ''else '' end)N'允许空', isnull(e.text,'')N'默认值'，isnull(g.[value],'')AS N'字段说明' FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d

on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id

left join sysproperties g

on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder

 快速获取表test的记录总数[对大容量表非常有效]

快速获取表test的记录总数:

select rows from sysindexes where id = object_id('test')and indid in(0,1)

update 2 set KHXH=(ID+1)2 2行递增编号

update [23] set id1 = 'No.'+right('00000000'+id,6)where id not like 'No%' //递增

update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6)//补位递增 delete from [1] where(id%2)=1 奇数

 替换表名字段

update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/')where domurl like '%Upload/Imgswf/%'  截位

SELECT LEFT(表名, 5)

下列语句部分是Mssql语句，不可以在access中使用。

SQL分类：

DDL-数据定义语言(CREATE，ALTER，DROP，DECLARE)

DML-数据操纵语言(SELECT，DELETE，UPDATE，INSERT)

DCL-数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)

首先,简要介绍基础语句：

1、说明：创建数据库

CREATE DATABASE database-name

2、说明：删除数据库

drop database dbname

3、说明：备份sql server

---创建 备份数据的 device

USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'

---开始 备份

BACKUP DATABASE pubs TO testBack

4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

A：create table tab_new like tab_old(使用旧表创建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

5、说明：删除新表drop table tabname

6、说明：增加一个列

Alter table tabname add column col type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：添加主键： Alter table tabname add primary key(col)

说明：删除主键： Alter table tabname drop primary key(col)

8、说明：创建索引：create [unique] index idxname on tabname(col….)

删除索引：drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：create view viewname as select statement

删除视图：drop view viewname

10、说明：几个简单的基本的sql语句

选择：select * from table1 where 范围

插入：insert into table1(field1,field2)values(value1,value2)

删除：delete from table1 where