--动态语句语法 /****************************************************************************************************************************************************** 动态语句语法:exec\sp_executesql语法整理人:中国风(Roy)日期:2008.06.06 ******************************************************************************************************************************************************/ --动态语句语法:--方法1查询表改为动态 select * from sysobjects exec('select ID,Name from sysobjects') exec sp_executesql N'select ID,Name from sysobjects'--多了一个N为unicode--方法2:字段名,表名,数据库名之类作为变量时,用动态SQL declare @FName varchar(20) set @FName='ID' exec('select '+@FName+' from sysobjects where '+@FName+'=5' ) declare @s varchar(1000) set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5' exec sp_executesql @s--会报错 declare @s nvarchar(1000)--改为nvarchar set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5' exec sp_executesql @s--成功 --方法3:输入参数declare @i int,@s nvarchar(1000) set @i=5 exec('select ID,Name from sysobjects where ID='+@i)set @s='select ID,Name from sysobjects where ID=@i' exec sp_executesql @s,N'@i int',@i--此处输入参数要加上N--方法4:输出参数declare @i int,@s nvarchar(1000) set @s='select @i=count(1) from sysobjects'--用exec exec('declare @i int '+@s+' select @i')--把整个语句用字符串加起来执行--用sp_executesql exec sp_executesql @s,N'@i int output',@i output--此处输出参数要加上N select @i --方法5:输入输出--用sp_executesql declare @i int,@con int,@s nvarchar(1000) set @i=5 select @s='select @con=count(1) from sysobjects where ID>@i' exec sp_executesql @s,N'@con int output,@i int',@con output ,@i select @con--用exec declare @i int,@s nvarchar(1000) set @i=5 select @s='declare @con int select @con=count(1) from sysobjects where ID>'+rtrim(@i)+' select @con' exec(@s)本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2009/04/25/4106390.aspx
动态sql语句基本语法 1 :普通SQL语句可以用Exec执行 eg: Select * from tableName Exec('select * from tableName') Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg: declare @fname varchar(20) set @fname = 'FiledName' Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。 Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可 declare @fname varchar(20) set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) set @s = 'select ' + @fname + ' from tableName' Exec(@s) -- 成功 exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) set @s = 'select ' + @fname + ' from tableName' Exec(@s) -- 成功 exec sp_executesql @s -- 此句正确 3. 输出参数 declare @num int, @sqls nvarchar(4000) set @sqls='select count(*) from tableName' exec(@sqls) --如何将exec执行结果放入变量中? declare @num int, @sqls nvarchar(4000) set @sqls='select @a=count(*) from tableName ' exec sp_executesql @sqls,N'@a int output',@num output select @num 几个是几个相关实例:--1. 使用 EXEC 实现的动态参数存储过程 CREATE PROC p_test @para1 varchar(10)=null, @para2 varchar(10)=null, @para3 varchar(10)=null, @para4 varchar(10)=null AS SET NOCOUNT ON DECLARE @sql varchar(8000) SET @sql='SELECT * FROM tbname WHERE 1=1' IF @para1 IS NOT NULL SET @sql=@sql+' AND col1='''+@para1+'''' IF @para2 IS NOT NULL SET @sql=@sql+' AND col2='''+@para2+'''' IF @para3 IS NOT NULL SET @sql=@sql+' AND col3='''+@para3+'''' IF @para4 IS NOT NULL SET @sql=@sql+' AND col4='''+@para4+'''' EXEC(@sql) GO /*======================================================*/--2. 使用 sp_executesql 实现的动态参数存储过程 CREATE PROC p_test @para1 varchar(10)=null, @para2 datetime=null, @para3 varchar(10)=null, @para4 int=null AS SET NOCOUNT ON DECLARE @sql nvarchar(4000) SET @sql='SELECT * FROM tbname WHERE 1=1' +CASE WHEN @para1 IS NULL THEN '' ELSE ' AND col1=@para1' END +CASE WHEN @para2 IS NULL THEN '' ELSE ' AND col2=@para2' END +CASE WHEN @para3 IS NULL THEN '' ELSE ' AND col3=@para3' END +CASE WHEN @para4 IS NULL THEN '' ELSE ' AND col4=@para4' END EXEC sp_executesql @sql,N' @para1 varchar(10)=null, @para2 datetime=null, @para3 varchar(10)=null, @para4 int=null ',@para1,@para2,@para3,@para4 GO /*======================================================*/--3. 不使用动态 Transact-SQL 语句实现的动态参数存储过程 CREATE PROC p_test @para1 varchar(10)=null, @para2 datetime=null, @para3 varchar(10)=null, @para4 int=null AS SET NOCOUNT ON SELECT * FROM tbname WHERE (@para1 IS NULL OR col1=@para1) AND (@para2 IS NULL OR col2=@para2) AND (@para3 IS NULL OR col3=@para3) AND (@para4 IS NULL OR col4=@para4)
这个不需要动态,用程序代码拼接sql语句就可以了string sql="select * from tb where 1=1 "; if(aa<>"") sql+=" and aa='"+aa+"'"
动态SQL简介: 1、静态SQL 静态SQL是指直接嵌入在PL/SQL块中的SQL语句。在编写PL/SQL时。静态SQL用于完成特定或固定的任务: 2、动态SQL 动态SQL是指在运行PL/SQL块时动态输入的SQL语句。如果在PL/SQL中需要执行DDL语句,DCL语句(GRANT,REVOKE),或者在PL/SQL中需要执行更加灵活的SQL语句(例如在SELECT语句中使用不同的WHERE条件),那么就必须使用动态SQL。 在PL/SQL块中编写动态语句时,需要将SQL语句存放在字符创变量中,而且SQL语句可以包含占位符(以冒号开始)。 如:CREATE TABLE temp(cola INT,colb VARCHAR2(10)) GRANT SELECT ON temp TO smith DELETE FROM emp WHERE sal>:a SELECT ename,sal FROM emp WHERE empno=:l 3、动态SQL的处理方法 (1)、使用EXECUTE IMMEDIATE 不能用于处理多行查询语句。 (2)、使用OPEN-FOR FETCH 和CLOSE语句 为了处理动态的多行查询操作,必须使用OPEN-FOR语句打开游标,使用FETCH 语句提取循环数据,最终使用CLOSE语句关闭游标. (3)、使用批量动态SQL处理非查询语句 为了动态的处理非查询语句(DML DDL DCL)或者单行查询语句,可以在PL/SQL快中使用EXEXUTE IMMEDIATE 语句, EXECUTE IMMEDIATE dynamic_string [INTO |define_variable|,define_variable...|record] [USING [IN|OUT|INOUT] bind_argument...] [{RETURNING|RETURN} INTO bind_argument...]; dynamic_string 用于指定存放SQL语句或者PL/SQL块的字符串变量; define_variable用于指定存放单行查询结婚的变量 输入bind_argument(IN)用于指定存放被传递给动态SQL值的变量 输出bind_argument(OUT)用于指定存放动态SQL返回值的变量 1、使用EXECUTE IMMEDIATE 处理DDL操作 EXECUTE IMMEDIATE后面只需要带有DDL语句文本即可,而不需要INTO和USING 字句。 CREATE OR REPLACE PROCEDURE drop_table(table_name VARCHAR2) IS sql_statement VARCHAR2(100); BEGIN sql_statement:='DROP TABLE '||table_name; EXECUTE IMMEDIATE sql_statement; END; 2、使用EXECUTE IMMEDIATE 处理DCL操作 和处理DDL语句差不多 3、使用EXECUTE IMMEDIATE 处理DML 语句 如果DML语句既没有占位符,也咩有RETURNING 字句,那么在EXECUTE IMMEDIATE 语句之后不需要带有USING和RETURNING INTO子句; 如果DML语句含有占位符,那么在 EXECUTE IMMEDIATE 语句之后要带有USING子句; 如果DML语句之后带有RETURNING子句,那么在EXECUTE IMMEDIATE 语句之后带有RETURNING INTO子句。 (1)、处理无占位符和RETURNING子句的DML语句 DECLARE sql_statement VARCHAR2(100); BEGIN sql_statement:='UPDATE emp SET sal=sal*1.1 WHERE depno=30'; EXECUTE IMMEDIATE sql_statement; END; (2)、处理包含占位符的DML语句 要使用USING 自己为占位符提供输入数据。 DECLARE sql_state VARCHAR2(100); BEGIN sql_state:='UPDATE emp SET sal=sal*(1+:percent/100) WHRE deptno=:dno'; EXECUTE IMMEDIATE sql_state USING &1,&2; END; (3)、处理包含RETURNING 子句的DML语句 必要要使用RETURNING INTO 子句接受返回数据。 注意:直接使用EXECUTE IMMEDIATE 语句处理带有RETURNING 子句的DML语句时,只能处理作用在单行的DML语句。 如果DML语句作用在多行伤,则必须要使用BULK子句。 DECLARE salary NUMBER(6,2); sql_state VARCHAR2(100); BEGIN sql_state:='UPDATE emp SET sal=(sal*:present/100) WHERE empno=:eno RETURNING sal INTO :salary'; EXECUTE IMMEDIATE sql_state USING &1,&2 RETURNING INTO salary; dbms_output.put_line('新工资:'||salary); END; (4)、使用EXECUTE IMMEDIATE 处理单行查询 要使用INTO 子句接受返回数据 DECLARE sql_state VARCHAR2(100); emp_record emp%ROWTYPE; BEGIN sql_state:='SELECT * FROM emp WHERE empno=:eno'; EXECUTE IMMEDIATE sql_state INTO emp_record USING &1; dbms_output.put_line('雇员:'||emp_record.ename||'的工资为:'||emp_record.sal); END; 处理多行查询语句: 使用EXECUTE IMMEDIATE只能处理单行查询语句,为了动态的处理SELECT语句所返回的多行数据,需要使用OPEN-FOR,FETCH,CLOSE语句。 步骤如下 定义游标变量-->打开游标变量-->循环提取数据-->关闭游标变量。 1、定义游标变量: TYPE cursortype IS REF CURSOR; cursor_variable cursortype; 2、打来游标变量 OPEN cursor_variable FOR dynamic_string [USING bind_argument,...]; bind_argument 用于存放传递给动态SELECT语句值的变量。 3、循环提取数据 FETCH cursor_variable INTO {var1[,var2]...|record_var}; 4、关闭游标变量 close cursor_variable; 5、多行查询实例: DECLARE TYPE cursor_type IS REF CURSOR; sql_state VARCHAR2(100); emp_record emp%ROWTYPE; v_cursor vursor_type; BEGIN sql_state:='SELECT * FROM emp WHERE deptno=:dno'; OPEN v_cursor FOR sql_state USING &deptno; LOOP FETCH v_cursor INTO emp_record; EXIT WHEN v_cursor%NOTFOUND; dbms_output.put_line('雇员名:'||emp_record.ename||'的工资是:'||emp_record.salary); END LOOP; CLOSE v_cursor; END; 在动态SQL中使用BULK子句 ORACLE9i新增加的特性。可以加快批量数据的处理速度。 有三种语句支持BULK子句:EXECUTE IMMEDIATE,FETCH和FORALL 1、在EXECUTE IMMEDIATE语句中使用动态BULK子句 EXECUTE IMMEDIATE dynamic_string [BULK COLLECT INTO |define_variable|,define_variable...|record] [USING [IN|OUT|INOUT] bind_argument...] [{RETURNING|RETURN} BULK COLLECT INTO bind_argument...]; (1)、使用BULK子句处理DML语句返回子句 DECLARE TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; TYPE sal_table_type IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER; ename_table ename_table_type; sal_table sal_table_type; sql_state VARCHAR2(150); BEGIN sql_state:='UPDATE emp SET sal=sal*(1+:precent/100) WHERE deptno=:dno' ||'RETURNING ename,sal INTO :name,:salary'; EXECUTE IMMEDIATE sql_state USING &precent,&depno RETURNING BULK COLLECT INTO ename_table,sal_table ; FOR i IN 1..ename_table.COUNT LOOP dbms_output.put_line('雇员名:'||ename_table(i)||'的工资是:'||sal_table(i)); END LOOP; END; (2)、使用BULK子句处理多行查询 DECLARE sql_state VARCHAR2(100); TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; ename_table ename_table_type; BEGIN sql_state:='SELECT ename FROM emp WHERE empno=:eno'; EXECUTE IMMEDIATE sql_state BULK COLLECT INTO emp_record USING &1; FOR i IN 1..ename_table.COUNT LOOP dbms_output.put_line('雇员:'||ename_table(i)); END LOOP; END; 2、在FETCH 语句中使用BULK子句 OPEN-FOR ,FETCH,CLOSE .....BULK DECLARE TYPE cursor_type IS REF CURSOR; TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; sql_state VARCHAR2(200); v_vursor cursor_type; ename_table ename_table_type; BEGIN sql_state:='SELECT ename FROM emp WHERE deptno=:dno'; OPEN v_corsor FOR sql_state USING &deptno; FETCH v_cursor BULK COLLECT INTO ename_table; FOR i IN i..ename_table.COUNT LOOP dbms_output.put_line('雇员:'||ename_table(i)); END LOOP; CLOSE v_cursor; END;
DECLARE @WHERE varchar(1000) DECLARE @SQL varchar(1000) SET @SQL='SELECT * from sysobjects where '+@WHEREEXEC sp_executesql @SQL
/******************************************************************************************************************************************************
动态语句语法:exec\sp_executesql语法整理人:中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/
--动态语句语法:--方法1查询表改为动态
select * from sysobjects
exec('select ID,Name from sysobjects')
exec sp_executesql N'select ID,Name from sysobjects'--多了一个N为unicode--方法2:字段名,表名,数据库名之类作为变量时,用动态SQL
declare @FName varchar(20)
set @FName='ID'
exec('select '+@FName+' from sysobjects where '+@FName+'=5' )
declare @s varchar(1000)
set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5'
exec sp_executesql @s--会报错
declare @s nvarchar(1000)--改为nvarchar
set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5'
exec sp_executesql @s--成功
--方法3:输入参数declare @i int,@s nvarchar(1000)
set @i=5
exec('select ID,Name from sysobjects where ID='+@i)set @s='select ID,Name from sysobjects where ID=@i'
exec sp_executesql @s,N'@i int',@i--此处输入参数要加上N--方法4:输出参数declare @i int,@s nvarchar(1000)
set @s='select @i=count(1) from sysobjects'--用exec
exec('declare @i int '+@s+' select @i')--把整个语句用字符串加起来执行--用sp_executesql
exec sp_executesql @s,N'@i int output',@i output--此处输出参数要加上N
select @i
--方法5:输入输出--用sp_executesql
declare @i int,@con int,@s nvarchar(1000)
set @i=5
select @s='select @con=count(1) from sysobjects where ID>@i'
exec sp_executesql @s,N'@con int output,@i int',@con output ,@i
select @con--用exec
declare @i int,@s nvarchar(1000)
set @i=5
select @s='declare @con int select @con=count(1) from sysobjects where ID>'+rtrim(@i)+' select @con'
exec(@s)本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2009/04/25/4106390.aspx
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num 几个是几个相关实例:--1. 使用 EXEC 实现的动态参数存储过程
CREATE PROC p_test
@para1 varchar(10)=null,
@para2 varchar(10)=null,
@para3 varchar(10)=null,
@para4 varchar(10)=null
AS
SET NOCOUNT ON
DECLARE @sql varchar(8000)
SET @sql='SELECT * FROM tbname WHERE 1=1'
IF @para1 IS NOT NULL
SET @sql=@sql+' AND col1='''+@para1+''''
IF @para2 IS NOT NULL
SET @sql=@sql+' AND col2='''+@para2+''''
IF @para3 IS NOT NULL
SET @sql=@sql+' AND col3='''+@para3+''''
IF @para4 IS NOT NULL
SET @sql=@sql+' AND col4='''+@para4+''''
EXEC(@sql)
GO
/*======================================================*/--2. 使用 sp_executesql 实现的动态参数存储过程
CREATE PROC p_test
@para1 varchar(10)=null,
@para2 datetime=null,
@para3 varchar(10)=null,
@para4 int=null
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SET @sql='SELECT * FROM tbname WHERE 1=1'
+CASE WHEN @para1 IS NULL THEN '' ELSE ' AND col1=@para1' END
+CASE WHEN @para2 IS NULL THEN '' ELSE ' AND col2=@para2' END
+CASE WHEN @para3 IS NULL THEN '' ELSE ' AND col3=@para3' END
+CASE WHEN @para4 IS NULL THEN '' ELSE ' AND col4=@para4' END
EXEC sp_executesql @sql,N'
@para1 varchar(10)=null,
@para2 datetime=null,
@para3 varchar(10)=null,
@para4 int=null
',@para1,@para2,@para3,@para4
GO
/*======================================================*/--3. 不使用动态 Transact-SQL 语句实现的动态参数存储过程
CREATE PROC p_test
@para1 varchar(10)=null,
@para2 datetime=null,
@para3 varchar(10)=null,
@para4 int=null
AS
SET NOCOUNT ON
SELECT * FROM tbname
WHERE (@para1 IS NULL OR col1=@para1)
AND (@para2 IS NULL OR col2=@para2)
AND (@para3 IS NULL OR col3=@para3)
AND (@para4 IS NULL OR col4=@para4)
if(aa<>"")
sql+=" and aa='"+aa+"'"
动态SQL简介:
1、静态SQL
静态SQL是指直接嵌入在PL/SQL块中的SQL语句。在编写PL/SQL时。静态SQL用于完成特定或固定的任务:
2、动态SQL
动态SQL是指在运行PL/SQL块时动态输入的SQL语句。如果在PL/SQL中需要执行DDL语句,DCL语句(GRANT,REVOKE),或者在PL/SQL中需要执行更加灵活的SQL语句(例如在SELECT语句中使用不同的WHERE条件),那么就必须使用动态SQL。
在PL/SQL块中编写动态语句时,需要将SQL语句存放在字符创变量中,而且SQL语句可以包含占位符(以冒号开始)。
如:CREATE TABLE temp(cola INT,colb VARCHAR2(10))
GRANT SELECT ON temp TO smith
DELETE FROM emp WHERE sal>:a
SELECT ename,sal FROM emp WHERE empno=:l
3、动态SQL的处理方法
(1)、使用EXECUTE IMMEDIATE
不能用于处理多行查询语句。
(2)、使用OPEN-FOR FETCH 和CLOSE语句
为了处理动态的多行查询操作,必须使用OPEN-FOR语句打开游标,使用FETCH 语句提取循环数据,最终使用CLOSE语句关闭游标.
(3)、使用批量动态SQL处理非查询语句
为了动态的处理非查询语句(DML DDL DCL)或者单行查询语句,可以在PL/SQL快中使用EXEXUTE IMMEDIATE 语句,
EXECUTE IMMEDIATE dynamic_string
[INTO |define_variable|,define_variable...|record]
[USING [IN|OUT|INOUT] bind_argument...]
[{RETURNING|RETURN} INTO bind_argument...];
dynamic_string 用于指定存放SQL语句或者PL/SQL块的字符串变量;
define_variable用于指定存放单行查询结婚的变量
输入bind_argument(IN)用于指定存放被传递给动态SQL值的变量
输出bind_argument(OUT)用于指定存放动态SQL返回值的变量
1、使用EXECUTE IMMEDIATE 处理DDL操作
EXECUTE IMMEDIATE后面只需要带有DDL语句文本即可,而不需要INTO和USING
字句。
CREATE OR REPLACE PROCEDURE drop_table(table_name VARCHAR2)
IS
sql_statement VARCHAR2(100);
BEGIN
sql_statement:='DROP TABLE '||table_name;
EXECUTE IMMEDIATE sql_statement;
END;
2、使用EXECUTE IMMEDIATE 处理DCL操作
和处理DDL语句差不多
3、使用EXECUTE IMMEDIATE 处理DML 语句
如果DML语句既没有占位符,也咩有RETURNING 字句,那么在EXECUTE IMMEDIATE 语句之后不需要带有USING和RETURNING INTO子句;
如果DML语句含有占位符,那么在 EXECUTE IMMEDIATE 语句之后要带有USING子句;
如果DML语句之后带有RETURNING子句,那么在EXECUTE IMMEDIATE 语句之后带有RETURNING INTO子句。
(1)、处理无占位符和RETURNING子句的DML语句
DECLARE
sql_statement VARCHAR2(100);
BEGIN
sql_statement:='UPDATE emp SET sal=sal*1.1 WHERE depno=30';
EXECUTE IMMEDIATE sql_statement;
END;
(2)、处理包含占位符的DML语句
要使用USING 自己为占位符提供输入数据。
DECLARE
sql_state VARCHAR2(100);
BEGIN
sql_state:='UPDATE emp SET sal=sal*(1+:percent/100) WHRE deptno=:dno';
EXECUTE IMMEDIATE sql_state USING &1,&2;
END;
(3)、处理包含RETURNING 子句的DML语句
必要要使用RETURNING INTO 子句接受返回数据。
注意:直接使用EXECUTE IMMEDIATE 语句处理带有RETURNING 子句的DML语句时,只能处理作用在单行的DML语句。
如果DML语句作用在多行伤,则必须要使用BULK子句。
DECLARE
salary NUMBER(6,2);
sql_state VARCHAR2(100);
BEGIN
sql_state:='UPDATE emp SET sal=(sal*:present/100) WHERE empno=:eno RETURNING sal INTO :salary';
EXECUTE IMMEDIATE sql_state USING &1,&2 RETURNING INTO salary;
dbms_output.put_line('新工资:'||salary);
END;
(4)、使用EXECUTE IMMEDIATE 处理单行查询
要使用INTO 子句接受返回数据
DECLARE
sql_state VARCHAR2(100);
emp_record emp%ROWTYPE;
BEGIN
sql_state:='SELECT * FROM emp WHERE empno=:eno';
EXECUTE IMMEDIATE sql_state INTO emp_record USING &1;
dbms_output.put_line('雇员:'||emp_record.ename||'的工资为:'||emp_record.sal);
END;
处理多行查询语句:
使用EXECUTE IMMEDIATE只能处理单行查询语句,为了动态的处理SELECT语句所返回的多行数据,需要使用OPEN-FOR,FETCH,CLOSE语句。
步骤如下
定义游标变量-->打开游标变量-->循环提取数据-->关闭游标变量。
1、定义游标变量:
TYPE cursortype IS REF CURSOR;
cursor_variable cursortype;
2、打来游标变量
OPEN cursor_variable FOR dynamic_string [USING bind_argument,...];
bind_argument 用于存放传递给动态SELECT语句值的变量。
3、循环提取数据
FETCH cursor_variable INTO {var1[,var2]...|record_var};
4、关闭游标变量
close cursor_variable;
5、多行查询实例:
DECLARE
TYPE cursor_type IS REF CURSOR;
sql_state VARCHAR2(100);
emp_record emp%ROWTYPE;
v_cursor vursor_type;
BEGIN
sql_state:='SELECT * FROM emp WHERE deptno=:dno';
OPEN v_cursor FOR sql_state USING &deptno;
LOOP
FETCH v_cursor INTO emp_record;
EXIT WHEN v_cursor%NOTFOUND;
dbms_output.put_line('雇员名:'||emp_record.ename||'的工资是:'||emp_record.salary);
END LOOP;
CLOSE v_cursor;
END;
在动态SQL中使用BULK子句
ORACLE9i新增加的特性。可以加快批量数据的处理速度。
有三种语句支持BULK子句:EXECUTE IMMEDIATE,FETCH和FORALL
1、在EXECUTE IMMEDIATE语句中使用动态BULK子句
EXECUTE IMMEDIATE dynamic_string
[BULK COLLECT INTO |define_variable|,define_variable...|record]
[USING [IN|OUT|INOUT] bind_argument...]
[{RETURNING|RETURN} BULK COLLECT INTO bind_argument...];
(1)、使用BULK子句处理DML语句返回子句
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE sal_table_type IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
sal_table sal_table_type;
sql_state VARCHAR2(150);
BEGIN
sql_state:='UPDATE emp SET sal=sal*(1+:precent/100) WHERE deptno=:dno'
||'RETURNING ename,sal INTO :name,:salary';
EXECUTE IMMEDIATE sql_state USING &precent,&depno RETURNING BULK COLLECT INTO
ename_table,sal_table ;
FOR i IN 1..ename_table.COUNT LOOP
dbms_output.put_line('雇员名:'||ename_table(i)||'的工资是:'||sal_table(i));
END LOOP;
END;
(2)、使用BULK子句处理多行查询
DECLARE
sql_state VARCHAR2(100);
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
BEGIN
sql_state:='SELECT ename FROM emp WHERE empno=:eno';
EXECUTE IMMEDIATE sql_state BULK COLLECT INTO emp_record USING &1;
FOR i IN 1..ename_table.COUNT LOOP
dbms_output.put_line('雇员:'||ename_table(i));
END LOOP;
END;
2、在FETCH 语句中使用BULK子句
OPEN-FOR ,FETCH,CLOSE .....BULK
DECLARE
TYPE cursor_type IS REF CURSOR;
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
sql_state VARCHAR2(200);
v_vursor cursor_type;
ename_table ename_table_type;
BEGIN
sql_state:='SELECT ename FROM emp WHERE deptno=:dno';
OPEN v_corsor FOR sql_state USING &deptno;
FETCH v_cursor BULK COLLECT INTO ename_table;
FOR i IN i..ename_table.COUNT LOOP
dbms_output.put_line('雇员:'||ename_table(i));
END LOOP;
CLOSE v_cursor;
END;
DECLARE @SQL varchar(1000)
SET @SQL='SELECT * from sysobjects where '+@WHEREEXEC sp_executesql @SQL