[oracle]sqlplus 输出格式化print_table学习
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了[oracle]sqlplus 输出格式化print_table学习,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4063字,纯文字阅读大概需要6分钟。
内容图文
![[oracle]sqlplus 输出格式化print_table学习](/upload/InfoBanner/zyjiaocheng/524/6e6cdc4ac7974c2bb981d9999d584fce.jpg)
定义了一个子存储过程restore,用于脚本执行完成恢复配置(cursor_sharing、nls_date_format),以及关闭游标。
-- small inline procedure to restore the sessions state -- we may have modified the cursor sharing and nls date format -- session variables, this just restores them procedure restore is begin if (upper(l_cs) not in ( ‘FORCE‘,‘SIMILAR‘ )) then execute immediate ‘alter session set cursor_sharing=exact‘; end if; if ( p_date_fmt is not null ) then execute immediate ‘alter session set nls_date_format=‘‘‘ || l_date_fmt || ‘‘‘‘; end if; dbms_sql.close_cursor(l_theCursor); end restore;
若输入参数p_date_fmt不为空,存储系统默认的nls_date_format,用于restore
begin -- I like to see the dates print out with times, by default, the -- format mask I use includes that. In order to be "friendly" -- we save the date current sessions date format and then use -- the one with the date and time. Passing in NULL will cause -- this routine just to use the current date format if ( p_date_fmt is not null ) then select sys_context( ‘userenv‘, ‘nls_date_format‘ ) into l_date_fmt from dual; execute immediate ‘alter session set nls_date_format=‘‘‘ || p_date_fmt || ‘‘‘‘; end if;
为了让这个存储过程,执行的SQL语句能用上绑定变量,减小硬解析,设置cursor_sharing为FORCE,
-- to be bind variable friendly on this ad-hoc queries, we -- look to see if cursor sharing is already set to FORCE or similar, if not, set it so when we parse -- literals are replaced with binds if (dbms_utility.get_parameter_value( ‘cursor_sharing‘, l_status, l_cs ) = 1 ) then if ( upper(l_cs) not in (‘FORCE‘,‘SIMILAR‘)) then execute immediate ‘alter session set cursor_sharing=force‘; end if; end if;
解析和描述SQL语句原文,逐字段来定义(113是BLOB类型此处不适用),所有的字段全部转换为string .按照4000字节定义,
-- parse and describe the query sent to us. we need to know the number of columns and their names. dbms_sql.parse(l_theCursor, p_query, dbms_sql.native ); dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); -- define all columns to be cast to varchar2‘s, we are just printing them out for i in 1 .. l_colCnt loop if ( l_descTbl(i).col_type not in ( 113 ) ) then dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000); end if; end loop;
逐字段打印每一列,因为dbms_output仅打印255个字符/行,因此此处列显示200个字符截取了,同时列名右补空格满足30位,
-- execute the query, so we can fetch l_status := dbms_sql.execute(l_theCursor); -- loop and print out each column on a separate line -- bear in mind that dbms_output only prints 255 characters/line -- so we‘ll only see the first 200 characters by my design... while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop for i in 1 .. l_colCnt loop if ( l_descTbl(i).col_type not in ( 113 ) ) then dbms_sql.column_value( l_theCursor, i, l_columnValue ); dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 )|| ‘: ‘ ||substr( l_columnValue, 1, 300 ) ); end if; end loop; dbms_output.put_line( ‘-----------------‘ ); end loop;
执行完成/出现异常时调用了restore
-- now, restore the session state, no matter what restore; exception when others then restore; raise; end; /
refer to :https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958
[oracle]sqlplus 输出格式化print_table学习
标签:number -- parameter his read ext 入参 mil div
本文系统来源:https://www.cnblogs.com/tingxin/p/12897319.html
内容总结
以上是互联网集市为您收集整理的[oracle]sqlplus 输出格式化print_table学习全部内容,希望文章能够帮你解决[oracle]sqlplus 输出格式化print_table学习所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。