-- 悲观锁定/乐观锁定
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 otheroperation
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 otheroperation
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.EⅩEcute()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.EⅩEcute()
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 rnFROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM | 
注册
登录
