Skip to content

singRowFunction.md

Latest commit

 

History

History
280 lines (240 loc) · 10.9 KB

singRowFunction.md

File metadata and controls

280 lines (240 loc) · 10.9 KB
  • map 1-1 giữa input và output
  • They always return a single result row for every row of a queried table.
  • They can return a data type value different from the one that is referenced.

Quote function:

  select q'[my name is]' text from dual;
  • sử dụng thay vì '' thông thường trong trường hợp text có chứ kí tự ', ví dụ q'[I'm Letaro]'
  • còn nếu dùng '' thông thường sẽ phải là 'I''m Letaro'
  • có thể sử dụng <> hoặc {} để làm delimiter

UNIQUE and DISTINCT

  • UNIQUE and DISTINCT are completely the same, nhưng UNIQUE là từ khóa cũ.
  • nếu trong select có 2 cột, ví dụ select a, b from table_c => nó sẽ combine cả 2 cột a và b để unique sự combine này

CONCAT

  • select first_name || ' and ' || last_name from customer
  • select concat ('my name is', ename) from customer

Toán

  • có thể select sysdate + 3 from dual
  • phép toán học với cột null => trả null, ví dụ: select salary * comm, mà comm bị null thì kết quả sẽ là null

BETWEEN

  • bao gồm cả 2 giá trị biên

LIKE

  • LIKE:
    • %: 0 or more character
    • _ : only 1 character

FETCH

  • cú pháp:
[offset row_to_skip row[s]] fetch [first|next] [row_count|percent PERCENT] row[s] [only|with ties]
  • If the offset is negative, Oracle treats it as 0.
  • If the offset is NULL or greater than the number of rows returned by the query, then the statement returns no rows.
  • If the offset includes a fraction like 5.5, then the Oracle truncates the fractional portion.
  • For semantic clarity purposes, you can use the keyword ROW instead of ROWS, FIRST instead of NEXT. For example, the following clauses behave the same:
    • FETCH NEXT 1 ROWS
    • FETCH FIRST 1 ROW
  • The ONLY returns exactly the number of rows or percentage of rows after FETCH NEXT (or FIRST).
  • The WITH TIES returns additional rows with the same sort key as the last row fetched.
    • Note that if you use WITH TIES, you must specify an ORDER BY clause in the query. If you don’t, the query will not return the additional rows.

alias

  • select sal [as] "a b": sử dụng "" vì có dấu cách

INITCAP

  • INITCAP( string1 ): The string argument whose first character in each word will be converted to uppercase and all remaining characters converted to lowercase.
INITCAP('tech on the net');
Result: 'Tech On The Net'

SUBSTR

  • The Oracle/PLSQL SUBSTR functions allows you to extract a substring from a string.

  • Syntax: SUBSTR( string, start_position [, length ] )

  • Parameters or Arguments

    • string: The source string.
    • start_position: The starting position for extraction. The first position in the string is always 1.
    • length: Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

  • If length is a negative number, then the SUBSTR function will return a NULL value.

  • If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).

  • If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.

  • If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.

LPAD

  • LPAD: The Oracle/PLSQL LPAD function pads the left-side of a string with a specific set of characters (when string1 is not null).
  • Syntax: LPAD( string1, padded_length [, pad_string] )
  • Parameters or Arguments
    • string1: The string to pad characters to (the left-hand side).
    • padded_length: The number of characters to return. If the padded_length is smaller than the original string, the LPAD function will truncate the string to the size of padded_length.
    • pad_string: Optional. This is the string that will be padded to the left-hand side of string1. If this parameter is omitted, the LPAD function will pad spaces to the left-side of string1.
  • ví dụ:
LPAD('tech', 2);
Result: 'te'
LPAD('tech', 8, '0');
Result: '0000tech'

RPAD

  • tương tự LPAD, nhưng bên phải

LTRIM

  • LTRIM: The Oracle/PLSQL LTRIM function removes all specified characters from the left-hand side of a string.
  • Syntax: LTRIM( string1 [, trim_string] )
  • Parameters or Arguments
    • string1: The string to trim the characters from the left-hand side.
    • trim_string: Optional. The string that will be removed from the left-hand side of string1. If this parameter is omitted, the LTRIM function will remove all leading spaces from string1.
  • The LTRIM function may appear to remove patterns, but this is not the case as demonstrated in the following example. 
    LTRIM('xxyyxzyxyyxTech', 'xyz')
Result: 'Tech'
    • It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
  • The LTRIM function can also be used to remove all leading numbers as demonstrated in the next example. 
    LTRIM( '637Tech', '0123456789')
Result: 'Tech'
    • In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all leading numbers will be removed by the LTRIM function.

RTRIM

  • RTRIM: tương tự LTRIM, nhưng bên phải

ROUND

  • ROUND (có thể hiểu là round up): The Oracle/PLSQL ROUND function returns a number rounded to a certain number of decimal places.
  • Syntax (with numbers): ROUND( number [, decimal_places] )
  • Parameters or Arguments
    • number: The number to round.
    • decimal_places: Optional. The number of decimal places rounded to. This value must be an integer. If this parameter is omitted, the ROUND function will round the number to 0 decimal places.
  • Ví dụ 
    ROUND(125.315): Result: 125
ROUND(125.315, 0): Result: 125
ROUND(125.315, 1): Result: 125.3
ROUND(125.315, 2): Result: 125.32
ROUND(125.315, 3): Result: 125.315
ROUND(-125.315, 2): Result: -125.32
ROUND(125.315, -1): Result: 130
ROUND(125.315, -2): Result: 100

TRUNC

  • TRUNC (của number) (hiểu là cắt bỏ 1 phần của số): The Oracle/PLSQL TRUNC function returns a number truncated to a certain number of decimal places.
  • The syntax for the TRUNC function in Oracle/PLSQL is: TRUNC( number [, decimal_places] )
  • Parameters or Arguments
    • number: The number to truncate.
    • decimal_places: Optional. The number of decimal places to truncate to. This value must be an integer. If this parameter is omitted, the TRUNC function will truncate the number to 0 decimal places.
  • Ví dụ:
TRUNC(125.815): Result: 125
TRUNC(125.815, 0): Result: 125
TRUNC(125.815, 1): Result: 125.8
TRUNC(125.815, 2): Result: 125.81
TRUNC(125.815, 3): Result: 125.815
TRUNC(-125.815, 2): Result: -125.81
TRUNC(125.815, -1): Result: 120
TRUNC(125.815, -2): Result: 100
TRUNC(125.815, -3): Result: 0

CEIL

  • CEIL: The Oracle/PLSQL CEIL function returns the smallest integer value that is greater than or equal to a number.
  • The syntax: CEIL( number )
  • Parameters or Arguments
    • number: The value used to find the smallest integer value.
  • Returns: The CEIL function returns an integer value.
  • Ex:
  CEIL(32.65)
  Result: 33

  CEIL(32.1)
  Result: 33

  CEIL(32)
  Result: 32

  CEIL(-32.65)
  Result: -32

  CEIL(-32)
  Result: -32

FLOOR

  • FLOOR: The Oracle/PLSQL FLOOR function returns the largest integer value that is equal to or less than a number.
  • The syntax for the FLOOR function in Oracle/PLSQL is: FLOOR( number )
  • Parameters or Arguments
    • number: The value used to determine the largest integer value that is equal to or less than a number.
  • Returns: The FLOOR function returns an integer value.
  • Ex:
  FLOOR(5.9)
  Result: 5

  FLOOR(34.29)
  Result: 34

  FLOOR(-5.9)
  Result: -6

MOD

  • MOD: The Oracle/PLSQL MOD function returns the remainder of m divided by n.
  • The syntax for the MOD function in Oracle/PLSQL is: MOD( m, n )
  • Parameters or Arguments
    • m: Numeric value used in the calculation.
    • n: Numeric value used in the calculation.
  • Calculation
    • The MOD is calculated as follows: m - n * floor(m/n)
  • Returns
    • The MOD function returns a numeric value.
    • The MOD function returns m if n is 0.
  • Ex
MOD(15, 4)
Result: 3

MOD(15, 0)
Result: 15

MOD(11.6, 2)
Result: 1.6

MOD(11.6, 2.1)
Result: 1.1

MOD(-15, 4)
Result: -3

MOD(-15, 0)
Result: -15

INSTR

  • INSTR: The Oracle/PLSQL INSTR function returns the location of a substring in a string.
  • The syntax: INSTR( string, substring [, start_position [, th_appearance ] ] )
  • Parameters or Arguments
    • string: The string to search. string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
    • substring: The substring to search for in string. substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
    • start_position: Optional. The position in string where the search will start. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the INSTR function counts back start_position number of characters from the end of string and then searches towards the beginning of string.
    • nth_appearance: Optional. The nth appearance of substring. If omitted, it defaults to 1.
  • Returns
    • The INSTR function returns a numeric value. The first position in the string is 1.
    • If substring is not found in string, then the INSTR function will return 0.
    • If string is NULL, then the INSTR function will return NULL.
    • If substring is NULL, then the INSTR function will return NULL.
  • Ex:
INSTR('Tech on the net', 'e')
Result: 2   (the first occurrence of 'e')

INSTR('Tech on the net', 'e', 1, 1)
Result: 2   (the first occurrence of 'e')

INSTR('Tech on the net', 'e', 1, 2)
Result: 11  (the second occurrence of 'e')

INSTR('Tech on the net', 'e', 1, 3)
Result: 14  (the third occurrence of 'e')

INSTR('Tech on the net', 'e', -3, 2)
Result: 2

REPLACE

  • REPLACE: The Oracle/PLSQL REPLACE function replaces a sequence of characters in a string with another set of characters.
  • Syntax: REPLACE( string1, string_to_replace [, replacement_string] )
  • Parameters or Arguments
    • string1: The string to replace a sequence of characters with another set of characters.
    • string_to_replace: The string that will be searched for in string1.
    • replacement_string: Optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the REPLACE function simply removes all occurrences of string_to_replace, and returns the resulting string.
  • Returns: The REPLACE function returns a string value.
  • Ex:
REPLACE('123123tech', '123');
Result: 'tech'

REPLACE('123tech123', '123');
Result:'tech'

REPLACE('222tech', '2', '3');
Result: '333tech'

REPLACE('0000123', '0');
Result: '123'

REPLACE('0000123', '0', ' ');
Result: '    123'

Other