Oracle part special writing method

Oracle special writing

First n lines

Use the ROWNUM keyword, the same as the limit in mysql

#Get the first ten lines
select * from table where ROWNUM<=10;

Time field filtering

The built-in to is required for comparing time in Oracle_ Date (STR, FMT) function

#Filter results using two time parameters
SELECT WELL_ID,SPUD_DATE FROM CD_WELL_SOURCE Where SPUD_DATE >= TO_DATE('1987-12-10 00:00:00', 'yyyy-MM-dd hh24:mi:ss') AND SPUD_DATE < TO_DATE('1999-12-10 00:00:00','yyyy-MM-dd hh24:mi:ss') AND ROWNUM<=10;

oracle set primary key auto increment column

New test table

create table member(
memberId number primary key,
memberMail varchar2(20)not null,
memberName varchar2(20) not null,
memberPassword varchar2(20)
);

A custom sequence (similar to auto increment function) is required

CREATE SEQUENCE test_sequence
INCREMENT BY 1 -- How many at a time
START WITH 1 -- Count from 1
NOMAXVALUE -- Do not set maximum
NOCYCLE -- Always accumulate, not cycle
NOCACHE -- No buffer

Finally, you need a trigger to execute it

create trigger emp_trig(Trigger name) before
insert on member(Table name) for each row when (new.memberId is null)
begin

 select emp_sequence((sequence name).nextval into:new.memberId((auto increment field) from dual;

 end;

oracle function

Introduce common oracle functions

Introduction to dual table

It is different from the sql writing method of directly obtaining constants in mysql. oracle must contain the from table name. If it is a constant, you need to write a specific dual table
```
SQL> select 3 from dual;

	 3
----------
	 3
SQL> select 3;
select 3
	   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
```
Dual is an actual row column table in Oracle, which can be read by any user. It is often used in the Select statement block without target table

decode() function

Decode (condition, value 1, return value 1, value 2, return value 2,... Value n, return value n, default value) is equivalent to case when multiple selection function

# The example is the sex column in the user table. It outputs male for 0 and female for 1. Neither is satisfied or empty. The output is unknown
select id, username, age, decode(sex,0,'male',1,'female','unknown') from users;

(+) sign usage

External connections can also be represented by "(+)". Some notes on using (+):
The (+) operator can only appear in the WHERE clause and cannot be used with the OUTER JOIN syntax.
When you use the (+) operator to perform an outer join, if you include multiple conditions in the WHERE clause, you must include the (+) operator in all conditions.
The (+) operator applies only to columns, not expressions.
The (+) operator cannot be used with the OR and IN operators.
The (+) operator can only be used to implement left and right outer joins, not complete outer joins.
```
select * from t_A a left join t_B b on a.id = b.id Equivalent to Select * from t_A a,t_B b where a.id=b.id(+);
 Similarly   Select * from t_A a,t_B b where a.id(+)=b.id; Right connection
```

||Usage of

It is equivalent to contact(), splicing the first and second strings together

# You can splice constants or columns
SQL> select 'aaa' || ',' || 'bbb' || ',' || 'ccc' from dual;

'AAA'||','|
-----------
aaa,bbb,ccc

nvl() function

Non empty function, NVL(eExpression1, eExpression2). If expression 1 is empty, expression 2 is returned. It is generally used for numeric types. If it is empty, it returns 0
```
# If the student table id is empty, 0 will be returned
select NVL(id, 0) id1 from student;
```

tochar() function

The function is to convert numeric or date type to character type. The second parameter of all formatting functions is the template for conversion.
See details tochar Daquan

substr() function

String interception function, should be very familiar with

SQL> select substr('adbdfg',1,4) from dual;

SUBS
----
adbd

SQL> select substr('adbdfg',2) from dual;

SUBST
-----
dbdfg

Format 1: substr (string, string, int a, int b);
1. String the string to be intercepted
2. The starting position of the intercepted string (Note: when a is equal to 0 or 1, it is intercepted from the first bit)
3. b the length of the string to be intercepted
Format 2: substr (string, int a);
1. String the string to be intercepted
2. A can be understood as intercepting all subsequent strings from the a character.

instr() function

Character lookup function
Format 1: instr (string1, string2) / / instr (source string, target string)
Format 2: instr (string1, string2 [, start_position [, nth_appearance]] / / instr (source string, target string, start position, matching sequence number)
Parsing: the value of string2 should be found in string1 from start_ The value given by position (i.e. position) starts to be retrieved in string1 and the nth is retrieved_ Appearance (several) occurrences of string2.
Note: in Oracle/PLSQL, the instr function returns the position of the string to be intercepted in the source string. Only retrieve once, that is, from the beginning of the character to the end of the character.
```
1 select instr('helloworld','l') from dual; --Return result: 3    Default first occurrence“ l"Location of
2 select instr('helloworld','lo') from dual; --Return result: 4    Namely“ lo"At the same time, the first letter“ l"Location of occurrence
3 select instr('helloworld','wo') from dual; --Return result: 6    Namely“ wo"At the same time, the first letter“ w"Location of occurrence
```

Round function usage

Intercept digital function
The format is as follows: ROUND (number[,decimals])
Where: number is the value to be intercepted
decimals indicates that the number of digits after the decimal point should be reserved. Optional. If it is ignored, all decimal parts will be truncated and rounded. If it is negative, it means the number of digits to the left from the decimal point. The corresponding integer number is filled with 0 and the decimal will be removed. It should be noted that, unlike the trunc function, the truncated number should be rounded.
```
SQL> select round(73.2456,2) from dual;

ROUND(73.2456,2)
----------------
	   73.25
```

floor function usage

Function: returns the maximum integer less than or equal to n

SQL> SELECT FLOOR(0.1) FROM dual;

FLOOR(0.1)
----------
	 0

replace function usage

The REPLACE function replaces a value in a string with another value.
For example, you can REPLACE each occurrence of a letter with a matching number. The format of REPLACE is as follows:
REPLACE (char, search_string [, replace_string])
If the value of the replace_string variable is not specified, it will be deleted when the value of the search_string variable is found. The input can be any character data type - CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
```
# example:
select replace('0123456789','0','a') from dual;--a123456789
select replace('0123456789','0','') from dual;--123456789
select replace('0123456789','0') from dual;--123456789
```

trunc() function

Date usage

1.select trunc(sysdate) from dual --2013-01-06 Today's date is 2013-01-06
2.select trunc(sysdate, 'mm') from dual --2013-01-01 Return to the first day of the month.
3.select trunc(sysdate,'yy') from dual --2013-01-01 Return to the first day of the year
4.select trunc(sysdate,'dd') from dual --2013-01-06 Return to the current date
5.select trunc(sysdate,'yyyy') from dual --2013-01-01 Return to the first day of the year
6.select trunc(sysdate,'d') from dual --2013-01-06 (Sunday)Returns the first day of the current week
7.select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 The current time is 17:35 
8.select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()Function has no precision of seconds

Use figures
TRUNC(number,num_digits)
Number requires a truncated number.
Num_digits is a number used to specify rounding precision. The default value of num_digits is 0.
TRUNC() function intercepts without rounding
```
# Example
select trunc(123.458,1) from dual --123.4
```

Keywords: Database Oracle SQL

Added by virgil on Fri, 19 Nov 2021 17:26:05 +0200

Programming VIP