进入sqlplus写这个存储过程: create or replace CreateTable(tabName string) as begin EXECUTE IMMEDIATE 'create table '||tabName||' ...'; end CreateTable; /
呵呵 谢谢各位帮助! 我搞定了..存储过程格式大致这样吧 create or replace procedure admin.CreateTable( tabName string ) as // 或者声明SQL字串 begin EXECUTE IMMEDIATE 'create table '||tabName||'(..字段定义...)'; //括号后不要分号 end; //调用 admin.CreateTable(admin.NewTable); 请教下 EXECUTE IMMEDIATE 语句就执行SQL语句吗? 是否有返回信息,或其他的功能?
不知道可不可以执行其它的语句 象我下面例子:(呵呵这说我开始的第二个问题,不犯规吧) create or replace function admin.CreateTable( tabName1 string ) return varchar2 is p_Msg varchar2(140); begin (1)select Msg into p_Msg from admin.Receive where ID=188; //有结果 (2)EXECUTE IMMEDIATE 'select Msg into p_Msg from '||tabName1||' where ID=188 '; (3)EXECUTE IMMEDIATE 'select Msg into p_Msg from admin.Receive where ID=188 '; (4)EXECUTE IMMEDIATE 'select Msg into p_Msg from admin.Receive where ID=188;'; .... return p_Msg; //都是执行出错.. 没关键字或无效字符..没结果返回 end; //调用 p_Msg:=admin.CreateTable(admin.Receive) 不知道这样能行通吗? 谢谢!
create or replace function admin.CreateTable( tabName1 string ) return varchar2 is p_Msg varchar2(140); begin --select Msg into p_Msg from admin.Receive where ID=188; //有结果 EXECUTE IMMEDIATE 'select Msg from '||tabName1||' where ID=188 ' into p_Msg; .... return p_Msg; //都是执行出错.. 没关键字或无效字符..没结果返回 end;
create or replace CreateTable(tabName string)
as
begin
EXECUTE IMMEDIATE 'create table '||tabName||' ...';
end CreateTable;
/
create or replace procedure admin.CreateTable( tabName string )
as
// 或者声明SQL字串
begin
EXECUTE IMMEDIATE 'create table '||tabName||'(..字段定义...)'; //括号后不要分号
end;
//调用 admin.CreateTable(admin.NewTable); 请教下 EXECUTE IMMEDIATE 语句就执行SQL语句吗? 是否有返回信息,或其他的功能?
create or replace function admin.CreateTable( tabName1 string )
return varchar2 is
p_Msg varchar2(140);
begin
(1)select Msg into p_Msg from admin.Receive where ID=188; //有结果
(2)EXECUTE IMMEDIATE 'select Msg into p_Msg from '||tabName1||' where ID=188 ';
(3)EXECUTE IMMEDIATE 'select Msg into p_Msg from admin.Receive where ID=188 ';
(4)EXECUTE IMMEDIATE 'select Msg into p_Msg from admin.Receive where ID=188;';
....
return p_Msg; //都是执行出错.. 没关键字或无效字符..没结果返回
end; //调用 p_Msg:=admin.CreateTable(admin.Receive) 不知道这样能行通吗? 谢谢!
return varchar2 is
p_Msg varchar2(140);
begin
--select Msg into p_Msg from admin.Receive where ID=188; //有结果
EXECUTE IMMEDIATE 'select Msg from '||tabName1||' where ID=188 ' into p_Msg;
....
return p_Msg; //都是执行出错.. 没关键字或无效字符..没结果返回
end;
我的主程序是一个触发器... 所以创建表这个存储过程都是她的一些子模块吧..
创建表存储过程单独运行结果是对的..放进去时 错误是: 触发器中不能有commit语句(呵呵不是真的有,可能是EXECUTE IMMEDIATE 语句吧 ,注释它能运行)..错误出是该存储过程.
呵呵 有没有其他方法呢 不会真的要我该程序的架构吧...求指点指点...