使用PL/SQL内置的DBMS_SQL包执行动态SQL

DBMS_SQL允许我们对动态游标中的列进行描述,以记录的关联数组形式返回每列的信息。该功能为通用游标处理代码提供了可能性。

当我们调用该程序时,需要声明基于DBMS_SQL.DESC_TAB集合类型的PL/SQL集合,(或者DESC_TAB2,如果我们的查询返回长度大于30字符的列名称)。

我们可以使用集合方法遍历该表,并提取有关该游标的所需信息。以下匿名块显示了在使用该内置功能时的基本步骤:

DECLARE

–打开游标

CUR PLS_INTEGER :=DBMS_SQL.OPEN_CURSOR;

COLS DBMS_SQL.DESC_TAB;

NCOLS PLS_INTEGER;

BEGIN

–解析查询语句

DBMS_SQL.PARSE(CUR, ‘SELECT hiredate,empsal FROM emp’, DBMS_SQL.NATIVE);

–检索列信息

DBMS_SQL.DESCRIBE_COLUMNS(CUR, NCOLS, COLS);

–显示每列列名

FORCOLIND IN 1 .. NCOLS LOOP

DBMS_OUTPUT.PUT_LINE(COLS(COLIND).COL_NAME);

END LOOP;

Dbms_SQL.close_cursor(cur);

END;

我们也可以使用for_query函数获得动态查询的列信息,但是不可使用DBMS_SQL。

DECLARE

–打开游标

CURINTEGER := DBMS_SQL.OPEN_CURSOR;

TABDBMS_SQL.DESC_TAB;

BEGIN

–解析查询语句

DBMS_SQL.PARSE(CUR,

‘SELECT ename, empsal,hiredate FROM emp’,

DBMS_SQL.NATIVE);

–假设有desccols这个包

TAB:= DESCCOLS.FOR_CURSOR(CUR);

DESCCOLS.SHOW(TAB);

DBMS_SQL.CLOSE_CURSOR(CUR);

TAB:= DESCCOLS.FOR_QUERY(‘SELECT * FROM emp’);

DESCCOLS.SHOW(TAB);

END;

 

使用DBMS_SQL的一个示例:

构建PL/SQL程序intab来显示用户在运行时指定的任何表的内容。

Intab构建步骤:

1、 构建并解析SELECT语句(使用OPEN_CURSOR和PARSE)。

2、 在查询中绑定所有局部变量及其占位符(使用BIND_VARIABLE)。

3、 为该查询在游标中定义每一列(使用DEFINE_COLUMN)。

4、 从数据库中执行和提取行(使用EXECUTE IMMEDIATE和FETCH_ROWS)。

5、 从取出的行中获取信息,并将其放入字符串以进行显示(使用COLUMN_VALUE)。

调用DBMS_OUTPUT包的PUT_LINE过程然后显示字符串。

构建SELECT

为从表中提取数据,必须构建SELECT语句。查询的结构由各种程序输入(表名、WHERE子句等)和数据字典内容决定。

请记住,用户不必提供列列表。相反,我们必须从数据字典视图表中识别和提取列列表。

我们在intab程序中使用的是ALL_TAB_COLUMNS视图,这样用户不仅可以查看其所拥有的表(可访问USER_TAB_COLUMNS),同时也可以查看有SELECT访问权限的任何表。

我们用下面这个游标提取表的列的信息:

CURSOR col_cur

(owner_in IN VARCHAR2,

table_in IN VARCHAR2)

IS

SELECT column_name,

date_type,

data_length,

data_precision,

data_scale

FROM all_tab_columns

WHERE owner = owner_in

AND table_name = table_in;

通过这个列游标,我们可以得到在表内每一列的名字、数据长度和长度信息。该如何将所有这些信息存储在我们的PL/SQL程序中呢?

要回答这个问题,我们需要考虑将如何使用这些数据。事实证明,我们会将其用在很多方面。例如:

1、使用列名来建立查询的选择列表;

2、为了将表的输出以可读的方式显示,需要一个列头来显示列名称的列标题,这些列名必须用柱状形式间隔开。所以,我需要每一列的列名和数据长度;

3、为在动态游标中获取数据,需要调用DEFINE_COLUMN构建游标列,为此,我们需要列数据类型和长度;

4、为使用COLUMN_VALUE从列中提取数据,我们需要知道每一列的数据类型和列的数量。

5、为了显示数据,我们必须构造一个包含所有数据的字符串(使用TO_CHAR来转换数字和日期)。而且,我们必须使数据和列名相匹配,像标题行一样。

在整个程序中,我们需要多次处理列信息,不过我们并不想要从数字字典中重复读取。因此,当我们在ALL_TAB_COLUMNS视图查询列数据时,会将该数据存储在3个PL/SQL集合中,如下表所示:

集合

描述

colname

每列的名称

coltype

每一列的数据类型(字符串描述数据类型)

collength

显示列数据所需的字符数

如果emp表的第三列为SAL,那么colname(3)=’SAL’, coltype(3) = ‘NUMBER’, collength(3)=7等。

列名和数据类型信息直接保存在数字字典里。

所有的逻辑放在一个游标性的FOR循环中,在这个循环中遍历了表的所有列(在ALL_COLUMNS所定义的),在循环中填充PL/SQL集合,详见下面的例子:

FOR col_rec IN col_cur (owner_nm, table_nm)

LOOP

–为查询构建select列表

col_list:= col_list || ‘, ’ || col_rec.column_name;

–将数据类型和长度保存到DEFINE_COLUMN

col_count :=col_count + 1;

colname(col_count):= col_rec.column_name;

coltype(col_count):= col_rec.data_type;

–构建列标题行

col_header :=col_header || ‘ ’ || RPAD (col_rec.column_name, v_length);

END LOOP;

这个循环结束后,我们就构建了select列表,并且调用DBMS_SQL.DEFINE_COLUMN和DBMS_SQL.COLLUMN_VALUE所需的列信息填充了PL/SQL集合,并创建了列标题行。

 

定义游标结构

接下来是解析查询,然后在动态游标对象中构建各种列。

解析阶段只用将这些提炼出来的组件,尤其是刚刚构造出来的列表(col_list变量),拼成一个SQL语句而已:

DBMS_SQL.PARSE

(cur,

‘SELECT ’ || col_list || ‘ FROM ’ || table_in || ‘ ’ || where_clause,

DBMS_SQL.NATIVE);

我们的目的不仅仅是解析,还想执行这个游标。但是,在这之前,必须给游标提供一些结构。

在用DBMS_SQL打开游标时,我们得到的仅仅是一块内存空间的句柄。在解析SQL语句时,就把SQL语句和这块内存关联了起来。但是下一步我们必须定义游标中的列,以便存储所获取的数据。

我们不能在程序中用硬编码调用DBMS_SQL.DEFINE_COLUMN,因为在运行前我们无法得知关于列数量或类型的全部信息。对intab而言,幸运的是,我们已经知道每一列的信息。现在我们需要做的就是对colname集合中定义的每一行进行Dbms_sql.define_column调用。在进行实际编码之前,这里有一些关于Dbms_sql.define_column的提醒。

这个内置程序的标题如下:

procedure define_column(c in integer,position in integer, column in number|date|varchar2);

有关该内置过程,需要记住3件事情。

1、 第二个参数是数字。DBMS_SQL.DEFINE_COLUMN不使用列名称;仅使用列表内列的位置顺序。

2、 第三个参数用于确定列的数据类,是通过传给它的表达式类型确定的。换句话说,不可以向DBMS_SQL.DEFINE_COLUMN传递诸如“varchar2”这样的字符串,而是应该传递定义为varchar2的变量。

3、 定义一个字符串类型列时,还必须指定可从该游标提取的数值的最大长度。

在intab过程的上下文中,集合的行是列列表的第n个位置。数据类型存储在coltype集合中,但在调用DBMS_SQL.DEFINE_COLUMN时必须装换成合适的局部变量。这些均由以下FOR循环进行处理:

FOR col_indIN 1 .. col_count

LOOP

IF is_string (col_ind)

THEN

DBMS_SQL.DEFINE_COLUMN(cur,col_ind, string_value, colleen(col_ind));

ELSIF is_number(col_ind)

THEN

DBMS_SQL.DEFINE_COLUMN(cur,col_ind,date_value);

END IF;

END LOOP;

这个循环完成后,我们就已经为集合中定义的每列都调用了DEFINE_COLUMN。然后我们就可以执行游标并开始提取数据了。像这样:

fdbk := DBMS_SQL.EXECUTE(cur);

其中fdbk是调用EXECUTE后的返回值。

 

提取和显示数据

现在到了最后一步:数据提取和格式化输出

我们使用游标FOR循环来提取动态游标所定义的数据的每一行。如果位于第一行,还要显示一个标题(这样一来,如果查询没有返回任何数据,就不显示标题)。

对于所提取到的每一行,我们将建立行并进行显示。

LOOP

fdbk:= DBMS_SQL.FETCH_ROWS(cur);

EXIT WHEN fdbk = 0;

IF DBMS_SQL.LAST_ROW_COUNT = 1

THEN

–显示标题信息

。。。

END IF;

–从列信息中构建行文本

。。。

DBMS_OUTPUT.PUT_LINE(col_line);

END LOOP;

行建立程序实际上是一个数字的FOR循环,其中调用了DBMS_SQL.COLUMN_VALUE。分别为表内每列调用该内置函数(存储在集合内的信息)。我们使用 my is_*函数来确定列数据类型,并因此确定适当的变量来接收列值。

在将值转换为字符串后(对日期和数字非常必要),将适当数量的空格填充在右侧(储存在collon集合内),这样就与列标题对齐了:

col_line:= NULL;

FOR col_ind IN 1 .. col_count

LOOP

IF is_string (col_ind)

THEN

DBMS_SQL.COLUMN_VALUE(cur,col_ind, string_value);

ELS IF is_number(col_ind)

THEN

DBMS_SQL.COLUMN_VALUE(cur,col_ind,number_value);

ELS IF is_date(dol_ind)

THEN

DBMS_SQL.COLUMN_VALUE(cur,col_ind,date_value);

String_value:= TO_CHAR(date_value, date_format_in);

END IF;

–在列标题下间隔行的值

col_line :=col_line || ‘ ’ || RPAD (NVL(string_value, ‘ ’), collength(col_ind));

END LOOP;

现在我们有了一个非常通用的程序,可以用来在PL/SQL程序内显示数据库表的内容。

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐