Oracle学习笔记（五）
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);
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。