创建表空间 分配用户权限
/*分为四步 */
/*第1步：创建临时表空间 */
create temporary tablespace user_temp
tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第2步：创建数据表空间 */
create tablespace user_data
logging
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第3步：创建用户并指定表空间 */
create user username identified by password
default tablespace user_data
temporary tablespace user_temp;
/*第4步：给用户授予权限 */
grant connect,resource,dba to username;
---frist day
create table d_student( --创建学生表
id number(8)
constraint d_s_pk primary key,
s_name varchar(20),
sex varchar(10)
constraint sex_chk check(sex in ('man','woman')),
dob date
)
create table d_clazz( --创建班级表
id number(8)
CONSTRAINT d_c_pk PRIMARY KEY,
c_name varchar(20)
)
alter table d_student add
clazz_id number(8) constraint d_c_dl references d_clazz(id); --添加外键
desc d_clazz;
alter table d_student drop column clazz_id; --删除列
alter table d_student modify s_name varchar(20); --更改列
Truncate table d_clazz; --清空表数据
insert into d_clazz values (3,'三年级');
insert into d_student values(1,'鸣人','man',to_date('2000-08-01','yyyy-MM-dd'),1);
insert into d_student values(2,'佐助','man',to_date('2000-06-21','yyyy-MM-dd'),1);
insert into d_student values(3,'小樱','woman',to_date('2000-05-13','yyyy-MM-dd'),1);
insert into d_student values(4,'宁次','man',to_date('1999-11-28','yyyy-MM-dd'),2);
insert into d_student values(5,'小李','man',to_date('2000-01-01','yyyy-MM-dd'),2);
commit;
select * from d_student;
create view d_v_student as select s.id id, s.s_name name, c.C_NAME clazz_name from d_student s left join d_clazz c on s.CLAZZ_ID = c.ID order by s.id --创建视图
select * from d_v_student order by id desc;
create view d_v_clazz as (select a.clazz_id id , c.c_name name, a.s_sum from D_CLAZZ c right join (select clazz_id ,count(*) s_sum from d_student s group by s.CLAZZ_ID ) a on c.id = a.clazz_id) with read only; --创建一个班级只读视图
drop view d_v_clazz;
select * from d_v_clazz;
--pl/sql 程序
set serveroutput on
declare
v_student d_v_student%rowtype;--集合类型
v_name varchar(20);--标量类型
v_clazz D_V_STUDENT.CLAZZ_NAME%type;--记录类型
CURSOR v_students is select * from d_v_student;--游标类型
begin
select * into v_student from d_v_student where id = 1;
dbms_output.put_line(v_student.name);
select name into v_name from d_v_student where id = 1;
dbms_output.put_line(v_name);
select clazz_name into v_clazz from d_v_student where id = 1;
dbms_output.put_line(v_clazz);
open v_students; --打开游标
loop
fetch v_students into v_student; --遍历数据
dbms_output.put_line(v_student.name);
-- 游标属性：
-- Cursor_name%FOUND 布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；
-- Cursor_name%NOTFOUND 布尔型属性，与%FOUND相反；——注意区别于DO_DATA_FOUND(select into抛出异常)
-- Cursor_name%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
-- Cursor_name%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。
EXIT WHEN v_students%NOTFOUND; --退出
end loop;
close v_students; --关闭游标
end;
/
--游标的for循环
--PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；
--当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据；
--当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理；
--当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。
--格式：
-- FOR index_variable IN cursor_name[(value[, value]…)] LOOP
-- -- 游标数据处理代码
-- END LOOP;
set serveroutput on
declare
v_student d_v_student%rowtype;--集合类型
CURSOR v_students(i_id number) is select * from d_v_student where id = i_id;
begin
for v_student in v_students(1) loop
dbms_output.put_line(v_student.name||'所在班级：'||v_student.clazz_name);
EXIT WHEN v_students%NOTFOUND; --退出
end loop;
end;
/
--分支结构 if/case
set serveroutput on
declare
v_student d_student%rowtype;--集合类型
v_clazz_name D_CLAZZ.C_NAME%type;
CURSOR v_students is select * from d_student;
begin
for v_student in v_students loop
select c_name into v_clazz_name from d_clazz where id = v_student.clazz_id;
-- 使用 if
if v_student.sex='man' then
dbms_output.put_line(v_student.s_name||'性别男 '||'所在班级：'||v_clazz_name);
elsif v_student.sex='woman' then
dbms_output.put_line(v_student.s_name||'性别女 '||'所在班级：'||v_clazz_name);
else
dbms_output.put_line(v_student.s_name||'性别不详');
end if;
-- 使用 case
case v_student.sex
when 'woman' then
dbms_output.put_line(v_student.s_name||'性别女 '||'所在班级：'||v_clazz_name);
when 'man' then
dbms_output.put_line(v_student.s_name||'性别男 '||'所在班级：'||v_clazz_name);
else
dbms_output.put_line(v_student.s_name||'性别不详');
end case;
end loop;
end;
/
--循环语句
set serveroutput on
declare
v_i int :=1;
v_sum int :=0;
begin
-- loop
loop
exit when v_i>10;
v_sum := v_sum+v_i;
v_i := v_i+1;
end loop;
SYS.DBMS_OUTPUT.PUT_LINE(v_sum);
-- for
v_i := 0;
v_sum := 0;
for v_i in 0..10 loop
v_sum := v_sum+v_i;
end loop;
SYS.DBMS_OUTPUT.PUT_LINE(v_sum);
-- while
v_i := 0;
v_sum := 0;
while v_i<11 loop
v_sum := v_sum+v_i;
v_i := v_i+1;
end loop;
SYS.DBMS_OUTPUT.PUT_LINE(v_sum);
end;
/
--异常的捕获及自定义异常
set serveroutput on
declare
other EXCEPTION;
e_clazz exception;
e_not_clazz exception;
v_clazz d_student.clazz_id%type;
begin
v_clazz := &clazz;
if v_clazz>3 and v_clazz<6 then
raise e_not_clazz;
end if;
insert into d_student values(7,'鸣人','man',to_date('2000-08-01','yyyy-MM-dd'),v_clazz);
if v_clazz>2 then
raise e_clazz;
end if;
exception
when e_not_clazz then
SYS.DBMS_OUTPUT.PUT_LINE('该班级不存在');
when e_clazz then
SYS.DBMS_OUTPUT.PUT_LINE('该班级已经满员');
rollback;
when other then
SYS.DBMS_OUTPUT.PUT_LINE('请核对数据'||sqlcode||sqlerrm);
rollback;
end;
/
--返回自定义游标变量 存在问题
set serveroutput on
declare
type student_r is record(
name d_v_student.name%type,
clazz_name d_v_student.clazz_name%type
);
stu_r student_r;
type student_cur is ref cursor return student_r;
stu_cur student_cur;
begin
if not stu_cur%isopen then
open stu_cur for select name ,clazz_name from d_v_student;
end if;
-- loop
-- fetch stu_cur into stu_r ;
-- exit when stu_cur%notfound;
-- SYS.DBMS_OUTPUT.PUT_LINE(stu_r.name);
-- end loop;
colse stu_cur;
end;
/
---the next day
select * from d_v_student;
--参数游标
--游标表达式 语法 cursor(subquery)
set serveroutput on
DECLARE
in_id d_v_student.id%type;
stu d_v_student%rowtype;
CURSOR stu_c(v_id number) is SELECT * FROM d_v_student where id = v_id;
begin
in_id := &stu_id;
for stu in stu_c(in_id) loop
SYS.DBMS_OUTPUT.PUT_LINE(stu.name);
end loop;
end;
/
--游标变量
--游标变量是动态的 它与游标的关系就像一般的类型 与变量一样
--游标在打开时 可以取得不同的游标值（可以看作存放不同值得集合或容器）
set serveroutout on
declare
type stu_cur is ref cursor;
stu_r stu_cur;
stu d_v_student%rowtype;
begin
if not stu_r%isopen then
open stu_r for select * from d_v_student;--打开游标
end if;
loop
fetch stu_r into stu ;
exit when stu_r%notfound;
SYS.DBMS_OUTPUT.PUT_LINE(stu.name);
end loop;
close stu_r;--关闭游标
end;
/
--游标变量与记录绑定
--1.type ref_type_name is ref cursor [return return_type]
--2.cursor_variable ref_type_name
--3.open cursor_variable for select_statement
set serveroutput on
declare
type t_stu is RECORD(
name varchar(20)
);
v_stu t_stu;
type t_stu_c is ref cursor return t_stu;--指定返回类型
v_stu_c t_stu_c;
begin
if not v_stu_c%isopen then
open v_stu_c for select name from d_v_student ;
end if;
loop
fetch v_stu_c into v_stu;
exit when v_stu_c%notfound;
SYS.DBMS_OUTPUT.PUT_LINE(v_stu.name);
end loop;
end;
/
--复合数据类型
--记录类型
--s1 student%rowtype; --表记录 （根据现成的表）
--cursor student_sur is select * from student where id = 1 ;
--s2 student_sur%rowtype--游标定义 （根据查询结果生成的表）
--type student_r is record( field_name1 type1);
--s3 student_r;--显示定义 （自定义数据结构）
--记录表等暂时省略
--定义过程
create procedure d_p_student(v_id d_v_student.id%type)
as
v_name d_v_student.name%type;
begin
select name into v_name from d_v_student where id = v_id;
dbms_output.put_line(v_name);
end d_p_student;
/
--调用过程
call d_p_student(5);
--删除过程
drop procedure d_p_student;
-- 测试成功
create or REPLACE procedure d_p_students(v_id d_v_student.id%type, v_name out d_v_student.name%type)
as
-- v_name d_v_student.name%type;
begin
select name into v_name from d_v_student where id = v_id;
end d_p_students;
/
--variable v_clazz varchar;
variable v_name varchar2(20);
execute d_p_students(1,:v_name);
print :v_name ;
--函数
create or replace function d_f_student(v_id d_v_student.id%type)
return d_v_student.name%type
as
v_name d_v_student.name%type;
begin
select name into v_name from d_v_student where id = v_id;
return v_name;
end d_f_student;
/
--调用自定义函数
set serveroutput on
begin
SYS.DBMS_OUTPUT.PUT_LINE(d_f_student(1));
end;
/
--删除函数
drop function d_f_student;
create or replace function d_f_student(v_id d_v_student.id%type, v_clazz out D_V_STUDENT.CLAZZ_NAME%type)
return d_v_student.name%type
as
v_name d_v_student.name%type;
begin
select name ,clazz_name into v_name ,v_clazz from d_v_student where id = v_id;
return v_name;
end d_f_student;
/
variable v_clazz varchar2(20);
variable v_name varchar2(20);
execute :v_name := d_f_student(1,:v_clazz);
print :v_name :v_clazz;
--包
--触发器
---Third day
select * from d_clazz;
select * from d_student;
select * from d_v_student;
select * from d_v_clazz;
select * from d_v_teacher;
drop view d_v_student;
create OR REPLACE view d_v_student as select s.id, s.s_name name , c.c_name clazz from d_student s left join d_clazz c on s.clazz_id = c.id order by s.id;
create table d_teacher(
id number(5)
constraint d_t_pk primary key,
t_name varchar(20),
course varchar(20),
salary number(5),
clazz_id number(5)
constraint d_t_dl
references d_clazz(id)
);
desc d_student;
insert into d_teacher VALUES (3, '卡卡西1','组长',50,1);
insert into d_student values (6,'天天','woman',to_date('2001-08-09','yyyy-MM-dd'),2);
commit;
--插入多行
insert all into d_teacher VALUES (2, '阿凯','组长',50,2)into d_teacher VALUES (3, '阿凯','组长',50,2) select * from dual;
delete d_teacher where id = 3;
--创建视图
create or REPLACE view d_v_teacher as
select s.id ,s.s_name,s.sex,s.dob,a.c_name,a.t_name,a.course,a.salary from
d_student s left join (select * from
d_teacher t left join d_clazz c on c.id = t.CLAZZ_ID) a on s.clazz_id=a.clazz_id order by id;
drop PROCEDURE d_p_student;
----记录类型
--使用记录表
set serveroutput on
declare
type stu_tab is table of d_v_student%rowtype index by binary_integer;
v_stus stu_tab;
v_clazz d_v_student.clazz%type;
cursor stu_cur is select * from d_v_student where clazz = v_clazz;
i int :=1;
begin
v_clazz := '一年级';
open stu_cur;
loop
fetch stu_cur into v_stus(i);
exit when stu_cur%notfound;
DBMS_OUTPUT.PUT_LINE(v_stus(i).name);
i := i+1;
end loop;
close stu_cur;
end;
/
--联合数组类型
set serveroutput on
declare
type stu_tab is table of varchar(20) index by binary_integer;
v_stus stu_tab;
v_clazz d_v_student.clazz%type;
cursor stu_cur is select name from d_v_student where clazz = v_clazz;
i int :=1;
begin
v_clazz := '一年级';
open stu_cur;
loop
fetch stu_cur into v_stus(i);
exit when stu_cur%notfound;
DBMS_OUTPUT.PUT_LINE(v_stus(i));
i := i+1;
end loop;
close stu_cur;
end;
/
--嵌套表,变长数组
--过程
create or replace procedure d_p_student(v_id d_student.id%type,v_name out d_student.s_name%type)
as
v_student d_student%rowtype;
begin
select * into v_student FROM d_student where id = v_id;
-- SYS.DBMS_OUTPUT.PUT_LINE(v_student.s_name);
v_name := v_student.s_name;
end d_p_student;
/
set serveroutput on
variable v_name varchar2(20);
execute d_p_student(1,:v_name);
print :v_name;
--函数
create or replace function d_f_student(v_id d_v_student.id%type, v_clazz out D_V_STUDENT.clazz%type)
return d_v_student.name%type
as
v_name d_v_student.name%type;
begin
select name ,clazz into v_name ,v_clazz from d_v_student where id = v_id;
return v_name;
end d_f_student;
/
variable v_clazz varchar2(20);
variable v_name varchar2(20);
execute :v_name := d_f_student(1,:v_clazz);
print :v_name :v_clazz;
create or replace function d_f_students(v_id d_v_student.id%type, v_name out d_v_student.name%type)
return d_v_student.clazz%type
as
v_clazz d_v_student.clazz%type;
begin
select name , clazz into v_name, v_clazz from d_v_student where id = v_id;
return v_clazz;
end d_f_students;
/
var v_name d_v_student.name%type;
var v_clazz d_v_student.clazz%type;
execute :v_clazz := d_f_students(1,:v_name);
print :v_name :v_clazz;
--创建包
create or replace package body d_pack_student as
function d_f_student(v_id d_v_student.id%type, v_name out d_v_student.name%type)
return d_v_student.clazz%type
as
v_clazz d_v_student.clazz%type;
begin
select name , clazz into v_name, v_clazz from d_v_student where id = v_id;
return v_clazz;
end d_f_student;
procedure d_p_student(v_id d_student.id%type,v_name out d_student.s_name%type)
as
v_student d_student%rowtype;
begin
select * into v_student FROM d_student where id = v_id;
-- SYS.DBMS_OUTPUT.PUT_LINE(v_student.s_name);
v_name := v_student.s_name;
end d_p_student;
end d_pack_student;
/
--调用包
var v_name d_v_student.name%type;
var v_clazz d_v_student.clazz%type;
execute :v_clazz := d_pack_student.d_f_students(1,:v_name);
print :v_name :v_clazz;
--触发器 (有问题)
createt or replace trigger change_student
before insert or update or delete on d_student
begin
if(to_char(sysdate,'hh24')not between '8' and '17' ) or (to_char(sysydate,'dy','nls date_langudage = american') in ('sat','sun'))
then
raise_application_error(-20000,'在非法时间不能改变数据);
end if;
end change_student;
