| creating a sequence/primary k. based on select dept type [message #1565] |
Mon, 13 May 2002 10:24  |
Godwin
Messages: 37
Registered: January 2002
|
Member |
|
|
Please help me with this problem given to me by my boss.
''I need to create sequence/primary key which will pick the first 4 characters of a column called dept and count the number of records in that same dept to generate the p.k. the sequence must restart at every new dept. eg.
select dept,grade from staffrecord
insert into mytable
values (dept,grade,seq)
Thus the records in mytable should look like
Zoology Senior Clerk ZOOL001
Zoology Junior Clerk ZOOL002
Mathematics Lecturer MATH001
Mathematics T.A MATH002
............ ..... ......
in that order.
Will plsql rather be okay?
Thanks
|
|
|
|
| Re: creating a sequence/primary k. based on select dept type [message #1567 is a reply to message #1565] |
Mon, 13 May 2002 11:19  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
here you go. I have created a stored procedure
**********************************************
SQL> desc mytable;
Name Null? Type
----------------------------------------------------- -------- --------------------
DEPT VARCHAR2(20)
GRADE VARCHAR2(20)
SEQ VARCHAR2(50)
SQL> get seq
1 create or replace procedure seq
2 (P_dept in varchar2,
3 p_grade in varchar2)
4 as
5 cnt number;
6 seq mytable.seq%type;
7 begin
8 select count(dept) into cnt from mytable where dept=p_dept;
9 if cnt = 0 then
10 seq:=substr(p_dept,1,4)||1;
11 insert into mytable values(p_dept,p_grade,seq);
12 end if;
13 if cnt > 0 then
14 seq:=substr(p_dept,1,4);
15 seq:=seq||(cnt+1);
16 insert into mytable values (p_dept,p_grade,seq);
17 end if;
18 commit;
19* end;
SQL> /
Procedure created.
SQL> exec seq('Zoology','Clerk');
PL/SQL procedure successfully completed.
SQL> select * from mytable;
DEPT GRADE SEQ
-------------------- -------------------- ---------------
Zoology Clerk Zool1
Zoology Clerk Zool2
Mathematics senior clerk Math1
Mathematics senior clerk Math2
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
