Oracle用户操作的学习笔记（一）

最新推荐文章于 2025-03-03 21:04:55 发布

原创最新推荐文章于 2025-03-03 21:04:55 发布 · 224 阅读

0 ·

CC 4.0 BY-SA版权

学习笔记专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍了Oracle数据库中常用的SQL函数，包括数值处理、字符格式化、时间转换、NULL值处理、条件判断等功能，并提供了丰富的示例，帮助读者理解和掌握如何在Oracle环境中进行高效的数据操作。

1.查询用户下的所有对象(表，过程，自增序列等)

select object_name,object_type from user_objects order by object_name,object_type;

2.oracle取值函数
round(数值，小数点位数)：四舍五入

select round(((salary*12)/365),2) "Daily day" from employees;

trunc(): 去尾法

select trunc((salary*12)/365) "Daily day" from employees;

3.字符格式函数
upper(""):大写
lower(""):小写
initcap(""):首字母大写
4.时间格式转换

select to_char(sysdate,'FMMonth DD YYYY') from dual;

5.数字字符串转number类型

select to_number('4564646')+1 from dual;

6.null值标识转换
nvl():

select nvl(to_char(null),'no value') from dual;

nvl2():

select nvl2(null,'not null','no value') from dual;

7.case的用法
带参数

SELECT UNIQUE COUNTRY_ID ID,
       CASE COUNTRY_ID
         WHEN 'AU' THEN 'Australia'
         WHEN 'BR' THEN 'Brazil'
         WHEN 'CA' THEN 'Canada'
         WHEN 'CH' THEN 'Switzerland'
         WHEN 'CN' THEN 'China'
         WHEN 'DE' THEN 'Germany'
         WHEN 'IN' THEN 'India'
         WHEN 'IT' THEN 'Italy'
         WHEN 'JP' THEN 'Japan'
         WHEN 'MX' THEN 'Mexico'
         WHEN 'NL' THEN 'Netherlands'
         WHEN 'SG' THEN 'Singapore'
         WHEN 'UK' THEN 'United Kingdom'
         WHEN 'US' THEN 'United States'
       ELSE 'Unknown'
       END COUNTRY
FROM LOCATIONS
ORDER BY COUNTRY_ID;

不带参数

SELECT LAST_NAME "Name",
HIRE_DATE "Started",
SALARY "Salary",
CASE
  WHEN HIRE_DATE < TO_DATE('01-Jan-03', 'dd-mon-yy')
    THEN TRUNC(SALARY*1.15, 0)
  WHEN HIRE_DATE >= TO_DATE('01-Jan-03', 'dd-mon-yy') AND
       HIRE_DATE < TO_DATE('01-Jan-06', 'dd-mon-yy')
    THEN TRUNC(SALARY*1.10, 0)
  WHEN HIRE_DATE >= TO_DATE('01-Jan-06', 'dd-mon-yy') AND
       HIRE_DATE < TO_DATE('01-Jan-07', 'dd-mon-yy')
    THEN TRUNC(SALARY*1.05, 0)
  ELSE SALARY
END "Proposed Salary"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;

8.DECODE关键使用

SELECT LAST_NAME, JOB_ID, SALARY,
DECODE(JOB_ID,
  'PU_CLERK', SALARY * 1.10,
  'SH_CLERK', SALARY * 1.15,
  'ST_CLERK', SALARY * 1.20,
  SALARY) "Proposed Salary"
FROM EMPLOYEES
WHERE JOB_ID LIKE '%_CLERK'
AND LAST_NAME < 'E'
ORDER BY LAST_NAME;