用plsql写存储过程的时候,遇到的几个错误,写的简单点,
1、---------------------
问题:
选项缺失或无效
原因:
我的sql开头 create or replace set_salary(ida int, sal int) as
少了 procedure
正确的应该是:
create or replace procedure set_salary(ida int, sal int) as
这个问题都是少了 procedure 造成的
2、---------------------
报错问题:实际返回的行数超出请求的行数
-- Created on 2019/8/6 by XIAOC
declare
-- Local variables here
i int;
stu_id student.id%type;
begin
-- Test statements here
for i in 1 .. 10 loop
dbms_output.put_line('当前i: ' || i);
select student.id into stu_id from student where student.id = i;
if stu_id is null then
insert into student (id) values (i);
commit;
end if;
end loop;
end;
报错的是这一行:select student.id into stu_id from student where student.id = i;
我的原因:student 表里面的 ID 类型是 varchar,但在这里条件查询里定义的 i 的类型是 int
解决:可以将 i 用 to_char转一下,具体写法
select student.id into stu_id from student where student.id = TO_CHAR(i, '99');
其中里面为什么是 ‘99’ ,以及各种类型互转, 可参考 -->该链接
3、-------------------
记上一个问题之后,select 不到数据,会报错,目前的解决方法,先 count 一下有没有重复的,如果为0, 就 insert,如果不为0,就跳过
代码如下:
-- Created on 2019/8/6 by XIAOC
declare
-- Local variables here
i int;
s_count int;
stu_id student.id%type;
stu_name student.name%type;
begin
-- Test statements here
for i in 1 .. 10 loop
dbms_output.put_line('当前i: ' || i);
select count(*) into s_count from student where student.id = i;
if s_count = 0 then
insert into student (id) values (i);
commit;
select student.id, student.name
into stu_id, stu_name
from student
where student.id = i;
dbms_output.put_line('当前数据: ID-->' || stu_id || '名字:' || stu_name);
else
select student.id, student.name
into stu_id, stu_name
from student
where student.id = i;
dbms_output.put_line('当前数据: ID-->' || stu_id || '名字:' || stu_name ||
'已存在, count:' || s_count);
end if;
end loop;
exception
when NO_DATA_FOUND then
dbms_output.put_line('当前i没数据');
end;