oracle数据库之操作总结

时间：2022-06-12 18:47:29

## 连接数据库：

sqlplus test/test##@localhost:/ORCL

## 查询数据库所有的表：

select table_name from user_tables;

## 查询数据库表的字段名：

desc 表名;

## 设置oracle 客户端的字符集：

  • Unix操作:
## 中文字符集

export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK";

## unicode字符集

export NLS_LANG=american_america.AL32UTF8

也可以编辑 bash_profile 文件进行永久设置：
vi .bash_profile NLS_LANG="Simplified Chinese_china".ZHS16GBK export NLS_LANG 
## 使 bash_profile 设置生效 
source .bash_profile
  • Window操作:
## 中文字符集

set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK 

## unicode字符集

set NLS_LANG=american_america.AL32UTF8 

也可以通过修改注册表键值永久设置 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOMExx/NLS_LANG

## 查看oracle 客户端的字符集：

  • Unix操作:
## 查看NLS_LANG
env|grep NLS_LANG

## 修改oracle 服务端的字符集：

## oracle 服务端字符集查询

SQL>select userenv('language') from dual;

## oracle 服务端字符集修改

.管理员身份登录。

sqlplus /nolog

SQL>conn /as sysdba

.执行以下操作语句，关闭数据库,修改设置。

SQL>shutdown immediate;

SQL>startup mount

SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=;

SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=;

.开启修改后的数据库，修改字符集为ZHS16GBK。

SQL>alter database open;

SQL>ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

.修改成功，重启数据库，查看当前的字符集。

SQL>shutdown immediate;

SQL>startup

SQL>select userenv('language') from dual；

## 导出数据表的insert语句

set head off

set heading off

set termout off

set feedback off

set term off

set echo off

set line

set pagesize ;

spool test.sql;

select 'insert into test(ID,NAME,SEX) values('||''''||ID||''''||','||''''||NAME||''''||','||''''||SEX||''''||');' from test;

select 'commit;' from dual;

spool off;

## 查询数据库的实例名

方法一：select instance_name from v$instance;

方法二：show parameter instance

########################################################################################

## 查询数据库的下的全部函数

select object_name from user_objects where object_type='FUNCTION';

## 查看函数内容，引号内的函数名大写

select text from user_source where name='函数名';

## 查询数据库用户下的全部函数

SELECT * FROM dba_objects WHERE object_type='FUNCTION'and owner='用户名';

## 查看用户下函数内容

SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL('FUNCTION', '函数名', '用户名')) FROM dual;

## 创建函数

create or replace function 函数名 函数内容

########################################################################################

## 设置主键自增，创建序列

create sequence TEST_SEQUENCE

minvalue

maxvalue

start with

increment by

cache ;    

CREATE SEQUENCE seqTest

INCREMENT BY

start with

NOMAXvalue

NOCYCLE

NOCACHE;

## 创建触发器

CREATE OR REPLACE TRIGGER test_tri

BEFORE INSERT ON test

FOR EACH ROW WHEN(new.id IS NULL)

BEGIN

  select seqTest.nextval into :new.id from dual;

END;

## 创建表

drop table test;

create table test(

ID varchar2(),

stu_name varchar2(),

CONSTRAINTS test_id PRIMARY KEY(ID)

);

## 查询所有触发器

select * from user_triggers;

## 查询所有序列

select * from user_sequences;

## 删除触发器

DROP TRIGGER test_tri;

## 删除序列

DROP SEQUENCE seqTest;

## 查询序列当前值

select seqTest.currval from dual;

## 查询序列下一个值

select seqTest.nextval from dual;

########################################################################################

## 数据表添加字段

alter table test add my_name varchar2()

## 数据表删除字段

alter table test drop column my_name

## 修改数据表结构

alter table test modify stu_name varchar2();

########################################################################################

## 字符串和时间转换

-- 字符串转日期

SELECT to_date('20170531','yyyy/mm/dd') FROM DUAL;

SELECT to_date('2019-03-21 00:00:00','yyyy-MM-dd HH24:mi:ss') FROM DUAL;

SELECT to_timestamp('20160112 10:00:00', 'yyyymmdd HH:mi:ss') FROM DUAL;

-- 日期转字符串：

SELECT to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') FROM DUAL;

SELECT to_char(sysdate,'YYYY"年"MM"月"DD"日"') FROM DUAL;

例子：

-- 查询某时间段的日志信息

SELECT * FROM ACTION_LOG

WHERE

    LOG_TIME BETWEEN to_date ('2019-03-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

    AND to_date ('2019-03-21 23:59:59', 'yyyy-mm-dd hh24:mi:ss');

相关文章