ORACLE indexes, views, cursors, stored procedures, and triggers

1 Index

The goal is to improve the speed of the query, when the user is not satisfied with the query speed and needs to adjust the performance of the database, the priority is to establish an index.

The concept of indexes in a database is very similar to the concept of a book index, except that database indexes are used to find specific rows in a table.

Index disadvantage: When adding/removing rows to a table, additional time must be spent to update the index of the row.

When you create an index: You should create an index on a column when you need to retrieve a few rows from a large table.

Basic guideline: Indexes are useful when any single query to retrieve rows <= 10% of the entire table row count. Auto-Create INDEX: The primary key and unique key of the table will automatically create the index.

1.1 Syntax

Create [unique] Index name-------unique value in the specified index column is unique, the index name is suggested to begin with IDX

On table name (column 1, column 2 ...)--------can create indexes on multiple columns, which are called composite indexes

[Tablespace table space name]; Once the--------is omitted, the index is stored in the user's default table space

Tip: For performance reasons, you should typically store the index with a table in a different table space. Example: Create index idx_customers_lastname on customers (last_name);

1.2 Querying index and index column information

SELECT * FROM User_indexes where table_name in (' CUSTOMERS ', ' EMPLOYEES ');

1.3 Modifying indexes

Alter INDEX index name rename to new index name.

1.4 Deleting an index

Drop index index name.

2. Views (view) 2.1 Concepts

A view-----is a "logical table" defined by a SELECT query statement (which can be a single-table or multiple-table query), defined without data, and is a "virtual table". When you create a view, just the definition information for the view

Saved in a data dictionary without copying the actual data anywhere, that is, you do not need to allocate storage space for the view in the tablespace. A view is a way to view and manipulate the base table data as you would with table one

Use the view as you like. All operations on the view will affect the base table of the view; To prevent users from indirectly modifying the data of the base table through the view, you can create the view as read-only view (with the with read

Items). The data in the view is updated automatically as the base table is updated. A view is like a "window" of a base table, and with this "window", many management can be implemented. The maximum number of columns that can be defined in a view is 1000,

Is the same as the table's limit. View constraints: Allows you to generate constraints on the view, such as primary key constraints, unique key constraints, FOREIGN KEY constraints, check constraints, and so on. The syntax of a view constraint is the same as a table. When you create a view, you can make

Use the WITH CHECK option to define a check constraint on the view so that it can only query and manipulate the row of records that satisfy the check constraint.

2.2 Effects

1) provide a variety of data representation, you can use a variety of different ways to display the data of the base table in front of the user in order to conform to the user's habits (primary means: using aliases).

2) perform some queries that must use the view. Some queries must be done with the help of a view. For example, some queries need to connect a grouped statistic table and another table, which can be based on group statistics first

Result creates a view and then joins the view and another table in the query.

3) Provide certain security guarantees. Views provide a way to control how different users can see different columns without allowing access to sensitive columns, which ensures that sensitive data is not

See.

2.3 Creating a View

Permissions: To create a view in the current scenario, the user must have the CREATE VIEW system permission; To create a view in another scenario, the user must have the Create any view system permission. The functionality of the view depends on

The permissions of the owner of the view.

Syntax: Create [or replace] [force] view [schema.] view_name [(Column1,column2,...)]

As SELECT ... [WITH CHECK option]   [Constraint constraint_name] [with Read Only];

Explanation: or replace: If a view with the same name exists, use the new View "override" Existing view force: "Enforce" to create a view regardless of whether the base table exists or whether you have permission to use the base table col

Umn1,column2,... : The column name of the view must be the same as the number of columns in the Select query; If a select query contains a function or an expression, you must define a column name for it. At this point, you can either use Col

Umn1, Column2 Specifies the column name, or you can specify the column name in the select query. With CHECK option: Specifies that DML operations performed on a view must satisfy the condition of the

The deletion and modification of the operation to "check", the need to delete and change the operation of data, must be the Select query can query the data, otherwise do not allow operation and return error prompt. With Read only: Created View only

can be used to query data, not to change data.

Example of creating a simple view: Creating a Vw_emp view based on an EMP table the CREATE VIEW vw_emp as select Empno,ename,job,hiredate,deptno from EM

P Desc vw_emp; --------used as a table

SELECT * from Vw_emp where deptno=10;--------Query

INSERT into vw_emp values (1234, ' JACK ', ' Clerk ', ' 2 September-April -1963 ', ten);--------increase

Update vw_emp set ename= ' Andy Lau ' where ename= ' JACK ';--------updates

Delete vw_emp where ename= ' Andy Lau '; --------Delete

2.4 Deleting a view

You can delete any view in the current mode, and if you want to delete a view in another mode, you must have the drop any view system permission, and the view will be deleted from the dictionary when the view is removed.

And the permissions granted on that view will also be deleted. When the view is deleted, other views and stored procedures that refer to the view will be invalidated. Example 1:drop view Vw_test_tab;

2.5 View View

Example 1: Querying information for all views in the current scenario Desc user_views; Set long 400; Select View_name,text from User_views;

Example 1: Querying column name information for the specified view (or table) in the current scenario select * from User_tab_columns where table_name= ' vw_dept ';

3 Cursors

A cursor is a private SQL workspace, an area of memory. The data that is used to store the affected SQL statement is a virtual table.

3.1. Data operations that require cursors

When the result of a SELECT statement contains multiple tuples, the cursors can be used to access the tuples individually.

Activity set: The collection of tuples returned by the SELECT statement

Current line: The row currently being processed in the active set. The cursor is a pointer to the current row.

3.2. Cursor classification

Scroll cursor: The position of the cursor can be moved back and forth, and any tuple can be taken in the active set.

Non-scrolling cursors: Only a tuple can be removed sequentially in the active set.

Update cursor: The database locks the current row that the cursor points to, and when the program reads the next line of data, the bank data is unlocked and the next row of data is locked.

3.3. Define and use a cursor statement

DECLARE:

DECLARE cursor name [scroll] cursor FOR SELECT statement [for UPDATE [of list name]]

Define a cursor so that it corresponds to a SELECT statement

For UPDATE option, which indicates that the cursor can be used to modify and delete the current row

Open

Opens a cursor, executes the query corresponding to the cursor, and the result set is the active set of the cursor

Open cursor Name

Fetch

Move the cursor to a specific row in the active set and drop the row data into the appropriate variable

Fetch [Next | prior | first | last | current | relative n | absolute m] CURSOR name into [variable table]

Close

Closes the cursor, releasing the active set and the resources it occupies. When you need to use the cursor again, execute the Open statement

Close cursor Name

Deallocate

Delete the cursor and no longer execute the open statement on the cursor

DEALLOCATE cursor Name

@ @FETCH_STATUS

Returns the state of the last cursor executed by the FETCH statement.

0 FETCH statement Succeeded

-1 FETCH statement failed

-2 rows that were fetched do not exist

Example: Inquiry E-Commerce Department student Information, gender for female output for female, otherwise output for male?

Declare c1 cursor FOR select Sno,sname,ssex from student where sdept= ' EC '

Declare @sno char (TEN), @sname char (Ten), @ssex char (2)

Open C1

Fetch C1 into @sno, @sname, @ssex

While @ @fetch_status ==0

Begin

If @ssex = ' female '

Begin set @ssex = ' Female ' end

Else

Begin set @ssex = ' Male ' end

Select @sno, @sname, @ssex

Fetch C1 into @sno, @sname, @ssex

End

4, Stored Procedure 4.1 definition

: In a large database system, a set of SQL statements to complete a particular function, compiled and stored in a database, is executed by the user by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters).

4.2 Format

Create or Replace procedure stored procedure name (param1 in type,param2 out type)

As

Variable 1 type (value range);

Variable 2 type (value range);

Begin

Statement block

Exception--Exception handling

When others then

Rollback;

End;

4.3 Precautions:

A, stored procedures are generally used to complete data querying and processing operations, so you cannot use statements that create database objects in stored procedures.

b, parameters in the stored procedure: in means passing parameters to the stored procedure, out means returning parameters from the stored procedure, in-out means passing parameters and returning parameters;

4.4 Use examples

Defined:

Create or Replace procedure Withparpro (para_1 in number,para_2 out Number,para_3 on out number)

As

Temp1 number (20);

BEGIN

temp1:=para_1+10;

IF Temp1<para_3 then para_2:=para_3;

ELSE

PARA_2:=TEMP1;

END IF;

Dbms_output.put_line (para_2);

EXCEPTION

When OTHERS Then

ROLLBACK;

END;

Execute the stored procedure:

Declare

V_TEMP1 number (10);

V_TEMP2 number (10);

Begin

v_temp2:=10;

Withparpro (1,V_TEMP1,V_TEMP2);

End

/

5, Trigger

A trigger is a special stored procedure, especially when a trigger does not need to be started by an action called by an artificial (example: in a program), and the trigger is automatically executed by the execution of the event (delete, update, etc.)

Started.

5.1. Define the format

CREATE [OR REPLACE] TRIGGER trigger_name

{before | After}

{INSERT | DELETE | UPDATE [of column [, Column ...]}

[OR {INSERT | DELETE | UPDATE [of column [, Column ...]} ...]

on [schema.] table_name | [Schema.] View_name

[Referencing {old [as] old | NEW [as] new| Parent as parent}]

[For each ROW]

[When condition]

Pl/sql_block | Call procedure_name;

5.2. Trigger classification

Insert class trigger ... insert remove class trigger ... delete; Update class trigger ... updates

5.3. Basic points of DML triggers

Trigger Timing : Specifies the trigger time of the trigger. If specified as before, it is triggered before a DML operation is performed to prevent some error operations from occurring or to implement some business rules, if it is specified as AF

TER, which indicates that the action is triggered after the DML operation is performed to record the operation or to do some post-processing.

Trigger Event : The event that caused the trigger to be triggered, the DML operation (INSERT, UPDATE, DELETE). Can be either a single trigger event or a combination of multiple triggering events (only with OR

Logical combination, which cannot be combined with and logic).

Conditional predicates : When a combination of multiple triggering events (INSERT, UPDATE, DELETE) is included in a trigger, in order to perform different processing for different events separately, you need to use Oracle to provide

The following conditional predicate.

1), INSERTING: Evaluates to True when the trigger event is insert, false otherwise.

2), UPDATING [(column_1,column_2,..., column_x)]: When the trigger event is update, if the column_x column is modified, the value is true, otherwise false.

where column_x is optional.

3), DELETING: When the trigger event is delete, the value is true, otherwise false.

Jie Fa object: Specifies which table or view the trigger is created on.

Trigger type: Is a statement-level or row-level trigger.

Trigger condition: Specifies a logical expression by the When clause, allowing only the trigger condition to be specified on a row-level trigger, specifying the list of columns following updating.

5.4 Where to note when writing triggers:

The trigger does not accept parameters.

There can be up to 12 triggers on a table, but only one for the same time, the same event, and the same type of trigger. And there can be no contradiction between the triggers.

The more triggers on a table, the greater the performance impact on the DML operations on that table.

The maximum trigger is 32KB. If you do, you can create a procedure and then invoke it in a trigger with a call statement.

You can only use DML statements (SELECT, INSERT, UPDATE, DELETE) in the execution portion of a trigger, and you cannot use DDL statements (CREATE, ALTER, DROP).

The trigger cannot contain a transaction control statement (Commit,rollback,savepoint). Because the trigger is part of the trigger statement, when the trigger statement is committed, rolled back, the trigger is also committed, returned

Back up.

You cannot use transaction control statements for any procedure or function that is called in the body of a trigger.

You cannot declare any long and BLOB variables in the body of the trigger. The new value, old, and both cannot be any long and BLOB columns in the table.

Different types of triggers, such as DML triggers, INSTEAD of triggers, system triggers, have a greater difference in the syntax format and function.

5.5 Example:

Set up a trigger, when the Employee table EMP table is deleted a record, the deleted records are written to the Staff table delete log table.

CREATE TABLE Emp_his as SELECT * from EMP WHERE 1=2;

CREATE OR REPLACE TRIGGER tr_del_emp

Before Delete--Specifies that the trigger time is triggered before the delete operation

On Scott.emp

For each row--the description creates a row-level trigger

BEGIN
--Insert the pre-modified data into the Log record table del_emp for monitoring use.

INSERT into Emp_his (Deptno, empno, ename, Job, Mgr, Sal, Comm, HireDate)

VALUES (: Old.deptno,: Old.empno,: Old.ename,: Old.job,:old.mgr,: old.sal,: Old.comm,: old.hiredate);

END;

DELETE emp WHERE empno=7788;

DROP TABLE Emp_his;

DROP TRIGGER del_emp;

ORACLE indexes, views, cursors, stored procedures, and triggers