270 likes | 463 Vues
PL / SQL P rocedural L anguage / S tructured Q uery L anguage. Chapter 7 in Lab Reference. Introduction to PL/SQL. PL/SQL is the procedural extension to SQL. PL/SQL is a programming language like C, Java or Pascal, used to access the database from various environments.
E N D
PL / SQLProcedural Language / Structured Query Language Chapter 7 in Lab Reference
Introduction to PL/SQL • PL/SQL is the procedural extension to SQL. • PL/SQL is a programming language like C, Java or Pascal, used to access the database from various environments. • e.g. Forms, Reports to create triggers, procedures, functions, etc… • SQL-DML can be natively embedded in PL/SQL programs.
Introduction to PL/SQL • PL/SQL provides high-level language features such as block structure, conditional statements, loop statements, variable types, structured data and customized error handling. • PL/SQL is integrated with the database server. It does not exist as a standalone language.
Basic Structure of PL/SQL • The basic unit in PL/SQL is a block. • All PL/SQL programs are made up of blocks, which can be nested within each other.
Basic Structure of PL/SQL DECLARE /* Declarative section: variables, types, and local subprograms. */ BEGIN /* Executable section: procedural and SQL-DML statements go here. */ /* This section of the block is required. */ EXCEPTION /* Exception handling section: error handling statements go here. */ END;
Basic Structure of PL/SQL • Only the executable section is required. The other sections are optional. • The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other data manipulation statements plus some transaction control. • Data definition statements like CREATE, ALTER, or DROP are not allowed.
Basic Structure of PL/SQL • The executable section also contains constructs such as assignments, branches, loops, procedure calls, and triggers. • PL/SQL is not case sensitive. • C style comments (/* ... */) may be used, and for a one line comment precede it with (--).
Variables and Types • Types in PL/SQL are the same as in SQL. • Variables can be declared in the following ways: 1. Declare • id NUMBER; • name VARCHAR(20);
Variables and Types 2. Declare • id emp.empno%TYPE; • name emp.ename%TYPE; • /* TableName.ColumnName%TYPE */ 3. Declare depttupledept%ROWTYPE; /* depttuple will be a record that contains fields that represent the columns in table dept. */ /* depttuple(deptno,dname,location) */
Variables and Types • A variable can be initialized: • A variable can be constrained to not null: Declare a NUMBER :=3; Declare a NUMBER NOT NULL :=3; or Declare a NUMBER NOT NULL DEFAULT 3;
Select Statement • Select statement has a different form: • Select should return a single tuple, if several tuples are required, use a Cursor. SELECT column INTO variables FROM table WHERE condition ;
Simple Program • Using the following table declaration: CREATE TABLE T1( e NUMBER, f NUMBER ); INSERT INTO T1 VALUES ( 1, 3 ); INSERT INTO T1 VALUES ( 2, 4 ); T1
Simple Program DECLARE a NUMBER; b NUMBER; BEGIN SELECT e, f INTO a, b FROM T1 WHERE e>1; INSERT INTO T1 VALUES ( b, a ); END; T1 before T1 after
Control Flow in PL/SQL • IF – THEN – END IF • LOOP – EXIT WHEN – END LOOP • FOR – END LOOP • WHILE – END LOOP
Cursors • Cursor stands for “Current set of records”. • A cursor is a variable that runs through the tuples of some relation. • By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple.
Procedures Syntax • Parameters: variable_name mode data_type • Modes: IN, OUT or INOUT. • Typesshould be unconstrained, i.e. char and varchar should be used instead of char(10) and varchar(20). CREATE [OR REPLACE] PROCEDURE procedure_name (parameters) IS /* No declare keyword */ local_var_declarations BEGIN /* list of statements for procedure body */ END procedure_name;
Procedure Example • Example: • This creates the procedure, to execute it: CREATE PROCEDURE addtuple1( i IN NUMBER, j IN NUMBER ) IS BEGIN INSERT INTO T1 VALUES ( i, j ); END addtuple1; BEGIN addtuple1( 99,100 ); END;
Procedure Example • Example: • This creates the procedure, to execute it: CREATE PROCEDURE addtuple3( a IN NUMBER, b OUT NUMBER ) IS BEGIN b := 4; INSERT INTO T1 VALUES ( a, b ); END addtuple3; DECLARE v NUMBER; BEGIN addtuple3 (10, v ); END;
Functions Syntax CREATE [OR REPLACE] FUNCTION function_name ( parameters ) RETURN return_type IS /* No declare keyword */ Local_var_declarations BEGIN /* list of statements for procedure body */ Return return_variable; END function_name ;
Error Handling • Two types of exceptions: • Predefined exceptions. • User-Definedexceptions. • Predefined exceptions: • INVALID_CURSOR, NO_DATA_FOUND, INVALID_NUMBER, ZERO_DIVIDE,TOO_MANY_ROWS, ROWTYPE_MISMATCH, etc. • Exceptions must be: • Declaredin the declarative section of the block, • Raised in the executable section, • and Handled in the exception section.
User Defined Exceptions • User-defined exceptions are declared in the declarative section. DECLARE exception_nameEXCEPTION; -- exceptions are declared here BEGIN executable_statements-- exceptions are raised here [ EXCEPTION exception_handling ] -- exceptions are handled here END;
User Defined Exceptions DECLARE e_toomany EXCEPTION; -- exception are declared here BEGIN IF num_students > 100 THEN RAISEe_toomany; -- exception is raised here -- Any code here is not executed, if the condition is true EXCEPTION WHEN e_toomany THEN -- Control passes to the exception handler ...-- Code here will be executed END;
Exceptions • When an exception is raised, control immediately passes to the exception section of the block. • Once control passes to the exception handler, there is no way to return to the executable section of the block. • Predefined exceptions are automatically raised when the associated error occurs.
Handling Exceptions …. EXCEPTION WHEN exception_name1 THEN Statements1; -- do something here WHEN exception_name2 THEN Statements2; WHEN OTHERS THEN Statements3; END;
Handling Exceptions • A single handler can also be executed for more than one exception: …. EXCEPTION WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN INSERT INTO log_table (info) VALUES ( 'A select error occurred.‘ ); END;
Triggers • A trigger defines an action the database should take when some database-related eventoccurs (events such as inserts, updates, deletes). • Triggers are similar to procedures, in that they are named PL/SQL blocks. • Differences between Procedures and Triggers: • A procedure is executed explicitly from another block via a procedure call with passing arguments, • while a trigger is executed (or fired) implicitly whenever the triggering event happens (DML: INSERT, UPDATE, or DELETE), and a trigger doesn't accept arguments.
Trigger Example • We create a trigger that may insert a tuple into T5 when a tuple is inserted into T4. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into T5: CREATE TABLE T4 ( a INTEGER, b CHAR(10) ); CREATE TABLE T5 ( c CHAR(10), d INTEGER ); CREATE TRIGGER trig1 AFTER INSERT ON T4 REFERENCING NEW AS newRow FOR EACH ROW WHEN ( newRow.a <= 10 ) BEGIN INSERT INTO T5 VALUES ( :newRow.b, :newRow.a ); END trig1;