Oracle Simple Summary

1. Index

① in a relational database table, each row in the table is identified with a rowid in order to be able to find the data . RowID tells the database the exact location of the row, including the file where it resides, the block in the file, and the address of the row in the block.

The ② index is the database structure that helps users find records quickly in a table. It can both improve database performance and ensure the uniqueness of column values.

How the ③ index is created

A) automatically create an index: when you define a primary key or uniqueness constraint for a table, a unique index is created.

b) Manually create indexes: users can create their own indexes.

Create an index on one or more columns

CREATE Index index on table (column[, column] ...);

When to create an index:

The column is used frequently in the where statement.

The column contains values that have a large range of values.

When there are two or more columns in a where statement that are used frequently together.

The table is large.

When you don't need to create an index:

The table is very hour.

This column is not used frequently in queries.

The table is constantly being updated.

Function-based index: is an expression-based index, built on table columns, SQL functions, and so on.

2. Synonyms:

A synonym is an alias for an object that can be accessed by multiple users without having to add the owner's name as a prefix. A synonym is a pointer to a Oracl object that itself does not contain its own data.

3. View:

A view is a collection of data that is produced by one or more base tables, and it does not occupy storage space.

Views are like the windows of a database, and when the administrator defines these "Windows", the user can only view the data that he sees. A view is not a data table, it is just a collection of SQL query statements that extract different data from a data table according to different requirements.

The view does not hold data, but is simply a select statement. For users, it is thought to work in a table if they do not tell that they are operating in the view. Using views can enhance the security of your data tables and the complexity of hidden data.

Unlike a table, a view is a virtual table, that is, the data for the view is not actually stored, only the definition of the view is stored in the database, and when the data on the view is manipulated, the table associated with the view is manipulated according to its definition.

The difference between a view and a table:

In the same place:

1. The view and the table are composed of columns, which are queried in exactly the same way as the table.

2. As with tables, users can insert, update, or delete data in a view that is the same in a view as in a table.

Difference:

1. Unlike tables, there is no data in the view, just a SQL query statement. The data retrieved by this query statement is represented as a table. The columns in the view can be found in one or more base tables. Views do not use physical storage locations to store data.

2. The definition of the view (the arrangement of the columns, the permissions granted, and so on) is stored in the data dictionary. When a view is queried, the view queries its base table and returns values in the format and order specified by the view definition.

3. Because the view has no directly related physical data, it cannot be indexed like a table.

4. Subqueries can contain complex select statements , and subqueries cannot contain an ORDER by statement.

4. sequence

A sequence is a database object that can be used by multiple users to automatically generate a series of unique (ordered) numbers. The sequence is a shared object. is typically used to generate primary key values.

Nextval returns the value of the next sequence. Returns one value at a time.

Currval the current sequence value.

Each time a pseudo-column of a sequence is nextval, a new sequence code is generated according to the definition of the sequence, and the current sequence code can be reused through the sequence pseudo-column currval.

Applicable occasions:

1. In the VALUES clause of the INSERT statement

2. In the table column name that was previously selected in the SELECT statement

3. SET clause in UPDATE statement

Non-applicable occasions:

1, sub-query

2. Query in view definition

3. Select statement with distinct operator

4. Select statement with GROUP BY or ORDER BY clause

5. In the WHERE clause of the SELECT statement

6, check the constraint conditions

PL/SQL Programming

5. Cursors

A cursor is data extracted from a data table, stored in memory as a temporary table, a data pointer in the cursor, a first record in the initial state, a fetch statement that moves the pointer, a variety of operations on the data in the cursor, and then writes the result of the operation back to the data table.

definition of ① cursors

cursors, as a data type, must first be defined with the following syntax.

The cursor cursor name is a select statement:

The cursor is the keyword that defines the cursor, andSelect is the Data Table Query command that establishes the cursor.

② The opening of a cursor

To open a cursor with a created cursor, the syntax is as follows.

Open cursor name;

The procedure for opening a cursor has the following two steps.

1) , the eligible records are fed into memory.

2), pointing the pointer to the first record

extraction of ③ cursor data

To extract data from a cursor, you need to use the FETCH command in the following syntax.

Fetch cursor name into variable name 1, variable name 2, ...   ; or

Fetch cursor name into record type variable name;

④ the cursor is closed

After using the cursor, you need to close the cursor, using the Close command, as follows.

Close cursor name

Important properties of Cursors

①%isopen Property

Property function: Tests whether the cursor is open or not, and uses a FETCH statement to prompt for an error if no cursor is opened .

② %found

logical value, whether the cursor finds a record. If the cursor finds a record whose value is true, the reverse is False

③%notfound

The logical value, the cursor does not find the record, is the logical non of the%found property.

④%rowcount

Returns the number of rows that fetch a cursor record

6. process and function

Procedure procedure is also called a stored procedure, which is a single executable unit that is combined by SQL statements and A/ PL Statement to perform a task. We can call it when we use it, and the procedure does not return a value.

function functions and procedures are similar in structure, and are an executable unit that has SQL statements combined with a PL/SQL statement to perform a task. The difference between a procedure and a function is that the function always returns a single value to the caller, and the procedure has no value returned to the caller.

Creation of a non-parametric process

Grammatical structure

Create or Replace procedure procedure name as

declaration statement segment;

Begin

Executes the statement segment;

exception

Exception handling statement segment;

End;

Creation of process with parameters

① parameter types

You can have parameters of type 3 in the PL/SQL process .

in Parameter: read-in parameter, the main program passes the parameter value to the procedure.

out parameter: read out the parameter, the procedure passes the parameter value to the main program.

In out parameter: Bidirectional parameter, the process communicates with the main program two-way data.

② defining syntax with parametric procedures

Create or Replace procedure procedure name (

parameter 1[in|out|in out] data type

[, parameter 2[in|out|in out] data type ]

......

[, parameter n[in|out|in out] data type ]

)

(Is|as)

declaration statement segment;

Begin

executes the statement segment;

Exception

exception handling statement segment;

End;

Execution of the process

① use begin. End Execution

Begin

process name;

End

③ using Execute execution

Execute Procedure name

7. Trigger

A trigger is a special type of stored procedure that consists of a number of SQL statements that are primarily used to enforce a mandatory business rule or requirement, but do not return a result. When the action on the database triggers the trigger's condition, the system automatically executes the SQL statement set in the trigger to complete some specific functions. When the data table is modified, the trigger associated with it is implicitly fired for execution. You can define a trigger for an INSERT,UPDATE, or DELETE operation on a table.

Structure of the trigger

Trigger an event or statement: An SQL statement that causes the trigger to fire , an INSERT, UPDATE, or DELETE statement that executes on the specified table.

Trigger limit: A Boolean expression that must be true when the trigger fires. The limit for a trigger is specified by the When clause.

Action of a trigger: a PL /SQL block (procedure) consisting of a statement of both a and Pl When the trigger statement is issued, the limit of the trigger evaluates to TRUE when it is executed. In the statement of the trigger action, the column values of the current row (including new and old values) that are processed by the trigger are used in the form: New. Column name (reference new value) or old . Column name (reference old value).

Classification of triggers

Classification by activation-triggered action

Activation trigger action refers to the operation of the data table when activating the trigger, divided into 3 categories:

　　Insert Trigger: Executes a trigger action when data is inserted into the data table.

Update Trigger: Executes a trigger action when data is updated on a data table.

Delete trigger: Executes a trigger action when data is deleted from the data table.

Creation of triggers

A trigger can be created using create TRIGGER. Unlike procedures, each trigger must be related to a particular table, and each trigger is related only to one or more actions that are acting on that table.

You can name triggers with any names you want to use, but each trigger must be named by a unique name. It is recommended that you include the tables and actions associated with the trigger within the name of the trigger.

• Creation of triggers

Create a trigger below the Enterprise Manager.

• Changes to triggers

Under Enterprise Manager, right-click the trigger name to select ' View / Edit details '

• Deletion of triggers

Under Enterprise Manager, right-click to remove

• Activation of triggers

ALTER TRIGGER "HR"." temptrigger"

ENABLE

if you want to enable all the triggers for a data table, you can use the following SQL code.

ALTER TABLE "HR"." EMPLOYEES"

ENABLE all TRIGGERS;

• Deactivation OF triggers

the deactivation of the trigger can be done by executing the following SQL statement.

ALTER TRIGGER "HR"." temptrigger"

DISABLE

Oracle Simple Summary