Oracle PL/SQL/PL SQL/Comments

Multiline comments

   <source lang="sql">

SQL> SQL> CREATE TABLE session (

 2    department       CHAR(3),
 3    course           NUMBER(3),
 4    description      VARCHAR2(2000),
 5    max_lecturer     NUMBER(3),
 6    current_lecturer NUMBER(3),
 7    num_credits      NUMBER(1),
 8    room_id          NUMBER(5)
 9    );

Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);

1 row created. SQL> SQL> DECLARE

 2    v_Department  CHAR(3);  /* Variable to hold the 3 character
 3                               department name */
 4    v_Course      NUMBER;   /* Variable to hold the course number */
 5  BEGIN
 6    /* Insert the course identified by v_Department and v_Course
 7       into the session table in the database. */
 8    INSERT INTO session (department, course)
 9      VALUES (v_Department, v_Course);
10  END;
11  /

PL/SQL procedure successfully completed. SQL> SQL> drop table session; Table dropped. SQL>

</source>
   
  

One line comment

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE session (

 2    department       CHAR(3),
 3    course           NUMBER(3),
 4    description      VARCHAR2(2000),
 5    max_lecturer     NUMBER(3),
 6    current_lecturer NUMBER(3),
 7    num_credits      NUMBER(1),
 8    room_id          NUMBER(5)
 9    );

Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);

1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)

 2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);

1 row created. SQL> SQL> DECLARE

 2    v_Department  CHAR(3);  
 3                            
 4    v_Course      NUMBER;   
 5  BEGIN
 6    
 7    
 8    INSERT INTO session (department, course)
 9      VALUES (v_Department, v_Course);
10  END;
11  /

PL/SQL procedure successfully completed. SQL> SQL> drop table session; Table dropped. SQL>

</source>
   
  

Remarks in Scripts

   <source lang="sql">

SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION

---

---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> -- Remarks in Scripts SQL> SQL> SET echo off SQL> REM February 13, 2006 SQL> REM modified Feb. 14, 2006 SQL> REM Script for employee"s current salary report SQL> SELECT id, first_name, salary, city FROM employee ORDER BY city

 2  /

ID FIRST_NAME SALARY CITY

---

---------- ---------- ----------

07 David 7897.78 New York 06 Linda 4322.78 New York 01 Jason 1234.56 Toronto 05 Robert 2334.78 Vancouver 08 James 1232.78 Vancouver 03 James 6544.78 Vancouver 02 Alison 6661.78 Vancouver 04 Celia 2344.78 Vancouver 8 rows selected. SQL> REM clean up parameters set before the SELECT SQL> SET verify on SQL> SET echo on SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL>

      </source>