Oracle Database SQL Summary

1 Oracle time period Query

1.1 scenario: filter the corresponding records based on the time range entered by the user.

1.2 solution:

Method 1:

SQL code
SELECT * FROM T_XJXX_XJGL
Where createdate> = TO_DATE (2011-6-13, yyyy-MM-dd)
And createdate <= TO_DATE (2011-6-17, yyyy-MM-dd );

SELECT * FROM T_XJXX_XJGL
Where createdate> = TO_DATE (2011-6-13, yyyy-MM-dd)
And createdate <= TO_DATE (2011-6-17, yyyy-MM-dd );
Method 2:

SQL code
SELECT * FROM T_XJXX_XJGL
WHERE TO_CHAR (CREATEDATE, yyyy-MM-dd)> = 2011-6-13
AND TO_CHAR (CREATEDATE, yyyy-MM-dd) <= 2011-6-16;

SELECT * FROM T_XJXX_XJGL
WHERE TO_CHAR (CREATEDATE, yyyy-MM-dd)> = 2011-6-13
AND TO_CHAR (CREATEDATE, yyyy-MM-dd) <= 2011-6-16;

Method 3:

SQL code
SELECT * FROM T_XJXX_XJGL
Where createdate> TO_DATE (2011-6-15, yyyy-MM-dd)-1
And createdate <= TO_DATE (2011-6-16, yyyy-MM-dd) + 1;

SELECT * FROM T_XJXX_XJGL
Where createdate> TO_DATE (2011-6-15, yyyy-MM-dd)-1
And createdate <= TO_DATE (2011-6-16, yyyy-MM-dd) + 1;

Retrieve records with the current time in the range of Start Time and End Time:

SQL code
SELECT * FROM T_XJXX_XJGL
WHERE 1 = 1
AND TO_CHAR (KSSJ, yyyy-mm-dd) <= TO_CHAR (SYSDATE, yyyy-mm-dd)
AND TO_CHAR (JSSJ, yyyy-mm-dd)> = TO_CHAR (SYSDATE, yyyy-mm-dd)

SELECT * FROM T_XJXX_XJGL
WHERE 1 = 1
AND TO_CHAR (KSSJ, yyyy-mm-dd) <= TO_CHAR (SYSDATE, yyyy-mm-dd)
AND TO_CHAR (JSSJ, yyyy-mm-dd)> = TO_CHAR (SYSDATE, yyyy-mm-dd)

2 example of creating a trigger in Oracle

2.1 scenario: Create the BH field in T_XJXX_XJGL as the auto-increment type, and start by 1 increment by 1

2.2 solution: Set the BH field type to number and create a Sequence

SQL code
Create sequence SEQ_XJXX_BH
MINVALUE 1 MAXVALUE 999999
Increment by 1 start with 11
CACHE 10 noorder nocycle;

Create sequence SEQ_XJXX_BH
MINVALUE 1 MAXVALUE 999999
Increment by 1 start with 11
CACHE 10 noorder nocycle; create a trigger:

SQL code
Create or replace trigger TR_ADDID
Before insert on T_XJXX_XJGL
FOR EACH ROW
BEGIN
IF (: NEW. bh is null) THEN
SELECT SEQ_XJXX_BH.NEXTVAL INTO: NEW. bh from dual;
End if;
END;

Create or replace trigger TR_ADDID
Before insert on T_XJXX_XJGL
FOR EACH ROW
BEGIN
IF (: NEW. bh is null) THEN
SELECT SEQ_XJXX_BH.NEXTVAL INTO: NEW. bh from dual;
End if;
END;

3 common Oracle Functions

3.1 scenario: filter the data according to the user-defined timer time period and Update the next execution time. For example, if you set the mail sending frequency to 12:00:00 on the 15th day of each month, the next sending time point is calculated as the current sending time + 1 month. The setting frequency is sent at 09:00:00 on every Monday, the next sending time + one week is calculated.

3.2 solution:

1) ADD_MONTHS (x, y) function. I usually use the ADD_MONTHS function to add or subtract a month. If y is a negative integer, it indicates the operation to subtract a month; y is a positive integer that adds y months to x months.

SQL code
SELECT ADD_MONTHS (SYSDATE,-1) FROM DUAL

SELECT ADD_MONTHS (SYSDATE,-1) FROM dual 2), NEXT_DAY (x, day) returns the date of x next day. For Beginners, note that the day here refers to the Week. 1 indicates Sunday, 2 indicates Monday, and so on.

SQL code
SELECT NEXT_DAY (SYSDATE, 2) from dual;

SELECT NEXT_DAY (SYSDATE, 2) from dual; returns the date of the next Monday FROM the current time.

3) if the operation is + 1 day or-1 day, you can directly perform the +-operation on the date, like this

SQL code
SELECT SYSDATE-1 FROM dual

SELECT SYSDATE-1 FROM dual oracle supports date operations in days.

4) LAST_DAY (x) to obtain the last day of month x

5) MONTHS_BETWEEN (x, y); x> y returns a positive number, indicating the number of months between x and y (not used in the actual project, too many records are not made for the time being ).

String processing functions:

6) nvl (x, value). If x is null, return the value in value; otherwise, return x;

7), length (x), returns the length of x;

(8) substr (x, start, length) truncates string x. The length starting from start is length; if you need to take the following digits of the string, you can write as follows:

SQL code
Select substr (TEST,-2) from dual;

Select substr (TEST,-2) from dual; returns the last two characters ST

 

 

4. Tips for using pseudo columns in Oracle

4.1 scenario: Paging Using Pseudo columns in oracle is a simple and convenient paging method. In some scenarios, we can also use pseudo columns to replace group functions, so as to skillfully implement filtering requirements.

1) use pseudo columns to retrieve records of the 6th to 10th employees with the highest salary

 

SQL code
SELECT *
FROM (select rownum rn, TEMP .*
FROM (select e. ENAME, E. SAL
FROM EMP E
Where rownum <= 10
Order by e. sal desc) TEMP)
Where rn> 5

SELECT *
FROM (select rownum rn, TEMP .*
FROM (select e. ENAME, E. SAL
FROM EMP E
Where rownum <= 10
Order by e. sal desc) TEMP)
Where rn> 5

2) use pseudo columns for paging:

 

SQL code
Select B. *, RN
FROM (select a. *, rownum as RN2
FROM (select xxkc. WID,
...
ROWNUM AS RN
FROM T_PY_XXKC XXKC
Left join T_PY_KC kc on kc. KCDM = XXKC. XXKCDM
Order by xxkc. XXKCDM) A) B
Where B. RN2> v_start
And B. RN2 <= (v_start/10) + 1) * v_limit)

Select B. *, RN
FROM (select a. *, rownum as RN2
FROM (select xxkc. WID,
...
ROWNUM AS RN