Explore topic-wise InterviewSolutions in .

• WHEN CLAUSE SPECIFIES for what CONDITION the TRIGGER has to be TRIGGERED.

• Comments are those SENTENCES that have no EFFECT on the functionality and are USED for the purpose of enhancing the readability of the code. They are of two types:
  • Single Line COMMENT: This can be created by using the symbol -- and writing what we want to mention as a comment next to it.
  • Multi-Line comment: These are the comments that can be specified over MULTIPLE lines and the syntax goes like /* comment information */
• Example:

• This statement is used by ANONYMOUS BLOCKS of PL/SQL such as non-stored and stand-alone PROCEDURES. When they are being used, the statement should come first in the stand-alone FILE.

• As the name indicates, ‘Trigger’ means to ‘activate’ something. In the case of PL/SQL, a trigger is a stored procedure that SPECIFIES what action has to be taken by the database when an event related to the database is performed.

• Syntax:

In the above syntax, if the trigger_name the trigger is in the enabled state, the trigger_event causes the database to FIRE actions_of_trigger if the restrictions are TRUE or unavailable.

• They are mainly used in the following scenarios:
  • In order to maintain COMPLEX integrity constraints.
  • For the purpose of auditing any table information.
  • Whenever changes are done to a table, if we need to signal other actions UPON completion of the change, then we use triggers.
  • In order to enforce complex rules of business.
  • It can also be used to prevent invalid transactions.
• You can refer https://docs.oracle.com/database/121/TDDDG/tdddg_triggers.htm for more information regarding triggers.

• This can be done by using PRAGMA called EXCEPTION_INIT.
• This gives the flexibility to the programmer to instruct the compiler to provide custom error messages based on the BUSINESS LOGIC by overriding the pre-defined messages during the COMPILATION time.
• Syntax:

• We use this clause while referencing the CURRENT row from an explicit cursor. This clause ALLOWS applying updates and DELETION of the row currently under CONSIDERATION without explicitly referencing the row ID.
• Syntax:
  UPDATE table_name SET field=new_value WHERE CURRENT OF cursor_name

• A PL/SQL cursor is nothing but a pointer to an area of memory having SQL statements and the information of statement processing. This memory area is called a context area. This special area makes use of a special feature called cursor for the purpose of retrieving and processing more than one row.
• In short, the cursor selects multiple ROWS from the database and these selected rows are individually processed within a program.
• There are two types of cursors:
  • Implicit Cursor:
    • Oracle automatically creates a cursor while running any of the commands - SELECT INTO, INSERT, DELETE or UPDATE implicitly.
    • The execution cycle of these cursors is internally handled by Oracle and RETURNS the information and status of the cursor by MAKING use of the cursor attributes- ROWCOUNT, ISOPEN, FOUND, NOTFOUND.
  • Explicit Cursor:
    • This cursor is a SELECT statement that was declared explicitly in the declaration block.
    • The programmer has to control the execution cycle of these cursors starting from OPEN to FETCH and close.
    • The execution cycle while executing the SQL statement is defined by Oracle along with associating a cursor with it.
• Explicit Cursor Execution Cycle:
  • Due to the flexibility of defining our own execution cycle, explicit cursors are used in many instances. The following diagram represents the execution flow of an explicit cursor:

• Cursor Declaration:
  • The first step to use an explicit cursor is its declaration.
  • Declaration can be done in a package or a block.
  • Syntax: CURSOR cursor_name IS query; where cursor_name is the name of the cursor, the query is the query to fetch data from any table.
• Open Cursor:
  • Before the process of fetching rows from cursor, the cursor has to be opened.
  • Syntax to open a cursor: OPEN cursor_name;
  • When the cursor is opened, the query and the bind variables are parsed by Oracle and the SQL statements are executed.
  • The execution plan is determined by Oracle and the result set is determined after associating the cursor parameters and host variables and post these, the cursor is set to point at the first row of the result set.
• Fetch from cursor:
  • FETCH statement is used to place the content of the CURRENT row into variables.
  • Syntax: FETCH cursor_name INTO variable_list;
  • In order to get all the rows of a result set, each row needs to be fetched.
• Close Cursor:
  • Once all the rows are fetched, the cursor needs to be closed using the CLOSE statement.
  • Syntax: CLOSE cursor_name;
  • The instructions tell Oracle to release the memory allocated to the cursor.
    • Cursors declared in procedures or anonymous blocks are by default closed post their execution.
    • Cursors declared in packages need to be closed explicitly as the scope is global.
    • Closing a cursor that is not opened will result in INVALID_CURSOR exception.

• The basic structure of PL/SQL follows the BLOCK structure. Each PL/SQL code comprises SQL and PL/SQL statement that CONSTITUTES a PL/SQL block.
• Each PL/SQL block consists of 3 sections:
  • The optional Declaration Section
  • The mandatory EXECUTION Section
  • The optional Exception handling Section

• PL/SQL TABLES are nothing but OBJECTS of type tables that are modeled as database tables. They are a way to PROVIDE ARRAYS that are nothing but temporary tables in memory for faster processing.
• These tables are useful for moving bulk DATA thereby simplifying the process.

Following are the features of PL/SQL:

• PL/SQL provides the feature of decision making, looping, and BRANCHING by making use of its procedural nature.
• Multiple queries can be processed in one block by making use of a single command using PL/SQL.
• The PL/SQL code can be reused by applications as they can be grouped and stored in databases as PL/SQL units like FUNCTIONS, procedures, packages, triggers, and types.
• PL/SQL supports exception handling by making use of an exception handling block.
• Along with exception handling, PL/SQL also supports error CHECKING and validation of data before data manipulation.
• Applications developed using PL/SQL are PORTABLE across computer hardware or operating system where there is an ORACLE engine.