游标和动态SQL

游标和动态SQL游标类别:静态游标(指在编译的时候,游标就与一个select语句进行了静态绑定的游标,这种游标只能作用于一个查询语句)和动态游标(就是希望我们的查询语句在运行的时候才跟游标绑定,为了使用动态游标,必须声明游标变量)。动态游标分两种,分别是强类型和弱类型。强类型的动态游标只能支持查询结果与他类型匹配的这种查询语句,弱类型的动态游标可以支持任何的查询语句。静态游标分为两种,隐式游标和显

大家好,又见面了,我是你们的朋友全栈君。

游标类别:静态游标(指在编译的时候,游标就与一个select语句进行了静态绑定的游标,这种游标只能作用于一个查询语句)和动态游标(就是希望我们的查询语句在运行的时候才跟游标绑定,为了使用动态游标,必须声明游标变量)。

动态游标分两种,分别是强类型和弱类型。强类型的动态游标只能支持查询结果与他类型匹配的这种查询语句,弱类型的动态游标可以支持任何的查询语句。

静态游标分为两种,隐式游标和显示游标。显示游标是有用户声明和操作的一种游标。隐式游标是Oracle为所有的数据操作语句自动声明的一种游标。

在每个用户的会话中,我们可以同时打开多个游标,这个数量有数据库初始化参数文件中的OPEN CURSORS这个参数来定义。

显示游标的用法步骤:

1、声明显式游标,语法:CURSOR<游标名>ISSELECT<语句>;

在声明游标的时候通常还要声明一些变量用来存放查询语句产生的查询结果。声明游标和变量都在declare中的。通常先声明变量,在声明游标。

2、打开游标,从打开游标开始,后面的步骤都是在beginend中执行的。语法:open<游标名>;当打开游标后查询语句就开始执行了,查询结果放到Oracle的缓冲区中,然后游标指向了这个缓冲区中查询结果的第一行记录之前。

3、提取游标,通过提取游标,游标依次指向查询结果的每一行。语法:FETCH<游标名>INTO<变量列表>;

4、关闭游标,语法:CLOSE<游标名>;

示例:

declare
 name varchar2(50);                               --定义变量存储employees表中的内容。
 department_name varchar2(20);               --定义变量存储departments表中的内容;
 cursor emp_cur IS                               --定义游标emp_cur
select name,department_name               --选出所有员工的姓名和所做部门。
from employees e,departments d
  where e.department_id=d.department_id;
begin
 open emp_cur;                                      --打开游标
 LOOP
 FETCH emp_cur INTO name,depart_name;      --将第一行数据放入变量中,游标后移。
      EXIT WHEN emp_cur%NOTFOUND;
      dbms_output.put_line(name||’在’||department_name);
 END LOOP;
 CLOSE emp_cur;
END;

游标的属性:%ISOPEN,游标是否打开;%FOUND,游标是否指向有效行;%NOTFOUND,游标是否没有指向有效行;%ROWCOUNT,游标抽取过的行数。

语法:游标名%属性名。

例如:公司上市,决定给员工提高薪资,入职时间超过1年涨1001000元封顶。

declare
 hire_date date;              --存放员工入职日期
 e_id number;                   --存放员工id
 cursor emp_cur is            --定义游标
      select id,hire_date from employees;
begin
 open emp_cur;                 --打开游标
 loop
      fetch emp_cur into e_id,hire_date       --将数据逐条存入变量
      exit when emp_cur%NOTFOUND;
      if 100*(2014-to_char(hire_date,’yyyy’))<1000 then
             update salary setsalaryvalue=salaryvalue+100*(2010-to_char(hire_date,’yyyy’)) where employee.id=e_id;
      else
             update salary setsalaryvalue=salaryvalue+1000 where employee.id=e_id;
      end if;
 end loop;
      close emp_cur;
end

使用循环游标游标的读取,语法:FOR <类型> IN <游标名>LOOP –操作各行数据 END LOOP;

DECLARE
      CURSOR emp_cur IS
      SELECT name,department_name
      FROM employees e,departments d;
      WHERE e.department_id=d.department_id;
BEGIN
      FOR employ_record IN emp_cur LOOP
             dnms_output.put_line(employ_record.name||’在’||employee_record.department_name);
      END LOOP;
END;

隐式游标

隐式游标与显示游标的区别:1、不用声明游标。2、不用打开和关闭游标。3、必须使用INTO子句,结果只能是一条。

隐式游标与显示游标的相同的:有相同的属性,隐式游标使用属性的方法是在属性名前面加上SQL%,即SQL%FOUNDSQL%ISOPEN等。

DECLARE
      name VARCHAR2(50);
      department_name varchar(20);
BEGIN
      SELECT name,department_name
      INTO name,deprtment_name
      FROM employees e,departments d;
      WHERE e.department_id=d.department_id and e.id=1;
      dbms_output.put_line(name||’在’||department_name);
END;

因为隐式游标查询结果只有一行,所以如果用来计数没有多大的意义,所以%ROECOUNT这个属性经常用来判断插入、删除、更新是否成功,但是要在COMMIT语句之前。如果在COMMIT之后,%ROECOUNT只能是0

begin
      update employees set name=name||’A’
      where id=7;
      if sql%rowcount=1 then
             dbms_output.put_line(‘表已更新!’);
      else
             dbms_output.put_line(‘编号未找到’);
      end if;
end;

REF动态游标

ref动态游标可以在运行的时候与不同的语句进行关联,他是动态的。ref动态游标被用于处理多行的查询结果集,ref动态游标是ref类型的变量,类似于指针。

定义ref动态游标类型:type<类型名> is ref cursor return <返回类型>;

声明ref动态游标:<游标名> <类型名>;

打开ref动态游标:OPEN<游标名> FOR <查询语句>;

示例:

DECLARE
      TYPE refcur_t IS REF CURSOR
      RETURN employess%ROWTYPE;
      refcur refcur_t;
      v_emp employees%ROWTYPE;
BEGIN
      OPEN refcur FOR
      SELECT * FROM employees;
      LOOP
             FETCH refcur INTO v_emp;
             EXIT WHEN refcur%NOTFOUND;
             dbms_output.put_line(refcur%ROWCOUNT||’‘||v_emp.name);
      END LOOP;
      CLOSE refcur;
END;

强类型ref动态游标:带有RETURN语句的REF动态游标。

弱类型ref动态游标:不带有RETURN语句的REF动态游标。

例如:

DECLARE
      TYPE refcur_t IS REF CURSOR 
       refcur refcur_t;
      e_id number;
      e_name varchar2(50);
BEGIN
      OPEN refcur FOR
      SELECT id,name FROM employees;
      FETCH refcur INTO e_id,e_name;
      WHILE refcur%FOUND LOOP
             dbms_output.put_line(‘#’||e_id||’:’||e_name);
             FETCH refcur INTO e_id,e_name;
      END LOOP;
      CLOSE refcur;
END;

根据用户的输入(员工、部门)打印信息

DECLARE
      TYPE refcur_t IS REF CURSOR;
      refcur refcur_t;
      p_id NUMBER;
      p_name VARCHAR2(50);
      selection VARCHAR2(1) :=UPPER(SUBSTR(‘&tab’,1,1));
BEGIN
      IF selection = ‘E’ THEN
             OPEN refcur FOR
                    SELECT id,name FROMemployees;
             dbms_output.put_line(‘===员工信息===’);
      ELSEIF selection = ‘D’ THEN
             OPEN refcur FOR
                    SELECTdepartment_id,department_name FROM departments;
             dbms_output.put_line(‘===部门信息===’);
      ELSE
             dbms_output.put_line(‘请输入员工信息E或部门信息D’);
             RETURN;
      END IF;
      FETCH refcur INTO p_id,p_name;
      WHILE refcur%FOUND LOOP
             dbms_output.put_line(‘#’||p_id||’:’||p_name);
             FETCH refcur INTO p_id,p_name;
      END LOOP;
      CLOSE refcur;
END;

创建动态
SQL
语句。

静态SQL,编译时确定。

动态SQL,不编译,执行时动态确定;根据用户输入参数等才能确定SQL语句;解决PL/SQL中不支持DDL语句的问题。

创建动态DML.DDLSQL语句语法:

EXECUTEIMMEDIATE ‘DMLDDL语句’;[INTO<变量序列>] [USING <参数序列>];只能执行返回一行或0行的语句。

如果后面的语句是个select语句,则可以使用into子句用于接收select语句选择的记录值。可以是一个变量序列,或者一个记录型变量也就是record型的变量。如果SQL语句中有参数需要动态确定,那么我们使用USING子句,USING子句用于绑定输入的参数变量。SQL语句中若有参数,使用”:参数名

示例:动态创建表

BEGIN
      EXECUTE IMMEDIATE
             ‘CREATE TABLE bonus(id NUMBER,amtNUMBER)’;
END;

示例:动态查询一个员工电话

DECLARE
      sql_stmt VARCHAR2(200);
      emp_id NUMBER(10) :=’&emp_id’;
      emp_rec employees%ROWTYPE;
BEGIN
      sql_stmt :=’select * from employees WHEREid =:id’;
      EXECUTE IMMEDIATE sql_stmt INTO emp_recUSING emp_id;
END;

示例
:
动态插入记录

DECLARE
      Sql_stmt varchar2(200);
      emp_id NUMBER(10) := ‘&emp_id’;
      emp_rec employees%ROWTYPE;
BEGIN
      sql_stmt := ‘INSERT INTO employees(id)values(:id)’;
      EXECUTE IMMEDIATE sql_stmt USING emp_id;
      Dbms_output.put_line(emp.rec.phone);
END;

EXECUTEIMMEDIATE
语句只能返回一行或没有返回,如果编写返回多行的
SQL
语句,可以使用
ref
动态游标,他的语法:
OPEN cursor_name FOR <SQL
语句
> [USING <
参数序列
>];

示例:动态输出工资大于某个数额的员工信息

DECLARE
      e_id NUMBER(10);
      e_name VARCHAR2(50);
      s_salary NUMBER(8);
      TYPE c_type is REF CURSOR;
      cur c_type;
      p_salary NUMBER := ‘&p_id’;
BEGIN
      OPEN cur FOR ‘selecte.id,e.name,e.salaryvalue from employees e,salary s where e.id=s.employeeid ands.salaryvalue >:sal ORDER BY id ASC’;
      USING p_salary;
      dbms_output.put_line(‘薪水大于’||p_salary||’的员工有:’);
      LOOP
             FETCH cur INTOe_id,e_name,e_salary;
             EXIT WHEN cur%NOTFOUND;
             dbms_output.put_line(‘编号:’||e_id||’姓名:’||e_name||’薪水:’||e_salary);
END LOOP
      CLOSE cur;
END;

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/152491.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • Linux查看开放端口_linux查看对外端口

    Linux查看开放端口_linux查看对外端口linux查看并对外开放端口(防火墙拦截处理) 查看端口是否可访问:telnetip端口号(如本机的35465:telnetlocalhost35465)开放的端口位于/etc/sysconfig/iptables中查看时通过more/etc/sysconfig/iptables命令查看如果想开放端口(如:8889)(1)通过v…

  • vi的常用命令

    vi的常用命令

  • Android测试三件套:传文件、抓包、看日志

    Android测试三件套:传文件、抓包、看日志

    2020年11月20日
  • 美国福克斯Fox热式流量计[通俗易懂]

    福克斯Fox热式流量计英维思集团的美国Foxboro公司是一家为工业过程自动化提供仪表、系统和服务的制造商,在全球同行业中处于**地位。它为用户提供的解决方案包括一切典型的智能型现场仪表,自动化系统以及先进的软件应用,以及与之相配套的综合工程。美国FOXBORO福克斯波罗因其产品的高智能和质量稳定性在电力、石油、化工、天然气、冶金、水泥、造纸、采矿、轻纺、食品、医药等行业中大量使用,获得了用户的广泛好评,产品行销全球,销售业绩在全球自动化领域内稳居前列。公司可提供从简单的需求到**的过程控制仪表,

  • mysql数据库备份方法_oracle数据库备份文件格式

    mysql数据库备份方法_oracle数据库备份文件格式数据库备份Mysqldump:逻辑备份,热备份,全量xtrabackup:物理,热,全量+增量备份一、什么是MySQL主备情况一:客户端的业务操作,读、写访问的是主库主库通过某种机制,将数据实时同步给备库主库由于有些原因,无法正常响应客户端的请求情况二:完成主备切换客户端读写,访问的是备库(此时备库升级为新主库)数据同步是如何实现的?1.主从同步原理1、在备库执行changemaster命令,绑定主库的信息mysql>CHANGEMAS

  • 区分clientHeight、scrollHeight、offsetHeight

    区分clientHeight、scrollHeight、offsetHeight区分clientHeight、scrollHeight、offsetHeightclientHeight:元素的可见高度scrollHeight:元素的整体高度offsetHeight:元素的高度参考文献:[1]搞清clientHeight、offsetHeight、scrollHeight、offsetTop、scrollTop[2]js中offsetHe…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号