oracle笔记一无限更新(1)

查询一个表的定义语句：

set long 1000 select dbms_metadata.get_ddl('TABLE', 'YOURTABLENAME') from dual; 第一个参数是类型，后面是名称，注意要大写。 用这个成功！ 有时候，我们想查看表，存储，触发器等对象的定语语句，有以下两种方法： 1. 查 all_source 表 2. 用 DBMS_METADATA 包。    一． 通过 all_source 表   先来确认下，通过all_source 表可以查看哪些类型的对象： SQL> SELECT distinct type FROM ALL_SOURCE; TYPE ------------ PROCEDURE PACKAGE PACKAGE BODY LIBRARY TYPE BODY TRIGGER FUNCTION JAVA SOURCE TYPE   从以上的结果我们可以看到，我们可以通过该表查询的对象.但是没有table吧？？？这个我未成功！   查看存储过程定义语句： SQL> SELECT text FROM ALL_SOURCE where TYPE='PROCEDURE' AND NAME ='ADDCUSTBUSS'; TEXT -------------------------------------------------------------------------------- PROCEDURE addcustbuss (    acustid       IN       custbuss.custid%TYPE,    bussname      IN       custbuss.businessname%TYPE,    aopid         IN       custbuss.opid%TYPE,    acreatetime   IN       custbuss.createtime%TYPE,    aCustTel      IN       custbuss.CustTel%TYPE,--客户电话    aContact      IN       custbuss.Contact%TYPE,--联系人    aFeedback     IN       custbuss.Feedback%TYPE,--客户反馈    asid          OUT      custbuss.ID%TYPE,    RESULT        OUT      INTEGER ) IS BEGIN    RESULT := -1;      SELECT getarea || TO_CHAR (idseq.NEXTVAL, 'FM0999999999')      INTO asid      FROM DUAL;      INSERT INTO custbuss                (ID, custid, businessname, opid, createtime,CustTel,Contact,Feedback           )         VALUES (asid, acustid, bussname, aopid, acreatetime,aCustTel,aContact,aFeedback                );    RESULT := 0; EXCEPTION    WHEN OTHERS    THEN       RESULT := -1; END addcustbuss; 已选择32行。 SQL>   查看触发器 定义语句 SQL> SELECT text FROM ALL_SOURCE where TYPE='TRIGGER' AND NAME ='TRDB_TEAM'; TEXT ----------------------------------------------------------------------------- TRIGGER "NEWCCS".trdb_team    BEFORE DELETE    ON team    REFERENCING NEW AS NEW OLD AS OLD    FOR EACH ROW BEGIN    DELETE FROM team_teamgroup_map          WHERE teamid = :OLD.teamid; END trdb_team; 已选择10行。   方法也比较简单，修改TYPE 和 NAME 就可以。 注意要大写。       二． 通过  DBMS_METADATA  包 Oracle 的在线文档，对这个包有详细说明： DBMS_METADATA http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_metada.htm#ARPLS640     通过该dbms_metadata 包的get_ddl（） 方法，我们可以查看表，索引，视图，存储过程等的定义语句。 用法： SQL> select dbms_metadata.get_ddl('对象类型','名称','用户名') from dual;    1. 查看表的定义语句 SQL> set long 9999999 SQL> select dbms_metadata.get_ddl('TABLE','BBS','NEWCCS') from dual;   DBMS_METADATA.GET_DDL('TABLE','BBS','NEWCCS') --------------------------------------------------------------------------------       CREATE TABLE "NEWCCS"."BBS"    (    "BBSID" NUMBER(16,0),         "SENDER" VARCHAR2(20) NOT NULL ENABLE,         "INCEPT" VARCHAR2(20) NOT NULL ENABLE,         "MSGTITLE" VARCHAR2(40) NOT NULL ENABLE,         "MSG" VARCHAR2(500),         "SENDTIME" DATE DEFAULT sysdate NOT NULL ENABLE,         "MSGTOP" VARCHAR2(1) DEFAULT 0,         "MSGFILENAME" VARCHAR2(100),         "MSGFILE" LONG RAW,         "EDITTIME" DATE DEFAULT sysdate,          CONSTRAINT "PK_BBS" PRIMARY KEY ("BBSID")   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE  DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "NEWCCS"  ENABLE    ) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE  DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "NEWCCS" SQL>   从上面的查询可以看到，返回的结果里面含有一些storage 属性，看起来很不舒服。我们可以通过设置会话级别来不显示这些storage 属性。   Example: Fetch the DDL for all Complete Tables in the Current Schema, Filter Out Nested Tables and Overflow Segments This example fetches the DDL for all "complete" tables in the current schema, filtering out nested tables and overflow segments. The example uses SET_TRANSFORM_PARAM (with the handle value = DBMS_METADATA.SESSION_TRANSFORM meaning "for the current session") to specify that storage clauses are not to be returned in the SQL DDL. Afterwards, the example resets the session-level parameters to their defaults. To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query.   SQL> SET LONG 2000000 SQL> SET PAGESIZE 0 SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,' STORAGE',false);   PL/SQL 过程已成功完成。   SQL> select dbms_metadata.get_ddl('TABLE','BBS','NEWCCS') from dual;     CREATE TABLE "NEWCCS"."BBS"    (    "BBSID" NUMBER(16,0),         "SENDER" VARCHAR2(20) NOT NULL ENABLE,         "INCEPT" VARCHAR2(20) NOT NULL ENABLE,         "MSGTITLE" VARCHAR2(40) NOT NULL ENABLE,         "MSG" VARCHAR2(500),         "SENDTIME" DATE DEFAULT sysdate NOT NULL ENABLE,         "MSGTOP" VARCHAR2(1) DEFAULT 0,         "MSGFILENAME" VARCHAR2(100),         "MSGFILE" LONG RAW,         "EDITTIME" DATE DEFAULT sysdate,          CONSTRAINT "PK_BBS" PRIMARY KEY ("BBSID")   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   TABLESPACE "NEWCCS"  ENABLE    ) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING   TABLESPACE "NEWCCS"   SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,' DEFAULT'); PL/SQL 过程已成功完成。 SQL>   2. 查看 存储过程 的定义语句   SQL> SET LONG 9999 SQL> select dbms_metadata.get_ddl('PROCEDURE','ANALYZEDB','NEWCCS') from dual;   CREATE OR REPLACE PROCEDURE "NEWCCS"."ANALYZEDB" IS    CURSOR get_ownertable    IS       SELECT table_name         FROM user_tables;    ownertable   get_ownertable%ROWTYPE; BEGIN    OPEN get_ownertable;    LOOP       FETCH get_ownertable        INTO ownertable;       EXIT WHEN get_ownertable%NOTFOUND;       EXECUTE IMMEDIATE    'analyze table '                         || ownertable.table_name                         || ' compute statistics for table for all indexes for all indexed columns ';     END LOOP; EXCEPTION    WHEN OTHERS    THEN       RAISE; END;     其他的查询和这个类似，只需修改对象类型即可。 感兴趣的自己实验一下。       ------------------------------------------------------------------------------  Blog： http://blog.csdn.net/tianlesoftware  网上资源： http://tianlesoftware.download.csdn.net  相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx      --现有字符串'23456中国3-00=.,45'，想得到结果2345630045 --方法一：translate函数 select translate('23456中国3-00=.,45','0123456789'||'23456中国3-00=.,45','0123456789') from dual; --方法二：自定义函数。 create or replace function f_filter_str(var_str varchar) return varchar is var_str_new varchar2(2000); begin for i in 1..length(var_str) loop if ascii(substr(var_str,i,1))>=48 and ascii(substr(var_str,i,1))<=57 then var_str_new := var_str_new || substr(var_str,i,1); end if; end loop; return var_str_new; end f_filter_str; / select f_filter_str('23456中国3-00=.,45') from dual; --方法三：正则表达式 --oracle10g以上版本 select regexp_replace('23456中国3-00=.,45','[^0-9]') from dual; --方法四： create or replace and compile java source named stringutil as import Java.io.*; import Java.sql.*; public class StringUtil { public static String filterStr2Num(String str){ String tmpstr = str; String savestr; String result = ""; for (int i=0;i<tmpstr.length();i++){ savestr = tmpstr.substring(i,i+1); if (StringUtil.isNumeric(savestr)){ result+=savestr; } } return result; } public static String filterStr2Str(String str){ String tmpstr = str; String savestr; String result = ""; for (int i=0;i<tmpstr.length();i++){ savestr = tmpstr.substring(i,i+1); if (!StringUtil.isNumeric(savestr)){ result+=savestr; } } return result; } public static boolean isNumeric(String str){ try{ Integer.valueOf(str); return true; }catch(Exception e){ return false; } }  } 然后： create or replace function Java_filterStr2Num(str In Varchar) return varchar2 as LANGUAGE JAVA NAME 'StringUtil.filterStr2Num(java.lang.String) return java.lang.String'; create or replace function Java_filterStr2Str(str In Varchar) return varchar2 as LANGUAGE JAVA NAME 'StringUtil.filterStr2Str(java.lang.String) return java.lang.String'; 再然后 Select java_filterStr2Num('254名字性345345别介绍') From dual; 取数字 Select java_filterStr2Str('254名字性345345别介绍') From dual; 取文字