PL-SQL

1. PL-SQL Software System Presentation By Amitabh Saikia Avinash Pendur Jaiwant Dang Rajendra Sansare

2. Introduction • Procedural Language extension of SQL • Basic unit in PL/SQL is a BLOCK • Object Oriented FLAVOR

3. DECLARE /* Declarative section: variables, types, and local subprograms. */ BEGIN /* Executable section: procedural and SQL statements go here. */ /* This is the only section of the block that is required. */ EXCEPTION /* Exception handling section: error handling statements go here. */ END;

4. Executable Section • Queries using SELECT,INSERT,UPDATE,DELETE • Constructs assignments,branches,loops,procedure calls, and triggers

5. SQL Statements allowed in PL/SQL • SELECT,INSERT,UPDATE,DELETE • Data Manipulation Statements • Transaction Control

6. Variables and Types • Information is transmitted between a PL/SQL program and the database throughVARIABLES . • One of the types used by SQL for database columns • A generic type used in PL/SQL such as NUMBER • Declared to be the same as the type of some database column

7. Variable Examples • Declare age NUMBER; myname VARCHAR(10); • Assuming Name has the schema (first,second,third). • myname Name.first%TYPE; • mytuple Name%ROWTYPE;

8. Control Flow in PL/SQL • Branching and Looping in PL/SQL • IF statement looks like this IF <condition> THEN <statement_list> ELSE <statement_list> END IF;

9. LOOP Statement LOOP <loop_body> END LOOP; <loop_body> should have an EXIT statement asEXIT WHEN <condition>

10. WHILE LOOP WHILE <condition> LOOP <loop_body> END LOOP;

11. FOR Statement FOR <var> IN <start>..<finish> LOOP <loop_body> END LOOP;

12. CURSORS

13. PROCEDURES CREATE PROCEDURE <procedure_name> (<parameters> <mode> <type> ) AS <local_var_declaration> BEGIN <procedure_body> END; Can have any number of parameters each followed by a mode and a type Types of mode are IN(read only),OUT(write only) and INOUT(read write)

14. FUNCTIONS CREATE FUNCTION <func_name> (<param_list>) RETURN <return_type> AS <function_body> RETURN <expression> <function_body>

15. TRIGGERS CREATE [OR REPLACE] TRIGGER <trigger_name> {BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name> [FOR EACH ROW [WHEN (<trigger_condition>)]] <trigger_body>

16. DISCOVERING ERRORS show errors procedure <procedure_name>; show errors trigger <trigger_name>; SHO ERR" abbreviation for "SHOW ERRORS"

17. Printing Variables • Define a bind variable, which is the only kind • that may be printed with a print command. • Bind a variable as VARIABLE <name> <type> • Assign value to the bound variable , prefix with a “:” • PRINT : <name> ;