How to give comments in SQL

Question

I am working in SQL. What is the complete explanation for using comments in SQL, with some example code?

Answer 1

MySQL Server supports three comment styles:

From a # character to the end of the line.

From a -- sequence to the end of the line. In MySQL, the “-- ” (double-dash) comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on). This syntax differs slightly from standard SQL comment syntax, as discussed in Section 1.8.2.5, “'--' as the Start of a Comment”.

From a /* sequence to the following */ sequence, as in the C programming language. This syntax enables a comment to extend over multiple lines because the beginning and closing sequences need not be on the same line.

Reference http://dev.mysql.com/doc/refman/5.0/en/comments.html

Answer 2

In Oracle, there are three ways.

1.

SQL> REM This is a comment

2.

SQL> -- This is a single line comment

3.

SQL> /* This is a
SQL> multiple line
SQL> comment */
SQL>

The difference between REM and the other two is that, -- and /* */ can be used in a PL/SQL block, while REM[ARK] cannot.

Let's see.

SQL> REM comment 1
SQL> -- comment 2
SQL> /* comment 3*/
SQL> begin
  2     DBMS_OUTPUT.PUT_LINE('comment 1'); --comment 1
  3     DBMS_OUTPUT.PUT_LINE('comment 2'); /* comment 2*/
  4  end;
  5  /
comment 1
comment 2

PL/SQL procedure successfully completed.

SQL>

So, -- and /* */ works in PL/SQL block too. However, REM won't.

SQL> begin
  2     DBMS_OUTPUT.PUT_LINE('comment'); REM comment
  3  end;
  4  /
   DBMS_OUTPUT.PUT_LINE('comment'); REM comment
                                        *
ERROR at line 2:
ORA-06550: line 2, column 41:
PLS-00103: Encountered the symbol "COMMENT" when expecting one of the
following:
:= . ( @ % ;
The symbol "; was inserted before "COMMENT" to continue.

Update Mostly all GUI based tools are able to execute SQL*Plus commands in their own sqlplus type window. It works perfectly in SQL Developer when executed as a script. In PL/SQL Developer too it should work with COMMAND window.

A screenshot from SQL Developer.

Same in SQL*Plus.

SQL> SELECT * FROM DUAL;

D
-
X

SQL> REM THIS IS A COMMENT
SQL> SELECT 'ABOVE COMMENT WORKS' FROM DUAL;

'ABOVECOMMENTWORKS'
-------------------
ABOVE COMMENT WORKS

Answer 3

You can write a comment in SQL & PLSQL statements in two easy ways:

1. Begin the comment section with a forward slash and an asterisk (/*). Proceed with the text of the comment. This text can be spanning multiple lines.

2. Begin the comment section with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line for new commenting. Every line start with – (two hyphens). Let see below examples

   SELECT e.ename /* Author: OnlinteItTutor.com */ FROM emp e;

Here is a SQL comment in query that appears in the middle of the line:

SELECT  /* Author: OnlinteItTutor.com */  e.ename
FROM emp e;

Here is a SQL comments in query that appears at the end of the line:

SELECT e.ename /* Author: OnlinteItTutor.com */
FROM emp e;    

Or:

SELECT e.ename  -- Author: OnlinteItTutor.Com
FROM emp e;

In Oracle, you can create a SQL comment that shows multiple lines in your SQL statement. For example:

SELECT e.ename
/*
 * Author: OnlineItTutor.Com
 * Purpose: To show a comment of multiple lines in SQL Query.
 */
FROM emp e;

This SQL comment in query shows across multiple lines in Oracle - in this example, it shows across 4 lines. In Oracle, you can also create a SQL comment in query that spans multiple lines using this syntax:

SELECT e.ename /* Author: OnlineItTutor.com
Purpose: To show a comment in SQL Query for multiple lines. */
FROM emp e;

Answer 4

/*
this is a
multiple-line comment
*/

Answer 5

MySQL Server supports three comment styles:

From a “#” character to the end of the line.

From a “-- ” sequence to the end of the line. In MySQL, the “-- ” (double-dash) comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on). This syntax differs slightly from standard SQL comment syntax, as discussed in Section 1.8.2.5, “'--' as the Start of a Comment”.

From a /* sequence to the following */ sequence, as in the C programming language. This syntax enables a comment to extend over multiple lines because the beginning and closing sequences need not be on the same line. The following example demonstrates all three comment styles:

mysql> SELECT 1+1;     # This comment continues to the end of line
mysql> SELECT 1+1;     -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;