ITPub博客

首页 > 数据库 > Oracle > Oracle--语句4--设计表,存储过程,方法,触发器,数据导入导出

Oracle--语句4--设计表,存储过程,方法,触发器,数据导入导出

Oracle 作者:zxm524 时间:2011-01-19 23:27:27 0 删除 编辑

-----------------------------------------------表设计----------------------------------------------------
1.表设计

一对多
员工和部门
create table emp (
empno number(4) primary key,
ename varchar2(20) not null,
deptno number(2) references dept(deptno)
);
create table dept(
deptno number(2) primary key,
dname varchar2(20) not null
);
deptno dname
10 财务部
empno ename deptno
3000 张三 10
3001 李四 10
对于一对多关系,关系字段应当放在多的一方
一对一 student , computer  外键+唯一约束
create table student(
sid number(4) primary key,
name varchar2(20) not null
);
create table computer(
cid number(4) primary key,
name varchar2(20) not null,
sid number(4) unique references student(sid);
);
sid name
1 张三
2 李四
cid name sid
5 联想 1
6 惠普 2
7 神舟 2 非法
多对多 学生, 课程
create table student(
sid number(4) primary key,
name varchar2(20) not null
);
create table course (
cid number(4) primary key,
name varchar2(20) not null
);
create table sc (
id number(4) primary key,
cid number(4) references course(cid),
sid number(4) references student(sid)
);
sid name
1 张三
2 李四
cid name
7 java
8 oracle
sid cid id
1 7 1
1 8 2
2 7 3
-----------------------------------------------表设计----------------------------------------------------

--------------------------------------------- 数据库设计时应当遵循的规范: 范式-----------------------------------------------------------
第一范式 -- 属性不可再分
create table student (
sid number(4) primary key,
name varchar2(20) not null,
fav varchar2(200)
);
sid name fav
1 张三 '游泳,游戏,电影'
2 李四 '游戏,打牌'
select * from student where fav like '%打牌%';
create table student (
sid number(4) primary key,
name varchar2(20) not null
);
create table fav(
name varchar2(20) , -- 爱好名称
sid number(4) references student(sid);
);
sid name
1 张三
2 李四
name sid
游泳 1
游戏 1
电影 1
游戏 2
打牌 2
select * from student inner join fav on student.sid = fav.sid where fav.name = '打牌';
第二范式 -- 其他普通属性不应当存在部分依赖于主要属性 -> 此普通属性应当被分离到新的表中去
主要属性(看这一列或多列是否能够作为这张表的主键)
orderid + productid = 主要属性
部分依赖 完全依赖 部分依赖
普通 普通    普通
orderid orderdate  count productid   productname
10 今天  5 1 java编程
10 今天  3 2 oracle编程
20 今天  4 2 oracle编程

修改为
orderid orderdate
10 今天  
20 今天
productid   productname
1 java编程
2 oracle编程
orderid count productid  
10 5 1
10 3 2
20 4 2
第三范式 -- 普通属性不能间接依赖主要属性 -> 分离出去
间接依赖
主要属性 普通 普通 普通
empno ename deptno dname
8888 张三 10 财务部
8889 李四 10 财务部
9000 王五 20 市场部
修改为
empno ename deptno
8888 张三 10
8889 李四 10
9000 王五 20
deptno dname
10 财务部
20 市场部
自关联
empno ename mgr
1000 张三 null
1001 李四 1000
1002 王五 1000
create table emp (
empno number(4) primary key,
ename varchar2(20) not null,
mgr number(4) references emp (empno) 
);
--------------------------------------------- 数据库设计时应当遵循的规范: 范式-----------------------------------------------------------
--------------------------------------业务逻辑直接写在数据库中---------------------------------------------
2.存储过程,触发器,视图
业务逻辑直接写在数据库中
set serveroutput on -- 打开服务器输出
BEGIN
     -- System.out
     dbms_output.put_line('hello, world');
END;
/

定义变量
DECLARE
a   number(4) :=  10;
BEGIN
     dbms_output.put_line('a的值是:' || a);
END;
/

-- 条件判断
DECLARE
a   number(4) :=  10;
BEGIN
if a = 10 then
dbms_output.put_line('a=10');
elsif a > 10 then
dbms_output.put_line('a>10');
else 
dbms_output.put_line('a<10');
end if;
END;
/
--------------------------------------业务逻辑直接写在数据库中---------------------------------------------

--------------------------------------------------循环----------------------------
-- while 循环
declare 
i number(4);
begin
i := 0;
while i < 10 loop
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
/
--直接写loop循环
declare 
i number(4);
begin
i := 0;
loop
exit when i >= 10;
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
/

-- 这种比较特殊,不需要定义参数.for后面的临时变量名不需要在declare 中定义
begin
for i in 0..9 loop
dbms_output.put_line(i);
end loop;
end;
/
--------------------------循环----------------------------
-- dml (insert ,update, delete...) 需要自己控制事务
-- select 
begin
update emp set sal = 1000 where empno = 7369;
commit;
end;
/

declare 
name varchar2(20);
begin
-- 用在pl/sql中,必须结合into关键字使用,实际表示赋值操作 :=
select ename into name from emp where empno = 7369; -- 查询7369员工的姓名,赋值给name变量
dbms_output.put_line(name);
end;
/
-- 限制1:不能用select  into查询多条记录 会抛出 too_many_rows
-- 限制2: 不能查询不到数据 no_data_found
------------------------------------------------------------游标---------------------------------------------
-- 游标 -- jdbc ResultSet 
 %type 获取某表某列的数据类型
declare 
cursor rs is select ename, sal from emp;
-- name varchar2(20);
-- salary number(7,2);
name emp.ename%type;
salary emp.sal%type;
begin
open rs; -- 打开游标, 执行游标对应的查询语句
loop
fetch rs into name, salary;
exit when rs%notfound;
dbms_output.put_line('姓名:' || name || ' 工资:' || salary);
end loop;
close rs; -- 关闭游标
end;
/
  %rowtype 让一个变量的定义包含整个行所有列
declare
cursor rs is select * from emp;  --定义一个游标.
tmp emp%rowtype;
begin
open rs; -- 打开游标, 执行游标对应的查询语句
loop
fetch rs into tmp;   --循环获得游标的值.
exit when rs%notfound;
dbms_output.put_line('姓名:' || tmp.ename || ' 工资:' || tmp.sal ||  ' 部门编号:'  || tmp.deptno );
end loop;
close rs; -- 关闭游标
end;
/

-- 第二种遍历游标的写法 使用for循环遍历游标 可以省略 open , close , fetch
declare
cursor rs is select * from emp;
begin
for tmp in rs loop
dbms_output.put_line('姓名:' || tmp.ename || ' 工资:' || tmp.sal ||  ' 部门编号:'  || tmp.deptno );
end loop;
end;
/
------------------------------------------------------------游标---------------------------------------------
=============================================================================================================

----------------------------------------------定义存储过程------------------------------------------
create procedure showemp is   --没有了declare语句,直接就定义一个游标.
cursor rs is select * from emp;
begin
for tmp in rs loop
dbms_output.put_line('姓名:' || tmp.ename || ' 工资:' || tmp.sal ||  ' 部门编号:'  || tmp.deptno );
end loop;
end;
/
调用方式1: exec 过程名
调用方式2: 
begin
showemp();
end;
/

-- 加法运算
-- 存储过程没有返回值,只能通过输出参数返回值
create procedure addnumber(a  number, b number) is
begin
dbms_output.put_line('结果:' || (a+b));
end;
/

create or replace procedure addnumber(a number, b number, c out number) is  --最后一个记得要写out
begin
-- dbms_output.put_line('结果:' || (a+b));
c := a + b;
end;
/

declare
d number(10);  --这个时候一定要先初始化,不然报错.
begin
addnumber(11, 33, d);
dbms_output.put_line('结果:' || d);
end;
/
----------------------------------------------定义存储过程------------------------------------------


=============================================================================================================
下午
==================================================自定义方法===========================================================
create or replace function addfun (a number, b number) return number is --方法也进行加减操作只定义了两个参数
begin
return a + b;  --方法可以有返回值,存储过程就仅仅是给第三个数赋值了而已.
end;
/

调用方法(1)
declare 
c number;
begin
c := addfun(33, 22);
dbms_output.put_line('结果:' || c);
end;
/

调用方法(2) 
select addfun(33,22) from dual;  --方法调用之后生成的是一张表

======================================================自定义方法=======================================================


-------------------------------------------java 代码调用存储过程和函数----------------------------------------------
-- 更新员工工资
CREATE OR REPLACE PROCEDURE updateEmp(empnoIn emp.empno%TYPE, salIn emp.sal%TYPE) IS
BEGIN
       UPDATE emp SET sal = salIn WHERE empno = empnoIn;
COMMIT;
END;
/

-- 根据员工编号查询员工姓名
CREATE OR REPLACE PROCEDURE findEmp(empnoIn emp.empno%TYPE, enameOut OUT emp.ename%TYPE) IS
BEGIN
       SELECT ename INTO enameOut FROM emp WHERE empno = empnoIn; --  no_data_found
EXCEPTION
       WHEN no_data_found THEN 
     enameOut := NULL;
-- others 表示所有类型异常
END;
/

-- 嵌套表(oracle 中一种数组类型)
                        类型名称          是数组类型              数组中的元素类型
CREATE OR REPLACE TYPE myArray         IS TABLE OF                NUMBER(4);
/


DECLARE
       list1 myArray := myArray(10,20,30,40); -- list1变量时自定义的myArray类型, 并初始化
BEGIN
       -- dbms_output.put_line(list1(2));     -- 获得数组中元素值
list1(3) := 50;                         -- 修改数组中元素值
FOR i IN list1.FIRST .. list1.LAST LOOP
    dbms_output.put_line(list1(i));
END LOOP;
END;
/

-- 根据员工编号删除多个员工
-- [7369, 7499, 7521]
CREATE OR REPLACE PROCEDURE deleteEmps(empnos myArray) IS 
BEGIN
       FOR i IN empnos.FIRST .. empnos.LAST LOOP
    DELETE FROM emp WHERE empno = empnos(i);
END LOOP;
COMMIT;
EXCEPTION 
       WHEN OTHERS THEN ROLLBACK;
END;
/


-- 配合jdbc使用 , 用ref 游标可以向jdbc端返回一个结果集, 不能用for循环遍历
DECLARE
   -- CURSOR rs IS SELECT * FROM emp;  
TYPE curType IS REF CURSOR RETURN emp%ROWTYPE; -- 定义了一个游标类型,  游标引用的结果集中的数据类型是员工表的数据类型
rs curType;                                    -- 定义一个游标变量
tmp  emp%ROWTYPE;
BEGIN
   OPEN rs FOR SELECT * FROM emp;
LOOP 
     FETCH rs INTO tmp;
EXIT WHEN rs%NOTFOUND ;
dbms_output.put_line('编号:' || tmp.empno);
END LOOP;
CLOSE rs;
END;
/

-- 查询所有员工
CREATE OR REPLACE PROCEDURE findAllEmps(emps OUT SYS_REFCURSOR) IS
       TYPE curType IS REF CURSOR RETURN emp%ROWTYPE;
rs curType;   
BEGIN
       OPEN rs FOR SELECT * FROM emp;    -- rs结果集
emps := rs;                         -- 把结果集作为输出参数返回
END;
/
-------------------------------------------java 代码调用存储过程和函数----------------------------------------------


=============================================================================================================
视图
create table bbb as select * from emp;
create or replace view ccc as select * from emp; -- 查询视图实际就是运行 视图定义时所对应的select 语句
1) 简化复杂的sql语句
--视图里没有真正存储数据,因此基表数据发生了改动,视图也会发生修改  -- 虚拟表
2) 实现复杂的权限管理
3) 视图也可以增删改数据(其实是修改基表),但限制很多, 主要把它用作查询
CREATE OR REPLACE VIEW deptemp AS 
       SELECT empno, ename, sal, emp.deptno, dept.dname FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno;

=============================================================================================================
触发器
DELETE FROM emp WHERE empno = 7369;


-- 希望删除员工数据的同时做备份操作
INSERT INTO LOG( ID, NAME) VALUES( 7369, 'SMITH ');
                 -- 触发 可以响应 插入 删除 更新等事件
-- 触发器 一般属于某张表
-- 在员工表上的删除操作发生前会执行触发器内的代码,每删一行,触发一次
CREATE OR REPLACE TRIGGER emp_tri BEFORE DELETE ON emp 
FOR EACH ROW
DECLARE
    
BEGIN
    -- :OLD 表示正在被删除的记录
    INSERT INTO LOG( ID, NAME) VALUES( :OLD.empno, :OLD.ename);
END;
/


-- 插入员工记录, 保证编号不重复
CREATE SEQUENCE emp1_seq START WITH 8000;

INSERT INTO emp (ename , deptno) VALUES ('李四', 10);
INSERT INTO emp (ename , deptno) VALUES ('张三', 10);

CREATE OR REPLACE TRIGGER emp_tri_insert BEFORE INSERT ON emp
FOR EACH ROW
DECLARE
BEGIN
    -- :NEW 表示正被插入的记录
SELECT emp1_seq.NEXTVAL INTO :NEW.empno FROM dual;
END;
/

3.数据导入导出
EXP 导出程序
IMP 导入程序


4.习题
-- 第三部分
01.
SELECT rq, COUNT( CASE 
                      WHEN shengfu = 'WIN' THEN 1
                  END ) WIN, 
COUNT( CASE 
                      WHEN shengfu = 'LOSE' THEN 1
                  END ) LOSE FROM tmp GROUP BY rq;
 
05
SELECT MIN(ID), MAX(ID) FROM TEST GROUP BY (ID-ROWNUM) ORDER BY (ID-ROWNUM);  


<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23601849/viewspace-1116717/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-03-27