[database] Oracle

Time:2021-6-9

1. Oracle database installation

1.1. Integrate PL / SQL Developer Software

1. Click Tools > Preferences > connection to fill in the connection information

 

2. Connect to Oracle database. The default connection method is as follows:

192.168.88.6: the address of the server where the Oracle database is installed;

1521: default port number

Orcl: default Oracle installation database name

 

3. Connect by modifying tnsnames.ora file configuration mode:

① Copy tnsnames.ora in the Oracle installation directory of the remote server to the local instantclient_ 12_ 1 Software Directory:

 

② Modify the tnsnames.ora configuration of local replication as follows:

 

③ Set the local tnsnames.ora to the local environment variable configuration

After configuring the environment variables, continue to log in PL / SQL to connect to the Oracle database, and then you can use orcl to connect to the database.

 

2. Oracle architecture

2.1. Database

Oracle database is the physical storage of data. This includes (data file ora or DBF, control file, online log, parameter file). In fact, the concept of Oracle database is different from other databases. The database here is an operating system with only one database. We can think of Oracle as having only one big database.

 

2.2. Examples

An Oracle instance consists of a series of background processes and memory structures. A database can have n instances.

 

2.3. Users

The user is created under the instance. Different instances can create users with the same name.

 

2.4. Table space

Table space is the logical mapping of Oracle to related data files (ORA or DBF files) on the physical database. A database is logically divided into one to several table spaces. Each table space contains a logically related set of structures. Each database has at least one table space (called system table space).

Each table space consists of one or more files on the same disk. These files are called datafiles. A data file can only belong to one table space.

 

2.5. Data files (DBF, ora)

Data file is the physical storage unit of database. The data of the database is stored in the table space, which is really in one or more data files. A table space can be composed of one or more data files, and a data file can only belong to one table space. Once a data file is added to a table space, it cannot be deleted. If you want to delete a data file, you can only delete the table space to which it belongs.

Note: the data of a table is put into a table space by a user, and the table space will randomly put the table data into one or more data files.

Because Oracle database is not a common concept, Oracle has user and table space to manage and store data. But the table is not a table space to query, but by the user to query. Because different users can create a table with the same name in the same table space! Here the distinction is the user.

 

3. Create a table space

Table space is the logical unit of Oracle database. Database — table space, a table space can be associated with multiple data files (physical structure).

Multiple table spaces can be created in one database, multiple users can be created in one table space, and multiple tables can be created in one user.

create tablespace mytable
datafile 'c:\mytable.dbf'
size 100m
autoextend on
next 10m

among

Mytable: the name of the table space

Datafile: Specifies the data file corresponding to the table space

Size: defines the initial size of the table space

AUTOEXTEND on: automatically grow when the table space is full

Next: Specifies the size of an automatic growth

 

After writing the SQL statement, click the pinion button and execute it in Excel.

The following statement isCreate a table spacesentence:

 

Here’s howDelete table spacesentence:

 

4. Users

Create and authorize users:

 

 Switch login users:

First select log off to log out of the current user, and then select log on to log in; Modify the login user name and password to login;

 

5. Oracle data type

No data type describe
1 varchar,varchar2 Represents a string of variable length. The common type is VARCHAR2
2 NUMBER Number (n) represents an integer, the length of which is n
3 NUMBER Number (m, n) represents a decimal, the total length is m, the decimal is n, and the integer is m-n
4 DATA

Represents the date type

5 CLOB Large object, representing large text data type, can store 4G
6 BLOB Large object, representing binary data, can store 4G

 

 6. Table management

6.1. Table construction

Grammar:

Create table name(

Field 1 data type [default],

Field 2 data type [default],

  …

)

Example:

 

6.2. Modify table structure

Add a column of data, if (N1, N2) is used for multi column data:

---Modify table structure
---Add a column
alter table person add (gender number(1));

View the table structure after adding:

 

 

 

Modify column type:

---Modify column type
alter table person modify gender char(1);

Modify column name:

---Modify column name
alter table person rename column gender to sex;

Delete a column:

---Delete a column
alter table person drop column sex;

 

6.3. Data addition, deletion and modification

Add data insert:

---Add a record
Insert into person (PID, pname) values (1, 'Xiaoming');
commit

Modify data update:

---Modify a record
Update person set pname ='pony 'where PID = 1;
commit;

Delete / truncate / drop:

---Three deletions
---Delete all records in the table
delete from person;
---Delete table structure
drop table person;
---Delete the table first and create it again. The effect is equivalent to deleting all records in the table
---In the case of large amount of data, especially in the case of index in the table, the operation efficiency is high.
---Index can provide query efficiency, but it will affect the efficiency of addition, deletion and modification
truncate table person;

 

6.4. Sequence

---A sequence does not really belong to any table, but it can be logically bound to a table
---Sequence: it starts from 1 by default and increases in turn. It is mainly used to assign values to primary keys
---Dual: empty table, just to complete grammar, without any meaning
create sequence s_person;
select s person.currval from dual;

---Add a record
insert into person (pid, pname) values (s_ Person. Nextval, 'Xiaoming');
commit;
select * from person;

 

6.5. Scott user profile

Scott is a user for beginners to learn. Learners can log in to the system with Scott. Note that after Scott logs in, he can use the database and data table provided by Oracle. These are provided by Oracle. Learners do not need to create their own database and data table, but directly use these databases and data tables to practice SQL.

---Scott, the password is tiger
---Unlock Scott user
alter user scott account unlock;
---Unlock Scott user的密码【此句也可以用来重置密码】
alter user scott identified by tiger;

 

7. Oracle query

7.1. Single line / multi line function

Single line function: acts on a line and returns a value.

Multiline function: acts on multiple lines and returns a value.

Character function

select upper('yes') from dual;  -- YES
select lower('YES') from dual;  -- yes

Numerical function

select round(26.16, 1) from dual;  --  Rounding, the following parameter represents the number of digits reserved, and the result is 26.2
select round(26.16, -1) from dual; --  Keep one decimal place forward and the result is 30
select trunc(56.16, 1) from dual; --  Direct interception, no longer see whether the number of the last digit is greater than 5. fifty-six point one
select mod(10, 3) from dual;  --  Mod 

Date function

---Find out the EMP table of all employees from the current few days
select sysdate-e.hiredate from emp e;
---Figure out tomorrow at the moment
select sysdate+1 from dual;
---How many months is the entry distance of all employees in EMP table
select months_between(sysdate, e.hiredate) from emp e;
---Find out how many years is the entry distance of all employees in EMP table
select months_between(sysdate, e.hiredate)/12 from emp e;
---Find out how many weeks is the entry distance of all employees in EMP table
select round((sysdate-e.hiredate)/7) from emp e;

Transfer function

Date to string

Note: FM means that if it is in “04” format, 0 will be automatically omitted in front of it and displayed as “4”;

---Transfer function
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;

 

String to date

---String to date
select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual;

 

Multiline function (aggregate function)

---Multiline function [aggregate function]: acts on multiple lines and returns a value.
select count(1) from emp;   --- Total number of queries
select sum(sal) from emp;   --- Total wages
select max(sal) from emp;   --- Maximum wage
select min(sal) from emp;   --- minimum wage
select avg(sal) from emp;   --- average wage

 

7.2. General functions

---General function
---Calculate the annual salary of all employees in EMP table
---There is a null value in the bonus. If the null value and any number do arithmetic operation, the result will be null
select e.sal*12+nvl(e.comm, 0) from emp e;

The results are as follows

 

7.3. Conditional expression

The following two expressions are general expressions of MySQL and Oracle

The first one is the first one

---Conditional expression
---Give Chinese names to employees in EMP table
select e.ename,
       case e.ename
           When 'Smith' then 'Cao thief'
           When 'Allen' then 'big eared thief'
           When 'ward' then 'Zhuge children'
           Else 'nameless' --- note: if else is not written, it will be null
           end
from emp e;

 

Second:

---Judge the employee's salary in EMP table. If it is higher than 3000, it shows high income; If it is higher than 1500 and lower than 3000, it shows middle income; The rest show low income
select e.sal,
    case
     When e.sal > 3000 then 'high income'
     When e.sal > 1500 then 'middle income'
     Low income
    end
from emp e;

 

The following is the special condition expression in Oracle:

Among them, Chinese name is alias, double quotation marks are used for alias, and single quotation marks are used for others.

---Oracle special condition expression
select e.ename,
    decode(e.ename,
          'smith ','cao thief',
          'Allen','Big eared thief ',
          'ward','zhuge Xiaoer ',
          Chinese name
from emp e;

 

7.4. Aggregate function

---Group query
---Find out the average salary of each department
---Group query中,出现在group by 后面的原始列,才能出现在select后面
---There is no column after group by. If you want to follow select, you must add an aggregate function
---Aggregate functions have a feature that turns multiple rows of records into one value
select e.deptno, avg(e.sal)
from emp e
group by e.deptno;

 

Alias problem:

---Find out the Department information whose average salary is higher than 2000
select e.deptno, avg(e.sal) asal
from emp e
group by e.deptno
having avg(e.sal)>2000;
---All conditions cannot be judged by alias
---For example, the following conditional statement cannot use alias as a condition
select ename, sal s from emp where sal>1500;

 

Group by and having problems:

---Where is the data before filtering and having is the data after filtering
---Expression: where must be before group by and having after group by
---Find out the average salary of employees whose salary is higher than 800 in each department
---Then find out the departments whose average salary is higher than 2000
select e.deptno, avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;

 

7.5. Multi table join query

left join、right join、inner join

The unique connection mode in Oracle, (+) denotes right join

---Special external connection in Oracle
select *
from emp e, dept d
where e.deptno(+) = d.deptno;

 

7.6. Self join query

---Find out the names of employees and leaders
---Self connection: in fact, self connection is to treat a table as multiple tables from different angles
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;

 

7.7. Paging query

---Rownum line number: when we do the select operation,
---If no record is found, a line number will be added to the line,
---The line number starts from 1 and increases in turn. You can't jump
---After the salary flashback of EMP table is arranged, there are five records on each page. Query the second page
---Sorting affects the order of rownum
select rownum, e.* from emp e order by e.sal desc
---If sorting is involved, but rownum is also used, we can nest the query again
select rownum, t.* from(
    select rownum, e.* from emp e order by e.sal desc) t;

 

Fixed format of paging query:

---After the salary flashback of EMP table is arranged, there are five records on each page. Query the second page
---Rownum line number cannot be greater than a positive number
select * from(
    select rownum rn, tt.* from(
        Select * from EMP order by Sal desc --- you can write the data to be paged here
    ) tt where rownum < 11
) where rn > 5;

 

8. Oracle view

viewView is to provide a query window, all data from the original table.

--Query statement to create a table
create table emp as select * from scott.emp;
select * from emp;
--Create view [must have DBA permission]
create view v_emp as select ename, job from emp;
--Query view
select * from v_emp;
--Modify view [not recommended]
update v_emp set job ='CLERK' where ename = 'ALLEN';
commit;
--Create read only view
create view v_emp as select ename, job from emp with read only;

The role of view:

First, views can mask some sensitive fields;

The second is to ensure the data of headquarters and branches are unified in time;

 

9. Oracle index

IndexesIndex is to build a binary tree on the column of the table, which can greatly improve the efficiency of query, but index will affect the efficiency of addition, deletion and modification.

The index is divided into:single column AndComposite index

--Create a single column index
create index idx_ename on emp(ename);
--Single column index trigger rule, the condition must be the original value in the index column
--Single line function, fuzzy query, will affect the trigger index
select * from emp where ename = "SOCTT";
--Composite index
--Create composite index
create index idx_enamejob on emp(ename, job);
--Composite index中第一列为优先检索列
--If you want to trigger a composite index, you must include the original value in the priority search column
select * from emp where ename = 'SCOTT' and job = 'XX';  --  Trigger composite index
select * from emp where ename = 'SCOTT' or job = 'XX';  --  Do not trigger index
select * from emp where ename = 'SCOTT';  --  Trigger single column index

The results are as follows:

 

10. PL / SQL programming language

PL / SQL programming language is an extension of SQL languageSo that SQL language has the characteristics of procedural programming.

PL / SQL programming language is more flexible and efficient than the general procedural programming language.

PL / SQL programming language is mainly used to write stored procedures and stored functions.

10.1.pl/sql programming basic syntax

---Declaration method
---Assignment operation can be used: = or into query statement
declare
    i number(2) := 10;
    S VARCHAR2 (10): ='xiao Ming ';
    ena emp.ename%type; --- Reference variable
    emprow emp%rowtype; --- Recorded variable
begin
    dbms_output.put_line(i);
    dbms_output.put_line(s);
    select ename into ena from emp where empno = 7788;
    dbms_output.put_line(ena);
    select * into emprow from emp where empno = 7788;
    dbms_ output.put_ Line (empty. Ename |'s job is: '| empty. Job)
end;

 

If judgment in 10.2.pl/sql

---Input numbers less than 18, output minors
---Input more than 18 less than 40 numbers, output middle-aged
---Input more than 40 numbers, output the elderly
declare
    i number(3) := &ii;
begin
    if i<18 then
        dbms_ output.put_ Line ('minor ');
    elsif i<40 then
        dbms_ output.put_ Line ('middle-aged person ');
    else
        dbms_ output.put_ Line ('old people ');
    end if;
end;

 

Loop loop in 10.3.pl/sql

---Loop loop in PL / SQL
---Outputting 1 to 10 in three ways is a number
---While loop
declare
    i number(2) := 1;   ---  Define variables and assign values
begin
    while i<11 loop
        dbms_output.put_line(i);
        i := i+1;
    end loop
end;
---Exit loop
declare 
    i number(2) := 1;
begin
  loop
    exit when i > 10;
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;
---For loop
declare 

begin
  for i in 1..10 loop
      dbms_output.put_line(i);
  end loop;
end;

 

10.4.pl/sql cursor

cursor: it can store multiple objects and multiple lines of records.

---Output the names of all employees in EMP table
declare
    cursor c1 is select * from emp;
    emprow emp%rowtype;
begin
    open c1;
        loop
            fetch c1 into emprow;
            exit when c1%notfound;
            dbms_output.put_line(emprow.ename);
        end loop;
    close c1;
end;

---Raise the salary of the staff in the designated department
declare
    cursor c2(eno emp.deptno%type) 
    is select empno from emp where deptno = eno;
    en emp.empno%type;
begin
    open c2(10);
        loop
            fetch c2 into en;
            exit when c2%notfound;
            update emp set sal=sal+100 where empno = en;
            commit;
        end loop;
    close c2;
end;

 

10.5. Stored procedure

stored procedureA stored procedure is a piece of PL / SQL language that has been compiled in advance and placed in the database.

Can be called directly. This section of PL / SQL is generally a fixed step business.

The creation of stored procedure

---100 yuan increase for designated employees
---Using or replace
create or replace procedure p1(eno emp.empno%type)
is

begin
    update emp set sal=sal+100 where empno = eno;
    commit;
end;

---Test P1
declare

begin
    p1(7788);
end;
---Through the storage function to calculate the annual salary of the designated employees
---Parameters of stored procedures and stored functions cannot have length
---The return value type of a storage function cannot have a length
create or replace function f_yearsal(eno emp.empno%type) return number
is
    s number(10);
begin
    select sal*12+nvl(comm, 0) into s from emp where empno = eno;
    return s;
end;

---Test f_ yearsql
---When the storage function is called, the return value needs to be received
declare
    s number(10);
begin
    s:= f_yearsal(7788);
    dbms_output.put_line(s);
end;

---How to use out type parameter
---Use stored procedure to calculate annual salary
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
    s number(10);
    c emp.comm%type;
begin
    select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
    yearsal := s+c;
end;

---Test p_ yearsal
declare
    yearsal number(10);
begin
    p_yearsal(7788, yearsal);
    dbms_output.put_line(yearsal);
end;

---What is the difference between in and out type parameters?
---Any parameter that involves the assignment of an into query statement or the assignment of: = must be modified with out

 

stored procedureandStorage functionThe differences between them are as follows:

Grammatical differences: different keywords; The stored function has two more returns than the stored procedure

Essential difference:The stored function has a return value, while the stored procedure does not

If a stored procedure wants to implement a business with a return value, we must use an out type parameter. Even if stored procedures use out type parameters, they do not actually have return values. Instead, we assign a value to the out type parameter inside the stored procedure. After execution, we get the value of the output type parameter directly.

 

We can use the storage function to have the return value property to customize the function.

Stored procedures cannot be used to customize functions.

---Case needs: find out the name of the employee and the name of the Department where the employee is

---Case preparation: copy the dept table of Scott user to the current user
create table dept as select * from scott.dept;
---Using traditional methods to achieve case requirements
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;

---Use the storage function to provide a department number and output a department name
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
    dna dept.dname%type;
begin
    select dname into dna from dept where deptno = dno;
    return dna;
end;
--Use FDNA storage function to achieve case requirements: find out the name of the employee, the name of the Department where the employee is
select e.ename, fdna(e.deptno)
from emp e;

 

10.6. Trigger

trigger: is to specify a rule. When we add, delete, or modify, the rule will be automatically triggered without calling.

Triggers are divided into statement level triggers and row level triggers

Statement Level Trigger : does not contain trigger with for each row.

row-level trigger : contains row level triggers for each row.

Add for each row to use: old or: new objects or a row of records.

---Statement level triggers
---Insert a record and output a new employee
create or replace trigger t1
after
insert
on person
declare 

begin
    dbms_ output.put_ Line ('a new employee's entry ');
end;
---Trigger T1
Insert into person values (1, 'Xiaohong');
commit;
select * from person;
---Row level trigger
---We can't cut the salary of the staff
--- raise_ application_ Error (- 20001-20999,'error message ');
create or replace trigger t2
before 
update
on emp
for each row
declare

begin
    if :old.sal > :new.sal then
        raise_ application_ Error (- 20001, 'can't reduce employee's salary');
    end if;
end;
---Trigger T2
select * from emp where empno = 7788;
update emp set sal=sal-1 where empno = 7788;
commit;

 

Trigger realizes primary key auto increment

We use row level triggers

---Analysis: before the user does the insert operation, get the data to be inserted. Assign a value to the primary key column in the data
create or replace trigger auid
before
insert
on person
for each row
declare

begin
    select s_person.nextval into :new.pid from dual;
end;
---Query person table data
select * from person;
---Auto increment of primary key by using auid
insert into person (pname) values ('a');
commit;

 

11. Java calls stored procedure

11.1.java calls Oracle stored procedure environment preparation

Oracle 10g uses Ojdbc14. Jar package;

Oracle 11g uses ojdbc6. Jar package

1. Create Maven project and import the following dependent jar packages:

com.oralce
        ojdbc14
        10.2.0.4.0
        runtime
    
    
        junit
        junit
        4.10
        test

 

11.2. Calling stored procedure code implementation

Jdbc.properties configuration:

String driverName = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@192.168.79.10:1521:orcl";
String username = "scott";
String password = "tiger";

 

1. Query operation:

@Test
public void javaCallOracle() throws Exception {
    //Load database driver
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //Get connection connection
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "scott", "tiger");
    //Get the precompiled statement object
    PreparedStatement pstm = connection.prepareStatement("select * from emp where empno = ?");
    //Assign values to parameters
    pstm.setObject(1, 7788);
    //Perform database query operation
    ResultSet rs = pstm.executeQuery();
    //Output results
    while(rs.next()){
        System.out.println(rs.getString("ename"));
    }
    //Release resources
    rs.close();
    pstm.close();
    connection.close();
}

 

2. Java calls stored procedure

/**
  *Calling stored procedure with Java
  * {?=  Call [(,...)]} to call the storage function
  *Using {call [(,...)]} to call stored procedure
  * @throws Exception
  */
@Test
public void javaCallProcedure() throws Exception {
    //Load database driver
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //Get connection connection
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "scott", "tiger");
    //Get the precompiled statement object
    CallableStatement pstm = connection.prepareCall("{call p_yearsal(?, ?)}");
    //Assign values to parameters
    pstm.setObject(1, 7788);
    pstm.registerOutParameter(2, OracleTypes.NUMBER);
    //Perform database query operation
    pstm.execute();
    //Output result [second parameter]
    System.out.println(pstm.getObject(2));
    //Release resources
    pstm.close();
    connection.close();
}

 

3. Java calls storage function

/**
   *Java calls storage function
   * {?=  Call [(,...)]} to call the storage function
   *Using {call [(,...)]} to call stored procedure
   * @throws Exception
   */
@Test
public void javaCallFunction() throws Exception {
    //Load database driver
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //Get connection connection
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "scott", "tiger");
    //Get the precompiled statement object
    CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}");
    //Assign values to parameters
    pstm.setObject(2, 7788);
    pstm.registerOutParameter(1, OracleTypes.NUMBER);
    //Perform database query operation
    pstm.execute();
    //Output result [first parameter]
    System.out.println(pstm.getObject(1));
    //Release resources
    pstm.close();
    connection.close();
}

 

Relevant documents and notes of this blog have been uploaded to GitHub address:

OracleDBReview