Oracle PL/SQL/PL SQL/Variable init

An initialization using the assignment operator (:=)

   <source lang="sql">

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>

      </source>
   
  

Assign init value to a number

   <source lang="sql">

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>

      </source>
   
  

Declare variables to be used in the block

   <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)  := "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>

</source>
   
  

No initial value but Has a size

   <source lang="sql">

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>

      </source>