PL/SQL从自定义查询中提取SELECT语句列名称 [英] PL/SQL extract SELECT statement column names from custom query

问题描述

我有一个自定义查询，我需要知道如何从中提取列名.我已经在寻找任何方法来做到这一点:

I have a custom query and I need to know how to extract colum names from this. I've already searched for any way to do it:

• 正则表达式:我已经构建了此可以或多或少地获得我想要的东西，但是它不起作用(我正在使用Toad 12.6.0.53).

• Regex: I've build this one to get more or less what I want but it won't work (I'm using Toad 12.6.0.53).

这是我尝试测试我的正则表达式的代码:

This is the code I have tried to test my regex:

DECLARE
  v_SQL VARCHAR2(32767 CHAR);
  v_Result VARCHAR2(32767 CHAR);
BEGIN 
  v_SQL := 'SELECT 
  ALIASBASE.CDCMPANY, 
  ALIASBASE.PAFCODCTR, 
  ALIASBASE.PAFDTSCAD, 
  NUMFLUSSO.NUMERO, 
  ALIASBASE.PAFDESC, 
  ALIASBASE.PAFTYPE, 
  ALIASBASE.PAFNATURE, 
  ALIASBASE.PAFRECEIV, 
  ALIASBASE.PAFCAUSAL, 
  ALIASBASE.PAFCODDNE, 
  ALIASBASE.PAFMATURITY
  FROM ALBSIAE';

  BEGIN
    SELECT 
      REGEXP_SUBSTR (v_SQL, '(?:\s*(?=\w+\.|.*as\s+|)(\*|\w+)(?=\s*(?=,|from)))',1, NULL,'i') REGEX_RESULT 
    INTO
      v_Result
    FROM DUAL;
  END;

  DBMS_OUTPUT.put_line('v_Result: ' || v_Result);
END;

但是最后v_Result为空.

But ant the end the v_Result is empty.

• 另一种方法是在表列之间循环，如我所见此处，但我认为是不是我的情况.

• Another way is to loop between my table columns,as I've seen here and here, but I think is not my case.

所以，我想知道是否还有其他方法可以执行此操作，我对正则表达式是否有错?里面有什么问题吗? Oracle是否具有其他正则表达式语法?

So, I want to know if is there any other way to do this, am I wrong with the regex? Is something wrong in it? Does Oracle have other regex syntax?

推荐答案

您可以使用 DBMS_SQL.parse 解析语句，并使用 DBMS_SQL.describe_columns 获取列:

You can parse the statement with DBMS_SQL.parse and get the columns with DBMS_SQL.describe_columns:

DECLARE
  v_SQL VARCHAR2(32767 CHAR);
  v_Result VARCHAR2(32767 CHAR) := '';
  l_cursor        PLS_INTEGER;
  l_col_cnt       PLS_INTEGER;
  i               PLS_INTEGER;
  l_desc_tab      DBMS_SQL.desc_tab;
BEGIN 
  v_SQL := q'[SELECT 1 as "MY_NUMBER", 'z' as "MY_CHAR" from dual]';
  l_cursor := DBMS_SQL.open_cursor;
  DBMS_SQL.parse(l_cursor, v_SQL, DBMS_SQL.native);
  DBMS_SQL.describe_columns(l_cursor, l_col_cnt, l_desc_tab);

  FOR i IN 1 .. l_col_cnt LOOP
    v_Result := v_Result ||' ' || l_desc_tab(i).col_name;
  END LOOP;

  DBMS_OUTPUT.put_line('v_Result: ' || v_Result);

END;
/

这篇关于PL/SQL从自定义查询中提取SELECT语句列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！