oracle函数

oracle字符集设置

• windows

  方法一：我的电脑->属性->高级->环境变量->新建：
  变量名为：nls_lang
  变量值为：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
  方法二：开始-运行-CMD：
  SET NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
  注意：该方法只是在当前会话生效，关闭CMD窗口后下次还需要重新设置
  方法三：开始-运行-regedit：
  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMExx\NLS_LANG
  修改NLS_LANG将其改为SIMPLIFIED CHINESE_CHINA.ZHS16GBK

• linux
  方法一：打开命令行窗口，su- oracle切换Oracle用户：
  export NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
  方法二：可以编辑 bash_profile 文件进行永久设置：
  vi .bash_profile
  export NLS_LANG=”SIMPLIFIED CHINESE_CHINA.ZHS16GBK”
  或者
  export NLS_LANG=”Simplified Chinese_china”.ZHS16GBK
  最后source .bash_profile 使修改后的配置文件生效

• oracle
  查看数据库当前字符集:
  select * from v$nls_parameters;

oracle命令

• sqlplus
  1）sqlplus username/password 如：普通用户登录，sqlplus scott/tiger，如果没有连接标识符, sqlplus将连接到默认数据库；
  2）sqlplus username/password@net_service_name 如: sqlplus scott/tiger@orcl， 是服务的简单名称，它解析为连接标识符；
  3） sqlplus / as sysdba，或sqlplus username/password as sysdba，登录的用户均为SYS，通过select user from dual;可以查看当前登录的用户；
  4）sqlplus username/password@//host:port/net_service_name
  5）在进入sql*plus之后，可以使用conn连接到其他用户，如：conn sys/admin as sysdba。

oracle函数

• DECLARE
  DECLARE
  –变量区(不能在begin..end中定义）
  BEGIN
  –业务代码区
  excetipn
  –异常捕获区
  END;

DECLARE -- 声明变量  v_dir           VARCHAR2(100) := 'D:\';  v_sql           VARCHAR2(4000);  v_year          VARCHAR2(5);  v_month         VARCHAR2(3);  PROCEDURE exec_sql(p_sqlstr IN VARCHAR2) IS  BEGIN -- 可执行语句开始    EXECUTE IMMEDIATE p_sqlstr;  EXCEPTION -- 如果表已存在，则会引发异常    WHEN OTHERS THEN      NULL;  END;

• length
  1）lengthb(string)计算string所占的字节长度，返回字符串的长度，单位是字节
  2）length(string)计算string所占的字符长度，返回字符串的长度，单位是字符

• substr
  substr(字符串,截取开始位置,截取长度) //返回截取的字
  substr('Hello World',0,1) //返回结果为 ‘H’，从字符串第一个字符开始截取长度为1的字符串
  substr('Hello World',1,1) //返回结果为 ‘H’，0和1都是表示截取的开始位置为第一个字符
  substr('Hello World',2,4) //返回结果为 ‘ello’
  substr('Hello World',-3,3) //返回结果为 ‘rld’，负数(-i)表示截取的开始位置为字符串右端向左数第i个字符
  eg：select substr('Hello World',-3,3) value from dual;

• instr
  instr（sourceString,destString,start,appearPosition） //返回查找到的字符串的位置。start代表查找的开始位置，这个参数可选，默认为1。appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1。如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算。

• REPLACE
  replace（char,search_string,replacement_string）//每个search_string都被replacement_string所代替。如果replacement_string为空或为null，那么所有的search_string都被移除。
  eg：select replace ('HelloWorld','Hello','Hi') from dual; //返回Hi World
  eg：select replace ('HelloWorld','Hello') from dual; //返回World

• loop
  loop //循环开始
statament; //循环体
end loop; //循环结束
  eg：WHILE length(str) > 0 LOOP
  eg：FOR i IN 0 .. 6 LOOP

• to_char
  to_char(sysdate, 'yyyy') //把sysdate转换成string，返回值为yyyy
  to_char(123) //把sysdate转换成string，返回值为yyyy

• trim
  trim(' str1 str2 ') //去除指定字符的前后空格
  ltrim(' str1 str2 ') //去除指定字符的前面空格
  rtrim(' str1 str2 ') //去除指定字符后面后空格
  trim([leading | trailing | both] trim_char from string) //从字符串String中删除指定的字符trim_char。leading:从字符串的头开始删除。trailing:从字符串的尾部开始删除。borth:从字符串的两边删除。

• trunc
  select trunc(sysdate) from dual –返回今天的日期
  select trunc(sysdate, 'mm') from dual –返回当月第一天.
  select trunc(sysdate,'yy') from dual –返回当年第一天
  select trunc(sysdate,'dd') from dual –返回当前年月日
  select trunc(sysdate,'yyyy') from dual –返回当年第一天
  select trunc(sysdate,'d') from dual –返回当前星期的第一天
  select trunc(sysdate, 'hh') from dual – 当前时间为hh
  :mi
  select trunc(sysdate, 'mi') from dual –当前时间为hh
  :mi:00

• EXECUTE IMMEDIATE
  EXECUTE IMMEDIATE代替了以前Oracle8i中DBMS_SQL package包。在PL/SQL中在执行SQL语句时可以直接写SQL或者可以把一个SQL语句拼成一个字符串，如下：
  select * from dual;
v_sql:='select * from dual'; EXECUTE IMMEDIATE v_sql;

• dbms_output
  dbms_output.put('a'); –写入buffer但不输出
  dbms_output.put('b'); –写入buffer但不输出
  dbms_output.new_line; –回车(换行)，输出
  dbms_output.put_line('hello world!'); –输出ab，换行，hello world

• upper&lower
  update user　set 　user_name=Upper(user_name); –将小写转换为大写
  update user　set 　user_name=lower(user_name); –将大写转换为小写

• RAISE
  DECLARE
e_app EXCEPTION;
BEGIN
RAISE e_app; –抛出异常
  END;

• imp
  imp 用户名/密码@目标数据库IP/SID file= 本地路径\dmp名称.dmp log= 本地已有路径\日志命名.txt fromuser=(源用户名) touser=(目标用户) ignore=y compile=n statistics=none buffer=838860800 commit=y

• to_number
  select to_number('0123') from dual; –将字符串转换为数字数据类型

oracle创建表空间

• 先查询空闲空间
  select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space;

• 增加Oracle表空间
  先查询数据文件名称、大小和路径的信息，语句如下：
  select tablespace_name,file_id,bytes,file_name from dba_data_files;

• 修改文件大小语句如下
  alter database datafile '需要增加的数据文件路径，即上面查询出来的路径' resize 800M;

• 创建Oracle表空间

create tablespace test  datafile 'D:/oradata/test.dbf' size 800M  autoextend on  next 50M  maxsize 1000M maxsize unlimited  --大小不受限制extent management local uniform; --unform表示区的大小相同，默认为1Mextent management local uniform size 500K; --unform size 500K表示区的大小相同，为500Kextent management local autoallocate; --autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区temporary; --temporary创建字典管理临时表空间

创建本地管理临时表空间

create temporary tablespace test  tempfile 'D:/oradata/test.dbf' size 800M  autoextend on  next 50M  maxsize 1000M

为表空间增加数据文件

alter tablespace test  add  datafile 'D:/oradata/test.dbf' size 800M  autoextend on next 50M  maxsize 1000M;

更改自动扩展属性：

alter database datafile  'D:/oradata/test01.dbf',  'D:/oradata/test02.dbf'  autoextend off;

oracle创建用户及授权

create user username identified by password default tablespace tablespaceName temporary tablespace tablespaceName; --创建用户grant select any table to username; --用户授权grant select on tablename to username; --用户授权grant create table tousername; --用户授权grant select table on tablename to usernamewith grant option; --权限传递revoke select on tablename to username; --撤销权限alter user username identified by password; --修改密码password --修改密码select username from dba_users --查看所用用户select * from user_sys_privs; --查看当前用户所有权限select * from user_tab_privs; --查看所用用户对表的权限

• 创建DBLINK

select db_link FROM user_db_links; --查看DBLINKselect owner,object_name from dba_objects where object_type='DATABASE LINK'; --查看DBLINKselect * from dba_db_links; --查看DBLINKdrop public database link dblinkname; --删除DBLINKcreate public database link DBLINK名 connect to 连接用户 identified by 连接密码 using '...'; --其中'...'与tnsnames.ora中该库对应的连接标识符一致

v$instance

1.查询dba_objects 知，v$instance是同义词select * FROM DBA_OBJECTS WHERE OBJECT_name='V$INSTANCE'2.查询dba_synonyms，可知道他是V_$INSTANCE表的同义词select * FROM dba_synonyms WHERE synonym_name='V$INSTANCE'3.查看v_$instance的创建语句，SELECT * FROM dba_views WHERE view_name=upper('V_$INSTANCE')发现它是有v$instance创建的4.select instance_name, host_name INTO v_instance_name, v_host_name FROM v$instance;查看数据库实例名和主机名

dual

dual是一个虚拟表，dual里面永远只有一行一列。我们可以用它来做很多事情，如下：1、查看当前用户select user from dual;2、查看当前日期、时间select sysdate from dual;select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名select dbms_random.random from dual;--获得一个随机数3、得到序列的下一个值或当前值create sequence sq increment by 1 start with 1;select sq.nextval from dual;--获得序列sq的下一个值select sq.currval from dual;--获得序列sq的当前值4、可以用做计算器select 1+2 from dual;

触发器
ALTER TRIGGER trigger_name COMPILE; –编译触发器

同义词
select * from dba_synonyms where synonym_name; –查看同义词
create [or replace] [public] synonym [ schema.] 同义词名称 for [ schema.] object [ @dblink ]; –创建或替换同义词

在删除一个表中的全部数据时，须使用：truncate table 表名
因为用drop table，delete * from 表名时，tablespace表空间该表的占用空间并未释放，反复几次drop，delete操作后，该tablespace上百兆的空间就被耗光了。