Oracle PL/SQL/PL SQL/Variable Declare

Declare variable as a loop counter

SQL>
SQL>
SQL> -- unconstrained loop: exit when
SQL>
SQL>
SQL>  declare
  2      l_loops number := 0;
  3    begin
  4      dbms_output.put_line("Before my loop");
  5
  6      loop
  7        exit when l_loops > 4;
  8        dbms_output.put_line("Looped " || l_loops || " times");
  9        l_loops := l_loops + 1;
 10      end loop;
 11
 12      dbms_output.put_line("After my loop");
 13    end;
 14    /
Before my loop
Looped 0 times
Looped 1 times
Looped 2 times
Looped 3 times
Looped 4 times
After my loop
PL/SQL procedure successfully completed.
SQL>

Declare variables which will be used in SQL statements

 
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2    v_NewMajor VARCHAR2(10) := "History";
  3    myFirstName VARCHAR2(10) := "Scott";
  4    v_LastName VARCHAR2(10) := "Urman";
  5  BEGIN
  6    UPDATE lecturer
  7      SET major = v_NewMajor
  8      WHERE first_name = myFirstName
  9      AND last_name = v_LastName;
 10    IF SQL%NOTFOUND THEN
 11      INSERT INTO lecturer (ID, first_name, last_name, major)
 12        VALUES (10020, myFirstName, v_LastName, v_NewMajor);
 13    END IF;
 14  END;
 15  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>

Defining the Indefinable: NULL

 
SQL>
SQL>
SQL> declare
  2    value1 varchar2(100);
  3    value2 varchar2(100) := "";
  4    value3 varchar2(100) := null;
  5    value4 varchar2(100) default null;
  6  begin
  7    null;
  8  end;
  9  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Raise the "no data found" exception

 
SQL>
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
SQL> DECLARE
  2    v_TempVar NUMBER(2);
  3  BEGIN
  4    SELECT id
  5      INTO v_TempVar
  6      FROM lecturer
  7      WHERE last_name = "Smith";
  8  END;
  9  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

SQL>
SQL> drop table lecturer;
Table dropped.
SQL>

Raise the VALUE_ERROR exception

 
SQL>
SQL> DECLARE
  2    v_TempVar VARCHAR2(3);
  3  BEGIN
  4    v_TempVar := "ABCD";
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

SQL>
SQL>
SQL>

Variables and Constants

 
SQL>
SQL> declare
  2    myNumber_variable number;
  3  begin
  4    myNumber_variable := 50;
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL>