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)
     select emp_sequence((sequence name).nextval into:new.memberId((auto increment field) from dual;

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;
    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 = Equivalent to Select * from t_A a,t_B b where;
     Similarly   Select * from t_A a,t_B b where; 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;

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;
    SQL> select substr('adbdfg',2) from dual;
  • 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;

floor function usage

  • Function: returns the maximum integer less than or equal to n
    SQL> SELECT FLOOR(0.1) FROM dual;

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 trunc(sysdate) from dual --2013-01-06 Today's date is 2013-01-06 trunc(sysdate, 'mm') from dual --2013-01-01 Return to the first day of the month. trunc(sysdate,'yy') from dual --2013-01-01 Return to the first day of the year trunc(sysdate,'dd') from dual --2013-01-06 Return to the current date trunc(sysdate,'yyyy') from dual --2013-01-01 Return to the first day of the year trunc(sysdate,'d') from dual --2013-01-06 (Sunday)Returns the first day of the current week trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 The current time is 17:35 trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()Function has no precision of seconds
  • Use figures
    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