ORACLE数据库 —— PL/SQL知识点2-Toy模板网

这篇具有很好参考价值的文章主要介绍了ORACLE数据库 —— PL/SQL知识点2。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

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;
   /文章来源地址https://www.toymoban.com/news/detail-480431.html