Oracle PL/SQL Introduction

Oracle PL/SQL Introduction

*Procedural Language extension with SQL with design features of Programming language.
*Data Manipulation & query Statement of SQL are included with procedural Unit of
Code.
*It mainly used for reduce the network traffic.
*It group the set of logical statement Into single block & send the block
to the server for execution.

CONCEPTS IN PL/SQL::

1.Anonymous Block
2.Conditional Statements
3.Cursors
4.Exception
5.Stored Procedure
6.Stored Functions
7.Packages
8.Triggers

1.Unnamed block or Anonymous Block Structure::

DECLARE
           variable;
           cursor;
           Exception;
           SubProgram(Like Procedure);
BEGIN

           SQL Statements (*Mandatory)
           Business logics
EXCEPTION (*Optional)
Error Handling;
END;
/


EXAMPLE FOR ANONYMOUS BLOCK::
SQL> SET SERVEROUTPUT ON;
SQL> BEGIN
2 dbms_Output.put_Line('This is My First Program');
3 END;
4 /

This is My First Program
PL/SQL procedure successfully completed.

VARIABLES::

*Temporary Storage of Data
*Manipulation of Stored Value
*Reusability
PL/SQL VARIABLES::
------------------
1.Scalar
2.Composite
3.Reference
4.Large Object

NON PL/SQL VARIABLES::

1.Bind Variables

Standard Rules to be follow to Declare PL/SQL Variable::

*Follow Naming rule
(v_Name...(Start with v_)
1-30 Character size
*Initialize NOT NULL/Constant/Default
*Declare One identified Per line
*Initialize identifiers by using assignment Operator(:=)


EXAMPLE-1

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_a NUMBER(3):=4;
3 BEGIN
4 dbms_Output.put_Line('The Value Of a Is :' || v_a);
5 END;
6 /
The Value Of a Is :4
PL/SQL procedure successfully completed.
------------------------------------------------------------------

Example-2

DECLARE
v_a NUMBER:=&a;
v_b NUMBER:=&b;
v_c NUMBER;
BEGIN
v_c := v_a + v_b;
dbms_output.put_Line('The Values Of a+b Is :' || v_c);
END;
/
Enter value for a: 4
Enter value for b: 5
The Values Of a+b Is :9
-----------------------------------------------------------------

Example-3


DECLARE
   v_a             NUMBER := 100;
   v_name          VARCHAR2 (100) := INITCAP ('Arul Xavier');
   v_dt            DATE := SYSDATE;
   v_dt_dif_fmt    VARCHAR2 (30) := TO_CHAR (SYSDATE, 'DD Mon, YYYY');
   v_dept          VARCHAR2 (10) NOT NULL := 'CSE';
   v_depid         NUMBER DEFAULT 10;
   v_pi   CONSTANT NUMBER := 3.14;
BEGIN
   v_a := 200;
   DBMS_OUTPUT.put_line ('Number : ' || v_a);
   DBMS_OUTPUT.put_line ('Name : ' || v_name);
   DBMS_OUTPUT.put_line ('Date : ' || v_dt);
   DBMS_OUTPUT.put_line ('Char Chk : ' || v_dt_dif_fmt);
   DBMS_OUTPUT.put_line ('Dname : ' || v_dept);
   DBMS_OUTPUT.put_line ('did : ' || v_depid);
   DBMS_OUTPUT.put_line ('PI : ' || v_pi);
END;
/
------------------------------------------------------------------

NESTED BLOCK or SCOPE OF VARIABLE::


<<Outer>>
DECLARE
   v_a   NUMBER := 20;
   v_c   NUMBER := 100;
   v_d   NUMBER;
   v_e   BOOLEAN := FALSE;
   v_f   NUMBER (200) := 39000;
BEGIN
   DBMS_OUTPUT.put_Line ('-------->' || v_e);
   DBMS_OUTPUT.put_Line ('-------->' || v_f);

   IF v_e
   THEN
      DBMS_OUTPUT.put_Line ('Boolean Check :' || 1 || '<------------');
   ELSE
      DBMS_OUTPUT.put_Line ('Boolean Check :' || 0 || '<------------');
   END IF;

  <<inner>>
   DECLARE
      v_a   NUMBER := 30;
   BEGIN
      DBMS_OUTPUT.put_Line ('Value Of a is:' || Outer.v_a);
      DBMS_OUTPUT.put_Line ('Value Of a is:' || v_c);
   END;

   DBMS_OUTPUT.put_Line ('Value Of a is:' || v_a);
END;
/
--------------------------------------------------------------------
DROP TABLE TEST_INSERT
/
CREATE TABLE TEST_INSERT(id NUMBER)
/
BEGIN
INSERT INTO TEST_INSERT VALUES(1000);
INSERT INTO TEST_INSERT VALUES(1001);
INSERT INTO TEST_INSERT VALUES(1002);
INSERT INTO TEST_INSERT VALUES(1003);
INSERT INTO TEST_INSERT VALUES(1004);
INSERT INTO TEST_INSERT VALUES(1005);
COMMIT;
END;
/
-->--
DECLARE
   v_salary   NUMBER (8);
BEGIN
   SELECT MAX (salary) INTO v_salary FROM employees;

   DBMS_OUTPUT.put_line ('Maximum Salary : ' || v_salary);
END;
/
-------------------------------------------------------------------------------
DECLARE
   v_empid    employees.employee_id%TYPE := 100;
   v_fname    employees.first_name%TYPE;
   v_salary   employees.salary%TYPE;
   v_hdate    employees.hire_date%TYPE;
BEGIN
   SELECT first_name, salary, hire_date
     INTO v_fname, v_salary, v_hdate
     FROM employees
    WHERE employee_id = v_empid;

   DBMS_OUTPUT.put_line (' First Name : ' || v_fname);
   DBMS_OUTPUT.put_line (' Salary : ' || v_salary);
   DBMS_OUTPUT.put_line (' Hire Date : ' || v_hdate);
END;
/
-------------------------------------------------------------------------------
DECLARE
   v_empid     employees.employee_id%TYPE := 100;
   v_alldate   employees%ROWTYPE;
BEGIN
   SELECT *
     INTO v_alldate
     FROM employees
    WHERE employee_id = v_empid;

   DBMS_OUTPUT.put_line (' First Name : ' || v_alldate.first_name);
   DBMS_OUTPUT.put_line (' Salary : ' || v_alldate.salary);
   DBMS_OUTPUT.put_line (' Hire Date : ' || v_alldate.hire_date);
END;
/
----------------------------------------------------------------------------------
DECLARE
   v_department_id   NUMBER := 80;
BEGIN
   DELETE FROM employees
         WHERE department_id = v_department_id;
END;
/


BIND VARIABLES::

By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time.

SQL> VARIABLE a NUMBER;
SQL>
SQL>
SQL> BEGIN
2 SELECT MAX(salary) INTO :a FROM employees;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> PRINT a;
A
----------
24000
SQL> SELECT :a FROM dual;
:A
----------
24000

                                               --END PL/SQL INTRO--