oracle sql计算百分比,Oracle sql百分比问题 - oracle 使用笔记

Oracle  sql百分比问题

问题:需要对在Oracle数据库中查询出来的数值进行比率的计算。类似于如下的SQL：

Select Round((discount/Amount),2) from dual;

这样算出来的数字可以满足绝大部分的需求。但是对于不到1的数字，问题出来了......显示的类似于.45样子的数据。0.45前面的0不见了。

以下总结了两种方法来解决缺0的问题。

第一种解决方法：

SELECT DECODE (TRUNC (1/100),0, REPLACE (1/100, '.', '0.'),TO_CHAR (1/100))

FROM DUAL;

大家只要在1/100的地方替换成自己需要的百分数就可以了。

第二种解决方法：

SELECT RTrim(To_Char(1/100,'FM99999999990.9999'),'.') FROM dual;

替换方式和上面相同。

两种方式的详细比较：

第一种公式比较复杂，但可以处理通用的数值。

第二种看起来简单，不过格式化的值的位数一定要大于计算的位数才行。比如SELECT RTrim(To_Char(200,'FM90.9999') ,'.') FROM dual;显示的就是########,因为已经超过了格式化的范围

Oracle截取小数点后两位四舍五入

cast(colas dec(18,2))

Oracle强行删除表

oracle :--------正常情况下，需要在删除book表的前提下，才能删除父表person表。但是可以通过以

上关键字，强行删除person表，如下

drop table person cascade constraint;

Oracle日期格式

24-12月-10 10.28.18.578000 上午    TIMESTAMP(6)

2011-1-13      Date

Oracle给用户解锁

用sys以sysdba的身份来进行登录,在sys状态是locked的时候也是能登录的.然后再使用

alter user sys account unlock来进行解锁

sys登陆sqlplus / as sysdba

system/igit

alte r user  system(user_name)  account unlock;

commit;

alter user zhangdianliang account unlock;

commit;

Oracle返回唯一的值函数

select distinct(c.contactid) as contactId,

Oracle模糊匹配函数

instr("efabcdefg","e")的结果当然是1

instr(2，"efabcdefg","e")的结果就是7

instr(5，"efabcdefg","a")的结果就是0

instr("efabcdefg","k")的结果也是0

if (StringUtils.isNotBlank(fullQueryString)) {

sql += " and (instr(b.USERNAME, '" + fullQueryString

+ "')>0 or instr(a.POSTPONEDCAUSE, '" + fullQueryString

+ "')>0 or instr(c.SERIALNUMBER,'" + fullQueryString

+ "')>0)";

}

Oracle判断函数

Distinct 去除重复select distinct(id) from dual;

Oracle的Decode函数进行多值判断

decode  (expression, search_1, result_1)

-- expression查询结果和search_1匹配，如果相同，返回result_1，如果不同，返回空值

decode  (expression, search_1, result_1, search_2, result_2)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

decode  (expression, search_1, result_1, default)

-- expression查询结果和search_1匹配，如果相同，返回result_1

如果不同，返回default(可自定义结果)

decode  (expression, search_1, result_1, search_2, result_2, default)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；

如果未定义default值，则返回空值。以下是一个简单测试，用于说明Decode函数的用法:

SQL> create table t as select username,default_tablespace,

lock_date from dba_users;

Table created.

SQL> select * from t;

USERNAME

DEFAULT_TABLESPACE             LOCK_DATE

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

SYS                            SYSTEM

SYSTEM                         SYSTEM

OUTLN                          SYSTEM

CSMIG                          SYSTEM

SCOTT                          SYSTEM

EYGLE                          USERS

DBSNMP                         SYSTEM

WMSYS

SYSTEM                         20-OCT-04

8 rows selected.

SQL> select username,

decode(lock_date,null,'unlocked','locked') status from t;

USERNAME                       STATUS

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

SYS                            unlocked

SYSTEM                         unlocked

OUTLN                          unlocked

CSMIG                          unlocked

SCOTT                          unlocked

EYGLE                          unlocked

DBSNMP                         unlocked

WMSYS                          locked

8 rows selected.

SQL> select username,decode(lock_date,null,'unlocked') status from t;

USERNAME                       STATUS

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

SYS                            unlocked

SYSTEM                         unlocked

OUTLN                          unlocked

CSMIG                          unlocked

SCOTT                          unlocked

EYGLE                          unlocked

DBSNMP                         unlocked

WMSYS

8 rows selected.

Oracle截取日期的年月

extract(year from compltime(字段)) || '年' || extract(MONTH from compltime) || '月'

group by extract(year from compltime),extract(MONTH from compltime)

Oracle日期比较大小

sql+="andrinfo.reportTime<=to_date('"+endProvidedTimeTime+"','yyyy-mm-dd hh24:mi:ss')";

TO_CHAR(compltime,'YYYY')  TO_CHAR(compltime,'MM')

Oracle表空间

1.查看某库下所有表空间名字

select distinct TABLESPACE_NAME from tabs

2.查看某库下有几个表空间

select count(distinct TABLESPACE_NAME) from tabs

3.创建空间

createtablespacetest datafile'c:\oracle\oradata\orcl9\test.dbf'

size50M defaultstorage (initial 500KNext500K  minextents 1

maxextents unlimited  pctincrease 0);

创建用户

create userlxg identifiedbylxgdefaulttablespacetest;

授权

grantresource,connect,dbatotest;

4.增加表空间

altertablespace chinawateradddatafile'c:\oracle\oradata\orcl9\ADDCHINAWATER.dbf' size200M

5.删除表空间

drop tablespace "空间名" including contents and datafiles

6.下面SQL用来查看表空间总的大小，可用空间大小，占用百分比

select  fs.tablespace_name  "Tablespace", (df.totalspace-fs.freespace)   "Used MB",

fs.freespace  "Free MB", df.totalspace "Total MB", round(100*(fs.freespace/df.totalspace)) "Pct Free" from

(select tablespace_name, round(sum(bytes)/1048576) TotalSpace  from  dba_data_files

group by

tablespace_name) df, (select  tablespace_name, round(sum(bytes)/1048576) FreeSpace

from  dba_free_space  group by  tablespace_name)  fs

where

df.tablespace_name=fs.tablespace_name ;

注：60M的备份文件，导入前建表空间的话，给到300M足够用了，当然是要把数据文件的AUTOEXTENSIBLE打开的。

新建数据库

1.创建数据库

在oracle服务端的 配置和移植工具 里面，有 Database Configration Assistant 项 ， 选中 就可以新建数据库了---建完数据库关闭监听服务看这个文件 F:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora

listener.ora文件监听改为本机ip (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.104)(PORT = 1521))

)

2.Plsql连接数据库：

F:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora文件里添加

172.24.10.27 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.10.27)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = xzts)

)

)

3.手动建立数据库

Oracle的版本

进入oracle命令：

第一步，cmd进入dos命令界面，输入sqlplus /nolog；此时已经可以看见oracle的版本

如果还要查看其他的信息输入以下步骤。

第二步，conn用户名/密码@实例名

第三步，select * from v$instance查看当前版本以及其他信息

Oracle添加监听

打开cmd输入netca。

F:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora文件直接添加

Oracle查询tree的SQL

String sql =

" select orgid from t_orgmanage_organization

start with  orgid ="+ orgId +"

connect by  prior orgid = parentid";

测试Oracle能否连上

命令: tnsping orcl

ORACLE数据导入导出dmp文件

1.倒入

imp username/password@SID file=XXX.dmp fromuser=(XXX,XXX)(fromuse指对方数据库用户名) touser=(XXX,XXX)(touser指你的数据库的用户名) tables=(XXX,XXX) ignore=y

2.倒出

exp username/password@SID file=d:\daochu.dmp full=y

注：tables :如果倒入全部表，不需要tables参数。

SID：通常是数据库名称，注册中：HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE下面，有一个 ORACLE_SID。Regedit，@SID可加，可不加 SID是数据库名或服务名

(1)将数据库中system用户与sys用户的表导出

exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)

(2)将数据库中的表inner_notify、notify_staff_relat导出

exp aichannel/aichannel@TESTDB2    file=d:\data\newsmgnt.dmp   tables=(inner_notify,notify_staff_relat)

(3)将数据库中的表table1中的字段filed1以"00"打头的数据导出

exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

(4)压缩

上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。

也可以在上面命令后面 加上 compress=y 来实现。

创建用户

create user 你的用户名 IDENTIFIED BY 你的密码

删除用户

drop user “1xg”cascade

修改用户密码

Alter user userNameidentified  by password;

给用户授权

grant dba to lxg;--授予DBA权限

grantunlimited tablespacetolxg;--授予不限制的表空间

grant select any table tolxg;--授予查询任何表

grant select anydictionarytolxg;--授予 查询 任何字典

连接字符

concat(concat('%',ddv.value),'%') "