Oracle Database Programming

I. How to learn Oracle

1. Database administrator

Managing Oracle (Start-Stop, database recovery)

Improve Oracle Performance (allocate memory) optimizes data storage structure ... 2. The database programmer writes the business SQL database script programming (functions, stored procedures, triggers) 3. Java programmers should first understand the basic architecture of Oracle database and then focus on the development tools of PL/SQL and Oracle two. Introduction to Oracle Database1. Object-relational database management system (ORDBMS) 2. It is widely used in management information system, enterprise data processing, Internet and e-commerce, etc. 3. Superior performance in data security and data integrity Control 4. Data interoperability across operating systems and across hardware platforms 5. Key Features of Oracle database: A. Transactions that support multi-user, event traffic B. Data security and integrity control C. Supports distributed data processing D. Portability three. Oracle Architecture1. The architecture of the database refers to the composition, working process and principle of the database, as well as the organization and management mechanism of the data in the database 2. Oracle servers provide open, comprehensive, and comprehensive information management, comprised of Oracle databases and Oracle instances

A collection of background processes and memory structures that manage a database is called an Oracle instance

Four. Oracle instance

1. An Oracle instance is a collection of background processes and memory structures 2. In general, each Oracle database must correspond to an Oracle instance, but in special cases one database in a server can correspond to multiple instances. Therefore, before you can establish a database, you must first specify the system identifier for the Oracle instance, which is the Oracle SID. Identifier. This Oracle SID is the name of the Oracle instance. Because the Oracle SID can be used to differentiate between different Oracle databases, the SID name must be unique. Five. Oracle Instance memory structureOracle's memory structure consists of the following two memory areas: System Global Area (SGA), program Global Zone (PGA) Note: When the server process starts, it assigns the program global Zone 1. The SGA (System global Zone) is divided into the following areas: Log buffer, shared pool, data buffer SGA saves the database environment and dictionary information, and A is shared by multiple database processes. Log buffers: All modification information for the logging database reduces the operation of the disk when the log is saved, improving database performance. B. Data buffers: Used to store data read from a disk data file, shared by all users. The size of the data buffer has a direct effect on the read speed of the database. Data from common tables has been read, reducing disk operations and improving database performance. C. Shared pool: A shared pool is an area of memory that parses, compiles, and executes SQL, PL/C programs, and the size of the shared pool directly affects the performance of the database. Save the results of SQL analysis, reduce the time to analyze the same SQL, and improve the efficiency of SQL execution. 2. The program Global Zone (PGA) contains the data and control information required for a single server process. The PGA is the information that is automatically assigned when a user process connects to the database and creates a session, and it saves each user process that is connected to the Oracle database. PGA is a non-shared zone that can only be used by a single process, and when a user session ends, the PGA releases Six. Background processIn Oracle's various processes, the most important is the background process: 1. Pmon Process monitoring process: Clean up the failed process. Releases all currently pending locks. Releases the resources used by the failed process. 2. Smon system monitoring process after the instance fails, the instance is automatically resumed when the database is reopened. Organize the free space of the data files and combine the adjacent areas. Frees temporary segments that are no longer used. 3. DBWR Data Write Process: Manages the data buffer and retains the most recently used blocks in memory. Writes the modified buffer data to the data file. 4. LGWR Log Write process: Responsible for writing log data in the log buffer to the log file. The system has more than one log file, which writes data to a file in a circular fashion. seven. Oracle database1. The Oracle database consists of operating system files that provide the actual physical storage area 2 for database information. Oracle database includes logical structure and physical structure

Logical structure refers to the relationship between logical concepts formed after database creation

A. Physical architecture A. Data files: Used to store database data, such as tables, index data, and so on. B. log file: Records all modification information for the database for recovery. C. control file: A binary file that records the physical structure of the database. B. Logical architecture: What is the logical "unit" for Oracle to store a. Block: A data block is the smallest storage unit that an Oracle server can allocate, read, or write. The Oracle server manages the storage space for data files in blocks. B. District: The area allocates space for a segment, which consists of contiguous data blocks. When all the space in the segment is fully used, the system automatically assigns a new zone to the segment. Zones cannot exist across data files, only in one data file. C. Paragraph: a segment is a logical storage structure that constitutes a table space, consisting of a set of regions. According to the characteristics of the data stored in the segment, the segments are divided into four types, namely data segments, index segments, fallback segments, and temporary segments. D. The tablespace is the largest logical unit in the database, and an Oracle database contains at least one table space, which is the system table space that is named systems. Each tablespace is made up of one or more data files, and a data file can be associated with only one table space. The size of the tablespace equals the sum of all the data file sizes that make up the tablespace. The syntax for creating a tablespace is:

create tablespace tablespacename

datafile ' filename ' [SIZE integer [k| M]]

[Autoextend [off| On]];

E. Schema: The schema is the generic name of the database object created by the user. Schema objects include tables, views, indexes, synonyms, sequences, procedures, and packages. Relationship to the user: the schema name is the user name that controls the pattern. Eight. Instance-to-database relationships1. Relationship one instance can manage only one database one database may be managed by multiple instances at the same time 2. Single instance and multi-instance a database has only one instance management, called Single Instance database. A database is managed by multiple instances and is called a multi-instance database. Nine. Introduction to PL/SQL procedures1. PL/SQL programs comprise the most basic unit of the PL/SQL program is the PL/SQL block. 2. Classification of PL/SQL blocks a. Anonymous BLOCK: Directly executes block B. Named BLOCK: Block a called by name. Subroutines (including stored procedures and functions): Java-like methods and function B. Triggers: Java-like event handling code that executes C automatically when something happens. Package: Java-like packages, the main role of organization code and avoid naming conflicts. 10. PL/SQL operating mechanism1. Understanding Pl/sqlpl/sql is an extension of standard SQL. PL/SQL execution requires PL/SQL engine execution, and the PL/SQL engine may generally be installed on the server side, but it may also be installed on the client. 2. Operating mechanism A. The server-side PL/SQL engine (such as Oracle Server) executes procedure a. Standard SQL is given to SQL engine processing B. The non-standard part is given to PL/SQL engine processing B. The client's PL/SQL engine (such as Oracle Forms) executes procedure a. Standard SQL gives the server SQL engine processing B. Non-standard parts are given to the PL/SQL engine for processing on the client, and if there is no SQL, all PL/SQL is handled on the client. 11. The compilation process for PL/SQL blocks1. Why compiling PL/SQL blocks must be compiled into machine code to perform 2. PL/SQL Block A. Anonymous block: Before execution, compile B. Named blocks: when created or modified into immediate compilation 3. Compilation Process A. PL/SQL engine B. Grammar check: Check the syntax or compilation errors of PL/SQL code.

A. Binding: If the grammar check passes, the compiler assigns a storage address to the program variables that Oracle stores data, a process known as bindings. Bindings allow Oracle to reference storage addresses while the program is running.

B. Checking references to objects such as tables, columns, and so on

C. Generate pseudo-code and set the legal state

Note that even if the status is set to legal, execution will still fail if the corresponding table does not exist, and the state is automatically set to unavailable.

12. Benefits of using PL/SQL1. Why use Pl/sqla. Reduce network traffic and improve application performance B. Provides modular program development capabilities, simplifying development and maintenance C. With good compatibility, different development tools can use the same PL/SQL script d. Allows the definition of identifiers, which increases the readability of the program E. Provides a program control structure, enhanced business processing capacity F. Provides exception handling to make PL/SQL programs more robust 13. Data dictionaryA. The data dictionary holds information about data objects and segments, which is a read-only object that is stored in the system table space and is not allowed to be modified by anyone. B. Database objects include: Users, Roles, tablespaces, tables, views, clusters, types, synonyms, sequences, indexes, database links, stored procedures, functions, packages, triggers, and so on. C. Oracle combines these database objects into a collection called schema (translated into schema). A schema can only be owned by a data user, and the schema name is the same as the user's name. 14. TableThe concept of tables in Oracle and the syntax for creating, deleting, and modifying tables are essentially the same as for other databases. Common data types for Oracle tables: Table

Data type	Sql server	Oracle
Integer	Int,smallint,bigint	Number (m)
Decimal	Decimal	Number (M,n)
Fixed length string	Char	Char
Variable length string	varchar	Varchar2
Date	Smalldatetime,datetime	Date

15. Synonyms1. Why use synonyms when a database object, such as a table modification name, is used to avoid re-modifying the SQL statement by using synonyms, thereby reducing system maintenance effort. 2. Create synonym A. Common synonym: Create public synonym synonym name for database object name; b. private synonym: Create synonym name for database object name; 3. Delete synonym syntax: drop synonym synonym; 16. View(VIEW) is a virtual table that is generated from one or more tables through a query statement. Only the definition of the view is stored in the database (exists in the data dictionary) 1. Creating view syntax: Create or Replace view view name as query; 2. Using the view example: SELECT * from the name of the Where condition; 3. Delete View syntax: Drop view name; 17. Sequence:is an object of the Oracle database that provides the application with a basic continuous increment and maintains a unique numeric value. 1. Why a sequence is used primarily to implement automatic growth of the primary key columns of a database table. 2. Creating sequence Syntax: Create sequence sequence name 3. Use sequence syntax: sequence name. nextval Syntax: sequence name. currVal4. Delete sequence syntax: Drop sequence sequence name case add student, number use AutoNumber.

Oracle Database Programming