Writings comments in the code will help you and anyone who reads the code by giving more readability and understandings. As you know the complier ignores the comments and there is no straight way to verify the comments, we should make sure that the comments are easily readable and should not duplicate the code.
We need to keep the same in the mind when we are writing Stored Procedures. so here I am trying to make some guidelines to remember when we are commeting Stored Procedures.
Commenting guidelines
Below are the commenting guidelines for writing or modfying the Stored Procedures in your database:
- Mention the name of the author and date of creation.
- Purpose of the stored procedure ( What is does).
- Effects of the procedure. ( External variables, Tables, files, etc.)
- Specify the purpose of the input parameters.
- Explain the values returned by the procedure.
- Page or module from the stored procedure is calling.
- Write comments which includes what is the code doing and why its doing the same.
- If any modification is doing mention the name of the author and date of modification.
- Reason for the modification. ( Bug fixing, Improvemnts, Support tickets, etc)
- Write exception handing methods and log it. ( Optional)
Example procedure in PL/SQL
create or replace PROCEDURE SP_NAME(
P_COMPANY_POID NUMBER,
P_USER_POID NUMBER,
P_STATUS OUT VARCHAR2
)
AS
L_COUNT NUMBER;
L_USER_NAME VARCHAR2(100);
L_ERROR_LINE VARCHAR2(4000);
L_ERROR_MSG VARCHAR2(4000);
BEGIN
-- Author Name
-- Date of creation
-- Description of the stored procedure.
-- If its possible, explain procedure parameters.
-- SOM1 / Author name / Date / Details of modifications.
-- SOM2 / Author name / Date / Details of modifications.
-- Write comments describing what is the below code doign and why the same is doing.
BEGIN
SELECT COUNT(*) INTO L_COUNT FROM USERS
WHERE USER_POID = P_USER_POID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_COUNT := 0;
END;
IF L_COUNT = 0 THEN
P_STATUS := 'ERROR: No Record found in table.';
RETURN;
END IF;
-- Comments
SELECT USER_NAME INTO L_USER_NAME FROM USERS WHERE USER_POID = P_USER_POID;
INSERT INTO NEW_USERS_TABLE
( USER_NAME )
SELECT L_USER_NAME FROM DUAL;
-- SOM1 / Author name / Date of creation ** SOM means start of modification.
-- Reason and details of the change.
DELETE FROM USERS WHERE USER_POID = P_USER_POID;
-- EOM / Author name / Date of creation
-- Author name / Date of modification.
-- Write why the below is commented.
/*
-- Comments
INSERT INTO USERS_MIGRATED
( USER_NAME )
SELECT L_USER_NAME FROM DUAL;
*/
-- Error handling
EXCEPTION
WHEN OTHERS THEN
L_ERROR_LINE := DBMS_UTILITY.FORMAT_ERROR_STACK ||' at line ' || $$PLSQL_LINE
|| ' ERROR_BACKTRACE: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
L_ERROR_MSG := SQLERRM;
P_STATUS := 'ERROR : In document posting ' || L_ERROR_LINE;
END;
Hope the above will help you to comment the Stored Procedures more effectively.
Use the share button below if you liked it.
It makes me smile, when I see it.