oracle学习日记

[color=gray]
仅供自己学习记录
set linesize 300
set pagesize 20
scott账户解锁 alter user scott account unlock;
给scott账户赋权限 创建视图 表 grant create table, create view to

scott;
去掉重复行 distinct
认定转义字符 select * from emp where ename like '%' escape'%' 此处%

失去作用
排序可以叠加
select * from emp order by sal desc,empno asc

当前时间 sysdate

转换小写lower() select lower(ename) from emp
大写uper()
substr(ename,2,3) 从第二个字符截 一共截取3个字符(不是第三个)
chr(65) a 从ASCII转换成字符
ascii('A') 65
四舍五入 round(23.4567,1) 23.5 1是小数后精度 round(23.4567)精度整数
to_char(sal,'$999,999.99') $800.00 $1,600.00
to_char(sal,'L000,000.00') 本地货币标志Y000,800.00 $001,600.00
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') 2009-07-07 17:55:08 HH标示

12进制 HH24
to_date('1981-2-20 00:00:00','YYYY-MM-DD HH24:MI:SS') 把字符串转换

成date类型 可在与数据比较时使用
to_number('$1,250.00','$9,999.99') 得到数字1250
nvl(comm,0)如果comm是空值 用0替代 解决运算表达式空值问题
分组group by要查询的表达式只能是用了组函数或者是在分组语句中用到的

select deptno,avg(sal) from emp group by deptno
having分组限制 having avg(sal)>2000

等值连接 select ename,dname from emp join dept on

(emp.deptno=dept.deptno)=select ename,dname from emp join dept

using(deptno)

导出数据 在要导出的文件夹cmd输入exp 输入账户名密码 一路回车
导出数据 imp
创建用户 create user lsg identified by lsg default tablespace users

quota 10m on users
分配权限
grant create session,create table, create view to lsg;

创建视图:
create view v$_这里写视图名 as 这里是select语句

constraint 约束
字段级约束 id number(6) unique, 表级约束 constraint 约束名 约束类型

(要约束字段) like constraint stu_name_email_uni unique(email,id)
字段级直接unique ,not, null, default, primary key, references 表名(

字段名)
表级要制定字段 primary key(id,name),constraint stu_class_fk foreign

key(class) reference class(id),

删除约束alter table stu drop constraint stu_class_fk
增加约束alter table stu add constraint stu_class_fk foreign key

(class) reference class(id)

alter table stu add(增加字段 address verchar2(20))
alter table stu modify(address varchar2(90))
alter table stu drop(删除字段 address)

数据字典表 desc user_tables table_name user_views view_name
select table_name from user_tables
数据字典表的表 dictionary

索引 create index idx_stu_email on stu(email)查询email效率高 插入

效率低
create index idx_stu_email_class on stu(email,class)查询email和

class组合效率高
drop index idx_stu_email;

sequence create sequence seq;
select seq.nextval from dual;
插入值的时候用seq.nextval
mysql:autoincrement

三范式:
1.要有主键,列不可分(不能有冗余数据)
2.多对多设计:不能存在部分依赖

题:
雇员中有哪些人是经理人
不准用组函数,求薪水的最高值
求平均薪水最高的部门的部门编号
求平均薪水最高的部门的部门名称
球薪水平均等级最低的部门的部门名称
select deptno from (select deptno,avg(grade) avg_grade from emp

join salgrade on sal between losal and hisal group by deptno) t1

join
(select min(avg(grade)) min_grade from emp join salgrade on sal

between losal and hisal group by deptno) t2 on avg_grade =min_grade

;
球比部门经理人中平均薪水最低的部门名称

球比普通员工的最高薪水还要高的经理人名称

求薪水最高的前五名雇员

球薪水最高的第6到10名雇员
用rownum rownum不能>某个数

没看懂:
select date '1800-1-1'+n-1 from dual,(select level n from dual

connect by level <=366) where date'1800-1-1'+n-1<date '1801-1-1'


create table class
(
id number(2) primary key,
name varchar2(20) not null
);


create table stu
(
id number(6) primary key,
name varchar2(20) not null,
sex number(1),
age number(2),
sdate date default sysdate,
grade number(2) default 1,
class number(2) references class(id),
email varchar2(50) unique
);


--pl/sql
打开在控制台显示语句:
set serveroutput on;
begin
dbms_output.put_line('hello');
end;
声明变量:declare v_num number :=0;赋值number :=0
异常控制:
declare v_num number:=0;
begin
v_num:=2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error');
end;
常用变量:
binary_integer:整数,主要是计数
number 数字类型
char 定长字符串
varchar2 变长字符串
date 日期
long 长字符串 最长2GB
boolean 可为true false null ;boolean类型不能被打印
常量用constant修饰 例如:v_pi constant number(3,2):3.14 ;
非空 v_name varchar2(20) not null:='name';
boolean类型不能被打印
可变类型:
declare

v_empno emp.empno%type:=100;

begin
dbms_output.put_line(v_empno);
end;

复合变量

table:相当于数组
declare
type type_table_emp_empno is table of emp.empno%type index by

binary_integer;
v_empnos type_table_emp_empno;
begin
v_empnos(0):=1234;
v_empnos(1):=4321;
v_empnos(-1):=2314;
dbms_output.put_line(v_empnos(-1));
end;

record:相当于类
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno:=50;
v_temp.loc:='上海';
v_temp.dname:='企划部';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
用%rowtype声明
declare
v_temp dept%rowtype;
begin
v_temp.deptno:=50;
v_temp.loc:='上海';
v_temp.dname:='企划部';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
error1:未找到数据
error2:司机返回的行数超出请求数

sql语句 有且只有一个返回值
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=7369;
dbms_output.put_line(v_ename||' '||v_sal);
end;
--关键:select .... into..v_..

被影响记录的数量 关键:sql%rowcount

执行ddl(数据库定义语句)关键字:execute immediate 'create table T

(name varchar2(20) default ''aaa'')';原先要用'的地方用两个'
begin
execute immediate 'create table t (nnn varchar2(20) defalut

''aaa'')';
end;

控制语句ifelse
--出错:
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7369;
if(v_sal<1200) then
dbms_output.put_line('l');
elsif(v_sal<2000) then
dbms_output.put_line('m');
else
dbms_output.put_line('h');
end if;
end;
注意 elsif这里没有e

循环
loop循环:相当于dowhile循环
declare i number:=1;
begin loop dbms_output.put_line(i);
i:=i+1;
exit when i>=11;
end loop;
end;

while loop循环:
declare i binary_integer:=1;
begin
while i<11 loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;

for循环:
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;

异常:declare v_temp number;
declare v_temp number;
begin select empno into v_temp from emp where deptno=10;
exception

when too_many_rows then
dbms_output.put_line('toomanyrows');

when others then
dbms_output.put_line('error');
when no_data_found then
end;

错误记录日志:
create table error_log
(
id number primary key,
errcode number,
errmsg varchar2,
errdate date
);
declare
v_sqlcode number;
v_sqlmsg varchar2(1024);
begin
delete from dept where deptno=10;
commit;
exception
when others then
rollback;

v_sqlcode:=sqlcode;
v_sqlmsg:=sqlerrm;

insert into error_log values(seq_error_id.nextval,

v_sqlcode,v_sqlmsg,sysdate);

commit;
end;

游标cursor:
declare cursor c is select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;

declare
cursor c is select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;

declare
cursor c(v_deptno emp.deptno%type,v_job emp.job%type)
is select ename,sal from emp where deptno=v_deptno and job=v_job;
begin
for v_temp in c(30,'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;

游标更新
declare cursor cs is
select * from emp2 for update;
begin
for v_temp in cs loop
if(v_temp.sal=2000) then
update emp2 set sal=sal*2 where current of cs;
end if;
end loop;
commit;
end;

存储过程:
create or replace procedure p is
select * from emp;
end;
exec p;
带参数的:
create or replace procedure p
(v_a in number,v_b number,v_out c number,v_d in out number)
is
begin
if(v_a>v_b)then v_c:=v_a;
else v_c:=v_b;
end if;
v_d:=v_d+1;
end;
默认是in参数
declare
v_a number:=1;
v_b number:=2;
v_c number:=3;
v_d number:=4;
begin
p(v_a,v_b,v_c,v_d);
dbms_output.put_line(v_c);
dbms_output.put_line(v_d);
end;

函数:
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal<2000) then
return 0.1;
elsif(v_sal<3000) then
return 0.15;
else return 0.2;
end if;
end;

触发器:
记录对emp的操作
create table emp_log
(
uname varchar2(20),
action varchar2(20),
atime date
);
创建触发器 对增删改查做出记录
create or replace trigger emp_tig
after insert or delete or update on emp (for each row)这里可省略
begin
if inserting then
insert into emp_log values(USER,'insert',sysdate);
elsif updating then
insert into emp_log values(USER,'update',sysdate);
elsif deleting then
insert into emp_log values(USER,'delete',sysdate);
elsif selecting then
insert into emp_log values(USER,'select',sysdate);
end if;
end;

更新后子表跟随
create or replace trigger tig
after update on dept for each row
begin
update emp set deptno=:new.deptno where deptno=:old.deptno;
end;

update dept set deptno=99 where deptno=30;不报错 子表更新引用
说明先触发触发器


牛B oracle也能用递归:
create table article
(
id number primary key;
content varchar2(4000);
pid number,--父节点
isleaf number(1)--0代表非叶节点 1代表叶节点
)
create or replace procedure p(v_pid aritcle.pid%type) is
cursor c is select * from article where pid=v_pid;
begin
for v_aritcle in c loop
dbms_output.put_line(v_article.coutent);
if(v_article.isleaf=0) then
p(v_article.id);
end if;
end loop;
end;
[/color]