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程序内显示数据库表的内容。