ITPub博客

ORACLE中的支持正则表达式的函数主要有下面四个：1，REGEXP_LIKE ：与LIKE的功能相似2，REGEXP_INSTR ：与INSTR的功能相似3，REGEXP_SUBSTR ：与SUBSTR的功能相似4，REGEXP_REPLACE ：与REPLACE的功能相似它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，但是它们使用POSIX 正则表达式代替...

export ORACLE_SID=DWTHsqlplus / as sysdba <<EOFset feedback offset serveroutput ondeclarev_sql varchar2(4000);v_sqlerrm varchar2(4000);cursor cur_tab is select dt.owner,dt.table_name from dba_tables dt   where dt.owner in ('ODS_BUFF_KKX','SGDM','ZS_OUTPUT','NOVELL_IAS','ODS_B...

This procedure displays the call stack at the point where an exception was raised, evenif the procedure is called from an exception handler in an outer scope. The output issimilar to the output of the SQLERRM function, but not subject to the same sizelimitation. SyntaxDBMS_UTILITY.FORMAT...

CREATE VIEW v_user_viewas(SELECT uu_uugr_ug_uo_ugrr_ur.id,uu_uugr_ug_uo_ugrr_ur.groupid,uu_uugr_ug_uo_ugrr_ur.groupname,uu_uugr_ug_uo_ugrr_ur.identifier,uu_uugr_ug_uo_ugrr_ur.username,uu_uugr_ug_uo_ugrr_ur.account,uu_uugr_ug_uo_ugrr_ur.password,uu_uugr_ug_uo_ugrr_ur.position,uu_uugr_ug_uo...

A用户的过程如下：create or replace procedure ssasv_ename varchar(30);begin select ut.ename into v_ename from emp ut where rownum=1; dbms_output.put_line(v_ename);end;在A用户下执行此过程SQL> set serveroutput on SQL> exec ss;SMITHPL/SQL procedure successfully compl...

create or replace procedure p_di_crtvwis lvc_sql varchar2(500);cursor cur is SELECT DISTINCT upper(viewsql) viewsql FROM (SELECT 'create or replace force view di_'||DMLX_DM ||' as select * from J1_DI.DI_GY_1JDM where dmlx_dm = ''' || DMLX_DM || '''' viewsql  FROM J1_DI.DI_G...

create or replace procedure get_stat(i_owner in varchar2,                                       i_table in varchar2 default null                    ...

declare v_text clob; v_owner varchar2(30); v_view_name varchar2(30); v_count number; v_loc number; v_loc_s number; v_len number; v_text_len number; v_count_s number; v_count_t number; cursor c_xv is  select av.owner,av.view_name...

<span style="font-family:Verdana, Arial, Helvetica, sans-serif;font-size:14px;line-height:20px;white-space:normal;background-color:#FFFFFF;">在oracle中chr()数和ascii()是一对反函数。<br /><br /><p style="margin:10px auto;font-family:Verdana, Arial, Helvetica, sans-serif;font-size:14px;line-height:2...

declare v_text clob; v_owner varchar2(30); v_view_name varchar2(30); v_count number; v_loc number; v_loc_s number; v_len number; cursor c_xv is  select xv.owner,xv.view_name from xt_vwqd xv,al...

declare<br />&nbsp;v_text clob;<br />&nbsp;v_owner varchar2(30);<br />&nbsp;v_view_name varchar2(30);<br />&nbsp;v_count number;<br />&nbsp;v_loc number;<br />&nbsp;v_loc_s number;<br />&nbsp;v_len number;<br />-- select av.view_name,av.owner from all_views av where av.owner='J1_DW' and av.v...

declare v_text varchar2(4000); v_line number; v_max_line number; v_owner varchar2(30); v_name varchar2(30); v_type varchar2(12);-- i_owner varchar2(30);-- i_object varchar2(30); cursor c_xo is&nb...

declare<br />&nbsp;v_text varchar2(4000);<br />&nbsp;v_line number;<br />&nbsp;v_min_line number;<br />&nbsp;v_max_line number;<br />-- i_owner varchar2(30);<br />-- i_object varchar2(30);<br />&nbsp;cursor c_as is<br />&nbsp; select line,case when line=1 then 'create or replace '||text&nbsp...

SQL&gt; declare<br />&nbsp; 2 &nbsp; v_text clob;<br />&nbsp; 3 &nbsp; --v_len number;<br />&nbsp; 4 &nbsp;begin<br />&nbsp; 5 &nbsp; SELECT dbms_metadata.get_ddl('VIEW','V_SB_QYSDSNDSSYHQKMXB','J1_DW') INTO V_TEXT from dual;<br />&nbsp; 6 &nbsp; dbms_output.put_line(v_text);<br />&nbsp; 7 &nb...

create or replace procedure p_getusertabdata(i_user in varchar2)is-- fhandle utl_file.file_type;-- v_line varchar2(4000); v_user varchar2(30); v_table varchar2(30); v_oner varchar2(30); v_line varchar2(4000); cursor cur_tab is  select xt.owner,xt.table_name fro...

create or replace procedure p_gettabdata(i_owner in varchar2,i_table in varchar2)isv_insrt varchar2(4000);v_column_row varchar2(4000);v_mx_col_id number;v_owner varchar2(30);v_table varchar2(30);cursor cur_f is select atc.COLUMN_NAME,atc.DATA_TYPE,atc.CHAR_LENGTH,atc.COLUMN_ID,atc.num_n...

set pagesize 0set linesize 4000set feedback offcol text for a4000spool I:\J1_G3_ZBQ.sqlselect   case when ds.line=1 and rownum=1 then 'create or replace '||text       when ds.line=1 and ds.type='PROCEDURE' and rownum<>1 then '/'||chr(10)||'create or replace '||te...

create or replace procedure p_getusertabsql(   avc_user    varchar2)is  lvc_user      varchar2(30);  lvc_table     varchar2(30);  li_count      number(10);  lvc_bbbz      varchar2(200);  lv...

create or replace procedure p_gettabsql(   avc_user    varchar2,   avc_table   varchar2)is  lvc_user      varchar2(30);  lvc_table     varchar2(30);  fhandle utl_file.file_type;  li_count       ...

用into赋予多个变量时只需要一个into就可以了，如下select NEXT_EXPIRY_DATE,CURR_LIMIT,TOTAL_LIMIT into V_NEXT_EXPIRY_DATE,V_CURR_LIMIT,V_TOTAL_LIMIT from p2p.s_fin_prod_interest_info where FINANCE_PROD_ID=i_prod_id;