CODEWITHSUNDEEP
oracle-databaseplsql
Grandizer
Grandizer

Reputation: 3025

Oracle Sequence Field Increments Based On Different Field

I know SQL Server. On a project using Oracle and having a heck of a time doing things. My current issue is that we have a table that is similar to the following:

ACCOUNT_ID   NUMBER
SEQUENCE_ID  NUMBER
DISPLAY_NAME VARCHAR2
TOKEN        VARCHAR2
PAYMENT_TYPE NUMBER

The Account_ID can occur multiple times in this table. What I need is the Sequence_ID needs to Increment uniquely for each Account_ID. So looking something like this.

ACCOUNT_ID    SEQUENCE_ID    DISPLAY_NAME    TOKEN    PAYMENT_TYPE
------------------------------------------------------------------
111111        1              Primary         abcd     1
222222        1              Primary         bcde     1
222222        2              Secondary       dffs     1
333333        1              Default         fdsf     2
111111        2              Alternate       sfff     2
222222        3              Another         slsl     1

As you can see, the Account_ID of 11111 has 2 records and the Sequence_ID does not repeat for that Account_ID. Basically, Sequence_ID is auto-incrementing but uniquely for each Account_ID.

Is this possible?

Upvotes: 0

Views: 32

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

As illustrated in the above answer perfectly you can use Procedure to do this. If you don't want to use everything in Procedure, you can use function as you can use the return value of function in any way. Hope this helps.

CREATE OR REPLACE FUNCTION create_Account(
  in_account_id    IN ACCOUNTS.ACCOUNT_ID%TYPE,
  in_display_name  IN ACCOUNTS.DISPLAY_NAME%TYPE,
  in_token         IN ACCOUNTS.TOKEN%TYPE,
  in_payment_type  IN ACCOUNTS.PAYMENT_TYPE%TYPE
)
RETURN PLS_INTEGER
AS
  p_seq_id ACCOUNTS.SEQUENCE_ID%TYPE;
BEGIN
  SELECT COUNT(*) + 1
  INTO   p_seq_no
  FROM   ACCOUNTS
  WHERE  ACCOUNT_ID = in_account_id;

RETURN p_seq_no;
END;

Upvotes: 0

mrkovec
mrkovec

Reputation: 376

I think easiest solution to your problem would be to create a trigger on your table, like this:

create or replace trigger yourtable_biu
    before insert or update of ACCOUNT_ID on yourtable
    referencing new as new old as old
    for each row
begin
    select count(*) into :new.SEQUENCE_ID from yourtable where ACCOUNT_ID = new.ACCOUNT_ID;
end;
/

Upvotes: 0

MT0
MT0

Reputation: 168681

You can encapsulate all the logic in a procedure:

CREATE OR REPLACE PROCEDURE create_Account(
  in_account_id    IN ACCOUNTS.ACCOUNT_ID%TYPE,
  in_display_name  IN ACCOUNTS.DISPLAY_NAME%TYPE,
  in_token         IN ACCOUNTS.TOKEN%TYPE,
  in_payment_type  IN ACCOUNTS.PAYMENT_TYPE%TYPE
)
AS
  p_seq_id ACCOUNTS.SEQUENCE_ID%TYPE;
BEGIN
  SELECT COUNT(*) + 1
  INTO   p_seq_no
  FROM   ACCOUNTS
  WHERE  ACCOUNT_ID = in_account_id;

  INSERT INTO ACCOUNTS (
    account_id,
    sequence_id,
    display_name,
    token,
    payment_type
  ) VALUES (
    in_account_id,
    p_seq_id,
    in_display_name,
    in_token,
    in_payment_type
  );
END;
/

Or you could just use an INSERT

INSERT INTO ACCOUNTS
SELECT :account_id,
       COUNT(*) + 1,
       :display_name,
       :token,
       :payment_type
FROM   ACCOUNTS
WHERE  account_id = :account_id;

Upvotes: 2

Related Questions