oracle – EXEC_SQL,EXECUTE IMMEDIATE,DBMS_SQL和内联SQL之间的
我一直在浏览一些PL / SQL(在Oracle SQL Developer中),并且已经看到了几种不同格式的SQL被调用.
为了保证当前和未来代码的一致性和速度,我想知道哪个是首选. 我见过四种类型. 1)普通DDL: CREATE TABLE newtable AS SELECT * FROM pSource; 2)执行立即(本机动态SQL): statement := 'CREATE TABLE newtable AS SELECT * FROM ' || pSource; EXECUTE IMMEDIATE statement; 3)EXEC_SQL: EXEC_SQL('CREATE TABLE newtable AS SELECT * FROM ' || pSource); 4)DBMS_SQL: cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cursor,'CREATE TABLE newtable AS SELECT * FROM ' || pSource,DBMS_SQL.NATIVE); numRows := DBMS_SQL.EXECUTE(cursor); 这些不同的呼叫方式之间是否有任何特定的优点/缺点/限制? 解决方法1)您不能在PL / SQL块内执行直接DDL.BEGIN CREATE TABLE TEST AS ( SELECT * FROM FND_USER ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / 产量: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: 2)EXECUTE IMMEDIATE(及其姐妹DBMS_SQL)用于在PL / SQL块内执行SQL.这些与“常规”SQL的不同之处在于它们实际上使用完全不同的SQL引擎(在PL / SQL的情况下它在oracle进程中运行)来计算.这就是为什么我们这么多人宣讲“如果你能在SQL中做到这一点,就不要在PL / SQL中做到”. declare c number; d number; col_cnt integer; f boolean; rec_tab dbms_sql.desc_tab; col_num number; procedure print_rec(rec in dbms_sql.desc_rec) is begin dbms_output.new_line; dbms_output.put_line('col_type = ' || rec.col_type); dbms_output.put_line('col_maxlen = ' || rec.col_max_len); dbms_output.put_line('col_name = ' || rec.col_name); dbms_output.put_line('col_name_len = ' || rec.col_name_len); dbms_output.put_line('col_schema_name = ' || rec.col_schema_name); dbms_output.put_line('col_schema_name_len = ' || rec.col_schema_name_len); dbms_output.put_line('col_precision = ' || rec.col_precision); dbms_output.put_line('col_scale = ' || rec.col_scale); dbms_output.put('col_null_ok = '); if (rec.col_null_ok) then dbms_output.put_line('true'); else dbms_output.put_line('false'); end if; end; begin c := dbms_sql.open_cursor; dbms_sql.parse(c,'select * from fnd_user',dbms_sql.native); d := dbms_sql.execute(c); dbms_sql.describe_columns(c,col_cnt,rec_tab); /* * Following loop could simply be for j in 1..col_cnt loop. * Here we are simply illustrating some of the PL/SQL table * features. */ col_num := rec_tab.first; if (col_num is not null) then loop print_rec(rec_tab(col_num)); col_num := rec_tab.next(col_num); exit when (col_num is null); end loop; end if; dbms_sql.close_cursor(c); end; / Source 3)EXEC_SQL是上述DBMS_SQL的表单特定版本.明智地使用它. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |