oracle笔记脚本齐全第七天单行函数

1、Using Single-Row Functions to Customize Output单行函数Objectives课程目标After completing this lesson, you should be able to do the following:完成本课，你应该学到：Describe the various types of functions available in SQL了解SQL里各种常用函数Use the character, number, and date functions in SELECTstatements使用字符函数,数字函数,日期函数3 - 2Le

2、sson Agenda课程进度Single-row SQL functions单行函数 Character functions Number functions Working with datesDate functions3 - 3SQL Functions函数InputOutputFunction performs action函数处理3 - 4arg nResult valuearg 2arg 1FunctionTwo Types of SQL Functions两种类型的函数FunctionsSingle-row Functions 单行函数Multiple-row Function

3、s 多行函数Return one result per row每行返回一个结果Return one result per set of rows每个行组集返回一个结果3 - 5Single-Row Functions单行函数Single-row functions:Manipulate data items使用函数为了操作数据Accept arguments and return one value将输入的变量处理，每行返回一个结果Act on each row that is returned处理返回的每一行Return one result per row May modify the d

4、ata typeCan be nested一行返回一个结果可以转化数据类型能嵌套使用Accept arguments that can be a column or an expression传入的变量可以是列的值，也可以是表达式3 - 6function_name (arg1, arg2,.)Single-Row Functions单行函数Character 字符Single-row functionsGeneral 通用Number 数字Conversion 转换Date 日期3 - 7Lesson Agenda课程进度Single-row SQL functions Character

5、functions字符函数Number functions Working with datesDate functions3 - 8Character Functions字符函数Character functionsCharacter-manipulation Functions 字符处理类Case-conversion Functions 大小写类LOWERUPPER INITCAPCONCAT SUBSTR LENGTH INSTRLPAD | RPADTRIM REPLACE3 - 93 - 10Case-Conversion Functions大小写函数These functions

6、 convert the case for character strings以下函数将对字符串进行转化处理:3 - 11FunctionResultLOWER(SQL Course)sql courseUPPER(SQL Course)SQL COURSEINITCAP(SQL Course)Sql CourseUsing Case-Conversion Functions使用大小写函数Display the employee number, name, and department number for employee Higgins显示员工higgins的雇员号，姓名，部门号:3 -

7、12SELECT employee_id, last_name, department_id FROMemployeesWHERELOWER(last_name) = higgins;SELECT employee_id, last_name, department_id FROMemployeesWHERE last_name = higgins;Character-Manipulation Functions字符处理函数These functions manipulate character strings:3 - 13FunctionResultCONCAT(Hello, World)H

8、elloWorldSUBSTR(HelloWorld,1,5)HelloLENGTH(HelloWorld)10INSTR(HelloWorld, W)6LPAD(salary,10,*)*24000RPAD(salary, 10, *)24000*REPLACE(JACK and JUE,J,BL)BLACK and BLUETRIM(H FROM HelloWorld)elloWorldUsing the Character-Manipulation Functions字符处理函数11233 - 14SELECT employee_id, CONCAT(first_name, last_n

9、ame) NAME, 2job_id, LENGTH (last_name),INSTR(last_name, a) Contains a?3FROMemployeesWHERE SUBSTR(job_id, 4) = REP;Lesson Agenda课程进度Single-row SQL functions Character functionsNumber functions数字函数Working with datesDate Functions3 - 15Number Functions数字函数ROUND: Rounds value to a specified decimal按照指定的

10、小数位四舍五入TRUNC: Truncates value to a specified decimal按照指定的小数位截断数据MOD: Returns remainder of division两数相除，返回余数3 - 16FunctionResultROUND(45.926, 2)45.93TRUNC(45.926, 2)45.92MOD(1600, 300)100Using the ROUND Function使用ROUND函数12123DUAL is a public table that you can use to view results from functions and c

11、alculations.DUAL是一个公共表，可用它做些计算或返回函数处理结果3 - 17SELECT,3FROMDUAL;ROUND(45.923,2),ROUND(45.923,0)ROUND(45.923,-1)Using the TRUNC Function使用TRUNC函数121233 - 18SELECT3FROMDUAL;TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-1)Using the MOD Function使用MOD函数For all employees with the job title of Sales Representa

12、tive, calculate the remainder of the salary after it is divided by 5,000.计算销售代表员工的工资除以5000余数是3 - 19SELECT last_name, salary, FROMemployeesWHERE job_id = SA_REP;MOD(salary, 5000)Lesson Agenda课程进度Single-row SQL functions Character functionsNumber functionsWorking with dates日期Date functions3 - 20Workin

13、g with Dates日期The Oracle Database stores dates in an internal numeric format: century, year, month, day, hours, minutes, and seconds.Oracle数据库日期格式：世纪,年,月,日,时,分,秒The defauate display format is DD-MON-RR默认显示: DD-MON-RR.Enables you to store 21st-century dates in the 20th century by specifying only the

14、last two digits of the yearEnables you to store 20th-century dates in the 21st century in the same way默认显示年的最后2个数字3 - 21SELECT last_name, FROMemployeesWHERE hire_date SELECT sysdate DateFROM dual;3 - 412. The HR department needs a report to display the employee number, last name, salary,and salary i

15、ncreased by 15.5% (expressed as a whole number) for each employee.Label the column New Salary. Save your SQL statement in a file named lab_03_02.sql.SQLSELECT employee_id, last_name, salary, ROUND(salary * 1.155, 0) New SalaryFROM employees;SQL save /opt/soft/updba/tech/sql/less03/lab_02_02.sql3 - 4

16、23. Modify your query lab_03_02.sql to add a column that subtracts the old salary from the new salary. Label the column Increase. Save the contents of the file as lab_03_04.sql. Run the revised query.SQL get /opt/soft/updba/tech/sql/less03/lab_02_02.sql1 SELECT employee_id, last_name, salary,2 ROUND

17、(salary * 1.155, 0) New Salary 3* FROM employeesSQL 22* ROUND(salary * 1.155, 0) New SalarySQL i ,ROUND(salary * 1.155, 0) - salary Increase SQL list123SELECT employee_id, last_name, salary, ROUND(salary * 1.155, 0) New Salary,ROUND(salary * 1.155, 0) - salary Increase4* FROM employees SQL runSQL sa

18、ve /opt/soft/updba/tech/sql/less03/lab_02_03.sql3 - 434. Write a query that displays the last name (with the first letter in uppercase and all the other letters in lowercase) and the length of the last name for all employees whose name starts with the letters “J,” “A,” or “M.” Give each column an ap

19、propriate label.Sort the results by the employees last names.Rewrite the query so that the user is prompted to enter a letter that starts the last name. For example, if the user enters H (capitalized) when prompted for a letter, then the output should show all employees whose last name starts with t

20、he letter “H.”Modify the query such that the case of the entered letter does not affect the output. The entered letter must be capitalized before being processed by the SELECT query.SQLSELECT INITCAP(last_name) Name, LENGTH(last_name) LengthFROM employeesWHERE last_name LIKE J% OR last_name LIKE M%O

21、R last_name LIKE A%ORDER BY last_name ;3 - 44Rewrite the query so that the user is prompted to enter a letter that starts the last name. For example, if the user enters H (capitalized) when prompted for a letter, then the output should show all employees whose last name starts with the letter “H.”SQ

22、LSELECT INITCAP(last_name) Name, LENGTH(last_name) LengthFROM employeesWHERE last_name LIKE &start_letter% ORDER BY last_name;3 - 45Modify the query such that the case of the entered letter does not affect the output. The entered letter must be capitalized before being processed by the SELECT query.

23、SQLSELECT INITCAP(last_name) Name, LENGTH(last_name) LengthFROM employeesWHERE last_name LIKE UPPER(&start_letter% ) ORDER BY last_name;3 - 465. The HR department wants to find the duration of employment for each employee. For each employee, display the last name and calculate the number of months b

24、etween today and the date on which the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.SQLSELECT last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date) MONTHS_WORKED FROM employeesORDER BY months_worked;3 - 476. Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with the $ symbol. Label the column SALARY.SQLSELECT last_name,LPAD(salary, 15, $) SALA