使用动态SQL克隆数据库对象

上一篇 / 下一篇 2012-03-31 09:43:21 / 个人分类：数据库

如果你需要复制一个表并在（另一个用户名下的）另一个方案（schema）制作它的一个副本，那么你可以使用 SQL*Plus 的 COPY 命令，或者使用简单的语句“create table foo as select * from other.foo;”，只要你在另一个用户的表上有足够的 select 权限。

xE#|]!Fnm.N l0　　然而，如果你需要对任何其它数据库对象做同样的事情，比如包、过程、函数或视图，就没有这么简单的命令了。你需要手工找出其代码并在新实例中运行它。如果能够简单“克隆对象”并让另一个用户的对象出现在自己的实例中，那将是一件非常值得高兴的事。对于为开发过程创建测试方案来说，这一点非常有用。在这个例子中，我将创建一个包，使用该包可以对大多数对象进行克隆（具有一些限制）。

f-F'``y0　　要处理的主要问题是获得源代码。对象所有者通过视图USER_SOURCE 可以访问源代码。而对于其它用户，如果对象被授予了EXECUTE 权限给一个用户，那么这个用户就只能看到通过视图ALL_SOURCE 选出的源代码。我们可以通过一个过程来封装“give me the source for your object（给我你的对象的源代码）”请求：51Testing软件测试网NW m jG/T6c_

t/ml'`%[+k5e0

create or replace procedure get_source
(
p_type          varchar2,
p_name          varchar2,
p_cursor out    sys_refcursor
)
as
begin
open p_cursor for
select text from user_source
where type = upper(p_type) and name = p_name
order by line;
end get_source;
/
show errors;

　　注意，我没有用“UPPER(name)”。这就意味着你必须匹配这个存储过程的字母大小写。Java 存储过程使用很多大小写混合的名字。

　　如果这个过程是由对象所有者所有的，那么那个对象的源代码就可以通过一个 REF CURSOR 变量导出。如果这个过程被授予了其他用户 EXECUTE 权限，那么这个用户将能够调用这个过程并查看任何数据库对象的源代码——即使是那些没有授权给他们的对象和那些在 ALL_SOURCE 中不给出的对象，比如 TYPE 声明。为了说明这种方法可行，请尝试在 SQL*Plus 中输入以下代码：

~t!jPkw I0

SQL> connect scott/tiger
SQL> create function foo return varchar2 as begin return 'hello world'; end;
SQL> /
SQL> @get_source.sql
SQL> variable c refcursor;
SQL> exec get_source('FUNCTION','FOO',:c);
SQL> print c

k3`^N*S:N(hV*Biy-O0　　有了从对象所有者手中得到的源代码，调用者就可以在创建自己的方案中创建对象了。我们需要动态SQL来从文本字符串构建对象。另外一个需要处理的问题是一些数据库对象的源代码的长度可能会超过32767个字符，即超过 VARCHAR2 字符串的最大长度限制。这样就不能使用简单的 VARCHAR2 字符串来保存 SQL。在Oracle中有一个很少使用的变量DBMS_SQL.PARSE，可以使用它将源代码存储为一个由 VARCHAR2 字符行所组成的表中。这样的表可以用来存储超过32767长度限制的 SQL。（在实际的应用中，你可能还需要将任何大于256个字符的代码行包装起来，因为USER_SOURCE 最多只能存储4000行字符）。下面将其实现为一个带有命令行参数的 SQL*Plus 脚本的代码：

)|;{p:|ZC7G+T3e6a0

declare
ipls_integer := 1;
l_source dbms_sql.varchar2s;
l_line varchar2(256);
l_cursorsys_refcursor;
c pls_integer;
r pls_integer;
begin
&1..get_source('&2','&3',l_cursor);
l_source(i) := 'create or replace';
loop
fetch l_cursor into l_line;
exit when l_cursor%notfound;
i := i + 1;
l_source(i) := l_line.text;
end loop;
close l_cursor;
if i = 1 then
raise_application_error(-20000,'object does not exist');
end if;
c := dbms_sql.open_cursor;
dbms_sql.parse(c,l_source,1,l_source.count,true,dbms_sql.native);
dbms_sql.close_cursor(c);
end;
/

举个例子，假设一个方案需要克隆 SCOTT 的方案中的“FOO”的函数。SCOTT 将拥有 CLONER 的一个副本并将 EXECUTE 权限授予允许克隆 SCOTT 的对象的用户。其它的用户可以发出以下 SQL*Plus 命令：

d@-M0_c7je{6S0

SQL> connect ANOTHER USER
SQL> @clone SCOTT FUNCTION FOO

)kdM1oJ g1]^"tf0

F:E(KvB~ee&bD0　　这种做法可行，但是依然需要 SQL*Plus 会话和脚本。我想将所有东西都放在 SQL 中，以使得任何应用程序都可以执行这一功能。为了实现这一想法，我们需要将前面的 SQL*Plus 脚本包装成另外一个动态 SQL 语句，在这个语句中我们可以加上所有者的名称并将所有者和类型参数组合。可以用以下过程来实现：

4li({(Y*X-C0

create or replace procedure clone_obj
(
p_owner         varchar2,
p_type          varchar2,
p_name          varchar2
)
authidcurrent_user
is
lf          char := chr(10);
begin
execute immediate
'declare' || lf
|| '    ipls_integer := 1;' || lf
|| '    l_source dbms_sql.varchar2s;' || lf
|| '    l_line varchar2(4000);' || lf
|| '    l_cursorsys_refcursor;' || lf
|| '    c pls_integer;' || lf
|| '    r pls_integer;' || lf
|| 'begin' || lf
|| '    '||p_owner||'.get_source(:1,:2,l_cursor);' || lf
|| '    l_source(i) := ''create or replace'';' || lf
|| '    loop' || lf
|| '        fetch l_cursor into l_line;' || lf
|| '        exit when l_cursor%notfound;' || lf
|| '        i := i + 1;' || lf
|| '        l_source(i) := l_line;' || lf
|| '    end loop;' || lf
|| '    close l_cursor;' || lf
|| '    if i = 1 then' || lf
|| '        raise_application_error(-20000,'
|| '''object does not exist'');' || lf
|| '    end if;' || lf
|| '    c := dbms_sql.open_cursor;' || lf
|| '    dbms_sql.parse(c,l_source,1,l_source.count,'
|| 'true,dbms_sql.native);' || lf
|| '    dbms_sql.close_cursor(c);' || lf
|| 'end;' || lf
using p_type,p_name;
end clone_obj;
show errors;

1[*uAF9o0

　　注意，使过程具有足够的权限来创建数据库对象，我必须添加AUTHID CURRENT_USER。现在你可以用任何能够调用 Oracle 存储过程的产品来调用这个过程。下面这个例子与前面的例子相同，只不过这个例子是写在 SQL*Plus 中的：

SQL> @clone_obj
SQL> exec clone_obj('SCOTT','FUNCTION','FOO');

　　在这里会有一些安全问题，但是不多。只有被授予对 GET_SOURCE 有 EXECUTE 权限的用户才能读取他们常规情况下无法看到的对象的源代码。在理想情况下，你可以创建一个只包含“GET_SOURCE”和一组模板对象的用户。51Testing软件测试网J{Ru4?7G

　　上面的程序还不完整，但是还是可以作为一个例子来用的。除了需要将4000个字符的源代码包装成256个字符长的目标行之外，可能还需要对其进行扩展以扫描对象的名称并插入一个所有者名称，以使得 DBA 所有者能够将对象从一个用户克隆到其他用户。

收藏举报

TAG:

查看全部评论