【笔记】oracle练习

创建表空间 分配用户权限
/*分为四步 */
/*第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;