create or replace procedure test as
Cursor cur is select student_name,student_id,student_age from student;
name cur%rowtype;
sql_stmt varchar2(4000);
begin
open cur;
loop
fetch cur into name;
if(cur%found)then
begin
sql_stmt:='insert into book(bookname,bookid,publishhouse) values(name.student_name,name.student_id,name.student_age)';
dbms_output.put_line(name.student_name);
dbms_output.put_line(sql_stmt);
commit;
end;
else
exit;
end if;
end LOOP;
close cur;
end test;
怎么才可以让红色的执行呢?
Cursor cur is select student_name,student_id,student_age from student;
name cur%rowtype;
sql_stmt varchar2(4000);
begin
open cur;
loop
fetch cur into name;
if(cur%found)then
begin
sql_stmt:='insert into book(bookname,bookid,publishhouse) values(name.student_name,name.student_id,name.student_age)';
dbms_output.put_line(name.student_name);
dbms_output.put_line(sql_stmt);
commit;
end;
else
exit;
end if;
end LOOP;
close cur;
end test;
怎么才可以让红色的执行呢?
values(name.student_name,name.student_id,name.student_age) 不行么
Cursor cur is select student_name,student_id,student_age from student;
name cur%rowtype;
sql_stmt varchar2(4000);
begin
open cur;
loop
fetch cur into name;
if(cur%found)then
begin
insert into book(bookname,bookid,publishhouse) values(name.student_name,name.student_id,name.student_age;
dbms_output.put_line(name.student_name);
dbms_output.put_line(sql_stmt);
commit;
end;
else
exit;
end if;
end LOOP;
close cur;
end test;
sql_stmt:='insert into book(bookname,bookid,publishhouse) values(''' || name.student_name||''','''||name.student_id||''','''||name.student_age||''')';
execute immediate sql_stmt;
直接进行插入即可。实测数据:CREATE TABLE T132
(
ID VARCHAR2(20),
MyNum NUMBER(4)
);
INSERT INTO T132 VALUES('01', 1);
INSERT INTO T132 VALUES('02', 2);
INSERT INTO T132 VALUES('03', 3);
INSERT INTO T132 VALUES('04', 4);CREATE TABLE T133
(
ID VARCHAR2(20),
MyNum NUMBER(4)
);
存储过程实现将T132中的数据取出,插入到表T133中CREATE OR REPLACE PROCEDURE ProcedureT133
IS
CURSOR cur IS SELECT ID, MyNum FROM T132;
rec T132%ROWTYPE;
BEGIN
FOR r IN cur LOOP
INSERT INTO T133 VALUES(r.id, r.MyNum);
END LOOP;
COMMIT;
END ProcedureT133;
结果: