Oracle notes (5) Single Row Functions

Oracle note (5) single-row functions although each database supports SQL statements, each database also has the operation functions supported by each database. These are single-row functions, if you want to develop a database, you need to learn more functions in addition to using SQL. Single-line functions are mainly divided into the following five types: character functions, numeric functions, date functions, conversion functions, and general functions. 1. The function of character functions is mainly to operate string data, the following are several character functions: UPPER (string | column): returns the input string in uppercase; LOWER (string | column): returns the input string in lowercase; INITCAP (string | column): starts with an uppercase letter; www.2cto.com
LENGTH (string | column): Obtain the LENGTH of the string. REPLACE (string | column): REPLACE; SUBSTR (string | column, start point [, end point]): String truncation; it is a little troublesome in Oracle. Even if you want to verify the string, you must write a complete SQL statement. Therefore, for the convenience of user query, therefore, a virtual table named "dual" is provided. Example: select upper ('hello') FROM dual; SQL> SELECT UPPER ('hello') FROM dual; UPPER---HELLO: in general use, do users care about whether the data is stored in uppercase or lowercase when entering data? SELECT * FROM emp WHERE ename = '& str'; SQL> SELECT * FROM emp WHERE ename = '& str'; input str value: smith original value 1: SELECT * FROM emp WHERE ename = '& str' New value 1: SELECT * FROM emp WHERE ename = 'Smith 'unselected row SQL> SELECT * FROM emp WHERE ename =' & str '; input str value: smith original value 1: SELECT * FROM emp WHERE ename = '& str' New value 1: SELECT * FROM emp WHERE ename = 'Smith 'empno ename job mgr hiredate sal comm deptno ------------------------ ----- ---------- -------------- ---------- 7369 SMITH clerke 7902-12-12-80 800 20 at this time, if the input is in lower case, it is certainly unable to query the data, so this time cannot require so many users, therefore, the program can only adapt to this situation. Add a function: SELECT * FROM emp WHERE ename = UPPER ('& str '); SQL> SELECT * FROM emp WHERE ename = UPPER ('& str'); input str value: smith original value 1: SELECT * FROM emp WHERE ename = UPPER ('& str ') new value 1: SELECT * FROM emp WHERE ename = UPPER ('Smith ') EMPNO ENAME JOB MGR HIRE Date sal comm deptno ---------- --------- ---------- -------------- ---------- 7369 smith clerk 7902-12 months-80 800 20 of course, the "&" operation above is the content of the substitution variable, this part is not important. Example: Observe the LOWER-case conversion operation and return all employee names to select lower (ename) FROM emp according to the LOWER-case letters. Example: Capital select initcap (ename) at the beginning of each employee name) FROM emp; example: query the LENGTH of each employee's name: SELECT ename, LENGTH (ename) FROM emp; example: query the employee information whose name LENGTH is exactly 5
SELECT ename, LENGTH (ename) FROM empWHERE LENGTH (ename) = 5; example: REPLACE all the letters "A" in the name with the letter "_" select replace (ename, 'A', '_') FROM emp; there are two syntax for string truncation: Syntax 1: SUBSTR (string | column, start point ), it indicates that the image is truncated FROM the start point to the end. SELECT ename, SUBSTR (ename, 3) FROM emp; Syntax 2: SUBSTR (string | column, start point, end point ), part of the content is intercepted FROM the start point to the end point. SELECT ename, SUBSTR (ename,) FROM emp; SELECT ename, SUBSTR (ename,) FROM emp; example: it is required to extract the last three letters of each employee's name. The normal idea is to use the length-2 to determine the start point: SELECT ename, SUBSTR (ename, L ENGTH (ename)-2) FROM emp; New Idea: set a negative number to specify the truncation position FROM the back; SELECT ename, SUBSTR (ename,-3) FROM emp; interview question: when the SUBSTR () function is intercepted, does the subscript start from 0 or 1? In the Oracle database, the SUBSTR () function is the same from 0 or 1. SUBSTR () can also be set to a negative number, indicating the starting point of the truncation specified later; 2. Numeric functions there are three numeric functions: ROUND (number | column [, number of digits reserved]): rounding operation; TRUNC (number | column [, retain the decimal digits]): discard the content at the specified position; MOD (number 1, number 2): modulo, remainder; www.2cto.com example: Verify ROUND () function select round (903.53567), ROUND (-903.53567), ROUND (903.53567, 2), ROUND (-90353567,-1) FROM dual; ROUND (903.53567) ROUND (-903.53567) ROUND (903.53567, 2) ROUND (-90353567,-1)
---------------- ----------------- ---------------- ------------------- 904-904 903.54-90353570 example: Verify the TRUNC () function select trunc (903.53567), TRUNC (-903.53567), TRUNC (903.53567, 2 ), TRUNC (-90353567,-1) FROM dual; TRUNC (903.53567) TRUNC (-903.53567) TRUNC (903.53567, 2) TRUNC (-90353567,-1) ---------------- ----------------- ---------------- --------------------- 903-903 903.53-90353560 example: select mod) FROM dual; MOD () ---------- three major mathematical functions above 1 will also have matched content in learning Java. 3. If you want to perform a date operation on the date function, you must first solve the problem of how to obtain the current date, which can be obtained using "SYSDATE, the Code is as follows: select sysdate from dual; In addition to the above current date, you can also perform some calculations in the date: date + number = date, indicating the date after several days; select sysdate + 3, SYSDATE + 300 FROM dual;
Date-number = date, which indicates the date before several days; select sysdate-3, SYSDATE-300 FROM dual; date-date = number, which indicates the number of days between two days, but it must be a big date-a small date. Example: Find the number of employment days for each employee until today, SELECT ename, hiredate, SYSDATE-hiredate FROM emp. In many programming languages, A concept is also proposed, and the date can be expressed by numbers. In addition to the preceding three formulas, the following four operation functions are provided: LAST_DAY (date): the last day of the specified date. Example: SELECT LAST_DAY (SYSDATE) FROM dual; NEXT_DAY (date, number of weeks) of the last day of the month: Find the date of the next specified week X; example: Find the next Monday SELECT NEXT_DAY (SYSDATE, 'monday') FROM dual;
ADD_MONTHS (date, number): Find the date after several months; example: Find the date after four months SELECT ADD_MONTHS (SYSDATE, 4) FROM dual; MONTHS_BETWEEN (date 1, date 2): Find the month between two dates. Example: Find the SELECT ename, hiredate, TRUNC (MONTHS_BETWEEN (SYSDATE, hiredate) FROM emp; we recommend that you use the above functions for date operations during all development, because these functions can avoid the leap year problem. Www.2cto.com 4. The conversion function is now exposed to three types of data in the Oracle database: NUMBER, string (VARCHAR2), and DATE ), the main function of the conversion function is to complete the mutual conversion between these types of data. There are three types of conversion functions: TO_CHAR (string | column, Format String): Convert the date or number into a string for display; TO_DATE (string, Format String): Convert string to DATE data display;
TO_NUMBER (string): converts a string to a number for display. The a and TO_CHAR () functions query the current system date and time before: select sysdate from dual; it is displayed in the format of "day-month-year". Obviously, this display format does not conform to the normal idea. Normally, it is "year-month-day ", in this case, you can use the TO_CHAR () function, but if you use this function, you need some format strings: Year (yyyy), month (mm), and day (dd ). SELECT TO_CHAR (SYSDATE, 'yyyy-mm-dd'), TO_CHAR (SYSDATE, 'yyyy') year, TO_CHAR (SYSDATE, 'mm') month, TO_CHAR (SYSDATE, 'dd') day FROM dual; TO_CHAR (sy year mo da ---------- ---- -- 2012-08-12 2012 08 12. However, in the displayed data at this time, leading 0 exists, if you want to remove this 0, you can add a "fm ". SELECT TO_CHAR (SYSDATE, 'fmyyyy-mm-dd') day FROM dual; DAY--2012-8-12 normal people add 0, so this mark is enough, but in Oracle, DATE contains the time, but the previous Code does not show the time. To display the time, you need to add the mark: SELECT TO_CHAR (SYSDATE, 'fmyyyy-mm-dd hh24: mi: ss') day FROM dual; DAY www.2cto.com ----------------- 16:13:38 note that after using the TO_CHAR () function, all content is a string and no longer the data of the DATE type. The TO_CHAR () function can also be used to format numbers. At this time, each "9" represents the concept of a number, instead of the number 9 concept. SELECT TO_CHAR (89078907890, 'l999, 999,999,999,999 ') FROM dual; TO_CHAR (89078907890, 'l999, 999, ---------------------------- ￥89,078,907,890
The letter "L" indicates the meaning of "Local", that is, the currency symbol in the current language environment. B. TO_DATE () function the main function of this function is to convert a string into DATE data. SELECT TO_DATE ('1970-09-12 ', 'yyyy-mm-dd') FROM dual; TO_DATE ('2017 --------------- 12-9-89 this function is generally used when updating the database;
C. TO_NUMBER () function: Do not use the TO_NUMBER () function to change the string to a number. SELECT TO_NUMBER ('1') + TO_NUMBER ('2 ') FROM dual; but it is really intelligent in Oracle, so the above functions can be completed without using TO_NUMBER (): SELECT '1' + '2' FROM dual; SQL> SELECT TO_NUMBER ('1') + TO_NUMBER ('2') FROM dual; TO_NUMBER ('1') + TO_NUMBER ('2 ') --------------------------- 3 SQL> SELECT '1' + '2' FROM dual; '1' + '2' ---------- 3 so the TO_NUMBER () function is basically ignored, the key functions are TO_CHAR (), followed by TO_DATE. 5. General functions: NVL () and DECODE (). These two functions are special functions of Oracle. a and NVL () functions, handle the null www.2cto.com example: query the total annual salary of each employee SELECT ename, sal, comm, (sal + comm) * 12 FROM emp; SQL> SELECT ename, sal, comm, (sal + comm) * 12 FROM emp; ename sal comm (SAL + COMM) * 12 ---------- ------------- SMITH 800 ALLEN 1600 300 22800 WARD 1250 500 21000 JONES 2975 MARTIN 1250 1400 31800 BLAKE 2850 CLARK 2450 SCOTT 800 KING 5000 TURNER 1500 0 18000 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 has selected 14 rows. At this time, the annual salary of some employees is changed to null, and the key to this problem is that the comm field is null. To solve this problem, we must do one thing: change null to 0, and this is the role of the NVL () function. SELECT ename, sal, comm, (sal + NVL (comm, 0) * 12, NVL (comm, 0) FROM emp; SQL> SELECT ename, sal, comm, (sal + NVL (comm, 0) * 12, NVL (comm, 0) FROM emp; ename sal comm (SAL + NVL (COMM, 0 )) * 12 NVL (COMM, 0) ---------- ------------------ ----------- SMITH 800 9600 1600 0 ALLEN 300 22800 300 1250 WARD 500 21000 500 2975 JONES 35700 1250 0 MARTIN 1400 31800 1400 2850 BLAKE 34200 2450 0 CLARK 29400 800 0 SCOTT 9600 0 KING 5000 60000 0 TURNER 1500 0 18000 0 ADAMS 1100 13200 0 JAMES 950 11400 0 FORD 3000 36000 0 MILLER 1300 15600 0 selected 14 rows. B. DECODE () function: determines multiple values. The DECODE () function is very similar to the if... Else... Statement, the only difference is that the DECODE () function determines the value rather than the logical condition. For example, www.2cto.com currently requires the display of positions for all employees, but the requirements for these positions should be replaced with the Chinese characters: CLERK; SALESMAN: sales; MANAGER: MANAGER; ANALYST: ANALYST; PRESIDENT: PRESIDENT; this kind of judgment must be based on rows, so DECODE () must be used at this time. The syntax of this function is as follows: DECODE (value | column, judgment value 1, display value 1, value 2, value 2, value 3, value 3 ,...) Example: SELECT empno, ename, job, DECODE (job, 'cler', 'clerk ', 'salesman', 'salesperson', 'manager ', 'manager', 'analyst', 'analysts', 'President ', 'President') FROM emp; SQL> SELECT empno, ename, job, DECODE (job, 'cler ', 'salesman', 'manager', 'manager', 'analyst', 'Analyst ', 'President', 'President') FROM emp; empno ename job decode (J ---------- --------- -------- 7369 smith clerk 7499 allen salesman 7521 WARD SALESMAN 7566 jones manager 7654 martin salesman sales staff 7698 blke MANAGER 7782 clark manager 7788 scott clerk 7839 king president 7844 turner salesman 7876 adams clerk 7900 james clerk 7902 FORD ANALYST 7934 miller clerk has selected 14 rows. The DECODE () function is the most distinctive function in Oracle and must be mastered. Exercise: www.2cto.com 1. Select all employees in department 30. SELECT * FROM emp WHERE deptno = 30; 2. List the names, numbers, and department numbers of all clerks (clers. SELECT empno, ename, deptno FROM emp WHERE job = 'cler'; 3. Find the employees whose commission is higher than the salary. SELECT * FROM emp WHERE comm> sal; 4. Find employees with a Commission higher than 60% of their salaries. SELECT * FROM emp WHERE comm> sal * 0.6; 5. Find the details of all managers (managers) in department 10 and all clerks (CLERK) in department 20. SELECT * FROM empWHERE (job = 'manager' AND deptno = 10) OR (job = 'cler' AND deptno = 20); 6. Find all managers in department 10 ), details of all clerks (clers) in department 20 who are neither managers nor clerks but whose salaries are greater than or equal to 2000. SELECT * FROM empWHERE (job = 'manager' AND deptno = 10) OR (job = 'cler' AND deptno = 20) OR (job not in ('manager ', 'cler') AND sal> = 2000 );
7. Find out the different jobs of employees who charge commissions. Select distinct job FROM emp WHERE comm is not null; 8. Find employees who do NOT receive commission or receive commission less than 100. SELECT * FROM emp WHERE comm is null or comm <100; 9. Find all employees employed for the last 3rd days of each month. The employment date of each employee must be different. Therefore, you must find the last day of the month in which each employee is hired, and then find the date of the previous three days by "date-number, this date must be in line with the employment date to meet the conditions. SELECT * FROM emp WHERE LAST_DAY (hiredate)-2 = hiredate; 10. Find the employees employed earlier than 12 years ago. If the year is required, the most accurate method is to use the total number of months/12; SELECT * FROM emp WHERE MONTHS_BETWEEN (SYSDATE, hiredate)/12> 12; 11. The names of all employees are displayed in uppercase. Select initcap (ename) FROM emp; www.2cto.com 12. display the name of an employee with exactly five characters. SELECT ename FROM emp where length (ename) = 5; 13. display the name of an employee without "R. SELECT ename FROM emp WHERE ename not like '% R %'; 14. display the first three characters of all employees' names. Select substr (ename,) FROM emp; 15. The names of all employees are displayed. Replace "a" with "". Select replace (ename, 'A', 'A') FROM emp; 16. display the name and employment date of an employee who has been in service for 10 years. SELECT ename, hiredate FROM empWHERE MONTHS_BETWEEN (SYSDATE, hiredate)/12> 10; 17. display details of employees, sorted by name. SELECT * FROM emp order by ename; 18. display the employee's name and employment date. The oldest employee is placed at the top of the list based on the service life. SELECT ename, hiredate FROM emp order by hiredate; 19. display the names, jobs, and salaries of all employees in descending ORDER of work. if jobs are the same, they are sorted BY salary. SELECT ename, job, sal FROM emp order by job DESC, sal; 20. display the names of all employees, year and month of joining the company, sorted BY all months of employment date, if the month is the same, the employees in the earliest year are placed at the top. This program needs to extract the year and month from the date and use the TO_CHAR () function. SELECT ename, TO_CHAR (hiredate, 'yyyy') year, TO_CHAR (hiredate, 'mm') monthsFROM empORDER BY months, year; 21. The daily salary of all employees is shown as 30 days in a month. The remainder is ignored. SELECT ename, sal, TRUNC (sal/30) FROM emp; 22. Find all employees who were hired in (any year) April February. SELECT * FROM emp WHERE TO_CHAR (hiredate, 'mm') = 2; 23. The number of days each employee joins the company. SELECT ename, SYSDATE-hiredate FROM emp; 24. display the names of all employees whose names contain "A" at any position in the Name field. Www.2cto.com SELECT ename FROM emp WHERE ename LIKE '% A %'; 25. The service life of all employees is displayed by year, month, or day. Step 1: calculate the number of years of employment for each employee: Total number of months/12 = number of years; SELECT ename, hiredate, TRUNC (MONTHS_BETWEEN (SYSDATE, hiredate)/12) yearFROM emp; step 2: calculate the number of months. The decimal points ignored in the preceding calculation are actually months, so you can directly obtain the remainder. SELECT ename, hiredate, TRUNC (MONTHS_BETWEEN (SYSDATE, hiredate)/12) year, TRUNC (MOD (MONTHS_BETWEEN (SYSDATE, hiredate), 12) monthsFROM emp;
Step 3: Find the number of days. The most accurate method is to obtain the number of days within the range of 30 days. Now we know that the current time is retrieved using SYSDATE, while the hired date is retrieved using hiredate, however, the gap between hiredate and SYSDATE is too large, so there must be an error. You must try to raise the gap between hiredate and SYSDATE within 30 days. Www.2cto.com has learned two functions before: MONTHS_BETWEEN (): calculate the number of months in the two-day period. If it is: MONTHS_BETWEEN (SYSDATE, hiredate), find the month of employment from the date of employment to today; ADD_MONTHS (): add the date after the specified month to a date. If hiredate + the number of months that are separated from today = a new date, the new date must be within 30 days from SYSDATE. SELECT ename, hiredate, TRUNC (MONTHS_BETWEEN (SYSDATE, hiredate)/12) year, TRUNC (MOD (MONTHS_BETWEEN (SYSDATE, hiredate), 12) months, TRUNC (SYSDATE-ADD_MONTHS (hiredate, MONTHS_BETWEEN (SYSDATE, hiredate) dayFROM emp; The above program is a comprehensive application of date functions.