我在Oracle数据库中有以下情况:
表T1具有属性ID和T2_ID。 表T2具有属性ID和T3_ID。 表T3具有属性ID和T4_ID。 表T4具有属性ID。
每个TX_ID都是指相关表的ID(例如:T3_ID =表T3中的ID)。
给定T1中的ID,我想编写一个SQL脚本来了解相关的T3_ID和T4_ID,将它们存储在单个变量(仅包含检索的值)中,并将这些值用于DELETE / SELECT的其他操作(如删除T * _ID = T3_ID的另一个T *表中的所有行)。
在Oracle中有没有办法做到这一点?
I have in an Oracle database the following situation:
Table T1 with attributes ID and T2_ID. Table T2 with attributes ID and T3_ID. Table T3 with attributes ID and T4_ID. Table T4 with attribute ID.
Each TX_ID is referring to the ID of the related table (example: T3_ID = ID in table T3).
Given an ID in T1, I would like to write a SQL-script to know the related T3_ID and T4_ID, store them in single variables (containing only the retrieved values) and use those values for other operations of DELETE/SELECT in the same script (like DELETE all the rows from another T* table which have T*_ID = T3_ID).
Is there a way to do that in Oracle?
最满意答案
在PLSQL块中,使用类似这样的方法来获取变量v_t3_id和v_t4_id数据,并在delte / update语句中使用它。
select t3.id,t4.id into v_t3_id,v_t4_id from t1 left join t2 on t1.t2_id=t2.id left join t3 on t2.t3_id=t3.id left join t4 on t3.t4_id=t4.id where t1.id=<your value>如果数据集很庞大,那么谷歌就如何使用BULK COLLECT来做同样的事情。
Inside a PLSQL block, use something like this to fetch data in variables v_t3_id and v_t4_id and use that in your delte/update statements.
select t3.id,t4.id into v_t3_id,v_t4_id from t1 left join t2 on t1.t2_id=t2.id left join t3 on t2.t3_id=t3.id left join t4 on t3.t4_id=t4.id where t1.id=<your value>If data set is huge, then google on how to use BULK COLLECT to do the same.
更多推荐
发布评论