有谁知道forall和merge的不同之处吗?最好是每个给个例子,另外我需要一个forall和merge一起使用的例子!请高手帮忙!!
解决方案 »
- oracle function是否支持返回多个值
- 中秋节快乐,请教行如何转换为列(矩阵转换)
- ******** 急: 在oracle中可以对视图建立触发器吗
- 请教一ORACLE错误:ORA-00911 如何解决?很急,在线等待解答
- oracle 10g 求自动增加字段SQL语句
- 在oracle中的java sources中写的java程序,在用到sun.net.ftp.ftpClient里的nameList()方法时告诉无些方法,应该怎么做
- 急需 oracle 7.x
- 1000分征求oracle数据库对于海量数据的存储、查询解决方案
- 插入特殊字符的处理方法
- 急急急!!麻烦高手帮忙!!!
- 请教大家一个sql语句,谢谢!!!
- ora:00933 错误,sql没有结束,大家帮看看!!
另外,forall只支持一行的sQL语句!
merge主要用于数据仓库中大了数据的转移.
FORALL <index_name> IN <bounds_clause>
<sql_statement>
SAVE EXCEPTIONS;Bounds_Clause
<lower_bound> .. <upper_bound>
or
INDICES OF <collection> BETWEEN <lower_bound> AND <upper_bound>
or
VALUES OF <index_collection>
CREATE TABLE t1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE t2 AS SELECT * FROM t1;CREATE OR REPLACE PROCEDURE forall_delete(iterations PLS_INTEGER) ISTYPE NumTab IS TABLE OF t1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF t1.pname%TYPE INDEX BY PLS_INTEGER;
pnums NumTab;
pnames NameTab;a INTEGER;
b INTEGER;
c INTEGER;BEGIN
FOR j IN 1..iterations LOOP -- load index-by tables
pnums(j) := j;
pnames(j) := 'Part No. ' || TO_CHAR(j);
END LOOP; a := dbms_utility.get_time; FOR i IN 1..iterations LOOP -- use FOR loop
INSERT INTO t1 VALUES (pnums(i), pnames(i));
END LOOP; b := dbms_utility.get_time; FORALL i IN 1 .. iterations -- use FORALL statement
INSERT INTO t2 VALUES (pnums(i), pnames(i)); c := dbms_utility.get_time; dbms_output.put_line('Execution Time (secs)');
dbms_output.put_line('---------------------');
dbms_output.put_line('FOR loop: ' || TO_CHAR((b - a)/100));
dbms_output.put_line('FORALL: ' || TO_CHAR((c - b)/100));
COMMIT;
END;
/set serveroutput on
exec forall_delete(500);
exec forall_delete(5000);
exec forall_delete(50000); FORALL with Part of a Collection CREATE TABLE test (
deptno NUMBER(3,0),
empname VARCHAR2(20));INSERT INTO test VALUES (100, 'Morgan');
INSERT INTO test VALUES (200, 'Cline');
INSERT INTO test VALUES (101, 'Lofstrom');
INSERT INTO test VALUES (102, 'Havemeyer');
INSERT INTO test VALUES (202, 'Starr');
INSERT INTO test VALUES (201, 'Math');
INSERT INTO test VALUES (103, 'Scott');
INSERT INTO test VALUES (104, 'Nishimura');
INSERT INTO test VALUES (105, 'Shastri');
INSERT INTO test VALUES (106, 'Mirza');
COMMIT;SELECT * FROM test;CREATE OR REPLACE PROCEDURE collection_part ISTYPE NumList IS VARRAY(10) OF NUMBER;depts NumList := NumList(100,200,101,102,202,201,103,104,105,106);BEGIN
FORALL j IN 4..7 -- use only part of varray
DELETE FROM test WHERE deptno = depts(j);
COMMIT;
END collection_part;
/SELECT * FROM test;
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;
CREATE TABLE employee (
employee_id NUMBER(5),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
dept_no NUMBER(2),
salary NUMBER(10))
TABLESPACE data_sml;INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);
INSERT INTO employee VALUES (2, 'Jack', 'Cline', 20, 100000);
INSERT INTO employee VALUES (3, 'Elizabeth', 'Scott', 20, 50000);
INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);
INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 30000);
INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 70000);
INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);
COMMIT;CREATE TABLE bonuses (
employee_id NUMBER, bonus NUMBER DEFAULT 100)
TABLESPACE data_sml;INSERT INTO bonuses (employee_id) VALUES (1);
INSERT INTO bonuses (employee_id) VALUES (2);
INSERT INTO bonuses (employee_id) VALUES (4);
INSERT INTO bonuses (employee_id) VALUES (6);
INSERT INTO bonuses (employee_id) VALUES (7);
COMMIT;MERGE INTO bonuses B
USING (
SELECT employee_id, salary
FROM employee
WHERE dept_no =20) E
ON (B.employee_id = E.employee_id)
WHEN MATCHED THEN
UPDATE SET B.bonus = E.salary * 0.1
WHEN NOT MATCHED THEN
INSERT (B.employee_id, B.bonus)
VALUES (E.employee_id, E.salary * 0.05);