Oracle Database Programming, oracle Database tutorial

Oracle Database Programming, oracle Database tutorial

1. How to learn about Oracle

1. Database Administrator

Manage Oracle (Start and Stop, database fault recovery)

Improves Oracle performance (allocates memory) and optimizes the data storage structure ...... 2. database programmers write business SQL database Script Programming (functions, stored procedures, triggers) 3. java programmers should first understand the basic architecture of Oracle databases, and then master PL/SQL and Oracle development tools. II. Introduction to Oracle databases1. object Relational Database Management System (ORDBMS) 2. it is widely used in management information systems, enterprise data processing, Internet and e-commerce. superior performance in data security and data integrity control 4. data interoperability across operating systems and hardware platforms 5. main features of Oracle Database:. supports multi-user, large transaction volume transaction processing B. data Security and Integrity Control c. support Distributed Data Processing d. portability Iii. Oracle Architecture1. the database architecture refers to the composition, working process and principle of the database, as well as the organization and management mechanism of data in the database. the Oracle server provides open, comprehensive, and comprehensive information management. It consists of an Oracle database and an Oracle instance.

The collection of background processes and memory structures for database management is called an Oracle instance.

Iv. Oracle instances

1. an Oracle instance is a collection of background processes and memory structures. in general, each Oracle database must correspond to one Oracle instance, but in special cases, one database on a server can correspond to multiple instances. Therefore, before creating a database, you must specify the System Identifier (System Identifier) of the Oracle instance, that is, the Oracle SID. The Oracle SID is the name of the Oracle instance. Because the Oracle SID can be used to distinguish different Oracle databases, the SID Name must be unique. V. Oracle instance Memory StructureThe memory structure of Oracle includes the following two memory areas: the system global zone (SGA) and the program global zone (PGA). Note: when the server process starts, the system allocates the program global Zone 1. SGA (system global zone) is divided into the following areas: log buffer, sharing pool, data buffer SGA stores the database environment and dictionary information, shared by multiple database processes. log Buffer: logs record all changes to the database. This reduces disk operations when saving logs and improves database performance. B. Data Buffer: used to store the data read from the disk data file, which is shared by all users. The size of the data buffer has a direct impact on the reading speed of the database. Reading data from common tables reduces disk operations and improves database performance. C. Shared Pool: The Shared Pool is the memory area for syntax analysis, compilation, and execution of SQL and PL/SQL programs. The size of the Shared Pool directly affects the database performance. Saves the SQL analysis results, reduces the time required to analyze the same SQL, and improves SQL Execution efficiency. 2. the program global area (PGA) contains the data and control information required by a single server process.. PGA is automatically allocated when a user process connects to the database and creates a session. It stores the information required by each user process connected to the Oracle database. B. PGA is not a shared zone and can only be used by a single process. When a user session ends, PGA is released. Vi. background processesAmong the various processes in Oracle, the most important is the background process: 1. PMON process monitoring process: clearing the faulty process. Release all currently suspended locks. Release the resources used by the faulty process. 2. the SMON system monitoring process automatically restores the instance when the database is re-opened after the instance fails. Organize free space for data files and combine adjacent areas. Release temporary segments that are no longer in use. 3. DBWR Data Writing Process: manage the data buffer and keep recently used blocks in the memory. Write the modified buffer data to the data file. 4. LGWR log writing process: Write the log data in the log buffer into the log file. The system has multiple log files. This process writes data to files cyclically. VII. Oracle Database1. The Oracle database is composed of operating system files that provide the actual physical storage zone for the database information. 2. The Oracle database includes the logical and physical structures.

Logical Structure refers to the relationship between logical concepts formed after the database is created.

A. Physical architecture a. Data File: used to store database data, such as tables and index data. B. Log File: records all changes to the database for fault recovery. C. Control File: the binary file that records the physical structure of the database. B. logical architecture: refers to the logical storage unit used by Oracle. a. Data Block: The minimum storage unit that can be allocated, read, or written by the Oracle server. The Oracle server manages the storage space of data files in data blocks. B. Zone: Zone allocates space for segments. It consists of continuous data blocks. When all space in the section is fully used, the system automatically allocates a new area for the Section. A partition cannot exist across data files. It can only exist in one data file. C. segment: the logical storage structure of the tablespace. The segment consists of a group of partitions. Based on the features of the data stored in segments, segments are divided into four types: data segments, index segments, rollback segments, and temporary segments. D. The tablespace is the largest logical unit in the database. An Oracle Database contains at least one tablespace, that is, the SYSTEM tablespace named SYSTEM. Each tablespace is composed of one or more data files. A data file can only be associated with one tablespace. The tablespace size is equal to the total size of all data files that constitute 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 a general term for the database objects created by the user. Schema objects include tables, views, indexes, synonyms, sequences, processes, and packages. Relationship with the user: the mode name is the user name that controls the mode. 8. Relationship between instances and databases1. Relationship between the two. One instance can only manage one database. One database may be managed by multiple instances at the same time. 2. A single instance and multiple instances can only be managed by one database, called a single instance database. A database is managed by multiple instances at the same time. IX. PL/SQL program Introduction1. The most basic unit for PL/SQL programs to form PL/SQL programs is PL/SQL blocks. 2. PL/SQL block classification. anonymous block: directly executed Block B. name block: Block a called by name. subroutine (including stored procedures and functions): similar to JAVA methods and functions B. trigger: similar to JAVA event processing code. When something happens, c. package: similar to the JAVA package, it mainly serves to organize code and avoid naming conflicts. 10. PL/SQL Operating Mechanism1. Understand that PL/SQLPL/SQL is an extension of standard SQL. PL/SQL Execution requires PL/SQL engine execution. PL/SQL engine may be installed on the server, but may also be installed on the client. 2. operating Mechanism. server-side PL/SQL engine (such as Oracle Server) Execution Process. pass standard SQL to SQL engine for processing B. non-standard parts are handed over to PL/SQL engine for processing B. client PL/SQL engine (such as Oracle Forms) Execution Process. standard SQL is handed over to the SQL engine of the server for processing B. non-standard parts are handed over to the PL/SQL engine for processing on the client. If no SQL is available, all PL/SQL statements are processed on the client. 11. PL/SQL block compilation process1. why must PL/SQL block be compiled into machine code for execution? 2. PL/SQL block. anonymous block: Compile B before execution. name block: during creation or modification to instant compilation 3. compilation process. PL/SQL engine B. syntax check: Check for PL/SQL code syntax or compilation errors.

A. Binding: If the syntax check is successful, the compiler assigns a storage address to the program variables that store data in Oracle. This process is called binding. Binding allows Oracle to reference the storage address when the program is running.

B. Check the reference of objects such as tables and columns.

C. Generate pseudocode and set the valid status

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

12. advantages of using PL/SQL1. why PL/SQLa. reduces network traffic and improves application performance B. provides modular program development capabilities to simplify development and maintenance. good compatibility. Different development tools can use the same PL/SQL script d. allow the definition of identifiers, increasing program readability e. provides a program control structure and enhanced business processing capabilities. provides exception handling to make PL/SQL programs more robust 13. Data DictionaryA. Data Dictionary stores information about data objects and segments. It is a read-only object stored in the SYSTEM tablespace and cannot be modified by anyone. B. database objects include users, roles, tablespaces, tables, views, clusters, types, synonyms, sequences, indexes, database links, stored procedures, functions, packages, and triggers. C. Oracle combines these database objects into a collection called Schema. A Schema can only be owned by one data user, and its name is the same as that of the user. 14. TableIn Oracle, the table concept and syntax for creating, deleting, and modifying tables are basically the same as those in other databases. Common Data Types of Oracle tables: such as tables

Data Type	SQLServer	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 does synonym apply when a database object such as a table name is modified, you can avoid modifying SQL statements again by using synonyms, thus reducing system maintenance workload. 2. create synonym. public synonym: create public synonym name FOR database object name; B. private synonym: CREATE synonym name FOR database object name; 3. delete SYNONYM Syntax: drop SYNONYM; 16. ViewVIEW is a virtual table generated from one or more tables using query statements. In the database, only view definitions are stored (in the data dictionary. create view Syntax: create or replace view name as query; 2. view Example: select * from view name where condition; 3. delete view Syntax: drop view name; 17. sequence:It is an object of Oracle databases. It can provide applications with a value that increases continuously and maintains a unique value. 1. Why sequence is used to achieve automatic growth of primary key columns in database tables. 2. create sequence Syntax: create sequence name 3. use sequence Syntax: sequence name. nextval Syntax: sequence name. currVal4. Delete sequence Syntax: drop sequence name case to add students. The student ID is automatically numbered.