本文的目的是介绍sql–为什么使用EXECUTEIMMEDIATE运行此查询会导致它失败？的详细情况，我们将通过专业的研究、有关数据的分析等多种方式，同时也不会遗漏关于com.facebook.com
本文的目的是介绍sql – 为什么使用EXECUTE IMMEDIATE运行此查询会导致它失败？的详细情况，我们将通过专业的研究、有关数据的分析等多种方式，同时也不会遗漏关于com.facebook.common.executors.UiThreadImmediateExecutorService的实例源码、execute executeQuery executeUpdate 三种的分别封装使用、execute immediate、execute immediate 的用法的知识。
本文目录一览：- sql – 为什么使用EXECUTE IMMEDIATE运行此查询会导致它失败？
- com.facebook.common.executors.UiThreadImmediateExecutorService的实例源码
- execute executeQuery executeUpdate 三种的分别封装使用
- execute immediate
- execute immediate 的用法
sql – 为什么使用EXECUTE IMMEDIATE运行此查询会导致它失败？
CREATE OR REPLACE PROCEDURE sqlout(query IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ');';
END;
它编译正确,但即使是非常简单的查询,例如：
BEGIN
sqlout('SELECT * FROM DUAL');
END;
IT抛出ORA-00911：无效字符.如果我手动运行创建的查询,它将正确运行.此时我能够确定导致问题的原因.
解决方法
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ')';
com.facebook.common.executors.UiThreadImmediateExecutorService的实例源码
void attach(BitmapUpdateListener listener) {
mBitmapUpdateListener = listener;
mAttachCounter++;
if (mAttachCounter != 1) {
// this is a secondary attach,ignore it,only updating Bitmap boundaries if needed.
Bitmap bitmap = getBitmap();
if (bitmap != null) {
listener.onSecondaryAttach(bitmap);
}
return;
}
listener.onImageLoadEvent(ImageLoadEvent.ON_LOAD_START);
Assertions.assertCondition(mDataSource == null);
Assertions.assertCondition(mImageRef == null);
// Submit the request
ImagePipeline imagePipeline = ImagePipelineFactory.getInstance().getimagePipeline();
mDataSource = imagePipeline.fetchDecodedImage(mImageRequest,RCtimageView.getCallerContext());
mDataSource.subscribe(this,UiThreadImmediateExecutorService.getInstance());
}
public PipelineDraweeControllerBuildersupplier(
Context context,ImagePipelineFactory imagePipelineFactory,Set<ControllerListener> boundControllerListeners,@Nullable DraweeConfig draweeConfig) {
mContext = context;
mImagePipeline = imagePipelineFactory.getimagePipeline();
if (draweeConfig != null && draweeConfig.getPipelineDraweeControllerFactory() != null) {
mPipelineDraweeControllerFactory = draweeConfig.getPipelineDraweeControllerFactory();
} else {
mPipelineDraweeControllerFactory = new PipelineDraweeControllerFactory();
}
mPipelineDraweeControllerFactory.init(
context.getResources(),DeferredReleaser.getInstance(),imagePipelineFactory.getAnimatedDrawableFactory(context),UiThreadImmediateExecutorService.getInstance(),mImagePipeline.getBitmapMemoryCache(),draweeConfig != null
? draweeConfig.getCustomDrawableFactories()
: null,draweeConfig != null
? draweeConfig.getDebugOverlayEnabledsupplier()
: null);
mBoundControllerListeners = boundControllerListeners;
}
public VolleyDraweeControllerBuildersupplier(
Context context,ImageLoader imageLoader,Set<ControllerListener> boundControllerListeners) {
mContext = context;
mImageLoader = imageLoader;
mVolleyDraweeControllerFactory = new VolleyDraweeControllerFactory(
context.getResources(),UiThreadImmediateExecutorService.getInstance());
mBoundControllerListeners = boundControllerListeners;
}
private ExperimentalBitmapAnimationDrawableFactory createDrawableFactory() {
supplier<Integer> cachingStrategysupplier = new supplier<Integer>() {
@Override
public Integer get() {
return ExperimentalBitmapAnimationDrawableFactory.CACHING_STRATEGY_FRESCO_CACHE_NO_REUSING;
}
};
final SerialExecutorService serialExecutorServiceForFramePreparing =
new DefaultSerialExecutorService(mExecutorsupplier.forDecode());
supplier<Integer> numberOfFramesToPreparesupplier = new supplier<Integer>() {
@Override
public Integer get() {
return NUMBER_OF_FRAMES_TO_PREPARE;
}
};
return new ExperimentalBitmapAnimationDrawableFactory(
getAnimatedDrawableBackendProvider(),serialExecutorServiceForFramePreparing,RealtimeSinceBootClock.get(),mPlatformBitmapFactory,mbackingCache,cachingStrategysupplier,numberOfFramesToPreparesupplier);
}
/**
* Wraps the given animation backend with an activity check.
* When no frame has been drawn for more than 2 seconds,an inactivity toast message will
* be displayed.
*
* @param context the context to be used for displaying the toast message
* @param animationBackend the backend to wrap with the inactivity check
* @return the wrapped backend to use
*/
public static AnimationBackend wrapAnimationBackendWithInactivityCheck(
final Context context,final AnimationBackend animationBackend) {
AnimationBackendDelegateWithInactivityCheck.InactivityListener inactivityListener =
new AnimationBackendDelegateWithInactivityCheck.InactivityListener() {
@Override
public void onInactive() {
// Forward the inactive callback to the backend if needed
if (animationBackend instanceof
AnimationBackendDelegateWithInactivityCheck.InactivityListener) {
((AnimationBackendDelegateWithInactivityCheck.InactivityListener) animationBackend)
.onInactive();
}
Toast.makeText(
context,"Animation backend inactive.",Toast.LENGTH_SHORT)
.show();
}
};
return createForBackend(
animationBackend,inactivityListener,UiThreadImmediateExecutorService.getInstance());
}
public PipelineDraweeControllerBuildersupplier(
Context context,draweeConfig != null
? draweeConfig.getDebugOverlayEnabledsupplier()
: null);
mBoundControllerListeners = boundControllerListeners;
}
public VolleyDraweeControllerBuildersupplier(
Context context,UiThreadImmediateExecutorService.getInstance());
mBoundControllerListeners = boundControllerListeners;
}
private ExperimentalBitmapAnimationDrawableFactory createDrawableFactory() {
supplier<Integer> cachingStrategysupplier = new supplier<Integer>() {
@Override
public Integer get() {
return ExperimentalBitmapAnimationDrawableFactory.CACHING_STRATEGY_FRESCO_CACHE_NO_REUSING;
}
};
final SerialExecutorService serialExecutorServiceForFramePreparing =
new DefaultSerialExecutorService(mExecutorsupplier.forDecode());
supplier<Integer> numberOfFramesToPreparesupplier = new supplier<Integer>() {
@Override
public Integer get() {
return NUMBER_OF_FRAMES_TO_PREPARE;
}
};
return new ExperimentalBitmapAnimationDrawableFactory(
getAnimatedDrawableBackendProvider(),numberOfFramesToPreparesupplier);
}
/** * Wraps the given animation backend with an activity check. * When no frame has been drawn for more than 2 seconds,UiThreadImmediateExecutorService.getInstance()); }
public void get() {
ImageRequest request = ImageRequestBuilder.newBuilderWithSource(Uri.parse(imageUrl))
.build();
ImagePipeline imagePipeline = Fresco.getimagePipeline();
DataSource<CloseableReference<CloseableImage>> dataSource
= imagePipeline.fetchDecodedImage(request,WikipediaApp.getInstance());
dataSource.subscribe(new BitmapDataSubscriber(),UiThreadImmediateExecutorService.getInstance());
}
execute executeQuery executeUpdate 三种的分别封装使用
package com.zhao.practice727_1;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* 测试类
* @author Administrator
*
*/
public class TestDBPreStat {
/**
* @param args
*/
public static void main(String[] args) {
new TestDBPreStat().testExecute();
}
/**
* 测试通过execute来查询数据
*/
public void testExecute(){
DBPreparedStatement db = new DBPreparedStatement();
String sql = "select name ,score from test2 where id = ?";
Object[] arrays = {10};
Object object = db.getDataByExecute(sql, arrays);
if(object instanceof List){
List list = (List)object;
for (Object obj : list) {
Map map = (Map)obj;
Iterator it = map.keySet().iterator();
while(it.hasNext()){
String key = (String) it.next();
String value = (String) map.get(key);
System.out.println(key +" "+value);
}
}
}
db.close();
}
// /**
// * 测试通过executeQuery或executeUpdate来查询数据
// */
// public void method1(){
// DBPreparedStatement db = new DBPreparedStatement();
// String sql = "select name ,score from test2 where id = ?";
// Object[] arrays = {10};
// List list = db.getDataBySQL(sql, arrays);
//
//
// for (Object object : list) {
// Map map = (Map)object;
// Iterator it = map.keySet().iterator();
// while(it.hasNext()){
// String key = (String) it.next();
// String value = (String) map.get(key);
// System.out.println(key +" "+value);
// }
//
// }
// db.close();
// }
}package com.zhao.practice727_1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* PreparedStatement工具类
* @author Administrator
*
*/
public class DBPreparedStatement {
/**
* 驱动字符串
*/
private static final String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // 加载JDBC驱动
/**
* 连接数据库的URL
*/
private static final String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=Zhao722"; // 连接服务器和数据库Zhao722
/**
* 用户名字符串
*/
private static final String USER= "sa";
/**
* 密码字符串
*/
private static final String PASSWORD = "zhao";
/**
* 数据库连接
*/
Connection conn = null;
/**
* PreparedStatement
*/
PreparedStatement pstat = null;
/**
* 结果集ResultSet
*/
ResultSet rs = null;
static {
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接
* @return
*/
private Connection getConnection(){
try {
conn = DriverManager.getConnection(dbURL, USER, PASSWORD);
// System.out.println(conn);
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 获得PreparedStatement对象
* @param sql sql语句
* @return PreparedStatement
*/
private PreparedStatement getPreparedStatement(String sql){
try {
pstat = getConnection().prepareStatement(sql);
System.out.println(sql);
return pstat;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 获得结果集
* @param arrays 传入的参数数组
* @param sql sql语句
* @return ResultSet
*/
private ResultSet getResultSet(String sql , Object[] arrays){
pstat = getPreparedStatement(sql);
try {
//设置参数
for (int i = 0; i < arrays.length; i++) {
pstat.setObject(i+1, arrays[i]);
}
//开始查询
rs = pstat.executeQuery();
return rs;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 增删改查的操作
* @param sql sql语句
* @param arrays 传入的参数数组
* @return int
*/
private int getDataByUpdate(String sql , Object[] arrays){
pstat = getPreparedStatement(sql);
try {
//设置参数
for (int i = 0; i < arrays.length; i++) {
pstat.setObject(i+1, arrays[i]);
}
//开始增删改查操作
int i = pstat.executeUpdate();
return i;
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
/**
* 对结果集进行处理
* @param sql sql语句
* @param arrays 传入的参数数组
* @return List
*/
private List getDataBySQL(String sql , Object[] arrays){
List list = new ArrayList();
try{
rs = getResultSet(sql, arrays);
//对结果集进行处理
while(rs.next()){
//定义Map来保存每行对应的每列的值
Map map = new HashMap();
//结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
for(int i= 1;i <= rsmd.getColumnCount();i++){
map.put(rsmd.getColumnName(i), rs.getObject(i));
}
//添加map到集合中
list.add(map);
}
return list;
}catch(SQLException e){
e.printStackTrace();
}
return null;
}
/**
*执行execute语句
*/
public Object getDataByExecute(String sql , Object[] arrays){
int line = 0;
boolean hasResultSet = false;
pstat = getPreparedStatement(sql);
try {
//设置参数
for (int i = 0; i < arrays.length; i++) {
pstat.setObject(i+1, arrays[i]);
}
//开始查询
hasResultSet = pstat.execute();
if(hasResultSet){
return getDataBySQL(sql, arrays);
}else{
line = pstat.getUpdateCount();
return line;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 关闭所有打开的数据库连接
*/
public void close(){
try{
if(rs != null){
rs.close();
}
if(pstat != null){
pstat.close();
}
if(conn != null){
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
execute immediate
首先在这里发发牢骚，指责下那些刻板的书写方式，不考虑读者理不理解，感觉就是给专业人员用来复习用的一样，没有前戏，直接就高潮，实在受不了！没基础或基础差的完全不知道发生了什么，一脸懵逼的看着，一星差评！！！
execute immediate
以下引用介绍比较好的例子说明
create or replace procedure proc_test(
--参数区域
)
is
--变量区域
--sql脚本
v_sql varchar2(2000) :='''';
--记录学生数量
v_num number;
begin
--执行区域
-- execute immediate用法1：立刻执行sql语句
v_sql := ''create or replace view myview as select id,name from student'';
execute immediate v_sql;
--- execute immediate用法2：立刻执行sql语句，并赋值给某个变量
v_sql := ''select count(1) from student'';
execute immediate v_sql into v_num;
-- execute immediate用法3：带参数的sql
v_sql:=''select * from student t where t.name=:1 and t.age=:2'';
execute immediate v_sql using ''ZhangSan'',23;
end proc_test;
/看了上面的代码，是否觉得理解？NO,NO,NO
对execute immediate 的解释如下：
简单来说 就是你一个存储过程当中 创建了一个表 table_a 然后要用insert into将其他的数据插入到这个table_a当中，但是因为你在创建过程的时候 table_a还不存在，过程就会显示有编译错误，因为table_a不存在必然导致过程无法执行，所以无法编译成功，而把insert into语句加如到 execute immediate之后 则oracle不会再去理会这个对象是否存在，因此可以成功编译和执行。
看完了，估计还会有小朋友举手问，跟动态有什么关系，为何扯上动态，哪里动态了，能不能讲明白，等等。。。
敲黑板，人家说的动态，是说execute immediate后面跟的SQL代码不固定，你想写就写啥，动态的！
然后又有小朋友，站起来了，你呀的，胡我啊，说最后代码，随便写，我都想笑，这个跟直接写SQL代码，不要前面的execute immediate有什么区别？不都执行那条语句么？
哈哈，小朋友你坐下别激动，你问到点上了，execute immediate后边SQL代码要用一对单引号的(即'' '')，而直接写SQL没有的对吧，告诉你动态的秘诀就在于execute immediate后边SQL代码可以去拼接，这就实现了所谓动态，会根据不同情况，拼接不同的代码。
简单举例
直接写的 select * from dual;
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[,define_variable]…| record}]
[USING [IN | OUT | IN OUT] bind_argument[,[IN | OUT \ IN OUT] bind_argumnet]…]
[{RETURNING | RETURN} INTO bind_argument[,bind_argument]…];本地动态SQL语句一个优点是可以在代码中是使用绑定变量。
先说说绑定变量 ":"称绑定变量指示符，解释如下：
它是用户放入查询中的占位符，它会告诉Oracle" 现在生成一个方案框架，实际执行语句的时候，会提供应该使用的实际值"。
例子如下：
select * from emp where dep=''sale'' ; //不使用绑定变量
select * from emp where dep=:sale //使用绑定变量
用法
处理DDL操作(CREATE,ALTER,DROP)
CREATE OR REPLACE PROCEDURE drop_table(table_name VARCHAR2)
IS
Sql_statemet VARCHAR2(100);
BEGIN
Sql_statement:=’DROP TABLE’ || table_name;
EXECUTE IMMEDIATE sql_statement;
END;
/
建立过程drop_table后，调用如下：
SQL> exec drop_table(‘worker’)
处理DCL操作(GRANT REVOKE)
SQL> conn system/manager
CREATE OR REPLACE PROCEDURE grant_sys_priv(priv VARCHAR2,username VARCHAR2)
IS
Sql_stat VARCHAR2(100);
BEGIN
Sql_stat:=’GRANT “ || priv|| ’ TO ’|| username;
EXECUTE IMMEDIATE sql_stat;
END;
/
调用
SQL> exec grant_sys_priv(‘CREATE SESSION’,’SCOTT’)
处理DML操作(INSERT UPDATE DELETE)
如果DML语句带有占位符，那么在E I语句中则要带USING子句
如果DML语句带有RETURNING子句，那么E I语句中要带有RETURNINGINTO子句
例子，处理单行查询：
DECLARE
sql_stat VARCHAR2(100);
emp_record tbl%ROWTYPE;
BEGIN
sql-stat:=''SELECT * FROM tbl WHERE tblno=:no'';
EXECUTE IMMEDIATE sql_stat INTO emp_record USING &1;
dbms_output.put_line(emp_record.ename||emp_record.sal);
END;
EXECUTE IMMEDIATE -- 用法例子
1. 在PL/SQL运行DDL语句
begin
execute immediate ''set role all'';
end;
2. 给动态语句传值(USING 子句)
declare
l_depnam varchar2(20) := ''testing'';
l_loc varchar2(10) := ''Dubai'';
begin
execute immediate ''insert into dept values (:1, :2, :3)''
using 50, l_depnam, l_loc;
commit;
end;
3. 从动态语句检索值(INTO子句)
declare
l_cnt varchar2(20);
begin
execute immediate ''select count(1) from emp''
into l_cnt;
dbms_output.put_line(l_cnt);
end;
4. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.
黓认为IN类型,其它类型必须显式指定
declare
l_routin varchar2(100) := ''gen2161.get_rowcnt'';
l_tblnam varchar2(20) := ''emp'';
l_cnt number;
l_status varchar2(200);
begin
execute immediate ''begin '' || l_routin || ''(:2, :3, :4); end;''
using in l_tblnam, out l_cnt, in out l_status;
if l_status != ''OK'' then
dbms_output.put_line(''error'');
end if;
end;
5. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate ''select empno, ename, deptno '' ||
''from emp where empno = 7934''
into empdtl;
end;
6. 传递并检索值.INTO子句用在USING子句前
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate ''select dname, loc from dept where deptno = :1''
into l_nam, l_loc
using l_dept ;
end;
7. 多行查询选项.对此选项用insert语句填充临时表，
用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.
declare
l_sal pls_integer := 2000;
begin
execute immediate ''insert into temp(empno, ename) '' ||
'' select empno, ename from emp '' ||
'' where sal > :1''
using l_sal;
commit;
end;
execute immediate 的用法
在scott用户中创建上面存储过程：
执行存储过程：
begin
proc_test('20');
end;
/
结果：
execute immediate用法1执行成功！
execute immediate用法2执行成功！员工总数：14
execute immediate用法3执行成功！20部门名称为：RESEARCH
今天关于sql – 为什么使用EXECUTE IMMEDIATE运行此查询会导致它失败？的讲解已经结束，谢谢您的阅读，如果想了解更多关于com.facebook.common.executors.UiThreadImmediateExecutorService的实例源码、execute executeQuery executeUpdate 三种的分别封装使用、execute immediate、execute immediate 的用法的相关知识，请在本站搜索。
本文标签：
