- 浏览: 1044700 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (538)
- 奇文共赏 (36)
- spring (13)
- hibernate (10)
- AOP/Aspectj (9)
- spring security (7)
- lucence (5)
- compass (3)
- jbmp (2)
- jboss rule(drools) (0)
- birt (1)
- jasper (1)
- cxf (3)
- flex (98)
- webgis (6)
- 设计模式 (1)
- 代码重构 (2)
- log4j (1)
- tomcat (9)
- 神品音乐 (1)
- 工作计划 (2)
- appfuse (1)
- svn (4)
- 寻章摘句 (3)
- eclipse (10)
- arcgis api for flex (1)
- 算法 (5)
- opengis-cs (1)
- bug心得 (13)
- 图标 (1)
- software&key (14)
- java (17)
- 搞笑视频 (13)
- sqlserver (9)
- postgresql (1)
- postgis (0)
- geoserver (5)
- 日子 (50)
- 水晶报表 (1)
- 绝对电影 (3)
- Alternativa3D (1)
- 酷站大全 (10)
- c++ (5)
- oracle (17)
- oracle spatial (25)
- flashbuilder4 (3)
- TweenLite (1)
- DailyBuild (6)
- 华山论贱 (5)
- 系统性能 (5)
- 经典古文 (6)
- SOA/SCA/OSGI (6)
- jira (2)
- Hadoop生态圈(hadoop/hbase/pig/hive/zookeeper) (37)
- 风水 (1)
- linux操作基础 (17)
- 经济 (4)
- 茶 (3)
- JUnit (1)
- C# dotNet (1)
- netbeans (1)
- Java2D (1)
- QT4 (1)
- google Test/Mock/AutoTest (3)
- maven (1)
- 3d/OSG (1)
- Eclipse RCP (3)
- CUDA (1)
- Access control (0)
- http://linux.chinaunix.net/techdoc/beginner/2008/01/29/977725.shtml (1)
- redis (1)
最新评论
-
dove19900520:
朋友,你确定你的标题跟文章内容对应???
tomcat控制浏览器不缓存 -
wussrc:
我只想说牛逼,就我接触过的那点云计算的东西,仔细想想还真是这么 ...
别样解释云计算,太TM天才跨界了 -
hw_imxy:
endpoint="/Hello/messagebr ...
flex+java代码分两个工程 -
gaohejie:
rsrsdgrfdh坎坎坷坷
Flex 与 Spring 集成 -
李涤尘:
谢谢。不过说得有点太罗嗦了。
Oracle数据库数据的导入及导出(转)
http://space.itpub.net/219982/viewspace-619978
好东西,大家赶紧收藏吧~~~
--行列转换 行转列
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.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;
--行列转换 列转行 分割
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
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.rn - 1 = 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;
好东西,大家赶紧收藏吧~~~
--行列转换 行转列
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.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;
--行列转换 列转行 分割
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
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.rn - 1 = 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 连接数相关
2012-12-26 09:54 957http://www.ltesting.net/html/26 ... -
Oracle 数据库下 查询当前用户下所有表的记录总数
2010-03-30 14:03 1490select t.table_name,t.num_rows, ... -
详谈Oracle优化CPU使用率
2010-03-29 08:07 1887http://database.51cto.com/art/2 ... -
表之间插入数据
2010-03-03 16:26 1168insert into usersraw values() s ... -
ORACLE优化SQL语句,提高效率(3)
2009-11-22 20:05 1158http://space.itpub.net/?uid-219 ... -
ORACLE优化SQL语句,提高效率(2)
2009-11-22 20:05 1148http://space.itpub.net/?uid-219 ... -
ORACLE优化SQL语句,提高效率(1)
2009-11-22 20:04 1084http://space.itpub.net/?uid-219 ... -
"the account is locked"的解决方法
2009-08-31 21:11 1561用sys以sysdba的身份来进行登录,在sys状态是lo ... -
两个字段内容连接起来
2009-08-03 16:12 1195concat函数即可,如果是数字,他会变成字符串之后连接起来 ... -
分析总结:关于两个表关联进行更新
2009-05-19 18:03 1954http://www.oracle.com.cn/viewth ... -
如何在亿级记录表中创建索引
2009-03-30 14:46 1120http://ninedns.com/oracle/20074 ... -
Oracle数据导入导出imp/exp命令
2009-03-30 08:56 900http://www.cnblogs.com/jason_lb ... -
oracle大表分区的一点点心得
2009-03-25 08:01 2354http://www.knowsky.com/388420.h ... -
管理好ORACLE数据表的几个建议
2009-03-25 07:57 925http://oracle.chinaitlab.com/in ... -
oracle IMP 命令详解
2009-03-24 17:09 1609http://www.bokee.net/bloggermod ... -
Oracle数据库数据的导入及导出(转)
2009-03-24 16:05 2015Oracle数据库数据的导入 ...
相关推荐
子查询是嵌套在其他SQL语句中的查询,可以作为查询的一部分提供临时结果。 Oracle支持多种数据类型,如: - `NUMBER`:用于数值存储,可以存储整数和小数。 - `VARCHAR2`:可变长度的字符串,最大长度为2000字符。 ...
根据提供的文件信息,我们可以归纳出以下几个关键的知识点: ### 1. 获取当前月份的开始日期与结束...以上是对给定文档中几个主要知识点的总结与解释,希望能帮助你更好地理解和掌握这些Oracle SQL语句的使用方法。
最后,"Oracle SQL精妙SQL语句讲解"和"SQL语句性能调整原则"会涵盖如何在Oracle环境中优化SQL。例如,Oracle的绑定变量可以防止SQL语句的硬解析,提高执行效率;使用物化视图和快速全局索引可以进一步加速查询。 ...
在Oracle SQL中,获取当前日期所在月份的第一天和最后一天是常见的需求之一。这里提供了两种方法来实现这一功能。 #### 方法一:获取下个月初与下个月末 ```sql SELECT TRUNC(TRUNC(SYSDATE, 'DAY') + 1, 'MONTH') ...
以下是对各部分提到的SQL语句及其功能的详细解析: ### 1. 假数据插入:`SELECT * INTO b FROM a WHERE 1<>1` 这一语句看似简单,实则巧妙地用于创建一个空表结构而不插入任何数据。由于`WHERE 1<>1`条件永远不...
"精妙SQL语句介绍.doc"这份文档很可能包含了以下关键知识点: 1. **SQL基础**:文档可能首先介绍了SQL的基本概念,包括SELECT语句用于查询数据,INSERT用于添加新记录,UPDATE用于修改现有数据,以及DELETE用于删除...
### SQL语句的高级应用 #### 1. 条件筛选与逻辑判断 在SQL中,`1<>1`这种写法实际上永远不会返回任何结果,因为`1<>1`(即`1!=1`)总是假的。这通常被用作一种技巧,在`WHERE`子句中排除所有行,从而使查询不返回...
根据提供的文件信息,我们可以归纳出以下Oracle SQL相关的高级知识点: ### 一、向表中插入不存在的数据 **知识点概述:** 在实际应用中,我们常常需要将数据从一个表(表A)移动到另一个表(表B),但并不是所有...
【SQL语句基础】 在SQL(Structured Query Language)中,创建和管理数据库是核心功能。以下是一些基本的SQL操作: 1. 创建数据库:`CREATE DATABASE database-name` 用于创建一个新的数据库,其中`database-name`...
"精妙Sql语句"这个主题涵盖了SQL的各种高级用法和技巧,旨在帮助用户更高效地操作和查询数据。以下是关于SQL的一些核心知识点,以及如何在实际工作中运用这些技巧: 1. **基础查询**:SQL的基础包括SELECT、FROM、...
以下语句将删除主表`info`中在副表`infobz`中不存在的记录: ```sql delete from info where not exists (select * from infobz where info.infid=infobz.infid) ``` 4. **自连接查询**: 自连接是将同一张表...
10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1...
10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1...
理解并熟练运用这些SQL语句是进行Oracle编程的基础。此外,还会深入讨论如何优化SQL查询,提高数据检索的速度,这对于大型数据库的性能至关重要。 其次,PL/SQL作为Oracle的内置过程式语言,是编写存储过程、触发器...
### SQL 编程艺术:存储过程与函数的精妙之别 #### 一、数据库概述 数据库是一个存储、检索和管理数据的系统。它允许用户存储结构化的数据集合,并可以通过多种方式查询和操作这些数据。数据库通常由数据库管理...
3. **精妙SQL语句介绍**:SQL是与Oracle交互的主要语言,高效且复杂的SQL查询能显著提升数据库操作效率。这可能涉及到子查询、联接操作、窗口函数、集合操作以及自定义SQL函数等高级技术,掌握这些能优化数据处理...
Oracle 数据库的体系结构设计得非常复杂且精妙,它能够高效地处理大量的并发请求,并确保数据的安全性和一致性。Oracle 的基本体系结构主要包括内存结构、进程结构以及存储结构。 #### 二、内存结构详解 Oracle 的...
当SQL语句首次执行时,Oracle会对其进行解析并将其结果保存在共享池的Library Cache中。之后再次执行相同SQL时,Oracle可以直接复用解析结果,提高系统性能。 2. **数据缓冲区(Database Buffers)**:用于存储从磁盘...