What is PL/SQL?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
Compare SQL & PL/SQL.
| Criteria | SQL | PL/SQL |
| What it is | Single query or command execution | Full programming language |
| What it comprises | Data source for reports, web pages | Application language to build, format and display report,
web pages |
| Characteristic | Declarative in nature | Procedural in nature |
| Used for | Manipulating data | Creating applications |
What are local and global variables and their differences?
Local variables are the variables which can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred or used. Variables can be created whenever that function is called.
Global variables are the variables which can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.
What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?
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.
Advantages and Disadvantages of Stored Procedure?
Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.
Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.
Define Implicit and Explicit Cursors?
A cursor is implicit by default. The user cannot control or process the information in this cursor.
If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.
Show the cursor attributes of PL/SQL.
%ISOPEN : Checks if the cursor is open or not
%ROWCOUNT : The number of rows that are updated, deleted or fetched.
%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched
%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.
What is PL/SQL?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
Explain the uses of database trigger?
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
What is the basic structure of PL/SQL?
PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statement which form a PL/SQL block.
PL/SQL block contains 3 sections.
The Declaration Section (optional)
The Execution Section (mandatory)
The Exception handling Section (optional)Mention
what PL/SQL package consists of?
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
What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT.
What are the datatypes available in PL/SQL?
There are two types of datatypes in PL/SQL:
Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
Composite datatypes Example are RECORD, TABLE etc.
Differentiate between Syntax and runtime errors.
A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.
A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.
What packages are available to PL SQL developers?
DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.
What is the purpose of the optional argument [OR REPLACE] in a CREATE TRIGGER command?
The optional argument [OR REPLACE] in a CREATE TRIGGER command re-creates an existing trigger. Using this option allows changing the definition of an existing trigger without having to delete it first.
How many types of triggers exist in PL/SQL?
There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.
Are you Looking for Oracle PLSQL Online Training? Please Enroll for Demo Oracle PLSQL..! |
How do you declare a user-defined exception?
User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION.
Syntax −
<exception_name> EXCEPTION;
How do you refer to the types, objects and subprograms declared within a package?
The types, objects, and subprograms declared within a package are referred to using the dot notation as −
package_name.type_name
package_name.object_name
package_name.subprogram_name
What is consistency?
Consistency simply means that each user sees the consistent view of the data.
Consider an example: there are two users A and B. A transfers money to B’s account. Here the changes are updated in A’s account (debit) but until it will be updated to B’s account (credit), till then other users can’t see the debit of A’s account. After the debit of A and credit of B, one can see the updates.