各位大哥们,我想问一下如何在Oracle存储过程里面替换字符串里面的一部分字符串呀,这是一个表的一部分:
NAME STUDNUMBER GRADE CLASS C_ID YEAR GENDER
-------------------- -------------------- ----- ----- ---- ----- --------------------
李四 2060060622 6 3 14 2020 女
然后我想通过改grade这列的值的同时,顺便把studnumber这列中对应grade的部分也一起改了,我这studnumber对应的是year+grade+class+c_id组成的,然后中间用lpad方法用0补齐,我替换字符串用的是replace这个方法:
update students set studnumber=replace(i_studnumber,lpad(vv_grade,2,'0'),lpad(i_grade,2,'0')) where studnumber =i_studnumber;
那么问题来了,我用这种方法他改的不光是grade这部分的值,他把字符串里跟那个数一样的地方都给我改了,我运行exec updatestudent('李四',3,2060060622),得到的结果是2030030322,这个怎么办呀!
这是我整体的代码,可能比较辣鸡,新手求保护
create or replace procedure updatestudent(i_name students.name%type,
i_grade students.grade%type,
i_studnumber students.studnumber%type) as
v_name students.name%type;
v_grade students.grade%type;
vv_grade students.grade%type;
begin
select count(*) name into v_name from students where name = i_name;
select count(*) grade into v_grade from students where grade = i_studnumber;
select grade into vv_grade from students where studnumber = i_studnumber;
if
v_name = 0 then
update students set name = i_name where studnumber = i_studnumber;
dbms_output.put_line('姓名已更改为:' || i_name);
end if ;
if v_grade = 0 then
update students set grade = i_grade where studnumber = i_studnumber;
update students set studnumber=replace(i_studnumber,lpad(vv_grade,2,'0'),lpad(i_grade,2,'0')) where studnumber =i_studnumber;
dbms_output.put_line('年级已更改为:' || i_grade||'年级');
end if;
end;
update 表名 set 要更新的字段=replace(要更新的字段,'要替换的字符串','替换后字符串') where +条件