create table stu
(
s_id number(6) ,
name varchar2(20) constraint stu_name_nnnot null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
c_id number(4) ,
email varchar2(50),
constraint stu_class_ref foreign key(c_id)references class,
constraint stu_id_pk primary key(s_id),
constraint stu_name_email_uniunique(email,name)
)
/
create table class
(
c_idnumber(4) primary key,
name varchar2(20)
)
第一范式(一对多)
1.要有主键
2.列不可分
第二范式(多对多)
1.不能存在部分依赖
oracle----PL/SQL
变量
1.binary_integer 整数,主要用来计数而不是用来表示字段类型
2.number 数字类型
3.char 定长字符串
4.date 日期
5.varchar2 变长字符串
6.long 长字符串,最长2G
7.boolean 布尔类型,可以取值true,false,null值
复合变量
自定义数组(下标可以为负)
declare
typetype_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnostype_table_emp_empno;
begin
v_empnos(0):= 7369;
v_empnos(2):= 7899;
v_empnos(-1):= 9999;
dbms_output.put_line(v_empnos(-1));
end;
/
---------
record变量类型
declare
typetype_record_dept is record
(
deptnodept.deptno%type,
dnamedept.dname%type,
locdept.loc%type
);
v_temptype_record_dept;
begin
v_temp.deptno:= 50;
v_temp.dname:= 'aaaa';
v_temp.loc:= 'bj';
dbms_output.put_line(v_temp.deptno|| ' '||v_temp.dname);
end;
/
------------
使用%rowtype声明record变量(好处跟随表的变化而变化)
declare
v_tempdept%rowtype;
begin
v_temp.deptno:= 50;
v_temp.dname:= 'aaaa';
v_temp.loc:= 'bj';
dbms_output.put_line(v_temp.deptno|| ' '||v_temp.dname);
end;
/
------------
--sql语句的运用
declare
v_enameemp.ename%type;
v_salemp.sal%type;
begin
selectename,sal into v_ename,v_sal from emp where empno = 7369;
dbms_output.put_line(v_ename|| ' '||v_sal);
end;
/
--if语句
--取出7369的薪水,如果<1200,则输出low,如果<2000,则输出middle,否则high
declare
v_salemp.sal%type;
begin
selectsal into v_sal from emp
whereempno = 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;
/
--如果7839人的薪水小于2500,薪水就剩于2,如果大于2500,就除于2,如果等于2500,就把薪水值打印出来
declare
v_salemp.sal%type;
begin
selectsal into v_sal from emp where empno = 7839;
if(v_sal< 2500) then
v_sal:= v_sal*2;
elsif(v_sal>2500) then
v_sal:= v_sal/2;
else
dbms_output.put_line('v_sal_values:'|| v_sal);
endif;
end;
--循环
(类似于java中的do-while循环)
declare
ibinary_integer := 1;
begin
loop
dbms_output.put_line(i);
i:= i+1;
exitwhen(i>=11);
endloop;
end;
/
(类似于java中的while循环)
declare
iInt := 1;
begin
whilei < 11 loop
dbms_output.put_line(i);
i:= i+1;
endloop;
end;
/
(类似于java中的for循环
begin
fork in 1..10 loop
dbms_output.put_line(k);
endloop;
fork in reverse 1..10 loop
dbms_output.put_line(k);
endloop;
end;
/
---错误处理
declare
v_tempnumber(4);
begin
selectempno into v_temp from emp where deptno = 10;
exception
whentoo_many_rows then
dbms_output.put_line('太多记录');
whenothers then
dbms_output.put_line('error');
end;
/
--错误信息的保存
--创建一个表存记录信息
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
)
/
--创建一个序列记录递增
create sequence seq_error_log_id start with1 increment by 1
/
declare
v_deptnodept.deptno%type := 10;
v_errcodenumber;
v_errmsgvarchar2(1024);
begin
deletefrom dept where deptno = v_deptno;
commit;
exception
whenothers then
rollback;
v_errcode:=SQLCODE;
v_errmsg:=SQLERRM;
insert into errorlog values(seq_error_log_id.nextval,v_errcode,v_errmsg,sysdate);
commit;
end;
/
-----
--显示错误具体时间
select to_char(errdate,'YYYY-MM-DDHH24:MI:SS') from errorlog
/
----
--游标
declare
cursorc is
select* from emp;
v_empc%rowtype;
begin
openc;
fetchc into v_emp;
dbms_output.put_line(v_emp.ename);
closec;
end;
/
--遍历所有名字
declare
cursorc is
select* from emp;
v_empc%rowtype;
begin
openc;
loop
fetchc into v_emp;
exitwhen(c%notfound);
dbms_output.put_line(v_emp.ename);
endloop;
closec;
end;
/
--------------
declare
cursorc is
select* from emp;
v_empc%rowtype;
begin
openc;
fetchc into v_emp;
while(c%found)loop
dbms_output.put_line(v_emp.ename);
fetchc into v_emp;
endloop;
closec;
end;
/
declare
cursorc is
select* from emp;
begin
forv_emp in c loop
dbms_output.put_line(v_emp.ename);
endloop;
end;
/
--存储过程 produre
create or replace produre p
is
cursorc is
select* from emp;
v_empc%rowtype;
begin
openc;
loop
fetchc into v_emp;
exitwhen(c%notfound);
dbms_output.put_line(v_emp.ename);
endloop;
closec;
end;
/
--exec p;
--begin
p;
end;
--带参数的存储过程
create or replace procedure p
(v_ain number, v_b number, v_ret out number, v_temp in out number)
is
begin
if(v_a> v_b) then
v_ret:= v_a;
else
v_ret:= v_b;
endif;
v_temp:= v_temp + 1;
end;
/
declare
v_anumber := 3;
v_bnumber := 4;
v_retnumber;
v_tempnumber := 5;
begin
p(v_a,v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
/
---函数
--触发器(trigger)
依附在一个表上
create table emp2_log
(
nname varchar2(20),
action varchar2(10),
atime date
)
/
建立触发器
create or replace trigger trig
afterinsert or update or delete on emp2 for each row
begin
ifinserting then
insertinto emp2_log values (USER,'insert',sysdate);
elsifupdating then
insertinto emp2_log values (USER,'update',sysdate);
elsifdeleting then
insertinto emp2_log values (USER,'delete',sysdate);
endif;
end;
/
更改数据
update emp2 set sal = sal*2 where deptno =30
/
--利用触发器更改具有约束条件的语句
--------树状结构的存储与展示
create table article
(
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1), --0代表非叶子节点,1代表叶子节点
alevel number(2)
)
/
insert into article values (1,'蚂蚁大战大象',0,0,0);
insert into article values (2,'大象被打趴下了',1,0,1);
insert into article values (3,'蚂蚁也不好过',2,1,2);
insert into article values (4,'瞎说',2,0,2);
insert into article values (5,'没有瞎说',4,1,3);
insert into article values (6,'怎么可能',1,0,1);
insert into article values (7,'怎么没有可能',6,1,2);
insert into article values (8,'可能性是很大',6,1,2);
insert into article values (9,'大象进医院类',2,0,2);
insert into article values (10,'护士是蚂蚁',9,1,3)
commit;
/
create or replace procedure p(v_pidarticle.pid%type,v_level binary_integer)
is
cursorc is select * from article where pid = v_pid;
v_preStrvarchar2(1024) := '';
begin
fori in 1..v_level loop
v_preStr:= v_preStr ||'*****';
endloop;
forv_article in c loop
dbms_output.put_line(v_preStr||v_article.cont);
if(v_article.isleaf= 0) then
p(v_article.id,v_level+1);
endif;
endloop;
end;
/
如果是插入日期类型数据的话,先用to_date转换再插入就可以了。
例:insert into 表(c_date)values(to_date('20120927 20:09:47','YYYYMMDD HH24:MI:SS'));