Getting Started with Oracle databases

Basic Concepts of Oracle Database

1. PL/SQL (procedural language/sql) is a procedural language extension of Oracle Corporation's standard language SQL for relational databases.

PL/SQL possesses features that are not ①: variable definition;

② control structure;

③ a custom subroutine;

④ object type;

2. PL/SQL blocks are the most basic units that compose PL/SQL programs.

Block: anonymous block;

Named blocks: subroutines, packages, triggers (using named blocks to enable modular programming and optimize application performance).

The anonymous block code is placed on the client, and the named block code is placed on the server side.

3. PL/SQL Block compilation: When each anonymous PL/SQL block executes, the code is sent to the PL/SQL engine on the server, where PL/SQL blocks are compiled. The named PL/SQL block is compiled only when the block is created or modified.

The compilation process includes syntax checking, binding, and pseudo-code generation. Successful compilation of a named PL/SQL block does not guarantee that the code will be successfully executed in the future.

Benefits of using PL/sql:

① improve application performance;

② provides modular program development capabilities;

③ has good compatibility;

④ allows defining identifiers;

⑤ provides a program control structure;

⑥ provides exception handling.

4. Oracle Memory Architecture

Oracle memory structure can be divided into shared memory and non-shared memory area, the shared memory area is mainly composed of the SGA (System Global area), the non-shared memory is mainly composed of the PGA (Program Global Region).

The Oracle database runtime now plans a fixed zone in memory to store the data that each user needs to access, as well as the system information required by the Oracle runtime. We call this area the system global area, referred to as the SGA. The SGA includes several important areas: database buffers, redo log buffers, shared pools, other regions (such as Large pool, etc.).

The PGA user holds data and control information for the server process, which is an area of memory independent of the SGA. When a user process connects to an Oracle server, the Oracle server assigns the appropriate PGAto each server process, and Oracle automatically releases the memory space occupied by the PGA when the server processes. The PGA consists of 4 parts: Sort area, session information, cursor State, stack space, etc.

5.

A view is a virtual table generated from a query statement in one or more tables, creating the syntax for the view:

1 create or replace view view_name as SQL statement;

Synonyms are the aliases of database objects and the syntax for creating synonyms:

　　 1 create public synonym synonym name for database object name;--Common synonyms

1 Create public synonym name for database object name;--private synonym

A sequence is a special object in Oracle that implements the automatic growth of the primary key columns of a database table, creating the syntax for the sequence:

1 create sequence sequence name increment by increment seed number start with start number MaxValue maximum value;

PL/SQL Programming basics

1. Introduction to PL/SQL blocks

A block is a basic program unit of PL/SQL and there is no limit to the nesting of blocks. PL/SQL consists of 3 parts: The definition section, the execution section, and the Exception handling section. 　

 　　　Create /*  */begin* * execute partial--pl/sql statement and  SQL statement * / Exception /* Exception Handling section--handling run errors   */ End; 　　　　　　　　　　　

2. Types of PL/SQL blocks

The PL/SQL blocks can be divided into anonymous blocks, subroutines, and triggers, depending on the application module functionality you need to implement.

Anonymous blocks: Dynamically constructed blocks that can be executed directly, anonymous blocks can be embedded into the application or used directly in an interactive environment.

Subroutines: Include stored procedures, functions, packages. You can simplify the development and maintenance of client programs and improve application performance.

Procedure: Used to perform a specific operation, either specifying a data parameter or specifying an output parameter.

Function: For returning specific data, the function header must contain a return statement.

Package: A procedure and function related to a logical composition, which consists of the inclusion and package Specification 2 parts.

Package Specification: Contains only the description of the procedure and function, and the implementation code of the function without the procedure.

Package: A body is used to implement procedures and functions in a package specification.

Trigger: Refers to a stored procedure that is implicitly executed. When defining a trigger, you must specify the trigger event and trigger the action.

Program Control Structure

Condition Control:

If statement eg:

If condition 1 then SQL statement elsif condition 2 then SQL statement else SQL statement end if;

Case statement eg:

 1  case   selector  2  when  expression1 then   Sequence_of_statement1;  3  then   Sequence_of_statement2;  4  then   Sequence_of_statement3;  5   ...  6  then   sequence_of_statementn;  7   Case ; 

1  CaseV_deptno2       when Ten  Thensequence_of_statement1;3       when  -  ThenSequence_of_statement2;4       when  -  ThenSequence_of_statement3;5 End  Case;

Loop control:

Basic loop:

1 Loop 2     statement1; 3     Exit [whencondition] ; 4 end Loop;

While loop:

1  while condition Loop 2     statement1; 3     Statement2; 4     ... 5 end Loop;

For loop:

1  for inch [reverse] lower_bound. Upper . Bound Loop 2     statement1; 3     ... 4 end Loop;

Exception handling:

Common pre-defined exceptions:　

Access_into_null undefined Object

If the corresponding when is not included in the Case_not_found case and no else is set

Curser_already_open Cursor already open

Dup_val_on_index duplicate values on a column corresponding to a unique index

Invalid_number Embedded SQL statements cannot convert characters to numbers

Too_many_rows when performing a select INTO, the result set exceeds one row

Zero_divide Divisor is 0

Login_denied PL/SQL application provides an incorrect user name or password when connecting to an Oracle database

not_logged_on PL/SQL applications access data without a connection to the Oralce database

Changing data and managing transactions in PL/SQL

Syntax for the ForAll statement:

1 forall index in lower. Bound. Upper . Bound SQL statements;

Oracle considers a logical unit of work consisting of a set of SQL statements as a database transaction. A logical unit of work must have four properties that become acid (atomicity, consistency, isolation, persistence):

Basic transaction processing consists of the following aspects:

① start the business;

② Execute SQL statement;

③ commits the transaction;

④ rolls back the transaction (executes the rollback statement).

Isolation level of the transaction:

Read-committed: Lowest Most dangerous isolation level, transactions susceptible to dirty reads

Dirty reads: is one of the data consistency issues, where a dirty read occurs when a transactional read is changed by another transaction but no data has been submitted.

Read-Committed Isolation level: Dirty reads do not occur, but cannot prevent non-repeatable reads;

REPEATABLE READ Isolation level: While preventing dirty reads and non-repeatable reads, phantom reads may occur;

Phantom read: is one of the data consistency issues, similar to repeatable reads, but it considers the fact that other transactions add a new record to the table when the current transaction is acting on a table.

Serializable isolation Level: prevents dirty reads, non-repeatable reads, Phantom reads, but low performance.

Using cursors in PL/SQL to get

The cursor's state can be obtained by the cursor's properties, and the cursor property is added after the cursor name as a "% property name."

6 Cursor Properties:

%found,%notfound,%rowcount,%isopen,%bulk_rowcount,%bulk_exception

Using an implicit cursor: 1 select list column name [bulk collection] into PL/ SQL variable list 2 ... other parts of the SELECT statement ...

Using explicit cursors: steps: Declaring, opening, fetching records, closing

1) Statement:

1  cursor cursor name [][]2      is  Select  statement 3         [ for Update [of [column Name list ] ];

2) Open cursor: The open display cursor name (parameter list);

3) FETCH RECORD: Fetch cursor name into record or variable list;

4) Close the display cursor: close cursor name;

Developing PL/SQL subroutines and packages

Subroutine: is a named PL/SQL block that can be taken with parameters and can be called at any time when needed.

Sub-Program Advantages: modularity, reusability, maintainability.

Type: Process: Used to perform a specific operation;

Function: Used to return specific data.

To develop stored procedures:

1 Create or Replace procedure procedure_name (argument1 [model] datatype1,argument2[model Datatype2, ...
2 is [as]
3 PL/SQL statements;

Development function:

1 Create or Replace function function_name (argument1 [model] datatype1,argument2[model Datatype2, ...
2 return datatype
3  is |  as
4 PL/SQL statements;

To set up Package specifications:

1 Create or Replace Package Package_name 2  is |  as 3    Public  and Item Declarations 4   subprogram Specifications5end package_name;

Build the package Body:

1 Create or Replace Package Body Package_name 2   is |  as 3     Public  and Item Declarations 4    subprogram Body5  End Package_name;

Calling Package Component: Package name. Component Name;

Subroutine overloading: Package name is the same, parameter list is different

Benefits of the package:

① Modular;

② easier application design;

③ information hiding;

④ performance is better.

Getting Started with Oracle databases