DELETE FROM JDMOV_M001 WHERE JDMOV_M001.DISK_ID IN (SELECT DISK_ID FROM JDMOV_M001 WHERE JDMOV_M001.MOVIE_ID = 'help1');
DELETE FROM JDMOV_V001 WHERE JDMOV_M001.DISK_ID IN (SELECT DISK_ID FROM JDMOV_M001 WHERE JDMOV_M001.MOVIE_ID = 'help1');
一样的sql;DELETE FROM JDMOV_M002 WHERE JDMOV_M002.MOVIE_ID = 'help1';
SELECT DISK_ID FROM JDMOV_M001 WHERE JDMOV_M001.MOVIE_ID = 'help1';
是什么意思?
DELETE FROM JDMOV_V001 WHERE JDMOV_M001.DISK_ID IN (SELECT DISK_ID FROM JDMOV_M001 WHERE JDMOV_M001.MOVIE_ID = 'help1');
一样的sql;DELETE FROM JDMOV_M002 WHERE JDMOV_M002.MOVIE_ID = 'help1';
SELECT DISK_ID FROM JDMOV_M001 WHERE JDMOV_M001.MOVIE_ID = 'help1';
是什么意思?
解决方案 »
- 问一个oracle中建立表空间的问题。
- 请问 database links的密码怎么解密
- win7下安装Toad for Oracle 9.7报错
- 请教触发器的写法,帮忙写这个最简单的触发器
- tns不能启动?
- 监测服务器是否可以?
- 哪里有10g客户端下载啊!!!我找遍了没找到,各位爷们帮忙啊!!!急!!!!!!
- 提一个菜鸟问题:数据表的字段值只有四五种情况时,该字段类型是使用Number(1)好,还是Char(1)好?
- 请问:Oracle 默认能提供的最大物理Connection数
- vmware esxi虚拟主机部署ORACLE 11G RAC--咨询帖
- 难道Oracle8i不支持INNER JOIN?
- 关于创建触发器的问题!!!!
DELETE FROM JDMOV_V001 WHERE JDMOV_M001.DISK_ID IN (SELECT DISK_ID FROM JDMOV_M001 WHERE JDMOV_M001.MOVIE_ID = 'help1');
你的意思是用一条DELETE语句同时执行上面这两步操作?一个SQL语句对两个不同表进行DELETE?
begin
DELETE FROM JDMOV_M002 WHERE JDMOV_M002.MOVIE_ID = 'help1';
DELETE FROM JDMOV_V001
WHERE JDMOV_M001.DISK_ID IN
(SELECT DISK_ID FROM OV_M001 WHERE JDMOV_M001.MOVIE_ID = 'help1');
commit;
end;以后每次你只要执行下句就可以同时完成上述操作。
execute delete_2_table;
begin
DELETE FROM JDMOV_M002 WHERE JDMOV_M002.MOVIE_ID = 'help1';
//此处追加一行
DELETE FROM JDMOV_M001 WHERE JDMOV_M001.MOVIE_ID = 'help1'; DELETE FROM JDMOV_V001
WHERE JDMOV_M001.DISK_ID IN
(SELECT DISK_ID FROM JDMOV_M001 WHERE JDMOV_M001.MOVIE_ID = 'help1');
commit;
end;以后每次你只要执行下句就可以同时完成上述操作。
execute delete_2_table;
整个过程结束。
在强调效率的情况下
应当把这个语句改写为存储过程
CURSOR C1 IS
SELECT DISK_ID
FROM JDMOV_M001
WHERE MOVIE_ID = 'help1';BEGIN
FOR I IN C1
LOOP
DELETE FROM JDMOV_V001 WHERE DISK_ID = I.DISK_ID;
END LOOP;
;
在这种批量更新的情况下,因为删除语句都是相同的格式:
DELETE FROM JDMOV_V001 WHERE DISK_ID = XXX
所以,oracle的优化器会使用共享池中的编译方案,只需编译将该语句编译一次
就可以重复使用,从而使执行效率大大提高
这就是所谓的“绑定变量”(似于jdbc中的PrepareStatement)