ORACLE笔记

Oracle连接数据库。Class.forName("oracle.jdbc.driver.OracleDriver");
c = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myoracle01", "scott", "abcd1234");


1


start d:\a.sql;
edit d:\a.sql;
spool d:\b.sql;
spool off;
show user; //显示当前有户名
show linesize;
set linesize 90;
set pagesize 10; //设置页面大小
create user xiaoming identified by m123; //创建用户并设置密码，普通用户无法创建，必须为sys或system用户才行
password 有户名 //修改密码
drop user 用户名 cascade //级联删除用户
grant connect to 用户名 //授权该用户connect角色，该角色拥有登陆数据库系统权限以及其他系统权限和一些对象权限，创建的用户默认无法登陆数据库。
dba //超级权限。
resource //该角色拥有在表空间创建表的权限。
desc 表名 //查看表结构。
grant select on  表名 to 用户名; //授权某一用户名的用户能够查看我的这张表。
grant update on  表名 to 用户名; //授权某一用户名的用户能够修改我的这张表。
grant all on  表名 to 用户名; //授权某一用户名的用户能够查看，修改，添加，删除我的这张表。
revoke select on 表名 from 用户名; //撤销该用户名的select权限。
grant select on  表名 to 用户名 with grant option //授权某一用户后，该用户可以继续向后传递权限，该权限是对象权限，该机制是株连式的，一旦对该用户撤销权限，后面的用户权限将都会撤销。
grant connect on  表名 to 用户名 with admin option //授权某一用户后，该用户可以继续向后传递权限，该权限是系统权限，该机制是株连式的，一旦对该用户撤销权限，后面的用户权限将都会撤销。
create profile lock1 limit failed_login_attempts 3 password_lock_time 2;//创建名为lock1的配置文件，该文件错误登陆三次将会锁定，锁定时间为2天。
alter user 用户名 profile 配置文件名 //向该用户添加某一配置，必须是DBA用户才行。
alter user 用户名 account unlock; //给该用户解锁，必须是DBA用户才行。
create profile myprofile limit password_life_time 10 password_grace_time 2; //创建一个名为myprofile的配置文件，该文件每隔10天要修改一次密码，宽限期限为2天，必须是DBA用户才行。
create profile myprofile limit password_life_time 10 password_grace_time 2 password_reuse_time 10; //建一个名为myprofile的配置文件，该文件每隔10天要修改一次密码，宽限期限为2天,口令10天后即可重用，必须是DBA用户才行。
drop profile myprofile cascade; //级联删除名为myprofile的配置文件，受此配置文件约束的用户全部释放。
select sysdate from emp; //显示当前时间，sysdate当前时间的关键字。


2，3


 create table student(
  xh number(4),
  xm varchar2(20),
  sex char(2),
  brithdate date,
  sal number(7,2)
  ); //创建一个名为student的表，该表拥有 xh，xm，sex，brithdate这些列。
create table emp2 as select * from emp; //创建一个和emp一模一样的表。
create user xiaogao identified by xiaogao defult tablespace users quota 10m on users; //默认在users表空间创建一个名为xiaogao的用户，密码是xiaogao,并分配10m空间。
create view v$_aaa as select empno from emp; //用指定sel-ect语句创建一个视图，视图默认用v&开头。
alter table student add studentClass number(4); //向表student中添加名为studentClass的列。
alter table student modify studentClass number(5); //把名为student的表中名为studentClass的列的数据类型改为number(5)。
alter table student drop column studentClass; //把名为student的表中名为studentClass的列删除。
drop table student; //删除名为student的表。
rename student to stu; //把名为student的表改名为stu。
alter session set nls_date_format = 'yyyy-mm-dd'; //修改date数据的输入格式。
insert into student values (001,'张三','男','2005-12-12',50000.00,12); //插入一行数据。
insert into student(xh,xm,sex) valuse(002,'李四','男'); //插入部分数据。
insert into student(xh,xm,sex) values(003,'john',null); //可以插入空值。
insert into dept2 select * from dept; //插入另一张表的数据。
select * from student where sal is null; //查询sal为null的行。
select * from student where sal is not null; //查询sal为非null的行。
update student set xm='tom',sex='man' where xh=004; //修改数据。
savepoint aa; //创建并保存一个名为aa的回滚点。
rollback to aa; //回滚操作到aa点。
delete from student; //删除名为student表的全部数据，表结构还在，写日志，可以恢复，速度慢
delete from student where xh=001; //删除一行数据
truncate table student; //删除名为student表的全部数据，表结构还在，不写日志，不可恢复，速度快。
clear; //清屏。
set timing on; //显示操作时间。


select count (*) from user; //显示user表中有多少条数据。
select distinct deptno，job from emp; //在emp表过滤掉deptno，job重复的数据。
select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp;
select ename,hiredate from emp where hiredate > '1982-1-1';
select ename,hiredate from emp where sal>=2500 and sal<=3000;
select ename,sal from emp where ename like 'S%'; //%指0或多个任意字符，对应的_指单个任意字符。
select ename,sal from emp where ename like '__O%';
select empno,ename,sal from emp where empno in(123,234,456);
select * from emp where mgr is null;
select * from emp order by sal asc; //升序。
select * from emp order by sal desc; //降序。
select * from emp order by deptno asc,sal desc; //按两种方式排序。
select empno,ename,job,sal*13+13*nvl(comm,0) "aaa" from emp where ename='KING';
select empno,ename,job,sal*13+13*nvl(comm,0) "aaa" from emp order by "aaa";
select min(sal),max(sal),sum(sal),avg(sal),count(sal) from emp;//各种函数。
select * from emp where sal > to_number('$1,320.56','$9,999.99'); //9不会填充，0会填充。
select * from emp where hiredate > to_date('1992-8-8 00:00:00' 'hh24:mi:ss'); //用指定格式进行日期查询。
select ename, sal from emp where sal=(select max(sal) from emp);//子查询。
select avg(sal),max(sal),deptno from emp group by deptno order by deptno;
select avg(sal),max(sal),avg(sal),deptno,job from emp group by deptno,job order by deptno;
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; //分组之后查询。
select a1.ename,a1.sal,a2.dname from emp a1, dept a2 where a1.deptno = a2.deptno; //多表查询。
select t1.ename, t2.ename from emp t1 join emp t2 on (t1.mgr=t2.empno); //多表查询，99年新语法，无需再where语句中指定，新语法用on进行筛选，表的连接必须用join，不能用逗号隔开当做连接。
select t1.ename, t2.ename from emp t1 left join emp t2 on (t1.mgr=t2.empno); //新语法左连接，右连接为right，全连接为full。
select t1.ename,t1.sal,t2.dname,t1.deptno from emp t1,dept t2 where t1.deptno = t2.deptno and t1.deptno = 10;
select t1.ename,t1.sal,t2.grade from emp t1,salgrade t2 where t1.sal between t2.losal and t2.hisal;
select t1.ename,t1.sal,t2.dname,t1.deptno from emp t1,dept t2 where t1.deptno=t2.deptno order by t1.deptno desc;
select ename from emp where empno=(select mgr from emp where ename = 'SMITH');
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno; //自查询。
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename='SMITH';
select ename from emp where deptno=(select deptno from emp where ename='SMITH');
select ename,sal,job,deptno from emp where job in(select job from emp where deptno=10);
select ename,sal,job,deptno from emp where job=any(select job from emp where deptno=10);
select ename,sal,deptno from emp where sal>any(select sal from emp where deptno = 30);
select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);
select * from emp t1,(select deptno,avg(sal) avg from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.s3al > t2.avg;
select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rn>=5; //分页查询，条件必须先小后大，rownum只接受< 或<=,变态的设计。
select count(*) from emp; //查询表中有多少记录。
create table myemp(id,mysal,dept) as select empno,ename,sal from emp; //创建一个名为myemp的表，并将emp表中的部分数据引入。
insert into kkk (id,myname,mysal) select empno,ename,sal from emp where deptno = 10;
update emp set(job,sal,comm)=(select job,sal,comm from emp where ename = 'SMITH') where ename = 'SCOTT';
commit; //提交事务。保存点全部消失。
set transaction read only; //设置只读事务。
select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) "bigchar" from emp;
select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) "smallchar" from emp;
select replace(ename,'A','我是老鼠') from emp;
round(comm,1); //四舍五入截取到小数点后1位。
trunc(comm,1); //截取到小数点后1位，无需四舍五入，省略第二个参数，默认截取到整数位。
floor(10.3); //向下取整，地板的意思。
ceil(10.3); //向上取整，天花板的意思。
mod(10,3); //取模，即求余。
select sysdate from emp; //显示当前系统时间。
to_date('2010-07-12','yyyy-mm-dd'); //设置时间格式。
add_mouth(d,n); //在当前日期下添加n个月。
select * from emp where sysdate > add_months(hiredate,120); //显示入职日期>10年的员工。
last_day(d); //扳回指定日期所在所在月份的最后一天。
select * from emp where last_day(hiredate)-2 = hiredate; //显示在所在月份倒数第三天入职的员工信息。
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
to_char(sal,l9999.99); //在工资前加上单位显示。用"."隔开。
select * from emp where to_char(hiredate,'yyyy') = 1980;
sys_context('userenv',db_name); //userenv是固定的。


4


shutdown; //关闭数据库。
startup; //开启数据库。
show parameter; //显示参数。
exp userid=scott/abcd1234@myoracle01 tables=(emp) file=d:\test1.dmp //导出指定表到指定位置，属于逻辑导出，必须在数据库启动情况下才能导出。
exp userid=scott/abcd1234@myoracle01 tables=(emp) file=d:\test1.dmp rows=n //导出指定表的结构到指定位置。
exp userid=scott/abcd1234@myoracle01 tables=(emp) file=d:\test1.dmp direct=y //直接导出，速度非常快。
exp system/abcd1234@myoracle01 owner=(system,scott) file=d:\system.dmp //导出指定用户名的方案。
desc user_tables; //所有的表都装在这张表中。
select table_name from user_tables; //显示当前用户所有表的名称。
d
esc user_views; //所有视图都在这张表中。
select view_name from user_views; //显示当前用户所有的视图，约束表同上。
create index emp_index on emp(empno,job); //创建组合索引。
三范式
1.第一个范式要求：要有主键，列不可分。
2.第二个范式要求：当一张表里有多个字段作为主键的时候，非主键的字段不能依赖部分主键，可以依赖主键的组合。
3.第三个范式要求：


6
begin 
  dbms_output.put_line('helloWord');
end;
/
  
declare
  v_name varchar2(10);
begin
  v_name:='abc';
  dbms_output.put_line(v_name);
end;
/


declare
  v_name number(1) not null :=0;
  v_pi constant number(5,2):=3.14; //用constant为常量。
begin
  v_name:=2/v_name;
  dbms_output.put_line(v_name);
exception
  when others then
  dbms_output.put_line('error');
end;
/


declare
  v_no number(1):=5555;
  v_ename emp.ename%type; //oracle的自动匹配类型。
  v_job emp.job%type;
begin
  dbms_output.put_line(v_no);
end;
/


declare
  type typt_new_name is table of emp.ename%type index by binary_integer; //声明一个数组类型，该数组数据类型为emp.ename类型，数组下表类型为binary_integer,这是一种复合类型。
  v_ename type_newname;
begin
  v_ename(0):='aaa';
  v_ename(1):='bbb';
  v_ename(-1)='ccc';
  dbms_output.put_line(v_no);
end;
/


declare
  type type_record is record  //声明一种类型，该类型由三种基本类型组成，类似c语言中的结构体。
    (empno emp.emono%type,
     ename emp.ename%type,
     job emp.job%type
    );
  v_temp type_record;
begin
  v_record.empno:=5555;
  v_record.ename:='tom';
  v_record.job:='manager';
  dbms_output.put_line(v_record.empno||'  '||v_record.ename);
end;
/


declare
  v_temp emp%rowtype; //使用%rowtype创建record复合类型变量。
begin
....
end;
/


declare
  v_id emp.empno%type;
begin
  select empno into v_id from emp where empno = 7902;
  dbms_output.put_line(v_id);
 end;
/


declare
  v_temp emp%rowtype;
begin
  select * into v_temp from emp where empno = 7839;
  dbms_output.put_line(v_temp.ename);
end; 
/


declare
  v_ename myemp.ename%type:='leo';
  v_id myemp.empno%type:='1234';
  v_job myemp.job%type:='manager';
begin
  insert into myemp(empno,ename,job) values(v_id,v_ename,v_job);
end;
/


declare
  v_temp number:=10;
begin
  update myemp set sal=sal/2 where deptno = v_temp;
  dbms_output.put_line(sql%rowcount); //sql%是关键字，rowcount是属性，表示多少条记录受影响，一般扳回几个值，就有几条记录收到影响。不确定。
end;
/


begin
  execute immediate 'create table t(id number(5),name varchar2(20) default ''abc'')';
end;
/  //在pl/sql中执行dcl语句可能需要两个单引号当一个单引号。


declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno = 7902;
  if(v_sal<1200) then dbms_output.put_line('low');
  elsif (v_sal>3000) then dbms_output.put_line('hight');
  else dbms_output.put_line('middle');
  end if;
end;
/  //if语句。


declare
  v_temp number:=1;
begin
  loop
    dbms_output.put_line(v_temp);
    v_temp:=v_temp+1;
    exit when (v_temp>=5);
  end loop;
end;
/  //相当于java中的do while循环。


declare
  v_temp number:=1;
begin
   while(v_temp<=12) loop
    dbms_output.put_line(v_temp);
    v_temp := v_temp + 1;
  end loop;
end;
 / //相当于java中的while循环。


begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
  for k in reverse 1..10 loop
    dbms_output.put_line(k);
  end loop;
end;
/  //相当于java中的for循环。


declare
  cursor c is select * from emp;
  v_temp c%rowtype;
begin
  open c;
  fetch c into v_temp;
  dbms_output.put_line(v_temp.ename);
  close c; 
end;
/  //游标的使用。


declare 
  cursor c is select * from emp;
  v_temp c%rowtype;
begin
  open c;
  loop
    fetch c into v_temp;
    exit when(c%notfound);
    dbms_output.put_line(v_temp.ename);
  end loop;
  close c;
end;
/  //游标结合循环。


declare
  cursor c(v_job emp.job%type,v_deptno emp.deptno%type) is select * from emp where job=v_job and deptno=v_deptno;
begin
  for v_temp in c('MANAGER',20) loop
    dbms_output.put_line(v_temp.ename || v_temp.job);
  end loop;
end;
/  //游标结合for循环，无需声明变量，for循环会自动打开或关闭游标，推荐使用for循环。


create or replace procedure p is
  cursor c is select * from myemp for update;
begin
  for v_temp in c loop
    if(v_temp.deptno=10) then
      update myemp set sal=sal+10 where current of c;
      elsif(v_temp.deptno=20) then
      update myemp set sal=sal+20 where current of c;
      else update myemp set sal = sal + 20 where current of c;
      end if;
  end loop;
end;
/  //创建一个存储过程p。


exec p; //执行存储过程。


create or replace procedure p1(v_input1 in number, v_output out number, v_input2 in number, v_put in out number) is
begin
  if(v_input1<v_input2) then v_output:=v_input2;
  else v_output:=v_input1;
  end if;
  if(v_put<>0) then v_put:=100;
  end if;
  dbms_output.put_line(v_output || '  ' || v_put);
end;
/  //创建一个带有参数的存储过程。


declare
  v_a number:=4;
  v_b number:=6;
  v_ren number;
  v_temp number:=10;
begin
  p1(v_a,v_ren,v_b,v_temp);
end;
/  //执行这个存储过程。


drop procedure p;  //删除存储过程。


create procedure mypro01 is
begin
  insert into testpro values('gao','abcd1234');
end;
/ //创建过程，必须以“/”结尾，若create 后面有or replace则代表如果有相同的存储过程，则覆盖此过程。


create or replace function set_sax(sal_num number)
return number
is
begin
  if(sal_num>2000) then return 0.2;
  elsif(sal_num>1000) then return 0.1;
  else return 0;
  end if;
end;
/  //创建一个名为set_sax的函数。


create or replace trigger trig
after insert or update or delete on myemp for each row    //如果后面跟上"for each row"则代表没更新一行数据触发一次操作。
begin
  if(inserting) then
    insert into myemp_log values(user,'insert',sysdate);
  elsif(updating) then
    insert into myemp_log values(user,'update',sysdate);
  elsif(deleting) then
    insert into myemp_log values(user,'delete',sysdate);
  end if;
end; 
/  //创建一个触发器，after代表出发后执行，相对的before代表触发器执行，inserting，updating，deleting是关键字，user也是关键字，代表当前用户。




show error; //显示错误信息。
exec mypro01; //编译存储过程。






create or replace procedure myempsort(v_id myemp.mgr%type) is
cursor c is select * from myemp where mgr = v_id;
v_name c%rowtype;
begin
  open c;
  loop
    fetch c into v_name;
    exit when(c%notfound);
    dbms_output.put_line(v_name.ename);
    if(v_id in (7566,7698,7782,7788,7839,7902,0)) then
      myempsort(v_name.empno);
    end if;
  end loop;
  close c;
end;
/  //myemp表的树状结构的展现，一下午的成果。