oracle自学总结.docx

1、针对oracle数据管理针对各种类型，如index，table，view，SYNONYM，comments等objects都有各自对应的管理表，如all_indexs user_indexs dba_indexs等字段其中tab表，主要管理当前用户下（及user）下的table，view，及synonym信息左右连接另外写法select * from cr_corp_inf,cr_xd where cr_corp_inf.cifno=cr_xd.cifno(+);select * from cr_corp_inf,cr_xd where cr_corp_inf.cifno(+)=cr_xd.cifno;没有（+）内容需要全部展现同义词create synonym du for dual;创建同义词成功后，可以照常在同义词上update，insert，select内容。同样可以在同一词上面创建索引。其索引其实是建在真正表上面。删除表时，该表上的索引系统会一同被drop掉。但synonym不会被自动drop掉。需要用户自己drop表上创建约束条件create table parts( pcode char(2) primary key,pname varchar2(10) not null,color varchar2(10) constraint jk check( color in (RED,CREEN,BLUE),QQH number(4) ,ROL number(4),City varchar2(15) not null,Weight number(2);-create table orders( Orderno number(2) primary key,Scode char(2) constraint jk1 references supplier(scode),pcode char(2) constraint jk2 references parts(pcode),qty_supplied number(4) constraint jk3 check(qty_supplied =0),order_date date);-说明，此references约束条件中。则supplier或者parts表中的scode或者pcode必须为primary key 唯一主键的前提下，-该约束条件才能建立成功，不然会报此列表的唯一关键字或者主键不匹配的错误create table price(scode char(2),pcode char(2),price number(7,2),primary key (scode,pcode);-联合主键删除主键命令alter table parts drop constraint jk;alter table parts add constraint jk check (color in (RED,GREEN,BLUE);-存储过程重定向输出到某个文件或者log日志-先修改或添加init.ora的UTL_FILE_DIR参数 例如： utl_file_dir=G:oracletemp declare file_handle UTL_FILE.FILE_TYPE; begin file_handle := UTL_FILE.FOPEN( /tmp , 文件名 , w ); UTL_FILE.PUTF(file_handle, 写入的信息n ); UTL_FILE.FCLOSE(file_handle); Exception WHEN utl_file.invalid_path THEN raise_application_error(-20000, ERROR: Invalid path for file or path not in INIT.ORA. ); end; -PutF()过程用来以指定格式把文本写入一个文件 -Put_Line()过程把一个指定的字符串写入文件并在文件中开始新的一行 在pl-sql 下面打开dbms_output.put_line()命令set serveroutput on;set serveroutput off;-存储过程或者程序快中循环的使用declarev_empno number(4);beginselect max(empno)+1 into v_empno from emp;for i in 1.100 loopinsert into emp(empno) values (v_empno+i);end loop;commit;end;-接受两个数相除并且显示结果。如果第二个数为0,-则显示消息didnt your mother tell you not to dibide by zero;declarex number(4);y number(4);z number(4);beginx:=&n1;y:=&n2;z:=x/y;exceptionwhen zero_divide then -dbms_output.put_line(didnt your mother tell you not todivide by zero);end;-编写一个PL/SQL 程序块以提升两个资格最老的职员为-高级职员;declarecursor c1 is select * from emp order by hiredate;beginfor rec in c1 loopupdate emp set job=MANAGER whereempno=rec.empno;exit when c1%rowcount=2; -end loop;commit;end;-编写一个PL/SQL 程序块,对所有雇员按他们基本薪水的10%给他们加薪.如果所增加的薪水大于5000卢布，则取消加薪。declarecursor c1 is select * from emp order by hiredate;beginfor rec in c1 loopif rec.sal*0.1 5000 thennull; -elseupdate emp set sal=sal+sal*0.1 whereempno=rec.empno;end loop;commit;end;-如果薪水在此范围内，则显示消息Salary is ok“，否则,-更新薪水为该范围内的最小值.create or replace function test_function(vempno number) return number isv_job varchar2(12);v_hig number;v_low number;v_sal number;beginselect job,sal into v_job,v_sal from emp where empno=vempno;if v_job=CLERK thenv_low :=1500;v_hig :=2500;end if;if v_job=SALESMAN thenv_low :=2501;v_hig :=3500;end if;if v_job=ANALYST thenv_low :=3501;v_hig :=4500;end if;if v_job not in (CLERK,SALESMAN,ANALYST) thenv_low :=4500;end if;if v_sal =v_low and v_sal=v_hig thendbms_output.put_line(salary is ok);commit;return 1; -elseupdate emp set sal=v_low where ename=v_mc;commit;return 0; -end if;end;-编写一个数据包,它有两个函数和两个过程以操作EMP表-该数据包要执行的任务为:-插入一个新雇员。-删除一个现有雇员。-显示指定雇员的整体薪水。-显示指定雇员的部门名称。create or replace package test asprocedure insrec(empno in number,ename in varchar2);procedure delrec(empno in number);function salsum(empno in number) return number;function getdname(empno in number) return varchar2;end;create or replace package body test asprocedure insrec(empno in number,ename in varchar2) isbegininsert into emp(empno,ename) values (empno,ename);dbms_output.put_line(1 rows inserted);end;procedure delrec(empno in number) isbegindelete emp where empno=empno;dbms_output.put_line(1 rows deleteed);end;function salsum(empno in number) return number isv_sal number;beginselect nvl(comm,0)+sal into v_sal from emp;return v_sal;end;function getdname(empno in number) return varchar2 isv_dname varchar2(12);beginselect b.dname into v_dname from emp a ,dept bwhere a.deptno=b.deptno and a.empno=empno;return v_dname;end;end;-编写一个数据库书呆子被教导追求正确答案，而受欢迎的小孩被教导讨人喜欢以显示任何时候雇员加薪时的加薪情况.create or replace trigger mytriafter update on empfor each rowbegindbms_output.put_line(:old.sal| |:new.sal);end;-编写一个数据库触发器，它允许用户只在上午9.00到下午-5.00之间执行DML 任务.create or replace trigger mytribefor update or delete or insert on empfor each rowbeginif to_char(sysdate,HH24)17 thenraise_application_erro(-20011,sorry); -end if;end;-编写一个数据库触发器以检查某个组织中不能有两个总裁.create or replace trigger triafter insert or update on empdeclaren1 number;beginselect count(job) into n1 from emp where job=PRESIDENT;if n1