Oracle indexes, views, stored procedures, triggers, Oracle data types

1. Index: an index is a database structure that helps users quickly find records in a table.

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.

Creating index: Create index name on table name (column name 1, column name 2);

For example:

When to use the index: The table is large, and is often used in where, when the value of the column is large range;

No index is required: The table is not indexed for very hours and is updated frequently;

Single index and composite index:

------------Single index: CREATE index name on table name (column name);

------------composite Index: creat index name on table name (column name 1, column name 2); For example: Create index i_deptno_job on EMP (deptno,job); ------------index The DEPTNO and job columns of the EMP table.

　SELECT * from emp where deptno=66 and job= ' sals ' walk index.

SELECT * from emp where deptno=66-------------Walk index.

SELECT * from emp where job= ' sals '---------------for full table scan, do not go index.

If there is an or operator in the WHERE clause or a separate reference to the job column (the following column of the index column), the index will not go , and a full table scan will be performed.

2. Views: A view is a collection of data produced by one or more base tables that does not occupy storage space.

The ----------------------view does not hold data, but is simply a SELECT statement.

Creating a View: Create or Replace view view name as SELECT * from table name where condition;

For example:

View view: select* from view name;

For example:

--------------------Delete a view: Drop view ABC;

3. Stored procedure:

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.

(1) No reference:

Create or Replace procedure procedure name as

declaration paragraph;

Begin

Executes the statement segment;

exception

Exception handling statement segment;

End

(2) 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, process and main program bidirectional AC number

With parameter creation:

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;

Example:

Create or replace procedure Runbyparmeters_1 (
Isal in emp.sal%type,sjob in varchar)
As icount number;
Begin
Select COUNT (*) into icount from EMP where sal>isal and job=sjob;
Dbms_output. Put_Line (' Qualifying records are ' | | icount | | ' article ');
exception
When Too_many_rows Then
Dbms_output. Put_Line (' return value more than 1 rows ');
When others then
Dbms_output. Put_Line (' ERROR in runbyparmeters process! ‘);
End

The procedure folder inside the visualizer can be used to see if the process has been established successfully; there will be a difference if you don't succeed.

Then, in the Plsql, pass the parameters:

Execution in Plsql

Declare

Realsal Emp.sal%type;

Realjob varchar (40);

Begin

realsal:=1100;

realjob:= ' clerk ';

Runbyparmeters_1 (Realsal,realjob);

--Runbyparmeters_1 (sjob=>realjob,isal=>realsal);

END;

4. Trigger:

A trigger is a special type of stored procedure that consists of some SQL statements that are primarily used to enforce mandatory business rules or requirements, but do not return results.

triggering an event or statement : The SQL statement that caused the trigger to be fired, which is an insert, update, or DELETE statement executed on the specified table .

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

--Create a trigger

Create or Replace Trigger Tri_test

before--before triggering

Update or delete--updates or deletes

On EMP

For each row--operation on a row

Begin

Dbms_output.put_line (: old.sal);--old indicates database old value

INSERT into demo (ID) VALUES (: new.sal);--new New value

End

Update emp set sal=888 where empno=7788;

Commit

after the trigger code is created, the last UPDATE statement is executed. When the EMP table is updated, the values stored in the database will be output, and a statement is inserted into the demo table by triggering the Add statement.

For example:

Create or Replace Trigger Tri_test

Before

Update or DELETE or insert

On EMP

For each row

Begin

Case

When updating then

Raise_application_error (-20000, ' cannot be modified today ');

When inserting then

Raise_application_error (-20001, ' cannot be inserted today ');

When deleting then

Raise_application_error (-20000, ' cannot be deleted today ');

End case;

End

Trigger Trigger when modified

Update emp set sal=777 where empno=7788;

INSERT into EMP (EMPNO) VALUES (123);

Commit

5. Data type

--Create a table

CREATE TABLE UserInfo

(

ID Number (6,0),

Usernam VARCHAR2 (20),

Userpwd varchar2 (20),

Email VARCHAR2 (30),

RegDate Date

);

Delete a table

--delete the structure of the table

DROP TABLE table_name;

Add Field

ALTER TABLE table_name add column_name datatype;

TABLE_NAME is the table name, COLUMN_NAME is the column name, datatype is the data type

4. Modify table field name and name

--Renaming a field

ALTER TABLE table_name rename column column_name to new_column_name;

--Modify the name of the table

Rename table_name to new_table_name;

5. Delete a field

ALTER TABLE table_name DROP COLUMN column_name;

6. Change the field length, type

Eg: Modify the length of the field modified by the Remarks field

ALTER TABLE UserInfo Modify remarks varchar2 (150);

Eg: modifying field types

A. If there is no data in the table

ALTER TABLE name Modify (field name 1 type , field name 2 type , field name 3 type ...)

B. If there is data in the table (divided into four steps)

--Modify the original field name

ALTER Table name RENAME COLUMN field name A To field name B;

--Add a field named "Field name a" with the same name as the original field and add the fields

ALTER table name ADD field name target type;

--Update the data in field name B to the new field, field name a

UPDATE table name SET field name a = tirm (field name B);

--After the update is complete, delete the original backup field "field name B"

ALTER Table name DROP COLUMN field name b

Oracle indexes, views, stored procedures, triggers, Oracle data types