Oracle温习典故

--创建表空间
create tablespace teacher datafile
'f:/test/oracle/teacher.dbf' size 10m autoextend on;
--创建新用户
create user ajh
identified by "ajh"
default tablespace teacher;
--为新用户授权
grant connect,resource to ajh;
/*
动态SQL P130
 
定义：动态SQL是指在PL/SQL块编译时SQL语句是不确定的。
用途：可组合PL/SQL 语句动态执行
      可在PL/SQL中使用DDL语句
语法：
      execute immediate 动态语句字符串
      [into 定义变量列表]
      [using 绑定参数列表]
*/
--可在PL/SQL中使用DDL语句
begin
     execute immediate
     'create table student(
     stuId number(3),
     stuName varchar2(10)
     )' ;
end;
--测试
select * from student;
insert into student values(1,'aaa');
--可组合PL/SQL 语句动态执行
declare
sql_stmt varchar2(200);
emp_rec student%rowtype;
begin   
     --select * into emp_rec from student where stuid = '&stuId';  
     --sql_stmt := 'select * from student where stuId = :1';
     sql_stmt := 'select * from ' || '&tablename' || ' where stuId = :1';
     
     execute immediate sql_stmt into emp_rec using '&stuId';      
     
     dbms_output.put_line('学生姓名： ' || emp_rec.stuName);
end;


/*
键保留表  P99
定义：在联接视图中，如果视图包含了一个表的主键，并且也是这个视图的主键，则这个表
称为键保留表
用途：可以使用DML语句
数据字典 ：user_updatable_columns
总结：
一般对于主外键关系的表，外键表作为键保留表
对于外部联接的视图通常不会包含键保留表，除非外部联接生成 非空的值
*/
create table stuInfo
(
 stuId number(3) primary key,
 stuName varchar2(10)
)
insert into stuInfo values(1,'a');
insert into stuInfo values(2,'b');
insert into stuInfo values(3,'c');

create table stuMark
(
 markId number(3) primary key,
 stuId number(3),
 javaMark number(5,2)
)
insert into stuMark values(1,1,80);
insert into stuMark values(2,2,90);
insert into stuMark values(3,2,60);

--创建视图
--有+号，则说明是一个左外连接，但+号在哪个表后面，哪个表就代表着右表
create or replace view v_stu
as
select i.stuId,i.stuName,m.markId,m.javaMark from stuInfo i,stuMark m
where   i.stuId = m.stuId;
--select i.stuId,i.stuName,m.javaMark from stuInfo i
--left join stuMark m
--on i.stuId = m.stuId;

select * from v_stu;
--查询数据字典确认键保留表
select * from user_updatable_columns where table_Name = 'V_STU';

--测试，说明无法修改非键保留表内容
update v_stu set javaMark = 95 where stuId = 2;
update v_stu set stuName = 'bbb' where stuId = 2;

--对于键保留表，可以执行DML语句
update v_stu set javaMark = 65 where stuId = 2;
update v_stu set stuName = 'bbb' where stuId = 2;

--对于外部联接的视图通常不会包含键保留表，除非外部联接生成 非空的值
insert into stuMark values(3,70);
delete from stuMark where stuId = 3;

/*
索引组织表:
与在一个或多个列上建立索引的普通表相似,但它只维护一个索引
创建：organization index
优点：无需为表和索引维护两个单独的存储空间，节省存储空间
该表的数据存储在其关联的索引中，所以数据访问速度快

*/
create table stu_mark
(
 markId number(5) primary key,
 javaMark number(5,2)
);
create table stu_mark_org
(
 markId number(5) primary key,
 javaMark number(5,2)
);
drop table stu_mark;
drop table stu_mark_org;

--插入数据
--需要在command window下运行
set timing on;
set autotrace traceonly;
begin
   for i in 1..20000 loop
     insert into stu_mark values(i,80);     
   end loop;
   commit;
end;

begin
   for i in 1..20000 loop
     insert into stu_mark_org values(i,80);     
   end loop;
   commit;
end;
--插入数据时，普通表比索引组织表要快
--删除数据时，索引组织表快
select * from stu_mark where markId < 10000;
select * from stu_mark_org where markId < 10000;

delete from stu_mark;
delete from stu_mark_org;

/*
游标

*/
--显示游标
select * from emp where deptno = 20;

declare
e emp%rowtype;
cursor emp_cur(dept_no emp.deptno%type) is
     select * from emp where deptno =  dept_no;
begin
     open emp_cur(20);
     loop
         fetch emp_cur into e;
         exit when emp_cur%notfound;
         if e.job = 'CLERK' then
            dbms_output.put_line(e.ename || '  是一名普通职员');
         elsif e.job = 'ANALYST' then
            dbms_output.put_line(e.ename || '  是一名分析员');
         elsif e.job = 'MANAGER' then
            dbms_output.put_line(e.ename || '  是一名管理员');
         else
             dbms_output.put_line(e.ename || '  职位不明');
         end if;
     end loop;
     close emp_cur;
end;

--以上用循环游标替换
declare
cursor emp_cur(dept_no emp.deptno%type) is
     select * from emp where deptno =  dept_no;
begin
     for e in emp_cur(30)
     loop
         if e.job = 'CLERK' then
            dbms_output.put_line(e.ename || '  是一名普通职员');
         elsif e.job = 'ANALYST' then
            dbms_output.put_line(e.ename || '  是一名分析员');
         elsif e.job = 'MANAGER' then
            dbms_output.put_line(e.ename || '  是一名管理员');
         else
             dbms_output.put_line(e.ename || '  职位不明');
         end if;
     end loop;
end;

--ref游标
declare
type ref_type is ref cursor;
refcur ref_type;
p_id number;
p_name varchar2(100);
selection varchar2(1) := upper(substr('&你想查看什么信息？(E)(D):',1,1));
begin
     if selection = 'E' then
        open refcur for 'select empno,ename from emp where job = :job'
             using upper('&职位');
        dbms_output.put_line('====员工信息====');
     elsif selection = 'D' then
        open refcur for select deptno,dname from dept;
        dbms_output.put_line('====部门信息====');
     else
        dbms_output.put_line('员工信息(E)或是部门信息(D)');
        return;
     end if;
     loop
         fetch refcur into p_id,p_name;
         exit when refcur%notfound;
         dbms_output.put_line('#' || p_id || ':' || p_name);
     end loop;
     close refcur;
end;

/*
程序包
包规范和包主体
*/
create or replace package emp_pack
is
procedure emp_proc(e_no in number,e_name out varchar2);
function emp_fun(e_name varchar2) return varchar2;
e_table varchar2(10);
end;

create or replace package body emp_pack
is  
  procedure emp_proc(e_no in number,e_name out varchar2)
  is
  begin
       select ename into  e_name  from emp where empno = e_no;
       dbms_output.put_line(e_name);  
  end;
  function emp_fun(e_name varchar2) return varchar2
  is
  begin
       return e_table;
  end;
end;

declare
e_name varchar2(10);
begin
     emp_pack.e_table := 'emp';
     emp_pack.emp_proc(7369,e_name);
     dbms_output.put_line(emp_pack.emp_fun('aa'));
end;