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;