为什么我的Oracle脚本不提供任何输出？

px9o7tmv 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(134)

强文本 *``大家好，

我刚开始与甲骨文和尝试写一个脚本下面的问题陈述。
问题说明：我想对两个表执行对账表A和表B。基于标记为1的Map表中存在的字段。在本例中，我使用动态SQL来处理运行时列和连接条件，但我的查询没有给出任何输出。请给予我解决方案或其他任何方法，通过我可以在Oracle中执行此动态对账？

对账查询-注意--以下查询正在运行，没有任何错误，但没有给出任何输出。我不想使用Cursor打印结果，因为我有Million条记录。--代码

DECLARE
v_sql VARCHAR2(32767);
v_columns VARCHAR2(32767);
v_join_condition VARCHAR2(32767);
v_column_list SYS.ODCIVARCHAR2LIST;
v_result SYS_REFCURSOR;
BEGIN
SELECT LISTAGG(Column_name, ',') WITHIN GROUP (ORDER BY Column_name)
INTO v_columns
FROM MappIng_table
WHERE Flag = 1;
-- Split the column list into individual column names
v_column_list := SYS.ODCIVARCHAR2LIST();
v_column_list.EXTEND(REGEXP_COUNT(v_columns, ',') + 1);
FOR i IN 1..v_column_list.COUNT LOOP
v_column_list(i) := REGEXP_SUBSTR(v_columns, '[^,]+', 1, i);   
END LOOP;

-- Build the join condition dynamically
v_join_condition := '';
FOR i IN 1..v_column_list.COUNT LOOP
IF i > 1 THEN
v_join_condition := v_join_condition || ' AND ';
END IF;
v_join_condition := v_join_condition || 'a.' || v_column_list(i) ||
'=b.' || v_column_list(i);
END LOOP;
v_sql := 'SELECT * FROM Table_A a full outer JOIN Table_B b ON ' || 
v_join_condition;
dbms_output.put_line(v_sql); 
EXECUTE IMMEDIATE v_sql;
END;

oracle

来源：https://stackoverflow.com/questions/76919603/why-my-oracle-script-not-giving-any-output

1条答案

按热度按时间

ohfgkhjo1#

相同的表将用于JOIN示例。用你的两张table代替。

create table EMPLOYEES as select * from HR.EMPLOYEES;

据我所知，你的目标是：

SELECT * FROM EMPLOYEES a full outer JOIN EMPLOYEES b ON a.EMPLOYEE_ID = b.EMPLOYEE_ID AND a.FIRST_NAME = b.FIRST_NAME AND a.LAST_NAME = b.LAST_NAME;

Map表：

CREATE TABLE MappIng_table (
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (CACHE 5) PRIMARY KEY,
  Column_name  VARCHAR2(128),
  Flag NUMBER default 0
);

一些联接条件列：

insert into MAPPING_TABLE (column_name, flag) values ('EMPLOYEE_ID', 1);
insert into MAPPING_TABLE (column_name, flag) values ('FIRST_NAME', 1);
insert into MAPPING_TABLE (column_name, flag) values ('LAST_NAME', 1);
insert into MAPPING_TABLE (column_name) values ('EMAIL');
commit;

个人加盟条件：

select 'a.' || Column_name || ' = b.' || Column_name as join_condition from MAPPING_TABLE where Flag = 1;

整个连接条件：

SELECT LISTAGG(jc.join_condition, ' AND ') WITHIN GROUP (order by jc.join_condition) as v_join_condition
  FROM (select 'a.' || Column_name || ' = b.' || Column_name as join_condition from MAPPING_TABLE where Flag = 1) jc;

返回完整语句的查询：

with v_join_condition as (
    SELECT LISTAGG(jc.join_condition, ' AND ') WITHIN GROUP (order by jc.join_condition) as v_join_condition
        FROM (select 'a.' || Column_name || ' = b.' || Column_name as join_condition 
                from MAPPING_TABLE where Flag = 1) jc)
select 'SELECT * FROM EMPLOYEES a full outer JOIN EMPLOYEES b ON ' || vjc.v_join_condition as v_sql
    from v_join_condition vjc;

赞(0）回复(0）举报 2023-10-16

我来回答

为什么我的Oracle脚本不提供任何输出？

1条答案

相关问题

热门标签

最新问答