Installation, uninstallation and use of SQL statements for Oracle-Oracle databases

Posted by reivax_dj on Sat, 16 Oct 2021 18:57:53 +0200

1. Overview of Oracle

ORACLE database system is a set of software products provided by ORACLE Corporation (Oracle) in the United States with distributed database as the core. It is currently the most popular client/server (CLIENT/SERVER) ORACLE database is one of the most widely used database management systems in the world. As a general database system, ORACLE database has complete data management functions; as a relational database, it is a product of complete relationships; as a distributed database, it implements distributed processing functions. However, all its knowledge is limited toTo learn ORACLE on one type of machine, you can use it on all types of machines.

1.1 History of Oracle

  • Oracle 2 was born in 1978 and was developed in assembly language, but its appearance has not caused much attention.
  • In 1982, Oracle introduced Oracle 3, the first relational database to run on both large and small computers
  • In 1997, Oracle introduced Oracle 8 based on the Java language.
  • In 1999, Oracle officially provided Oracle8i, the world's first Internet database
  • In June 2001, Oracle released Oracle 9i
  • On July 12, 2007, Oracle introduced Oracle 11g, with "g" in Oracle 11g representing "grid"

1.2 Features of Oracle Database System

  • Supports large database, multi-user, high-performance transaction processing
  • Good safety
  • Provides new distributed database capabilities
  • Portability and compatibility

1.3 Installation and uninstallation of Oracle

1.3.1 Oracle installation steps

1) Download Oracle files

You can download the installation program from the official website based on how many bits (32 or 64 bits) your operating system is, as shown in the following figure.

It is important to note that the Oracle installer is divided into two files, which can be extracted into the same directory after downloading.


2) After downloading, select the two compressed files together, right mouse button - > decompress the files, as shown in the following figure.

Two compressed files are decompressed into the same directory. It is important to note that irregular characters such as Chinese and spaces should not appear in the path names.


3) Open the corresponding decompression path, find the installation file "setup.exe", double-click to install, as shown in the following figure:

4) Configure security updates. E-mail can be written or not written, cancel the following "I want to accept security updates (W) through My Oracle Support s", as shown in the figure below, and click Next.

5) Installation options.

Choose the default Create and Configure Database directly, as illustrated below, and click Next.


6) System class.

Since we are installing a server, select Server Class, as shown in the following image, and click Next.


7) Grid installation options.

Select Single Instance Database Installation, as illustrated below, and click Next.

8) Installation type.

Select Advanced Installation, as illustrated below, and click Next.


9) Product language.

By default (Simplified Chinese, English), as shown in the following image, click Next.


10) Database version.

Select Enterprise Edition, as shown in the image below, and click Next.


11) Installation location.

Fill in the installation path (just fill in the Oracle base directory and the Software Location will be generated automatically), as shown in the following image, and click Next.


12) Configuration type.

Select General Purpose/Transaction, as shown in the following figure, and click Next.


13) Database identifier.

Fill in the global database name and SID (default), as shown below, and click Next.


14) Configuration options.

Switch to the Character Set tab and select Use Unicode (AL32UTF8), as shown in the following image, and click Next.

15) Management options.

Click Next directly, as illustrated below.


16) Database storage.

Click Next directly, as illustrated below.


17) Backup and recovery.

If you need to make a backup of your data, enable automatic backup. Select "Do not enable automatic backup" here in the subtitle. Click Next as shown in the following image.

18) Scheme password.

For ease of testing, the same password is used here, which can be deployed at your discretion.


19) Summary.

After completing the prerequisite check, click Finish to formally begin the installation, as shown in the following figure, and click Next.


20) Install the product.

Once the installation is complete, a list of relevant database configurations is listed, which is best saved by clicking OK as shown in the following image. (In addition, click Password Management --> Modify the password of the scott user, which is the default common user conversion)


21) Complete.

When the installation is complete, click Close.


22) Test it.

Open the SQL PLUS that comes with Oracle, as illustrated below.


23) Enter the user name and password (that is, the password set in step 18), and the test is successful!

You can enter the SQL statement directly! It is important to note that the password entered by Oracle here is not displayed.

Oracle uninstallation steps under 1.3.2 Windows

1.Delete oracle registry information.

Run regedit, delete registry entries
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle

2.Delete oracle service.

The location of the oracle service in the registry is:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Service
Delete all services that start with oracle characters.

3.Delete event log.

The location in the registry is
HKEY_LOCAL_MACHINE\SYSTEM\CurrentContrilSet\Services\Eventlog\Application
Delete all keys that start with oracle characters.

4.Delete ORACLE environment variables, such as "JSERV" variables and Oracle paths in Path variables
5.Delete the oracle menu.
6.Delete "program files\oracle" directory
7.Restart WINDOWS.
8.Delete oracle home directory.

Start and close of 1.4 Oracle 11g instance

Oracle services under Windows operating system are managed as background service processes

  • OracleOraDb11g_homeTNSListener Service: Listener for Oracle Server
  • OracleServiceSID: Oracle's core service, which must be started in order to start an Oracle instance
  • OracleJobScheduler SID: Task Scheduling Service that periodically schedules user-created jobs to execute at pre-set times for the automatic management of Oracle servers
  • OracleDBConsoleSID: Responsible for launching Oracle Enterprise Manager on Windows platform. Oracle 11g Enterprise Manager is a fully functional Oracle database management tool that can manage local and grid environments

Start --> Run --> services.msc

1.5 Oracle Database Default Account

When creating a new database, Oracle will create several users by default

  • SYS user: SYS user is a superuser in Oracle, mainly used to maintain system information and manage instances. All tables and views of data dictionary in database are stored in SYS mode
  • SYSTEM user: The SYSTEM user is the default administrator in Oracle and has DBA privileges. This user has internal tables and views used by Oracle management tools, and usually manages users, privileges, storage, etc. of Oracle databases through the SYSTEM user.
  • SCOTT User: SCOTT is a demonstration account for the Oracle database. The SCOTT user mode contains four demonstration tables and the default password for SCOTT users is tiger

For daily administrative tasks, it is recommended that you use SYSTEM users to log on to the Oracle database server.
If you need to perform the tasks of backing up, restoring, and changing the database, you must log on to the Oracle database server as a SYS user

1.6 Oracle Logical Structure and Table Space

  • Data Block

    Data blocks are the smallest unit of data management, i.e. all I/O operations in Oracle are in blocks. The size of a data block is an integral multiple of the size of an operating system block, with a common size of 2KB or 4KB

  • Data Extent

    A data interval consists of physically contiguous blocks, which are the smallest units of Oracle storage allocation, and one or more blocks form a data interval

  • Data Segment

    Several data intervals form a data segment

  • Table Space

    • To improve the efficiency of database server management and operation, Oracle 11g uses the virtual concept of "tablespace" to manage logical objects. Users can store logical objects of different qualities in different tablespaces
    • In the Windows operating system, folders are used to categorize and manage various files, and tablespaces are equivalent to folders in Oracle databases.
    • Each tablespace consists of one or more data files, and a data file can only be associated with one tablespace

A database is like a cabinet, a drawer in a cabinet is like a table space, a folder in a drawer is like a table, paper in a folder is like a data range, and information written on paper is like data.

The role of table space

  • You can use tablespaces to limit the size of database files
  • Use tablespace to store data files on different disks to improve IO performance and facilitate data backup and recovery

Create tablespace syntax:

CREATE TABLESPACE tablespacename
DATAFILE 'filename' [SIZE integer [K|M]] 
[AUTOEXTEND [OFF|ON]];

create tablespace myspace
datafile 
  'D:\PLSQL\test\my0928am.dbf' size 5 M,
  'D:\PLSQL\test\my0928pm.dbf' size 10 M;

-- When creating tables,Specify a tablespace for a table
 create table person
 (
    pid number ,
    pname varchar2(20) ,
    page number(3),
    birthday date,
    address varchar2(20) default 'Unknown'
 )tablespace myspace;

To create a tablespace, you must log in as dba.

conn system/admin

2. Users and Rights

2.1 Create Oracle users

You can have multiple users in the same database at the same time. Each user manages his or her own database objects, such as database tables, indexes, views, and so on.
The CREATE USER command in Oracle is used to create new users. Each user has a default table space and a temporary table space. If not specified, Oracle sets SYSTEM as the default table space and TEMP as the temporary table space.

Create user syntax: create user user name identified by password default tablespace tablespace

CREATE USER xiaoming
IDENTIFIED BY 123
DEFAULT TABLESPACE myspace

User is locked by default

Unlock syntax: alter user [username] account unlock;

   alter user scott account unlock;

2.2 Permissions and Roles

Common system predefined roles in Oracle are as follows.

  • CONNECT: Temporary users, especially those who do not need to create tables, are usually assigned this role.
  • RESOURCE: More reliable and formal database users can grant this role, creating tables, triggers, procedures, and so on.
  • DBA: The database administrator role, with the highest privileges to manage the database. A user with the DBA role can revoke any other user or even other DBA privileges, which is dangerous, so do not easily grant this role to some less important users.

Assign privileges or roles to users

The GRANT command is used to assign privileges or roles to users, while the REVOKE command is used to revoke privileges and roles for users

Grammar for assigning permissions or roles: GRANT [<permissions> | <roles>] TO <users>;

grant create session,create table to xiaoming;--Assign permissions to users
grant connect to xiaoming;--Assign roles to users
grant resource to xiaoming;
--grant connect,resource to xiaoming;--Assign roles to users

--by xiaoming Assignment Query scott Under account emp Permissions on tables
 -- grant select|insert|update|delete on surface to user
 grant select on scott.emp to xiaoming
 --with xiaoming Account Logon Query
 select * from scott.emp

Revoke privilege and role syntax: REVOKE [<privilege> | <role>] FROM <user>;

 revoke create session, create table from xiaoming;
 revoke select on scott.emp from xiaoming;

3. Data Types and SQL Statements

3.1 Common data types for Oracle

  • Character type

    • CHAR type: CHAR represents a fixed-length string that is not long enough to be filled with spaces and can store up to 2000 bytes.
      The CHAR type distinguishes between Chinese and English. Chinese accounts for two bytes in the CHAR, while English only accounts for one byte.
    • VARCHAR2 type: VARCHAR2 represents a variable-length string that can store up to 4000 bytes.
      When defining this data type, you should specify its size. Using VARCHAR2 saves disk space compared to CHAR types.

  • value type

    • NUMBER type: can store positive, negative, zero, fixed, and 38-bit floating point numbers
    • NUMBER(M,N). Where M represents precision and represents the total number of digits; N represents the number of digits to the right of the decimal point
      number(10,2): 10 significant digits are exactly 2 digits after the decimal point

  • Date type

    • DATE type: Used to store date and time data in tables ranging from January 1, 4712 B.C., to December 31, 9999 A.D., with a length of 7 bytes representing centuries, years, months, days, hours, minutes, and seconds
    • TIMESTAMP type: Hours, minutes, and seconds used to store the date's year, month, day, and time, where the seconds are accurate to the six digits after the decimal point and contain time zone information

  • Large Object Type

    • CLOB Large String Object Type: CLOB (Character Large Object) data type is used to store variable length character data, up to 4GB data, and long text information that VARCHAR2 type cannot store
    • BLOB Large Binary Type: The BLOB (Binary Large Object) data type is used to store large binary objects such as graphics, video clips, sound clips, and so on, up to 4 GB of data.

3.2 DDL statement

3.2.1 CREATE TABLE statement

Used to create a table. When creating a table, the table's primary key, foreign key, unique constraint, Check constraint, and so on are often created.

---Create Table: create table Table Name(Column Name 1 Data Type [constraint])
create table dept
(
   deptno number(6) primary key,
   deptname varchar2(50) not null,
   deptnum  number(5)  check (deptnum>=0),
   loc varchar(100) default 'Zhengzhou' 
)

create table employee
(
   empId number(10) primary key,
   empName varchar2(50) not null,
   empAge number(3) check(empAge>=18 and empAge<=60),
   deptno number(6) references dept(deptno)
)

Oracle does not have the concept of primary key auto-increment, which can be resolved by sequence if required

​

3.2.2 ALTER TABLE statement

  • Add a new column to the created table

    Syntax: alter table Name add columnName dataType;

alter table employee add mobile number(11)
  • Modify the data type and type length of a specified column in a table

    Syntax: alter table tableName modify columnName dataType;

alter table employee modify mobile number(12)
  • Delete column specified in table

    Syntax: alter table Name drop column columnName;

alter table employee drop column  mobile
  • Add constraints to tables

    Syntax: alter table tableName add constraint constraintName constraintType(columnName)

alter table employee add constraint UN_empName unique(empName)
  • View constraints from view USER_CONS_COLUMNS
select  constraint_name,column_name from user_cons_columns

3.2.3 Sequence

Sequence (SEQUENCE) is a named sequence number generator that generates a series of sequential integers in a serial manner

Main uses of sequences:

  • Primary and foreign key value application requirements
  • Running water number application requirements
  • The generation and definition of sequences

Sequence syntax:

CREATE SEQUENCE sequnce_name
 [START WITH n1]					//Specify the first sequence number to generate (starting with n1)
 [INCREMENT BY n2]  				//Used to specify the interval between serial numbers, default value is 1 
 [{MAXVALUE n3 | NOMAXVALUE}] 	    //Specifies the maximum value a sequence can generate 
 [{MINVALUE n4 | NOMINVALUE}] 	    //Specifies the minimum value a sequence can generate 
 [{CACHE n5 | NOCACHE}] 		    //Specifies the number of serial numbers that can be pre-allocated in the cache, defaulting to 20 
 [{CYCLE | NOCYCLE}]  				//Used to specify whether to cycle after reaching the maximum or minimum value of a sequence 
 [ORDER];  							//Used to specify sequence number generation in order to ensure unique and ordered sequences

create sequence my_seq
   start with 1
   increment by 1
   maxvalue 100000
   minvalue 1
   nocycle
   cache 10

Get the value of the next sequence from nextval ue

select my_seq.nextval from dual

Get the value of the current sequence through currval

select my_seq.currval from dual;

--Create Sequence
create sequence my_seq
start with 1  --Start from 1
increment by 1 --Interval of sequences(Value per change)
maxvalue 10000000 --Maximum
minvalue 1   --minimum value
nocycle --No cycle
cache 10 

   
insert into dept(deptno,deptname,deptnum)values(20,'test2',2)
select * from dept
   
 select * from dual
 select my_seq.nextval from dual
 select my_seq.currval from dual
   
 select * from employee
   --Oracle There is no concept of primary key self-increasing in,But it can be done by customizing the sequence
   insert into employee(empId,empName,empAge,deptno) values(my_seq.nextval,'zhangsan',20,10)
   insert into employee(empId,empName,empAge,deptno) values(my_seq.nextval,'zhangsan2',22,10)

Change and delete sequences:

#Change Sequence
ALTER SEQUENCE my_seq MAXVALUE 5000 CYCLE;
#Delete Sequence
DROP SEQUENCE my_seq;

3.3 Basic SQL Statements

3.3.1 New Statement

insert into table name (column name 1, column name 2, column name 3...) values (value 1, value 2, value 3...)

3.3.2 Modify statement

update table name set column name 1 = value 1, column name 2 = value 2....[Where condition]

3.3.3 Delete statement

delete from table name [where condition]

3.3.4 Basic Query Statement Practice

--Use scott Account Practice Basic Query Statements
--1 Query all tables under the current user
select * from tab;
--2 Query employee table for all information
select * from emp;
--3 Query employee number,Full name,work,wages
select empno,ename,job,sal from emp
--4 Query employee number,Full name,work,wages,And display Chinese(Alias Columns)
select empno as number,ename as Full name,job as work,sal as wages from emp
--5 Eliminate duplicate columns,Query employee job types
select distinct job from emp
--6 String Join Operation(||)
--Query employee number,Full name,work.Show by the following grid:number:7369,Full name:Smith,work:Clerk
select 'number:'||empno,'Full name:'||ename,'work:'||job from emp
--7 Query columns support four operations(Annual salary=(wages+bonus)*12)
--Query employee number,Full name,work,Annual salary 
select empno,ename,job,(sal+nvl(comm,0))*12 from emp
nvl(comm,0)==>If comm Value is empty, value 0

--8 Where Conditional Query
-- Query all employees whose wages are greater than 1500
select * from emp where sal>1500
--Query all employees who receive a bonus
select * from emp where comm is not null
--Query employees who are paid more than 1500 or are eligible for bonuses
select * from emp where sal>1500 or comm is not null
--Query employees who are paid more than 1500 and are eligible for bonuses
select * from emp where sal>1500 and comm is not null
--Query employees whose wages are not more than 1500 or who are not eligible for bonuses
select * from emp where sal<=1500 or comm is null
--Query all employees whose salaries range from 1500 to 3000
select * from emp where sal>=1500 and sal<=3000
select * from emp where sal between 1500 and 3000
--Query employee information hired in 1981
select * from emp where hiredate like '%81%'
--Query employee names for the second letter"M"Employees of
select * from emp where ename like '_M%'
--Query employee salaries for numbers with 8
select * from emp where sal like '%8%'
--Query number is 7369,7499,7521,7799 Employee information for
select * from emp where empno=7369 or empno=7499 or empno=7521 or empno=7799
select * from emp where empno in(7369,7499,7521,7799)
--Query employee number is not 7369,7499,7521,7799 All Employee Information
select * from emp where empno not in(7369,7499,7521,7799)
--Query employee information with employee number 7369
select * from emp where empno =7369
--Query employee information with employee number not 7369
select * from emp where empno !=7369
select * from emp where empno <>7369

--Query employee information,Sort by salary from low to high
select * from emp order by sal asc
--Query employee information,Sort by salary from high to low
select * from emp order by sal desc

--Operations Collection:
--union:Merge two records,Remove duplicates 
select distinct deptno from emp union select deptno from dept;
--union:Merge two records,Do not remove duplicates 
select distinct deptno from emp union all select deptno from dept;
--intersect:Take the intersection of two sets
select distinct deptno from emp intersect select deptno from dept;
--minus:Remove intersection(aggregate A-(aggregate A And collections B Intersection))
select deptno from dept minus select distinct deptno from emp ;

4. Common Functions

4.1 Character Function

Character functions accept character parameters, which can be columns in a table or a string expression.

Common character functions:

--Character function
--initcap(char):Capitalize the first letter
select initcap('hello') from dual;
select initcap(ename) from emp;

--lower(char):To lower case  upper(char):Uppercase
select lower('HELLO') from dual;
select lower(ename) from emp;
select upper('hello') from dual;
select upper(ename) from emp;

--ltrim(char,set):Left Clipping rtrim(char,set):Right Clipping
select ltrim('hello,string','hello') from dual;
select rtrim('hello,string','string') from dual;
--Translate by character:translate
select translate('jack','abcd','1234') from dual;
--String substitution:replace
select replace('jack and jue','j','bl') from dual;

4.2 Date Function

  --1.Returns the month between two dates: the month precedes, otherwise it is negative
 select months_between('01-8 month-2018','01-6 month-2018') from dual;
 --2.Returns the new date when the number of months is added to the date:Parameter 2: equivalent to plus or minus months
 select add_months('01-8 month-2018',1) from dual;--2018/9/1 Saturday
 select add_months('01-8 month-2018',-2) from dual;--2018/6/1 Friday
 --3.Returns the new date for the week after the specified date
 select next_day('02-10 month-2018','Monday') from dual;--Query the date of the last week. 2018/10/8 Monday
 --4.Returns the last day of the month in which the specified date occurs
select last_day('02-2 month-2018') from dual;--2018/2/28 Wednesday 
--5.Round dates in the specified format [long struggled, foreign dates are not the same as Chinese habits, don't have to tangle with this period of dates, skip it] 
 --Designated year: year rounded by month
 select round(to_date('30-6 month-03'),'YEAR') from dual;--2003/1/1 Wednesday
--Specified month: Rounding of months by day
select round(to_date('15-8 month-03'),'MONTH') from dual;--2003/8/1 Friday
--appointed day:Number of days in the middle of the week
select round(to_date('01-10 month-2018'),'DAY') from dual;

4.3 Conversion Function

Oracle's type conversion is divided into automatic type conversion and mandatory type conversion. Automatic conversion between data types is possible, but the display conversion function is still recommended to maintain a good design style.

Common type conversion functions are:

  • To_number: Converts a character to a number
  • To_char (numeric or date): Converts a numeric value or date to a character
  • Convert characters to dates using the to_date('character to be transferred','date format') function
 --Numeric Conversion Characters number --> cahr
 --Specify the display format:
 --9 Place holder for position: eg:999,999,999=>Three groups separated by commas
 --Place-holder grouping is possible, but if the real number of bits is insufficient, 0 is used to fill in
select to_char(987654321,'￥999,999,999')from dual;
select to_char(123456789,'000,000,000,000.000')from dual;
--Character Conversion Date cahr-->date
--Use to_date('Characters to be transferred','Date format')Function converts character to date
select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1982-01-01';
select * from emp where hiredate>to_date('1982-01-01','yyyy-mm-dd');
--Date Conversion Character date-->char
--Use to_char('Date to be transferred','Conversion Format')
select to_char(hiredate,'yyyy-mm-dd') from emp;

4.4 Numeric Functions

A numeric function accepts numeric parameters, either from a column in a table or a numeric expression.

--ceil(number):ceil--->The smallest integer not less than the specified number
select ceil(3.14) from dual;
--floor(number):Rounding Down--->Maximum integer not greater than specified number
select floor(3.14) from dual;
--round(number):Four colors and five entries
select round(3.14) from dual;
--mod(Divisor,Dividend)Remaining:
select mod(10,3) from dual;
--power(x,y):Calculation x Of y Power
select power(3,3) from dual;
--sqrt(x):Calculation x Square root
select sqrt(9) from dual;

5. Advanced Queries

5.1 Connection Query

  • inner join: inner join shows only two tables to the associated data
--Equivalent Connection
select  e.empno,e.ename,e.sal,d.dname from emp e, dept d where e.deptno=d.deptno;
--Internal connection
select  e.empno,e.ename,e.sal,d.dname from emp e join dept d on(e.deptno=d.deptno)
  • Left join: The left join table shows all the data, while the right table shows only the associated data
select  e.empno,e.ename,e.sal,d.dname from emp e left join dept d on(e.deptno=d.deptno)
select  e.empno,e.ename,e.sal,d.dname from emp e ,dept d  where e.deptno=d.deptno(+);
  • Right Join: The right join table shows all the data, while the left table shows only the associated data
select  e.empno,e.ename,e.sal,d.dname from emp e right join dept d on(e.deptno=d.deptno)
select  e.empno,e.ename,e.sal,d.dname from emp e ,dept d where e.deptno(+)=d.deptno;
  • cross join Cartesian Product
select * from emp,dept
select  e.empno,e.ename,e.sal,d.dname from emp e cross join dept d
  • Full connection: full join
select  e.empno,e.ename,e.sal,d.dname from emp e full join dept d on(e.deptno=d.deptno)

5.2 Subquery

A subquery is a condition in which the results of one query statement are treated as a separate query statement.

--Query average wage>2000,Department number,Department name, average wage
--average wage>2000 Department number

--Query average wage>2000 Department number and average wage for department number
select deptno,avg(sal)from emp group by deptno having avg(sal)>2000
 
select d1.deptno,d1.dname,d2.savg from dept d1 inner join 
       (select deptno,avg(sal) savg from emp group by deptno having avg(sal)>2000) d2 --View the results of the query as a table for joined queries
       on (d1.deptno=d2.deptno)      
 
--Query average wage>2000,Department number,Department Name
--Query average wage>2000 Department number
select deptno from emp group by deptno having avg(sal)>2000

select deptno,dname from dept where deptno in(select deptno from emp group by deptno having avg(sal)>2000)
--Query average wage>2000,Department number,Department name, average wage
select d.deptno,d.dname,avg(e.sal) from emp e,dept d where e.deptno=d.deptno 
group by d.deptno,d.dname having avg(e.sal)>2000

5.3 Oracle Paging

The reasons for paging are as follows:

  1. Improve performance, 20 at a time, is certainly better than 20,000 at a time; in addition, if the amount of data is large, the contents are queried out at once, the results of the query are placed in memory, memory is not so large
  2. You don't need so much data, like news. The average person may only look at the last 20 items; if we look up the following, it's a waste.
  3. Presentation considerations: If you present too much data at a time, neither typography nor aesthetics are good

Rownum: is a pseudo column that generates a serialized number from the returned records. You can use the ROWNUM column to return the first N records in the query result set

--Oracle paging: adopt rownum:Pseudo Columns
---MySQL paging: Use limit Starting position,Paging units   SQLServer:Use top Keyword
select * from emp;
--Paging units:5, First page:Top 5
select rownum rn, e.* from emp e;
delete from emp where empno=8000
--View the results of the query as a table for joined queries
select e2.* from (select rownum rn, e.* from emp e) e2 where e2.rn<=5
select e2.* from (select rownum rn, e.* from emp e) e2 where e2.rn>0 and e2.rn<=5
--Paging units:5, Page 2: rn>5 and rn<=10
select e2.* from (select rownum rn, e.* from emp e) e2 where e2.rn>5 and e2.rn<=10
--Paging units:5, Page 2: rn>10 and rn<=15
select e2.* from (select rownum rn, e.* from emp e) e2 where e2.rn>10 and e2.rn<=15

--select e2.* from (select rownum rn, e.* from emp e) e2 where e2.rn>Actual Location and e2.rn<=End position
--Starting position:(Current Page-1)*Paging units,
--End position:Current Page*Paging units

Note: Aliases for columns can be added as in Oracle, but aliases for tables cannot be added as

  • Total Pages: Total Records%Paging Units==0? Total Records/Paging Units: Total Records/Paging Units+1

6. Locks

Locks in Oracle are mainly used to solve concurrency problems and ensure data consistency and integrity during concurrent operations.

Classification of Oracle locks

  • Row level locks
  • Table level locks

6.1 row level locks

A row-level lock is an exclusive lock that prevents other transactions from modifying the row. Oracle automatically applies row-level locks when the following statements are used:

  • INSERT statement
  • UPDATE statement
  • DELETE statement
  • SELECT ... FOR UPDATE [wait second] [no wait]]
  • The SELECT...FOR UPDATE statement allows the user to lock multiple records at once for updates

Release locks using COMMIT or ROLLBACK statements

--user A:
select * from emp where empno=7934 for update  
--user B:
update scott.emp set sal=sal+10  where empno=7934 --Unable to change
update scott.emp set sal=sal+10  where empno=7902 --Can be changed

6.2 Table Level Locks

Table level locks: Locks the entire table, restricting access to it by other users.

LOCK TABLE table_name IN mode MODE

--user A:
lock table emp in share mode;
--user B:
select * from scott.emp --Yes?
update scott.emp set sal=sal+10  where empno=7902 --May not

6.3 Deadlock

Deadlock: A deadlock occurs when two transactions wait for each other to release resources.

Oracle automatically detects deadlocks and resolves them by ending one of these transactions.

--user A:
lock table emp in share mode;
update emp set sal=sal+10  where empno=7902
--user B:
lock table emp in share mode;
update scott.emp set sal=sal+10  where empno=7902

7. Views

Views display data from one or more tables in a customized manner, and views can be thought of as virtual tables or stored queries, and the tables on which the view is created are called base tables.

Advantages of views:

  • Another level of table security is provided
  • Hidden data complexity
  • Simplified user's SQL commands
  • Isolate changes in base table structure
  • Provide data from another perspective by renaming columns

Grammar:

create [or replace] view view name
as select statement;

--Query employee number, name, job,Salary, department name and location
select * from emp;
select * from dept;

select e.empno,e.ename,e.job,e.sal,d.dname,d.loc from emp e inner join dept d on(e.deptno=d.deptno)
--Use DBA Log on as scott User Authorization
grant create view to scott
--Create View
create or replace view myview 
as select e.empno,e.ename,e.job,e.sal,d.dname,d.loc from emp e inner join dept d on(e.deptno=d.deptno)
--Query View
select * from myview;

Delete view: drop view view view name;

--Delete View: 
drop view myview;

8. Index

Indexes are similar to index catalogs in dictionaries, and adding appropriate indexes to tables can improve retrieval efficiency.

Indexing Advantages:

  • To improve the performance of SQL statement execution
  • Reduce disk I/O access

Adding Index Principles:

  • Only creating an index on a large table makes sense
  • Conditions (columns) in where clauses are often indexed
    • The primary key automatically adds a unique index.
  • Indexing incurs some additional overhead:
    • Writing slows down: additional maintenance of index information is required
    • Increase disk and memory footprint (1.2 times)

Create standard index syntax:
CREATE INDEX index name ON table name (column name)
TABLESPACE tablespace

--grammar: create index Index Name on Table Name(Column Name) 
create index myIndex on emp(ename);
select * from emp where ename='test'
--Combined index:create index Index Name on Table Name(Column Name 1,Column Name 2...) 
create index myIndex2 on emp(ename,sal)
select * from emp where ename='test'and sal=1300

Delete index: drop index index name;

drop index myIndex;

Create unique index syntax:

CREATE UNIQUE INDEX index name
ON table name (column name);

--Create Unique Index: create unique index Index Name on Table Name(Column Name) 
create unique index myIndex3 on emp(ename);

9. PL/SQL Language

Introduction to 9.1 PL/SQL

PL/SQL is also a programming language called Procedural Language/SQL PL/SQL is an extension of the Oracle database to SQL statements. The use of ordinary SQL statements adds the features of programming languages, so PL/SQL is a program language that organizes data operations and query statements into procedural units of PL/SQL code, and performs complex functions or calculations through logical judgment, looping and other operations.

  • PL/SQL is a procedural extension of Oracle to the standard SQL language
  • Allow embedding of SQL statements, defining variables and constants,
  • Allow procedural language structures (conditional branching and looping statements),
  • Allow exceptions to handle Oracle errors
  • It can be used to create stored procedures, triggers, packages, and so on.
  • It can also be used to handle business rules, database events, or add program logic to the execution of SQL commands

All PL/SQL programs use blocks as their basic units, which contain procedural statements and SQL DLL statements. These blocks can appear sequentially or nested within one another.

Classification of blocks:

  • Anonymous Blocks
    • Anonymous blocks are blocks that appear in an application without a name and are not stored in a database
    • Anonymous blocks appear where SQL statements can appear. They can call other programs, but they cannot be called by other programs.
      A named block is an anonymous block with a label that specifies a name for the block
  • subroutine
    • A subroutine is a procedure, function stored in a database that can be executed multiple times after it is generated.
  • Packages
    • A package is a set of subprograms, variable definitions stored in a database that can be called by other packages or subprograms
  • trigger
    • A trigger is a named block stored in a database that can be executed multiple times after being generated
      Executed once or more before or after the corresponding trigger event occurs

9.2 Program Structure

Basic structure of 9.2.1 PL/SQL block

Component

  • Definition section: Used to define constants, variables, cursors, exceptions, and complex data types
  • Execution section: Used to implement application module functions, which contains PL/SQL statements and SQL statements that need to be executed
  • Exception handling section: Used to handle possible run errors in the execution section

Grammar:

DECLARE
Definition section
BEGIN
Execution Part
EXCEPTION
Exception handling section
END;

Note: DECLAREE, BEGIN and EXCEPTION are not followed by';'(semicolon), while END must be followed by';' (semicolon).

9.2.2 Variables and Constants

In the declaration section, you can declare the constants, variables, functions, cursors, exception handling names you want to use.

Declare variable syntax: variable name type: =value;
Syntax for assigning values to variables: variable name: =value;
Set first in sqlplus, otherwise execution has no output
set serveroutput on;

--Declare Variables(Suggest variable names to v_Start)
--Variable Name Type:=value;
--Assignment variable name:=value;
declare 
   v_hello varchar2(20):='hello';
   v_world varchar2(20);
   v_cons constant number(4):=123;--declare constant:Cannot reassign
begin
  v_world:=',oralce!';
  --v_cons:=345;
  dbms_output.put_line(v_hello||v_world);--Be similar to System.out.println();
  dbms_output.put_line('v_cons='||v_cons); 
end;
--Use select ..into Statement assigns values to variables
declare
    v_name varchar2(20);
    v_sal number(10);
    v_empno number(10);
begin
   v_empno:='&empno';--Gets the number entered by the console:&(Interactive commands)
   select ename,sal into v_name,v_sal from emp where empno=v_empno;
   dbms_output.put_line('Full name:'||v_name);
   dbms_output.put_line('salary:'||v_sal);
end;
--%type:Data types referencing variables and database columns
--%rowtype:Provide a record type that represents a row in a table
declare
    v_name emp.ename%type;-- and emp In table ename Columns of the same type
    v_sal emp.sal%type;
    v_empno emp.empno%type;
    v_record emp%rowtype;--Keep a record
begin
   v_empno:='&empno';--Gets the number entered by the console:&(Interactive commands)
   select ename,sal into v_name,v_sal from emp where empno=v_empno;
   dbms_output.put_line('Full name:'||v_name);
   dbms_output.put_line('salary:'||v_sal);
   dbms_output.put_line('**************************');
   select * into v_record from emp where empno=v_empno;--Assign queried records to v_record
   dbms_output.put_line('work:'||v_record.job);
end;

9.2.2 Process Control

Conditional structure:

  • If...end if structure
  • If...else...end if structure
  • If...elsif...else...end if structure
  • case structure
--Conditional structure
--if.... end if;
--grammar  if condition then Sentence end if;
declare
   v_age number(3);
begin
   v_age:='&age';
   if v_age>=18 then
      dbms_output.put_line('adult!');
   end if;  
   if v_age<18 then
      dbms_output.put_line('Under age!');
   end if;  
end;

--if....else....end if;
declare 
  v_age number(3);
begin
  v_age:='&age';
  if v_age >= 18 then
     dbms_output.put_line('adult!');
  else 
     dbms_output.put_line('Under age!');
  end if;
end;

---if...elsif...else....end if
declare 
  v_age number(3);
begin
  v_age:='&age';
  if v_age >= 50 then
     dbms_output.put_line('old age!');
  elsif v_age>=30 then
     dbms_output.put_line('First year!');
  elsif v_age>=18 then
     dbms_output.put_line('Youth!');  
  else
     dbms_output.put_line('Under age!');
  end if;
end;
--case clause:Be similar to switch
declare
   v_score varchar2(2);
begin
   v_score:='&score';
   case v_score   --Similar switch In statement switch
     when 'A' then  --Be similar to switch In statement case
        dbms_output.put_line('excellent!');
     when 'B' then
        dbms_output.put_line('good!');
     when 'C' then
        dbms_output.put_line('commonly!');
     when 'D' then
        dbms_output.put_line('Fail!');
     else  --Be similar to swtich In default
        dbms_output.put_line('Incorrect input!'); 
   end case;  
end;

Cycle structure:

  • Loop loop structure: similar to do...while loop
  • While loop structure: similar to while loop
  • For loop structure: similar to for loop
--loop Cyclic structure: do...while loop
--loop
--Circulatory body;
-- exit when condition
--end loop;
declare
  i binary_integer:=1;
begin
  loop
     dbms_output.put_line('i='||i);
     i:=i+1;
     exit when i>10; --Satisfy Conditions Launch Cycle
  end loop;
end;

--while Cyclic structure:Be similar to while loop
--while condition 
--loop
--Circulatory body
--end loop;
declare
  i binary_integer:=1;
begin
  while i<=10
  loop
     dbms_output.put_line('i='||i);
     i:=i+1;
  end loop;
end;
--for Circular variable in [REVERSE] Initial value expression..Final value expression 
--loop
--	Circulatory body;
--end loop;
declare
  v_i binary_integer;
begin
  for v_i in 1..10
  loop
      dbms_output.put_line('i='||v_i);
  end loop;
end;

declare
  v_i binary_integer;
begin
  for v_i in reverse 1..10
  loop
      dbms_output.put_line('i='||v_i);
  end loop;
end;

10. Cursors

When a query statement (SELECT) and a data manipulation statement (DML) are executed in a PL/SQL block, Oracle assigns a Context Area to it. A cursor is a pointer to the Context Area. It provides a way for applications to process each row of data with a multiline data query result set separately.

10.1 Cursor Overview

Cursors are divided into explicit and implicit cursors

  1. Implicit cursors are used to process SELECT INTO and DML statements
  2. Explicit cursors are used to process multiple rows of data returned by SELECT statements

Steps for using explicit cursors

  1. Define cursor: CURSOR cursor_name IS select_statement;

  2. Open cursor: OPEN cursor_name;

  3. Extract data: FETCH cursor_name INTO variable1,variable2,...;

  4. Close cursor: CLOSE cursor_name;

10.2 Display Cursor

Show cursor properties

Explicit cursor properties are used to return execution information for explicit cursors
Cursor attributes are formatted as: cursor name + attribute name

Property NameMeaning
%ISOPENUsed to determine if the cursor is already open. Returns TRUE if the cursor is already open; otherwise returns FALSE
%FOUNDCheck if data is extracted from the result set. If data is extracted, the return value is TRUE; otherwise, FALSE is returned
%NOTFOUNDContrary to the%FOUND property, if data is extracted, the return value is FALSE; otherwise, TRUE is returned
%ROWCOUNTReturns the actual number of rows fetched to the current behavior
/*
Explicit Cursor
 An explicit cursor defines a query in the declaration part of a PL/SQL block that can return multiple rows
*/
--Use loop Loop traversal cursor
declare
   v_emp_record emp%rowtype; --Declare Variables:Keep record information for a row in the employee table
   cursor emp_cur is select * from emp;--Define Cursor:The cursor maintains all employee information
begin
   open emp_cur; --open
   loop
     fetch emp_cur into v_emp_record; --Extracting data from cursors
     exit when emp_cur%NOTFOUND; --Determine if there is no data in the cursor
      dbms_output.put_line('number:'||v_emp_record.empno||'Full name:'||v_emp_record.ename);
   end loop;  
   close emp_cur;
end;

10.3 Cursors with parameters

After defining a parameter cursor, different result sets can be generated when the cursor is opened multiple times with different parameter values

Syntax: CURSOR cursor_name(param_name datatype)
IS select_statement;

When defining a parameter cursor, a cursor parameter can only specify a data type, not a length.

When using a cursor for loop, oracle implicitly opens the cursor, extracts data, and closes the cursor

--Cursor with parameters
declare
    v_sal emp.sal%type;
    v_emp_record emp%rowtype;
    cursor emp_cur(salPara number) is select * from emp where sal < salPara; --Define a cursor with parameters
begin
    v_sal:='&Please enter compensation';
    for v_emp_record in emp_cur(v_sal)
    loop
         dbms_output.put_line('Full name:'||v_emp_record.ename||'Pay:'||v_emp_record.sal);   
    end loop;
end;

11. Stored Procedures

Procedures are typically used to perform a specified action and can encapsulate common, specific actions into a process

Advantages of stored procedures:

  • Modularization: Decomposition programs into logical modules
  • Reusability: Can be called by any number of programs
  • Maintainability: simplifying maintenance operations
  • Security: Make your data safer by setting permissions
  • Efficient execution

Stored procedure syntax:

CREATE [OR REPLACE] PROCEDURE 
   <procedure name> [(<parameter list>)]
IS|AS --The declarative part of a variable
   <local variable declaration>  
BEGIN  
   <executable statements> --business
[EXCEPTION   --exception handling
   <exception handlers>]

END;
stay sqlplus Set first in, otherwise execution has no output
set serveroutput on;

create or replace procedure  pro1
 is 
   v_hello varchar2(20):='hello,procedure!';
 begin
   dbms_output.put_line(v_hello);
 end;

How stored procedures are called:

  • execute stored procedure name (in sqlplus)

    execute pro1();

  • call stored procedure name (in sqlplus/PLSQLDev)

    call pro1();

  • Call using a block
    begin
    Stored procedure name
    end;

    begin
  pro1();
end;


--According to department number,Increase pay for employees(Department number=10,Pay raise 100,Department number=20,Pay raise 200,Department number=30,Pay raise 300)
create or replace procedure pro2
is
  cursor  c is select * from emp for update;
begin
  for v_emp_rec in c
     loop
        if(v_emp_rec.deptno=10) then
          update emp set sal=sal+100 where current of c;--current of c Records pointed to by the current cursor
        elsif v_emp_rec.deptno=20 then
           update emp set sal=sal+200 where current of c;
        elsif v_emp_rec.deptno=30 then
           update emp set sal=sal+300 where current of c;   
        end if;   
     end loop;
  commit;  --Submit Transaction
end;
select * from emp
begin
  pro2();
end;

Stored Procedures with Parameters

  • IN: Value used to accept the caller (input parameter)
  • OUT: Used to return a value (output parameter) to the caller
  • IN OUT: The value used to accept the caller and return the updated value to the caller (that is, to act as an input parameter or an output parameter)
create or replace procedure pro3(emp_no  number)
is 
  v_name varchar2(20);
begin
  select ename into v_name from emp where empno=emp_no;
  dbms_output.put_line('Name:'||v_name);
  exception when NO_DATA_FOUND then 
    dbms_output.put_line('No employee information found..');
end;  
--Calling stored procedures with parameters
begin
   pro3('793422');
end;
--Both input and output parameters(Accept Return Value)
create or replace procedure pro4(emp_no in number,emp_sal out number)
is 
  v_name varchar2(20);
begin
  select ename,sal into v_name,emp_sal from emp where empno=emp_no;
  dbms_output.put_line('Name:'||v_name);
  exception when NO_DATA_FOUND then 
    dbms_output.put_line('No employee information found..');
end;  

-- call
declare 
 emp_no number:=7934;
 emp_sal number;--Receive Return Value
begin
  pro4(emp_no,emp_sal);
  dbms_output.put_line('out:....'||emp_sal);
end;

   --input/Output parameters: in out
  create or replace procedure pro5(p_empno in out number,p_sal in out number)
  is
   v_name varchar2(20);
  begin
    select ename,sal into v_name ,p_sal from emp where empno=p_empno;
    dbms_output.put_line('Employee Name:'||v_name); 
     exception when NO_DATA_FOUND then
     dbms_output.put_line('No employee information found..');   
  end;  
   --call
 declare
   p_empno number:=7369;
   p_sal number; --Accept the value of the output parameter
 begin
     pro5(p_empno,p_sal);
     dbms_output.put_line('Value of output parameter:'||p_sal);    
 end;

12. Triggers

Triggers are implicitly executed stored procedures that can be developed using PL/SQL
Oracle automatically executes the trigger code when a specific event occurs, such as modifying a table, creating an object, logging in to a database

Triggers are stored procedures that execute automatically when a specific event occurs

Trigger Type

  • DML trigger: Triggers when a DML operation is performed on a database table and can be triggered on each row or statement operation.

  • Alternative trigger: A trigger specific to view operations in oracle8

  • System Trigger: Triggers database system events, such as startup, shutdown, etc.

Trigger Composition

  • Trigger event: DML or DDL statement.
  • Trigger time: before or after the trigger event occurs
  • Trigger action: Use PL/SQL blocks for appropriate database operations
  • Trigger object: table, view, mode, database
  • Trigger frequency: The number of times an action defined within a trigger has been executed.

When writing trigger execution code, you need to be aware of the following limitations

  • Trigger code cannot be larger than 32K in size. If you do need to create a trigger using a large amount of code, you should first create a procedure, then invoke it in the trigger using a CALL statement
  • Trigger code can only contain SELECT, INSERT, UPDATE, and DELETE statements.
  • Cannot contain DDL statements (CREATE, ALTER, DROP) and transaction control statements (COMMIT, ROLLBACK, and SAVEPOINT)

Trigger Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_name
[FOR EACH ROW]
begin
   pl/sql_block;
end;

--------------------------trigger-------------------------------

create table tbl_dept
(
  deptId number(4) primary key,
  deptName varchar2(20),
  deptNum number(4)
)

create table tbl_emp
(
  empId number(4) primary key,
  empName varchar2(20),
  deptId number(4) references tbl_dept(deptId)
)

insert into tbl_dept(deptId,deptName,deptNum) values(1,'Development Department',1);
insert into tbl_dept(deptId,deptName,deptNum) values(2,'Test Department',2);

insert into tbl_emp(empId,empName,deptId) values(1,'zhangsan',1);
insert into tbl_emp(empId,empName,deptId) values(2,'zhangsan2',2);
insert into tbl_emp(empId,empName,deptId) values(3,'zhangsan3',2);



--Take advantage of triggers,Number of maintenance departments
--(If new employees are added,Number of departments+1;
--If employees are deleted,Number of departments-1;
--If department is changed for employees:Number of original departments-1，Number of existing departments+1)
create or replace  trigger tri1
after insert or delete or update
on tbl_emp
for each row
begin
   if inserting then
     update tbl_dept set deptNum=deptNum+1 where deptId=:new.deptId;--:new Represents new data
   elsif deleting then
     update tbl_dept set deptNum=deptNum-1 where deptId=:old.deptId;--:old Represents old data  
   elsif updating then
     update tbl_dept set deptNum=deptNum-1 where deptId=:old.deptId;--:old Represents old data 
     update tbl_dept set deptNum=deptNum+1 where deptId=:new.deptId;--:new Represents new data 
   end if;  
end;  

insert into tbl_emp values(4,'zhaoliu',1);
delete from tbl_emp where empId=4
update tbl_emp set deptId = 1 where empId=2

Make small improvements every day
Move forward, or you'll fall behind

Topics: Oracle

Hot Topics

©2024 Programmer Think