--5个约束条件 constraint
a. 非空 null
b .唯一 unique 可以插入null
c .主键 primary key
d. 外键 被参考的字段必须是主键（class表）
e. check
create table stu
(
id number() primary key,
name varchar2(20) constraint stu_name_nn not null, --字段级唯一
sex number(),
age number(),
sdate date,
grade number() defalt 1,
class number() references class(id),
email varchar2(),
constraint stu_id_pk primary key(id),
constraint stu_class_fk foreign key (class) references class(id),
constraint stu_name_uni unique(email, name) --表级唯一
)
create table class
(
id number() primary key,
name varchar()
)
--修改表结构
alter table stu add (addr varchar2(100));
alter table stu drop (addr);
alter table stu modify (addr varchar2(50));
--删除添加约束条件
alter table stu drop constraint stu_class_fk;
alter table stu add constraint stu_class_fk foreign key(class) references class(id);
--oracle数据字典表
SQL>desc user_tables;
select table_name from user_tables;
select view_name from user_views;
select constraint_name from user_constraints;
--数据字典表的表
select * from dictionary
--索引
create index idx_stu_email on stu(email);
drop index idx_stu_email;
select index_name from user_indexes;
--views
create viewv$_stu as select id,name,age from stu;
--序列sequence
create sequence seq;
select seq.nextval from dual;
insert in to article values (seq.nextval, 'a','b');
--三范式
数据库范式(1NF_2NF_3NF_BCNF)详解请参考文档：http://download.csdn.net/detail/liu_liu213/4013839
定义：
第一范式-表要有主键、列不可分
第二范式-(多对多关系 以学号和教师编号为主键的表)当一张表中有多个字段作为主键时，非主键的字段不能只依赖部分主键（学生姓名依赖学号，二不依赖组合主键）;解决方法分成三张表
第三范式-不能存在传递依赖（有不依赖主键的栏位）
解释：
第一范式（1NF）
无重复的列
完全依赖于主键[消除非主属性对主码的部分函数依赖]
首先我们考虑，把所有这些信息放到一个表中(学号，学生姓名、年龄、性别、课程、课程学分、系别、学科成绩，系办地址、系办电话)下面存在如下的依赖关系。
（学号）→ (姓名, 年龄，性别，系别，系办地址、系办电话)
(课程名称) → (学分)
（学号，课程）→ (学科成绩)
首先我们考虑，把所有这些信息放到一个表中(学号，学生姓名、年龄、性别、课程、课程学分、系别、学科成绩，系办地址、系办电话)下面存在如下的依赖关系。
（学号）→ (姓名, 年龄，性别，系别，系办地址、系办电话)
(课程名称) → (学分)
（学号，课程）→ (学科成绩)
问题分析
因此不满足第二范式的要求，会产生如下问题
数据冗余： 同一门课程由n个学生选修，"学分"就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。
更新异常：
1)若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现同一门课程学分不同的情况。
2)假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字，课程名称和学分也无法记录入数据库。
删除异常 ： 假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。
解决方案
把选课关系表SelectCourse改为如下三个表：
学生：Student(学号，姓名, 年龄，性别，系别，系办地址、系办电话)；
课程：Course(课程名称, 学分)；
选课关系：SelectCourse(学号, 课程名称, 成绩)。
不依赖于其它非主属性[消除传递依赖]
接着看上面的学生表Student(学号，姓名, 年龄，性别，系别，系办地址、系办电话)，关键字为单一关键字"学号"，因为存在如下决定关系：
（学号）→ (姓名, 年龄，性别，系别，系办地址、系办电话)
但是还存在下面的决定关系
(学号) → (所在学院)→(学院地点, 学院电话)
即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。
它也会存在数据冗余、更新异常、插入异常和删除异常的情况。 (数据的更新，删除异常这里就不分析了，可以参照2.1.1进行分析)
根据第三范式把学生关系表分为如下两个表就可以满足第三范式了：
学生：(学号, 姓名, 年龄, 性别，系别)；
系别：(系别, 系办地址、系办电话)。
--P/L SQL
1.一个简单的程序
Set serveroutput on;
begin
dbms_output.put_line(“HelloWorld!”);
end;
2.一个简单程序块
declare
v_name varchar2(20);
begin
v_name := 'myname';
dbms_output.put_line(v_name);
end;
3.一个完整的简单程序
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;
4.变量声明尽量以V_开头
--变量声明的规则
a.变量名不能使用保留字，如from、select等
b. 第一个字符必须是字母
c. 变量名最多包含30个字符
d. 不要与数据库或者列同名
e. 每行只能声明一个变量
--常用变量类型
a. binary_integer:整数，主要用来计数而不是用来表示字段类型
b. number：数字类型
c. char:定长字符串
d. varchar2:变长字符串
e. date:日期
f. long:长字符串，最长2G
g. boolean:布尔类型，可以取值为true、false和null值
6.变量声明
declare
v_temp number(1);
v_count binary_integer :- 0;
v_sal unmber(7,2) := 4000.00;
v_date date := sysdate;
v_pi constant number(3,2) := 3.14;
v_valid boolean := false;
v_name varchar2(20) not null := 'MyName';
begin
dbms_output.put_line('v_temp value' || v_temp)
end;
7.变量声明 使用%type属性
声明变量的数据对应表的某个字段的数据，当表结构改变时（如把number(7,2)，改为number(8,3)时，必须修改相应程序），用%type属性可以解决该问题。
例:
declare
v_empno number(4);
v_empno2 emp.empno%type; --empno2数据类型于emp表中的empno字段相同；
v_empno3 v_empno2%type; --v_empno3数据类型于v_empno2 数据类型相同
begin
dbms_output.put_line('TEST')
end;
-----------------------自定义变量---------------------
1.Table类型(复合变量, 相当于java中的数组)
declare
type type_table_emp_empno is table of
emp.empno%type index by binary_integer;
v_empnos type_tabel_emp_empno;
begin
v_empnos(0) := 7369;
v_empnos(2) ：= 7893;
v_empnos(-1) ：= 9999；
end;
说明：table变量类型命名规则：type（自定义变量）-table（table类型变量）-emp（表emp）-empno（empno字段）；下标值可以为负值；
2.Record类型(复合变量, 类似于java中的类)
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dpt.loc%type
);
v_temp tyep_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'jb';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
但上述dept表变动时，此程序也得手动做相应改动，可以用下述方法自动改动
-使用%rowtype声明record类型(复合变量)
declare
v_tmp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'jb';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
PL/SQL中的SQL语句
1.SQL语句运用
有且只有一条返回值，且必须加into
例子：
declare
v_ename emp.ename%type;
v_sql emp.sql%type;
begin
--select 配合into 使用且保证有且只有一条
select enmae,sal into v_ename,v_sal from emp where empno=7369;
dbms_output.put_line(v_ename ||' ' ||v_sal );
end;
2. update 、delete、insert语句和sql中相同，可能和变量混用；
declare
v_deptno emp2.deptnpo%type:=10;
v_count number;
begin
update emp2 set sal =sal/2 where deptno =v_deptno;
dbms_output.put_line(sql%rowcount||'条记录被修改'); --sal%rowcount 返回记录数
commit;
end;
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output.put_line(v_emp.ename);
end;
declare
V_定义
begin
insert into dept2 values(v_deptno, v_dname, v_loc);
commit;
end;
3、执行ddl语句--（记住有execute immediate）
begin
execute immediate 'create table t(nn varchar(2) default ''a'')';
end;
4.IF语句
-取出7369的薪水，如果<1200,输出'low',<2000输出'middle',否则'high'
declare
v_sal emp.sal%type;
begin
select sal into v_sql from emp where empno=7369;
if (v_sal<1200) then
dbms_output.put_line('low');
elsif (v_sal<2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
5.循环语句
declare
i binary_number := 1;
begin
loop
dbms_output.put_line(i);
i := i+1;
exit when (i >= 11);
end loop;
end;
declare
i binary_number := 1;
begin
while (i < 11) loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
declare
i binary_number := 1;
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop --逆序
dbms_output.put_line(k);
end loop;
end;
6.错误处理
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
)
create sequence seq_errorlog_id start with 1 increment by 1;
declare
v_deptno dept.deptno%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
insert into errorlog values (seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate);
commit;
end;
7.--游标
-------------------游标（pl/sql重点）-----------------
1、plsql中select语句只能返回一条语句，要想返回多条语句，用游标。
2、简单循环
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;--fetch完后自动定位到下条记录
exit when(c%notfound);
--不能放在exit前，否则最后一条记录打印两次
dbms_output.put_line(v_emp.ename) ;
end loop;
close c;
end;
3、while循环
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found)loop
dbms_output.put_line(v_emp.ename) ;
fetch c into v_emp;
end loop;
close c;
end;
4、for循环 （循环时最简单）
declare
cursor c is
select * from emp;
begin
for v_emp in c loop --不用定义v_emp，不用打开关闭游标了
dbms_output.put_line(v_emp.ename) ;
end loop;
end;
5、带参数的游标
declare
cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is
select * from emp where deptno = v_deptno and job= v_job;
begin
for v_emp in c(30,'CLERK') loop
dbms_output.put_line(v_emp.ename) ;
end loop;
end;
6、可更新的游标
游标一般是作为记录集读取数据用的，但有时候用游标修改记录，这就是可更新游标；
declare
cursor c is
select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.sal <2000) then
update emp2 set sal =sal+1 where current of c ;
--修改定位到的当前记录，注意形式
elsif(v_emp.sal>=2000) then
delete from emp2 where current of c;
end if;
end loop;
commit; --提交
end;
-------------------------存储过程--------------------
1、把过程的declare变成 create or Replace produce p is 就行。
--declare
create or replace procedure p
is
cursor c is
select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.sal <2000) then
update emp2 set sal =sal+1 where current of c ;
elsif(v_emp.sal>=2000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;
创建了存储过程不代表运行了存储过程；
运行此存储过程 ：
方式一 exec p；
方式二
begin
p;
end;
2、带参数的存储过程
in 相当于程序里的参数，供传入用，在存储过程不能改变其值；
out 相当于程序里的返回值，在存储过程中可以为其赋值传出；
in out 既可以当参数又可以当返回值用；
不带上述说明符默认为in类型；
下例中v_a v_b 为in类型
v_c 为out类型
v_d 为in out 类型
create or replace procedure p
(v_a in number,v_b number,v_c out 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;
---> 调试时：
可以在命令窗口调试，出错时 用show errors 显示出错信息；
可以在plDv中调试；
---> 运行时：
可以在命令窗口运行：
declare
v_a number:=3;
v_b number:=4;
v_c number;
v_d number:=5;
begin
p(v_a,v_b,v_c,v_d);
dbms_output.put_line(v_c);
dbms_output.put_line(v_d);
end;
可以在plDv中调试；
------------------函数-------------------
1、它有返回值
create or replace function tax_tag(sal number)return number --计算税率
is
begin
if(sal > 1000)then
return 0.1;
elsif(sal>=2000)then
return 0.15;
else
return 0.2;
end if;
end;
select ename, tax_tag(sal) from emp ；-- 直接用函数tax_tag
551
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
