请选择 进入手机版 | 继续访问电脑版

PL/SQL 知识 (个人笔记)(四)

[复制链接]
菜鸡 发表于 2021-1-1 18:30:46 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
PL/SQL 知识 (个人笔记)(四)

个人笔记
接上篇
PL/SQL 知识 (个人笔记)(一)
PL/SQL 知识 (个人笔记)(二)
PL/SQL 知识 (个人笔记)(三)
**
之前知识点回顾:
  1. 1.变量,常量界说及赋值;赋值 变量名 :=值;常量名 constant 数据范例(长度);变量名 数据范例(长度)2.注释;单行注释: -- 多行注释: /* */3.范例;变量名/常量名 表名.列名%type--沿用表中某一列的数据范例 变量名/常量名 表名%rowtype --沿用整张表的数据范例;tyepe 范例名 is record--人为界说范例(); 变量名 范例名;--变量沿用范例的数据范例4.动态SQL动态SQL主要执行DDLSQL语句用单引号大概q'[]'包裹,用一个变量承接SQL内容,然后 execute immediate 变量;如果动态SQL里是查询语句,则需要 execute immediate 变量 into 变量;5.绑定变量SQL中是 查询 =:1的方式;execute immediate 变量 into 变量 using 值;6.流程控制if-elseif和end if成对出现elsif的写法每个if大概elsif后都有一个then, else后是没有的case when类似查询时的case when,分等值和范围判定两种;case和end case成对出现; 每个 when之后都有一个then,else之后没有7.循环重点是for循环,loop和while重要性和广泛性次之for1:for i in 1..9 loop循环体;end loop;--不需要界说循环变量,循环变量自增for2:for 变量 in(查询语句) loop循环体;end loop;--不需要界说循环变量,不限定循环上下限8.退出循环exit:退出循环体,执行循环体以外的代码continue:退出本次循环,执行下一次循环return:直接跳到end,结束步伐,return到end中间的代码不再执行
复制代码
代码编写要领:
1.底子得扎实,在写之前想好该用什么,好比 while,loop大概for
2.先写框架,再写代码块;需要什么变量,再转头去界说;
3.写好之后执行验证效果,斟酌代码逻辑;

游标(cursor):

界说:游标是将数据文件中的数据读入内存,有一个指针指向内存中数据的第一行,每fetch一次,指针往后挪一行,以内存换效率,尤其是处置惩罚大量数据时;
语法:
  1. declarecursor 游标名 is select语句;beginopen 游标名;fetch 游标名 into 变量;针对变量执行动作;close 游标名;end;
复制代码
范例:
  1. declarecursor cur_dept is select * from dept;v_dept dept%rowtype;begin  open cur_dept;    fetch cur_dept into v_dept;    dbms_output.put_line('deptno:'||v_dept.deptno||', dname:'||v_dept.dname||', LOC:'||v_dept.loc);    close cur_dept;end;
复制代码
到场循环:
  1. declarecursor cur_dept is select * from dept;v_dept dept%rowtype;begin  open cur_dept;  loop    exit when cur_dept%NOTFOUND;    fetch cur_dept into v_dept;    dbms_output.put_line('deptno:'||v_dept.deptno||', dname:'||v_dept.dname||', LOC:'||v_dept.loc);    end loop;    close cur_dept;end;
复制代码
参数:
  1. 游标名%ISOPEN:查抄游标是否打开;值返回true和false,若为true,则说明游标打开,false则说明游标未打开;游标名%FOUND:查抄游标内是否另有数据,返回true则说明有数据,false则说明无数据;游标名%NOTFOUND:查抄游标是否有数据;只返回true大概false,返回true就退出,返回false则继续往下执行;游标名%ROWCOUNT:统计本次执行行数(不是全部,只是本次);SQLCODE:报错代码,ORA-数字;SQLERRM: ERRM:error massage缩写,错误信息;
复制代码
重点:
1.游标语法;
2.每次fetch只有一行;
3.代码开始就open 游标,最后需要close游标;
4.%NOTFOUND,SQLCODE,SQLERRM,%ROWCOUNT

**
游标分类:

**
1.静态和动态
使用动态SQL的叫动态游标,别的的是静态游标;
静态游标范例:
  1. declarecursor cur_dept is select * from dept;v_dept dept%rowtype;begin  open cur_dept;  loop    fetch cur_dept into v_dept;    exit when cur_dept%NOTFOUND;    dbms_output.put_line('deptno:'||v_dept.deptno||', dname:'||v_dept.dname||', LOC:'||v_dept.loc);    end loop;    close cur_dept;end;
复制代码
动态游标范例:
  1. declare  v_sql   varchar2(300);  v_emp   emp%rowtype;  type ref_cur is ref cursor;  cur_emp ref_cur;begin  v_sql := q'[select * from emp]';  open cur_emp for v_sql;  loop  fetch cur_emp into v_emp;  exit when cur_emp%NOTFOUND;  dbms_output.put_line(v_emp.empno);  end loop;  close cur_emp;end;
复制代码
  1. declare  v_sql   varchar2(300);  v_emp   emp%rowtype;  cur_emp sys_refcursor;begin  v_sql := q'[select * from emp]';  open cur_emp for v_sql;  loop  fetch cur_emp into v_emp;  exit when cur_emp%NOTFOUND;  dbms_output.put_line(v_emp.empno);  end loop;  close cur_emp;end;
复制代码
(1).使用动态游标前须界说游标范例, 沿用系统游标范例:type ref_cur is ref cursor;
(2).游标沿用系统游标属性 的界说类似之前沿用范例的方式,是 游标名 范例名 的形式;
(3).动态游标是 open 游标名 for v_sql的方式,区别于静态游标先界说游标,再open的方式;

**
2.隐式和显式

**
有游标界说的叫显式,执行DML时会自动加隐式游标
显示游标:
  1. declarecursor cur_dept is select * from dept;v_dept dept%rowtype;begin  open cur_dept;  loop    fetch cur_dept into v_dept;    exit when cur_dept%NOTFOUND;    dbms_output.put_line('deptno:'||v_dept.deptno||', dname:'||v_dept.dname||', LOC:'||v_dept.loc);    end loop;    close cur_dept;end;
复制代码
隐式游标:
  1. declare  v_count number(5);begin  select count(1) into v_count from emp;  if SQL%FOUND then    dbms_output.put_line('隐式游标存在');  else    dbms_output.put_line('不存在');  end if;end;
复制代码
(1):在代码块中执行增删改查时,系统会自动增加隐式游标;
(2):隐式游标统一都叫 SQL,所以可以使用SQL%NOTFOUND, SQL%FOUND,SQL%OPEN等参数确认游标状态;
(3):隐式游标不需要人为打开大概关闭;

绑定变量方式:
  1. declare  v_sql   varchar2(300);  v_emp   emp%rowtype;  type ref_cur is ref cursor;  cur_emp ref_cur;  v_deptno number(4):=10;begin  v_sql := q'[select * from emp where deptno=:1]';  open cur_emp for v_sql using v_deptno;  loop  fetch cur_emp into v_emp;  exit when cur_emp%NOTFOUND;  dbms_output.put_line(v_emp.empno);  end loop;  close cur_emp;end;
复制代码
  1. (1):动态游标绑定变量方式在处置惩罚大量数据时,效率很高;(2):类似绑定变量,在使用时是 open 游标名 for v_sql using 参数 的方式;(3):别的的方面跟动态游标保持一致;
复制代码
1.测试动态游标绑定变量方式处置惩罚数据与静态游标的区别;–实际过滤数据;
2.打印全表数据,动态游标接纳 where 1=:1 绑定变量方式;

**
批量取数据

**
fetch 游标名 bulk collect into 变量名 limit 数字;
范例:
  1. declare  cursor cur_emp is select * from emp;  type t_emp is table of emp%ROWTYPE;  v_emp t_emp;begin  open cur_emp;  loop    fetch cur_emp bulk collect into v_emp limit 5;      for i in  v_emp.first..v_emp.last loop        dbms_output.put_line('EMPNO:'||v_emp(i).EMPNO||', ENAME:'||v_emp(i).ENAME);        end loop;        dbms_output.put_line(cur_emp%ROWCOUNT);      exit when cur_emp%NOTFOUND;  end loop;close cur_emp;end;
复制代码
(1): bulk collect时,变量范例需要是聚集范例,聚集范例的界说:type t_emp is table of emp%ROWTYPE;
(2): 外层使用loop循环,内层使用for循环,循环变量的范围是 变量名.first … 变量名.last 的方式;
(3): 打印大概使用数据时,需要带上下标 i, v_emp(i)体现第i行.

  1. declare  cursor cur_emp is select * from emp;  type t_emp is table of emp%ROWTYPE;  v_emp t_emp;begin  open cur_emp;  loop    fetch cur_emp bulk collect into v_emp limit 5;    exit when v_emp.count=0;    --exit when cur_emp%NOTFOUND;      for i in  v_emp.first..v_emp.last loop        dbms_output.put_line('EMPNO:'||v_emp(i).EMPNO||', ENAME:'||v_emp(i).ENAME);        end loop;        dbms_output.put_line(cur_emp%ROWCOUNT);      --exit when cur_emp%NOTFOUND;  end loop;close cur_emp;end;
复制代码
使用 游标名%NOTFOUND的退出条件,如果exit跟在fetch之后,且表中数据量不能被Limit数值整除时,会出现数据不全的现象.原因是当最后fetch的数据量不敷limit值时,会执行exit退出,所以不会操纵数据.
修改方案:
1.改用 exit when 变量名.count=0 的方式退出;–推荐
2.将exit when 游标名%NOTFOUND下移,移到外层循环结束前使用;
思考:
  1. declare  cursor cur_dept is select * from dept;  v_dept dept%ROWTYPE;begin  open cur_dept;  loop    fetch cur_dept into v_dept;    dbms_output.put_line(v_dept.deptno);    exit when cur_dept%NOTFOUND;  end loop;  close cur_dept;end;
复制代码
  1. declare  cursor cur_dept is select * from dept;  v_dept dept%ROWTYPE;begin  open cur_dept;  loop    fetch cur_dept into v_dept;    exit when cur_dept%NOTFOUND;    dbms_output.put_line(v_dept.deptno);  end loop;  close cur_dept;end;
复制代码
训练:
打印emp表的全部信息;
1、界说游标:列出每个员工的姓名、部门名称并编程显示第5个到第10个记载.
  1. declare  type t_1 is record(    ename varchar2(50),    dname varchar2(50));  v_1 t_1;  cursor cur_1 is   select ename,dname from( select rownum rn, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno) where rn>=5 and rn 1000;begin  open cur_emp;  loop    fetch cur_emp into v_emp;    exit when cur_emp%NOTFOUND;    if mod(cur_emp%ROWCOUNT, 2) = 1 then      dbms_output.put_line('ENAME:' || v_emp.ename || ', DEPTNO' ||v_emp.deptno || ', SAL:' || v_emp.sal);    end if;  end loop;  close cur_emp;end;
复制代码
3、用游标显示所有部门编号与名称,以及其所拥有的员工人数。
  1. declarecursor cur_check is select b.deptno,b.dname,count(1) cnt from emp a, dept b where a.deptno=b.deptno group by b.deptno,b.dname;type t_dept is record(   deptno  dept.deptno%TYPE,   dname  dept.dname%TYPE,     cnt number(3));v_dept t_dept;begin  open cur_check;  loop    fetch cur_check into v_dept;    exit when cur_check%NOTFOUND;    dbms_output.put_line('Deptno:'||v_dept.deptno||', Dname: '||v_dept.dname||', Cnt'||v_dept.cnt);    end loop;    close cur_check;end;
复制代码
4、用游标属性%rowcount实现输出前十个员工的信息
  1. declare  cursor cur_emp is select * from emp;  v_emp emp%ROWTYPE;begin  open cur_emp;  loop    fetch cur_emp into v_emp;    exit when cur_emp%NOTFOUND;    if (cur_emp%ROWCOUNT > 10) then      exit;    end if;    dbms_output.put_line(v_emp.ename);  end loop;  close cur_emp;end;
复制代码
5、通过使用游标来显示dept表中的部门名称,及其相应的员工列表(提示:可以使用双重循环)。
  1. declare  cursor cur1 is    select * from dept;  v_dept  dept%ROWTYPE;  v_ename emp.ename%TYPE;  type t_emp is table of emp%ROWTYPE;  v_emp t_emp;begin  open cur1;  loop    fetch cur1      into v_dept;    exit when cur1%NOTFOUND;    dbms_output.put_line('');    dbms_output.put_line(v_dept.dname || ':');    for i in (select ename from emp where deptno = v_dept.DEPTNO) loop      dbms_output.put_line(i.ename);    end loop;  end loop;  close cur1;end;
复制代码
6、继承一个部门号,使用For循环,从emp表中显示该部门的所有雇员的姓名,工作和薪水。
  1. declarev_deptno number(4):=&Deptno;begin  for v in (select * from emp where mgr in(select mgr from emp where deptno=v_deptno)) loop    dbms_output.put_line('Ename:'||v.ename||', Job:'||v.job||', Sal:'||v.sal);    end loop;end;
复制代码
来源:https://blog.csdn.net/yang_z_1/article/details/111880054
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )