PLSQL Software System Presentation By Amitabh Saikia Avinash
PL-SQL Software System Presentation By Amitabh Saikia Avinash Pendur Jaiwant Dang Rajendra Sansare
Introduction • Procedural Language extension of SQL • Basic unit in PL/SQL is a BLOCK • Object Oriented FLAVOR
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;
Executable Section • Queries using SELECT, INSERT, UPDATE, DELETE • Constructs assignments, branches, loops, procedure calls, and triggers
SQL Statements allowed in PL/SQL • SELECT, INSERT, UPDATE, DELETE • Data Manipulation Statements • Transaction Control
Variables and Types • Information is transmitted between a PL/SQL program and the database through VARIABLES. – 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
Variable Examples • Declare age NUMBER; myname VARCHAR(10); • Assuming Name has the schema (first, second, third). • myname Name. first%TYPE; • mytuple Name%ROWTYPE;
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;
LOOP Statement LOOP <loop_body> END LOOP; <loop_body> should have an EXIT statement as EXIT WHEN <condition>
WHILE LOOP WHILE <condition> LOOP <loop_body> END LOOP;
FOR Statement FOR <var> IN <start>. . <finish> LOOP <loop_body> END LOOP;
CURSORS
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)
FUNCTIONS CREATE FUNCTION <func_name> (<param_list>) RETURN <return_type> AS <function_body> RETURN <expression> <function_body>
TRIGGERS CREATE [OR REPLACE] TRIGGER <trigger_name> {BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name> [FOR EACH ROW [WHEN (<trigger_condition>)]] <trigger_body>
DISCOVERING ERRORS show errors procedure <procedure_name>; show errors trigger <trigger_name>; SHO ERR" abbreviation for "SHOW ERRORS"
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> ;
- Slides: 17