| Re: generating sequence/primary key based on dept (a column)type [message #1573] |
Tue, 14 May 2002 06:19  |
Godwin
Messages: 37
Registered: January 2002
|
Member |
|
|
after implenting this code...
procedure seq20
as
p_dept varchar2(30);
p_grade varchar2(30);
cnt number;
seq mytable.seq%type;
begin
DECLARE CURSOR C IS
select dept,pgrade
from godwin
order by dept asc;
BEGIN
FOR CURSOR IN C LOOP
select count(a.dept) into cnt from mytable a,mytable b where a.dept=b.dept ;
if cnt = 0 then
seq :=substr(cursor.dept,1,4);
seq:=seq||(cnt+1);
insert into mytable values(Cursor.dept,Cursor.pgrade,seq);
--end if;
--select count(a.dept) into cnt from mytable a,mytable b where a.dept=b.dept ;
elsif cnt >0 then
seq:=substr(cursor.dept,1,4);
seq:=seq||(cnt+1);
insert into mytable values(Cursor.dept,Cursor.pgrade,seq);
end if;
END LOOP;
commit;
END;
end;
this is the result:
DEPT GRADE SEQ
------------------------------ ------------------------------ -------
GEOGRAPHY LECTURER GEOG2
GEOGRAPHY LECTURER GEOG5
GEOGRAPHY LECTURER GEOG10
GEOGRAPHY SENIOR LECTURER GEOG17
GEOGRAPHY LECTURER GEOG26
GEOGRAPHY LECTURER GEOG37
GEOGRAPHY SENIOR LECTURER GEOG50
GEOGRAPHY SENIOR LECTURER GEOG65
HISTORY LECTURER HIST82
HISTORY SENIOR LECTURER HIST83
HISTORY SENIOR LECTURER HIST86
DEPT GRADE SEQ
------------------------------ ------------------------------ -------
HISTORY LECTURER HIST91
HISTORY LECTURER HIST98
HISTORY SENIOR LECTURER HIST107
GEOGRAPHY LECTURER GEOG1
Please come out with your suggestions.
thanks.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
