游标概述学习

游标分为静态游标和动态游标，其中静态游标又分为隐式游标和显示游标，动态游标分为强类型，弱类型。

显示游标的使用步骤和语法

显示游标通常与查询语句结合使用，用于循环获取结果集。

1.声明游标

　　CURSOR<游标名>IS SELECT<语句>；

2.打开游标

　　OPEN<游标名>;

3.提取游标

　　FETCH<游标名>INTO<变量列表>;

4.关闭游标

　　CLOSE<游标名>;

示例：

DECLARE
v_name emp.ename%TYPE;--声明变量
v_empno emp.empno%TYPE;--声明变量
CURSOR emp_cur IS --定义游标
SELECT ename,empno FROM emp;--查询语句
BEGIN
OPEN emp_cur;--打开游标
LOOP
FETCH emp_cur INTO v_name,v_empno;--提取游标
EXIT WHEN emp_cur%NOTFOUND;
dbms_output.put_line(emp_cur%ROWCOUNT||' '||v_empno||' '||v_name);
END LOOP;
CLOSE emp_cur;--关闭游标
END;

 

游标的属性：

%ISOPEN 游标是否打开，返回布尔值；

%FOUND 游标是否指向有效行，返回布尔值；

%NOTFOUND 游标是否没有指向有效行，返回布尔值；

%ROWCOUNT 游标抽取的行数，DML语句影响行的情况返回1否则返回0；

使用方法：游标名%属性名

循环游标

其中"类型"循环游标自动创建%ROWTYPE类型的变量并将此变量用作记录索引。

FOR<类型>IN<游标名>LOOP...;

--数据操作--

END LOOP;

示例

DECLARE
emp_row emp%ROWTYPE;--员工 信息表行类型
dept_row dept%ROWTYPE;--部门 信息表行类型
CURSOR emp_cur IS --声明游标
SELECT e.empno,e.ename FROM emp e,dept d--自定义记录类型的变量
WHERE e.deptno=d.deptno;
BEGIN
FOR emp_record IN emp_cur LOOP
dbms_output.put_line('第'||emp_cur%ROWCOUNT||'个雇员,编号为'||
emp_record.empno||'姓名为'||emp_record.ename);
END LOOP;
END;

 隐式游标

隐式游标通常拿来判断DML的执行是否成功，也就是对表的增，删，改。

隐式游标与显示游标的区别：

1.不用声明游标

2.不用打开和关闭游标

3.SELECT语句必须使用INTO子句，结果只能是一条

隐式游标与显示游标的相同点：都有相同的属性，is%OPEN,%FOUND,%NOTFOUNT,%ROWCOUNT。

示例

DECLARE
  EMP_ROW EMP%ROWTYPE; --声明数据行
BEGIN
  SELECT EMPNO, ENAME, SAL
    INTO EMP_ROW.EMPNO, EMP_ROW.ENAME, EMP_ROW.SAL --使用INTO子句
    FROM EMP
   WHERE EMPNO = 7788;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '   ' || EMP_ROW.EMPNO || '  ' ||
                         EMP_ROW.ENAME || '   ' || EMP_ROW.SAL);
  END IF;
END;

示例

declare 
emp_row emp%ROWTYPE;
begin
      UPDATE emp SET ename='Lucy' WHERE empo=7756;
      IF  SQL%ROWCOUNT >0 THEN 
        dbms_output.put_line('更新成功');
       ELSE
        dbms_output.put_lind('更新失败');
       END IF;
end;

 

REF动态游标

动态游标往往结合存储过程使用，如查询需要动态改变，就使用它。

注意：强类型REF动态游标只能为查询结果与return类型相同的查询打开。

示例：

--强类型REF动态游标
DECLARE
  TYPE REF_EMP_TYPE IS REF CURSOR RETURN EMP%ROWTYPE; --声明ref游标
  REF_EMP REF_EMP_TYPE; --游标类型变量
  EMP_ROW EMP%ROWTYPE; --
BEGIN
  OPEN REF_EMP FOR  --打开游标
    SELECT * FROM EMP;
  LOOP
    FETCH REF_EMP  
      INTO EMP_ROW;--提取游标
    EXIT WHEN REF_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(REF_EMP%ROWCOUNT || ' ' || EMP_ROW.EMPNO || ' ' ||
                         EMP_ROW.ENAME);
  END LOOP;
  CLOSE REF_EMP;--关闭游标
END;

弱类型REF游标

注意：弱类型REF游标比较灵活适用于对用户动态输入动态查询表单。

--根据用户输入动态获取信息
DECLARE
TYPE ref_type IS REF CURSOR;--声明REF游标
ref_sec ref_type;--声明游标类型变量
v_id NUMBER;
v_name VARCHAR2(50);
flag VARCHAR2(1):=UPPER(SUBSTR('&tab',1,1));
BEGIN
  IF flag='E' THEN
    OPEN ref_sec FOR SELECT empno,ename  FROM emp;--打开emp游标
    dbms_output.put_line('======员工信息表======');
    ELSIF flag='D' THEN
    OPEN ref_sec FOR SELECT deptno,dname FROM dept;
    dbms_output.put_line('======部门信息表======');--打开dept游标
    ELSE 
    dbms_output.put_line('请输入员工信息表（E）或部门信息表（D）');
    RETURN;
    END IF;
    FETCH ref_sec INTO v_id,v_name; --提取游标
    WHILE ref_sec%FOUND LOOP--while 循环语句
    dbms_output.put_line('#'||v_id||':'||v_name);
    FETCH ref_sec INTO v_id,v_name;     --提取第二条记录
    END LOOP;
    CLOSE ref_sec;
END;

 

静态SQL

静态SQL是在编写PL/SQL程序时，SQL语句已经编写好了（DML语句和事务控制语句），但是DDL以及会话控制语句不能在PL/SQL中直接使用，但是可以通过动态SQL来解决这个问题。

动态SQL

不编译，执行时动态 确定

根据用户输入参数等才能确定的SQL语句

解决PL/SQL中不支持DDL语句的问题

语法：

EXECUTE IMMEDIATE '动态SQL语句字符串';

[INTO]子句用于接收SELECT语句选择的记录值。

[USING]子句用于绑定输入参数变量。

示例：

BEGIN
  EXECUTE IMMEDIATE 'create table text (id number,name varchar2(30))';
END;

DECLARE
  V_ID       NUMBER := &VID;
  V_NAME     VARCHAR(30) := '&vname';
  INSERT_SQL VARCHAR(200) := 'insert into text values(:1,:2)';--(:1,:2)为占位符代表值不能是关键字，这里：1指的是v_id
BEGIN
  EXECUTE IMMEDIATE INSERT_SQL
    USING V_ID, V_NAME;
END;

DECLARE
sql_stmt VARCHAR2(200);
emp_id NUMBER:=&emp_id;
emp_rec emp%ROWTYPE;
BEGIN
  sql_stmt:='select * from emp  where empno=:id';--这里的：id指的是动态输入的ID也就是emp_id
  EXECUTE  IMMEDIATE sql_stmt INTO emp_rec  USING emp_id;
  dbms_output.put_line(emp_rec.sal);
END;

 

带参数的游标

--带参数的游标
DECLARE
  CURSOR CUR_EMP(DNO NUMBER) IS
    SELECT * FROM EMP WHERE DEPTNO = DNO;
  EMP_ROW EMP%ROWTYPE;
BEGIN
  OPEN CUR_EMP(30);
  LOOP
    FETCH CUR_EMP
      INTO EMP_ROW; --3、提取游标
    EXIT WHEN CUR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || '   ' || EMP_ROW.EMPNO || '  ' ||
                         EMP_ROW.ENAME || '   ' || EMP_ROW.SAL);
  END LOOP;
  CLOSE CUR_EMP;
END;