sql 代码
- /*
- 说明:
- 在调试sql脚本时,如果要用Dbms_Output.Put_Line显示脚本中的变量,如果变量中的内容单行长度
- 超过了255个字符,会提示下面错误:
- ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
- 所以我们可以用下面的已经创建好的包和视图来实现。
- 简单点说就是也可以创建一个表,其中一个字段为long,将变量插入后再用select去查看。上面方式省略了创建表的过程。
- */
- -- 第一步
- create or replace package my_output
- as
- procedure put( s in varchar2 );
- procedure put_line( s in varchar2 );
- procedure new_line;
- function get_line( n in number ) return varchar2;
- pragma restrict_references( get_line, wnds, rnds );
- function get_line_count return number;
- pragma restrict_references( get_line_count, wnds, rnds, wnps );
- pragma restrict_references( my_output, wnds, rnds, wnps, rnps );
- end;
- -- 第二步
- create or replace package body my_output
- as
- type Array is table of varchar2(4000) index by binary_integer;
- g_data array;
- g_cnt number default 1;
- procedure put( s in varchar2 )
- is
- begin
- if ( g_data.last is not null ) then
- g_data(g_data.last) := g_data(g_data.last) || s;
- else
- g_data(1) := s;
- end if;
- end;
- procedure put_line( s in varchar2 )
- is
- begin
- put( s );
- g_data(g_data.last+1) := null;
- end;
- procedure new_line
- is
- begin
- put( null );
- g_data(g_data.last+1) := null;
- end;
- function get_line( n in number ) return varchar2
- is
- l_str varchar2(4000) default g_data(n);
- begin
- g_data.delete(n);
- return l_str;
- end;
- function get_line_count return number
- is
- begin
- return g_data.count+1;
- end;
- end;
- -- 第三步
- create or replace view my_output_view
- as
- select rownum lineno, my_output.get_line( rownum ) text
- from all_objects
- where rownum < ( select my_output.get_line_count from dual );
- -----------------------------------------------------------------
- -- 实现
- -- 1 写入要查看的内容
- declare
- QuerySql varchar2(4000);
- begin
- Dbms_Output.enable(4000);
- QuerySql :=' IIDD AS ID, IsDel AS 已删除, AnJianID AS 案件编号, BaoGaoRenXM AS 报案人姓名, JieBaoRenXM AS 接报人姓名, JieBaoShiJian AS 接报时间, BM_MingCheng AS 办案单位名称 From XZ_ShouLiDengJi a WHERE (exists (Select ''S'' from GG_AnJianBanLi where AnJianID=a.AnJianID) and not exists (Select ''S'' From GG_AnJian Where AnJianID=a.AnJianID and HuiBiRenYuan_BH like ''%admin%'')) ORDER BY AddDateTime DESC';
- Dbms_Output.Put_Line('LENGTH : ' || Length(QuerySql));
- my_output.put_line(QuerySql);
- end;
- -- 2 查看结果
- select * from my_output_view