Shulou(Shulou.com)06/01 Report--

Data type conversions are as follows:

Implicit data type conversion

Show data type conversion

Implicit data type conversion:

In the expression, the Oracle server automatically completes the following conversions

FromToVARCHAR2 or CHARNUMBERVARCHAR2 or CHARDATENUMBERVARCHAR2 or CHARDATEVARCHAR2 or CHAR

Show data type conversion

1. Use TO_CHAR function to convert date

Format: TO_CHAR (date, 'format_model')

Must be enclosed in single quotation marks

Case sensitive.

Can contain any valid date format.

You can use fm to remove extra spaces or leading zeros.

Separated from the date value by a comma.

The composition of the date format

Element result YYYY complete year YEAR year MM double digit month MONTH full month name MON month three letter acronym DY week three letter acronym DAY full week name DD month numeric day

1. Find the entry date of the employee and display it according to the day, month and year.

Select last_name,to_char (hire_date,'fmDD Month YYYY') as hiredate from employees

2. Inquire about the entry date of employees according to the number of weeks, months and years in English

Select last_name,to_char (hire_date,'fmDAY Month YEAR') as hiredate from employees

3. Inquire the entry date of employees according to the digital day, month and year.

Select last_name,to_char (hire_date,'fmDD MM YYYY') as hiredate from employees

4. Inquire the entry date of employees according to the way of year, month and day

Select last_name,to_char (hire_date,'YYYY,MM,fmDD') as hiredate from employees

5. Create a report that now has an employee entry date of Saturday, and displays information about the FIRST_NAME and LAST_NAME columns and an expression aliased as START_DATE.

Select first_name,last_name,to_char (hire_date,'fmDAY Month YYYY') start_date from employees where to_char (hire_date,'fmDAY') = 'SATURDAY'

It can also be displayed like this.

Select first_name,last_name,to_char (hire_date,'fmDAY, "the" ddth "of" Month, Yyyysp.') Start_date from employees where to_char (hire_date,'fmDAY') = 'SATURDAY'

Conversion of numbers using TO_CHAR function

Element result 9 represents a number 0 that forces the display of 0$ to place a floating dollar sign L using the floating local currency symbol. Print a decimal point and print a comma as a thousand-digit identifier

1. Find the salary of the employee named Ernst and output it in the format of $99999.0

Select salary,to_char (salary,'$99999.00') salary from employees where last_name='Ernst'

2. Find the salary of the employee named Zlotkey and output it in the format of $99999.0

Select salary,to_char (salary,'L0,0000.000') salary from employees where last_name='Zlotkey'

Using the TO_NUMBER and TO_DATE functions

Use the TO_NUMBER function to convert characters to numeric format:

TO_NUMBER (char [, 'format_model'])

Use the TO_DATE function to convert a string to a date format:

TO_DATE (char [, 'format_model'])

This function has a fx modifier that specifies that the character arguments in TO_DATE match exactly with the format.

Using the RR date format, look in the employee table for employees who joined before 1990 and execute the query in 1999 or now, and the results are the same.

1. Find employees who have joined the workforce since January 1, 2005.

Select last_name,to_char (hire_date,'DD-Mon-YYYY') from employees where hire_date > to_date.

Nested function

One-line functions can be nested to any level

The execution order of nested functions is from the inside to the outside.

1. Find the employee last_name of Department 60 and capitalize the name, stitching it with US.

Select last_name,upper (concat (substr (last_name,1,8),'_ US')) con_last_name from employees where department_id=60

Generic functions: these functions apply to any data type, as well as to null values:

NVL (expr1, expr2): returns expr2 if the expr1 bit is empty

NVL2 (expr1, expr2, expr3): if the parameter expression expr1 value is NULL, the NVL2 () function returns the value of the parameter expression expr3; if the parameter expression expr1 value is not NULL, the NVL2 () function returns the value of the parameter expression expr2.

NULLIF (expr1, expr2): returns a null value if two specified expressions are equal. Unequal returns expr1

COALESCE (expr1, expr2,..., exprn): refer to each parameter expression in turn, stop and return a non-null value. If all expressions are null, a null value will eventually be returned

NVL function

Convert a null value to a known value:

The data types that can be used are numbers, dates, and characters.

The data types must match:

-NVL (commission_pct,0)

-NVL (hire_date,'01-JAN-97')

-NVL (job_id,'No Job Yet')

1. Calculate the annual salary of employees according to last_name, commission, and 12*salary display

Select last_name,salary,nvl (commission_pct,0) comm, (salary*12) + (salary*12*NVL (commission_pct,0)) AN_SAL from employees

2. Query the salaries of employees in departments 50 and 80 and now display SAL if commission_pct is empty and SAL+COMM if not empty

Select last_name,salary,commission_pct, nvl2 (commission_pct, 'SAL+COMM',' SAL') income from employees where department_id in (50. 60)

3. Find employees who neither make a commission nor lead id, as shown by last_name and employee_id

Select last_name,employee_id,coalesce (to_char (commission_pct), to_char (manager_id),'No commission and no manager') from employees

4. Create a report that requires you to retrieve a set of rows with a DEPARTMENT_ID of 100 in the EMPLOYEES table. This collection contains

FIRST_NAME and LAST_NAME, and expressions aliased as NAME_LENGTHS. If FIRST_NAME and

The LAST_NAME length is not equal. The expression returns the string 'Different Length',' otherwise the return string is' Same

Length'

Select last_name,first_name,nvl2 (nullif (length (last_name), length (first_name)), 'Different Length','Same Length') name_lengths from employees where department_id=100

Conditional expression

Use IF-THEN-ELSE logic in SQL statements.

Use two methods:

-CASE expression

-DECODE function

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

An example of implementing IF-THEN-ELSE using an case expression:

1. The salary of the employees whose job_id is it_prog, st_clerk, sa_rep and other positions will be raised according to the ratio of 1.1max 1.15max 1.2 respectively, except for the positions in these three departments, all the positions will be displayed normally.

Select last_name, job_id, salary

Case job_id when 'it_prog' then 1.10*salary

When 'st_clerk' then 1.15*salary

When 'sa_rep' then 1.20*salary

Else salary end "revised_salary"

From employees

LAST_NAME JOB_ID SALARY revised_salary

--

OConnell SH_CLERK 2600 2600

Grant SH_CLERK 2600 2600

Whalen AD_ASST 4400 4400

Hartstein MK_MAN 13000 13000

Fay MK_REP 6000 6000

Mavris HR_REP 6500 6500

Baer PR_REP 10000 10000

Higgins AC_MGR 12008 12008

Gietz AC_ACCOUNT 8300 8300

King AD_PRES 24000 24000

Kochhar AD_VP 17000 17000

De Haan AD_VP 17000 17000

Hunold IT_PROG 9000 9000

Ernst IT_PROG 6000 6000

Austin IT_PROG 4800 4800

Pataballa IT_PROG 4800 4800

Lorentz IT_PROG 4200 4200

Greenberg FI_MGR 12008 12008

Faviet FI_ACCOUNT 9000 9000

Chen FI_ACCOUNT 8200 8200

Sciarra FI_ACCOUNT 7700 7700

Urman FI_ACCOUNT 7800 7800

Popp FI_ACCOUNT 6900 6900

Raphaely PU_MAN 11000 11000

Khoo PU_CLERK 3100 3100

Baida PU_CLERK 2900 2900

Tobias PU_CLERK 2800 2800

Himuro PU_CLERK 2600 2600

Colmenares PU_CLERK 2500 2500

Weiss ST_MAN 8000 8000

Fripp ST_MAN 8200 8200

Kaufling ST_MAN 7900 7900

Vollman ST_MAN 6500 6500

Mourgos ST_MAN 5800 5800

Nayer ST_CLERK 3200 3200

Mikkilineni ST_CLERK 2700 2700

Landry ST_CLERK 2400 2400

Markle ST_CLERK 2200 2200

Bissot ST_CLERK 3300 3300

Atkinson ST_CLERK 2800 2800

Marlow ST_CLERK 2500 2500

Olson ST_CLERK 2100 2100

Mallin ST_CLERK 3300 3300

Rogers ST_CLERK 2900 2900

Gee ST_CLERK 2400 2400

Philtanker ST_CLERK 2200 2200

Ladwig ST_CLERK 3600 3600

Stiles ST_CLERK 3200 3200

Seo ST_CLERK 2700 2700

Patel ST_CLERK 2500 2500

Rajs ST_CLERK 3500 3500

Davies ST_CLERK 3100 3100

Matos ST_CLERK 2600 2600

Vargas ST_CLERK 2500 2500

Russell SA_MAN 14000 14000

Partners SA_MAN 13500 13500

Errazuriz SA_MAN 12000 12000

Cambrault SA_MAN 11000 11000

Zlotkey SA_MAN 10500 10500

Tucker SA_REP 10000 10000

Bernstein SA_REP 9500 9500

Hall SA_REP 9000 9000

Olsen SA_REP 8000 8000

Cambrault SA_REP 7500 7500

Tuvault SA_REP 7000 7000

King SA_REP 10000 10000

Sully SA_REP 9500 9500

McEwen SA_REP 9000 9000

Smith SA_REP 8000 8000

Doran SA_REP 7500 7500

Sewall SA_REP 7000 7000

Vishney SA_REP 10500 10500

Greene SA_REP 9500 9500

Marvins SA_REP 7200 7200

Lee SA_REP 6800 6800

Ande SA_REP 6400 6400

Banda SA_REP 6200 6200

Ozer SA_REP 11500 11500

Bloom SA_REP 10000 10000

Fox SA_REP 9600 9600

Smith SA_REP 7400 7400

Bates SA_REP 7300 7300

Kumar SA_REP 6100 6100

Abel SA_REP 11000 11000

Hutton SA_REP 8800 8800

Taylor SA_REP 8600 8600

Livingston SA_REP 8400 8400

Grant SA_REP 7000 7000

Johnson SA_REP 6200 6200

Taylor SH_CLERK 3200 3200

Fleaur SH_CLERK 3100 3100

Sullivan SH_CLERK 2500 2500

Geoni SH_CLERK 2800 2800

Sarchand SH_CLERK 4200 4200

Bull SH_CLERK 4100 4100

Dellinger SH_CLERK 3400 3400

Cabrio SH_CLERK 3000 3000

Chung SH_CLERK 3800 3800

Dilly SH_CLERK 3600 3600

Gates SH_CLERK 2900 2900

Perkins SH_CLERK 2500 2500

Bell SH_CLERK 4000 4000

Everett SH_CLERK 3900 3900

McCain SH_CLERK 3200 3200

Jones SH_CLERK 2800 2800

Walsh SH_CLERK 3100 3100

Feeney SH_CLERK 3000 3000

107 rows selected.

DECODE function

When you need to use IF-THEN-ELSE logic:

DECODE (col | expression, search2, result1

[, search3, result2,...,]

[, default])

As in the above example, there is a corresponding increase in money for employees in the three positions of it_prog, st_clerk and sa_rep, and other positions are calculated according to the default.

Select last_name, job_id, salary

Decode (job_id, 'it_prog', 1.10*salary

'st_clerk', 1.15*salary

'sa_rep', 1.20*salary

Salary)

Revised_salary

From employees

LAST_NAME JOB_ID SALARY REVISED_SALARY

--

OConnell SH_CLERK 2600 2600

Grant SH_CLERK 2600 2600

Whalen AD_ASST 4400 4400

Hartstein MK_MAN 13000 13000

Fay MK_REP 6000 6000

Mavris HR_REP 6500 6500

Baer PR_REP 10000 10000

Higgins AC_MGR 12008 12008

Gietz AC_ACCOUNT 8300 8300

King AD_PRES 24000 24000

Kochhar AD_VP 17000 17000

De Haan AD_VP 17000 17000

Hunold IT_PROG 9000 9000

Ernst IT_PROG 6000 6000

Austin IT_PROG 4800 4800

Pataballa IT_PROG 4800 4800

Lorentz IT_PROG 4200 4200

Greenberg FI_MGR 12008 12008

Faviet FI_ACCOUNT 9000 9000

Chen FI_ACCOUNT 8200 8200

Sciarra FI_ACCOUNT 7700 7700

Urman FI_ACCOUNT 7800 7800

Popp FI_ACCOUNT 6900 6900

Raphaely PU_MAN 11000 11000

Khoo PU_CLERK 3100 3100

Baida PU_CLERK 2900 2900

Tobias PU_CLERK 2800 2800

Himuro PU_CLERK 2600 2600

Colmenares PU_CLERK 2500 2500

Weiss ST_MAN 8000 8000

Fripp ST_MAN 8200 8200

Kaufling ST_MAN 7900 7900

Vollman ST_MAN 6500 6500

Mourgos ST_MAN 5800 5800

Nayer ST_CLERK 3200 3200

Mikkilineni ST_CLERK 2700 2700

Landry ST_CLERK 2400 2400

Markle ST_CLERK 2200 2200

Bissot ST_CLERK 3300 3300

Atkinson ST_CLERK 2800 2800

Marlow ST_CLERK 2500 2500

Olson ST_CLERK 2100 2100

Mallin ST_CLERK 3300 3300

Rogers ST_CLERK 2900 2900

Gee ST_CLERK 2400 2400

Philtanker ST_CLERK 2200 2200

Ladwig ST_CLERK 3600 3600

Stiles ST_CLERK 3200 3200

Seo ST_CLERK 2700 2700

Patel ST_CLERK 2500 2500

Rajs ST_CLERK 3500 3500

Davies ST_CLERK 3100 3100

Matos ST_CLERK 2600 2600

Vargas ST_CLERK 2500 2500

Russell SA_MAN 14000 14000

Partners SA_MAN 13500 13500

Errazuriz SA_MAN 12000 12000

Cambrault SA_MAN 11000 11000

Zlotkey SA_MAN 10500 10500

Tucker SA_REP 10000 10000

Bernstein SA_REP 9500 9500

Hall SA_REP 9000 9000

Olsen SA_REP 8000 8000

Cambrault SA_REP 7500 7500

Tuvault SA_REP 7000 7000

King SA_REP 10000 10000

Sully SA_REP 9500 9500

McEwen SA_REP 9000 9000

Smith SA_REP 8000 8000

Doran SA_REP 7500 7500

Sewall SA_REP 7000 7000

Vishney SA_REP 10500 10500

Greene SA_REP 9500 9500

Marvins SA_REP 7200 7200

Lee SA_REP 6800 6800

Ande SA_REP 6400 6400

Banda SA_REP 6200 6200

Ozer SA_REP 11500 11500

Bloom SA_REP 10000 10000

Fox SA_REP 9600 9600

Smith SA_REP 7400 7400

Bates SA_REP 7300 7300

Kumar SA_REP 6100 6100

Abel SA_REP 11000 11000

Hutton SA_REP 8800 8800

Taylor SA_REP 8600 8600

Livingston SA_REP 8400 8400

Grant SA_REP 7000 7000

Johnson SA_REP 6200 6200

Taylor SH_CLERK 3200 3200

Fleaur SH_CLERK 3100 3100

Sullivan SH_CLERK 2500 2500

Geoni SH_CLERK 2800 2800

Sarchand SH_CLERK 4200 4200

Bull SH_CLERK 4100 4100

Dellinger SH_CLERK 3400 3400

Cabrio SH_CLERK 3000 3000

Chung SH_CLERK 3800 3800

Dilly SH_CLERK 3600 3600

Gates SH_CLERK 2900 2900

Perkins SH_CLERK 2500 2500

Bell SH_CLERK 4000 4000

Everett SH_CLERK 3900 3900

McCain SH_CLERK 3200 3200

Jones SH_CLERK 2800 2800

Walsh SH_CLERK 3100 3100

Feeney SH_CLERK 3000 3000

107 rows selected.

2. For each employee who shows that the department is 80, the applicable tax rate is:

It means that if the intercept is zero, the tax rate is 0, the tax rate is 0, the tax rate is 0.09 2, the tax rate is 0.2, and so on.

Select last_name, salary

Decode (trunc (salary/2000, 0)

0, 0.00

1, 0.09

2, 0.20

3, 0.30

4, 0.40

5, 0.42

6, 0.44

0.45) tax_rate

From employees

Where department_id = 80

LAST_NAME SALARY TAX_RATE

Russell 14000. 45

Partners 13500. 44

Errazuriz 12000. 44

Cambrault 11000. 42

Zlotkey 10500. 42

Tucker 10000. 42

Bernstein9500. 4

Hall9000. 4

Olsen8000. 4

Cambrault7500. 3

Tuvault 7000. 3

King 10000. 42

Sully9500. 4

McEwen9000. 4

Smith8000. 4

Doran7500. 3

Sewall7000. 3

Vishney 10500. 42

Greene9500. 4

Marvins 7200. 3

Lee6800. 3

Ande6400. 3

Banda6200. 3

Ozer 11500. 42

Bloom 10000. 42

Fox9600. 4

Smith7400. 3

Bates7300. 3

Kumar6100. 3

Abel 11000. 42

Hutton8800. 4

Taylor8600. 4

Livingston8400. 4

Johnson 6200. 3

34 rows selected.

3. Query the rows in the LOCALTIONS table. The value of the COUNTRY_ID column of these rows is US. Please use the alias LOCALTION_INFO.

To evaluate the STATE_PROVINCE column value and return different information The result is as follows:

STATE_PROVINCE returns the Washington string 'Headquarters'Texas string' Oil Wells' roomniaCITY value New JerseySTREET _ ADDRESS column value

Select decode (state_province,'Washington','Headquarters','Texas','Oil Wells','California',city,'New Jersey'

Street_address) location_info,state_province,city,street_address,country_id from locations where country_id='US'

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.