Oracle database objects

Oracle databases have many objects, such as tables, views, indexes, functions, triggers, and stored procedures.
 
Table, the most important object in the database
 
If the data size of a table is large, you can consider the Oracle partition table to improve performance and balance IO.
 
Database Object-View
 
Create view avgsal

As
 
Select d. dname, count (*), round (avg (e. sal), 0) from emp e, dept d where e. deptno = d. deptno group by d. dname;
 
Query views of the current user
 
Select * from user_views;
 
Database Object-Sequence
 
A Sequence is a database object that can generate a unique integer. The values of a sequence are automatically generated by the Oracle database. The sequence can be defined as auto increment or decrease.
 
Create sequence testid start with 1 increment by 1 maxvalue 9999999 nocache nocycle;
 
Select testid. nextval from dual;
 
Select testid. currval from dual;
 
 
Database Object-Function
 
There are two types of functions: one is the functions of the Oracle database and the other is the functions compiled by the user.
 
Example: get Employee Salary Based on employee ID
 
Create or replace function get_empsal (emp_noin number)
 
Return number is emp_sal number (7,2 );
 
Begin
 
Selectsal into emp_sal from emp where empno = emp_no;
 
Return (emp_sal );
 
End;
 
 
 
Execute a function
 
Select get_empsal (7369) from dual;
 
 
 
Database Object-Stored Procedure
 
Stored Procedure is a set of SQL statements created to complete specific functions. It is compiled and Stored in the database. You can run a stored procedure by specifying its name and giving a parameter (if any.
 
 
 
Example: delete employee information based on employee ID
 
Create or replace procedure DelEmp (empid innumber) is

Begin
 
Deletefrom emp where emp. empno = empid;
 
Commit;
 
End DelEmp;
 
Execute the Stored Procedure
 
Execute DelEmp (7369 );
 
 
 
Database Object-Index
 
An Index is a structure used to sort the values of one or more columns in a database table. Indexes can speed up database queries.
 
 
 
Example:
 
Create a table and insert data in batches to compare the query speed with or without indexes.
 
Create table test_index (id number, namevarchar2 (200 ));
 
Create a stored procedure and insert data to the test_index table cyclically.
 
Create or replace procedure insert_data is
 
Test_name varchar2 (20): = 'Dragon ';
 
Begin
 
Fori in 1 .. 10000000 loop
 
Insertinto test_index (id, name) values (I, test_name );
 
Endloop;
 
Commit;
 
End;
 
 
 
Execution of stored procedures (long time)
 
Execute insert_data;
 
 
 
Verify that 10 million pieces of data are inserted into the test_index table.
 
Select count (*) from test_index;
 
 
 
The time required to query data with the ID of 100 without an index.
 
Use PL/SQL Developer to view time consumption
 
Select * from test_index where id = 100;
 
Because the test_index table does not have an index for the ID column, it takes a long time to perform a full table scan when querying the data on the 100 th.
 
Full table scan: when the computer queries data, all data rows from 1 to 10 million are scanned.
 
 
 
Create a table index (time-consuming)
 
Create index test_index_id on test_index (id );
 
 
 
Measure the test taker's knowledge about the time required to query data with an ID of 100 when indexing is used.
 
Select * from test_index where id = 100;
 
 
 
It can be seen that the query time is greatly reduced after the index is used.
 
 
 
Database Object-Synonym
 
Synonym is an alias of an existing object. It can be classified into private synonyms and public synonyms. If a table name is too long or you need to add a username as the prefix to access a table of another user, you can use an alias to solve this problem.
 
Check whether the current user has the permission to create a synonym. If not, authorize it.
 
Select * from session_privs where privilegelike '% SYNONYM % ';
 
 
 
Create a synonym for the test_index table
 
Create synonym test for test_index;
 
 
 
Query with synonyms
 
Select count (*) from test;
 
 
 
Public synonyms. Other users access scott's test_index table through public synonyms.
 
First, use the sys account to create a user.
 
Create user dragon identified by oracle;
 
Grant connect to dragon;
 
Grant select on scott. test_index to dragon;
 
 
 
Select count (*) from scott. test_index;
 
The dragon user needs to add the prefix 'Scott. 'to query scott's test_index table .'
 
Public synonyms do not require a prefix.
 
Create public synonym test_public fortest_index;
 
Switch to dragon user test
 
Select count (*) from test_public;
 
 
Differences between functions and stored procedures
 
1. The function must have a return value. The process does not have a return value.
 
2. The function can be executed independently. The process must be executed through execute.
 
3. functions can be embedded and executed in SQL, but the process cannot.
 
You can write complex queries as functions and call these functions in the stored procedure.