oracle中游标与动态sql,学习笔记_oracle——游标与动态SQL

游标

查询结果及记录不足，很难用变量表示

游标可以按行访问结果集

基本原理

DML的结果放在缓冲区

游标是指向这个缓冲区的指针

分类

静态游标

定义是在编译时绑定了一个select语句

隐式游标 oracle自动生成的游标

显示游标 用户手动申明和操作

动态游标

在运行时游标绑定查询语句

强类型 只支持与之类型匹配的查询语句

弱类型 只支持任何查询语句

显式游标

使用步骤

1、声明游标

在声明(DECLAR)中

CURSOR 游标名 IS ;

2、打开游标

OPEN ;

3、提取游标

FETCH INTO

--变量类型、顺序、数量相同，每次调用移动一次

4、关闭游标

CLOSE

游标的属性(用游标名%属性名调用)

%ISOPEN  判断是否打开

%FOUND  是否有指向效行

%NOTFOUND 是否有指向效行

%ROWCONUT 游标提取过的行数

使用循环游标简化游标的读取

FOR IN LOOP

--数据库操作

END LOOP

其中是用户的自定义类型，

系统自动生成，与查询结果中一致

隐式游标

在PL/SQL中使用SELECT操作即是隐式游标

特点 1、不用声明

2、不用打开和关闭

3、必须使用INTO字句，结果只能一条

属性的使用，在数属性前加上SQL

其中 %ROWCONUT、%FOUND常用来判断是否为空

在commit之前使用

例如 update .....

IF SQL%ROWCOUNT = 1 THEN

--表已更新

ELSE

--表未更新

END IF;

动态游标

REF动态游标，用于处理多行的查询结果集，在

打开游标是才确定类型

特点： 1、动态游标可以与不同的语句关联

2、打开游标是绑定、是REF类型

3、常用在存储过程中

使用过程

定义游标

TYPE IS REF COUSER

RETURN

声明游标

打开动态游标

OPEN FOR

//=================实例=================

DECLARE

TYPE refcur IS REF COUSOR

RETURN employees%ROWTYPE

v_emp employees%ROWTYPE

BEGIN

OPEN refcur FOR

--SQL语句

LOOP

FETCH refcur INTO v_emp;

EXIT WHEN refcur%NOTFOUND;

--执行语句

END LOOP

CLOSE refcur

END

强类型和弱类型的区别

强类型：带有RETURN语句

在OPEN时必须是指定的类型的select语句

弱类型：不带有RETURN语句

在OPEN时可以是任意的select语句

动态SQL

静态SQL

1、编译时确定，一般只能使用DML和事务控制语句

2、DDL，回话控制语句不能直接使用

动态SQL

1、不编译，执行时动态确定

2、根据用户输入参数等确定SQL

3、解决PL/SQL中不支持DDL的问题

语法

EXECUTE IMMEDIATE '语句'

EXECUTE IMMEDIATE '语句'

[INTO ] --用于接收SQL的查询结果

[USING ] --用于动态接收参数

//============实例================

EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'

DECLARE

sql_stmt VARCHAR2(200)

emp_id NUMBER(10) := XX;

emp_rec enployees%ROWTYPE;

BEGIN

sql_stmt := 'select * from employees where id = :id'

EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

END;

[注]只能执行返回结果为一行或者没有的语句

当返回多行时需要使用REF游标

OPEN FOR

[USING ]

SQL行列转换

静态语句

SELECT t_name 姓名，

sum(CASE t_sourse WHEN '语文' THEN t_score ELSE 0 END) 语文,

sum(CASE t_sourse WHEN '数学' THEN t_score ELSE 0 END) 数学,

sum(CASE t_sourse WHEN '物理' THEN t_score ELSE 0 END) 物理,

FORM td

GROUP BY t_name;

动态语句

DECLARE

t_name VARCHAR(10);

t_coures1 NUMBER;

t_coures2 NUMBER;

t_coures3 NUMBER;

TYPR c_type IS REF CURSOR;

cur c_type;

BEGIN

OPEN cur FOR

'sum (case t_course when ''' || ' 语文' || ''' then t_score else 0 end ) ,

sum (case t_course when ''' || ' 数学' || ''' then t_score else 0 end ) ,

sum (case t_course when ''' || ' 物理' || ''' then t_score else 0 end )

FROM td GROUP BY t_name'

LOOP

FETCH cur INTO t_name,t_course1,t_course2,t_course3

EXIT WHEN cur%NOTFOUND;

END LOOP;

CLOSE cur;

END;