oracle 视频笔记整理（3）

index 索引：当加主键约束或唯一约束，自动添加索引

  建立索引：

 

create index index_stu_email on stu(email); 

 

  删除索引：

 

drop index index_stu_email; 

 

 查看索引：

 

select index_name from user_indexes;

 

 

 增加读取的效率，降低修改的效率。

sequence 序列：seq.nextval下一个值 seq.currval当前值

建立序列：

 

create sequence seq start with 1 increment by 1 NOMAXVALUE NOCYCLE CACHE 10;

 

  

附加：hibernate中配置sequence

 

 

<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="Student" table="Student"> <id name="student_id" column="student_id" type="java.lang.Integer"> <generator class="native"> <param name="sequence">student_sequence</param> </generator> </id> <property name="student_name" column="Student_Name" type="java.lang.String"/> <property name="student_age" column="Student_Age" type="java.lang.Integer"/> </class> </hibernate-mapping> 

 

三范式：不存在冗余数据

第一范式：有主键，列不可分
第二范式：当一张表中有过个字段做联合主键，非主键不能存在部分依赖将一张表分开
第三范式：不能存在传递依赖

 

 

 

plsql：oracle内部的编程语言
sqlserver：transactsql

declare：声明变量
begin：程序开始
exception：例外
end：程序结束；

打开输出：
   

set serveroutput on;
 

dbms_output.put_line('error'); 

  

 
赋值： :=

例子：

 

declare v_num number:=0;//定义变量，赋值 begin v_num:=2/v_num; dbms_output.put_line(v_num);//具体过程操作 exception//捕捉异常 when others then dbms_output.put_line('error');//打印错误 end; / 

 

变量声明：
     --注释

变量类型：

  binary_integer:整数，用来技术
  number:数字类型
  char:定长字符串
  varchar2:变长字符串
  date：日期
  long：长字符串
  boolean：true,false,null

例子

 

delare v_temp number(1); v_date date:=sysdate; v_pi constant number(3,2):=3.14; v_valis boolean:=false;

 

--利用表字段定义变量属性 %type
   

 

v_salary emp.empno%type; 

  

 

复杂变量

 

table：相当于java里的数组 type:定义一中新类型 table：类型名

  declare
      type    type_table_emp_empno   is table of   emp.empno%type  index by   binary_integer
  --用上面定义的类型定义变量 
 

  v_empnos type_table_emp_empmo;     begin v_empnos(0):=0; v_empnos(1):=1; v_empnos(-1):=2;     end; 

 

 

 record:有点类似于类

 

declare type_record_dept is record ( deptno dept.deptno%type, dname dept.dname%type, loc dept.loc%type ); --用%rowtype类型声明record类型 -- v_temp dept%rowtpe v_temp type_record_dept; begin v_temp.deptno:=50; v_temp.dname:='wxy' ; end; plsql:select语句必须返回一条语句，必须有into变量 declare v_ename emp.ename%type; v_sal emp.sal%type; begin select ename,sal into v_ename,v_sal from emp where empno=7369; dbms_output.put_line(v_ename||''||v_sal); end; 

 

 plsql中执行ddl语句：execute immediate

 

begin execute immediate 'create table T(nnn varchar2(10) default ''aaa'')'; end; 

  
分支语句：if   then elsif then else end if；

 

declare v_sal emp.sal%type; begin select sal into v_sal from emp where empno=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;

  

循环语句：

1.loop   exit when   end loop;
2.while  loop  end loop;
3.for k in 1..10 loop
  end loop;
  for k in reverse(逆序循环) 1..10 loop
  end loop;

错误处理：show error 编译错误

例外的类别：too_many_rows（数据太多）,no data_found(没有数据)
           
     

exception when too_many_rows then when others then

 

 

 

传统记录错误：

建一个表存储错误

 

create table errorlog ( id number primary key, errcode number, errmsg varchar2(1024), errdate date );

 

 

--建一个序列，指向id create sequence seq_errorlog_id start with 1 increment by 1;

 

 

  

--创建程序

declare v_deptno dept.deptno%type:=10; v_errcode number; v_errmsg varchar2(1024); begin delete from dept where deptno=v_deptno; commit; exception when others then rollback; v_errcode:=SQLCODE; v_errmsg:=SQLERRM; insert into errorlog values (seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate); commit; end; 

  
  

游标：cursor指针

属性

 c%isopen:是否打开
 c%notfound：到了最后一条
 c%find:能找到记录

 

declare cursor c is select * from emp; v_emp c%rowtype; begin --打开游标 open c; --开始循环 loop --取一条记录 fetch c into v_emp; --当找不到记录了，退出 exit when (c%notfound); dbms_output.put_line(v_emp.ename); --结束循环 end loop; --关闭游标 close c; end; 

 

while循环： declare     cursor c is      select * from emp;      v_emp c%rowtype;  begin      open c;      fetch c into v_emp;      while(c%found) loop      dbms_output.put_line(v_emp.ename);      fetch c into v_emp;      end loop;      close c;   end;

 

 

for循环： --不需要open close --不需要定义变量 --不需要判断结束 declare    cursor c is       select * from emp; begin     for v_emp in c loop     dbms_output.put_line(v_emp.ename);     end loop; end; 

 

 

 带参数的游标：current of c:指向当前记录 

 

declare cursor c(v_deptno emp.empno%type,v_job emp.job%type) is select ename,sal from emp where deptno=v_deptno and job=v_job; begin for v_temp in c(30,'CLERK') loop dbms_output.put_line(v_temp.ename); --current of c 的应用 if(v_temp.sal<2000) then update emp ser sal=sal*2 where current of c; elsif(v_temp.sal=5000) then delete from emp where current of c; end if; end loop; end; / 

 

 

 
存储过程：
--建立存储过程 in 传入参数(默认)，out 传出参数 in out既传入又传出
--单独执行存储过程exec p;
--显示错误 show error;

 创建：

 

create or replace procedure p (v_a in 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; end if; v_temp:=v_temp+1; end;

 

--调用

 

declare v_a number:=3; v_b number:=4; v_ret number; v_temp number:=5; begin p(v_a,v_b,v_ret,v_temp); dbms_output.put_line(v_ret); dbms_output.put_line(v_temp); end;

 

 

 

--函数

create or replace function sal_tax(v_sal number) --声明返回值类型 return number is begin if(v_sal<2000) then return 0.10; elsif(v_sal<2750) then return 0.15; else return 0.20; end if; end;

 

 --调用函数
   

 

select sal_tax(sal) from emp;

 

触发器：

 for each row
 对每行的操作都做记录

 

 

create table emp2_log ( uname varchar2(20), action varchar2(10), atime date );

 

 

create or replace trigger trig after insert or delete or update on emp2 for each row begin if inserting then insert into emp2_log values(USER,'insert',sysdate); elsif updating then insert into emp2_log values(USER,'update',sysdate); elsif deleting then insert into emp2_log values(USER,'delete',sysdate); end if; end;
 

 

 

 rollback;自动退回。

 drop trigger trig; 删除触发器

 

 

--------利用触发器，修改外键

 

create or replace trigger trig after update on dept for each row begin update emp set deptno=:NEW.deptno where deptno=:OLD.deptno; end; 

 

例子：

 

 

create table article( id number primary key, cont varchar2(4000), pid number, isleaf number(1), alevel number(2) );

 

    插入值

  

 

insert into article values(10,'护士是蚂蚁',9,1,3);

 

  

create or replace procedure p_tree(v_pid article.pid%type,v_level binary_integer) is cursor c is select * from article where pid=v_pid; v_preStr varchar2(1024):=''; begin for i in 0..v_level loop v_preStr:=v_preStr||'---'; end loop; for v_article in c loop dbms_output.put_line(v_preStr||v_article.cont); if(v_article.isleaf=0) then p_tree(v_article.id,v_level+1); end if; end loop; end; 
 

 

   

 

 

exec p_tree；