Oracle常用SQL
WHERE ROWID IN
(SELECT RID
FROM (SELECT ROWNUM RN, RID
FROM (SELECT ROWID RID, 主键字段 from 表)
WHERE ROWNUM <= ((page - 1) * pageSize + pageSize)) --每页显示几条
WHERE RN > ((page - 1) * pageSize)) --当前页数
ORDER BY 主键字段;
--实际操作
SELECT *
FROM student
WHERE ROWID IN
(SELECT RID
FROM (SELECT ROWNUM RN, RID
FROM (SELECT ROWID RID, xh from student)
WHERE ROWNUM <= ((2 - 1) * 6 + 6)) --每页显示几条
WHERE RN > ((2 - 1) * 6)) --当前页数
ORDER BY xh;
--查询下一个序号
select ENTRY_USER_TD_sequence.Nextval from dual;
## 五、树查询以及层级显示
SELECT SYS_CONNECT_BY_PATH(T.AREA_CODE, '>') SEQ_AREA
FROM AREA T
START WITH T.AREA_ID = '23'
CONNECT BY PRIOR T.AREA_ID = T.PARENT_AREA_ID
## 六、查看表空间
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
## 七、表空间操作
### 7.1、查询表空间存放路径
select * from dba_data_files;
### 7.2、创建表空间
create tablespace tiger datafile'/oracle/oradata/orcl/tiger.dbf' size 10m autoextend on next 1m;
### 7.3、创建完表空间需指定对应用户
alter user c##tiger default tablespace tiger;
### 7.4、查询dba\_users下名字为cqx对应的表空间，需要这一步确认指定是否完成
select default_tablespace from dba_users where username='C##TIGER';
### 7.5、表空间自增
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/jkzd1.dbf' AUTOEXTEND ON NEXT 200M ;
## 八、数据处理
### 8.1、数据合并
merge into emp t1
using (select t.* from emp01 t) t2
on (t1.empno = t2.empno and t1.ename = t2.ename)
when matched then
update set t1.job = t2.job, t1.mgr = t2.mgr, t1.hiredate = t2.hiredate
when not matched then
insert
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values
(t2.empno,
t2.ename,
t2.job,
t2.mgr,
t2.hiredate,
t2.sal,
t2.comm,
t2.deptno);
### 8.2、存储过程语法
--create or replace procedure mydeal is
/*
*第一种写法
/
declare
v_name varchar(100);
v_xh number :=0;
begin
select xm,xh into v_name,v_xh from student where xh=3;
dbms_output.put_line(v_name||'------------>'||v_xh);
end;
/
*第二种写法
/
declare
type stu_record is record(
v_name varchar(100),
v_xh number :=0
);
v_interface stu_record;
begin
select xm,xh into v_interface from student where xh=6;
dbms_output.put_line(v_interface.v_name||'------------>'||v_interface.v_xh);
end;
/
*if else
/
declare
v_xh student.xh%TYPE;
v_temp varchar(30);
begin
select xh into v_xh from student where xh=23;
if v_xh<6 then
v_temp:='xh小于6';
elsif v_xh=6 then
v_temp:='xh等于6';
else
v_temp:='xh大于6';
end if;
dbms_output.put_line(v_temp);
end;
/
case when then end
/
declare
v_xm varchar(30);
v_temp varchar(30);
begin
select xm into v_xm from student where xh=2;
v_temp:=''
case v_xm when 'john' then 'xh是偶数'
when 'martin' then 'xh是奇数'
else 'null'
end;
dbms_output.put_line(v_temp);
end;
/
*loop 循环
/
declare
--①
v_i number(5) :=1;
begin
loop
dbms_output.put_line(v_i);
--③
exit when v_i>=100;
--②
v_i:=v_i+1;
end loop;
end;
/
*while
/
declare
v_i number(5) :=1;
begin
while v_i <=100 loop
dbms_output.put_line('--------------->'||v_i);
v_i:=v_i+1;
end loop;
end;
/
*for
/
begin
for c in 1..100 loop
dbms_output.put_line('c'||c);
end loop;
end;
/
*游标
/
declare
v_name student.xm%Type;
--定游标
cursor stu is select xm from student;
begin
--打开游标
open stu;
--提取游标
fetch stu into v_name;
while stu%found loop
dbms_output.put_line(v_name);
fetch stu into v_name;
end loop;
end;
/
练习
/
--游标练习
declare
type stu_record is record(
v_xm varchar(100),
v_xh number :=0
);
v_student stu_record;
--定游标
cursor stu is select xm,xh from student;
begin
--打开游标
open stu;
--提取游标
fetch stu into v_student;
while stu%found loop
dbms_output.put_line(v_student.v_xh||'---------------->'||v_student.v_xm);
fetch stu into v_student;
end loop;
--关闭游标
close stu;
end;
/
*for代替游标
/
declare
--定游标
cursor stu is select xm,xh from student;
begin
for c in stu loop
dbms_output.put_line(c.xh||'---------------->'||c.xm);
end loop;
end;
/
*函数
/
create or replace function hello(v_xm varchar2)
return varchar2
is
begin
return '=hello===='||v_xm;
end;
select hello('王正和') from dual;
--练习
--1
create or replace function get_sysadte
return date
is
begin
return sysdate;
end;
select get_sysadte from dual;
--2
create or replace function add_parm(v_num1 number,v_num2 number)
return number
is
v_sum number(10);
begin
v_sum:=v_num1+v_num2;
return v_sum;
end;
select add_parm(100,300) from dual;
/
*存过
*/
create or replace procedure deal_hello
is
begin
dbms_output.put_line('---------------->hello');
end;
--1
create or replace procedure deal_sum_xh
is
v_sum number(20) :=0;
cursor stu is select xh,xm from student;
begin
for c in stu loop
v_sum:=c.xh+v_sum;
dbms_output.put_line('<-----数据----------->'||c.xh);
end loop;
dbms_output.put_line('-----最终数据----------->'||v_sum);
end;
### 8.3、存储过程案例
--测试1
CREATE OR REPLACE PROCEDURE test_ceshi is
v_name c_table_name.c_name%Type;
--定游标
cursor stu is select c_name from c_table_name;
begin
--打开游标
open stu;
--提取游标
fetch stu into v_name;
while stu%found loop
dbms_output.put_line(v_name);
fetch stu into v_name;
end loop;
close stu;
end;
--测试2
CREATE OR REPLACE PROCEDURE test_ceshi is
--定游标
cursor stu is select c_name from c_table_name;
begin
for c in stu loop
dbms_output.put_line(c.c_name);
end loop;
end;
## 九、用户创建、权限赋权
### 9.1、创建用户以及赋权
创建用户
create user username identified by password
绑定表空间
alter user 用户名 default tablespace 表空间名;
sysdba授权
grant sysdba to c####bb container=all;
一、创建
sys;//系统管理员，拥有最高权限
system;//本地管理员，次高权限
scott;//普通用户，密码默认为tiger,默认未解锁
oracle有三个默认的用户名和密码~
1.用户名:sys密码:change_on_install
2.用户名:system密码:manager
3.用户名:scott密码:tiger
二、登陆
sqlplus / as sysdba;//登陆sys帐户
sqlplus sys as sysdba;//同上
sqlplus scott/tiger;//登陆普通用户scott
三、管理用户
create user zhangsan;//在管理员帐户下，创建用户zhangsan
alert user scott identified by tiger;//修改密码
四，授予权限
1、默认的普通用户scott默认未解锁，不能进行那个使用，新建的用户也没有任何权限，必须授予权限
grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限，允许用户登录数据库
grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限
grant create table to zhangsan;//授予创建表的权限
grante drop table to zhangsan;//授予删除表的权限
grant insert table to zhangsan;//插入表的权限
grant update table to zhangsan;//修改表的权限
grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)
2、oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权
grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限
grant drop on tablename to zhangsan;//授予删除表的权限
grant insert on tablename to zhangsan;//授予插入的权限
grant update on tablename to zhangsan;//授予修改表的权限
grant insert(id) on tablename to zhangsan;
grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update
grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限
五、撤销权限
基本语法同grant,关键字为revoke
六、查看权限
select * from user_sys_privs;//查看当前用户所有权限
select * from user_tab_privs;//查看所用用户对表的权限
七、操作表的用户的表
select * from zhangsan.tablename
八、权限传递
即用户A将权限授予B，B可以将操作的权限再授予C，命令如下：
grant alert table on tablename to zhangsan with admin option;//关键字 with admin option
grant alert table on tablename to zhangsan with grant option;//关键字 with grant option效果和admin类似
九、角色
角色即权限的集合，可以把一个角色授予给用户
create role myrole;//创建角色
grant create session to myrole;//将创建session的权限授予myrole
grant myrole to zhangsan;//授予zhangsan用户myrole的角色
drop role myrole;删除角色
## 十、数据导入、导出
### 10.1、Oracle数据库数据导出
exp system/manager@TEST file=d:\daochu.dmp full=y
### 10.2、Oracle数据库数据导入
exp system/manager@TEST file=d:\daochu.dmp full=y
### 10.3、Oracle指定表导出
exp aaa/bbb@127.0.0.1:1521/orcl file=d:\daochu20220726.dmp tables=('USER')
## 十一、数据库僵尸进程处理
### 11.1、查询僵尸进程处理
SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid||','|| serial# ||''';', username, program, status
FROM v$session
WHERE status = 'INACTIVE';
不活跃进程处理
ALTER SYSTEM KILL SESSION '4,27915';
## 十二、数据库用户创建、表空间、赋权
### 12.1、数据库用户创建、表空间、赋权
--查询表空间
select * from dba_data_files;
--创建表空间
create tablespace GSRS datafile'E:/ORACLE/INSTALL/ORADATA/ORCL/GSRS.dbf' size 4096m autoextend on next 100m;
--创建用户
create user gsrs identified by gsrs;
--用户绑定表空间
alter user gsrs default tablespace GSRS;
--验证表空间绑定情况
select default_tablespace from dba_users where username='GSRS';
--设置表空间自增
ALTER DATABASE DATAFILE 'E:/ORACLE/INSTALL/ORADATA/ORCL/GSRS.dbf' AUTOEXTEND ON NEXT 100M ;
--用户赋予dba权限
GRANT DBA TO qhrs
--用户赋予登录会话权限
grant create session,resource,connect to gsrs;
## 十三、物化视图创建
### 13.1、物化视图创建
--创建物化视图，1s刷新一次
create materialized view mv_inter_view
build immediate
refresh force on demand start with sysdate next sysdate+1/24/60/60
as
select
- from aa
where i_flag ='-1'
union all
select
- from bb
where i_flag ='-1'
--物化视图创建索引
create unique index mv_inter_view_px on mv_inter_view (ywlsh,id);
## 十四、浮动IP vip重启命令
su -l grid
sqlplus / as sysdba
## 十五、检查监听是否正常
SELECT instance_name, version, started FROM v$instance;
SELECT pid, spid, username FROM v$process;
SELECT STATUS FROM V$INSTANCE;
SELECT instance_name,STATUS FROM V$INSTANCE;
## 十六、数据库表空间扩展
/**
*xxx 需要扩展表空间名称
一、前言
Oracle是世界领先的信息管理软件开发商，因其复杂的关系数据库产品而闻名。Oracle数据库产品为财富排行榜上的前1000家公司所采用，许多大型网站也选用了Oracle系统。Oracle的关系数据库是世界第一个支持SQL语言的数据库。1977年，Lawrence J.Ellison领着一些同事成立了Oracle公司，他们的成功强力反击了那些说关系数据库无法成功商业化的说法。Oracle公司的财产净值已经由2000美元增值到了年收入超过97亿美元。
二、建表基本数据操作
-- 创建用户
create user wzh identified by orcl;
--创建表
--学生表
create table student (
xh number(4), --学号
xm varchar2(20), --姓名
sex char(2), --性别
birthday date, --出生日期
sal number(7,2) --奖学金
);
-- 添加主键
alter table student add constraint pk_student primary key (xh);
--班级表
create table classStu(
classid number(2),
cname varchar2(40)
);
--修改表
--添加一个字段
alter table student add (classid number(2));
--修改一个字段的长度
alter table student modify (xm varchar2(30));
--修改字段的类型或是名字（不能有数据） 不建议做
alter table student modify (xm char(30));
--删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应该是加在后面)
alter table student drop column sal;
--修改表的名字 很少有这种需求
rename stu to student;
--删除表
drop table student;
--修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）
alter session set nls_date_format ='yyyy-mm-dd';
--删除数据
delete from student; --删除所有记录，表结构还在，写日志，可以恢复的，速度慢。
--delete的数据可以恢复。
savepoint student; --创建保存点
delete from student;
rollback to a; --恢复到保存点
一个有经验的dba，在确保完成无误的情况下要定期创建还原点。
drop table student; --删除表的结构和数据；
delete from student where xh = 'a001'; --删除一条记录；
truncate table student; --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。
/**
* oracle 事务
*事务的几个重要操作
*1.设置保存点 savepoint a
*2.取消部分事务 rollback to a
*3.取消全部事务 rollback
*/
savepoint a; --创建保存点a
Savepoint created
delete from student where xh=1
savepoint b; --创建保存到b
Savepoint created
rollback to a; --通过保持点来恢复这条记录;delete from student where xh=1
/**
一、字符函数
字符函数是oracle中最常用的函数，我们来看看有哪些字符函数：
lower(char)：将字符串转化为小写的格式。
upper(char)：将字符串转化为大写的格式。
length(char)：返回字符串的长度。
substr(char, m, n)：截取字符串的子串，n代表取n个字符的意思，不是代表取到第n个
replace(char1, search_string, replace_string)
instr(C1,C2,I,J) -->判断某字符或字符串是否存在，存在返回出现的位置的索引，否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
二、数学函数
数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round等
我们讲最常用的：
round(n,[m]) 该函数用于执行四舍五入，
如果省掉m，则四舍五入到整数。
如果m是正数，则四舍五入到小数点的m位后。
如果m是负数，则四舍五入到小数点的m位前。
*/
SELECT round(23.75123) FROM dual; --返回24
SELECT round(23.75123, -1) FROM dual; --返回20
SELECT round(27.75123, -1) FROM dual; --返回30
SELECT round(23.75123, -3) FROM dual; --返回0
SELECT round(23.75123, 1) FROM dual; --返回23.8
SELECT round(23.75123, 2) FROM dual; --返回23.75
SELECT round(23.75123, 3) FROM dual; --返回23.751
trunc(n,[m]) --该函数用于截取数字。如果省掉m，就截去小数部分，如果m是正数就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位。
--查看角色
select * from dba_role_privs where grantee='SYSTEM';
--查询orale中所有的系统权限，一般是dba
select * from system_privilege_map order by name;
--查询oracle中所有对象权限，一般是dba
select distinct privilege from dba_tab_privs;
--查询oracle 中所有的角色，一般是dba
select * from dba_roles;
--查询数据库的表空间
select tablespace_name from dba_tablespaces;
--一个角色包含的系统权限
select * from dba_sys_privs where grantee='角色名'
select * from role_sys_privs where role='角色名'
--一个角色包含的对象权限
select * from dba_tab_privs where grantee='DBA'
--oracle究竟有多少种角色
select * from dba_roles;
--查看某个用户，具有什么样的角色
select * from dba_role_privs where grantee='SYSTEM'
--显示当前用户可以访问的所有数据字典视图。
select * from dict where comments like '%grant%';
--显示当前数据库的全称
select * from global_name;
--1.创建两个用户ken，tom。初始阶段他们没有任何权限，如果登录就会给出错误的信息。
create user ken identified by ken;
--2 给用户ken授权
grant create session, create table to ken with admin option;
grant create view to ken;
--3 给用户tom授权
--我们可以通过ken给tom授权，因为with admin option是加上的。当然也可以通过dba给tom授权，我们就用ken给tom授权：
grant create session, create table to tom;
grant create view to ken; --ok 吗？不ok
/*
*创建视图
*/
create or replace view v_stu_class
as
select * from (
select s.xh,s.classid,s.xm,s.sex,s.birthday,c.cname from student s,classstu c where s.classid=c.classid
);
--创建序列
create sequence ENTRY_USER_TD_sequence
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
三、数据库常用时间
间隔/interval是指上一次执行结束到下一次开始执行的时间间隔，当interval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。
（1）、每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/ (24*60)
每小时执行
Interval => TRUNC(sysdate,'hh') + 1/ (24)
（2）、每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate+ 1) +1/ (24)
（3）、每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
（4）、每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
（5）、每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
（6）、每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
（7）、每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24
四、Oracle分页
-- 查询
select * from student order by xh ASC;
select * from student where birthday is null;
/**
*查询分页
*page:3
*pageSize:10
*/
--分页公式
SELECT *
FROM 表
* 32G 代表需要扩展的大小
* /data/oradata/orcl/xxx1.dbf 表空间模块存储路径
*/
alter tablespace xxx add datafile '/data/oradata/orcl/xxx1.dbf' SIZE 32G
十七、数据库账号密码重置
17.1. 切换到oracle帐号
su - oracle
17.2. dba登录数据库，重置密码
sqlplus /nolog
SQL> conn /as sysdba
# 上面两行可以简写为 sqlplus / as sysdba
SQL> alter user sys identified by Mypasswd1234;
十八、数据库常用命令
17.1. 启动、关闭命令
以oracle身份登录数据库，命令：su -oracle
进入Sqlplus控制台，命令：sqlplus /nolog
以系统管理员登录，命令：connect / as sysdba
启动数据库，命令：startup
如果是关闭数据库，命令：shutdown immediate
退出sqlplus控制台，命令：exit
进入监听器控制台，命令：lsnrctl
启动监听器，命令：start
退出监听器控制台，命令：exit
十八、Oracle数据库占用内存过高问题
18.1、步骤如下
1.cmd sqlplus system账户登录
2.show parameter sga; --显示内存分配情况
3.alter system set sga_max_size=200m scope=spfile; --修改占用内存的大小，根据需要设置
4.alter system set memory_target = 200M scope=spfile； --修改目标内存占用大小，根据需要设置
5.重启oracle服务
18.2、注意一下
sga_target < = sga_max_size <= memory_target <= memory_max_target
18.2、效果图：
修改前占用1G：
修改后占用200M
18.3、由于数据库无法启动，只能调整编辑启动参数文件
1, 根据错误的spfile创建pfile;
| 1 | SQL> create pfile='/tmp/pfile20150115.txt' from spfile; |
|---|
2**, ** 编辑上面生成的pfile将memory_target的值修改成大于SGA_MAX_SIZE
3，备份以前的参数文件
4，恢复参数文件：
| 1 | SQL> create spfile from pfile='/tmp/pfile20150115.txt'; |
|---|
5， 启动数据库：
| 1 | SQL> startup |
|---|
18.3、pfile20150115.txt
ORCL.__data_transfer_cache_size=0
ORCL.__db_cache_size=8019509248
ORCL.__inmemory_ext_roarea=0
ORCL.__inmemory_ext_rwarea=0
ORCL.__java_pool_size=0
ORCL.__large_pool_size=100663296
ORCL.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
ORCL.__pga_aggregate_target=3254779904
ORCL.__sga_target=9730785280
ORCL.__shared_io_pool_size=134217728
ORCL.__shared_pool_size=1442840576
ORCL.__streams_pool_size=0
ORCL.__unified_pga_pool_size=0
*.audit_file_dest='/opt/oracle/admin/ORCL/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/opt/oracle/oradata/ORCL/control01.ctl','/opt/oracle/oradata/ORCL/control02.ctl'
*.db_block_size=8192
*.db_name='ORCL'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.local_listener='LISTENER_ORCL'
*.memory_target=3628m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=3088m
*.processes=1280
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=200m
*.sga_target=100m
*.undo_tablespace='UNDOTBS1'
OK，到此结束，数据库正常启动。
浙公网安备 33010602011771号