Oracle学习笔记（五） 电脑资料

Oracle学习笔记（五） 电脑资料 Oracle学习笔记（五） 举例一个完整的数据库脚本 -从此处开始复制,存成文件名为xxx.sql -先删除所有约束条件 alter table xxx drop constraint. -删除序列 drop sequence xxx -删除视图 drop view xxx drop view aaa. -删除表 drop table xxx. -创建表 create table xxx. -创建视图 -创建序列 -创建约束条件 -增加基础数据 DML操作 insert / update / delete 事务(Transaction)处理语句:mit/rollback/savepoint DDL:create / drop / alter / truncate SQLtruncate table student; SQLdrop table student; 其它数据库对象:视图/索引 视图的本质-sql查询语句 -简单视图 create view emp_10 as select * from emp where deptno = 10; -复杂视图 create view emp_sum as select deptno, sum(sal) sum_sal from emp group by deptno; create view emp_dept as select e.ename, d.dname from emp e join dept e on e.deptno = d.deptno; -索引index xx0225 -行内视图:查询语句出现在 from 后面 -匿名视图 select ename, sal from emp e join (select deptno, avg(sal) avgsal from emp group by deptno) a on e.deptno = a.deptno and e.sal a.avgsal; -子查询:查询语句出现在条件中 select ename, sal from emp where sal (select sal from emp where ename = SCOTT); -伪列 rownum, rowid,oracle特有的概念 select rownum,ename, sal from emp where rownum 5; -第5条到第10条记录的获取方式 -这种方式仅适用于oracle数据库 select ename, sal from (select ename, sal, rownum rn from emp ) where rn between 5 and 10; -排名问题,Top-N分析 -薪水最高的三个人,成绩最低的五个人 -错误的例子: select ename, sal from emp where rownum = 3 order by sal desc; -正确的例子: select ename, sal from (select * from emp where sal is not null order by sal desc)where rownum = 3; -序列Sequence -一种数据库对象.主要用于生成主键值. create sequence myseq_ning; -序列的两个伪列:nextval, currval -nextval获得序列的下一个值 -currval获得序列的当前值 -当序列建好以后,必须先执行nextval,才能执行currval. select myseq_ning.nextval from dual; select myseq_ning.currval from dual; create table mytable_ning(id number primary key, name varchar2(20); insert into mytable_ning values(myseq_ning.nextval, chris); select * from mytable_ning; -创建序列,起点是1000,步进是10 create sequence mysequ_ning start with 1000 increment by 10; user_objects; user_tables; user_sequences; user_views; . -获得openlab用户名下所有的对象种类. select distinct object_type from user_objects; -PL/SQL编程 -匿名块 / 函数 / 过程 / 包 / 触发器 -打开输出,sqlplus命令 set serveroutput on declare v_count number := 0; begin select count(*) into v_count from emp; dbms_output.put_line(total num is |v_count); end; / begin dbms_output.put_line(Hello World); end; / declare . begin . exception . end; declare v_sal number := 0; begin select sal into v_sal from emp where ename = aaa; dbms_output.put_line(sal is |v_sal); exception when too_many_rows then dbms_output.put_line(too many rows!); when no_data_found then dbms_output.put_line(no data!); when others then dbms_output.put_line(some error!); end; / 0-1000 0% 1001-2000 1% xx-3000 2% 3001-5000 4% 5001-. 5% -函数 create or replace function tax_ning( v_sal number) return number is v_result number; begin if (v_sal 1000) then v_result := 0; elsif (v_sal 2000) then v_result := v_sal * 0.01; elsif (v_sal 15000) then dbms_output.put_line(too many sal); else update emp set sal =