oracle 学习笔记

/*  游标 cursor */declare  cursor c is   --声明游标时并不从数据库查询数据    select * from emp ;  v_emp c%rowtype;begin  open c;  --open游标才真正的查询数据保存到内存  loop     fetch c into v_emp;    exit when (c%notfound);    dbms_output.put_line(v_emp.ename);  end loop;close c;  --关闭游标end;/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;/declare  cursor c is       select * from emp ; begin  for v_emp in c loop      dbms_output.put_line(v_emp.ename);  end loop;end;/--带参数游标declarecursor c(v_deptno emp.deptno%type,v_job emp.job%type) isselect ename,sal from emp where deptno=v_deptno and job=v_job;--v_temp c%rowtype;beginfor v_temp in c(30,'CLERK') loopdbms_output.put_line(v_temp.ename);end loop;end;/--可更新游标declarecursor c is select * from emp2 for update;beginfor v_temp in c loopif(v_temp.sal<2000) thenupdate emp2 set sal=sal*2 where current of c ;elsif(v_temp.sal=5000) thendelete from emp2 where current of c;end if;end loop;commit;end;/

/*  存储过程 Procedure  创建存储过程必须有create procedure权限*/--打开显示set serveroutput on;--创建一个存储过程create or replace procedure p_update_empas cursor c is select * from emp for update;beginfor v_emp in c loopif(v_emp.deptno = 10) thenupdate emp set sal=sal+10 where current of c;elsif(v_emp.deptno = 20) thenupdate emp set sal=sal+20 where current of c;elseupdate emp set sal=sal+50 where current of c;end if ;end loop;commit;end;/--查询所有存储过程/*  这里比较好玩:根据一般数据字典表中的列命名,这里p_update应为procedure_name  但是不是,它是object_name,procedure_name为空*/select * from user_procedures;--执行存储过程 (方式一)begin  p_update_emp;end;--执行存储过程 (方式二)exec p_update_emp;--创建带参数存储过程/*in : 表输入参数out: 表输出参数不加关键字的表示默认输入参数两个都加的既表示输入参数又表示输出参数注意：存储过程的参数只能指定类型，而不能指定大小如  v_a number(2)是错误的，正确为v_a number*/create or replace procedure p_in_out(v_a in number,v_b number,v_ret out number,v_temp in out number)asbeginif(v_a > v_b) thenv_ret := v_a ;elsev_ret := v_b ;end if ;v_temp := v_temp +1 ;end;/--执行带参存储过程declarev_a number := 3 ;v_b number := 4 ;v_ret number;v_temp number := 5 ;beginp_in_out(v_a,v_b,v_ret,v_temp);dbms_output.put_line(v_ret);dbms_output.put_line(v_temp);end;/--结果打印 4 和 6 --删除存储过程drop procedure p_update_emp;  declare   v_a number :=1;  v_b number :=2;  v_set number:=3;  v_temp number :=4;begin  p_in_out(v_a,v_b,v_set,v_temp);  dbms_output.put_line(v_set);  dbms_output.put_line(v_temp);end;/

/*  触发器 trigger触发器必须依附于表才能起作用*/--创建日志表 emp_log,用于记录对表emp的所有操作日志create table emp_log(uname varchar2(20),action varchar2(10),atime date);--创建触发器/*触发条件:insert|delete|update 对某张表做插入|删除|更新的操作触发时间:after|before 操作之后|操作之前触发行:each row 操作影响一行触发一次，不写表求一次操作触发一次*/create or replace trigger trigafter insert or delete or update on emp for each rowbeginif inserting theninsert into emp_log(uname,action,atime) values(USER,'insert',sysdate);elsif updating theninsert into emp_log(uname,action,atime) values(USER,'update',sysdate);elsif deleting theninsert into emp_log(uname,action,atime) values(USER,'delete',sysdate);end if ;end;/--触发update emp set sal=sal*2 where deptno=30;delete from emp where empno = 7369 ;--查看日志表select * from emp_log;--? : 有外键关系时,主表中的主键存在外表的引用关系,因此不能随意更新,但可以用触发器解决这个问题    --如: update dept set deptno=99 where deptno=10--删除触发器drop trigger trig;create or replace trigger trigafter update on dept for each rowbeginupdate emp set deptno = :NEW.deptno where deptno = :OLD.deptno ;end;//*通常一条update语句会产生新旧两个状态 :NEW代表新状态 :OLD代表旧状态*/update dept set deptno=99 where deptno=10;

oracle 序列 和同义词

/*  序列 sequence :     序列号,在每次取的时候自动增加(第一次使用是初始值)     start with number : 从多少开始增长     increment by number : 每次增长多大     nomaxvalue : 没有最大值,不限制最在值     nocycle : 不循环     cache number : 预先在内存里存放一些sequence , 这样存取速度快.     nocache: 数据库突然不正常down掉(shutdown abort)，cache中的sequence就会丢失。 所以可以在create sequence的时候用nocache防止这种情况       使用序场所:  1. insert语句 values  2. update语句 set  */ --创建序列/*需要有 create sequence 或 create any sequence权限*/create sequence seq_up1 start with 1 increment by 1 nomaxvalue nocycle  cache 10;create sequence seq_up2 start with 10 increment by 2 maxvalue 100 cycle nocache;--使用序列select seq_up1.nextval from dual ;  --让序列增长,并返回下一个值select seq_up1.currval from dual ;  --返回序列当前值select seq_up2.nextval from dual ;select seq_up2.currval from dual ;insert into t10(id,name,address) values(seq_up2.nextval,'张二','不详');--修改序列alter sequence seq_up2 increment by 1 maxvalue 1000 ;--删除序列drop sequence seq_up2 ;/*     同义词 synonyms          优点：节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;         扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;         同义词可以创建在不同一个数据库服务器上，通过网络实现连接。      */--创建同义词conn sys/change_on_install ;  --以sys/change_on_install身份登录进去select * from emp ;           --查看emp提示无此表或视图select * from scott.emp ;     --可以查询,可知emp表是属于scott的create synonym emp for scott.emp ;  --创建同义词emp,它的作用是scott.emp的别名select * from emp ;           --利用创建的同义词(表的别名)来查询,ok--查看所有同义词select * from user_synonyms ;--删除同义词drop synonym emp ;--创建公共同义词create public synonym dept for scott.dept;--删除公共同义词drop public synonym dept ;

注：同义词的使用 通常在一个oracle 数据库中，有不同的方案，可以在一个具体的方案中，创建同义词来引用另外一个表中的数据，象是自己的表一样。

常用ORACLE  语句

drop TABLE FBI_BAI_TRANS_HIS; commit; --删除 表结构select * from all_users;    -- 查询当前系统下所有的用户select count(*) from dba_tables t where t.owner='TEST'; -- 查看当前用户下表的个数

oracle 删除用户下所有的表，以下SQL 会生成删除所有表的SQL.

select 'drop table '||table_name||';' from cat where table_type='TABLE'