Oracle Database Basics

Oracle Database Basics

Oracle Database Basics

1. Oracle Introduction: Oracle is a database management system and a core product of Oracle.

Oracle database features:

> Supports multi-user, large transaction volume Transaction Processing

> Superior performance in maintaining data security and integrity

> Support for Distributed Data Processing

> Portability

1. Database

Concept: Oracle is a collection of data stored on disks. It physically represents data files, log files, and control files. It logically exists as a tablespace. First, you must create a database, before using Oracle. You can also create a database while installing the software or separately after installation.

2. Global Database Name

3. database instance

4. tablespace

5. Data Files

6. Control File

7. Log Files

8. Mode and mode object

Install Oracle

Start a database in Windows

Three Common Oracle services

(1) OracleServiceSID is an Oracle database service, which is created for a database instance named SID (system identifier. SID is the name of the Database entered when the Oracle database is installed. It is automatically started by default.

(2) OeacleOraDb11g_home1TNSListerer: Listener service, remote database connection Function

(3) OracleDBConsoleSID: Database Console service.

 

Configure Database

 

Connect to database

1. SQL * Plus: a client tool that interacts with Oracle databases.

2. PL/SQL Developer: a third-party client tool.

2. Oracle Data Type

(1) character data type

A) Char data type:

B) Varchar2:

C) Nchar:

(2) numerical data type

A) Number:

(3) Date and Time Data Types

A) DATE data type

B) TIMESTAMP:

(4) LOB data type:

LOB, also known as "Big Data", can store up to 4 GB of unstructured data. LOB allows efficient, random, and segmented access to the database. It can be external or internal.

A) CLOB (Character): stores a large amount of Character data (including single-byte and multi-byte data). It is mainly used to store unstructured XML documents.

B) BLOB (Binary): stores large Binary objects.

C) BFILE: stores binary files in operating system files outside the database.

D) NCLOB: used to store large NCHAR character data.

Tables in Oracle can have multiple LOB types, and no LOB column can be of different LOB types.

Shoddy in Oracle:

1. ROWID: important use

A) You can access a row in the table as quickly as possible.

B) shows how rows in a table are stored.

C) It can be used as the unique identifier of the table's row.

2. ROWNUM: Limit the number of rows returned by the query,

SQL introduction:

> Database Definition Language (DDL): CREATE, ALTER, TRUNCATE, and DROP.

> Database operation language (DML): INSERT, SELECT, DELETE, and Update commands.

> Transaction control language (TCL): COMMIT, SAVEPOINT, and ROLLBACK ).

> Data Control Language (DCL): GRANT and REMOVE)

 

Data Definition Language: create table:

Create a labeling rule:

> The table name starts with a letter.

> The reserved Oracle words cannot be used as the table name.

> The maximum table name length is 30 characters.

> Different tables in user mode cannot have the same name.

> You can use "_" numbers and letters, but cannot use spaces or single quotes.

Oracle table names (column names, user names, and other object names) are case-insensitive and the system automatically converts the case to case-insensitive.

VARCHAR is not recommended for Varchar2 Standard Data Types

 

Truncate table <TABLE Name>: delete only the records in the TABLE and not the TABLE structure. Resource Saving and high execution efficiency.

Tip:

(1) when to start the transaction: in Oracle, the transaction is automatically started when the data is modified for the first time after the last transaction ends.

(2) When to close the transaction

There are two cases

1. Data Submitted

A) COMMIT

B) when executing DDL or DCL statements, the current transaction is automatically committed.

C) separate from Oracle and exit PL/SQL Developer

2. Data is revoked

A) ROLLBACK

B) the server process ends abnormally.

C) DBA stops the session.

SQL Operator

1. Arithmetic Operators: + -*/

2. Comparison OPERATOR: <=><=! = Between and, IN, LIKE, IS NULL

3. logical operators: AND OR NOT

4. Set operators: UNION, UNIONALL, INTERSECT, and MINUS ).

 

SQL Functions

1. conversion functions:

A) TO_CHAR: a function for formatting and displaying dates

B) TO_DATE: Convert the TO_CHAR and TO_NUMBER data types to the TO_DATE data types.

C) TO_NUMBER: implicit conversion.

2. Other functions:

A) NVL (exp1, exp2): If 1 is NULL, 2 is returned. Otherwise, 1 is returned.

B) NVL2 (, 3): If 1 is NULL, 2 is returned. Otherwise, 3 is returned.

C) DECODE (value, 1, then1, 2, then2,): value1 f returns 1 alue = 2 returns 2

3. analysis functions

A) ROW_NUMBER: returns a unique value. When the data is the same, the ranking order increases sequentially.

B) DENSE_RANK: return a unique value. The ranking sequence is the same for the same data.

C) RANK: return a unique value with the same data ranking and a blank ranking between the last record and the next record.

02 -- Oracle Database Application

1. tablespace and user permission management

1. tablespace:

Table space category:

Permanent, temporary, and undo tablespaces

Purpose:

(1) assign different tablespaces to different users and assign different tablespaces to objects in different modes to facilitate user data operations and manage mode objects.

(2) Different data files can be created on different disks, which is conducive to disk space performance management, data backup and recovery, and so on.

Create a tablespace:

Create table tablespacename

Delete tablespace:

Drop table tablespacename

2. Custom User Management:

A) SYS: Super User administrator

B) SYSTEM: Default SYSTEM Administrator

C) Scott: Temporary demonstration user

3. Database permission management:

A) system Permissions

Common system Permissions

> CREATESESSION: connect to the database

> CREATETABLE: Creates a table.
> CREATEVIEW: Create an attempt

> Create sequences: Creates a sequence.

B) object permissions

Oracle database users can obtain permissions in two ways:

1. The Administrator directly grants permissions to the user: 1. direct authorization

2. The Administrator grants permissions to the role and then grants the role to one or more users. 2. grant permissions through the role.

2. Sequence

I. Create sequence:

Ii. Access columns:

Iii. Change sequence:

Iv. Delete sequence:

V. Use sequence:

 

3. Synonyms

Synonyms are aliases of tables, views, sequences, functions, processes, packages, or other synonyms.

4. Index

An index is an optional structure associated with a table. It is a way to quickly access data and improve database performance.

Index category:

Single-Column or combined index: partitioned or non-partitioned Index

Unique or non-unique index: B-Tree Index

Function-based index: bitmap index, normal or reverse key index

(1) B-Tree Index: Standard Index, default Index

(2) unique and non-unique indexes:

A) unique index: there are no duplicate values. the keywords of the unique index can only point to a row in the table. a unique index is created when the primary key constraint and the unique constraint are created.

B) Non-unique index: A single keyword can have multiple rows associated with it.

(3) reverse key index: opposite to B-tree,

(4) bitmap index: suitable for low-base columns.

Advantages:

A) response time can be reduced for a large number of queries

B) compared with other indexing technologies, the occupied space is significantly reduced.

C) significant performance can be achieved even on poorly configured terminal hardware.

(5) other indexes:

A) composite index: create multiple columns in the table

B) function-based index: the function or expression used is creating one or more columns in the index table.

5. partitioned table

I. Oracle allows users to divide all rows in a table into several parts and store these parts in different locations. Partitioned Tables are called partition tables, and each part is called a partition.

Advantages of Table Partitioning:

Improve Table query performance

Easier to manage

Easy to back up and partition

Improve data security

Create Partition Table Conditions

"Data volume larger than 2 GB

"Existing data and newly added data have obvious boundaries.

Partition Table Category:

1. Range partitioning: Use the range of column values as the conditions for partitioning.

2. interval partitions: an enhancement of range partitions, which can automate range partitions.

Advantage: When you do not need to create a table, all partitions are clearly divided. As data increases, more partitions are divided and new partitions are automatically created.

Table partitions are used to manage and store data in large tables.

 

Summary:

A database consists of one or more tablespaces. Data in each tablespace is stored in one or more data files,

Partition methods include: range partition, hash partition, list partition, composite partition, interval partition, and virtual column partition.

 

03--PL/SQL programming

1. PL/SQL: An Extended Language that combines Oracle process language and structured query language.

Operators and expressions:

Declaration of constants and variables

Note :--,/**/

PL/SQL is a portable high-performance transaction processing language.

 

 

2. PL/SQL data type

1. scalar data type:

A) CHAR:

I. Character: Fixed Length string 0 ~ 32767

Ii. Nchar: National Language Character Set: Optional default value: 1

B) VARCHAR2

I. Varchar String variable String 0 ~ 32767

Ii. NVARCHAR2: National Language Character Set 0 ~ 32767

C) BINARY_INTEGER: A signed integer that optimizes the performance of integer computing.

D) NUMBER:

I. DEC: decimal

Ii. DOUBLE Precision: integer subtype

Iii. Integer: Integer subtype

Iv. INT: equivalent to NUmber

V. NUMERIC: equivalent to NUmber

Vi. REAL: the Integer is smaller than Integer.

Vii. Small int:

E) LONG: variable-length string

F) DATE: DATE

G) BOOLEAN: BOOLEAN Type TRUE FALSE NULL

2. LOB data type: the type used to store large data objects. Oracle mainly supports the BFILE, BLOB, CLOB, and NCLOB types.

 

3. attribute type

A) % Type:

B) % ROWTYPE:

 

3. PL/SQL control statements

There are three types of control structures:

1. condition control:

A)

2. Loop Control:

3. Sequence Control:

A) NULL statement: an executable statement

4. Exception Handling

An exception occurs when running the program.

5. Explicit cursor

Cursors can be divided into static cursors and dynamic cursors.

Static cursors can be classified into implicit, explicit, and REF cursors.

A) Steps for using an explicit cursor:

1. Declare a cursor

2. Open the cursor

3. Extract cursor

4. Close the cursor

Explicit cursor attributes:

'% FOUND: true is returned only when the DML statement affects the % FOUND attribute of one or more rows.

"% NOTFOUND: opposite to % FOUND

"% ROWCOUNT: returns the number of rows affected by the DML statement. returns 0.

'% ISOPRN: indicates whether the cursor is enabled.

6. Stored Procedure

1. Declaration Part

A) declarations include types, cursors, constants, variables, exceptions, and nested subprograms.

2. executable part

A) assign values, control the execution process, and operate Oracle Data statements

3. Exception Handling: including exception handling programs, responsible for handling exceptions during the execution of Stored Procedures

A) Modularization: the program can be decomposed into manageable and clear logic modules through subprograms.

B) reusability: After a subroutine is created and executed, it can be used in any number of applications.

C) maintainability: simplify maintenance operations of subroutines

D) Security: users can set permissions so that the only way to access data is through the stored procedures and functions provided by users.

4. Usage:

1. Create a stored procedure

2. Call the Stored Procedure

A) run the following command at the SQL prompt:

1. Execute the Stored Procedure

A) parameter transmission method:

I. Pass by position

Ii. Pass by name

Iii. Mixed Transfer

B) Calling in PL/SQL Blocks

3. Parameter mode of the stored procedure

4. Access Permissions for Stored Procedures

5. delete a stored procedure

5. specifications:

A) DDL statements cannot be directly used in stored procedures. They can be implemented through dynamic SQL.

B) corresponding error handling functions must be available

C. When referencing table fields in Stored Procedures, use the % TYPE and % ROWTYPE types.

D) You must handle exceptions in the stored procedure and output the exception information through the OS _MSG variable.

E) must contain two parameters: on_Flag (NUMBER) and OS _Msg (VarChar2)

F)-19999 ~ -1: The Exception Code defined by Oracle

G) "when others" must be placed at the end of the exception handling code as the default processor to handle exceptions that are not explicitly processed.