PL/SQL is Oracle's procedural language extension to SQL, which allows combining SQL with procedural logic for complex data manipulation and control.
A PL/SQL block is a group of related declarations, executable statements, and exception-handling constructs.
A PL/SQL block has three parts: Declaration (optional), Execution (mandatory), and Exception Handling (optional).
An anonymous block does not have a name and is executed only once, while a named block (like procedures and functions) can be reused.
%TYPE is used to declare a variable based on a column data type, and %ROWTYPE is used to declare a record variable that matches a row structure in a table.
A cursor is a pointer that holds the result set of a SQL query and allows row-by-row processing.
The two types are Implicit cursors (automatically managed by PL/SQL) and Explicit cursors (declared and controlled by the user).
A trigger is a stored procedure that automatically executes in response to specific events on a table or view.
A function returns a single value, while a procedure does not return a value and is mainly used to perform actions.
Exceptions are handled using the EXCEPTION section of a PL/SQL block. You can use pre-defined exceptions like NO_DATA_FOUND or define custom ones.
A package is a collection of related procedures, functions, variables, and other PL/SQL types grouped together as a single unit.
pragma EXCEPTION_INIT associates an exception name with an Oracle error number, enabling specific error handling.
The different types are BEFORE, AFTER, INSTEAD OF, and COMPOUND triggers.
Optimization can be achieved through techniques like using bulk operations (BULK COLLECT and FORALL), minimizing context switches between SQL and PL/SQL, and using native compilation.
SAVEPOINT allows partial rollbacks to a specific point within a transaction without rolling back the entire transaction.
COMMIT saves changes made in the transaction permanently, while ROLLBACK undoes all changes made in the current transaction.
Collections are composite data types like VARRAY, NESTED TABLE, and ASSOCIATIVE ARRAY that can hold multiple elements of the same type.
DELETE removes rows one by one and can be rolled back, while TRUNCATE is faster and removes all rows but cannot be rolled back.
ROWID is a unique identifier for a row in a table, while ROWNUM is a temporary sequential number assigned to a row during query execution.
Bulk binding is a method of processing multiple rows in a single context switch using BULK COLLECT or FORALL , improving performance
