答案AC, 6楼得奖。2楼关于字符串的说法有点钻牛角尖，SQL的VARCHAR2还不能超过4000呢(12C之前)？
A:
这些过载子程序的头部是这样的：
procedure parse(c in integer, statement in varchar2a,
lb in integer, ub in integer,
lfflg in boolean, language_flag in integer);
procedure parse(c in integer, statement in varchar2s,
lb in integer, ub in integer,
lfflg in boolean, language_flag in integer);
varchar2a 和 varchar2s 标识符是关联数组类型。下面是一个例子，它使用varchar2a过载子程序来将一个文件加载入数组并且作为动态DDL执行：
CREATE OR REPLACE PROCEDURE exec_ddl_from_file (
dir_in IN VARCHAR2
, file_in IN VARCHAR2
)
IS
l_fileUTL_FILE.file_type;
l_eof BOOLEAN;
l_lines DBMS_SQL.varchar2a;
PROCEDURE get_next_line (line_out OUT VARCHAR2, eof_out OUT BOOLEAN)
IS
BEGIN
UTL_FILE.get_line (l_file, line_out);
eof_out := FALSE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
line_out := NULL;
eof_out := TRUE;
END get_next_line;
PROCEDURE exec_ddl_statement (lines_in IN DBMS_SQL.varchar2a)
IS
l_curINTEGER;
l_exec PLS_INTEGER;
BEGIN
l_cur := DBMS_SQL.open_cursor;
DBMS_SQL.parse (l_cur
, l_lines
, l_lines.FIRST
, l_lines.LAST
, TRUE
, DBMS_SQL.native
);
l_exec := DBMS_SQL.EXECUTE (l_cur);
DBMS_SQL.close_cursor (l_cur);
END exec_ddl_statement;
BEGIN
l_file := UTL_FILE.fopen (dir_in, file_in, 'R');
LOOP
get_next_line (l_lines (l_lines.COUNT + 1), l_eof);
EXIT WHEN l_eof;
END LOOP;
UTL_FILE.fclose (l_file);
exec_ddl_statement (l_lines);
END exec_ddl_from_file;
B: 错误。你可以用DBMS_SQL.PARSE的两种过载，它们接受一个包含多行文本的集合，组合起来就构成动态执行的语句。另外，在Oracle11g，你可以传递一个CLOB给EXECUTE IMMEDIATE.
C: 正确，这里是一个利用此功能的例子：
CREATE OR REPLACE PROCEDURE exec_ddl_from_file (dir_inIN VARCHAR2
, file_in IN VARCHAR2)
IS
l_file UTL_FILE.file_type;
l_eofBOOLEAN;
l_line VARCHAR2 (32767);
l_ddlCLOB;
PROCEDURE get_next_line (line_out OUT VARCHAR2, eof_out OUT BOOLEAN)
IS
BEGIN
UTL_FILE.get_line (l_file, line_out);
eof_out := FALSE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
line_out := NULL;
eof_out := TRUE;
END get_next_line;
BEGIN
l_file := UTL_FILE.fopen (dir_in, file_in, 'R');
LOOP
get_next_line (l_line, l_eof);
EXIT WHEN l_eof;
l_ddl := l_ddl || CHR (10) || l_line;
END LOOP;
UTL_FILE.fclose (l_file);
EXECUTE IMMEDIATE l_ddl;
END exec_ddl_from_file;
/
D,E: 不幸的是，这样的过程不存在。
|
