Add Comments to a PL/SQL block in Oracle

Ask any IT consultant what differentiates a good programmer from a bad one, and they will answer that good programmers always comment their code. Adding comments to code promotes readability and aids in logic comprehension and code maintenance. Comments are strictly informational and do not enforce any conditions or behavioral logic or data. In fact, the Oracle compiler ignores all comments. Comments are used to describe the purpose and use of each code segment. PL/SQL supports two comment styles.

• Single-line Comments
  Single-line comments begin with a double hyphen (--) anywhere on a line and can extend to the end of line.
  

  -- This is an example of a comment
  

Multiline comments begin with /* and end with /. Comments can span multiple lines and multiline comments in PL/SQL can be used to comment out whole sections of code.

/* This is an example of a comment that
spans more than one line. */

This is similar to the syntax of C and Java Programming Languages.
Another great way to enhance clarity and readability of your code is to indent each subsequent level within a hanging indent level. Below is a good example of a well-documented PL/SQL block.

/************************************************/
/* Name:   pet_activity.sql                     */ 
/* Description: Sample Select Pet_Care_Log table*/ 
/* Arguments: (None)                            */ 
/* Return Value: (None)                         */ 
/* History:                                     */ 
/************************************************/
DECLARE
v_logdate DATE;
v_product NUMBER(10);
BEGIN
SELECT LOG_DATETIME,
PRODUCT_ID
INTO v_logdate,
v_product 
 FROM PET_CARE_LOG 
 WHERE PRODUCT_ID = 22;
END;

Now that you have learned about PL/SQL blocks as well as literals, identifiers, and comments, let’s use an instructional simulation to build a nested PL/SQL block. The following simulation demonstrates the creation of a PL/SQL block that declares a variable as a number. This variable is assigned the value 42. This block includes a nested block that converts this number as a string and displays the values using

DBMS_OUTPUT.PUT_LINE.

1. To connect to the database, type a valid user name, password, and service. Type PETSTORE in the User Name box. Type GREATPETS in the Password box. Type MYDB in the Host String box. Click the OK button.
2. To display the result of the PL/SQL block on the screen, set the server output on. Type the following command at the SQL > prompt:
   SET SERVEROUTPUT ONEnd your SET command by clicking ENTER key. This tells SQL*Plus to execute the command.
3. Next, begin the main block with the DECLARE statement. Type DECLARE at the SQL > prompt. End your command by clicking Enter. This tells SQL*Plus to move to the next line.
4. Next, declare a variable of type NUMBER and call it v_num. Type v_num NUMBER; at the 2 prompt. End your command by clicking Enter. This tells SQL*Plus to move to the next line.
5. Start the executable section of the block. Type BEGIN at the 3 prompt. End your command by clicking Enter. This tells SQL*Plus to move to the next line.
6. Assign the value of 42 to v_num. Type v_num := 42; at the 4 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
7. Next, add a comment to start the nested block. Type /* Start nested block */ at the 5 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
8. Next, declare the nested block. Type DECLARE at the 6 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
9. Declare v_char as a VARCHAR2(10) variable that you will use at a later stage to convert the number to a string. Type v_char VARCHAR2(10); at the 7 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
10. Start the executable section of the nested block. Type BEGIN at the 8 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
11. Convert the numeric value (v_num) to a character string using the TO_CHAR function and assign it to v_char. Type v_char := TO_CHAR(v_num); at the 9 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
12. Display the string value using the DBMS_OUTPUT.PUT_LINE package procedure. Type DBMS_OUTPUT.PUT_LINE (v_char); at the 10 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
13. End the nested block. Type END; at the 11 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
14. End the main block. Type END; at the 12 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
15. Type / at the 13 prompt for SQL*Plus to compile your PL/SQL block. End your command by clicking ENTER.
16. SQL*Plus now displays the result of the compilation of your block.

In the next lesson, we will discuss explicit versus implicit datatype conversions.