[DECLARE]
--declaration statements (optional)
BEGIN
--execution statements
[EXCEPTION]
--exception handling statements (optional)
END;​
CHAR, DATE, LONG, VARCHAR2, NUMBER, BOOLEAN
are some examples of scalar data types.RECORD, TABLE, VARRAY
, and so on.REF, CURSOR
is an example of a reference data type.BLOB, BFILE, CLOB, NCLOB
, and others are examples of large object data types.UPDATE table_name SET field=new_value WHERE CURRENT OF cursor_name
% ROWTYPE
is used when a query returns an entire row of a table or view.TYPE RECORD
, on the other hand, is used when a query returns column of different tables or views.TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)
%ROWTYPE
smpno,dept from smp;
%ROWTYPE
Cursor
is a named private area in SQL
from which information can be accessed. They are required to process each row individually for queries which return multiple rows. %ROWTYPE
as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.FOR smp_rec IN C1 LOOP
totalsal = totalsal + smp_recsal;
ENDLOOP;​
EXCEPTION_INIT
.DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT (exception_name, error_code);
BEGIN
// PL/SQL Logic
EXCEPTION
WHEN exception_name THEN
// Steps to handle exception
END;
COMMIT
command saves changes to a database permanently during the current transaction.ROLLBACK
command is used at the end of a transaction to undo any modifications made since the start of the transaction.SAVEPOINT
command saves the current point with a unique name.PL/SQL
can be adjusted. When an error occurs, the program’s error handling code is included. Trigger
’ means to activate. In PL/SQL
, the trigger is a stored procedure that defines an action taken by the database when the database-related event is performed.DECLARE
num NUMBER(2);
sq NUMBER(3);
BEGIN
num:= &Number1;
sq := num*num;
DBMS_OUTPUT.PUT_LINE(‘Square:’ ||sq);
END;
FUNCTION sqr (num IN NUMBER)
RETURN NUMBER is sq NUMBER(2);
BEGIN
sq:= num*num;
RETURN sq;
END;
Data Definition Language(DDL)
helps in the creation of a database structure or schema. CREATE, DROP, ALTER, RENAME
, and TRUNCATE
are the five types of DDL
commands in SQL
.ata Manipulation Language (DML)
allows you to insert, change, and delete data from a database instance. It’s incharge of making all kinds of changes to a database’s data. The database application and the user can insert data and information using three basic commands which are: INSERT, UPDATE, DELETE
.GRANT
and REVOKE
are commands in the DCL (Data Control Language)
that can be used to grant “rights and permissions.” The database system’s parameters are controlled by other permissions.Transaction Control Language(TCL)
, deal with database transactions. COMMIT, ROLLBACK, SAVEPOINT
are the commands of TCL.Data Query Language (DQL)
. It just has one command, which is SELECTDBMS_APPLICATION_INFO
DBMS_TRACE
DBMS_SESSION
and DBMS_MONITOR
trcsess
and tkprof
utilitiesraise_sal(emp_num, merit+ amount);
emp_num
and amount are the two actual parameters.PROCEDURE raise_sal( emp_id INTEGER) IS
curr_sal REAL:
………..
BEGIN
SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;
…….
END raise_sal;
PL/SQL
raises NO_DATA_FOUND
when there are no rows returned upon a select operation, and if more than one row is returned using a select statement, TOO_MANY_ROWS
error is generated. Some more examples :COLLECTION_IS_NULL
: when a collection is nullCURSOR_ALREADY_OPEN
: When a cursor is already openLOGIN_DENIED
: When login is incorrect or permission is not thereMERGE INTO orders o
USING customer c
ON (o.cust_id = c.cust_id)
WHEN MATCHED THEN
UPDATE SET o.del_address = c.address
WHEN NOT MATCHED THEN
INSERT (cust_id, address)
VALUES (c.emp_id, c.address);
SELECT SYSDATE FROM dual;
SELECT id, TO_CHAR(SYSDATE, 'yyyy/mm/dd') from InterviewBitEmployeeTable where customer_id < 200;
SELECT USER FROM dual;
SYS.ALL_DEPENDENCIES
is used for describing all the dependencies between procedures, packages, triggers, functions that are accessible to the current user. It returns the columns like name, dependency_type, type, referenced_owner
etc. DBMS_OUTPUT
prints the output to the standard console.DBMS_DEBUG
prints the output to the log file.SQL%FOUND
: This returns TRUE if at least one row has been processed.SQL%NOTFOUND
: This returns TRUE if no rows were processed.SQL%ISOPEN
: This checks whether the cursor is open or not and returns TRUE if open.SQL%ROWCOUNT
: This returns the number of rows processed by the DML statement.PL/SQL
:DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE
. PL/SQL
packages have the following two parts :EXECUTE or [EXEC] procedure_name;
procedure_name;
PL/SQL
to manipulate, compare, and calculate data. An expression is made up of two parts : operators and operands.+
’, ‘*
’, etc.SELECT
is parsed.SET SERVER OUTPUT ON
DECLARE
BEGIN
FOR i IN REVERSE 1..99
LOOP
IF Mod(i,3) = 0 THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
/
–
.DECLARE
num NUMBER(2); — it is a local variable.
BEGIN
/* and ends with */
.BEGIN
num := &p_num; /* This is a host variable used in program body */
……….
END
DECLARE
message VARCHAR2(255):= 'Welcome to PL/SQL';
byzero NUMBER;
BEGIN
DBMS_OUTPUT.put_line (message);
byzero := 1/0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
SQLCODE
and SQLERRM
are used to trace exceptions that are not explicitly handled in the program. These are globally defined variables. SQLCODE
returns the error code while SQLERRM
returns the corresponding error message. ACCEPT
keyword to take inputs from the user. Example –ACCEPT
age number prompt ‘Enter your age :
’CREATE PROCEDURE get_customer_details @age nvarchar(30), @city nvarchar(10)
AS
BEGIN
SELECT * FROM customers WHERE age = @age AND city = @city;
END;
NVL (exp1, exp2)
.If exp1 is null then the NVL
function returns the value of exp2.nvl2
returns exp2
, if exp1 is null
, nvl2
returns exp3. The argument exp1
can have any data type. NVL2 (exp1, exp2, exp3)
NULLIF (exp1, exp2)
COALESCE (exp1, exp2… expn)
. The advantage of the COALESCE
function over the NVL
function is that the COALESCE
function can take multiple alternative values.IF-THEN-ELSE
logic within a SQL
statement. Example: CASE
Expression and DECODE
Function. COUNT (*)
: Returns a number of rows in a table including duplicates rows and rows containing null values in any of the columns.COUNT (EXP)
: Returns the number of non-null values in the column identified by expression.COUNT (DISTINCT EXP)
: Returns the number of unique, non-null values in the column identified by expression.SELECT
statement that is embedded in a clause of another SELECT
statement. A subquery can be placed in WHERE HAVING
and FROM
clause.DECODE
and CASE
statements are very similar, but CASE
is extended version of DECODE
. DECODE
does not allow Decision making statements in its place.select decode(totalsal=12000,’high’,10000,’medium’)
as decode_tesr from smp where smpno in (10,12,14,16);
CASE
is directly used in PL SQL
, but DECODE
is used in PL/SQL
through SQL
only.OOP
. It is the ability to create a variable, an object or function with multiple forms. PL/SQL
supports Polymorphism in the form of program unit overloading inside a member function or package..Unambiguous logic must be avoided whilst overloading is being done. pragma_exception_init
command in PL/SQL
instructs the compiler to associate an exception name with an Oracle error number. This enables you to refer to any internal exception by name and create a custom handler for it. DMS_OUTPUT
package. get_line
, put_Line
, new_line, and many more are found in DBMS_OUTPUT
. The put_line
procedure, which is a part of the DBMS_OUPUT
package, is used to display the information in the line. PL/SQL
objects in the application. You can modify the package in one module and then reference those changes to another modulefcopy procedure
”, you can copy the complete contents of one file into another file. While to copy the contents of a file directly into a PL/SQL
table, you can use the program “file2pstab
”. PL/SQL
table are the closest to arrays in PL/SQL
, and in order to access this table you have to first declare a table type, and then you have to declare PL/SQL
table itself. But by using PLVtab
, you can avoid defining your own PL/SQL
table type and make PL/SQL
data-table access easy. two packages
that help you manage transaction processing in PL/SQL application. It is PLVcmt
and PLVrb
.‘1
’ and Maximum size is ‘4000
’1
’and Maximum size is ‘2000
’Scale
” can range from -84 to 127.ON DELETE CASCADE
Indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted. ON DELETE SET NULL
Covert foreign key values to null when the parent value is removed. Without the ON DELETE CASCADE
or the ON DELETE SET NULL
options, the row in the parent table cannot be deleted if it is referenced in the child table.