编写了一个存储过程 add_rows,然后在命令窗口中输入:exce add_rows;
执行后,发现执行了2次!
SQL> PL/SQL procedure successfully completedPL/SQL procedure successfully completedSQL> 这是为什么?
执行后,发现执行了2次!
SQL> PL/SQL procedure successfully completedPL/SQL procedure successfully completedSQL> 这是为什么?
code=SQL]
create or replace procedure add_rows
(
num in number, --数量
table_name in varchar2 --表名
)
asstudent_id students.st_id%type; --学生学号
student_name students.st_name%type; --学生姓名
student_sex students.st_sex%type; --学生性别
student_ph students.st_phone%type; --学生手机号码
cou_id courses.course_id%type; --课程ID
cou_name courses.course_name%type; --课程名称
cou_type courses.course_type%type; --课程类型
cou_length courses.course_length%type;--课时
i number;
--student_phone students.st_phone%type;
begin
case
when table_name = 'students' then
select max(st_id) into student_id from students;
select max(st_phone) into student_ph from students;
if student_id is null then
student_id:=10000;
end if;
if student_ph is null then
student_ph:=13000000000;
end if;
i:=1;
while i<= num loop
begin
i:=i+1;
student_id := student_id + 1;
student_sex := dbms_random.value(0,1);
student_name :='student'||student_id;
student_ph:=student_ph+1;
insert into students(st_id,st_name,st_sex,st_phone) values (student_id,student_name,student_sex,student_ph);
end;
end loop;
dbms_output.put_line(table_name||' add rows success!');
commit; when table_name = 'courses' then
select max(course_id) into cou_id from courses;
i:=1;
if cou_id is null then
cou_id := 10000;
end if;
while i<= num loop
begin
i:=i+1;
cou_id:=cou_id+1;
cou_name :='course'||cou_id;
cou_type :=dbms_random.value(1,10);
cou_length:=20;
insert into courses(course_id,course_name,course_type,course_length) values(cou_id,cou_name,cou_type,cou_length);
end;
end loop;
dbms_output.put_line(table_name||' add rows success!');
commit;
else
dbms_output.put_line(table_name || ' is not exsit'); end case;
exception
when others then
dbms_output.put_line(table_name || ' is not exsit');
rollback;
end;
[/code]
然后, 执行命令
SQL> exec add_rows(1,'students');
PL/SQL procedure successfully completed没有出现你所说的情况
数据也只有一条。
SQL> select * from students;
ST_ID ST_NAME ST_SEX ST_PHONE
--------------------------------------- -------------------- --------------------------------------- ---------------------------------------
10001 student10001 1 13000000001
所以, 觉得跟你写的过程没有关系, 可能是其他一些设置的问题。。
哦,谢谢你的回答,可能是我的PL/SQL工具设置问题吧,我再试试!!
在命令窗口中,输入:
EXEC add_rows(3,'students');
/
执行后,就执行了2遍,这里的/符号不是结束符号吗?为什么会多执行一遍?