Oracle基础学习四:字符串 数字 日期 等 相关函数

以下内容摘自
Oracle PL/SQL Programming(By Steven Feuerstein, Bill Pribyl)
自身并从未各个学习每个函数的效益及其用法
起趣味或发生动的话语  请自行掌握

A. String
B. Number

C. Dates and Times

A. String :

  1. ASCII(single_character)
  2. ASCIISTR(string1)
  3. CHR(code_location)
  4. COMPOSE(string1)
  5. CONCAT(string1, string2)
  6. CONVERT(string1, target_char_set)
  7. DECOMPOSE(string1)
  8. GREATEST(string1, string2, …)
  9. INITCAP(string1)
  10. INSTR(string1, string2)
  11. LEAST(string1, string2, …)
  12. LENGTH(string1)
  13. LOWER(string1)
  14. LPAD(string1, padded_length)
  15. LTRIM(string1)
  16. NCHR(code_location)
  17. NLS_INITCAP(string1)
  18. NLS_LOWER(string1) and NLS_LOWER(string1,
    ‘NLS_SORT=sort_sequence_name’)
  19. NLS_UPPER(string1) and NLS_UPPER(string1,
    ‘NLS_SORT=sort_sequence_name’)
  20. NLSSORT(string1) and NLSSORT(string1,
    ‘NLS_SORT=sort_sequence_name’)
  21. REPLACE(string1, match_string, replace_string)
  22. RPAD(string1, padded_length)
  23. RTRIM(string1)
  24. SOUNDEX(string1)
  25. SUBSTR(string1, start, length)
  26. TO_CHAR(national_character_data)
  27. TO_MULTI_BYTE(string1)
  28. TO_NCHAR(database_character_data)
  29. TO_SINGLE_BYTE(string1)
  30. TRANSLATE (string1, search_set, replace_set)
  31. TRANSLATE(text USING CHAR_CS) and TRANSLATE(text USING NCHAR_CS)
  32. TRIM (FROM string1)
  33. UNISTR(string1)
  34. UPPER(string1)

示例
SQL> SELECT LENGTH(TRIM(‘AAA   ‘)) VALUE FROM DUAL
  2  ;

     VALUE

         3

===========

B. Number :

  1. ABS(n)
  2. ACOS(n)
  3. ASIN(n)
  4. ATAN(n)
  5. ATAN2(n, m)
  6. BIN_TO_NUM(b1, b2,…bn)
  7. BITAND(n, m)
  8. CEIL(n)
  9. COS(n)
  10. COSH(n)
  11. EXP(n)
  12. FLOOR(n)
  13. GREATEST(n1, n2,…n3)
  14. LEAST(n1, n2,…n3)
  15. LN(n)
  16. LOG(b, n)
  17. MOD(n, m)
  18. NANVL(n, m)
  19. POWER(n, m)
  20. REMAINDER(n, m)
  21. ROUND(n)
  22. ROUND(n, m)
  23. SIGN(n)
  24. SIN(n)
  25. SINH(n)
  26. SQRT(n)
  27. TAN(n)
  28. TANH(n)
  29. TRUNC(n)
  30. TRUNC(n, m)

示例
SQL> SELECT POWER(4,2) AS VALUE FROM DUAL;

     VALUE

        16

====================

C. Dates and Times :

  1. ADD_MONTHS
  2. CAST
  3. CURRENT_DATE
  4. CURRENT_TIMESTAMP
  5. DBTIMEZONE
  6. EXTRACT
  7. FROM_TZ
  8. LAST_DAY
  9. LOCALTIMESTAMP
  10. MONTHS_ BETWEEN
  11. NEW_TIME
  12. NEXT_DAY
  13. NUMTODSINTERVAL
  14. NUMTOYMINTERAL
  15. ROUND
  16. SESSIONTIMEZONE
  17. SYSDATE
  18. SYS_EXTRACT_UTC
  19. SYSTIMESTAMP
  20. TO_CHAR
  21. TO_DATE
  22. TO_DSINTERVAL
  23. TO_TIMESTAMP
  24. TO_TIMESTAMP_TZ
  25. TO_YMINTERVAL
  26. TRUNC
  27. TZ_OFFSET

示例
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2         current_date DATE;
  3         next_month DATE;
  4      BEGIN
  5         current_date := SYSDATE;
  6         next_month := ADD_MONTHS(current_date, 1);
  7         DBMS_OUTPUT.PUT_LINE(next_month);
  8      END;
  9  /
16-5月 -07

PL/SQL 过程就成就。

EXTRACT (component_name, FROM {datetime | interval})

Datetime component names for use with EXTRACT

  1. YEAR
  2. MONTH
  3. DAY
  4. HOUR
  5. MINUTE
  6. SECOND
  7. TIMEZONE_HOUR
  8. TIMEZONE_MINUTE
  9. TIMEZONE_REGION
  10. TIMEZONE_ABBR

示例
SQL> BEGIN
  2         IF EXTRACT (MONTH FROM SYSDATE) = 11 THEN
  3            DBMS_OUTPUT.PUT_LINE(‘It is November’);
  4         ELSE
  5            DBMS_OUTPUT.PUT_LINE(‘It is not November’);
  6         END IF;
  7      END;
  8  /
It is not November

PL/SQL 过程都成功做到。

相关文章