- What is PL/SQL?
- PL/SQL is a procedural language designed specifically for the Oracle Database Management System to handle the database operations.
- Explain the architecture of PL/SQL.
- PL/SQL consists of a block structure, which can contain multiple statements. It has a declarative part, an executable part, and an exception-handling part.
- What are the benefits of using PL/SQL?
- Benefits include improved performance, scalability, security, maintainability, and reusability of code.
- Explain the difference between a procedure and a function.
- A procedure does not return a value, while a function returns a value.
- What is a cursor in PL/SQL?
- A cursor is a pointer to the context area, which stores the information of the last executed statement.
- How do you declare a cursor?
- You declare a cursor using the “CURSOR” keyword, followed by the SELECT statement.
- How do you handle exceptions in PL/SQL?
- Exceptions are handled in PL/SQL using the “EXCEPTION” keyword and the “WHEN” clause.
- What is a trigger in PL/SQL?
- A trigger is a named PL/SQL block that is automatically executed, or fired, when a specified event occurs.
- Explain the difference between a database trigger and a system trigger.
- A database trigger is fired in response to a DML statement, while a system trigger is fired in response to database-level events such as startup or shutdown.
- How do you use the “SAVEPOINT” and “ROLLBACK” statement in PL/SQL?
- The “SAVEPOINT” statement is used to mark a point in a transaction to which you can later roll back. The “ROLLBACK” statement is used to undo changes made during the transaction.
- What is a PL/SQL block?
- A PL/SQL block is a collection of statements grouped together to perform a specific task.
- What is an anonymous block in PL/SQL?
- An anonymous block is an unnamed PL/SQL block that does not have a name and can be used for simple, one-time tasks.
- Explain the use of the “IF-THEN” statement in PL/SQL.
- The “IF-THEN” statement is used to conditionally execute a statement based on a Boolean expression.
- What is the purpose of the “FOR LOOP” in PL/SQL?
- The “FOR LOOP” is used to repeatedly execute a block of code a specified number of times.
- What is the “WHILE LOOP” in PL/SQL used for?
- The “WHILE LOOP” is used to repeatedly execute a block of code as long as a specified condition is true.
- What is a PL/SQL record?
- A PL/SQL record is a composite data structure that groups together related data items, similar to a row in a database table.
- What is a PL/SQL table?
- A PL/SQL table is an indexed, one-dimensional collection of data that can be used to store and manipulate data within PL/SQL.
- How do you declare a PL/SQL table?
- You can declare a PL/SQL table using the “TYPE” keyword, followed by the table name and the “TABLE” keyword.
- What is the difference between a PL/SQL table and an associative array?
- A PL/SQL table is an indexed, one-dimensional collection of data, while an associative array is a composite data structure that can be used to store key-value pairs.
- What is the “BULK COLLECT” clause used for in PL/SQL?
- The “BULK COLLECT” clause is used to fetch large amounts of data from a database into PL/SQL collections.
- What is a PL/SQL function?
- A PL/SQL function is a named PL/SQL block that returns a value and can be called from other PL/SQL blocks or from SQL statements.
- What is a PL/SQL package?
- A PL/SQL package is a collection of related PL/SQL objects, including procedures, functions, and variables, that are stored together in the database.
- What is the purpose of the “EXIT WHEN” statement in PL/SQL?
- The “EXIT WHEN” statement is used to exit a loop when a specified condition is met.
- What is the “NULL” value in PL/SQL?
- The “NULL” value in PL/SQL is a marker used to indicate that data does not exist in a particular column.
- What is the “OPEN-FOR” statement used for in PL/SQL?
- The “OPEN-FOR” statement is used to open a cursor and associate it with a SELECT statement.
- What is the “FETCH” statement used for in PL/SQL?
- The “FETCH” statement is used to retrieve a row from a cursor into PL/SQL variables.
- What is the “CLOSE” statement used for in PL/SQL?
- The “CLOSE” statement is used to close a cursor and release any resources associated with it.
- What is a PL/SQL exception?
- A PL/SQL exception is an error condition that occurs during the execution of a PL/SQL block.
- What is a stored procedure in PL/SQL?
- A stored procedure in PL/SQL is a pre-compiled, reusable code block that can be called from other PL/SQL blocks or from SQL statements.