PL/SQL program control structure and change data and manage transactions in PL/SQL, plsql

PL/SQL program control structure and change data and manage transactions in PL/SQL, plsql
1. condition Control

A. IF condition branch Syntax:

If (condition 1) then

Statement;

Elsif (condition 2) then

Statement;

Elsif (Condition 3) then

Statement;

Else

Statement;

End if;

B. case condition branch: equivalent comparison, condition comparison, and case expression

A. Equivalent comparison Syntax:

CASE condition judgment

The condition operation to be executed by the expression THEN of the WHEN condition value;

.......

[ELSE ......]

End case;

B. Condition comparison Syntax:

CASE

WHEN different comparison conditions THEN need to perform the conditional operation;

.......

[ELSE ......]

End case;

2. Loop Control

A. Basic loop: whether or not the condition is met, the statement must be executed at least once.

Syntax: LOOP

........

EXIT [WHEN Condition Statement];

End loop;

B. WHILE loop: executed only when the condition is true

Syntax: WHILE Condition Statement LOOP

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

End loop;

C. FOR Loop

Syntax: for loop counter IN [REVERSE] lower limit... upper limit LOOP

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

End loop;

D. nested loop and label: Define label: <label Name>

3. Sequential Control

A. GOTO statement: used to jump to the specified <number> to execute the statement.

The disadvantage is that it increases the complexity of the program and reduces readability. Therefore, we recommend that you do not use Oracle.

B. NULL statement: NULL statement. It does not have any actual effect and is usually used as a placeholder.

4. Exception Handling

A. There are two types of exceptions:

A. Pre-defined exception: Implicit triggering when PL/SQL programs violate Oracle rules or exceed System Restrictions

B. User-defined exceptions: You can define exceptions in the Declaration section of PL/SQL blocks. Custom exceptions are explicitly thrown by RAISE statements.

B. Define the EXCEPTION Syntax: EXCEPTION name EXCEPTION;

PRAGMA EXCEPTION_INIT (Exception name, error code );

C. Thrown exception syntax

A. RAISE Exception name;

B. RAISE_APPLICATION_ERROR (error code, error description [, {TRUE | FALSE}]);

D. Handle the exception syntax

EXCEPTION

WHEN ....... THEN

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

WHEN THEN

WHEN OTHERS THEN

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

5. cursor attributes of DML operations

A. Implicit cursor

A. Name: SQL

B. attribute: SQL % attribute name

C. Common attributes

SQL % FOUND: operation to data

SQL % NOTFOUND: no operation on Data

SQL % ROWCOUNT: Number of Operation Records

B. RETURNING Statement of DML: return the result after SQL Execution

6. Execute DML in batches using the ForAll statement

A. ForAll statement syntax

ForAll index in start... end

SQL statement; -- Note that there is only one sentence!

B. Use batch binding on the INSERT statement: First assign values to the set elements, and then use the ForAll statement to batch bind

C. ForAll cursor attributes

A. SQL % FOUND: operation to data

B. SQL % NOTFOUND: No data is operated

C. SQL % ROWCOUNT: Number of Operation Records

D. SQL % BULK_ROWCOUNT: returns the number of rows affected by the I element during batch execution.

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 either succeed or fail

B. Transaction Features: ACID: atomicity, consistency, isolation, and permanent persistence

C. Transaction Control Process

Start transaction

The first SQL statement is automatically executed.

Execute SQL

Multiple SQL statements may be executed.

Submit or roll back

Commit or rollback

D. the concept of a lock: A lock is a mechanism used by a database to control concurrent access to shared resources. A lock is used to protect the data being modified. Other users can update the data only after the transaction is committed or rolled back.

E. Lock types: Row-level locks and table-level locks

A. Row-Level Lock: locks the row being modified. Other users can access rows other than locked rows

Oracle automatically applies row-level locks when the following statements are used:

A. INSERT

B. UPDATE

C. DELETE

D. SELECT... FOR UPDATE

Use the COMMIT or ROLLBACK statement to release the lock.

B. Table-Level Lock: Lock the entire table and restrict access to tables by other users.

Syntax: lock table table_name IN mode MODE;

F. deadlock: When two transactions wait for each other to release resources, a deadlock will occur. Oracle will automatically detect the deadlock and end one of the transactions to solve the deadlock.

8. transaction isolation level

A. Category

A. uncommitted read

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

B. Submit read: Oracle Support, which is also supported by default (non-repeated read data exceptions may occur)

C. Repeatable read: Oracle does not support this function (phantom read data exceptions may occur)

D. Serial reading: supported by Oracle, but inefficient

9. Database Transaction Implementation Mechanism: implemented through log files