oracle简单笔记学习4

create table student( --完整创建表的实例
id number(8) primary key, --SYS_C12345
name varchar2(20) not null;
email varchar2(30),
sex char,
mid number(3),
constraint student_id_pk primary key (id),
constraint student_email_uk unique (email),
constraint student_sex_ck check (sex in ('M','F')),
constraint student_mid_fk foreign key(mid) references major(id)
)
alter table student drop constraint student_mid_fk;
alter table student add constraint student_mid_fk foreign key (mid) references major(id); --外键的处理
alter table student drop constraint SYS_C12345;
alter table student add constraint student_id_pk primary key(id); ----添加主键约束条件
举例一个完整的数据库脚本
alter table xxx drop constraint........
----先删除所有的约束条件
conn openlab/open123
drop sequence xxx
drop view xxx
drop view xxx
----删除视图
drop table ...
----删除表
create table xxxx...
----创建表
create or replace view.....
----创建视图
constrain ...
----创建约束条件
insert
----增加基础数据
commit;
@ c:\script.sql

dml操作 insert /update/delete
事务 (Transaction)处理语句 commit,rollback,savepoint
ddl隐式的终止事务create / drop /alter /truncate(清除表数据，保留表结构)
truncate table student; drop table student ----->注意没有机会rollback
视图的本质就是sql查询语句
---------简单视图
create view emp_10
as select * from emp where deptno=10;
---------复杂视图
create view emp_sum as
select deptno,sum(sal) sum_sal from from emp group by deptno

create view emp_dept as
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

-------索引index
---sqlplus 命令
desc employee
-----sql命令
select * from employee;

------行内视图:查询语句出现在from后面 --匿名视图
---那些员工的工资比本部门的平均工资高
create view a
as
select deptno,avg(sal) avgsal
from emp group by deptno
--替换a
select ename,sal
from emp e join a on e.deptno=a.deptno
and e.sal>a.avgsal
----行内视图举例
select ename,sal from emp e join (select deptno,avg(sal) avgsal
from emp group by deptno) a on e.deptno=a.deptno
and e.sal>a.avgsal
-------子查询:查询语句出现在条件中
select * ename,sal
from emp
where sal >(select sal from emp where ename ='SCOTT');
---------伪列
rownum rowid ---oracle中独有
select rownum,ename,sal,rowid from emp;
rownum只能从1开始计算
－－－－mysql中 select ename,sal from emp limit 5, 5 -从第五条，向后加五条
取得第m到第n条记录的获取方式
select ename,sal
from(select ename,sal,rownum rn from emp where rownum<10)
where rn>5;
select ename,sal
from (select ename,sal,rownum rn from emp )
where rn<10 and rn>5;
------排名问题，Top-N分析,,,,行内查询
薪水最高的三个人，成绩最低的五个人
select ename,sal
from (select ename,sal from emp where sal is nut null order by sal desc)
where rownum<=3;
---------序列Sequence (一种数据库对象)
主要用于生成主键值，
create sequence myseq;
-----序列中两个伪列 :nextval , currval
select myseq.nextval from dual;

create table mytemp(id number primary key,
name varchar2(20));
insert into mytemp values(myseq.nextval,'sun');
select myseq.currval from dual;---取得当前的数字
当序列建立好以后，
------------起点是1000，，步进是10
create sequence myseq
start with 1000
increment by 10;

alter sequence myseq xxxxxxx;
alter sequence myseq increment by 2 ---更改步进

有哪些对象的种类
select distinct object_type from user_objects;
-------PL/SQL编程
--------匿名块 /函数/过程/存储过程/触发器/包
set serverout put on ---- sqlplus的命令
declare
v_count number :=0;
begin
select count(*) into v_count from emp;
dbms_output.put_line(v_count);
end;
/执行
在执行之前要使用set serveroutput on 这条sqlplus命令打开
--------------------------
declare
.........
begin
.......
[exception]
end;
/

declare
v_sal number :=0;
begin
select sal into v_sal
from emp where ename='SCOTT';
dbms_output.put_line('sal is '||v_sal);
exception
when too_many_rows then
dbms_output.put_line('too many row!!');
when no_data_found then
dbms_output.put_line('no data!!');
when others then
dbms_output.put_line('other exception');
end;
/

---函数
create or replace function sun_1(
v_sal number)
return number
is v_result number:=0;
begin
if(v_sal<1000) then
v_result:=0;
elsif (v_sal < 2000)then
v_result:=v_sal*0.01;
elsif (v_sal < 3000)then
v_result:=v_sal*0.02;
else v_result:=v_sal* 0.04;
end if;
return v_result;
end;
select ename,sal,sun_1(sal) from emp;
---------
create or replace function emp_count(v_deptno emp.deptno%type)
return number
is
v_count number;
begin
select count(*) into v_count from emp
where deptno=v_deptno;
return v_count;
end;
----------函数：必须返回数据，在sql语句中生效
----------过程:可以不返回数据,可以独立生效
create or replace procedure myproc(
v_deptno emp.deptno%type
)
is
v_count number;
begin
select count(*) into v_count
from emp where deptno =v_deptno;
dbms_output.put_line(v_count);
end;
set serveroutput on -------打开向屏幕上输出的开关
exec myproc(20)

---------有输出参数的过程
create or replace procedure cal_emp(
v_deptno emp.deptno%type,
v_sum_sal out emp.sal%type,
v_avg_sal out emp.sal%type)
is
begin
select sum(sal),avg(sal)
into v_sum_sal,v_avg_sal
from emp
where deptno=v_deptno;
end;
---------------------------------------------------------有参数的存储过程的测试如下
declare
v_sum number;
v_avg number;
begin
cal_emp(10,v_sum,v_avg);
dbms_output.put_line(v_sum);
dbms_output.put_line(v_avg);
end;
/
----------------------修改员工薪水
----如果员工的职位不是manager或者president，且薪水高于15000，则报错
------否则，修改指定员工的薪水为指定的数值
create or replace procedure changsal(
v_empno emp.empno%type,
v_sal emp.sal%type
)
is v_job emp.job%type;
begin
select job into v_job
from emp where empno=v_empno;
if(v_job not in ('MANAGER','PRESIDENT') and v_sal>15000)
then
dbms_output.put_line('too many sal');
else update emp set sal=v_sal
where empno=v_empno;
end if;
exception
when others then
dbms_output.put_line('some errs ');
end;
------------测试 exec changsal(7788,4000);