| writing the procedure [message #370111] |
Sat, 16 December 2000 04:14  |
geetha
Messages: 20
Registered: August 2000
|
Junior Member |
|
|
I HAVE A TABLE CALLED EMP IN THAT I HAVE EMPNO FIELD WHERE I HAVE RECORDS SAY 10,000 , LET US ASSUME THAT I HAVE A EMPNO OF 100.,101.,102., LIKE THAT TILL 10000, NOW MY DOUBT IS I WANTED TO DELETE THE FULLSTOP AFTER THE NUMBER ENDS(100.) SO CAN ANYBODY WRITE A PROCEDURE TO DELETE THE FULLSTOP AND UPDATE THE SAME.....
THANKING U IN ADVANCE
B.GEETHA
|
|
|
|
| Re: writing the procedure [message #370114 is a reply to message #370111] |
Sat, 16 December 2000 22:59  |
Robert
Messages: 43
Registered: August 2000
|
Member |
|
|
This package\Procedure should do what you need
Create or replace package Major_change is
Procedure id_change;
end Major_change;
SQL> Create or replace package body Major_change is
2 Procedure id_change is
3 begin
4 Delete from emp
5 where empno < 101;
6 Update emp
7 set empno = empno-100;
8 end;
9 end Major_change;
10 /
Package body created.
SQL> execute Major_change.id_change;
PL/SQL procedure successfully completed.
If empno is varchar2 use "To_number" to convert empno.
|
|
|
|
| Re: writing the procedure [message #370115 is a reply to message #370111] |
Sun, 17 December 2000 22:55 |
mini
Messages: 3
Registered: December 2000
|
Junior Member |
|
|
create or replace procedure del_dot as
cursor c_emp is select empno from employee ;
emp_c c_emp%rowtype;
begin
open c_emp;
loop
fetch c_emp into emp_c;
exit when c_emp%notfound;
UPDATE employee SET empno = decode(c1,emp_c,substr(emp_c,0,instr(emp_c,'.')-1))) where empno = emp_c;
end loop;
close c_emp;
commit;
EXCEPTION
when others then
ERROR MESSAGE;
end;
|
|
|
|
| Re: writing the procedure [message #370116 is a reply to message #370111] |
Sun, 17 December 2000 22:55 |
mini
Messages: 3
Registered: December 2000
|
Junior Member |
|
|
create or replace procedure del_dot as
cursor c_emp is select empno from employee ;
emp_c c_emp%rowtype;
begin
open c_emp;
loop
fetch c_emp into emp_c;
exit when c_emp%notfound;
UPDATE employee SET empno = decode(c1,emp_c,substr(emp_c,0,instr(emp_c,'.')-1))) where empno = emp_c;
end loop;
close c_emp;
commit;
EXCEPTION
when others then
ERROR MESSAGE;
end;
|
|
|
|
| Re: writing the procedure [message #370119 is a reply to message #370111] |
Mon, 18 December 2000 03:54 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Assuming every line in the table is of the format you specify, and that EMPNO is a char or varchar field, these solutions should be quicker than the Pl/Sql cursor loop solutions:
As SQL
UPDATE emp
SET empno = rtrim(empno,'.');
As Pl/SQL
BEGIN
UPDATE emp
SET empno = rtrim(empno,'.');
END;
As A Procedure
Create or Replace Procedure Pr_Update as
BEGIN
UPDATE emp
SET empno = rtrim(empno,'.');
END;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
