SQL> set serveroutput on SQL> declare 2 v_id test.id%type; 3 begin 4 insert into test values(7,'AC米兰') returning id into v_id; 5 dbms_output.put_line(v_id); 6 end; 7 / 7PL/SQL 过程已成功完成。SQL>
INSERT INTO a VALUES (xxx, xxx) RETURNING 列名 INTO 变量;
create or replace procedure getid(v_id out number) as v_sql varchar2(500); begin
v_sql:='insert into test values(8,''tom'') returning id into ' || v_id; execute immediate v_sql; exception when others then null; end; 我现在写成存储过程,但运行不起来,sql语句好像不对
oracle没有自增列,你的自增列肯定是seq+trigger的方式实现的。既然这样,不如drop trigger,并修改你的存储过程create or replace procedure getid(var1 in number,var2 in varchar2,v_id out number) as begin select seq_**.nextVal into v_id from dual; insert into test(no1,name,id) values(var1,var2,v_id) ; --v_id的顺序由表中所在位置确定,不过最好按字段一一对应 exception when others then v_id=null; end; / 这种方式更合理一些。个人意见,仅供参考。
create or replace procedure getid(v_id out number) as v_sql varchar2(500); begin insert into test1 values(8,'tom') returning id into v_id; dbms_output.put_line(v_id); end;
恩,大大这种方法确实是更合理,而且是可行的,非常感谢~!! 另外,关于我的写法,我之所以用动态sql语句,是因为sql语句我是准备从程序里传过来的,但单单动态的insert语句还行,一加上returning XX into XX 这种就不行了,是不是我动态语句写的有问题呢?
这个我倒是没有实验过,但是根据1楼DBA的写法: begin insert into test values(7,'AC米兰') returning id into v_id; dbms_output.put_line(v_id); end; 这是一句sql,而你的动态sql,则变成了 v_sql:='insert into test values(8,''tom'') returning id into ' || v_id; 此时v_id应该是个未知数。这句相当于: v_sql:='insert into test values(8,''tom'') returning id into null??unknow string?' ; excute immediate v_sql;出错应该不奇怪。 你可以试试这样: v_sql:='insert into test values(8,''tom'') returning id into :1'; execute immediate v_sql using v_id; 看看行不?再次声明,我没试过噢
刚试过拉,用动态sql不行,等待达人解决。。
create or replace procedure getid(v_id out number) as v_sql varchar2(500); begin v_sql:='insert into test values(7,''jack'') returning id into :1' ; execute immediate v_sql returning into v_id; end;改成这样,可以了,感谢大大提醒~~~
SQL> declare
2 v_id test.id%type;
3 begin
4 insert into test values(7,'AC米兰') returning id into v_id;
5 dbms_output.put_line(v_id);
6 end;
7 /
7PL/SQL 过程已成功完成。SQL>
RETURNING 列名 INTO 变量;
as
v_sql varchar2(500); begin
v_sql:='insert into test values(8,''tom'') returning id into ' || v_id;
execute immediate v_sql;
exception
when others then
null;
end;
我现在写成存储过程,但运行不起来,sql语句好像不对
as
begin
select seq_**.nextVal into v_id from dual;
insert into test(no1,name,id) values(var1,var2,v_id) ; --v_id的顺序由表中所在位置确定,不过最好按字段一一对应
exception
when others then
v_id=null;
end;
/
这种方式更合理一些。个人意见,仅供参考。
as
v_sql varchar2(500);
begin
insert into test1 values(8,'tom') returning id into v_id;
dbms_output.put_line(v_id);
end;
另外,关于我的写法,我之所以用动态sql语句,是因为sql语句我是准备从程序里传过来的,但单单动态的insert语句还行,一加上returning XX into XX 这种就不行了,是不是我动态语句写的有问题呢?
这个我倒是没有实验过,但是根据1楼DBA的写法:
begin
insert into test values(7,'AC米兰') returning id into v_id;
dbms_output.put_line(v_id);
end; 这是一句sql,而你的动态sql,则变成了
v_sql:='insert into test values(8,''tom'') returning id into ' || v_id;
此时v_id应该是个未知数。这句相当于:
v_sql:='insert into test values(8,''tom'') returning id into null??unknow string?' ;
excute immediate v_sql;出错应该不奇怪。
你可以试试这样:
v_sql:='insert into test values(8,''tom'') returning id into :1';
execute immediate v_sql using v_id;
看看行不?再次声明,我没试过噢
as
v_sql varchar2(500); begin v_sql:='insert into test values(7,''jack'') returning id into :1' ;
execute immediate v_sql
returning into v_id;
end;改成这样,可以了,感谢大大提醒~~~