oracle记录

37

oracle记录

create tablespace lottery datafile --建立表空间
'D:\oracle\product\10.2.0\oradata\dzw\lottery.dbf' 
size 50m reuse autoextend on next 56k maxsize 100m;
---------------------------------------
drop tablespace ssscms including contents and datafiles;
---------------------------------------
create user dongzhiwei identified by admin; --创建用户
---------------------------------------
create user dongzhiwei identified by admin default tablespace lottery; --创建用户并指定表空间
---------------------------------------
grant dba to dongzhiwei; --授权
---------------------------------------
drop user a cascade;
---------------------------------------
imp dongzhiwei/admin file='d:/lottery.dmp' full=y; --导入备份数据
exp dongzhiwei/admin file='d:/test.dmp' oner=(dongzhiwei); --导出备份
exp dongzhiwei/admin file='d:/test.dmp' oner=(dongzhiwei) rows=n --不导出数据
---------------------------------------
create table s3d( --创建表
id int,
at int,
num char(3),
num1 int,
num2 int,
num3 int,
dt date,
constraint pk_id primary key (id)); --添加主建约束
---------------------------------------
alter table s3d modify num1 int; --修改表num1列的类型
update s3d set num = num1||num2||num3 --连接字符串
---------------------------------------
set serveroutput on --启动服务器输出
declare --最简单的plsql程序块
begin
dbms_output.put_line('abc');
end;
/
---------------------------------------
declare ---定义变量与for循环
    i constant int := 10;
    j int ;
begin
    for j in 1..i loop
            dbms_output.put_line(j);
    end loop;
end;
/
---------------------------------------
declare
   i constant int := 10; --定义常量
   j int ;
   num char(3);
begin
   for j in 1..i loop
           select num into num from s3d where id = j; --从数据库中查询值赋予变量
           dbms_output.put_line(num);
   end loop;
end;
/
---------------------------------------
declare
   num s3d.num%type; --使用%type定义变量类型
begin
   select num into num from s3d where id =123;
   dbms_output.put_line(num);
end;
/
---------------------------------------
declare
   type typetest is record( --定义记录类型变量
           num s3d.num %type,
           dt date
);
   mytype typetest; --自己定义的类型
begin
   select num,dt into mytype from s3d where id =1;
   dbms_output.put_line(mytype.num||' '||mytype.dt);
end;
/
---------------------------------------
declare
    testtable s3d %rowtype; --用rowtype
begin
    select * into testtable from s3d where id=1;
    dbms_output.put_line(testtable.num);
end;
/
--------------------------------------
declare
s int:=0;
i int:=1;
begin
    loop --loop exit when
    s:=s+i;
    i:=i+1;
    exit when i>100;
end loop;
dbms_output.put_line(s);
end;
   ---------------------------------------
declare
s int:=0;
i int:=1;
begin
    while i<=100 loop --while loop ..end loop
            s:=s+i;
            i:=i+1;
    end loop;
dbms_output.put_line(s);
end;
   ---------------------------------------
   declare -- for in loop... end loop
s int:=0;
i int:=1;
begin
    for i in 1..100 loop
            s:=s+i;
    end loop;
dbms_output.put_line(s);
end;
---------------------------------------
declare --游标与多维表配合使用
type tt is table of s3d%rowtype index by binary_integer;
t tt; --自定义的多维表
cursor cs is
    select * from pt order by id desc;
i int := 0;
begin
open cs;
loop
    exit when cs%notfound;
    fetch cs --把游标中的数全部放放多维表t中
      into t(i);
    i := i + 1;
end loop;
close cs;
dbms_output.put_line(t.count);
for i in 0..t.count-2 loop --按顺序取多维表中的数
      dbms_output.put_line(t(i).id||' '||t(i+1).id||' '||(t(i).id-t(i+1).id));
      --dbms_output.put_line(t(i).num||' '||t(i+1).num||' '||(t(i).id-t(i+1).id));
end loop;
end;
---------------------------------------
sign(n)--n>0返回1 n=0返回0 n<0返回-1
ceil(n)--大于等于n的最小整数
floor(n)--小于等于n的最大整数
round(n1,n2)----四舍五入带n2位小数的n1,不指定n2默认为0
trunc(n1,n2)--截去部分小数位,使剩下n2位小数,不四舍五入
mod(n1,n2)--n1与n2的余数
---------------------------------------
select num,count(*) from s3d group by num having count(*)>5;--查询出现超过5次的数
---------------------------------------
select table_name from dba_tables where owner='DONGZHIWEI';--查询我自己的表所有的表名
--------------------------------------
declare
   i int :=0;
   j int :=0;
   k int :=0;
   s int :=0;
begin
   for i in 0..9 loop
           for j in 0..9 loop
                   for k in 0..9 loop
                           insert into allNum(id,num) values(s,i||j||k);
                           s:=s+1;
                   end loop;
           end loop;
   end loop;
end;
/
//反向取
declare
begin
for i in reverse 1..5 loop
dbms_output.put_line(i);
end loop;
end;
/
--------------------------------------
create table pt as select * from s3d where 1=2;--创建和现有表一致的表 ，不插入数据
insert into pt select * from s3d where id=1
--------------------------------------
select id,num,--case when then 结构 ,注意case之前的逗号
case
when id=1 then '111'
when id=2 then '222'
else 'else'
end
from s3d where id<5;
----------------------------------------
select id,num, --decode语句,注意decode之前的逗号
decode(id,1,'111',2,'222',3,'333','else') from s3d where id<5;
---------------------------------------- 
create table t0(id int primary key);--设定主键
create table t1(id int,constraint pk_t1_id primary key(id));--对主键操作
alter table t1 drop constraint pk_t1_id; --去掉主键
alter table t1 add constraint pk_t1_id primary key(id);--添加主键
alter table t1 disable constraint pk_t1_id;--使主键约束无效，此时可插入相同主键值
alter table t1 enable constraint pk_t1_id;--使主键约束生效

create table t2(id int,t1id,--建表，指定外键
constraint fk_t1_id foreign key(t1id) reference --指定t1id为外键 级联删除
s t1(id) on delete cascade);

alter table t2 drop constraint fk_t1_id;--删除外键
alter table t2 add constraint fk_t1_id foreign key(id) references t1(id)--添加外键
on delete set null;

create table t3(sex char(2),constraint ck_sex check(sex='男' or sex='女')); --添加check约束
create table t3(sex char(2) check(sex='男' or sex='女'));--添加check约束
---------------------------------------- 
update s3d set sj='0'||trim(sj) where length(trim(sj))=2; --去掉空格并在前面改成0
---------------------------------------- 
select trunc(134.91) from dual; --去掉小数部分，不四舍五入
---------------------------------------- 
select dbms_random.value(100,999) from dual; --100到999间的随机数
select dbms_random.string('u',3) from dual; --三个小写随机字符
--'u','U' : 大写 a-z 
    --'l','L' : 小写 a-z
    --'a','A' : 任意大写(小写)a-z 
    --'x','X' : 小写和数字
    --'p','P' : 任意可打印字符

---------------------------------------创建生成指定位数的随机整数
create or replace function random(num int:=3) return int is
s int:=1;
e int:=9;
begin
for i in 1..abs(abs(num)-1) loop
   s:=s||'0';
   e:=e||'9';
end loop;
return trunc(dbms_random.value(s,e));
end;
---------------------------------------- 授权给用户执行函数的权限
conn 
grant execute on random to tu1;
grant execute on dbms_lock to USERNAME；
select system.random from dual;
----------------------------------------
declare 
begin 
for i in 1..10 loop 
   dbms_output.put_line(i);
   dbms_lock.sleep(0.5);--使Pl/sql暂停1秒
end loop; 
end; 
----------------------------------------
select user#,name,password from user$ where name='D';
update user$ set name='A' where name = 'D';
----------------------------------------
在PL/SQL中执行DDL语句，用双单引号进行转义
execute immediate sql;
dbms_utility.exec_ddl_statement('sql');

declare
begin
execute immediate 'delete from place_province p where p.id=''12345''';
--dbms_utility.exec_ddl_statement('delete from place_province p where p.id=''12345''');
commit;
end;
----------------------------------------
oracle中使用hibernate时，基本类型在java文件和hbm文件中要配置为对象类型，否则常会报 Null value was assigned to a property of primitive
----------------------------------------
select to_date('2009-10-07 04:12:40','yyyy-mm-dd hh:mi:ss') from dual;
----------------------------------------
select to_char(sysdate,'yyyy"年"mmddhhmiss') from dual;
----------------------------------------
select to_number('$1,600.00','$99,999.00') from dual;
----------------------------------------
select nullif(2,null) from dual;--2
select nullif(2,2) from dual;-- null
----------------------------------------
select coalesce(null,null,3) from dual; --3
----------------------------------------
select decode(2,1,'111',2,'222') from dual;--222
select decode(3,1,'111',2,'222','333') from dual;
select sum(decode(num,'125',1,0)),sum(decode(num,'248',1,0)) from s3d;
----------------------------------------
select case 1 when 1 then '111' when 2 then '222' else '333' end from dual; --111
select 1, case 1 when 1 then '111' when 2 then '222' else '333' end from dual; -- 1 111
---------------------------------------- for使用
declare
cursor cs is
select * from s3d where rownum<5;
begin
for i in cs loop
dbms_output.put_line(i.num);
end loop;
end;

declare
begin
for i in (select * from s3d where rownum<5) loop
dbms_output.put_line(i.num);
end loop;
end;
----------------------------------------
select table_name from user_tables;--查看当前用户的表
select table_name from all_tables;--查看所有用户的表名
select table_name from dba_tables;--查看所有表名（其中包括系统表）
select * from tab/dba_tables/dba_objects/cat;--查看所有的表
----------------------------------------
相当于alias(别名),比如把user1.table1在user2中建一个同义词table1 
create synonym table1 for user1.table1; 
这样在user2中查select * from table1时就相当于查select * from user1.table1; 
----------------------------------------
查看数据归档模式：(要用dba身份)
archive log list;
设置日志为归档方式：
1. alter system set log_archive_start=true scope=spfile;
2. shutdown immediate
3. startup mount
4. alter database archivelog; (启用) 关闭则为 alter database noarchivelog;
5. alter database open;
----------------------------------------
热备份：
1. alter tablespace lottery begin backup;
2. copy数据库dbf文件到备份路径
3. alter tablespace lottery end backup;
4. alter system archive log current;
5. alter system switch logfile;
6. alter system switch logfile;(切换两次)
7. shutdown immediate;
8. 删除dbf数据文件
9. startup
ORA-01157: 无法标识/锁定数据文件 5 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 5: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DZW\LOTTERY.DBF'
10. select * from v$recover_file; --找丢失的数据文件号
11. alter database datafile 5 offline drop; --使数据文件脱机并删除文件
12. alter database open;
13. 把备份的文件拷回到数据库文件路径
14. recover datafile 5;
15. auto--指定日志为auto
16. alter database datafile 5 online;
----------------------------------------
重建控制文件：
备份控制文件里STARTUP NOMOUNT到ALTER DATABASE OPEN;之间的内容:control.txt
运行控制文件：@控制文件
----------------------------------------
重建日志文件：
recover database until cancel;--基于取消的恢复
alter database epen resetlogs;
----------------------------------------
select * from org start with org_id = 872 connect by prior org_id = parent_org_id;--start with connect by prior
----------------------------------------
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。

Total System Global Area 612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             171969412 bytes
Database Buffers          432013312 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
ORA-16038: 日志 2 序列号 57 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 2 线程 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\DZW\REDO02.LOG'
SQL> select group#,sequence#,archived,status from v$log;

    GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1         59 NO CURRENT
         3         58 NO INACTIVE
         2         57 NO INACTIVE

SQL> alter database clear unarchived logfile group 3;
数据库已更改。
SQL> shutdown immediate
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。

Total System Global Area 612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             171969412 bytes
Database Buffers          432013312 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
数据库已经打开。
SQL>
----------------------------------------
SQL> show parameter db_recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\product\10.2.0/flash _recovery_area
db_recovery_file_dest_size           big integer 2G
SQL> alter system set db_recovery_file_dest_size=3G scope=both;--增大日志文件
系统已更改。
SQL> alter database open;
----------------------------------------
truncate table ts;--截短表数据
----------------------------------------
--分页查询
SQL> select * from (select a.*,rownum rn from (select name from users) a where rownum<=10) b where b.rn>=6;
--删除重复记录：
delete from ts t where t.rowid<(select max(rowid) from ts t2 where t.id=t2.id);
----------------------------------------
--查询资源里的某个文本
select * from all_source where text like '%rpt_owe_min_age_02%';
-- 查询对象(table,procedure,package...)
select * from all_objects;    
----------------------------------------
select 1 from dual where decode(1,1,1,0)=1;
select 1 from dual where (case when 1=1 then 1 else 0 end) = 1;
----------------------------------------