有一个user表,有uid和uname字段。有一个customer表,有cid,cname,cpass字段,现在需要写一个存储过程:
要把select c.cname from customer c查出来的人名插入加到user表,uid为可以用序列。存储过程怎么写?
实际项目中select c.cname from customer c是一个复杂的查询,只投影名字字段,如查出张三、李四,在向user添加两条记录:
1 张三
2 李四
要把select c.cname from customer c查出来的人名插入加到user表,uid为可以用序列。存储过程怎么写?
实际项目中select c.cname from customer c是一个复杂的查询,只投影名字字段,如查出张三、李四,在向user添加两条记录:
1 张三
2 李四
insert into user
select 序列,c.cname from customer c;
commit;
end
insert into user2 (ID,name) values
(select copyt.nextval,c.cus_name from customer c);
insert into user2 (uid,uname) values
(select copyt.nextval,c.cname from customer c);copyt是序列
代码参考:DECLARE
vUID user2.uid%TYPE;
vUNme user2.uname%TYPE;
BEGIN
SELECT copyt.nextval,c.cname INTO vUID, vUName
FROM customer;
INSERT INTO user2(uid, uname) VALUES(vUID, vUName);
END;
FROM customer;可是我的customer表里有多条数据,SELECT INTO要求select from的查询结果只有一行
或者插入sql这样写:
begin
insert into user(id,name,age)
select 序列,c.cname,0 from customer c;
commit;
end
create sequence UISSEQ
minvalue 0
maxvalue 999999999999
start with 1
increment by 1
cache 100;-- 这种写法游标的定义有点局限,不能带参数条件
create or replace procedure testAdd
as
sSql varchar2(2000);
iNextValue number;
cursor c1 is select distinct c.cname from customer c;
begin
for c2 in c1 loop
sSql := 'begin select UISSEQ.NEXTVAL into :1 from dual; end;';
execute immediate sSql using out iNextValue;
sSql := 'begin insert into customer(uId,uname) values (:1,:2); end;';
execute immediate sSql using in iNextValue,in c2.cname
end loop;
commit;
end;--这种写法游标的定义可以带参数条件
create or replace procedure testAdd
as
sSql varchar2(2000);
sSql1 varchar2(2000);
iNextValue number;
vName varchar2(32);
type cur_type is ref cursor;
c1 cur_type
begin
sSql1 := 'select distinct c.cname from customer c ';
open c1 for sSql1
loop
fetch c1 into vName;
exit when c1%notfound;
sSql := 'begin select UISSEQ.NEXTVAL into :1 from dual; end;';
execute immediate sSql using out iNextValue;
sSql := 'begin insert into customer(uId,uname) values(:1,:2); end;';
execute immediate sSql using in iNextValue,in vName;
end loop;
close c1;
commit;
end;
create sequence UISSEQ
minvalue 0
maxvalue 999999999999
start with 1
increment by 1
cache 100;-- 这种写法游标的定义有点局限,不能带参数条件
create or replace procedure testAdd
as
sSql varchar2(2000);
iNextValue number;
cursor c1 is select distinct c.cname from customer c;
begin
for c2 in c1 loop
sSql := 'begin select UISSEQ.NEXTVAL into :1 from dual; end;';
execute immediate sSql using out iNextValue;
sSql := 'begin insert into user(uId,uname) values (:1,:2); end;';
execute immediate sSql using in iNextValue,in c2.cname
end loop;
commit;
end;--这种写法游标的定义可以带参数条件
create or replace procedure testAdd
as
sSql varchar2(2000);
sSql1 varchar2(2000);
iNextValue number;
vName varchar2(32);
type cur_type is ref cursor;
c1 cur_type
begin
sSql1 := 'select distinct c.cname from customer c ';
open c1 for sSql1
loop
fetch c1 into vName;
exit when c1%notfound;
sSql := 'begin select UISSEQ.NEXTVAL into :1 from dual; end;';
execute immediate sSql using out iNextValue;
sSql := 'begin insert into user(uId,uname) values(:1,:2); end;';
execute immediate sSql using in iNextValue,in vName;
end loop;
close c1;
commit;
end;应这样。。晕。。写错表名了。
insert into user(id,name,age)
select 序列,c.cname,0 from customer c;
commit;
end