1.删除重复项, 只保留第一条
delete
2.查找重复记录
select t1.ID, count(*) from table
3.导入数据时, 如果目标表存在主键且主键在源表中没有对应的字段时, 使用触发器+序列
create or replace trigger res_circuit_uuid_insert
begin
end ;
4.导入数据(目标表已存在, 目标表没有主键或主键约束禁用)
insert into
select distinct
from table1 t1;
5.通过查询直接生成一个新表(结构+数据)
create table new_table
as select * from old_table
nologging
6.通过查询直接生成一个新表(复制表结构)
(1).复制所有字段
create table new_table
as select * from old_table
where 1=2;
(2).复制部分字段
create table new_table
as select col1, col2
from old_table
nologging;
7.修改表结构
(1). 添加列
alter table tableName
ADD
( col1 col_type,
);
(2). 修改列
alter table tableName
MODIFY
(col1 new_col_type, --更改为新类型
);
(3).删除列
alter table department
DROP
(col1, col2...)
cascade constraints;
(4).软删除(标记为 unused 状态);
alter table tableName
set UNUSED
(col1, col2)
cascade constraints;
(5).彻底删除
alter table tableName
drop unused columns;
8.删除表
(1).删除所有记录(适用于记录数量小的表)
delete from tableName;
(2).drop table tableName
create table new_table
as select * from old_table;
(3).(适用于大量记录的表)
truncate table tableName;
9.表重命名
rename old_tableName
to new_tableName
10.添加注释
(1). 给表添加注释
comment on table tableName
is
'这是一个新表...'
(2). 给列添加注释
comment on COLUMN
tableName.columnName is
'这是一个列'
11.字典表
<1>.用户和概要文件相关的数据字典视图
(1).dba_users
--DBA视图描述数据库中的所有用户
select * from dba_users db
db.username --用户名
db.user_id --用户ID
db.password --口令(加密)
db.account_status --账户状态:OPNE || EXPIRED || LOCKED
db.expiry_date --终止日期
db.default_tablespace --表空间
db.created --创建日期
(2).all_users
--all视图列出当前用户可以看见的用户,
--但不予描述, 即只列出 UserName, User_ID, Created(date)
select * from all_users
(3).user_users
--仅仅列出当前用户的信息
select * from user_users
(4).dba_profiles
--显示所有的概要文件的资源参数
--pro.profile: 概要文件名称
--pro.resource_name: 资源名称:如 password_grace_time
--pro.resource_type: 资源类型:如:password
--pro.limit: 是否限制
select * from dba_profiles pro
(5).user_password_limits
--通过概要文件为用户设置的口令资源参数视图
--resusece_name: 资源名称:failed_login_attempts
--limit:限制状态
select * from user_password_limits
<2>.权限与角色字典视图
(1).dba_col_privs
--DBA视图描述了数据库中的所有列对象的授权
select * from dba_col_privs pri
pri.grantee --用户或角色(因为权限可以直接赋予用户)
pri.owner --所有者
pri.table_name --(列所属表名称)
pri.column_name --列
pri.grantor --
pri.privilege --权限
pri.grantable
(2).user_col_privs
--当前用户的所有列对象的授权
(3).dba_tab_privs
--DBA视图描述了数据库中的所有对象的(非系统的)授权
select * from dba_tab_privs pr
pr.grantee --用户或角色(被授权者)
pr.owner --使用者
pr.table_name --表
pr.grantor --授权者
pr.privilege --权限
pr.grantable --是否table对象
(4).user_tab_privs
--当前用户被授予的所有 对象的(非系统)授权
--对象权限包括:privilege:select, delete, update....
select * from user_tab_privs
(5).user_sys_privs
--当前用户的所有系统权限
select * from user_sys_privs
--系统权限包括:
--: create role,rule, type, user, view, table, cluster, trigger
--: create library, session, synonym, trigger, sequence, procedure.
select * from user_sys_privs
(6).dba_sys_privs
--dba的系统权限
select * from dba_sys_privs
(7).user_role_privs
--授予给当前用户的角色
--userName: 用户名
--granted_role: 被授予的角色
--admin_option: 此用户下的用户是否可以被继承此用户的角色
(8).role_sys_privs
--授予给角色的系统权限的信息
--role : name
--privilege: 权限
--amdin_option: 是否可以被继承
(9).role_tab_privs
--授予给角色的对象的(table)权限
--role:Name
--owner: user
--table_name:tableName
--column_name:
--privilege:alter, debuge, insert;;;;;
(10).session_privs
--用户当前启用的权限
(11).session_roles
--用户当前启用的角色
<3>.其他字典表
(1). 注释表
user_tab_comments
(2).软删除表
user_unused_col_tabs;
12.添加约束
(1).Not NULL
Alter table tableName
Modify column Not null;
相当于是更改列定义,故为 Modify
(2).Unique
Alter table tableName
Add Unique(col1, col2);
一个列只能定义一个唯一约束(名称)
(3).Check
Alter table tableName Add Constraint Check_Col1
Check(col1 betweetn 12 to 34);
不适当的数据类型上定义约束条件(如字符类型上定义数字范围)会报错
(4).Foreign Key
Alter table tableName Add Constraint new_FK
Foreing Key (col1) References
tableName2 (col2) on
Delete Set NULL;
说明: a: Delete Cascade: 删除子表中所有相关的记录
13.激活,禁用, 验证约束
(1).激活 CHK_SEX 约束
Alter table tableName
Enable constraint to CHK_SEX;
(2).激活 Unique 约束
Alter table tableName
Enable Unique (col1);
(3).禁用约束 CHK_SEX
Alter table tableName
Disable constraint CHK_SEX;
(4).验证约束
Alter Table tableName
Enable Validate constraint CHK_SEX;
(5).非验证约束
Alter table tableName
Enable NoValidate constraint CHK_SEX;
14.删除约束
(1).删除名称 Chk_Sex约束
Alter table tableName Drop Contraint Chk_Sex;
(2).删除 Unique 约束
Alter table tableName drop unique (col1);
(3).删除外键 约束
Alter table tableName Drop primary key cascade;
说明: 加入 casecade表示删除主键时同时会删除 子表中的外键约束
(4).删除 Not NULL
Alter Table tableName modify col1 NULL;
15. 修改约束名
(1).alter table tableName rename constraint check_sex to chk_sex;
16.延迟执行约束
原因:有时必须得延迟某个约束
比如: 如果某个表有外键约束(子表), 当向此表插入一条数据时, 而此记录数据还没有相应的父表中插入;
因为约束是默认是立即执行的, 这时通不过外键约束, 插入到子表中的记录会被回退.
但是如果延迟些外键约束, 把给子表和父表插入记录的过程放在在一个事务中,
用户可以随便颠倒父子表的次序的插入记录
当提交事务时再执行外键约束.
e.g
alter table tableName
modify constraint FK_tableName
Initially Deferred;
SQL>Insert Into tablName (xx, xx) values (xx, xx);
SQL>Insert Into tableName2(xx, xx) values (xx, xx);
SQL>Commit;
OK!
17.前 n 行
where rownum <= num
18.触发器
create or replace trigger tr_emp_sal_comm
before update of sal, comm
or delete
on emp
for each row
when( old.job='salesman') --指定条件: 此触发器只针对销售部门有效
begin
end;
19.过程
(1).定义过程
create or replace procedure query_emp
(v_no emp.impno%TYPE,
)
IS
e_sal_error exception;
begin
select ename, sal --给变量赋值
into
from emp where empon=v_no;
if v_sal <= 2500 then
DBMS_OUTPUT.PUT_LINE('该员工工资: '|| v_sal);
Raise e_sal_error;
end if;
exception --抛出异常
when no_data_found then
when e_sal_error then
dbms_output.put_line('该员工工资高于 2500了');
end query_emp;
(2).调用过程
set serveroutput on --开启输出
declare
v_a1 emp.ename%TYPE;
v_a2 emp.sal%TYPE;
BEGIN
--调用存储过程;
-- => 表示按名称 对应关系;注: 后面必须要有分号
query_emp(v_name => v_a1, v_sal => v_a2, v_no => 5678);
if v_a2 > 2500
end if;
End;
20.函数
(1)创建函数
函数与过程的区别
1.函数必须要有 return;
2.函数更侧重于数据相关;过程更侧重于常用操作相关;
create or replace function get_salary_by_deptno
(v_dept_no in emp_deptno%TYPE, --输入部门号
return number
IS
v_sum number(10, 2); --返回指定部门的工资总和
begin
end get_salary_by_deptno;
(2)调用函数
set serveroutput on
declare
v_a1 emp.deptno%TYPE;
v_a2 number;
v_sum number(10,2);
begin
v_sum := get_salary_by_deptno
(v_emp_cnt => v_a2, v_dept_no => 10);
if v_a2 =0 then --可直接使用 v_a2; 因为它是函数输出的;
else
end if;
end;
21. Oracle客户端用户管理文件
(Oracle客户端安装目录)
C:\oracle\ora92\network\ADMIN\tnsnames
22.创建序列
create sequence SEQ_TABLE_ID
minvalue 1
maxvalue 999999
start with 10001
increment by 1
cache 20;
23.游标应用
create or replace procedure
begin
end curor_check_wrong_cols;
24. 利用 union 结合游标将 表1中的字段1, 字段2插入到 表2字段 1中.
create or replace procedure pro_imp_colName is
begin
end pro_imp_colName;
25.导入数据时屏蔽重复记录(从table1向table2导入数据)
create or replace trigger table2_col1_insert
declare
begin
end ;
--对应的存储过程
create or replace procedure col1_is_null
(colName1 in varchar, intcount out integer )
as
begin
end ;
26. 创建3种类型的索引
创建索引过程:
1.创建索引时会对全表进行扫描
2.对索引列的数据进行排序
3.为索引分配存储空间
4.将索引的定义信息保存到数据字典中.
创建:
<1>.
条件: 如果在 where 条件子句中要经常引用 某列或某几列, 就应该基于这些列创建B树索引.
1.创建单列索引
create INDEX idx_table_col on table(colName)
PCTFREE 30
TABLESPACE tablespace_Index;
说明: pctfree 指定将来 insert 操作预留的空间.
因为插入新记录总会创引入新索引值进来.因此预留空间要求较大.
2.创建多列索引
create INDEX idx_table_col1_col2 on table(col1, col2)
pctfree 30
tablespace tablespace_index;
<2>.创建位图索引
条件: 某列上记录值的基数很小, 只是有限的几个固定值,如性别,婚姻状态等如果需要建立索引,
就应该建立位图索引.
1.单列位图索引
create bitmap index bmidx_table_col on table(colName);
<3>.创建函数索引.
条件: 当 where 子句中使用了函数和表达式时, 为这些函数和表达式建立函数索引后,
select 的速度会提高.
1. 创建函数索引
create Index funidx_col on table(col1+col2+col3*0.5)
查询下列语句时速度会加快:
select * from table where (col1+col2+col3*0.5) < 500;
2.创建函数索引2
create Index funidx_table_col on table Substr(col1, 1, 2)
compute statistics;
查询col1列的头两位时速度会明显加快
select * from table where Substr(col1, 1, 2) in ('12', '34');
27.更改,重命名,合并, 重建
(1).更改:
Alter Index indexName **;
(2).重命名:
Alter Index indexName RENAME TO indexName2;
(3).合并索引: (清除更新产生的磁片)
Alter Index indexName COALESCE;
(4).重建索引:(重新建立一个索引, 删除还来的索引, 但是各种参数不变);
Alter Index indexName REBUILD
TABLESPACE ANOTHER;
28.监视索引
(1).查询 V$object_usage, 了解索引的被监视情况
select index_name, monitoring, used, start_monitoring, end_monitoring
from V$OBJECT_USAGE;
结果:
未选定行
说明没有任何索引被监视.
(2).修改索引使之被监视.
Alter Index indexName MONITORING USAGE;
(3).再查询被监视情况:
结果:
indexName, yes, no, timeNow;
(4).执行查询后(利用索引)
再查询结果:
indexName, yes, yes, timeNow;
可见, usered=yes; 表示从开始监视以来索引已经被使用过了.
29.索引的空间控制
(1).分配索引空间
Alter index indexName ALLOCATE EXTENT(SIZE 100K);
(2).释放多余空间
Alter INDEX indexName DEALLOCATE UNUSED;
30.查看与删除索引
(1).查看
select index_name, index_type, tablespace_name, uniquesness, logging
from user_indexes
where table_name='tableName';
--DBA_INDEXES;
--ALL_INDEXES;
--USER_INDEXES;
--DBA_IND_COLUMNS; --索引列
--ALL_IND_COLUMNS;
--USER_IND_COLUMNS;
(2).删除
drop INDEX indexName;
31.同义词
引入同义词原因:
在开发数据库应用程序时, 应该普遍遵守的规则是尽量避免直接引用表,视图或其他对象.
否则,当DBA改变了表的名称或者结构,就必须重新改变并编译应用程序.
因此需要为对象建立同义词.
建立对象条件:
1.重要的对象;
2.分布式系统;
3.名称长而复杂;
语法:
create [or replace] public Synonym synonym_name
for [schema.]ojbect;
ege:
create or replace SYNONYM syn_students
for students
使用同义词:
select * from syn_students;
insert into syn_students values (**, **, **,..);
32.序列
需求
1.主键, 外键;
2.流水号;
创建:
create Sequence sequence
[start with n1]
[increment by n2]
[{maxvlue n3 | nomaxvalue }] --最大值(如果没有指定最大值则为:1027)
[{minvlue n4 | nominvalue }] --最小值(同上:-1026)
[{cache n5 | nocache}]
[{cycle | nocycle}]
[order]; --按顺序生成序列
ege;
create SEQUENCE Seq_id
Increment by 1
Start with 300
MaxVulue 999999999
MinVule 1
Cache 10
Order;
使用序列:
(1).下一个序列
sequence_name.nextval;
(2).当前序列
sequence_name.currval;
ege:
select * from sequence_name.currval from dual;
更改序列
Alter Sequence sequence
--同创建相同;
删除:
Drop Sequence sequence;
查看所有序列:
select * from user_sequence;
33. 视图
引入视图目的:
(1).提供各种数据表现形式
(2).提供某些数据的安全性
(3).隐藏数据的复杂性
(4).简化数据的查询语句
(5).执行特殊查询
(6).保存复杂查询
<1>.创建视图:
Create [Or Replace] [force]
[(col1, col2, ...)]
As select ...
[With Check Option] [Constraint constraint_name]
[Woth Read Only];
说明:
--force: 强制创建视图,不考虑基础表是否存在或是否有权限, 这样保证操作的独立性;
--With Check Option: 使用视图时,检查涉及的数据是否通过select子查询的约束条件,否则不允许操作。
ege:
创建一般视图
Create View v_table
as
select * from
tableName;
创建连接视图:
Create View v_table
as
select a.col1, a.col2, b.col1, b.col5
from table1 a, table2 b
where a.col1=b.col5
and a.col2 in (values1, values2);
创建复杂视图
Create View v_table
As
select col1, avg(col2), sum(col3), max(col4), min(col5)
from table
group by col1;
<2>.使用视图
1.select
select * from view_name;
2.DML操作
对于基础表为一张表的直接使用 DML语句
对于基础表为多张表的视图使用下列方法:
键保存表概念:
--如果连接视图中的一个基础表的键(主键,唯一键)在它的视图中仍然存在, 则称这个基础表为
原则:
1.一次只能对视图中的一个键保存表进行更新。
2.如果视图有 With Check Option选项
<3>.修改视图:
由于视图只是一个虚表,更改只是更改它的定义。
使用下面方法会重新创建一个新的视图,但会保留原视图上的所有设置(如授权等)
Create or replace view v_table
<4>.删除视图
Drop View view_table
<5>.查看视图字典:
user_views;
34. 口令策略
--创建用于口令的概要文件(口令策略)
--此概要文件为系统自带的口令策略文件
create profile default limit
password_life_time 60
password_grace_time 10
password_reuse_time 1800
password_reuse_max unlimited
password_login_attempts 3
password_lock_time 1
--系统自带的口令校验脚本
password_verify_function verify_function; --口令校验脚本(为一函数,名称为:verify_function)
--创建用户或修改用户时添加口令管理(default)
create user userName Profile default;
alter user userName2 profile default;
--锁定账户
Alter user userName Account Lock;
--解锁账户
Alter user userName Account UnLock;
35. ORACLE函数大全 (SQL中的单记录函数)
一. 字符
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
--------- --------- --------- ---------
2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵 A
3.CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23'
高乾竞电话
----------------
010-88888888转23
4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1
C2
I
J
SQL> select instr('oracle traning','ra',1,2) instring from dual;
---------
6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME
------ ------------ ---------------- ------------ --------- --------------------
高乾竞
7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字符)
RPAD
LPAD
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满
10.LTRIM和RTRIM
LTRIM
RTRIM
SQL> select ltrim(rtrim('
LTRIM(RTRIM('
-------------
gao qian jing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;
SUBSTR('
--------
08888888
12.REPLACE('string','s1','s2')
string
s1
s2
SQL> select replace('he love you','he','i') from dual;
REPLACE('H
----------
i love you
13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
SQL> select xm from table1 where soundex(xm)=soundex('weather');
XM
--------
weather
wether
14.TRIM('s' from 'string')
LEADING
TRAILING
如果不指定,默认为空格符
二.数字处理
15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
--------- ---------
16.ACOS
给出反余弦的值
SQL> select acos(-1) from dual;
---------
3.1415927
17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;
---------
.78539816
19.CEIL
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;
---------
242582598
22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
--------- ---------
7.3890561 2.7182818
23.FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
--------- --------- -------------
25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
--------- ---------
26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3)
--------- --------- ---------
27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100)
--------- ---------- ---------
30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
--------- ---------
.91294525 242582598
32.SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
--------- ---------
33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
--------- ---------
2.2371609 .64836083
34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;
--------- ---------
35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
--------- ------------------
三. 日期
36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
TO_CHA
------
199910
37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5月 -04
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
MON_BETWEEN
-----------
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;
---------
39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
BJ_TIME
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32
40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;
NEXT_DAY
----------
25-5月 -01
41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
TO_CHAR(SYSDATE,'
-----------------
09-05-2004 星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
HH
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00
42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;
conver
------
strutz
44.HEXTORAW
将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制
46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型
47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41
48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL>
TO
--
高
50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;
---------
四. 其他
51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source')
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
--------- --------- ------------------------------ ------
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME
------------------------------ --------------------------------------------------
ORACLE.WORLD
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;
GR
--
AC
SQL> select greatest('啊','安','天') from dual;
GR
--
天
56.LEAST
返回一组表达式中的最小值
SQL> select least('啊','安','天') from dual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"GAO"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME
------------------------------ ---------
GAO
58.USER
返回当前用户的名字
SQL> select user from
USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('isdba') from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
ENTRYID
返回会话人口标志
SQL> select userenv('entryid') from dual;
USERENV('ENTRYID')
------------------
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
LANGUAGE
返回当前环境变量
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;
USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS>
SQLWKS>
SQLWKS>
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;
AVG(DISTINCTSAL)
----------------
SQL> select avg(all sal) from gao.table3;
AVG(ALLSAL)
-----------
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;
MIN(ALLSAL)
-----------
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
64.VARIANCE(DISTINCT|ALL)
求协方差
SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
--------- --------- ---------
66.HAVING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
--------- --------- ---------
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
--------- --------- ---------
67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
--------- ---------- ---------
35. 带参数的SQL
OracleParameter parm = new OracleParameter(":unknown" , OracleType.VarChar);
dbCmd.CommandText = "select colName from table where
参数数组:
OracleParameter[] parm =
};
parm[0].Value = "401";
parm[1].Value = "ACB";
36. Oracel存储过程中返回数据集(转)
(1).建立包
END;
(2).游标生成数据集
END;