Oracle 10g: Guideline Create Update SP

CREATE OR REPLACE PACKAGE [Package Name] IS

TYPE [cursor name] is ref cursor;
PROCEDURE [sp_updateprocedure]
(
[input parameter 1] IN INTEGER,
[input parameter 2] IN INTEGER,
[input parameter 3] IN INTEGER,
[input parameter 4] IN INTEGER
);

END [Package Name];
/


CREATE OR REPLACE PACKAGE BODY [Package Name] IS
PROCEDURE [sp_updateprocedure]
(
[input parameter 1] IN INTEGER,
[input parameter 2] IN INTEGER,
[input parameter 3] IN INTEGER,
[input parameter 4] IN INTEGER
)
AS
BEGIN
UPDATE [table name]
SET
[table name].[fieldname 1] = [input parameter 1],
[table name].[fieldname 2] = [input parameter 2]
WHERE [table name].[fieldname 2] = [input parameter 3];
END;


END [Package Name];
/

Oracle 10g: Guideline Create Get SP

CREATE OR REPLACE PACKAGE [Package Name] IS

TYPE [cursor name] is ref cursor;
PROCEDURE [sp_getprocedure]
(
[input parameter 1] IN INTEGER,
[input parameter 2] IN VARCHAR2(1000),
[output cursor parameter] OUT [cursor name]
);
END [Package Name];
/


CREATE OR REPLACE PACKAGE BODY [Package Name] IS
PROCEDURE [sp_getprocedure]
(
[input parameter 1] IN INTEGER,
[input parameter 2] IN VARCHAR2(1000),
[output cursor parameter] OUT [cursor name]
)

AS
[var 1] INTEGER;
[var 2] VARCHAR2(100);

BEGIN

OPEN [output cursor parameter] FOR


SELECT [fieldname 1],[fieldname 2],
[fieldname 3],[fieldname 4],
[fieldname 5]
FROM [table name 1],[table name 2]
WHERE (
([table name 1].[fieldname 1] =
[input parameter 1]);
);
END;


END [Package Name];
/

Oracle 10g: Guideline Create Add SP

CREATE OR REPLACE PACKAGE [Package Name] IS
PROCEDURE [sp_addprocedure]
(
[output parameter] OUT [data type],
[input parameter 1] IN [table name].[fieldname]%TYPE,
[input parameter 2] IN [table name].[fieldname]%TYPE
);
END [Package Name];
/

CREATE OR REPLACE PACKAGE BODY [Package Name] IS
PROCEDURE [sp_addprocedure]
(
[output parameter] OUT [data type],
[input parameter 1] IN [table name].[fieldname]%TYPE,
[input parameter 2] IN [table name].[fieldname]%TYPE
)
AS
TABLEID INTEGER;
BEGIN
SELECT MAX([output parameter])
INTO TABLEID
from [table name]
where BRANCHID = [input parameter 5];

IF TABLEID IS NULL THEN
TABLEID := 0;
END IF;

TABLEID := TABLEID + 1;

INSERT INTO [table name]
(
[fieldname 1],[fieldname 2]
)
values
(
TABLEID,[input parameter 1],[input parameter 2]

)RETURNING [fieldname 1] INTO [input parameter 1];
END;
END [Package Name];
/