1 / 27

PL / SQL P rocedural L anguage / S tructured Q uery L anguage

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.

ketan
Download Presentation

PL / SQL P rocedural L anguage / S tructured Q uery L anguage

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PL / SQLProcedural Language / Structured Query Language Chapter 7 in Lab Reference

  2. 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.

  3. 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.

  4. 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.

  5. 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;

  6. 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.

  7. 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 (--).

  8. 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);

  9. 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) */

  10. 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;

  11. 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 ;

  12. 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

  13. 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

  14. Control Flow in PL/SQL • IF – THEN – END IF • LOOP – EXIT WHEN – END LOOP • FOR – END LOOP • WHILE – END LOOP

  15. 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.

  16. 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;

  17. 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;

  18. 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;

  19. 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 ;

  20. 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.

  21. 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;

  22. 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;

  23. 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.

  24. Handling Exceptions …. EXCEPTION WHEN exception_name1 THEN Statements1; -- do something here WHEN exception_name2 THEN Statements2; WHEN OTHERS THEN Statements3; END;

  25. 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;

  26. 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.

  27. 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;

More Related