本文介绍了从自定义子查询中选择列名称/别名列表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在Oracle中,有没有一种方法可以选择从别名定制查询返回的所有列?作为一个例子,假设我们有一个查询如下:

pre $ SELECT FIRST_NAME AS COL1,LAST_NAME AS COL2,ADDRESS AS COL3
FROM PEOPLE

我想知道是否可以返回一个封装查询:

  COL1 
COL2
COL3


解决方案

这里是如何在PL / SQL中完成的。不知道是否只有直接的oracle SQL才有可能。

  DECLARE 
类型RefCursor_Type是REF CURSOR;

D_RefCur RefCursor_Type;
D_DescriptionTable DBMS_SQL.DESC_TAB2;
D_ColumnCount INTEGER;
D_CursorHandle INTEGER;
BEGIN
OPEN D_RefCur
'SELECT FIRST_NAME AS COL1,LAST_NAME AS COL2,ADDRESS AS COL3 FROM PEOPLE';

D_CursorHandle:= DBMS_SQL.to_cursor_number(D_RefCur);

DBMS_SQL.DESCRIBE_COLUMNS2(D_CursorHandle,
D_ColumnCount,
D_DescriptionTable);


对于idx IN 1 .. D_ColumnCount
LOOP
DBMS_OUTPUT.put_line(D_DescriptionTable(idx).col_name);
END LOOP;
END;


In Oracle, is there a way to select all the columns that are returned from a custom query with aliases? As an example, let's say we have a query as the following:

    SELECT FIRST_NAME AS COL1, LAST_NAME AS COL2, ADDRESS AS COL3
    FROM PEOPLE

I would like to know if an encapsulating query can be made that would return:

    COL1
    COL2
    COL3
解决方案

Here is how to do it in PL/SQL. Don't know if it is possible with straight oracle SQL only. You could always encapulate it in some kind of function if needed.

DECLARE
   TYPE RefCursor_Type IS REF CURSOR;

   D_RefCur             RefCursor_Type;
   D_DescriptionTable   DBMS_SQL.DESC_TAB2;
   D_ColumnCount        INTEGER;
   D_CursorHandle       INTEGER;
BEGIN
   OPEN D_RefCur
   FOR 'SELECT FIRST_NAME AS COL1, LAST_NAME AS COL2, ADDRESS AS COL3 FROM PEOPLE';

   D_CursorHandle := DBMS_SQL.to_cursor_number (D_RefCur);

   DBMS_SQL.DESCRIBE_COLUMNS2 (D_CursorHandle,
                               D_ColumnCount,
                               D_DescriptionTable);


   FOR idx IN 1 .. D_ColumnCount
   LOOP
      DBMS_OUTPUT.put_line (D_DescriptionTable (idx).col_name);
   END LOOP;
END;

这篇关于从自定义子查询中选择列名称/别名列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-22 11:56
查看更多