Oracle Learning "indexes and Triggers"

Index
B_tree structure Please refer to the response legend

An index is a tree structure that allows direct access to a row of data in a data table, and is introduced as a table-independent object in order to improve query efficiency, and can be stored in a table space different from the table.
Index keys and pointers to data in the table (address) are stored in the index record
I/O operations on indexes are much less than operations on tables
Once the index is established, it will be automatically maintained by the Oracle system, without specifying which index to use in the query statement
ROWID: Pseudo-column uniquely identifies a data record that can be understood as the address of a row record
Select Rownum,rowid,username from T_user;

When a primary key (primary key) or UNIQUE constraint (UNIQUE constraint) is established, the unique index is automatically created

When a data table is created, ROWID is automatically added to the table, which is automatically generated by the system

Create a table T_index
CREATE TABLE T_index (
ID number,
Username VARCHAR2 (100),
Password Varchar2 (48),
Gender Char,
Addtime Date
);

Inserting records
INSERT into T_index values (1, ' Owen ', ' 121212 ', ' m ', sysdate);

Create an index
Create INDEX Index_t_index on T_index (ID);

View the name and type of the current user's data object
Select Object_name,object_type from User_objects;

Delete Index
Drop index Index_t_index;

Create a stored procedure (loop Insert 1999 Records)
Create or replace procedure Proc_index
As
Begin
For I in 2..2000 loop
INSERT into t_index values (i, ' Owen ' | | I, ' 121212 ' | | I, ' m ', sysdate);
End Loop;
End

Executing stored procedures
exec Proc_index;

Open execution time-consuming
Set timing on;

-------------------------------------------------
Trigger Trigger
is one of the database objects, written in a similar way to a stored procedure, associated with a table that can cause execution of a trigger when DML statement operations are performed
The aim is to control the consistency, correctness and regularity of the insertion record.

Create a Trigger
Create or replace trigger trigger_t_trigger--keyword and name for creating triggers
What is the action on the table after the insert on t_trigger--, concrete execution time
For each row--the control is executed for every row of records
declare--statement
Begin
Dbms_output.put_line (' you inserted a piece of data ... ');--specific business logic code block for triggers
End

Create a table T_trigger
CREATE TABLE T_trigger (
ID number,
Username VARCHAR2 (100)
);

Insert a record
INSERT into T_trigger values (1, ' Owen ');

Create a Trigger
Create or replace trigger trigger_t_trigger--keyword and name for creating triggers
What action on the table the after insert or update is on t_trigger--, concrete execution time
For each row--the control is executed for every row of records
declare--statement
Begin
Dbms_output.put_line (' You have updated a piece of data ... ');--specific business logic code block for triggers
Dbms_output.put_line (' old.username= ' | |:o Ld.username);
Dbms_output.put_line (' new.username= ' | |:new.username);
End

Insert a record
Update T_trigger set username= ' Jack ' where id=1;

Delete Trigger
Drop trigger Trigger_t_trigger;

Case:
When the update operation is performed in the first table, it is worthwhile to modify the second table.
For example, the table T_index is also modified when the table T_trigger is modified

Create a Trigger
Create or Replace Trigger Trigger_t_trigger
After insert or update on T_trigger
For each row
Declare
Begin
Update t_index set username= ' Jack ' where id=1;
End

modifying data in a T_trigger table
Update T_trigger set username= ' Owen ' where id=1;

Homework:
Using Java and Oracle database connections
Do a crud operation, use stored procedures to add data (sequences), modify data, query individual data, use views to query list data

Create a table student
CREATE TABLE Student (
Stuid number,
Sname varchar2 (100),
Sage number
);

Create a sequence
Create sequence seq_student--Creating a sequence of keywords and sequence names

Insert a piece of data
INSERT into student values (seq_student.nextval, ' Owen ', 23);

Create a stored procedure that inserts data
Create or Replace procedure Proc_add (pp1 in varchar2,pp2 in number)
As
Begin
INSERT into student values (SEQ_STUDENT.NEXTVAL,PP1,PP2);
End

Executing stored procedures
Call Proc_add (' Owen ', 23);
---------------------------------
Create a deleted stored procedure
Create or Replace procedure Proc_delete (pp in number)
As
Begin
Delete from student where stuid=pp;
End

Executing stored procedures
Call Proc_delete (2);

-------------------------------------
To create a modified stored procedure
Create or Replace procedure proc_modify (pp1 in varchar2,pp2 in NUMBER,PP3 in number)
As
Begin
Update sudent set sname=pp1,sage=pp2 where stuid=pp3;
End

Executing stored procedures
Call Proc_modify (' Tim ', 21, 3);

---------------------------------------
Create a stored procedure that queries a single data
Create or Replace procedure Proc_search_one (pp1 in number,pp2 out varchar2,pp3 out number)
As
Begin
Select Sname,sage into PP2,PP3 from student where Stuid=pp1;
End

Executing stored procedures
Set serverout on;
var pp2 varchar2;
var pp3 number;
Call Proc_search_one (3,:p P2,:p p3);

---------------------------------------
Build view (does not show Stuid)
Create or Replace view V_student
As
Select Sname,sage from student;

Query view
SELECT * from V_student;

Oracle Learning "indexes and Triggers"