Shulou(Shulou.com)05/31 Report--

Most people do not understand the knowledge points of this "PLSQL operator" article, so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "what are the PLSQL operators" article.

I. character function

1. LOWER (strexp)-returns a string and lowercase all characters.

Select lower ('ABCDE') from dual

2. UPPER (strexp) returns the string and capitalizes all characters.

Select upper ('abcdf') from dual

3. INITCAP (strexp) changes the first letter of a string (of each word) to uppercase, followed by lowercase

Select initcap ('source fore') from dual select initcap (' SOURCE FORE') from dual-Source Fore (if all uppercase will automatically change the first letter to uppercase and other lowercase)

4. CONCAT (strexp, strexp): concatenate two strings

Select concat (first_name,last_name) from employees

5. SUBSTR (str,start_index,length): intercepts a string of specified length from a specified location

Select substr ('abcdefg',2,3) from dual

6. LENGTH (strexp): returns the length of the string

Select length ('abcdef') from dual

7. INSTR (C1 ~ C2 ~ J): searches a string for a specified character and returns the position where the specified character was found

C1: characters searched for

C2: characters to search for

I: indicates where to start the search

J: find the number of occurrences

Select instr ('aborcdoryuklhorp','or',1,2) from dual-7 select instr (' aborcdoryuklhorp','or',6,2) from dual-14

8. LPAD (string1, padded_length, [pad_string]) pastes characters to the left of the column

Select lpad ('acd',8) from dual

9. RPAD (paste character) RPAD? Paste characters to the right of the column

Select rpad ('acd',8,'') from dual select rpad (' acdefghijklm',8,'') from dual

10. TRIM (str): intercepts special characters at both ends of a string

Select 'abd' from dual select trim ('abd') from dual

11. REPLACE (str,search_str [, replace_str]): replace each search_str that appears in str with replace_str

Select replace ('HELOVEYOU','HE','I') from dual

2. Mathematical function

1. ROUND: returns a value that is the result of rounding to the specified number of decimal places.

Select round (3000.926) from dual-- 3001-- the decimal part is two-digit select round (3000.926) from dual-- 3000.93

2. The TRUNC function returns the processed value, and its working mechanism is very similar to the ROUND function.

-- it's just that the function does not round off the parts before or after the specified decimal, but truncates them all. Select trunc (3000.926) from dual-3000 select trunc (3000.926) from dual-3000.92

3. MOD (number1,number2) divides two values and returns the rest. Operator to perform the number1 divided by number2 operation

Select mod (1600300) from dual

III. Transformation function

1. TO_CHAR (date,'fmt'): is a function of character type that converts dates to character format ('fmt')

Must be enclosed in single quotation marks and be case sensitive

Can contain any valid date format

The width of the fmt value is just enough to hold all the significant numbers

two。 Change the current language environment to Chinese

Alter session set nls_language='SIMPLIFIED CHINESE'-- enters the current system date in the format yyyy-mm-dd select sysdate from dual select to_char (sysdate,'yyyy-mm-dd') from dual-enter the current system date in the format yyyy-mon-dd select to_char (sysdate,'yyyy-mon-dd') from dual-enter the current system date in the format yyyy-month-dd select to_char (sysdate) 'yyyy-month-dd') from dual-enter the current system date in the format yyyy-mon-dd-day select to_char (sysdate,'yyyy-mon-dd day') from dual-enter the current system date in the format yyyy-mon-dd-dy select to_char (sysdate,'yyyy-mon-dd-dy') from dual-enter the current system date in yyyy-mon-dd-dy-d select to_char (sysdate) format 'yyyy/mon/dd/dy/d') from dual-- English display of ddspth date-- enter the current system date in the format yyyy-mon-ddspth select to_char (sysdate,'yyyy-mon-ddspth') from dual-- 2010-April-seventeenth

3. Change to English environment (this statement is only valid for the current window, reset when the window is closed)

Alter session set nls_language=AMERICAN -enter the current system date in the format yyyy-mm-dd select to_char (sysdate,'yyyy-mm-dd') from dual-enter the current system date in the format yyyy-mon-dd select to_char (sysdate,'yyyy-mon-dd') from dual-- 2010-apr-17-enter the current system date in the format YYYY-MON-DD select to_char (sysdate) 'yyyy-MON-dd') from dual-- 2010-APR-17-- enter the current system date in the format YYYY-MONTH-DD select to_char (sysdate,'YYYY-MONTH-DD') from dual-enter the current system date in the format yyyy-mon-dd-day select to_char (sysdate,'yyyy-mon-dd-day') from dual select to_char (sysdate) 'yyyy-mon-dd-DAY') from dual-- 2010-apr-17-SATURDAY-- enter the current system date in the format yyyy-mon-dd-dy select to_char (sysdate,'yyyy-mon-dd-dy') from dual-- 2010-apr-17-sat select to_char (sysdate,'yyyy-mon-dd-DY') from dual-- 2010-apr-17-SAT

4. English display of environmental dates in ddspth regardless of Chinese and English

-enter the current system date in the format yyyy-mon-ddspth select to_char (sysdate,'yyyy-mon-ddspth') from dual-- 2010-apr-seventeenth-- hours, minutes and seconds-- enter the current system date in the format yyyy-mon-dd HH24:MI:SS select to_char (sysdate,'yyyy-mon-dd HH24:MI:SS') from dual-- enter the current system date The format is dd-mon-yyyy SS:HH24:MI (the format is out of order)-- Note: use the to_char function to convert the date-- > the character format is not in order select to_char (sysdate,'dd-mon-yyyy SS:HH24:MI') from dual-- enter the current system date in the format yyyy-mon-dd HH24:MI:SS AM (PM)-- add AM PM-- AM PM makes no difference in this format The meaning of the expression is consistent with select to_char (sysdate,'yyyy-mon-dd HH24:MI:SS AM') from dual select to_char (sysdate,'yyyy-mon-dd HH24:MI:SS PM') from dual select to_char (sysdate,'yyyy-mon-dd HH24:MI:SS PM') from dual-- enter the current system date in the format dd of month (19 of January)-- you can add strings to the format String select to_char (sysdate,'dd of month') from dual with ""

5. To_date (char,fmt) converts characters to dates

First parameter: characters in accordance with date format second parameter: format (same as the format of date converted by to_char function) alter session set nls_language='SIMPLIFIED CHINESE' alter session set nls_language=AMERICAN;-convert 2008-April-18 this character to date select to_date ('April-18-18 April-18 years select to_date) from dual; select to_date (' April-18-2008 movie Modelyyyyyy') from dual

6.TO_CHAR (number,'fmt'): is a function of character type that converts numbers to characters

IV. Date function

1. Calculate the difference between 2008-April-1 and 2008-August-10 by months-using the months_between (date1,date2) function

Select months_between (to_date ('2008-April-01), to_date (' 2008-August-10)) from dual select months_between (to_date ('2008-August-10)), to_date (' 2008-April-01)) from dual

two。 How many months is the difference rounded to a whole number

Select round (months_between (to_date ('2008-August-10-month-10 beacon Yyyyymura), to_date (' 2008-April-01)) from dual-- give the date 2008-April-01 calculate the date six months later-- use Add_months (date1,number) select add_months (to_date ('2008-April-01)) from dual

3. Calculate the date of the next week after giving the date date and week x-using NEXT_DAY (date,'day')

Select next_day (to_date ('2010-April-01'), 'Wednesday') from dual

4. Calculates the date of the last day of the month in which the specified date is located-using last_day (date)

Select last_day (to_date ('2010-March-01) from dual above is the content of this article on "what are the PLSQL operators?" I believe you all have some understanding. I hope the content shared by the editor will be helpful to you. If you want to learn more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.