存储过程获得动态视图和返回值问题。问题比较多

Question

select intid from a
1
3
5
2
select intid from c
1
4
6
7小弟先想通过function来动态create view
create or replace function test_ab(v_intid in varchar2) return varchar2 is
  out_intid varchar(10);
  v_sql varchar2(1000);
begin
  dbms_output.put_line('v_intid= '||v_intid);
  v_sql:='create or replace view ab as select a.intid aa,c.intid bb from a,c where a.intid=c.intid and a.intid='''||v_intid||'''';  
  execute immediate  v_sql;
  select aa+bb into out_intid from ab;
  dbms_output.put_line('out_intid= '||out_intid);
  return(out_intid);
end test_ab;调试通过后执行
select test_ab('1') from dual
进行测试，报ORA-14552：cannot perform a DDL,commit or rollback inside a query or DML
这是问题一：为什么会报这个错误呢？是说function方法里 不能直接执行create or replace view创建视图吗？然后小弟就想function不行，按我改procedure把。create or replace procedure test_ab(v_intid in out varchar2) is
v_sql varchar2(1000);
begin
  dbms_output.put_line('v_intid= '||v_intid);
  v_sql:='create or replace view ab as select a.intid aa,c.intid bb from a,c where a.intid=c.intid and a.intid='''||v_intid||'''';
  execute immediate  v_sql;
  select aa+bb into v_intid from ab;
  dbms_output.put_line('v_intid= '||v_intid);
end test_ab;这个代码编译通过了。准备进行测试
就在oracle developer的命令窗口进行测试，在网上找了调用存储过程返回值的方法
declare @returnvalue int;
exec @returnvalue=test_ab('1');
print @returnvalue;
但上述代码怎么也执行不了。
当在命令窗口敲入declare @returnvalue int;回车时，
就出现
 declare @returnvalue int
  2  /
 
declare @returnvalue int
 
ORA-06550: line 2, column 9:
PLS-00103: Encountered the symbol "@" when expecting one of the following:   begin function package pragma procedure subtype type use
   <an identifier> <a double-quoted delimited-identifier> form
   current cursor问题二：就是上述用于测试存过过程的方法是否可行？如果可行，那我那里操作错了，望大神指正。
然后在存储过程中create view可行吗？是否还会报ORA-14552：cannot perform a DDL,commit or rollback inside a query or DML错误。还弱弱的问一下，像function或者procedure可以在developer调试窗口调试吗？
平常自己会那没有命名的存储快去调试窗口调试。如果是create or replace procedure test_ab可以到调试吗？
自己初接触存储过程，感觉调试存储过程或者function挺麻烦？各位大神平常都是怎么调试的呢？小弟在这里向各位大神取取经。谢谢了

Answer 1

>进行测试，报ORA-14552：cannot perform a DDL,commit or rollback inside a query or DML
这个错误和函数还是过程无关,原因是oracle不允许在查询语句中调用ddl。
所以只需要修改一下你调用函数的方式。
另外，view ab刚开始时并不存在，所以要修改一下select ... from ab的调用，改成execute immediate的动态调用方式，否则函数编译会报错。create or replace function test_ab(v_intid in varchar2) return varchar2 is
  out_intid varchar(10);
  v_sql varchar2(1000);
begin
  dbms_output.put_line('v_intid= '||v_intid);
  v_sql:='create or replace view ab as select a.intid aa,c.intid bb from a,c where a.intid=c.intid and a.intid='''||v_intid||'''';  
  execute immediate  v_sql;
  execute immediate 'select aa+bb from ab' into out_intid;
  dbms_output.put_line('out_intid= '||out_intid);
  return(out_intid);
end test_ab;declare
  l_ret varchar2(10);
begin
  l_ret := test_ab('1');
  dbms_output.put_line(l_ret);
end;

Answer 2

create or replace function test_ab(v_intid in varchar2) return varchar2 is
  out_intid varchar(10);
  v_sql varchar2(1000);
begin
  dbms_output.put_line('v_intid= '||v_intid);
  v_sql:='create or replace view ab as select a.intid aa,c.intid bb from a,c where a.intid=c.intid and a.intid='''||v_intid||'''';  
  execute immediate  v_sql;
  execute immediate 'select aa+bb from ab' into out_intid;
  dbms_output.put_line('out_intid= '||out_intid);
  return(out_intid);
end test_ab;declare
  l_ret varchar2(10);
begin
  l_ret := test_ab('1');
  dbms_output.put_line(l_ret);
end;

Answer 3

另外，过程是没有返回值的，如果test_ab是过程，调用方式就是create or replace procedure test_ab(v_intid in varchar2) is
  out_intid varchar(10);
  v_sql varchar2(1000);
begin
  dbms_output.put_line('v_intid= '||v_intid);
  v_sql:='create or replace view ab as select a.intid aa,c.intid bb from a,c where a.intid=c.intid and a.intid='''||v_intid||'''';  
  execute immediate  v_sql;
  execute immediate 'select aa+bb from ab' into out_intid;
  dbms_output.put_line('out_intid= '||out_intid);
--  return(out_intid);
end test_ab;
begin
  test_ab('1');
end;

Answer 4

另外，过程是没有返回值的，如果test_ab是过程，调用方式就是SQL codecreate or replace procedure test_ab(v_intid in varchar2) is
  out_intid varchar(10);
  v_sql varchar2(1000);
begin
  dbms_output.put_line('v_intid= '||v_intid);
  v_sql:='create or replace view ab as select a.intid aa,c.intid bb from a,c where a.intid=c.intid and a.intid='''||v_intid||'''';  
  execute immediate  v_sql;
  execute immediate 'select aa+bb from ab' into out_intid;
  dbms_output.put_line('out_intid= '||out_intid);
--  return(out_intid);
end test_ab;
begin
  test_ab('1');
end;过程参数里面不是有out，in out类型的paramete？这不就是说明参数可以传出吗？那这个out或者in out类型的参数怎么用呢？

Answer 5

对,可以用out类型参数。稍微改一下就好。create or replace procedure test_ab(v_intid in varchar2, out_intid out varchar2) is
  v_sql varchar2(1000);
begin
  dbms_output.put_line('v_intid= '||v_intid);
  v_sql:='create or replace view ab as select a.intid aa,c.intid bb from a,c where a.intid=c.intid and a.intid='''||v_intid||'''';  
  execute immediate  v_sql;
  execute immediate 'select aa+bb from ab' into out_intid;
  dbms_output.put_line('out_intid= '||out_intid);
--  return(out_intid);
end test_ab;declare
  out_intid varchar(10);
begin
  test_ab('1', out_intid);
  dbms_output.put_line(out_intid);
end;