Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: [Q] How to get a blank number?
"±ÇÇõȯ(Kwon Hyokhwan)" wrote:
> - TEST_TBL
> +------+-------+
> | CID | CNAME |
> +------+-------+
> | 1 | aaa |
> | 2 | bbb |
> | 4 | ddd |
> | 5 | eee |
> : :
>
> PROCEDURE GetBlankID()
> ........
> ........
>
> I want to get number 3.
> How to create procedure or function?
>
> error code :
>
> PROCEDURE GetBlankID()
> IS
> nIndex NUMBER(10) := 1;
> nCmp NUMBER(10) := 1;
> BEGIN
> DECLARE CURSOR C_Pos IS
> SELECT CID FROM TEST_TBL;
> open C_Pos;
> LOOP
> FETCH C_Pos into nCmp;
> EXIT when C_Pos%NOTFOUND;
> IF nIndex <> nCmp THEN EXIT;
> nIndex := nIndex + 1;
> END LOOP;
> CLOSE C_Pos;
>
> INSERT INTO TEST_TBL(CID, CNAME) VALUES( nCID, 'reserved');
>
> SELECT nCID AS 'BlankID';
> END
What you do is go to http://technet.oracle.com and look up sequences
which is the proper way to generate numbers in Oracle.
Daniel Morgan Received on Tue Jun 18 2002 - 10:04:40 CDT