Oracle Database Learning notes (ii)

Oracle performs SQL processing: syntax checking, semantic checking, parsing of SQL statements, execution of SQL, and return of results.

The PGA is an area of memory that is independent of the SGA, and when the user process connects to the Oracle server, the Oracle server assigns the appropriate PGA to each server process, and when the server process finishes, Oracle automatically releases the memory space occupied by the PGA.

The PGA consists of 4 parts, such as sorting area, reply information, cursor State, stack space, etc.
Oracle Background process: Data write process, log write process, system monitoring, process monitoring, checkpoint process, archive process.

Data integrity: Entity integrity, domain integrity, referential integrity, and custom integrity.

There is a maximum of one primary key in a table, multiple columns can be used as primary key (composite primary key, combined primary key), primary key selection: A single column is generally selected, as far as possible to choose the actual operation of the amount of irrelevant.

The data type of the columns of the table in Oracle: string, Number type, date type, pseudo example, large object LOB type, bfile type, raw type.

Sequence: is a special object in Oracle that provides basic sequential and unique values to the application. The sequence is used to automate the growth of the primary key columns of a database table. Nextval returns the next available sequence value, Currval gets the value of the current sequence.

Dual is a special kind of table (virtual table), which has only one row, the name of the column is virtual, and the data type is char (1). This table is used when we want to quickly get information that is not relevant to a particular table, but is related to an external source or function.

PL/SQL Programming basics
PL/SQL Features: 1). Good for the operation of C/S environment application, 2). Suitable for customer environment, 3). Modularization, 4). Process, 5). Provide a large number of built-in packages; 6). Operational error handling.

PL/SQL blocks are divided into: Database PL/SQL and tools PL/SQL.

The PL/SQL block consists of three parts: the definition section, the execution part, and the exception handling part. The definition section is used to define constants, variables, cursors, user-defined exceptions, complex data types, and the execution section is used to implement the application module functionality, which contains the PL/

Identifier naming rules:
1. Define variables, we recommend using v_ as prefix; 2. Defining constants, we recommend using C_ as a prefix
3. Define the cursor, it is recommended to use _cursor as the suffix; 4. Define the exception, we recommend using E_ as the prefix
5. Define the PL/SQL table type, we recommend using _table_type as the suffix;

Literal: numeric literal, character literal, string literal, Boolean literal, date time literal.

Note: Single-line Comments (-), multiline comments (/* */).

Oracle Database Learning notes (ii)