Oracle PL/SQL/PL SQL/Variable init

An initialization using the assignment operator (:=)

SQL>
SQL> -- An initialization using the assignment operator (:=).
SQL> set serverout on;
SQL>
SQL> DECLARE
  2     X NUMBER(11,2) := 10;
  3
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE(x);
  6  END;
  7
  8  /
10
PL/SQL procedure successfully completed.
SQL>

Assign init value to a number

SQL>
SQL> -- Adding ELSE to the IF block.
SQL> set echo on
SQL> DECLARE
  2    v_HoursWorked Number := 50 ;
  3    v_OverTime Number ;
  4  BEGIN
  5    IF v_HoursWorked > 40 THEN
  6      v_OverTime := v_HoursWorked - 40;
  7         DBMS_OUTPUT.PUT_LINE("Hours overtime worked = " || v_OverTime);
  8    ELSE
  9      v_OverTime := 0;
 10    END IF;
 11  END;
 12  /
Hours overtime worked = 10
PL/SQL procedure successfully completed.
SQL>

Declare variables to be used in the block

 
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)  := "ECN";
  3    v_Course           NUMBER(3) := 203;
  4    v_Description      VARCHAR2(20) := "Economics 203";
  5    studentMax      NUMBER := 15;
  6    studentCount       NUMBER := 0;
  7    v_NumCredits       NUMBER := 3;
  8    v_RoomID           NUMBER := 99997;
  9  BEGIN
 10    INSERT INTO session (department, course, description, max_lecturer,
 11                         current_lecturer, num_credits, room_id)
 12      VALUES (v_Department, v_Course, v_Description, studentMax,
 13              studentCount, v_NumCredits, v_RoomID);
 14  END;
 15  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from session;
DEP     COURSE
--- ----------
DESCRIPTION
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS    ROOM_ID
------------ ---------------- ----------- ----------
HIS        101
History 101
          30               11           4      20000
HIS        301
History 301
          30                0           4      20004
DEP     COURSE
--- ----------
DESCRIPTION
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS    ROOM_ID
------------ ---------------- ----------- ----------
CS         101
Computer Science 101
          50                0           4      20001
ECN        203
Economics 203
DEP     COURSE
--- ----------
DESCRIPTION
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS    ROOM_ID
------------ ---------------- ----------- ----------
          15                0           3      20002
CS         102
Computer Science 102
          35                3           4      20003
MUS        410
DEP     COURSE
--- ----------
DESCRIPTION
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS    ROOM_ID
------------ ---------------- ----------- ----------
Music 410
           5                4           3      20005
ECN        101
Economics 101
          50                0           4      20007

DEP     COURSE
--- ----------
DESCRIPTION
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS    ROOM_ID
------------ ---------------- ----------- ----------
NUT        307
Nutrition 307
          20                2           4      20008
MUS        100
Music 100
         100                0           3
DEP     COURSE
--- ----------
DESCRIPTION
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS    ROOM_ID
------------ ---------------- ----------- ----------
ECN        203
Economics 203
          15                0           3      99997

10 rows selected.
SQL>
SQL> drop table session;
Table dropped.
SQL>

No initial value but Has a size

SQL>
SQL> -- No initial value but Has a size.
SQL> set serverout on;
SQL>
SQL> DECLARE
  2     X NUMBER(11,2);
  3
  4  BEGIN
  5
  6
  7     DBMS_OUTPUT.PUT_LINE(x);
  8
  9  END;
 10  /
PL/SQL procedure successfully completed.
SQL>