Oracle中过程里加动态sql的问题

你需要有对目的表的INSERT和UPDATE权限,以及对源表的SELECT权限。

Oracle中实现自动增长列另一种方法

--在users表的userid列上实现自动增长列

--需要同时使用序列和行级触发器

create sequence seq_userid; --创建序列

create or replace trigger users_bir --创建行级触发器

before insert on users

for each row

declare

tempnum number;

begin

select seq_useridnextval into tempnum from dual;

:new userid := 'Usr' || lpda(tempnum,7,'0');

end users_bir ;

/

OTL介绍：

OTL 是Oracle, Odbc and DB2-CLI Template Library 的缩写，是一个C++ *** 控关系数据库的模板库，最新版本40104，参见>

前言

sql_trace 是我在工作中经常要用到的调优工具 相比较statspack 我更愿意用这个工具

因为数据库慢原因的 %以上是由于sql问题造成的 statspack没有sql的执行计划 显示没有它直观 方便 对想要针对性不强

介绍数据库调优需要经常会用到的工具 可以很精确地跟抓取相关session正在运行的sql 再通过tkprof分析出来sql的执行计划等相关信息 从而判断那些sql语句存在问题

统计如下信息（摘字官方文档）

Parse execute and fetch counts

CPU and elapsed times

Physical reads and logical reads

Number of rows processed

Misses on the library cache

Username under which each parse occurred

Each mit and rollback

使用

使用前需要注意的地方

初始化参数timed_statistics=true   允许sql trace 和其他的一些动态性能视图收集与时间（cpu elapsed）有关的参数 一定要打开 不然相关信息不会被收集 这是一个动态的参数 也可以在session级别设置

SQL>alter session set titimed_statistics=true

MAX_DUMP_FILE_SIZE跟踪文件的大小的限制 如果跟踪信息较多可以设置成unlimited 可以是KB MB单位 I开始默认为unlimited这是一个动态的参数 也可以在session级别设置

SQL>alter system set max_dump_file_size=

SQL>alter system set max_dump_file_size=unlimited

USER_DUMP_DEST指定跟踪文件的路径 默认路径实在$ORACLE_BASE/admin/ORA_SID/udump这是一个动态的参数 也可以在session级别设置

SQL>alter system set user_dump_dest=/oracle/trace

数据库级别

设置slq_trace参数为true会对整个实例进行跟踪 包括所有进程 用户进程和后台进程 会造成比较严重的性能问题 生产环境一定要慎用

SQL>alter system set sql_trace=true;

Session级别

当前会话

SQL>alter session set sql_trace=true;

SQL>alter session set sql_trace=false;

其他会话

通过oracle提供的系统包 DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION来实现

SQL>execute dbms_system set_sql_trace_in_session(sid serial# true);

SQL>execute dbms_system set_sql_trace_in_session(sid serial# false);

注

sid serial#从v$session视图中获得

DBMS_SYSTEM包里还可以对其他用户的参数（如 timed_statistics max_dump_file）进行设置 在这不做介绍了 很少用到 想了解dbms_system里的程序包可以desc dbms_system看一下

得到trace文件后我们要用tkprof他进行格式化 通过sql语句快速定位到相应的trace文件

Tkprof

tkprof的目的是将sql trace生成的跟踪文件转换成用户可以理解的格式

格式

tkprof tracefile outputfile [optional | parameters ]

参数和选项（这里只介绍最常用的 也是最实用的）

explain=user/password执行explain命令将结果放在SQL trace的输出文件中

sys=[yes/no]确定系统是否列出由sys用户产生或重调的sql语句

sort=sort_option按照指定的方法对sql trace的输出文件进行降序排序

sort_option选项

prscnt按解析次数排序

prscpu按解析所花cpu时间排序

prsela按解析所经历的时间排序

prsdsk按解析时物理的读 *** 作的次数排序

prsqry按解析时以一致模式读取数据块的次数排序

prscu按解析时以当前读取数据块的次数进行排序

execnt按执行次数排序

execpu按执行时花的cpu时间排序

exeela按执行所经历的时间排序

exedsk按执行时物理读 *** 作的次数排序

exeqry按执行时以一致模式读取数据块的次数排序

execu按执行时以当前模式读取数据块的次数排序

exerow按执行时处理的记录的次数进行排序

exemis按执行时库缓冲区的错误排序

fchcnt按返回数据的次数进行排序

fchcpu按返回数据cpu所花时间排序

fchela按返回数据所经历的时间排序

fchdsk按返回数据时的物理读 *** 作的次数排序

fchqry按返回数据时一致模式读取数据块的次数排序

fchcu按返回数据时当前模式读取数据块的次数排序

fchrow按返回数据时处理的数据数量排序

注

这些排序中我经常用到的是fchdsk fckchela fchqry 因为有问题的sql一般都是大的查询造成的 当然更新 插入 删除时也会存在全表扫描 这就需要:exedsk exeqry exeela等选项 根据具体情况具体分析

Cpu时间和Elapsed时间都是以秒为单位 而且两个值基本上一样 但我比较常用elapsed 他是反映的用户相应时间 从运行sql到用户得到结果的时间 会更实际些

tkprof输出文件各列的含义 （理解下面的含义对我们快速定位问题很有帮助）

parse:

将sql语句转换成执行计划 包括检查是否有正确的授权 需要到得表 列及其他引用到得对象是否存在 这些信息分别存在v$librarycache v$rowcache

execute

oracle实际执行的语句 如 insert update delete 这些会修改数据 对于select *** 作 这部只是确定选择的行数

fetch

返回查询获得的行数 只有执行select会被收集

Count

这个语句被parse execute fetch的次数的统计

Cpu

这个语句所有的parse execute fetch所用的cpu总的时间 以秒为单位 如果TIMED_STATISTICS 关闭的话 值为

Elapsed

这个语句所有的parse execute fetch所消耗的总的时间 以秒为单位 如果TIMED_STATISTICS 关闭的话 值为

Disk

这个语句所有的parse execute fetch从磁盘上的数据文件中读取的数据块的数量

Query

在一致性读的模式下 这个语句所有的parse execute fetch所获取的buffer数量（这部分是从内存读取的也就是逻辑读取的 相当于执行计划里的consistent gets）

Current

在current模式下 这个语句所有的parse execute fetch所获取的buffer数量 一般是current模式下发生的delect insert update的 *** 作都会获取buffer

Rows

语句返回的行数 不包括子查询中返回的记录数目 对于select语句 返回在fetch这步 对于insert delete update *** 作 返回记录是在execute这步

分析

我一般的思路步骤是

先找磁盘多的sq l(sort= fchdsk ) 意味着全表扫描 找运行时间长的(sort= fchela) 意味着sql可能写的不好或磁盘 逻辑读较多 找出一致性读较多的(sort= fchqry) 当表不是很大的时候（可能全部缓存住了） 没有发生磁盘读 但不意味着不需要建立索引 或者sql需要优化 找出当前模式从缓冲区获得数据的数量(sort=exedsk exeela exeqry) 这些主要集中在dml语句里的 *** 作 看是否有必要优化sql或建立索引之所以排序是为了在sql很多的时候快速定位sql 如果sql比较少的话就没必要排序了 但我们要有分析问题的思路

举例

我自己建立了一个表

create table t (id int);

begin

for v in loop

insert into t values(v );

end loop

mit;

end;

下面是sql_trace所抓到得sql

不正常状态

select

from t

where id=

call count cpu elapsed disk query current rows

     

Parse                                                                   Execute                                                                 Fetch                                                        

     

total                                                        

Misses in library cache during parse:

Optimizer goal: CHOOSE

Parsing user id: (WH)

Rows Row Source Operation

  

TABLE ACCESS FULL T

Rows Execution Plan

  

SELECT STATEMENT GOAL: CHOOSE

TABLE ACCESS (FULL) OF T

首先这是一个select语句 它走了全部扫描

磁盘读（ ）和逻辑读（ ）都很多

运行了 次（Execute） 分析了 次（Parse） 一共用了将近 秒（elapsed）

我只是选择表的一行的数据的结果 就发生这么大的成本 很显然是全表扫描的结果造成的

正常状态

在做跟踪前我为这个表建立了一个索引

Create index t on t (id);

select

from t

where id=

call count cpu elapsed disk query current rows

     

Parse                                                                 Execute                                                                 Fetch                                                                

     

total                                                            

Misses in library cache during parse:

Optimizer goal: CHOOSE

Parsing user id: (WH)

Rows Row Source Operation

  

INDEX RANGE SCAN T (object id )

Rows Execution Plan

  

SELECT STATEMENT GOAL: CHOOSE

INDEX (RANGE SCAN) OF T (NON UNIQUE)

同样的语句

它走了索引 物理读 这个 其实是开始读索引时需要第一次读入的 以后运行就没有了

逻辑读 （平均这个sql一次 个逻辑读）

同样运行了 次（Execute）

分析了 次（Parse） 运行次数越多 分析次数越少越好一共只用了 秒（elapsed）

lishixinzhi/Article/program/Oracle/201311/17866

内容摘要 在PL/SQL开发过程中 使用SQL PL/SQL可以实现大部份的需求 但是在某些特殊的情况下 在PL/SQL中使用标准的SQL语句或DML语句不能实现自己的需求 比如需要动态建表或某个不确定的 *** 作需要动态执行 这就需要使用动态SQL来实现 本文通过几个实例来详细的讲解动态SQL的使用 本文适宜读者范围 Oracle初级 中级 系统环境 OS windows Professional (英文版)Oracle 正文 一般的PL/SQL程序设计中 在DML和事务控制的语句中可以直接使用SQL 但是DDL语句及系统控制语句却不能在PL/SQL中直接使用 要想实现在PL/SQL中使用DDL语句及系统控制语句 可以通过使用动态SQL来实现 首先我们应该了解什么是动态SQL 在Oracle数据库开发PL/SQL块中我们使用的SQL分为 静态SQL语句和动态SQL语句 所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的 执行的是确定对象 而动态SQL是指在PL/SQL块编译时SQL语句是不确定的 如根据用户输入的参数的不同而执行不同的 *** 作 编译程序对动态语句部分不进行处理 只是在程序运行时动态地创建语句 对语句进行语法分析并执行该语句 Oracle中动态SQL可以通过本地动态SQL来执行 也可以通过DBMS_SQL包来执行 下面就这两种情况分别进行说明 一 本地动态SQL 本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的 本地动态SQL执行DDL语句 需求 根据用户输入的表名及字段名等参数动态建表 create or replace procedure proc_test(table_name in varchar 表名field in varchar 字段名datatype in varchar 字段类型field in varchar 字段名datatype in varchar 字段类型) asstr_sql varchar ( );beginstr_sql:= create table ||table_name|| ( ||field || ||datatype || ||field || ||datatype || ) ;execute immediate str_sql;　 动态执行DDL语句exceptionwhen others thennull;end ;以上是编译通过的存储过程代码 下面执行存储过程动态建表 SQL> execute proc_test( dinya_test id number( ) not null name varchar ( ) );PL/SQL procedure successfully pletedSQL> desc dinya_test;Name Type　Nullable Default Comments ID　 NUMBER( )NAME VARCHAR ( ) YSQL>到这里 就实现了我们的需求 使用本地动态SQL根据用户输入的表名及字段名 字段类型等参数来实现动态执行DDL语句 本地动态SQL执行DML语句 需求 将用户输入的值插入到上例中建好的dinya_test表中 create or replace procedure proc_insert(id in number 输入序号name in varchar 输入姓名) asstr_sql varchar ( );beginstr_sql:= insert into dinya_test values(: : ) ;execute immediate str_sql using id name; 动态执行插入 *** 作exceptionwhen others thennull;end ;执行存储过程 插入数据到测试表中 SQL> execute proc_insert( dinya );PL/SQL procedure successfully pletedSQL> select from dinya_test;ID　NAMEdinya在上例中 本地动态SQL执行DML语句时使用了using子句 按顺序将输入的值绑定到变量 如果需要输出参数 可以在执行动态SQL的时候 使用RETURNING INTO 子句 如 declarep_id number:= ;v_count number;beginv_string:= select count() from table_name a where a id=:id ;execute immediate v_string into v_count using p_id;end ;更多的关于动态SQL中关于返回值及为输出输入绑定变量执行参数模式的问题 请读者自行做测试 二 使用DBMS_SQL包 使用DBMS_SQL包实现动态SQL的步骤如下 A 先将要执行的SQL语句或一个语句块放到一个字符串变量中 B 使用DBMS_SQL包的parse过程来分析该字符串 C 使用DBMS_SQL包的bind_variable过程来绑定变量 D 使用DBMS_SQL包的execute函数来执行语句 使用DBMS_SQL包执行DDL语句 需求 使用DBMS_SQL包根据用户输入的表名 字段名及字段类型建表 create or replace procedure proc_dbms_sql(table_name in varchar 表名field_name in varchar 字段名datatype in varchar 字段类型field_name in varchar 字段名datatype in varchar 字段类型)asv_cursor number;　 定义光标v_string varchar ( );　 定义字符串变量v_row number;　 行数beginv_cursor:=dbms_sql open_cursor; 为处理打开光标v_string:= create table ||table_name|| ( ||field_name || ||datatype || ||field_name || ||datatype || ) ;dbms_sql parse(v_cursor v_string dbms_sql native); 分析语句v_row:=dbms_sql execute(v_cursor);　 执行语句dbms_sql close_cursor(v_cursor); 关闭光标exceptionwhen others thendbms_sql close_cursor(v_cursor);　 关闭光标raise;end;以上过程编译通过后 执行过程创建表结构 SQL> execute proc_dbms_sql( dinya_test id number( ) not null name varchar ( ) );PL/SQL procedure successfully pletedSQL> desc dinya_test ;Name Type　Nullable Default Comments ID　 NUMBER( )NAME VARCHAR ( ) YSQL> 使用DBMS_SQL包执行DML语句 需求 使用DBMS_SQL包根据用户输入的值更新表中相对应的记录 查看表中已有记录 SQL> select from dinya_test ;ID NAME Oracle CSDN ERPSQL>建存储过程 并编译通过 create or replace procedure proc_dbms_sql_update(id number name varchar )asv_cursor number; 定义光标v_string varchar ( );　 字符串变量v_row number;　 行数beginv_cursor:=dbms_sql open_cursor; 为处理打开光标v_string:= update dinya_test a set a name=:p_name where a id=:p_id ;dbms_sql parse(v_cursor v_string dbms_sql native);　 分析语句dbms_sql bind_variable(v_cursor :p_name name); 绑定变量dbms_sql bind_variable(v_cursor :p_id id);　 绑定变量v_row:=dbms_sql execute(v_cursor);　 执行动态SQLdbms_sql close_cursor(v_cursor); 关闭光标exceptionwhen others thendbms_sql close_cursor(v_cursor); 关闭光标raise;end;执行过程 根据用户输入的参数更新表中的数据 SQL> execute proc_dbms_sql_update( csdn_dinya );PL/SQL procedure successfully pletedSQL> select from dinya_test ;ID NAME Oracle csdn_dinya ERPSQL>执行过程后将第二条的name字段的数据更新为新值csdn_dinya 这样就完成了使用dbms_sql包来执行DML语句的功能 使用DBMS_SQL中 如果要执行的动态语句不是查询语句 使用DBMS_SQL Execute或DBMS_SQL Variable_Value来执行 如果要执行动态语句是查询语句 则要使用DBMS_SQL define_column定义输出变量 然后使用DBMS_SQL Execute DBMS_SQL Fetch_Rows DBMS_SQL Column_Value及DBMS_SQL Variable_Value来执行查询并得到结果 总结说明 在Oracle开发过程中 我们可以使用动态SQL来执行DDL语句 DML语句 事务控制语句及系统控制语句 但是需要注意的是 PL/SQL块中使用动态SQL执行DDL语句的时候与别的不同 在DDL中使用绑定变量是非法的（bind_variable(v_cursor :p_name name)） 分析后不需要执行DBMS_SQL Bind_Variable 直接将输入的变量加到字符串中即可 另外 DDL是在调用DBMS_SQL lishixinzhi/Article/program/SQLServer/201311/22089

好吧，我辛苦下，我来给你写拼接SQL函数

create table t_a (

c1 varchar2(200),

c2 varchar2(200),

ca varchar2(200),

cb varchar2(200),

cc varchar2(200)

);

create or replace function get_sql_of_tab( c_owner varchar, c_tab_name varchar) return varchar2 as

c_sql varchar2(4000) ;

cursor c1 is

select tcolumn_name from dba_tab_cols t

where table_name = upper(c_tab_name)

and owner = upper(c_owner)

order by tcolumn_id;

begin

for r1 in c1 loop

c_sql := c_sql ||' ' || r1column_name ||',' ;

end loop ;

c_sql := 'SELECT '|| substr(c_sql,1,length(c_sql)-1) || ' FROM '||upper(c_tab_name) ;

return c_sql ;

end get_sql_of_tab ;

/

---------------------以下是PL/SQL命令窗口函数的效果-------------

SQL> select get_sql_of_tab('gxbx','t_a') from dual ;

GET_SQL_OF_TAB('GXBX','T_A')

--------------------------------------------------------------------------------

SELECT C1, C2, CA, CB, CC FROM T_A

嘿嘿，动态生产SQL，好吧剩下的你应该明白咋做了。

函数的两个入参 用户名 表名，NND忘记说了

以上就是关于Oracle中过程里加动态sql的问题全部的内容，包括:Oracle中过程里加动态sql的问题、小弟使用OTL函数，动态拼凑SQL查询oracle数据库数据问题、Oraclesql等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！