ORACLE
来源:互联网 发布:mac图片浏览软件 编辑:程序博客网 时间:2024/05/01 09:36
--create test table
create table AAA_TEST
(aa varchar2(100),bb varchar2(100),cc varchar2(50));
--简单的动态执行
begin
execute immediate 'insert into table(field1) values(value)';
end;
--代参数和返回的动态执行
declare
v_str varchar2(100);
v_sql varchar2(500);
v_out varchar2(100);
begin v_str := '111';
v_sql := 'update aaa_test set aa=''ttt'' where aa=:1 returning aa into :2';
execute immediate v_sql using v_str returning into v_out;
end;
--使用open-for,fetch和close声明
declare
type cursorType is ref cursor;
mycursor cursorType;
v_sql varchar2(100);
v_aa varchar2(50);
v_bb varchar2(100);
begin v_sql := 'select bb from aaa_test where aa like :1';
v_aa := '11%';
open mycursor for v_sql using v_aa;
loop fetch mycursor into v_bb;
exit when mycursor%notfound;
end loop;
close mycursor;
end;
--Using Bulking Dynamic Sql(提高性能,减少上下文等待时间)
declare
type cursorType is ref cursor;
mycursor cursorType;
type numList is table of varchar2(100);
bbs numList;
v_sql varchar2(500);
begin v_sql := 'select bb from aaa_test';
open mycursor for v_sql;
fetch mycursor bulk collect into bbs;
close mycursor;
execute immediate v_sql
bulk collect into bbs;
end;
--第二种参数方式的动态Sql性能更优
execute immediate 'delete from table1 where empno =' || v_str;
execute immediate 'delete from table1 where empno = :1'
--但参数绑定方式对表,视图等对象不适用,因此下面报错
execute immediate 'drop table :tab' using v_tablename
- Oracle???
- oracle
- oracle
- oracle
- oracle
- oracle...
- oracle
- oracle
- ORACLE
- Oracle
- ORACLE
- Oracle
- Oracle
- oracle
- oracle
- oracle
- ORACLE
- oracle
- 常用JAVA类的使用
- 庐山一日游
- 数据格式化
- JavaScript测试工具Test.AnotherWay介绍
- 杀毒软件资源专区
- ORACLE
- ASP.NET会话(Session)保存模式
- 加入进度条及进度框
- TOP 1比不加TOP慢的疑惑
- 程序员,不要让自己做兔子
- 数据字典之定义
- Javascript使用9例
- JAVA WEB 程序乱码问题解决
- C语言嵌入式系统编程修炼之道——内存操作篇