PL/SQL Program control structure and change data and management transactions in PL/SQL

1. Condition Control

A. If conditional branching syntax:

if (condition 1) Then

Statement

elsif (condition 2) Then

Statement

elsif (condition 3) Then

Statement

Else

Statement

End If;

B. Case Conditional Branching: equivalence comparison, condition comparison, Case expression

A. Equivalence comparison syntax:

Case condition Judgment

When the expression of the condition value then the conditional action to be performed;

.......

[ELSE ...]

END case;

B. Conditional comparison syntax:

Case

When different comparison conditions then to perform the conditional operation;

.......

[ELSE ...]

END case;

2. Cycle control

A. Basic loops: statements are executed at least once, regardless of whether the condition is met

Syntax: LOOP

........

EXIT [when conditional statement];

END LOOP;

B. While loop: executes only if the condition is true

Syntax: While conditional statement LOOP

.........................................

END LOOP;

C. For loop

Syntax: For loop counter in [REVERSE] lower bound ... Upper LOOP

................................................................................................

END LOOP;

D. Nesting loops and Labels: defining labels:<< label names >>

3. Sequential control

A.goto statement: Used to jump to a specified << label >> to execute a statement.

The disadvantage is that it increases the complexity of the program and reduces readability, so Oracle does not recommend using it for the day.

B. NULL statement: Empty statement, do not have any actual effect, usually occupy position with

4. Exception Handling

A. There are two types of exceptions:

A. Pre-defined exceptions: implicitly thrown when PL/SQL programs violate Oracle rules or exceed system limits

B. User-defined exception: The user can define an exception in the Declarations section of the PL/SQL block, and the custom exception is explicitly raised by the RAISE statement

B. Define exception Syntax: Exception name EXCEPTION;

PRAGMA Exception_init (exception name, error number);

C. Throw exception syntax

A. RAISE exception name;

B. Raise_application_error (Error number, error description [, {true| FALSE}]);

D. Handling exception syntax

EXCEPTION

When ..... Then

................

When then

When OTHERS Then

.............

5. Cursor Properties for DML operations

A. An implicit cursor

A. Name: SQL

B. Property: sql% Property name

C. Common Properties

Sql%found: Manipulating to data

Sql%notfound: no action to data

Sql%rowcount: Number of records for the operation

B. Returning sentence for DML statement: Returns the result of SQL execution

6. Batch execution of DML with forall statements

A. ForAll statement syntax

ForAll index in Start: End

SQL statements; --note that there is only one sentence!

B. Use bulk binding on INSERT statements: First assign a value to the collection element, and then use the ForAll statement to bulk Bind

C. Cursor Properties for ForAll

A. Sql%found: Manipulating to data

B. Sql%notfound: no operation to data

C. Sql%rowcount: Number of records for operations

D. Sql%bulk_rowcount: Returns the number of rows that the first element of the batch executes

7. Database transaction management in PL/SQL

A. Transaction concept: A transaction is a complete logical unit of work, consisting of multiple operations. All operations are either all successful or all fail

B. Transaction characteristics: ACID: atomicity, consistency, isolation, (permanent) durability

C. Transaction control Process

Start a transaction

Automatic execution of the first SQL starts

Execute SQL

More than one SQL may be executed

Commit or roll back

Commit or Rollback

D. Lock concept: A lock is a mechanism that a database uses to control concurrent access to shared resources, locks are used to protect data that is being modified, and other users can update data until a transaction is committed or rolled back

E. Type of Lock: row-level lock, table-level lock

A. Row-level Lock: Locks the row being modified. Other users can access rows other than those that are locked

Oracle automatically applies row-level locks when using the following statement:

A. INSERT

B. UPDATE

C. DELETE

D. SELECT ... For UPDATE

Release a lock using a commit or ROLLBACK statement

B. Table-level Lock: locks the entire table and restricts access to the table by other users

Syntax: LOCK TABLE table_name in mode mode;

F. Deadlock: When two transactions are waiting for each other to release resources, a deadlock is formed, Oracle automatically detects deadlocks, and resolves a deadlock by ending one of the transactions

8. Transaction ISOLATION LEVEL

A. Classification

A. Non-submitted read

Read data not submitted by other users, also known as dirty Read

B. Read-Committed: Oracle support, which is also the default supported level (non-repeatable read data exceptions may occur)

C. REPEATABLE READ: Oracle does not support (phantom read data exceptions may occur)

D. Serial read: Oracle support, but low efficiency

9. Database transaction Implementation mechanism: through the log file implementation

PL/SQL Program control structure and change data and management transactions in PL/SQL