Oracle PL/SQL Introduction
*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--
