内部联接两个表变量，并从c#应用程序中的匿名plsql块返回一个输出游标

Question

我有这样的场景：

• 我需要将许多联接的结果(15个具有各种过滤器的表(插入到表变量var_TB_PROJECT中
• 然后将许多其他联接的结果(8个具有各种过滤器的表(插入另一个表变量var_TB_CAMERAS
• 最后，内部连接这两个表变量

所有这些都在一个匿名plsql块中，因为我不能在源数据库中创建任何对象(没有权限(。

这个匿名plsql块将在C#应用程序中执行，该应用程序将返回一个输出游标作为下面的示例代码。

有可能做到吗？

static void TestBlock()
{
string conString = ConfigManager.GetConnectionStringByName("CI_L3"); 
OracleConnection con = new OracleConnection();
con.ConnectionString = conString;
con.Open();
string cmdtxt = @"
DECLARE
type rectyp is record(TABLE1.FIELD1%TYPE, TABLE2.FIELD1%TYPE, TABLE3.FIELD1%TYPE, ... many fields ....  );
type tpTbl is table of rectyp;
var_TB_PROJETO tpTbl;
var_TB_CAMERAS tpTbl;
BEGIN
-- load var_TB_PROJETO
select FIELD1, FIEL2, FIELN .... bulk collect into var_TB_PROJETO
from TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.FID
INNER JOIN TABLE3 ON TABLE2.ID = TABLE3.FID .... 
.
.
-- many tables ...
.
.
-- load var_TB_CAMERAS
select FIELD1, FIEL2, FIELN .... bulk collect into var_TB_CAMERAS
from TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.FID
INNER JOIN TABLE3 ON TABLE2.ID = TABLE3.FID .... 
.
.
-- many tables ...
.
.
OPEN :1 for SELECT * FROM var_TB_PROJETO INNER JOIN var_TB_CAMERAS 
ON var_TB_PROJETO.ID = var_TB_CAMERAS.ID;
END;";
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = cmdtxt;
OracleParameter p1 = cmd.Parameters.Add("refcursor1", OracleDbType.RefCursor);  
p1.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleDataReader dr1 = ((OracleRefCursor)cmd.Parameters[0].Value).GetDataReader();
while (dr1.Read()) {
var st1 = dr1.GetString(0);
}
}

Answer 1

@Goldar:既然可以作为SQL语句解决，为什么要使用匿名PL/SQL块

WITH TB_PROJETO as (
select TABLE1.ID, FIELD1, FIEL2, FIELN
from TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.FID
INNER JOIN TABLE3 ON TABLE2.ID = TABLE3.FID .... 
.
.
-- many tables ...
.
.
), TB_CAMERAS as (
select TABLE1.ID, FIELD1, FIEL2, FIELN 
from TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.FID
INNER JOIN TABLE3 ON TABLE2.ID = TABLE3.FID .... 
.
.
-- many tables ...
.
.
)
SELECT *
FROM TB_PROJETO
INNER JOIN TB_CAMERAS ON TB_PROJETO.ID = TB_CAMERAS.ID;