Download
1 / 131
1.31k likes | 1.63k Views
PL/SQL. PL/SQL. What is it ?. PL/SQL. What is it ? PL/SQL = procedural Programming Language + SQL. PL/SQL. What is it ? PL/SQL = procedural P rogramming L anguage + SQL. PL/SQL. Why do we need it ?. PL/SQL. Why do we need it ? To bridge a gap between high level
E N D
PL/SQL • What is it ?
PL/SQL • What is it ? PL/SQL = procedural Programming Language + SQL
PL/SQL • What is it ? PL/SQL = procedural Programming Language + SQL
PL/SQL • Why do we need it ?
PL/SQL • Why do we need it ? To bridge a gap between high level declarative query language and procedural programming language
PL/SQL • Advantages
PL/SQL • Advantages • PL/SQL provides procedural capabilities
PL/SQL • Advantages • PL/SQL provides procedural capabilities • PL/SQL improves performance
PL/SQL • Advantages • PL/SQL provides procedural capabilities • PL/SQL improves performance • PL/SQL enhances productivity
PL/SQL • Advantages • PL/SQL provides procedural capabilities • PL/SQL improves performance • PL/SQL enhances productivity • PL/SQL enables portability
PL/SQL • Advantages • PL/SQL provides procedural capabilities • PL/SQL improves performance • PL/SQL enhances productivity • PL/SQL enables portability • PL/SQL integrates RDBMSs
PL/SQL • Overview
PL/SQL • Overview PL/SQL = INSERT + UPDATE + DELETE + SELECT +
PL/SQL • Overview PL/SQL = INSERT + UPDATE + DELETE + SELECT + variables +
PL/SQL • Overview PL/SQL = INSERT + UPDATE + DELETE + SELECT + variables + assignment statements +
PL/SQL • Overview PL/SQL = INSERT + UPDATE + DELETE + SELECT + variables + assignment statements + conditional control statement +
PL/SQL • Overview PL/SQL = INSERT + UPDATE + DELETE + SELECT + variables + assignment statements + conditional control statement + repetition statement +
PL/SQL • Overview PL/SQL = INSERT + UPDATE + DELETE + SELECT + variables + assignment statements + conditional control statement + repetition statement + exception handling +
PL/SQL • Overview PL/SQL = INSERT + UPDATE + DELETE + SELECT + variables + assignment statements + conditional control statement + repetition statement + exception handling + procedures and functions +
PL/SQL • Overview PL/SQL = INSERT + UPDATE + DELETE + SELECT + variables + assignment statements + conditional control statement + repetition statement + exception handling + procedures and functions + packages
PL/SQL • Program structure
PL/SQL • Program structure • PL/SQL is a block-structured language
PL/SQL • Program structure • PL/SQL is a block-structured language It means that its basic units like procedures, functions and anonymous blocks that make up PL/SQL program are logical blocks, which can contain a number of nested sub-blocks
PL/SQL • Block structure
PL/SQL • Block structure • PL/SQL block, procedure, function consist of the following components:
PL/SQL • Block structure • PL/SQL block, procedure, function consist of the following components: • declarative component,
PL/SQL • Block structure • PL/SQL block, procedure, function consist of the following components: • declarative component, • executable component,
PL/SQL • Block structure • PL/SQL block, procedure, function consist of the following components: • declarative component, • executable component, • exception component
PL/SQL • Declarative component
PL/SQL • Declarative component DECLARE -- declarations
PL/SQL • Declarative component DECLARE stock_num NUMBER(5); stock_name VARCHAR(5); dob DATE;
PL/SQL • Executable component BEGIN -- PL/SQL statements
PL/SQL • Executable component BEGIN stock_num := 23098;
PL/SQL • Executable component BEGIN stock_num := 23098; SELECT sname INTO stock_name FROM Inventory WHERE snum = stock_num;
PL/SQL • Executable component BEGIN stock_num := 23098; SELECT sname INTO stock_name FROM Inventory WHERE snum = stock_num; IF stock_name = ‘bolt’ THEN
PL/SQL • Executable component BEGIN stock_num := 23098; SELECT sname INTO stock_name FROM Inventory WHERE snum = stock_num; IF stock_name = ‘bolt’ THEN UPDATE Inventory SET quantity = quantity -1; WHERE snum = stock_num; ELSE IF;
PL/SQL • Exception component
PL/SQL • Exception component EXCEPTION -- exception handlers
PL/SQL • Exception component EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES( emp_id, 'No such number' );
PL/SQL • Exception component EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES( emp_id, 'No such number' ); WHEN OTHERS THEN INSERT INTO emp_error VALUES( sysdate, sqlerrm);
PL/SQL • Block structure DECLARE -- declarations BEGIN -- statements EXCEPTION -- handlers END;
PL/SQL • Procedure structure PROCEDURE name ... -- declarations BEGIN -- statements EXCEPTION -- handlers END name;
PL/SQL • Function structure FUNCTION name ... -- declarations BEGIN -- statements EXCEPTION -- handlers END name;
PL/SQL Example -- This is a comment
PL/SQL Example -- This is a comment DECLARE average NUMBER(5,2);
PL/SQL Example -- This is a comment DECLARE average NUMBER(5,2); BEGIN SELECT avg(salary) INTO average FROM Supplier;
PL/SQL Example -- This is a comment DECLARE average NUMBER(5,2); BEGIN SELECT avg(salary) INTO average FROM Supplier; IF average > 3000 THEN UPDATE Supplier SET salary = salary + 100;
PL/SQL Example -- This is a comment DECLARE average NUMBER(5,2); BEGIN SELECT avg(salary) INTO average FROM Supplier; IF average > 3000 THEN UPDATE Supplier SET salary = salary + 100; ELSE UPDATE Supplier SET salary = salary + 200; END IF; END;
PL/SQL • Declarations
