您现在的位置是:首页 > 正文

ORACLE数据库 —— PL/SQL知识点2

2024-01-30 21:03:14阅读 0

ORACLE数据库  PL/SQL语句基础知识点  适合有SQL基础的人群。 禁止转载!

内置函数
字符串函数
    lower(列名|字符串)函数用于返回字符串的小写形式。
        eg.SELECT ename,sal FROM emp WHERE ename=lower('&ename');

    upper(列名|字符串)函数用于返回字符串的大写形式。
        eg.SELECT ename,sal FROM emp WHERE ename=upper('&ename');

    initcap(列名|字符串)函数将单词的首字母大写。
        SELECT initcap('big') FROM dual;

    lpad(字符串,长度,填充字符)函数用于左补全字符串。 
        SELECT lpad('21','6','0') stock_code FROM dual;        //输出  000021
        SELECT lpad('1234567',6,'0') stock_code FROM dual;     //输出  123456 当原字符串的长度大于预期长度时,实际进行的是截取字符串操作。
    
    rpad(字符串,长度,填充字符)函数用于右补全字符串。 
        SELECT rpad('abc', 10, '*') FROM dual;            //输出  abc*******
        SELECT rpad('abcdefg', 6, '*') FROM dual;        //输出  abcdef    截取字符串时,都是从左端开始截取!

    length(列名|字符串)函数用于返回字符串的长度。
        SELECT length('abcde ') FROM dual;            //输出  6
        SELECT length('') FROM dual;                //输出  null
        SELECT length(12.51) FROM dual;            //输出  5
    
    substr(列名|字符串,截取字符串开始位置,[截取长度])函数用于截取字符串。
        SELECT substr('1234567890', 5, 4) FROM dual;        //输出  5678
    
    instr(父字符串, 子字符串, 起始位置, 匹配序号)函数用于获得子字符串在父字符串中出现的位置。
        SELECT instr('big big tiger', 'big') FROM dual;        //输出  1
        SELECT instr('big big tiger', 'big', 2) FROM dual;         //输出  3       以命令该函数从指定位置开始搜索。
        SELECT instr('big big tiger', 'big', 2, 2) FROM dual;    //输出  0     指定是第几次搜索到子字符串

    trim()函数可用于删除首尾空白符。
    ltrim() 函数用于删除字符串左端的空白符。 rtrim()函数用于删除字符串右端空白符。
        SELECT trim('   abc      ') FROM dual;            //输出abc


数学函数
    round(列名|数值,保留小数位)函数用于返回某个数字的四舍五入值。
        SELECT round(2745.173, 2) FROM dual;         //输出2745.17
        SELECT round(2745.173) FROM dual;             //输出2745     默认精确到整数
        SELECT round(2745, -1) FROM dual;             //输出2750    负数将数值精确到小数点之前的位数。
        SELECT round(-15.53, 1), round(-15.55, 1) FROM dual;    //输出-15.5    -15.6

    trunc(列名|数值)函数用于截取部分数字。该函数不对数值做四舍五入处理,而是直接截取。
        SELECT trunc(2745.575, 2) FROM dual;             //输出2745.57
        SELECT trunc(2745.575) FROM dual;            //输出2745
        当保留位数小于0时,表示保留到小数点之前的位数。    //输出2740

    mod(列1|数值1,列2|数值2)函数,第一个参数为被除数,第二个参数为除数。获得两数相除之后的余数。
        SELECT mod(5,2) FROM dual;                //输出1

    floor(列名|数值)函数用于返回小于等于某个数值的最大整数,向下取整。
        SELECT floor(21.897),floor(-21.897) FROM dual;        //输出21    -22
    
    ceil(|列名|数值), 函数将参数向上取整,以获得大于等于该参数的最小整数。
        SELECT ceil(21.897) , ceil(-21.897) FROM dual;        //输出22    -21

    sign(列名|数值)函数返回数字的正负性,若正,返回值为1; 若负,返回值为-1;若0,返回值为0
        SELECT sign(8), sign(-8) , sign(0) FROM dual;         //输出1,-1,0


日期函数
    日期+数字n=日期(n天后的日期)
    日期-数字n=日期(n天前的日期)
    日期1-日期2=n(天)
    
        eg1.查询emp表获得所有员工的雇用年数。
        SELECT ename,sysdate-hiredate FROM emp;

    months_between(日期1,日期2)函数用于获取两个日期所间隔的月数。该函数的返回值是一个实数。
        eg2.查询emp表获得所有员工的雇用年数。
        SELECT empno,hiredate,trunc(months_between(sysdate,hiredate)/12) as 雇佣年限 FROM emp;
    
    add_months(日期,数字)函数将为日期添加特定月份,并获得新的日期。
        SELECT to_char(add_months(sysdate, 20), 'yyyy-mm-dd') result FROM dual;        //返回2023-6-12

    last_day(日期) 该函数获得该月最后一天的日期。
        eg3.查询emp表获得雇用日期是月末倒数第二天的所有员工的信息。
            SELECT empno,hiredate,last_day(hiredate) FROM emp WHERE hiredate = last_day(hiredate)-2;

    to_char(日期|数字|列名,转换格式)函数用于将其他数据类型的数据转换为字符型。
        eg4.查询出所有1987年雇用的员工姓名、职位和薪水。
            SELECT ename,job,sal FROM emp WHERE to_char(hiredate,'yyyy') = '1987';
    
    to_date(字符串,转换格式)函数用于将字符串转换为日期。被转换的字符串必须符合特定的日期格式。转换格式为某种日期格式
        SELECT to_date('12/02/09', 'mm/dd/yy') result FROM dual;        // 返回02-12月-09

    to_number(字符串)函数可以将字符串转换为数值型。
        SELECT to_number('257.90') result FROM dual;            //如果不是数字,ORACLE会报错


通用函数
    nvl(列名,数值)函数用于处理某列的值。    nvl(comm,0)用于处理空值,如果其值不为空,返回comm,如果为空,则返回第二个参数的值0。
    eg5.查询每个雇员的年薪(基本工资+佣金)
        SELECT ename,sal,comm,sal*12+nvl(comm,0) 年薪 FROM emp;

    decode(列名,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)函数用于多值判断。其执行过程类似于解码操作。
    eg6.查询每个雇员的工作岗位中文名称
        SELECT ename, job, decode(job, 'CLERK','办事员','SALESMAN','销售员',
            'MANAGER','经理','ANALYST','分析员','主席') 中文岗位 FROM emp;

Oracle会根据操作符来自动进行数据类型的转换(隐式转换)
    * 字符串到数值。
    * 数值到字符串。
    * 字符串到日期。
    * 日期到字符串。
    eg7. SELECT '123' + 200, '123' || 200 FROM dual;
    eg8. SELECT months_between(sysdate , '01-1月-20') result1, '今天的日期是' || sysdate result2 FROM dual;

游标
    游标是一种PL/SQL控制结构,用来处理使用select语句从数据库中检索到的多行记录的工具,可以对多行数据逐条进行处理。游标是一个返回数据集的指针。
使用过程:1.声明游标    2.打开游标    3.检索数据    4.关闭游标

游标定义语法:
    CURSOR cursor_name [(parameter[, parameter]…)] [RETURN return_type] IS select statement;

游标的属性:
%FOUND         检验FETCH语句是否指向了记录
%ISOPEN         检查游标当前是否处在打开状态
%NOTFOUND         %FOUND的相反属性
%ROWCOUNT         检测任意给定的时刻,已从游标中获取的记录行数
%BULK_EXCEPTIONS     为批操作或Bulk Collect操作中产生的异常提供相关信息
%BULK_ROWCOUNT     提供Bulk操作过程中更改的行数信息

显式游标的使用方法:
    定义游标
         语法:CURSOR cur_name IS ..
    打开游标
         语法:OPEN cur_name
    取值游标
        语法: FETCH cur_name INTO variable_list
             variable_list必须与从游标提取的结果集类型相同
    关闭游标
         语法:CLOSE cur_name
             关闭游标后,所有资源都将被释放,且不能在此被打开。

eg9.使用游标查询每位雇员的编号和姓名
DECLARE
    CURSOR curTest IS SELECT * FROM emp;
    recEMP emp%ROWTYPE;
BEGIN
    OPEN curTest;
    FETCH curTest INTO recEMP;
    WHILE curTest%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(recEmp.empno||'号员工的姓名是'||recEmp.ename);
        FETCH curTest INTO recEMP;
    END LOOP;
    CLOSE curTest;
END;
/

eg10.(带参数) 使用游标查询某部门每位雇员姓名
DECLARE
    CURSOR cur_with_Parammm(did number) IS SELECT * FROM emp WHERE deptno = did;
    recemp emp%rowtype
BEGIN
    open cur_with_Parammm(10);
    FETCH cur_with_Parammm INTO recemp;
    IF cur_with_Parammm%NOTFOUND = true THEN
        dbms_output.put_line('该部门不存在');
    ELSE 
        LOOP
            dbms_output.put_line(recemp.ename||'是部门的员工。');
            FETCH cur_with_Parammm INTO recemp;
            EXIT WHEN cur_with_Parammm%NOTFOUND;
        END LOOP;
    dbms_output.put_line('部门共有员工'|| cur_with_Param%ROWCOUNT ||'名');
    END IF;
    CLOSE cur_with_Parammm;
END;
/

eg11.(带参数) 使用循环游标查询每位雇员的姓名            //使用for循环时,自动打开游标,而无需使用open语句;PL/SQL会自动对变量进行隐式声明;当循环结束后,游标会自动关闭。
DECLARE
    CURSOR cur_with_param(did number) ISSELECT * from emp where deptno=did;
BEGIN
     FOR recEmp IN cur_with_param(10) LOOP
         DBMS_OUTPUT.PUT_LINE(recEmp.ename||'是部门的员工。');
    END LOOP;
END;
/


使用游标更新表中数据
    使用FOR UPDATE子句,对要修改或删除的数据加行级锁。
    eg12.更新newemp表部门10的员工工资,使其工资增加100
        DEECLARE
            CURSOR salcur(v_deptno number) IS 
            SELECT sal FROM emp WHERE deptno = v_deptno  FOR UPDATE;
        BEGIN
            FOR vemp in salcur(10) LOOP
                UPDATE newemp SET sal = sal+100 WHERE CURRENT OF salcur;
            END LOOP
        END;
        /
    

游标变量
显示游标用于命名一个工作区域,其中保存多行查询的信息,而且该游标始终指向工作区域的内容。

创建游标变量:
第一步:创建一个引用的游标类型;
第二步:必须声明一个具有引用游标类型的游标变量。
通用的语法如下:
    TYPE cursortype_name is REF CURSOR [RETURN     return_type];
    Cursorvarname cursortype_name;
打开游标变量:
    OPEN Cursorvarname FOR select statement;

eg13.查询输出emp表部门10的员工工资和dept表的所有信息
    DECLARE
        TYPE v_cursortype is REF CURSOR;
        mycursort v_cursortype;
        recemp emp%rowtype;
        recdept dept%rowtype;
    BEGIN
        OPEN mycursort FOR SELECT * FROM emp WHERE deptno = 10;
        FETCH mycursort INTO recemp;
        WHILE mycursort%FOUND LOOP
             dbms_output.put_line('部门10的员工'||recemp.ename||'工资为: '|| recemp.sal);
             FETCH mycursort INTO recemp;
         END LOOP;
         OPEN mycursort FOR SELECT * FROM dept;
         FETCH mycursort INTO recdept;
         WHILE mycursort%FOUND LOOP
            dbms_output.put_line('部门'||recdept.deptno||'坐落于: '|| recdept.loc);
            FETCH mycursort INTO recdept;
        END LOOP;
         CLOSE mycursort;
    END;
    /

网站文章

  • security的一个过滤器——SecurityContextPersistenceFilter

    security的一个过滤器——SecurityContextPersistenceFilter1、关于security的用户信息获取a、SecurityContextHolder.getContex...

    2024-01-30 21:03:07
  • 聊聊ctrl+c和ctrl+z的区别

    一句话总结:ctrl+c是强制中断程序,ctrl+c是暂停程序。 Ctrl+C Ctrl+Z Ctrl+D bg fg jobs 强制中断程序,进程终止 暂停程序,挂起 退出shell 将一个在后台暂停的命令,变成继续执行 将后台中的命令调至前台继续运行 查看

    2024-01-30 21:02:36
  • 【蓝桥杯】100个数相乘末尾有几个零

    【蓝桥杯】100个数相乘末尾有几个零

    蓝桥杯-简单计算与模拟部分

    2024-01-30 21:02:23
  • 云时代架构读后感(十二)

    途牛订单的服务化演进 原文地址: https://mp.weixin.qq.com/s?__biz=MzI3MzEzMDI1OQ==&mid=2651814702&idx=1&sn=cafc4aa95db9cfdbd0373d00c633a8fb&scene=21#wechat_redirect 一个系统无论视同开发还是运行时的资源,都无法满足业务的需求...

    2024-01-30 21:02:16
  • 数字图像处理_傅里叶变换_输出矩阵的物理含义分析总结

    考虑二维傅里叶变换。傅里叶变换实现了将图像从空间域到频率域(也叫变换域)的转换,这种转换让我们得到了一个关于原图像灰度信息的频谱图,这个频谱图可以看做是图像梯度的分布图(图像梯度是两个点像素灰度的差值...

    2024-01-30 21:01:46
  • BZOJ2127Happiness

    题目描述高一一班的座位表是个n*m的矩阵,经过一个学期的相处,每个同学和前后左右相邻的同学互相成为了好朋友。这学期要分文理科了,每个同学对于选择文科与理科有着自己的喜悦值,而一对好朋友如果能同时选文科或者理科,那么他们又将收获一些喜悦值。作为计算机竞赛教练的scp大老板,想知道如何分配可以使得全班的喜悦值总和最大。题解这道题相当于给了我们一堆二元关系。容易想到用二元关系最小割来解决。我们设...

    2024-01-30 21:01:39
  • appsettings.json reloadOnChange事件处理

    appsettings.json reloadOnChange事件处理 写作一直是我比较头疼的事,从事10多年软件开发工作,基本上没写过什么文章,.net core的出现让我终于看到了.net平台的曙光,很希望广大.net开发者共同努力让.net core尽...

    2024-01-30 21:01:31
  • 【CCNP】第六章 流量控制 最新发布

    【CCNP】第六章 流量控制 最新发布

    R4(config)# access-list 1 permit 192.168.1.0 0.0.0.255 # 此处的permit不搭配接口下策略使用,不再是允许/放行的意思,而代表抓取。假设该公司...

    2024-01-30 21:01:03
  • 浙江省高校招生计算机类职业,浙江高校招生职业技能考试大纲:计算机类

    (三)数字媒体技术应用技能操作考试模块1.掌握Photoshop工具箱中常用工具的操作(1)能利用选区工具组绘制选区;(2)能利用选区编辑工具及命令进行选区的编辑;(3)能利用修复工具组和图章工具组进...

    2024-01-30 21:00:55
  • springcloud gateway分发服务报302,Network Error

    springcloud gateway分发服务报302,Network Error

    springcloud使用gateway分发服务,访问接口时一直报错:打开浏览器控制台一直提示302,然后转发到login;多次试验后发现是pom.xml文件中引入的secret依赖的问题,注释后重新...

    2024-01-30 21:00:47