Oracle pl sql package: A package is a schema object that groups logically related PL/SQL types, variables and subprograms. Parts of a package: 1. Package specification 2. Package body or definition Package specification: The package specification is the package interface which declares the types, variables, constants, exceptions, cursors and subprograms that can be referenced from … Read more
Oracle pl sql triggers: A database trigger is a stored program which is automatically fired or executed when some events occur. A trigger can execute in response to any of the following events: 1. A database manipulation (DML) statement like DELETE, INSERT or UPDATE. 2. A database definition (DDL) statement like CREATE, ALTER or DROP. … Read more
Exception: Exception refers to an exceptional event. Exception is an event that disrupts the normal flow of the program, during program execution. Pl sql exception handling: PL/SQL provides a mechanism to handle such exceptions so that normal flow of the program can be maintained. Types of exceptions: 1. System-defined exceptions. 2. User-defined exceptions. Syntax for … Read more
Context area: When processing an SQL statement, Oracle creates a temporary work area in the system memory which contains all the information needed for processing the statement known as context area. Cursor: A cursor is a pointer to context area i.e. Context area is controlled by the cursor. It is used to fetch and manipulate … Read more
Pl sql function: The pl sql function is a named PL/SQL block which performs one or more specific tasks and must returns a value. How to pass parameter in a function? We can use the below modes to pass the parameters in a function: IN-parameters: These parameters are the read-only parameters. Function cannot change the … Read more
Pl sql stored procedure: The pl sql stored procedure is a named PL/SQL block which performs one or more specific tasks. A pl sql stored procedure can be divided into two parts: Header and Body part. Header: The header part contains the name of the procedure and the parameters passed to the procedure. Body: The … Read more
Pl sql goto statement: The pl sql goto statement provides an unconditional jump from the GOTO to a labeled statement in the same subprogram. A label can be declare with the > syntax. pl sql goto statement syntax: GOTO label_name; //Other statements <<label_name>> Statement;GOTO label_name; //Other statements <<label_name>> Statement; Pl sql goto statement example: DECLARE … Read more
Pl sql loop label: The pl sql loop can be labeled by loop labels. Pl sql loop label syntax: << label >><< label >> Pl sql loop label example: DECLARE i number(1); j number(1); BEGIN << outer_loop >> FOR i IN 1..5 LOOP << inner_loop >> FOR j IN 1..5 LOOP dbms_output.put_line(’i is: ‘|| i … Read more
Pl sql continue statement: The pl sql continue statement is a control statement which is used to skip the following statement in the body of the loop and continue with the next iteration of the loop. Pl sql continue syntax: continue;continue; Pl sql continue statement example: DECLARE num NUMBER := 0; BEGIN WHILE num < … Read more
Pl sql for in loop: The pl sql for in loop repeatedly executes a block of statements for a fixed number of times. The loop iteration occurs between the start and end integer values. The counter is always incremented by 1 and loop terminates when the counter reaches the value of the end integer. Pl … Read more