oracle 值等于n a,ORACLE编程详解

最新推荐文章于 2021-05-29 09:32:48 发布

范奕信

最新推荐文章于 2021-05-29 09:32:48 发布

阅读量456

点赞数 1

文章标签： oracle 值等于n a

0 . 查看系统表空间

select * from dba_tablespaces

1 . 查看表结构

desc table_name

2 . 查询数据库SID

select name from v$database;

3 . 用 B 表的数据更新 A 表数据 ( 有关联的字段 )

UPDATE A SET name=(SELECT name FROM B WHERE

A.id=B.id) WHERE B.id IS NOT NULL ；

4 . 随机数函数 DBMS_RANDOM.RANDOM

SQL> select dbms_random.random from dual

5 . 如何在字符串里加回车？

SQL> select 'line1'||chr(10)||'line2' from dual;

6 . 如何使 select 语句使查询结果自动生成序号 ?

SQL> select rownum, fieldname from table;

7 . 怎么可以快速做一个和原表一样的备份表 ?

create table new_table as (select * from old_table) ；

8 . 如何单独备份一个或多个表？

exp 用户 / 密码 tables=( 表 1 ， … ，表 2)

9. 如何单独备份一个或多个用户？

exp system/manager owner=( 用户 1 ，用户 2 ， … ，用户 n) file= 导出文件

10 . 如何执行脚本 SQL 文件 ?

SQL>@$PATH/filename.sql ;

11. 如何快速清空一个大表 ?

SQL>truncate table table_name ;

12 . 字符串的连接

select concat(col1 ， col2) from table ;

select col1||col2 from table ;

13 . 怎么把 select 出来的结果导到一个文本文件中？

SQL> spool c:\test.txt;

SQL> select * from emp;

SQL> spool off ;

14. 如何测试 SQL 语句执行所用的时间 ?

SQL>set timing on ;

SQL>select * from tablename ;

15 . 改变字段大小 ?

-- 改大行，改小不行(除非都是空的)

SQL>alter table table_name modify (field_name varchar2(100)) ;

16 . 如何查询某天的数据 ?

-- datefield为要查询的日期字段

SQL>select * from table_name where trunc( datefield ) ＝ to_date('2003-05-02' ，'yyyy-mm-dd') ;

17. 如何修改表名 ?

SQL> alter table old_table_name rename to new_table_name;

18 . 如何搜索出前 N 条记录？

SQL> SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno;

-- 搜索一个范围的数据

SQL> SELECT * FROM (SELECT ROWNUM RN,EMPNO,ENAME FROM EMP) WHERE RN > 5 AND RN < 10 ORDER BY empno;

19 . 怎样用 Sql 语句实现查找一列中第 N 大值？

SQL> select * from (select t.* , dense_rank() over (order by sal) rank from employee) where rank = N ；

20 . 如何在给现有的日期加上 2 年？

SQL> select add_months(sysdate ， 24) from dual ；

21 . 返回大于等于 N 的最小整数值 ?

SQL> SELECT CEIL(N) FROM DUAL ；

22 . 返回小于等于 N 的最小整数值 ?

SQL> SELECT FLOOR(N) FROM DUAL ；

23 . 返回当前月的最后一天 ?

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL ；

24 . 如何不同用户间数据导入 ?

IMP SYSTEM/MANAGER FILE=AA.DMP FROMUSER=USER_OLD TOUSER=USER_NEW ROWS=Y INDEXES=Y ；

25 . 如何找数据库表的主键字段的名称 ?

SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='TABLE_NAME' ；

26 . 两个结果集互加的函数 ?

SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW ；

SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;

SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW ；

27 . 两个结果集互减的函数 ?

SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW ；

28. 如何配置 Sequence?

创建sequence

create sequence seq_cust start 1 incrememt by

建表

create table cust {

cust_id smallint not null,

...

}

insert 数据

insert into table cust values( seq_cust.nextval, ... )

insert into table cust values( seq_cust.currval, ... )

29 . 日期各部分的写法

-- 取时间点的年份的写法：

SELECT TO_CHAR(SYSDATE ， 'YYYY') FROM DUAL ；

-- 取时间点的月份的写法：

SELECT TO_CHAR(SYSDATE ， 'MM') FROM DUAL ；

-- 取时间点的日的写法：

SELECT TO_CHAR(SYSDATE ， 'DD') FROM DUAL ；

-- 取时间点的时的写法：

SELECT TO_CHAR(SYSDATE ， 'HH24') FROM DUAL ；

-- 取时间点的分的写法：

SELECT TO_CHAR(SYSDATE ， 'MI') FROM DUAL ；

-- 取时间点的秒的写法：

SELECT TO_CHAR(SYSDATE ， 'SS') FROM DUAL ；

-- 取时间点的日期的写法：

SELECT TRUNC(SYSDATE) FROM DUAL ；

-- 取时间点的时间的写法：

SELECT TO_CHAR(SYSDATE ， 'HH24 ： MI ： SS') FROM DUAL ；

-- 日期，时间形态变为字符形态：

SELECT TO_CHAR(SYSDATE) FROM DUAL ；

-- 将字符串转换成日期或时间形态：

SELECT TO_DATE('2003/08/01') FROM DUAL ；

-- 返回参数的星期几的写法：

SELECT TO_CHAR(SYSDATE ， 'D') FROM DUAL ；

-- 返回参数一年中的第几天的写法：

SELECT TO_CHAR(SYSDATE ， 'DDD') FROM DUAL ；

-- 返回午夜和参数中指定的时间值之间的秒数的写法：

SELECT TO_CHAR(SYSDATE ， 'SSSSS') FROM DUAL ；

-- 返回参数中一年的第几周的写法：

SELECT TO_CHAR(SYSDATE ， 'WW') FROM DUAL ；

30 . ROWNUM

-- 按设定排序的行的序号

SELECT * FROM emp WHERE ROWNUM < 10 ；

31. 如何查找重复记录？

SELECT * FROM TABLE_NAME WHERE ROWID!=(

SELECT MAX(ROWID)

FROM TABLE_NAME D

WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2

);

32 . 如何删除重复记录？

DELETE FROM TABLE_NAME WHERE ROWID!=(

SELECT MAX(ROWID)

FROM TABLE_NAME D

WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2

);

33 . 赋予权限

GRANT

CONNECT,

RESOURCE,

--DBA,

--unlimited tablespace,

CREATE SESSION,

CREATE ANY SEQUENCE,

CREATE ANY TABLE,

CREATE ANY VIEW ,

CREATE ANY INDEX,

CREATE ANY PROCEDURE,

CREATE ANY DIRECTORY,

ALTER SESSION,

ALTER ANY SEQUENCE,

ALTER ANY TABLE,

--ALTER ANY VIEW , --不能修改视图

ALTER ANY INDEX,

ALTER ANY PROCEDURE,

--ALTER ANY DIRECTORY, --不能修改目录

--DROP SESSION, --不能删除Session

DROP ANY SEQUENCE,

DROP ANY TABLE,

DROP ANY VIEW ,

DROP ANY INDEX,

DROP ANY PROCEDURE,

DROP ANY DIRECTORY,

SELECT ANY TABLE,

SELECT ANY DICTIONARY,

INSERT ANY TABLE,

UPDATE ANY TABLE,

DELETE ANY TABLE,

DEBUG ANY PROCEDURE,

DEBUG CONNECT SESSION,

exp_full_database,

imp_full_database

TO user;

34 . 导出视图数据

create table v_table as( select * from view_table);

35 . 解决Temp01.dbf不断变大的问题

第一步：

alter database tempfile 'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' drop;

第二步：

alter tablespace temp add tempfile

'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF'

size 2048M reuse autoextend on next 100M;

第三步：

select d.file_name, d.file_id, d.tablespace_name, d.bytes

from dba_temp_files d;

第四步：

alter database tempfile 'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' autoextend off;

36 . 行转列的多记录合并

name

1 . 使用 WM_CONCAT 函数

SQL> select wmsys.wm_concat(distinct name) from t;

SQL> select id,wmsys.wm_concat(distinct name) con_name from t group by id;

ID CON_NAME

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

1 a,b,c

2 aa,bb

2 . 使用 STRAGG

SQL> SELECT TRIM(',' FROM SYS.STRAGG(NAME||NVL2(NAME,',',''))) CON_NAME FROM T;

CON_NAME

----------

a,b,c,aa,bb

3 . 使用 partition by

select id,sys_connect_by_path(name,'>') con_name

from ( select id,name,row_number() over( partition by id order by name) rn from (select id,name from t )) t

start

with t.rn=1

connect by

t.id=prior t.id and t.rn-1=prior t.rn

ID CON_NAME

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

1 >a

1 >a>b

1 >a>b>c

2 >aa

2 >aa>bb

2 >aa>bb>china

2 >aa>bb>china>china

select id,substr(max(sys_connect_by_path(name,'>')),2) con_name

from (select id,name,row_number() over(partition by id order by name) rn from (select id,name from t ))t

start with t.rn=1

connect by t.id=prior t.id and t.rn-1=prior t.rn

group by id

ID CON_NAME

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

1 a>b>c

2 aa>bb>china>china

4 . 使用 XMLAGG 函数

RTRIM(XMLAGG(XMLPARSE(CONTENT field || ',' WELLFORMED)) .GETCLOBVAL(), ',')

5 . 自定义类型 VARCHAR2

-- create function of strcat

CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type;

-- create type define of strcat_type

create or replace type strcat_type as object

(

currentstr varchar2(4000),

currentseprator varchar2(8),

static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number,

member function ODCIAggregateIterate(self IN OUT strcat_type,value IN VARCHAR2) return number,

member function ODCIAggregateTerminate(self IN strcat_type,returnValue OUT VARCHAR2, flags IN number) return number,

member function ODCIAggregateMerge(self IN OUT strcat_type,ctx2 IN strcat_type) return number

)

-- create type body of strcat_type

create or replace type body strcat_type is

static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is

begin

sctx := strcat_type('',',');

return ODCIConst.Success;

end;

member function ODCIAggregateIterate(self IN OUT strcat_type, value IN VARCHAR2) return number is

begin

if self.currentstr is null then

self.currentstr := value;

else

self.currentstr := self.currentstr ||currentseprator || value;

end if;

return ODCIConst.Success;

end;

member function ODCIAggregateTerminate(self IN strcat_type, returnValue OUT VARCHAR2, flags IN number) return number is

begin

returnValue := self.currentstr;

return ODCIConst.Success;

end;

member function ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN strcat_type) return number is

begin

if ctx2.currentstr is null then

self.currentstr := self.currentstr;

elsif self.currentstr is null then

self.currentstr := ctx2.currentstr;

else

self.currentstr := self.currentstr || currentseprator || ctx2.currentstr;

end if;

return ODCIConst.Success;

end;

6 . 自定义类型 clob

-- create function of strcat

CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN clob PARALLEL_ENABLE AGGREGATE USING strcat_type;

-- create type define of strcat_type

create or replace type strcat_type as object

(

CURR_STR clob,

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT strcat_type) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT strcat_type, P1 IN VARCHAR2) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN strcat_type, RETURNVALUE OUT clob, FLAGS IN NUMBER) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT strcat_type, SCTX2 IN strcat_type) RETURN NUMBER

)

-- create type body of strcat_type

create or replace type body strcat_type is

static function ODCIAggregateInitialize(sctx IN OUT strcat_type)

return number is

BEGIN

SCTX := strcat_type(NULL);

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT strcat_type,P1 IN VARCHAR2) RETURN NUMBER IS

BEGIN

IF (CURR_STR IS NOT NULL) THEN

CURR_STR := CURR_STR || ',' || P1;

ELSE

CURR_STR := P1;

END IF;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN strcat_type, RETURNVALUE OUT clob, FLAGS IN NUMBER) RETURN NUMBER IS

BEGIN

RETURNVALUE := CURR_STR;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT strcat_type, SCTX2 IN strcat_type) RETURN NUMBER IS

BEGIN

IF (SCTX2.CURR_STR IS NOT NULL) THEN

SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;

END IF;

RETURN ODCICONST.SUCCESS;

END;

37 . 去掉重复字符串

/****************************************************

** Oracle去掉重复字符串

** 函数名称：RemoveSameStr

** 参数：【名称】【类型】【说明】

** oldStr varchar2 要处理的字符串

** sign varchar2 字符串分隔符

** 返回值： result varchar2 不包含重复子串的记录

****************************************************/

create or replace function rm_same_str(oldStr clob, sign varchar2)

return varchar2 is

str clob;

currentIndex number;

startIndex number;

endIndex number;

type str_type is table of varchar2(1000) index by binary_integer;

arr str_type;

Result varchar2(4000);

begin

-- null string

if oldStr is null then

return('');

end if;

--string is too long

if length(oldStr) > 32000 then

return(oldStr);

end if;

str := oldStr;

currentIndex := 0;

startIndex := 0;

loop

currentIndex := currentIndex + 1;

endIndex := instr(str, sign, 1, currentIndex);

if (endIndex <= 0) then

exit;

end if;

arr(currentIndex) :=substr(str,startIndex + 1, endIndex - startIndex - 1);

-- arr(currentIndex) := trim(substr(str,startIndex + 1,endIndex - startIndex - 1));

startIndex := endIndex;

end loop;

--get the last string

arr(currentIndex) := substr(str, startIndex + 1, length(str));

--remove the same string

for i in 1 .. currentIndex - 1 loop

for j in i + 1 .. currentIndex loop

if arr(i) = arr(j) then

arr(j) := '';

end if;

end loop;

str := '';

for i in 1 .. currentIndex loop

if arr(i) is not null then

str := str || sign || arr(i);

--set arrar to null:

arr(i) := '';

end if;

end loop;

--remove the prev mark

Result := substr(str, 2, length(str));

return(Result);

end rm_same_str;

38 . 主要函数

INITCAP(CHAR)：将字符串 CHAR 的第一个字符为大写,其余为小写。

SELECT INITCAP('ABCDE') FROM DUAL ;

LENGTH(CHAR) : 取一字符串 CHAR 的长度。

SELECT LENGTH('ABCDE') FROM DUAL ;

LOWER(CHAR) ：将字符串 CHAR 全部变为小写。

SELECT LOWER('ABCDE') FROM DUAL ;

LPAD(CHAR1 , N , CHAR2) ：用字符串 CHAR2 包括的字符左填 CHAR1 ,使其长度为 N 。

SELECT LPAD('ABCDEFG' , 10'123') FROM DUAL ;

-- 结果: '123ABCDEFG'

LTRIM(CHAR , SET) ：从字符串 CHAR 的左边移去字符串 SET 中的字符,直到第一个不是 SET 中的字符为

SELECT ('CDEFG' , 'CD') FROM DUAL ;

-- 结果: 'EFG'

NLS_INITCAP(CHAR)：取字符 CHAR 的第一个字符大写,其余字符为小写。

SELECT NLS_INITCAP('ABCDE') FROM DUAL ;

NLS_LOWER(CHAR)：将字符串 CHAR 包括的字符全部小写。

SELECT NLS_LOWER('AAAA') FROM DUAL ;

NLS_UPPER(CHAR)：将字符串 CHAR 包括的字符全部大写。

SELECT NLS_UPPER('AAAA') FROM DUAL ;

REPLACE(CHAR1 , CHAR2 , CHAR3)：用字符串 CHAR3 代替每一个列值为 CHAR2 的列,其结果放在 CHAR1 中。

SELECT REPLACE(EMP_NO , '123' , '456') FROM DUAL ;

RPAD(CHAR1 , N , CHAR2)：用字符串 CHAR2 右填字符串 CHAR1 ,使其长度为 N 。

SELECT RPAD('234' , 8 , '0') FROM DUAL ;

RTRIM(CHAR , SET)：移去字符串 CHAR 右边的字符串 SET 中的字符,直到最后一个不是 SET 中的字符为止。

SELECT RTRIM('ABCDE' , 'DE') FROM DUAL ;

SUBSTR(CHAR , M , N)：得到字符串 CHAR 从 M 处开始的 N 个字符 . 双字节字符,一个汉字为一个字符的。

SELECT SUBSTR('ABCDE' , 2 , 3) FROM DUAL ;

SUBSTRB(CHAR , M , N)：得到字符串 CHAR 从 M 处开始的 N 个字符 . 双字节字符,一个汉字为二个字符的。

SELECT SUBSTRB('ABCDE' , 2 , 3) FROM DUAL ;

TRANSLATE(CHAR1 , CHAR2 , CHAR3)：将 CHAR1 中的 CHAR2 的部分用 CHAR3 代替。

SELECT TRANSLATE('ABCDEFGH' , 'DE' , 'MN') FROM DUAL ;

UPPER(CHAR)：将字符串 CHAR 全部为大写。

ADD_MONTHS(D , N)：将 N 个月增加到 D 日期。

SELECT ADD_MONTHS(SYSDATE , 5) FROM DUAL ;

LAST_DAY(D)：得到包含 D 日期的月份的最后的一天的日期。

SELECT LAST_DAY(SYSDATE) FROM DUAL ;

MONTH_BETWEEN(D1 , D2)：得到两个日期之间的月数。

SELECT MONTH_BETWEEN(D1 , D2) FROM DUAL ;

NEXT_DAY(D , CHAR)：得到比日期 D 晚的由 CHAR 命名的第一个周日的日期。

SELECT NEXT_DAY(TO_DATE('2003/09/20') , 'SATDAY') FROM DUAL ;

ROUNT(D , FMT)：得到按指定的模式 FMT 舍入到的最进的日期。

SELECT ROUNT('2003/09/20' , MONTH) FROM DUAL ;

SYSDATE：得到当前系统的日期和时间。

SELECT SYSDATE FROM DUAL ;

TO_CHAR(D , FMT)：将日期 D 转换为 FMT 的字符串。

SELECT TO_CHAR(SYSDATE , 'YYYY/MM/DD') FROM DUAL ;

TO_DATE(CHAR , FMT)：将字符串 CHAR 按 FMT 的格式转换为日期。

SELECT TO_DATE('2003/09/20' , 'YYYY/MM/DD') FROM DUAL ;

ABS(N)：得到 N 的绝对值。

SELECT ABS(-6) FROM DUAL ;

EXP(N)：得到 N 的 E 的 N 次幂。

SELECT EXP(1) FROM DUAL ;

MOD(M , N)：得到 M 除以 N 的余数。

SELECT MOD(100 , 7) FROM DUAL ;

POWER(M , N)：得到 M 的 N 幂。

SELECT POWER(4 , 3) FROM DUAL ;

ROUND(N , M)：将 N 舍入到小数点后 M 位。

SELECT (78.87653 , 2) FROM DUAL ;

SIGN(N)：当 N<0 时,得到 -1 ;当 N>0 时,得到 1 ;当 N=0 时,得到 0 ;

SELECT SIGN(99) FROM DUAL ;

TRUNC(N , M)：得到在 M 位截断的 N 的值。

SELECT TRUNC(7.7788 , 2) FROM DUAL ;

COUNT()：计算满足条件的记录数。

SELECT COUNT(*) FROM TABLE1 WHERE COL1='AAA' ;

MAX()：对指定的列求最大值。

SELECT MAX(COL1) FROM TABLE1 ;

MIN()：对指定的列求最小值。

SELECT MIN(COL1) FROM TABLE1 ;

AVG()：对指定的列求平均值。

SELECT AVG(COL1) FROM TABLE1 ;

SUM()：计算列的和。

SELECT SUM(COL1) FROM DUAL ;

TO_NUMBER(CHAR)：将字符转换为数值。

SELECT TO_NUMBER('999') FROM DUAL ;

empty_b|clob()：返回一个空的 LOB 定位符 , 用在初始化 LOB 变量 , 或用在 INSERT 及 UPDATE 声明去初始化

LOB 列或将其属性置为空。

INSERT INTO TABLE1 VALUES(EMPTY_BLOB()) ;

UPDATE TABLE1 SET CLOB_COL=EMPTY_BLOB() ;

NVL(EXPR1 , EXPR2)：若 EXPR1 是 NULL ,则返回 EXPR2 ,否则返回 EXPR1 。

SELECT NAME , NVL(TO_CHAR(COMM) , 'NOT APPLICATION') FROM TABLE1 ;

39. CASE WHEN 和 DECODE

1 . case表达式

--简单Case函数

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他'

END

--Case搜索函数

CASE

WHEN sex = '1' THEN '男'

WHEN sex = '2' THEN '女'

ELSE '其他'

END

2 . CASE WHEN 在语句中不同位置的用法

2.1 SELECT 用法

SELECT

grade,

COUNT ( CASE WHEN sex = 1 THEN 1 ELSE NULL END) 男生数,

COUNT ( CASE WHEN sex = 2 THEN 1 ELSE NULL END) 女生数

FROM

students

GROUP BY

grade

2.2 WHERE 用法

SELECT

T2.*, T1.*

FROM

T1, T2

WHERE (

CASE

WHEN T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%' THEN 1

WHEN T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%' THEN 1

ELSE 0

END ) = 1

2.3 GROUP BY 用法

SELECT

CASE

WHEN salary <= 500 THEN '1' WHEN salary > 500 AND salary <= 600 THEN '2'

WHEN salary > 600 AND salary <= 800 THEN '3'

WHEN salary > 800 AND salary <= 1000 THEN '4'

ELSE NULL

END salary_class, -- 别名命名

COUNT(*)

FROM

GROUP BY

CASE

WHEN salary <= 500 THEN '1'

WHEN salary > 500 AND salary <= 600 THEN '2'

WHEN salary > 600 AND salary <= 800 THEN '3'

WHEN salary > 800 AND salary <= 1000 THEN '4'

ELSE NULL

END;

3 . DECODE() 函数

基本语法： decode(条件, 值1, 返回值1, 值2, 返回值2, ..., 值n, 返回值n, 缺省值)

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') from employees;

40. 修改clob字段为varchar2

alter table tablename add (new_column varchar2(4000));

update tablename set new_column=dbms_lob.substr(old_column,1000,1);

alter table tablename drop column old_column;

alter table tablename rename column new_column to old_column;

41. 使用正则表达式查询

--非正整数

select 字段 from 表 where regexp_replace(字段,'\d','') is not null;

--非数值类型

select 字段 from 表 where regexp_replace(字段,'^[-\+]?\d+(\.\d+)?$','') is not null;

--自定义函数,判断非值类型

create or replace function isnumber(col varchar2) return i number;

begin

i := to_number(col);

return 1;

exception

when others then

return 0;

end;

select 字段 from 表 where isnumber(字段)=0;

42. 判断某個字段的值是不是数字

共有三种方法，分别是使用to_number()、regexp_like()和translate()三种函数来进行匹配。如果字段的值是数字，返回1，否则返回0，具体的实现如下，三种方法任选壹种即可。运行结果：如果字符串是数字格式则返回1，不是则返回0。

--1、利用 to_number

CREATE OR REPLACE FUNCTION isnumeric(str IN VARCHAR2)

RETURN NUMBER

v_str FLOAT;

BEGIN

IF str IS NULL

THEN

RETURN 0;

ELSE

BEGIN

SELECT TO_NUMBER (str)

INTO v_str

FROM DUAL;

EXCEPTION

WHEN INVALID_NUMBER

THEN

RETURN 0;

END;

RETURN 1;

END IF;

END isnumeric;

--2、利用 regexp_like

CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)

RETURN NUMBER

BEGIN

IF str IS NULL

THEN

RETURN 0;

ELSE

IF regexp_like (str, '^(-{0,1}+{0,1})[0-9]+(.{0,1}[0-9]+)$')

THEN

RETURN 1;

ELSE

RETURN 0;

END IF;

END isnumeric;

--3、利用 translate

CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)

RETURN NUMBER

v_str VARCHAR2 (1000);

BEGIN

IF str IS NULL

THEN

RETURN 0;

ELSE

v_str := translate(str, '.0123456789', '.');

IF v_str = '.' OR v_str = '+.' OR v_str = '-.' OR v_str IS NULL

THEN

RETURN 1;

ELSE

RETURN 0;

END IF;

END isnumeric;

由于 Oracle 数据库本身没有提供检测字段是否为数字的方法，因此我们需要自己创建壹個 isnumeric() 的函数，然后再调用它来进行判断。具体的调用方式如下：

SQL> select isnumeric('123a') from dual;

ISNUMERIC('123A')

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

SQL> select isnumeric('123.509') from dual;

ISNUMERIC('123.509')

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

SQL> select isnumeric('123.205.10.8') from dual;

ISNUMERIC('123.205.10.8')

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

43. 重复记录查询

1、查找表中多余的重复记录，重复记录是根据单个字段 xxxId 来判断

select * from

xxx_table

where

xxxId in (select xxxId from xxx_table group by xxxId having count(xxxId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段 xxxId 来判断，只留有rowid最小的记录

delete from

xxx_table

where

xxxId in (select xxxId from xxx_table group by xxxId having count(xxxId) > 1) and

rowid not in (select min(rowid) from xxx_table group by xxxId having count(xxxId)>1)

3、查找表中多余的重复记录(多个字段)

select * from

xxx_table a

where

(a.xxxIdA,a.xxxIdB) in (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count(*) > 1)

4、删除表中多余的重复记录(多个字段)，只留有rowid最小的记录

delete from xxx_table a

where (a.xxxIdA,a.xxxIdB) in (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count(*) > 1)

and rowid not in (select min(rowid) from xxx_table group by xxxIdA,xxxIdB having count(*)>1)

5、查找表中多余的重复记录(多个字段)，不包含rowid最小的记录

select * from

xxx_table a

where

(a.xxxIdA,a.xxxIdB) in (select xxxIdA,xxxIdB from xxx_table group by xxxIdA,xxxIdB having count(*) > 1) and

rowid not in (select min(rowid) from xxx_table group by xxxIdA,xxxIdB having count(*)>1)

范奕信

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle 值等于n a,ORACLE编程详解

0 . 查看系统表空间select * from dba_tablespaces1 . 查看表结构desc table_name2 . 查询数据库SIDselect name from v$database;3 . 用 B 表的数据更新 A 表数据 ( 有关联的字段 )UPDATE A SET name=(SELECT name FROM B WHEREA.id=B.id) WHERE B.id ...
复制链接

扫一扫