sybaseguru
Instructor
We have an application running using transact sql. This application is being migrated to Oracle9i. It uses the next rowid table to go and fetch value for the primary key of a give table. We will be modifying the design to be more appropriate to Oracle. The table holding the next rowid value has two columns, nxr_name nxr_next_rowid. These are the table and and next rowid to be used respectively. The transact sql code is as follows:
This code basically goes and returns the nxr_next_rowid and make sure that the row is locked until the nxr_next_rowid value is incremented.
The PL/SQL code for this turns up to be
This compiles OK. However, I feel that we can improve the code better. Your comments specifically with regard to exceptions code etc is appreciated and if you think there are better ways of coding it please feel free to show it.
thanks
Code:
----------
-- RETURN values
--
-- >0 next rowID [success] --
-- -1 missing/empty parameter --
-- -100 UPDATE failed --
-- -101 table does not exist in 'hts_next_rowid' --
CREATE PROCEDURE
get_next_rowid_rv_sp
@table_name db_obj_name_t,
@nr int = 1
AS -- 380.95
BEGIN
SET nocount ON -- performance enhancement
-- check arguments passed
IF (ISNULL(@table_name, "") = "")
BEGIN
PRINT "get_next_rowid_rv_sp: Must supply argument @table_name"
RETURN -1
END
-- check the table exists
IF NOT EXISTS ( SELECT 1
FROM hts_next_rowid
WHERE nxr_name = @table_name)
BEGIN
PRINT "get_next_rowid_rv_sp: '%1!' does not have an entry in 'hts_next_rowid'",
@table_name
RETURN -101
END
IF (ISNULL(@nr, 0) < 1) -- make sure value is set correctly
SELECT @nr = 1
DECLARE @rv int,
@tc int
-- start a transaction to prevent
-- another process from reading an
-- incorrect value
-- determine if we need to open a new
-- transaction or do a savepoint
SELECT @tc = @@trancount
IF (@tc > 0)
SAVE TRANSACTION
get_next_rowid_rv_sp_trans
ELSE
BEGIN TRANSACTION
get_next_rowid_rv_sp_trans
UPDATE hts_next_rowid
SET nxr_next_rowid = nxr_next_rowid + @nr
WHERE nxr_name = @table_name
IF (@@error != 0)
BEGIN
PRINT "get_next_rowid_rv_sp: UPDATE failed"
ROLLBACK TRANSACTION
get_next_rowid_rv_sp_trans
RETURN -100
END
SELECT @rv = nxr_next_rowid - @nr
FROM hts_next_rowid
WHERE nxr_name = @table_name
IF (@tc = 0)
COMMIT TRANSACTION
get_next_rowid_rv_sp_trans
RETURN @rv
END
goThis code basically goes and returns the nxr_next_rowid and make sure that the row is locked until the nxr_next_rowid value is incremented.
The PL/SQL code for this turns up to be
Code:
CREATE OR REPLACE FUNCTION get_next_rowid
(
i_table_name IN hts_next_rowid.nxr_name%TYPE DEFAULT NULL,
i_nr IN NUMBER DEFAULT 1
)
RETURN NUMBER
AS
-- RETURN values
--
-- >0 next rowID [success] --
-- -1 missing/empty parameter --
-- -100 UPDATE failed --
-- -101 table does not exist in 'hts_next_rowid'
--
e_table_name EXCEPTION;
v_table_name hts_next_rowid.nxr_name%TYPE DEFAULT NULL;
v_nr NUMBER := i_nr;
v_nxr_next_rowid hts_next_rowid.nxr_next_rowid%TYPE DEFAULT NULL;
e_update EXCEPTION;
e_select EXCEPTION;
BEGIN
BEGIN -- Input parameter validation
-- check for inputs
IF i_table_name IS NULL
THEN
RAISE e_table_name;
END IF;
IF i_nr <= 1
THEN
-- DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: Warning i_nr = '||i_nr||', set to 1');
v_nr := 1;
END IF;
EXCEPTION
WHEN e_table_name
THEN
DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: Missing/empty parameter, must supply table name');
RETURN -1;
END; -- End input parameter validation
BEGIN -- Check that row exists in next_rowid
SELECT nxr_name
INTO v_table_name
FROM hts_next_rowid
WHERE nxr_name = i_table_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: No record found in hts_next_rowid table for '||i_table_name||'!');
RETURN -101;
END; -- end check that row exists in next_rowid
BEGIN -- select for update block
SELECT nxr_name
INTO v_table_name
FROM hts_next_rowid
WHERE nxr_name = i_table_name FOR UPDATE; -- lock the row
UPDATE hts_next_rowid
SET nxr_next_rowid = nxr_next_rowid + v_nr
WHERE nxr_name = i_table_name;
IF SQL%ROWCOUNT = 0 OR SQLCODE != 0
THEN
RAISE e_update;
END IF;
SELECT nxr_next_rowid - v_nr
INTO v_nxr_next_rowid
FROM hts_next_rowid
WHERE nxr_name = i_table_name;
IF SQL%ROWCOUNT = 0 OR SQLCODE != 0
THEN
RAISE e_select;
END IF;
commit;
RETURN v_nxr_next_rowid;
EXCEPTION
WHEN e_update
THEN
DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: Could not update nxr_next_rowid in hts_next_rowid table for '||i_table_name||'!');
RETURN -100;
WHEN e_select
THEN
DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: Could not get nxr_next_rowid in hts_next_rowid table for '||i_table_name||'!');
RETURN -100;
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM,true);
RETURN -100;
END; -- end select for update
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT('Exception encountered! (');
DBMS_OUTPUT.PUT_LINE(SQLCODE || '): ' || SQLERRM);
RETURN -100;
END get_next_rowid;This compiles OK. However, I feel that we can improve the code better. Your comments specifically with regard to exceptions code etc is appreciated and if you think there are better ways of coding it please feel free to show it.
thanks