PL / SQL

--Veriable decleration
DECLARE
  eName1 VARCHAR2(10) := 'Hello';
BEGIN
  dbms_output.put_line(eName1);
END;
/
--Veriable decleration constant
--The value can not changed
DECLARE
  eName1 CONSTANT VARCHAR2(10) := 'Hello';
BEGIN
  dbms_output.put_line(eName1);
END;
/
--Not Null
DECLARE
  --A variable declared NOT NULL must have an initialization assignment
  eName1 VARCHAR2(10) NOT NULL:= 'Hello';
BEGIN
  dbms_output.put_line(eName1);
  --eName1 := null; -- Error -- Expression is of wrong type
END;
/
--------------------------------------------------------------------------
--Number type veriable
DECLARE
  eNumber NUMBER(10):= 4500;
BEGIN
  dbms_output.put_line(eNumber);
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--veriable Concatination||
DECLARE
  eNumber VARCHAR(10):= 'Hello';
BEGIN
  dbms_output.put_line('This Name is ' || eNumber);
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- Convert number value to char string
DECLARE
  eSalary NUMBER(10):= 4560;
BEGIN
  dbms_output.put_line('This Emp Salary is ' || To_CHAR(eSalary));
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- *SQL
-- For writing a programm in file and execute just "ed f1(file name)"
-- For getting out put in *SQL Command -- set serverout on
DECLARE
  eMsg Varchar(100);
BEGIN
  eMsg := 'Hello PL SQL World';
  dbms_output.put_line(eMsg);
END;
/
--------------------------------------------------------------------------
--Memory veriable
DECLARE
  VSalary NUMBER(10,2);
  EName   VARCHAR(50);
BEGIN
  SELECT SALARY,
    FIRST_NAME
  INTO VSalary,
    EName
  FROM EMPLOYEES
  WHERE FIRST_NAME = 'Lex';
  dbms_output.put_line('The Salary of '|| EName || TO_CHAR(VSalary));
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- IF Condition
DECLARE
  vSalary NUMBER(10,2);
BEGIN
  SELECT salary INTO vSalary FROM employees WHERE first_name = 'Neena';
  IF(vSalary > 6000) THEN
    dbms_output.put_line('Earn more then tom' || vSalary);
  ELSE
    IF (vSalary = 17000) THEN
      dbms_output.put_line('Earn NO then tom' || vSalary);
    ELSE
      dbms_output.put_line('Earn LESS then tom' || vSalary);
    END IF;
  END;
  /
  --------------------------------------------------------------------------
  --------------------------------------------------------------------------
-- LOOP
-- Three kind of loop available in PL SQL 1. LOOP ... LOOP END , 2. While (Condition) Loop .. End Loop
-- FOR ... END LOOP
DECLARE
  c1 NUMBER := 0;
BEGIN
  LOOP
    dbms_output.put_line('Hello');
    c1:=c1 +1;
    EXIT
  WHEN c1 = 5;
  END LOOP;
END;
/
--------------------------------------------------------------------------
 --------------------------------------------------------------------------
-- LOOP
-- While (Condition) Loop .. End Loop
DECLARE
  c1 NUMBER := 10;
BEGIN
  WHILE (c1 < 13)
  LOOP
    dbms_output.put_line('Hello ' || c1);
    c1:=c1 +1;
  END LOOP;
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- LOOP
-- FOR Loop .. End Loop
BEGIN
  FOR i IN 10..13
  LOOP
    dbms_output.put_line('Hello ');
  END LOOP;
END;
/
--------------------------------------------------------------------------
 --------------------------------------------------------------------------
-- Impliciti Cursors
BEGIN
  DELETE FROM employees WHERE employee_id = 120;
  IF SQL%FOUND THEN
    dbms_output.put_line('The row to be deleted was not found');
  ELSE
    dbms_output.put_line('The row was deleted');
  END IF;
END;
/
--------------------------------------------------------------------------
 --------------------------------------------------------------------------
-- Impliciti Cursors
BEGIN
  DELETE FROM employees WHERE employee_id = 120;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('The row to be deleted was not found');
  ELSE
    dbms_output.put_line('The row was deleted');
  END IF;
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- Impliciti Cursors
BEGIN
  DELETE FROM employees WHERE employee_id = 120;
  IF SQL%FOUND THEN
    dbms_output.put_line('The row to be deleted was not found');
  ELSE
    dbms_output.put_line(SQL%ROWCOUNT ||'The row was deleted');
  END IF;
END;
/
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- Explicit Cursors (Work area for sql)
-- There are some steps need to follow to work on explicit cursor
-- 1.Declare a cursor
-- 2.open the cursor
-- 3.Fetching the cursor
-- 4.Close cursor
--
-- Proble raise and why we need to use explicit cursor please take a look on below query which return more then one row
-- If more then depno available wiht 20 then which row will be set in vName memory variable.
BEGIN
  DECLARE
    vName VARCHAR2(45);
  BEGIN
    SELECT ename INTO vName FROM employees WHERE deptno = 20;
    dbms_output.put_line(SQL%ROWCOUNT ||'The row was deleted');
  END;
  /
-- Correct SQL -------------------
BEGIN
  DECLARE
    vName VARCHAR2(45);
    CURSOR c1
    IS
      SELECT FIRST_NAME FROM employees WHERE DEPARTMENT_ID = 50;
  BEGIN
    OPEN c1;
    LOOP
      FETCH c1 INTO vName;
      --statement to exit loop
      dbms_output.put_line(vName);
    END LOOP;
  END;
  /
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- Data type and row type
BEGIN
  DECLARE
    vEmp employees%ROWTYPE;
  BEGIN
    SELECT * INTO vEmp FROM employees WHERE EMPLOYEE_ID = 101;
    dbms_output.put_line(vEmp.FIRST_NAME);
    dbms_output.put_line(vEmp.EMPLOYEE_ID);
  END;
  /
--------------------------------------------------------------------------