Oracle学习笔记（五）

举例一个完整的数据库脚本
--从此处开始复制,存成文件名为xxx.sql
--先删除所有约束条件
alter table xxx drop constraint....
--删除序列
drop sequence xxx
--删除视图
drop view xxx
drop view aaa....
--删除表
drop table xxx.....
--创建表
create table xxx...
--创建视图
--创建序列
--创建约束条件
--增加基础数据
 
 
DML操作 insert / update / delete
事务(Transaction)处理语句:commit/rollback/savepoint
DDL:create / drop / alter / truncate
SQL>truncate table student;
SQL>drop table student;
 
 
其它数据库对象:视图/索引
视图的本质-->sql查询语句
--简单视图
create view emp_10
as select * from emp where deptno = 10;
--复杂视图
create view emp_sum
as
select deptno, sum(sal) sum_sal from emp
group by deptno;
 
create view emp_dept
as
select e.ename, d.dname
from emp e join dept e
on e.deptno = d.deptno;
 
--索引index
 
 
20110225
--行内视图:查询语句出现在 from 后面
--匿名视图
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 from emp
where rownum < 5;
 
--第5条到第10条记录的获取方式
--这种方式仅适用于oracle数据库
select ename, sal
from (select ename, sal, rownum rn
      from emp )  
where rn between 5 and 10;
 
--排名问题,Top-N分析
--薪水最高的三个人,成绩最低的五个人
--错误的例子:
select ename, sal from emp  
where rownum <= 3
order by sal desc;
--正确的例子:
select ename, sal from  
(select * from emp  
where sal is not null  
order by sal desc)where rownum <= 3;
 
--序列Sequence
--一种数据库对象.主要用于生成主键值.
create sequence myseq_ning;
--序列的两个伪列:nextval, currval
--nextval获得序列的下一个值
--currval获得序列的当前值
--当序列建好以后,必须先执行nextval,才能执行currval.
select myseq_ning.nextval from dual;
select myseq_ning.currval from dual;
 
create table mytable_ning(id number primary key, name varchar2(20));
 
insert into mytable_ning values(myseq_ning.nextval, 'chris');
 
select * from mytable_ning;
 
--创建序列,起点是1000,步进是10
create sequence mysequ_ning
start with 1000
increment by 10;
 
user_objects;
user_tables;
user_sequences;
user_views;
...
--获得openlab用户名下所有的对象种类.
select distinct object_type from user_objects;
 
--PL/SQL编程
--匿名块 / 函数 / 过程 / 包 / 触发器
--打开输出,sqlplus命令
set serveroutput on
 
declare
v_count number := 0;
begin
select count(*) into v_count
from emp;
dbms_output.put_line('total num is '||v_count);
end;
/
 
 
begin
dbms_output.put_line('Hello World');
end;
/
 
[declare
....]
begin
...
[exception
...]
end;
 
declare
v_sal number := 0;
begin
select sal into v_sal
from emp where ename = 'aaa';
dbms_output.put_line('sal is '||v_sal);
exception  
when too_many_rows then
dbms_output.put_line('too many rows!');
when no_data_found then
dbms_output.put_line('no data!!');
when others then
dbms_output.put_line('some error!');
end;
/
 
0-1000   0%
1001-2000 1%
2001-3000 2%
3001-5000 4%
5001-.... 5%
 
--函数
create or replace function tax_ning(
    v_sal number)
    return number
is
    v_result number;
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, tax_ning
 
(sal) from emp;
 
--函数输入参数:deptno,输出参数:部门人数
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;
--测试
select emp_count(10) from dual;
 
 
--函数:必须返回数据,在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;
--测试过程的方式
exec myproc(20)
 
--有输出参数的过程
create or replace procedure calcu_emp(
    v_deptno  in  number(2),
        v_sum_sal out number(7,2),
      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
    calcu_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 changesal(
    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;
   commit;
end if;
exception
  when others then
    dbms_output.put_line('some error!');
end;
 
 
--测试方式
 exec changesal(7698, 20000);