求教：存储过程时的问题

shukechen

1、怎么样在数据库用户A中建一个过程C,调用数据库用户B中的过程D？
2、在数据库用户A存储过程中怎么样查找、调用数据库用户B中表C的数据？
是不是只能用动态SQL来解决？

nyfor

存储过程前加上schema B
如：A中过程C
c: call d;  ==>; call b.d;

shukechen

谢谢回复
但是有一点不明白
比如

create or replace procedure C (
--定义外部变量
)
as
--定义内部变量
schema b;
c : call d;--是在这里吗？
begin
d;--调用B中的d
end;

是这样吗？

nyfor

不是，是这样：
create or replace procedure C (
--定义外部变量
)
as
--定义内部变量
n number;
begin
d;--此为调用当前schema下的d
b.d;--此为调用schema B 下的d
select count(1) into n from b.c; -- 访问B中表c的数据方法一样
end;

nyfor

对了，在A的存储过程中要使用B的表C或是访问B中的过程，你需要显示授权给A,否则编译不通过！

连接到B后执行：
grant execute on d to a;
grant select on c to a;

shukechen

谢谢！！我用你说的赋权限可以解决这个问题。没有用到你先说的schema B    和  c: call d; ==>; call b.d;

但是我不知道

存储过程前加上schema B    －－  对下面的格式有什么作用
如：A中过程C
c: call d; ==>; call b.d;        －－ 对下面的格式有什么作用

create  or replace procedure C (
                               --定义外部变量
)
as
  --定义内部变量
  schema b;
  c : call d;--是在这里吗？
begin
    d;--调用B中的d
end;

nyfor

我先前写的schema B 和 c: call d; ==>; call b.d;
让你在存储过程书写上误解了，不好意思。

shukechen

当前用户的权限
grant dba,connect,resource to A;

在当前用户下运行下面这个块
set serveroutput on
declare
  v_sql varchar2(1000);
  v_sqlcode number(20);
  v_sqlerrm varchar2(300);
begin
  v_sql:='CREATE table b ( id number)';
  exedmlsql(v_khmc,v_sqlcode,v_sqlerrm);--动态执行sql语句，当前用户的存储过程
  dbms_output.put_line(v_sql);  
  dbms_output.put_line(v_sqlcode);
  dbms_output.put_line(v_sqlerrm);
end;

为什么会报权限不足？
ORA-01031: 权限不足
ORA-06512: 在"A.EXEDMLSQL", line 29
ORA-06512: 在line 10

nyfor

注意：定义者权限存储过程中需要的权限不能通过角色(Role)来获得，你必须显式授权给A，而调用者权限存储过程中则可以。


这就是存储过程的权限问题，存储过程可以以定义者权限运行，也可以以调用者权限运行。
所谓定义者权限，就是该存储过程在编译时就确定其权限，不可动态改变，由于role可以在seesion中动态的set，所以orale就规定存储过程以定义者权限运行时不启用role。

而调用者权限则不同，他在编译时确定其有权限，而在运行时又动态检查其权限，这时启用role，但注意如果这个过程被一个定义者权限过程调用，那么由于在定义者权限入口就set role none，所以这时也没有role启用，调用者权限过程也就看不到任何role了。

shukechen

CREATE OR REPLACE Procedure EXEDMLSQL  --执行SQL语句
   ( v_sqlstr     in    varchar2,
     v_sqlcode    out   number,         --错误代码
     v_sqlerrm    out   varchar2        --错误信息
   )   

as
  v_Cursor     integer;
  v_RetNum     integer;

begin
  
  v_Cursor :=DBMS_SQL.Open_Cursor;
  DBMS_SQL.Parse(v_Cursor,v_sqlstr,DBMS_SQL.NATIVE);
  
  v_RetNum:=DBMS_SQL.Execute_and_fetch(v_Cursor);

  DBMS_SQL.CLOSE_CURSOR(V_CURSOR);


  v_sqlcode := 0;
  v_sqlerrm := '';

  EXCEPTION
    WHEN OTHERS THEN
      begin
        v_sqlcode := SQLCODE;
        v_sqlerrm := SQLERRM;
        RAISE;
      end;
end;

这是我的EXEDMLSQL过程，它是默认的定义者权限，我怎么样把它定义为调用者权限，是在定义过程时定义还是在调用这个过程的块中定义，是不是有一个AUTHID，我不知怎么用？