What is PL/SQL?
PL/SQL stands for Procedural Language extension of SQL. It was developed by Oracle Corporation in the late 1980s to enhance the capabilities of SQL. It is the procedural extension language for SQL.
See more at: PLSQL overview.
Explain PL/SQL Block sections.
1. Declaration section (optional).
2. Execution section (mandatory).
3. Exception handling section (optional).
See more at: PLSQL overview.
Explain Pl sql stored procedure.
The pl sql stored procedure is a named PL/SQL block which performs one or more specific tasks. A pl sql stored procedure can be divided into two parts: Header and Body part.
See more at: Plsql stored procedure.
How to create a procedure?
Procedure example without parameters:
CREATE OR REPLACE PROCEDURE hello_world AS BEGIN dbms_output.put_line('Hello World!'); END; / |
See more at: Plsql stored procedure.
How to execute stored procedure?
A procedure can be executed by using EXEC or EXECUTE statement.
EXEC procedure_name();
EXEC procedure_name;
See more at: Plsql stored procedure.
How to drop stored procedure?
DROP PROCEDURE procedure_name;
See more at: Plsql stored procedure.
Explain Pl sql function.
The pl sql function is a named PL/SQL block which performs one or more specific tasks and must returns a value.
See more at: Plsql function.
How to create a function?
create or replace function getMultiple(num1 in number, num2 in number) return number is num3 number(8); begin num3 :=num1*num2; return num3; end; / |
See more at: Plsql function.
How to execute a function?
A functions return value can be assign to a variable.
result := getMultiple(4, 5);
As a part of a SELECT statement:
SELECT getMultiple(4, 5) FROM dual;
In a PL/SQL Statement:
dbms_output.put_line(getMultiple(4, 5));
See more at: Plsql function.
How to drop a function?
DROP FUNCTION function_name;
See more at: Plsql function.
What is Context area?
When processing an SQL statement, Oracle creates a temporary work area in the system memory which contains all the information needed for processing the statement known as context area.
See more at: Plsql cursor.
What is Cursor?
A cursor is a pointer to context area i.e. Context area is controlled by the cursor. It is used to fetch and manipulate the data returned by the SQL statement.
Note: The set of rows the cursor holds is known as active set.
See more at: Plsql cursor.
What is Implicit cursors?
Implicit cursors are automatically generated by Oracle while processing an SQL statement when no explicit cursor for the statement is used. They are created by default when DML statements like DELETE, INSERT, UPDATE and SELECT are executed.
See more at: Plsql cursor.
What is Explicit cursors?
Explicit cursors are the user defined cursors to gain more control over the context area. These are defined in the declaration section of the PL/SQL block. An explicit cursor is created on a SELECT Statement which returns more than one row.
See more at: Plsql cursor.
How to use explicit cursor?
1. DECLARE the cursor for initialization in the declaration section.
2. OPEN the cursor for memory allocation in the execution section.
3. FETCH the cursor for retrieving data in the execution section.
4. CLOSE the cursor to release allocated memory in the execution section.
See more at: Plsql cursor.
How to declare a cursor?
CURSOR cur_students IS
SELECT rollNo, name, address FROM students; |
See more at: Plsql cursor.
How to fetch a cursor?
FETCH cur_students INTO s_rollNo, s_name, s_address; |
See more at: Plsql cursor.
How to close a cursor?
CLOSE cur_students; |
See more at: Plsql cursor.
Explain Oracle pl sql triggers.
A database trigger is a stored program which is automatically fired or executed when some events occur. A trigger can execute in response to any of the following events:
1. A database manipulation (DML) statement like DELETE, INSERT or UPDATE.
2. A database definition (DDL) statement like CREATE, ALTER or DROP.
3. A database operation like SERVERERROR, LOGON, LOGOFF, STARTUP, or
See more at: Plsql triggers.
What is the maximum number of triggers, you can apply on a single table?
12 triggers.