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.
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. ( Externale 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 -- Bino / 01 Dec 2023 -- To save the username to another table -- Check the user is available in the users table, if it is not return a message. 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; -- Get the user name 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; -- SOM / Bino / 10 Dec 2023 -- As per the support ticket #123, delete the details from user table. DELETE FROM USERS WHERE USER_POID = P_USER_POID; -- EOM / Author name / Date of creation -- Bino / 18 Dec 2023 -- Write why the below is commented. /* -- Inserting to another table for logging. 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;