一、常见函数

1、字符连接函数：concat

  1. select concat('0371-','67255555')|| '转856' 电话号码 from dual;
  2. 字符串连接另一种方法，使用  || 字符串连接符

2、首字符大写：initcap

  1. select initcap('micro  SOFT') as name from dual;
  2. (参数无论是什么样字母组成，都将第一个字母大写，其余小写)

3、字符串查找：instr

  1. select instr('410106199012132018','19901213',1,1) instring from dual;
  2. 参数1：被查找的字符串，
  3. 参数2：要查找的字符串
  4. 参数3：查找的起始位置
  5. 参数4：第几次出现

4、字符串大小写：initcap（首字符大写），upper（全部大写），lower（全部小写）

  1. select initcap(ename),upper(ename),lower(ename),eanme from emp;

5、字符补充函数 lpad（左补充）, rpad（右补充）

  1. select rpad('gao',10,'#') from dual;
  2. select lpad(rpad('gao',10,'#'),15,'*') from dual;
  3. 参数1：原字符串
  4. 参数2：补充后达到的个数
  5. 参数3：补充的字符

6、提取子字符串：substr

  1. select substr(‘13012345678’,3,8) from  dual;
  2. --从第三个字符开始，提取8个字符

7、字符串替换：replace

  1. select replace('java是世界上最好的语言！','java','php') from dual;

8、字符去除空格 ：trim

  1. --删除tech两端的字符串 ：默认删除两边的空格
  2.     select trim('     tech     ') from dual;
  3. 也可以这样 : 删除两边的指定字符
  4.     select trim('-' from '-------东方不败------') from dual;
  5. 删除 000123头部的0字符 :  删除字符串头部的指定字符
  6.     select trim(leading '0' from '000123')  from dual;
  7. 删除Teach尾部的1字符 : 删除字符串尾部的指定字符
  8.     select trim(trailing '1' from 'Teach111')  from dual;
  9. 删除123Teach111两端的字符 :  删除字符串两端的指定字符
  10.     select trim(both '1' from '123Teach1111')  from dual;

9、数学函数

  1. Ceil返回大于或等于给出数字的最小整数  
  2.     select  ceil(3.1415927)  from dual; 
  3. Floor返回小于或等于给定数字的最大值 
  4.     select floor(3.1415927) from dual;
  5. Round函数进行四舍五入，trunc函数进行截取。
  6. 第二个参数为正时，表示从小数点向后计算。
  7. 第二个参数为负时，表示从小数点向前计算。
  8.     select round(124.1666,-2),round(124.1666,2) from dual; -- 100 ，124.17
  9.     select trunc(124.1666,-2) , trunc(124.16666,2) from dual; --100 ，124.16

10、日期函数

  1. --sysdate显示当前日期 
  2. select sysdate  from dual;
  3. --日期格式化，day表示当前星期几
  4. select to_char(sysdate,’yyyy-mm-dd day’) from dual; 
  6. trunc(date,fmt)按照给出的要求将日期截断,如果fmt=‘mi’表示保留分,截断秒，就是截断分后边要显示的内容数据，
  7. --trunc(date,fmt) fmt表示截取的内容,就是保留的内容，其余的为默认值（2021.07.26 04:00:00）
  8. select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh:mi:ss')  from dual; --阶段时
  1. addMonths 增加或减去月份
  2. select add_months(sysdate,2) from dual;
  3. select to_char( add_months(sysdate,2),'yyyy-mm') from dual;
  4. select to_char( add_months( to_date('1999-12','yyyy-mm'),2), 'yyyy-mm') from dual;
  6. Months_between 显示日期相差的月数
  7. select months_between('1-1月-2022'，'26-7月-2021') 相差月份 from dual; 
  8. select months_between ( to_date('2021.05.20','yyyy.mm.dd'), to_date('2005.05.20','yyyy.mm.dd')  ) 相差月份 from dual;
  9. 总结：得到结果，取绝对值，去小数
  10. select floor(ABS(months_between('1-1月-2022'，'26-7月-2021'))) 相差月份 from dual;

11、其他函数

  1. -- 返回当前用户的唯一标识
  2. select UID from dual;
  3. -- 返回当前用户的用户名
  4. select user from dual;
  5. -- NVL(expr1, expr2)
  6. expr1不为空的时候值未expr1，为空时值为expr2
  7. --NVL2 (expr1, expr2, xpr3)
  8. expr1不为NULL，返回expr2；为NULL，返回expr3
  9. --NULLIF (expr1, expr2)
  10. expr1和expr2相等返回NULL，不等返回expr1 
  11.   max(x):求最大值
  12.   min(x):求最小值
  13.   count(x):求总数
  14.   sum(x):求和
  15.   avg(x):求平均值

二、PL/SQL

1、PL/SQL块

所有的PL/SQL程序都以块作为基本单位
块中包含过程化语句和SQL的DML语句，这些块可以按照顺序出现，也可以相互嵌套
匿名块：
匿名块是出现在应用程序中的没有名字且不存储到数据库中的块
匿名块出现在SQL语句可以出现的地方，它们可以调用其他程序，却不能被其他程序调用
命名块：
命名块是一种带有标签的匿名块，标签为块指定了一个名称
子程序：
子程序是存储在数据库中的过程（procedure），函数（function）生成之后可以被多次执行
程序包：
程序包是存储在数据库中的一组程序，变量定义
程序包中的子程序可以被其他程序或者子程序调用
触发器：
触发器是一种存储在数据库中的命名块，生成之后可以被多次执行
在相应的触发事件发生之前或之后就会被执行一次或多次

2、PL/SQL程序结构

定义部分
用于定义常量，变量，游标，异常和复杂数据类型
执行部分
用于实现应用模块功能，该部分包含需要执行的PL/SQL语句和SQL语句
异常处理部分
用于处理执行部分可能出现的运行错误

  1. DECLARE
  2.         定义部分
  3. BEGIN
  4.         执行部分
  5. EXCEPTION
  6.         异常处理部分
  7. END;
  8. ~~~~~~~~~~~~~~~~~~~~~~~~
  9. DECLARE
  10.  v_ename varchar2(50);
  11. BEGIN
  12.     select ename into v_ename from emp where empno=&eno;    
  13.     dbms_output.put_line('您要查找的姓名是：'|| v_ename);       
  14. EXCEPTION
  15.     WHEN NO_DATA_FOUND THEN
  16.     dbms_output.put_line('输入的员工编号不存在！');
  17. END;

3、常量与变量

  1. constant：常量声明专用名词
  2. :=：冒号+等号为赋值符号
  3. dbms_output.put_line('需要输出的内容'||'+拼接的内容！！！')
  4. ~~~~~~~~~~~~~~~~~~~
  5. declare
  6. v_pi constant number(6,5):=3.14; --定义圆周率常量
  7. v_r number(1):=2;
  8. v_area number(6,2); --定义保存圆面积的变量
  9. begin
  10.   v_area:=v_pi*v_r*v_r;
  11.   dbms_output.put_line('半径：'||v_r); --输出半径值
  12.   dbms_output.put_line('面积：'||v_area); --输出圆的面积
  13. end;

4、数据类型

char：固定长度字符串，长度不够时使用空格来补充，最多存储2000字节
varchar2：可变长字符串，最多可以存储4000字节
number：可以存储正数，负数，零，定点数和精度为38的浮点数，number（m，n）m为数字的总长度，n为小数点右边的位数
date：存储表中的日期和时间数据，长度为7，分别是世纪，年，月，日，时，分，秒
timestamp：存储日期的年、月、日、小时、分和秒值。其中，秒值精确到小数点后6位数，该数据类型同时包含时区信息。
clob：存储长文本信息，最多可存储4GB

blob：存储二进制对象，图像，视频和音频，最多可存储4GB

  1. a  b%type ==>表示声明一个变量a，其数据类型和b相同
  2. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. type emp_record_type is RECORD(--定义一个记录类型，包含员工信息
  4.   ename emp.ename%type,
  5.   sal emp.sal%type,
  6.   comm emp.comm%type,
  7.   total_sal sal%type
  8. );
  9. v_emp_record emp_record_type;--声明记录类型变量
  10. ==>表示声明一个记录类型emp_record_type，里边包含了一张表的数据内容类型
  11. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  12. declare 
  13. type emp_record_type is RECORD(
  14.   ename emp.ename%type,
  15.   sal emp.sal%type,
  16.   comm emp.comm%type,
  17.   total_sal sal%type
  18. );
  19.     v_emp_record emp_record_type;
  20. begin
  21.   select ename,sal,nvl(comm,0),sal+nvl(comm,0) into v_emp_record
  22.   from emp where empno=7369;
  23.   dbms_output.put_line('员工姓名：'|| v_emp_record.ename);
  24.   dbms_output.put_line('基本工资：'|| v_emp_record.sal);
  25.   dbms_output.put_line('奖金：'|| v_emp_record.comm);
  26.   dbms_output.put_line('实发工资：'|| v_emp_record.total_sal);
  27. end;
  1. type dept_table_type is table of dept%rowtype index by binary_integer;
  2. v_dept_table  dept_table_type;
  3. ==>table数据类型，使一张表的一行数据类型赋给一个变量
  4. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  5. declare
  6. type dept_table_type is table of dept%rowtype
  7. index by binary_integer;
  8. v_dept_table  dept_table_type;
  9. begin
  10.   select * into v_dept_table(0) from dept where deptno=10;
  11.   select * into v_dept_table(1) from dept where deptno=20;
  12.   dbms_output.PUT_LINE('编号：'||v_dept_table(0).deptno||'   名称：'
  13.         ||v_dept_table(0).dname||'   所在地：'||v_dept_table(0).loc);
  14.   dbms_output.PUT_LINE('编号：'||v_dept_table(1).deptno||'   名称：'
  15.         ||v_dept_table(1).dname||'   所在地：'||v_dept_table(1).loc);
  16. end;

5、流程控制

IF条件控制

  1. 条件控制1:
  2. if    条件1 then
  3. 语句段1
  4. elsif 条件2 then
  5. 语句段2
  6. elsif 条件3 then
  7. 语句段3
  8. ......
  9. elsif 条件n
  10. end id;
  11. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  12. 输入员工编号，如果该员工
  13. 原来没有奖金，则按照工资的10%发放 
  14. 原来有奖金但不超过1000的，补到1000；
  15. 其余的按照原来奖金基础再加上10%发放；
  16. declare
  17.     v_emp emp%rowtype;
  18. begin
  19.       v_emp.empno:=&no;
  20.       select * into v_emp from emp where empno=v_emp.empno;
  21.       dbms_output.PUT_LINE('更新前的奖金'||nvl(v_emp.comm,0));
  22.       if v_emp.comm is null  then
  23.             update emp set comm=v_emp.sal*0.1 where empno=v_emp.empno;
  24.       elsIf v_emp.comm<1000 then
  25.             update emp set comm=1000 where empno=v_emp.empno;
  26.       else
  27.             update emp set comm=comm+comm*0.1 where empno=v_emp.empno;  
  28.       end if;    
  29. end;

CASE条件控制

  1. 条件控制2
  2. CASE  表达式
  3. WHEN 条件表达式结果1 THEN
  4. 语句段1;
  5. WHEN 条件表达式结果2 THEN
  6. 语句段2;
  7. ......
  8. ELSE
  9. 语句段n;
  10. END CASE;
  11. ~~~~~~~~~~~~~~~~~~~~~~
  12. 根据部门编号输出部门所在地
  13.     10   纽约
  14.     20   达拉斯
  15.     30   芝加哥
  16.     40   波士顿
  17. declare
  18.       v_deptno dept.deptno%type:=&deptno;
  19. begin
  20.       case v_deptno
  21.           when 10 then  dbms_output.put_line('部门所在地：纽约');
  22.           when 20 then  dbms_output.put_line('部门所在地：达拉斯');
  23.           when 30 then  dbms_output.put_line('部门所在地：芝加哥');
  24.           when 40 then  dbms_output.put_line('部门所在地：波士顿');
  25.           else  dbms_output.put_line('不存在该部门');
  26.       end case;
  27. end;
  28. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  29. 根据员工编号输出员工工资级别
  30. sal<2000               A级工资
  31. sal>=2000 and sal<3000       B级工资
  32. 其余               C级工资
  33. declare      
  34.       v_sal emp.SAL%type;
  35. begin
  36.       select sal into v_sal from emp where empno=&empno;
  37.       case 
  38.          when v_sal<2000 then dbms_output.put_line('A级工资');
  39.           when v_sal>=2000 and v_sal<3000 then 
  40.                         dbms_output.put_line('B级工资');
  41.           else dbms_output.put_line('C级工资');
  42.       end case;
  43. exception
  44.       when no_data_found then
  45.            dbms_output.put_line('员工编号不存在');
  46. end;

LOOP循环控制

  1. 循环控制1
  2. LOOP
  3. 语句段;
  4. EXIT [WHEN 条件表达式]
  5. END LOOP;
  6. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  7. declare 
  8.       type dept_table_type is table of dept%rowtype
  9.       index by binary_integer;
  10.       i number(1):=0;
  11.       v_dept_table dept_table_type;
  12. begin
  13.       v_dept_table(0).deptno:='50';
  14.       v_dept_table(0).dname:='研发部';
  15.       v_dept_table(0).loc:='北京';
  16.       v_dept_table(1).deptno:='60';
  17.       v_dept_table(1).dname:='开发部';
  18.       v_dept_table(1).loc:='上海';
  19.       v_dept_table(2).deptno:='70';
  20.       v_dept_table(2).dname:='推广部';
  21.       v_dept_table(2).loc:='北京';
  22.       loop
  23.             if i>2 then exit; end if;
  24.             insert into dept values
  25.             ( v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc);
  26.             i:=i+1;
  27.       end loop;
  28. end;

WHILE循环控制

  1. 循环控制2
  2. WHILE 条件表达式 LOOP
  3. 语句段;
  4. END LOOP;
  5. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  6. declare 
  7.       type dept_table_type is table of dept%rowtype
  8.       index by binary_integer;
  9.       i number(1):=0;
  10.       v_dept_table dept_table_type;
  11. begin
  12.       v_dept_table(0).deptno:='50';
  13.       v_dept_table(0).dname:='研发部';
  14.       v_dept_table(0).loc:='北京';
  15.       v_dept_table(1).deptno:='60';
  16.       v_dept_table(1).dname:='开发部';
  17.       ......
  18.       while i<=2 loop
  19.            insert into dept values(                                 
  20.         v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc);
  21.             i:=i+1;
  22.       end loop;
  23. end;

FOR循环控制

  1. 循环控制3
  2. FOR 循环变量 in [REVERSE] 初值表达式..终值表达式 LOOP
  3.     语句段;
  4. END LOOP;
  5. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  6. declare 
  7.       type dept_table_type is table of dept%rowtype
  8.       index by binary_integer;
  9.       i number(1):=0;
  10.       v_dept_table dept_table_type;
  11. begin
  12.       v_dept_table(0).deptno:='50';
  13.       v_dept_table(0).dname:='研发部';
  14.       v_dept_table(0).loc:='北京';
  15.       v_dept_table(1).deptno:='60';
  16.       ......
  17.       for i in 0..v_dept_table.count-1 loop
  18.            insert into dept values
  19.             ( v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc);
  20.         end loop;
  21. end;

6、异常处理

  1. EXCEPTION
  2. WHEN 异常错误名称1 [OR 异常错误名称2......] THEN
  3. 语句段1;
  4. WHEN异常错误名称3 [OR 异常错误名称4......] THEN
  5. 语句段2;
  6. ......
  7. WHEN  OTHERS  THEN
  8. 语句段3;
  9. ~~~~~~~~~~~~~~~用法~~~~~~~~~~~~~~~~
  10. EXCEPTION
  11. WHEN 错误名称 THEN
  12. 错误之后的后续内容(例如输出：dbms_output.put_line('出错了');)
  1. 预定义异常：
  2. begin
  3.       insert into dept values(10,'aaaa','bbbb');
  4. exception
  5.       when dup_val_on_index then
  6.             dbms_output.put_line('违反唯一约束！');
  7.         when others then
  8.             dbms_output.put_line('发生其他错误！');
  9. end;
  10. 常用预定义异常：
  11.     NO_DATA_FOUND ===》select操作未返回行
  12.   TOO_MANY_ROWS ===》select操作，结果集超过一行
  13. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  14. 自定义异常：
  15. declare
  16.     dept_no_emp exception;
  17. begin
  18.   delete from emp where empno=&empno;
  19.   if sql%notfound then    --sql未正确执行
  20.     raise dept_no_emp;    --调用该错误
  21.     else
  22.       dbms_output.put_line('删除成功！');
  23.   end if;
  24.   exception 
  25.     when dept_no_emp then
  26.     dbms_output.PUT_LINE('该员工不存在！');    
  27. end;

7、事务控制

  1. Commit 语句用于提交事务
  2. Rollback 语句可以将事务回滚到事务的起点或某个保存点开始
  3. Savepoint <回滚点>    ==设置回滚点
  4. Rollback to <回滚点> ==回滚到该事务回滚点