#Transactions:A database transaction is an atomic unit of work that may consist of one or more related SQL statements.
A successfully executed SQL statement and a committed transaction are not same. Even if an SQL statement is executed successfully, unless the transaction containing the statement is committed, it can be rolled back and all changes made by the statement(s) can be undone.
Committing a Transaction-A transaction is made permanent by issuing the SQL command COMMIT.
Rolling Back Transactions-Changes made to the database without COMMIT could be undone using the ROLLBACK command.
The general syntax for the ROLLBACK command is:
ROLLBACK [TO SAVEPOINT < savepoint_name>];
When a transaction is aborted due to some unprecedented situation, like system failure, the entire transaction since a commit is automatically rolled back. If you are not using savepoint, then simply use the following statement to rollback all the changes:
SAVEPOINT:
Savepoints-Savepoints are sort of markers that help in splitting a long transaction into smaller units by setting some checkpoints. By setting savepoints within a long transaction, you can roll back to a checkpoint if required. This is done by issuing the SAVEPOINT command.
The general syntax for the SAVEPOINT command is:
SAVEPOINT < savepoint_name >;
Automatic Transaction Control-To execute a COMMIT automatically whenever an INSERT, UPDATE or DELETE command is executed, you can set the AUTOCOMMIT environment
variable as:
SET AUTOCOMMIT ON;
You can turn-off auto commit mode using the following command:
SET AUTOCOMMIT OFF;
#Daate&Time:PL/SQL provides two classes of date and time related data types:
Datetime data types
Interval data types
The Datetime data types are:
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
The Interval data types are:
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
#DBMS Output:The DBMS_OUTPUT is a built-in package that enables you to display output, display debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers. We have already used this package all throughout our tutorial.
DBMS_OUTPUT Subprograms:
The DBMS_OUTPUT package has the following subprograms:
DBMS_OUTPUT.DISABLE- Disables message output
DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000)-Enables message output. A NULL value of buffer_size represents unlimited buffer size.
DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER)-Retrieves a single line of buffered information.
DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER)-Retrieves an array of lines from the buffer.
DBMS_OUTPUT.NEW_LINE-Puts an end-of-line marker
DBMS_OUTPUT.PUT(item IN VARCHAR2)-Places a partial line in the buffer.
DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2)-Places a line in the buffer.
A successfully executed SQL statement and a committed transaction are not same. Even if an SQL statement is executed successfully, unless the transaction containing the statement is committed, it can be rolled back and all changes made by the statement(s) can be undone.
Committing a Transaction-A transaction is made permanent by issuing the SQL command COMMIT.
Rolling Back Transactions-Changes made to the database without COMMIT could be undone using the ROLLBACK command.
The general syntax for the ROLLBACK command is:
ROLLBACK [TO SAVEPOINT < savepoint_name>];
When a transaction is aborted due to some unprecedented situation, like system failure, the entire transaction since a commit is automatically rolled back. If you are not using savepoint, then simply use the following statement to rollback all the changes:
SAVEPOINT:
Savepoints-Savepoints are sort of markers that help in splitting a long transaction into smaller units by setting some checkpoints. By setting savepoints within a long transaction, you can roll back to a checkpoint if required. This is done by issuing the SAVEPOINT command.
The general syntax for the SAVEPOINT command is:
SAVEPOINT < savepoint_name >;
Automatic Transaction Control-To execute a COMMIT automatically whenever an INSERT, UPDATE or DELETE command is executed, you can set the AUTOCOMMIT environment
variable as:
SET AUTOCOMMIT ON;
You can turn-off auto commit mode using the following command:
SET AUTOCOMMIT OFF;
#Daate&Time:PL/SQL provides two classes of date and time related data types:
Datetime data types
Interval data types
The Datetime data types are:
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
The Interval data types are:
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
#DBMS Output:The DBMS_OUTPUT is a built-in package that enables you to display output, display debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers. We have already used this package all throughout our tutorial.
DBMS_OUTPUT Subprograms:
The DBMS_OUTPUT package has the following subprograms:
DBMS_OUTPUT.DISABLE- Disables message output
DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000)-Enables message output. A NULL value of buffer_size represents unlimited buffer size.
DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER)-Retrieves a single line of buffered information.
DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER)-Retrieves an array of lines from the buffer.
DBMS_OUTPUT.NEW_LINE-Puts an end-of-line marker
DBMS_OUTPUT.PUT(item IN VARCHAR2)-Places a partial line in the buffer.
DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2)-Places a line in the buffer.
No comments:
Post a Comment