PL/SQL Transaction Control

1. SQL statements in PL/SQL

-The end statement has nothing to do with content such as commit.

-PL/SQL does not directly support data definition language (DDL) statements, such as CREATE TA BLE, ALTER TABLE, or DROP TABLE.

-PL/SQL does not the support Data Control Language (DCL) statements, such as GRANT or REVOKE.

-There must be one behind each SQL statement; Semicolon.

-PL/SQL can manage errors by setting exception, such as No_data_found, too_many_rows

2. SQL Cursors (Brief introduction)

A cursor is a private SQL work area.

2 types: Implicit cursors, explicit cursors

Implicit cursors: The Oracle server uses implicit cursors to parse and execute your SQL statements. (whenever issue a SQL statement, the Oracle server opens an area of memory in which the command is parsed and execute D. This section is called a cursor.

When the executable part of a block issues a SQL statement, PL-SQL creates an implicit cursor, which PL/SQL manages automa Tically.

Explicit cursors: Explicit cursors is explicitly declared by the programer. (These are the properties of the cursor)

-Sql%rowcount

-Sql%found

-Sql%notfound

-Sql%isopen

Note here: simply using Sql%rowcount, there is no definition of the display cursor, because this is an implicit cursor that proves that Oracle uses an implicit cursor when parsing SQL statements.

3. Transaction Control Statement

COMMIT [work];

SavePoint savepoint_name;

ROLLBACK [work];

ROLLBACK [Work] to [savepoint] savepoint_name;

Which: seemingly work is useless.

4. SELECT statement & Loop statement

IF condition Then

elsif condition then//note, here elsif, without that E

ELSE

END IF; Finally, the end is to have; semicolon

Case Selector

When expression1 then RESULT1//There's no sign behind this.

When Expression2 then RESULT2

......

When Expressionn then RESULTN

ELSE resultn+1

END; to have END and; Semicolon

Note that null:not null or NULL, and NULL calculations are all NULL,

--Basic loop (use the basic loop if the statements inside the loop must execute at least once)

LOOP

Statement 1;

EXIT [when condition];

END LOOP;

--while Loop (Use the while loop if the condition have to is evaluated at the start of each iteration)

While condition LOOP

Statement1;

Statement2;

...

END LOOP;

--for Loop (use a for loops if the number of iterations is known)

For counter in [REVERSE] lower_bound. Upper_bound LOOP//counter do not define their own, the system will be directly defined, REVERSE means decrement, or increment, increase or decrease amplitude 1

Statement1;

Statement2;

...

END LOOP;

For example:

For I in 1..3 LOOP

END LOOP; increase, I do not define

For i in REVERSE 1..3 LOOP

END LOOPS//minus, pay attention to the back of the range is not to change, or from low to high

--Nested Loops

Loops can be nested between each other, multilayer, you can use the label to jump to where you want to go, label to the corresponding code on the top,

It is good practice to write a flag (Inner_loop, outer_loop) behind END LOOP.

5. Composite Data Types

-PL/SQL Records

-PL/SQL Collections (Index by table, nested table, Varray) (seemingly infrequently used)
(that is, to focus on a variety of related data, such as Employeer, name, salary, birthday, etc., although name, Slary, birthday their storage type is different, but because of the correlation, so they as a whole view)

TYPE Type_name is RECORD

(Field_declaration, field_declaration);

identifier type_name;

For example:

TYPE Emp_record_type is record

(employee_id number (6) not NULL: = 100,

Last_Name VARCHAR2 (25),

job_id VARCHAR2 (10),

Salary Number (8, 2));

Emp_record Emp_record_type;

emp_record.job_id: = ' St_clerk ';

This is very similar to the structure in C

%rowtype

DECLARE

Emp_record Employees%rowtype;

To declare a record based on a collection of columns in a database table or view, you use the%rowtype attribute.

The benefit of using ROWTYPE, 1) can be changed dynamically, that is, when the base table itself is changed, ROWTYPE will change, 2) especially when you want to drop all the content, you can use the SELECT * from TABLE into the type, save a lot of typing operations.

INDEX by Tables

Consists of two parts (primary key and column)

-Primary Key of data type Binary_integer

-column of scalar or record data type (1 columns, or a standard type column, or a combined type column)

TYPE Type_name is TABLE of

{column_type Variable%type | table.column%type | table.%rowtype} [INDEX by Binary_integer];

identifier type_name;

For example:

TYPE Ename_table_type is Table of

Employees.last_name%type

INDEX by Binary_integer;

Ename_table Ename_table_type;

(a bit like XML)

If defined as the INDEX by Table type, there are many methods, EXISTS, NEXT, COUNT, TRIM, first and last, DELETE, PRIOR

6. Rights Management related content

System privileges includes the CREATE or any keyword

System permissions are given by SYS, system,

Object privilege is rights assigned to a specific object within a Shema and always include the name of object.

If you want to create a subprogram (procedure, function), you must have CREATE procedure Quanxian,

If a PL/SQL subprogram refers to any objects that is not in the same schema, you must being granted access on these explicit Y, not through a role.

If you want to call Subprogram, you must have EXECUTE object privilege.

Stored Information	Description	Access Method
General	Object Information	The User_objects data dictionary
Source Code	Text of the procedure	User_source
Parameters	In/out/in out datatype	Describe command
P-code	Compiled object code	Not accessible
Compile errors	PL/SQL syntax errors	User_errors
Run-time Debug Information	User-specified Debug variables and messages	The Dbms_output

You can use show errors to view compilation errors (which are also stored in user_errors)

DESCRIBE Query_employee (see procedure and other content, similar to DESC table_name)

You can also use Dbms_output to display content, debug using (like printf everywhere, see the contents of certain variables)

-Hint content 1:message upon entering, leaving a procedure, or indicating that an operation have occured

-Hint content 2:counter for a loop

-Hint content 3:value for a variable before and after an assignment.

Dbms_debug (some tools, such as PL/SQL DEVELOPER, provide single-step tracking, etc.)

PL/SQL Transaction Control