Oracle Database (3-7)

There are four main steps to using an explicit cursor:

declaring/Defining Cursors
Open cursor
Reading data
Close Cursors

Case conditional expression

When conditional expression result 1 Then
Statement 1
When conditional expression Result 2 Then
Statement 2
......
When conditional expression Results n Then
Statement n
[ELSE conditional expression result]
END;

A package is a data object that is a group of related procedures, functions, variables, constants, and cursors such as PL/SQL programming elements that are stored in the database as a complete unit and identified by name.

Benefits of the Package

1. Modular: using packages, you can encapsulate related types, objects, and subroutines. A large functional module is divided into a number of small functional modules, respectively, to complete their respective functions, so that the organization of the program is easy to write, understand and manage.
2. Easier application design: The Package Specification section and the Package body section can be created and compiled separately. In other words, we can write the code of the package specification and compile it without writing the package body.
3. Information hiding: The elements in a package can be divided into public and private elements, public elements can be accessed by procedures, functions, etc. within the package, and can be accessed by PL/SQL outside the package. However, private elements can only be accessed by procedures, functions, and so on inside the package. For the user, only need to know the package specification, do not need to understand the package body details.
4. Better performance: The first time an application invokes an element in the package, the entire package is loaded into memory, and when the element in the package is accessed for the second time, Oracle will read directly from within without requiring disk i/ O operations that affect speed, while in-memory packages can be shared by other applications during the same session. As a result, the package adds reusability and improves the efficiency of multi-user, multi-application environments.

3. definition of package

The package in PL/SQL consists of the package specification and the package body two parts. When you build a package, you first set up the package specification and then build the implementation of the package specification – the package body.

Item_list_1: Declaring a list of common components for a package

{type_definition--data type

| Cursor_declaration--Cursors

| Item_declaration--variables, constants, etc.

| Function_declaration--function

| Procedure_declaration--Process

}

1. stored Procedure Concepts

A stored procedure (Stored Procedure) is a set of SQL statements that are stored in a database in a large database system in order to accomplish a particular function. After the first compilation, the call does not need to be compiled again, and the user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters).

2. Transaction Characteristics

The SQL92 standard defines four characteristics of database transactions:

• Atomicity (atomicity): All the SQL statements contained within a transaction are performed as a whole, indivisible, either done, or not.
• Consistency (consistency): At the beginning of a transaction, the data in the database is consistent, and the data of the database should be consistent at the end of the transaction.
• Isolation (Isolation): refers to the ability of a database to read and write and modify data at the same time for multiple concurrent transactions, which prevents inconsistent state of data due to the cross-execution of their operations commands when the concurrent execution of a transaction occurs.
• Persistence (Durability): When a transaction ends, its impact on the database is permanent, and the data is not lost even if the system encounters a failure.

For a set of SQL statement operations to become transactions, the database management system must guarantee the atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability) of this set of operations, which is the acid feature.

3. Data Anomalies

3.1 Dirty Read

3.2 non-repeatable read

3.3 Phantom Reading

4. Transaction ISOLATION Level

The isolation level of 4 transactions is defined in the SQL92 standard for inconsistencies that may occur when reading data:

isolation level	Dirty Read	non-repeatable read	Magic Read
READ UNCOMMITTED (not submitted)	Is	Is	Is
Read Committed (Reading submitted)	Whether	Is	Is
REPEATABLE READ (Repeatable Read)	Whether	Whether	Is
Serializable (Serial Read)	Whether	Whether	Whether

5. Transaction control commands

5.1 Commit a transaction

A transaction can be committed using the commit statement, and when the commit statement is executed, the transaction is confirmed to be changed, the transaction is ended, the savepoint is deleted, and the lock is released. When a commit statement is used to end a transaction, other sessions will be able to view new data after the transaction has changed.

5.2 rolling back a transaction

SavePoint (savepoint): A point in a transaction that cancels a partial transaction and automatically deletes all the savepoint-defined save points when the transaction is ended. When rollback is executed, the specified savepoint can be rolled back to the specified point.

6. Database Lock

6.1 Lock Classification

6.2 Shared Locks ( S Lock)

6.3 Exclusive Lock ( X Lock)

6.4 row-Level shared locks ( RS Lock)

6.5 row-level exclusive locks ( RX Lock)

6.6 shared row-level exclusive locks ( SRX Lock)

Oracle Database (3-7)