Assignment Statement
An assignment statement sets the current value of a variable, field, parameter, or element. The statement consists of an assignment target followed by the assignment operator and an expression. When the statement is executed, the expression is evaluated and the resulting value is stored in the target.
Syntax:-
AUTONOMOUS_TRANSACTION Pragma
The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or rollback those operations, without committing or rolling back the data in the main transaction.
Syntax:-
Block Declaration
The basic program unit in PL/SQL is the block. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords partition the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. You can nest a block within another block wherever you can place an executable statement.
Syntax:-
CASE Statement
The CASE statement chooses from a sequence of conditions and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE.
Syntax:-
CLOSE Statement
The CLOSE statement indicates that you are finished fetching from a cursor or cursor variable and that the resources held by the cursor can be reused.
Syntax:-
Collection Definition
A collection is an ordered group of elements, all of the same type. For example, the grades for a class of students. Each element has a unique subscript that determines its position in the collection. PL/SQL offers three kinds of collections: associative arrays, nested tables, and arrays.
Syntax:-
Collection Methods
A collection method is a built-in function or procedure that operates on collections and is called using dot notation. You can use the methods EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, and DELETE to manage collections whose size is unknown or varies.
Syntax:-
Comments
Comments let you include arbitrary text within your code to explain what the code does. PL/SQL supports two comment styles: single-line and multi-line. A double hyphen (–) anywhere on a line (except within a character literal) turns the rest of the line into a comment. Multi-line comments begin with a slash-asterisk (/*) and end with an asterisk-slash (*/).
Syntax:-
COMMIT Statement
The COMMIT statement makes permanent any changes made to the database during the current transaction. A commit also makes the changes visible to other users. The SQL COMMIT statement can be embedded as static SQL in PL/SQL.
Constant and Variable Declaration
You can declare constants and variables in the declarative part of any PL/SQL block, a subprogram, or package. Declarations allocate storage for a value, specify its data type, and specify a name that you can reference. Declarations can also assign an initial value and impose the NOT NULL constraint.
Syntax:-
Cursor Attributes
Every explicit cursor and cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement.
The implicit cursor SQL has additional attributes, %BULK_ROWCOUNT, and %BULK_EXCEPTIONS.
Syntax:-
Cursor Variables
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type.
Syntax:-
If you want to learn SQL in-depth, check out this SQL Tutorial!
Cursor Declaration
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information.
Syntax:-
DELETE Statement
The DELETE statement removes entire rows of data from a specified table or view.
Syntax:-
EXCEPTION_INIT Pragma
The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler.
Syntax:-
Exception Definition
An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers.
Syntax:-
EXECUTE IMMEDIATE Statement
The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or an anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance.
Syntax:-
EXIT Statement
The EXIT statement breaks out of a loop. The EXIT statement has two forms: the unconditional EXIT and the conditional EXIT WHEN.
Syntax:-
Expression Definition
An expression is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression is a single variable.
Syntax:-
FETCH Statement
The FETCH statement retrieves rows of data from the result set of a multi-row query.
Syntax:-
FORALL Statement
The FORALL statement issues a series of static or dynamic DML statements, usually much faster than an equivalent FOR loop. It requires some setup code because each iteration of the loop must use values from one or more collections in its VALUES or WHERE clauses.
Learn from our SQL Server Interview Questions to crack top companies Interview
Function Declaration
A function is a subprogram that can take parameters and return a single value. A function has two parts: the specification and the body. The specification begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the data type of the return value. Parameter declarations are optional.
Functions that take no parameters are written without parentheses. The function body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional function name. The function body has three parts: an optional declarative part, an executable part, and an optional exception-handling part.
Syntax:-
GOTO Statement
The GOTO statement branches unconditionally to a statement label or block label. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. The GOTO statement transfers control to the labeled statement or block.
IF Statement
The IF statement executes or skips a sequence of statements, depending on the value of a Boolean expression.
INSERT Statement
The INSERT statement adds one or more new rows of data to a database table.
Literal Declaration
A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. The numeric literal 135 and the string literal ‘hello world’ are examples.
Syntax:-
LOCK TABLE Statement
The LOCK TABLE statement locks entire database tables in a specified lock mode. That enables you to share or deny access to tables while maintaining their integrity.
LOOP Statements
LOOP statements execute a sequence of statements multiple times. The LOOP and END LOOP keywords enclose the statements. PL/SQL provides four kinds of loop statements: basic loop, WHILE loop, FOR loop, and cursor FOR loop.
Syntax:-
MERGE Statement
The MERGE statement inserts some rows and updates others in a single operation. The decision about whether to update or insert into the target table is based upon a join condition: rows already in the target table that match the join condition are updated; otherwise, a row is inserted using values from a separate subquery.
NULL Statement
The NULL statement is a no-op (no operation); it passes control to the next statement without doing anything. In the body of an IF-THEN clause, a loop, or a procedure, the NULL statement serves as a placeholder.
Object Type Declaration
An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods. A special kind of method called the constructor creates a new instance of the object type and fills in its attributes.
Object types must be created through SQL and stored in an Oracle database, where they can be shared by many programs. When you define an object type using the CREATE TYPE statement, you create an abstract template for some real-world objects. The template specifies the attributes and behaviors the object needs in the application environment.
OPEN Statement
The OPEN statement executes the query associated with a cursor. It allocates database resources to process the query and identifies the result set — the rows that match the query conditions. The cursor is positioned before the first row in the result set.
Syntax:-
OPEN-FOR Statement
The OPEN-FOR statement executes the query associated with a cursor variable. It allocates database resources to process the query and identifies the result set – the rows that meet the query conditions. The cursor variable is positioned before the first row in the result set.
Package Declaration
A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Use packages when writing a set of related subprograms that form an application programming interface (API) that you or others might reuse. Packages have two parts: a specification and a body.
Procedure Declaration
A procedure is a subprogram that can take parameters and be called. Generally, you use a procedure to perform an action. A procedure has two parts: the specification and the body. The specification (spec for short) begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional.
Procedures that take no parameters are written without parentheses. The procedure body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: an optional declarative part, an executable part, and an optional exception-handling part.
The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the procedure. The executable part contains
statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution.
RAISE Statement
The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler. RAISE statements can raise predefined exceptions, such as ZERO_DIVIDE or NO_DATA_FOUND, or user-defined exceptions whose names you decide.
Syntax:-
Record Definition
Records are composite variables that can store data values of different types, similar to a struct type in C, C++, or Java. In PL/SQL records are useful for holding data from table rows, or certain columns from table rows. For ease of maintenance, you can declare variables as table%ROWTYPE or cursor%ROWTYPE instead of creating new record types.
RETURN Statement
The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Execution resumes with the statement following the subprogram call. In a function, the RETURN statement also sets the function identifier to the return value.
Syntax:-
RETURNING INTO Clause
The returning clause specifies the values return from DELETE, EXECUTE IMMEDIATE, INSERT, and UPDATE statements. You can retrieve the column values into individual variables or into collections. You cannot use the RETURNING clause for remote or parallel deletes. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined.
ROLLBACK Statement
The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction.
%ROWTYPE Attribute
The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Variables declared using %ROWTYPE are treated like those declared using a datatype name. You can use the %ROWTYPE attribute in variable declarations as a data type specifier.
SAVEPOINT Statement
The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACK TO statement, savepoints undo parts of a transaction instead of the whole transaction.
SELECT INTO Statement
The SELECT INTO statement retrieves data from one or more database tables and assigns the selected values to variables or collections.
In its default usage (SELECT … INTO), this statement retrieves one or more columns from a single row. In its bulk usage (SELECT … BULK COLLECT INTO), this statement retrieves an entire result set at once.
Syntax:-
SERIALLY_REUSABLE Pragma
The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server. An example could be an OCI call to the database or a stored procedure call through a database link. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.
SET TRANSACTION Statement
The SET TRANSACTION statement begins a read-only or read-write transaction, establishes an isolation level, or assigns the current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables.
SQL Cursor
Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They provide information about the execution of data manipulation
statements. The SQL cursor has additional attributes, %BULK_ROWCOUNT, and %BULK_EXCEPTIONS, designed for use with the FORALL statement.
SQLCODE Function
The function SQLCODE returns the number code of the most recent exception. For internal exceptions, SQLCODE returns the number of the associated Oracle error.
The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100. For user-defined exceptions, SQLCODE returns +1, or a value you assign if the exception is associated with an Oracle error number through pragma EXCEPTION_INIT.
Wish to crack PL/SQL interviews? Intellipaat’s Top PL/SQL Interview Questions are meant only for you!
SQLERRM Function
The function SQLERRM returns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODE. SQLERRM with no argument is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the normal, successful completion message. For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code.
For user-defined exceptions, SQLERRM returns the message user-defined exception, unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLERRM returns the corresponding error message.
Syntax:-
%TYPE Attribute
The %TYPE attribute lets use the datatype of a field, record, nested table, database column, or variable in your own declarations, rather than hardcoding the type names.
You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. If the types that you reference change, your declarations are automatically updated. This technique saves you from making code changes when, for example, the length of a VARCHAR2 column is increased.
UPDATE Statement
The UPDATE statement changes the values of specified columns in one or more rows in a table or view.
Syntax:-
