www.quontrasolutions.com
info@quontrasolutions.com
Introduction to PL SQL Online Training Classes
info@quontrasolutions.com
PL/SQL (Embedded SQL) Introduction Benefits Basic Constructs Anonymous blocks Procedures Functions Packages Triggers Cursors Dynamic SQL
Introduction • Embedded SQL (PL/SQL, JAVA/ VB & DB) • Database Server Level Programming •
(PL/SQL, Transact-SQL, IBM DB2-Cobol, ProC, ProCobol)
• Database Client Programming Developer 9i, JDeveloper 9i, Java (J2EE), VB, .Net
Benefits • More powerful than pure SQL because it combines the power of SQL and – Iteration (loops) – Selection (Ifs) – Cursors – Block Structures – Stored Procedures – etc.
Basic Constructs • • • • • • • •
Basic Structure Running a program Variables SELECT INTO Comments IFs LOOPs Output
Basic Structure DECLARE BEGIN
EXCEPTION END;
OUTPUT SET SERVEROUTPUT ON;
BEGIN DBMS_OUTPUT.PUT_LINE('This is my fist program');
END; / Before executing code that contains DBMS_OUTPUT.PUT_LINE, must run (in a current session) at SQL prompt: set serveroutput on
Basic Structure: Example DECLARE v_id INTEGER; v_empno NUMBER; BEGIN v_id := 1234567; SELECT EMPNO INTO V_EMPNO FROM EMP WHERE empno = v_id; DBMS_OUTPUT.PUT_LINE('Value is '||v_empno); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No record exists'); END; /
Basic Structure: Example DECLARE v_id INTEGER; BEGIN v_id := 1234567; DELETE FROM EMP WHERE id = v_id; END; /
Running a Program DECLARE BEGIN
EXCEPTION END;
/
Variables •
Common Data Types – – – – – –
•
NUMBER DATE INTEGER VARCHAR2 CHAR BOOLEAN
Declaration – – – – – –
V_salary NUMBER(9,2); V_id INTEGER; V_dob DATE; V_name VARCHAR2(35); V_gender CHAR; V_salary emp.salary%TYPE;
www.quontrasolutions.com
info@quontrasolutions.com
For More Details Contact us
Quontra Solutions Visit: http://www.quontrasolutions.com/ Email: info@quontrasolutions.com Call Now : (404)-900-9988. (USA)
info@quontrasolutions.com
Contd‌