欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

【笔记】oracle练习

程序员文章站 2022-05-13 17:34:51
创建表空间 分配用户权限 /*分为四步 */ /*第1步:创建临时表空间 */ create temporary tablespace user_temp tem...
创建表空间 分配用户权限
/*分为四步 */
/*第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;