Oracle Database PLSQL 12c Certification Training

PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.Master PL/SQL, in this PL/SQL certification training.

• A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
• Audit data modifications.
• Log events transparently.
• Enforce complex business rules.
• Maintain replica tables
• Derive column values
• Implement Complex security authorizations
• Any of the constant, variable or parameter has a data type depending on which the storage constraints, format and range of values and operations are determined.

• A PL/SQL package consists of
• PL/SQL table and record TYPE statements
• Procedures and Functions
• Cursors
• Variables ( tables, scalars, records, etc.) and constants
• Exception names and pragmas for relating an error number with an exception
• Cursors

• It provides several benefits like
• Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public
• Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves
• Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module
• Object oriented design: The package gives developers strong hold over how the modules and data structures inside the package can be used
• Guaranteeing transaction integrity: It provides a level of transaction integrity
• Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.

• Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes
• DBMS_APPLICATION_INFO
• DBMS_TRACE
• DBMS_SESSION and DBMS_MONITOR
• trcsess and tkproof utilities

• Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
• Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.
• Package: A package is schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.

• A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as schema object. It can be nested, invoked and parameterized.
• Learn more about PL/SQL Collections & Records.

A cursor is a temporary work area created in a system memory when an SQL statement is executed.A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data retrieved from the database and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. Cursor are required to process rows individually for queries.