Oracle复习笔记

/*----------------------------------------常用命令(sqlplus环境)----------------------------------------*/conn scott/tiger@orcl [as sysdba] || [as sysoper] --切换用户show user   --当前用户passw[ord]  --修改密码disc        --断开连接clear scr   --清屏start || @   --运行sql脚本edit        --编辑指定sql脚本spool       --sqlplus截屏(sql>spool d:\spool.sql; sql>spool off;)exit || quit    --退出(执行后会commit)&       --动态输入值set linesize     --设置显示行宽度(默认80字节)set pagesize     --设置每页显示的行数目(默认14行)set colsep |         --设置列与列之间的分割符号set echo on --设置运行命令是是否显示语句set feedback on --设置显示“已选择XX行”set serveroutput on  --启用输出(/表示结束PL-SQL块)set heading on       --设置显示列名set timing on        --显示执行速度set time on          --显示当前时间set autotrace on     --设置允许对执行的sql进行分析call pro||fun;       --调用存储过程或函数--给一个事物命名set transaction name tname --指定一个事物使用回滚段set transaction use rollback segment 回滚段名--如果在设置隔离级前有更新表t1没有提交，在设置隔离级后，更新表会等待，先前的提交后，隔离级里的会报错set transaction  isolation level serializable--默认情况的设置，如果在设置隔离级前有更新表t1没有提交，在设置隔离级后，更新表会等待，先前的提交后，隔离级里的会执行set transaction level read commited--只能读，不能进行dml操作set transaction read only--可以进行dml操作默认(默认)set transaction  read write/*----------------------------------------导入(imp)、导出(exp)----------------------------------------*/exp将数据库内的各对象以二进制方式下载成damp文件，方便数据迁移。buffer：下载数据缓冲区，以字节为单位，缺省依赖操作系统consistent：下载期间所涉及的数据保持read only，缺省为ndirect：使用直通方式        ，缺省为nfeedback：显示处理记录条数，缺省为0，即不显示file：输出文件，缺省为expdat.dmpfilesize：输出文件大小，缺省为操作系统最大值indexes：是否下载索引，缺省为n，这是指索引的定义而非数据，exp不下载索引数据log：log文件，缺省为无，在标准输出显示owner：指明下载的用户名query：选择记录的一个子集rows：是否下载表记录tables：输出的表名列表导出整个实例exp system/admin file=oradb.dmp log=oradb.log full=y consistent=y direct=y;导出指定用户所有对象exp dbuser/oracle file=dbuser.dmp log=dbuser.log owner=dbuser buffer=4096000 feedback=10000导出表exp dbuser/oracle file=dbuser.dmp log=dbuser.log tables=table1,table2 buffer=4096000 feedback=10000imp将exp下载的dmp文件上载到数据库内。buffer：上载数据缓冲区，以字节为单位，缺省依赖操作系统commit：上载数据缓冲区中的记录上载后是否执行提交feeback：显示处理记录条数，缺省为0，即不显示file：输入文件，缺省为expdat.dmpfilesize：输入文件大小，缺省为操作系统最大值fromuser：指明来源用户方ignore：是否忽略对象创建错误，缺省为n，在上载前对象已被建立往往是一个正常现象，所以此选项建议设为yindexes：是否上载索引，缺省为n，这是指索引的定义而非数据，如果上载时索引已建立，此选项即使为n也无效，imp自动更新索引数据log：log文件，缺省为无，在标准输出显示rows：是否上载表记录tables：输入的表名列表touser：指明目的用户方导入整个实例imp system/admin file=oradb.dmp log=oradb.log full=y buffer=4096000 commit=y ignore=y feedback=10000导入指定用户所有对象imp dbuser/oracle file=dbuser.dmp log=dbuser.log fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000导入表imp dbuser2/oracle file=user.dmp log=user.log tables=table1,table2 fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000/*----------------------------------------用户操作----------------------------------------*/1)创建用户并指定表空间create user lee identified by 123 [default tablespace users];2)修改用户(密码或指定表空间)alter user lee [identified by newpwd] || [default tablespace users];3)锁定用户alter user lee account lock;4)解锁用户alter user lee account unlock;5)删除用户[含所有对象]drop lee [cascade];/*----------------------------------------权限操作----------------------------------------*/授权时加with admin option可以使权限传递,当最上级回收权限时,系统权限不会被级联回收,对象权限会被级联回收.1)授予系统权限grant connect,resource to lee [with admin option];2)授对象权限grant all on scott.emp to lee [with admin option];3)撤销权限revoke resource from lee;revoke update,delete on scott.emp from lee;/*----------------------------------------角色操作----------------------------------------*/connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予connect和resource角色就够了。connect角色具有以下系统权限：alter session    create cluster    create database linkcreate session   create view       create sequenceresource角色具有应用开发人员所需要的其他权限，比如建立存储过程、触发器等。这里需要注意的是resource角色隐含了unlimited tablespace系统权限。resource角色包含以下系统权限：create cluster    create indextype    create tablecreate sequence   create type         create procedurecreate trigger1)创建角色create role lee not identified;create role lee identified by tiger; 2)删除角色drop role 角色名;/*----------------------------------------序列操作----------------------------------------*/--创建序列create sequence orcl_seqincrement by 1 --每次增长幅度[默认:1]start with 1   --开始时的序列号[默认:1]maxvalue 999   --限制生成的最大值minvalue 1     --限制生成的最小值nocycle        --达到最大值后,重新生成序列,[默认:nocycle]cache 20;      --预先分配20个空间以便更快生成序列--删除序列drop sequence orcl_seq;--使用序列select orcl_seq.nextval from dual;--查看序列的当前值(至少使用1次后才可查询)select orcl_seq.currval from dual;/*----------------------------------------表操作----------------------------------------*/--建表create table tb(id number(10) primary key,name varchar2(20),  age number(3) default 0 not null  --constraint CK_name check(name not in ('@','#','$'))) tablespace system;--插入数据insert into tb values(orcl_seq.nextval,'lee',26);--修改列alter table tb modify age default 1 modify name not null;--添加约束alter table tb add constraint CK_name check(name not in ('@','#','$')) add constraint UQ_name unique(name);--alter table tb add constraint FK_? foreign key(?) references tb2(?);--删除约束alter table tb drop constraint UQ_name;--修改列名alter table tb rename column name to tname;--修改表名(sqlplus)rename tb to tab;--删除表drop table tab;--复制表(不会复制约束)create table copy_tb as select ename,sal from scott.emp [where 1=2];--多行插入(将查询结果集直接插入现有表中)insert into copy_tb select ename,sal from emp where ename = 'KING';/*----------------------------------------视图操作----------------------------------------*/视图是一张虚拟表,是对基表数据的引用,如果基表数据发生改变，视图中的数据也随之改变,对视图成功的增删改操作，也将影响基表的数据.实际开发中，一般只对视图作查询.--创建视图create or replace view v_empasselect * from empwith read only;--删除视图(注：对视图的DML操作会影响原表的数据)drop view v_emp/*----------------------------------------索引操作---------------------------------------*/索引基于表的列创建，给经常用于where条件、分组、排序的列添加索引1)标准索引create index 索引名 on 表名(列名);2)唯一索引，Oracle自动为主键、唯一键创建唯一索引create unique index 索引名 on 表名(列名);3)组合索引，一般用于多个条件的组合查询create index 索引名 on 表名(列名1,列名2...);4)反向键索引，按字节形式将数据反转,用于经常从后匹配的列create index 索引名 on 表名(列名) reverse;5)基于函数的索引，在应用函数的列上创建索引--eg 经常按入职月份查询员工信息，请创建索引create index ix_hiredate1 on emp(to_char(hiredate,'fmmm'));--注意：Oracle自动根据查询语句应用索引进行优化select * from emp where to_char(hiredate,'fmmm')='2';6)删除索引drop index 索引名;/*----------------------------------------事务提交----------------------------------------*/1、概念1）事务是1个最小的执行单元（逻辑单元）2）该单元包含1个或1组数据库操作（增删改）3）该组操作要么同时成功，要么同时失败2、特点（ACID）A- 原子性，事务是不允许再分的单元C- 一致性，事务前后的数据应该保持一致I- 隔离性，事务之间是相互独立的D- 永久性，事务提交后，对数据库的影响是永久的，无法回滚Oracle中的事务1）1次连接=1次会话session=1个事务2）一旦遇到commit或rollback，则提交或回滚该会话中所有未提交的操作3）对于DDL语句（create/alter/drop），执行成功后相当于执行了commit--事务提交commit;--设置事务回滚点savepoint p1;--事务回滚rollback;rollback to p1;--删除表所有数据(不允许回滚)truncate table tb;/*----------------------------------------行级锁----------------------------------------*/1）Oracle自动为 insert/delete/update/select...for update 操作应用行级锁 一旦数据被锁住,就不允许其他会话进行操作,直到commit或rollback.2）用于锁定查询结果集 select * from emp for update;3）用于检测数据是否被锁定,以及限制用户等待的时间.   select * from emp for update nowait;  --不等待   select * from emp for update wait 10; --等待10S/*----------------------------------------表空间操作----------------------------------------*/--授权创建表空间系系统权限grant create tablespace to test2;--创建表空间create tablespace myspacedatafile 'd:\mydata.dbf'size 20mautoextend onnext 3mmaxsize 100m;--扩展表空间alter tablespace myspace add datafile 'd:\mydata2.dbf' size 30m;alter database datafile 'd:\mydata.dbf' resize 35m;alter database datafile 'd:\mydata2.dbf' autoextend on next 3m maxsize 100m;--删除表空间(including contents and datafiles物理文件会一起删除)drop tablespace myspace including contents and datafiles;/*----------------------------------------并集、交集、减集----------------------------------------*//*union并集(或的关系,不包含重复记录),union all包含重复记录 */select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';/*intersect交集(与的关系)*/select * from emp where sal > 2500 intersect select * from emp where job = 'MANAGER';/*munus减集 */select * from emp where sal > 2500 minus select * from emp where ename = 'MANAGER';/*----------------------------------------常用函数----------------------------------------*/--分组函数：针对每组数据返回1个结果select count(*) 总人数,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,avg(sal) 平均工资 from emp;select count(*) 公司总人数,count(mgr) 有上级的人数 from emp;select deptno,avg(sal) ageSal from emp group by deptno having avg(sal)>=2000 order by avg(sal) desc;--日期函数：针对每行数据返回1个结果select to_date('1-5月-13') - sysdate 天 from dual;select ename,months_between(sysdate,hiredate)/12 "工龄(年)" from emp;select ename,hiredate 入职时间,add_months(hiredate,3) 转正日期 from emp;select * from emp where hiredate = last_day(hiredate);select * from emp where extract(month from hiredate) = 5;--字符函数select length('oracle') from dual;select lengthb('你好') from dual;select substr('[e-mail]',7,4) from dual;select instr('oracle','a') from dual;select substr('&email',1,instr('&email','@')-1) 用户名 from dual;select replace('ddl','dl','ml') from dual;select length('abcabca') - length(replace('abcabca','a')) from dual;select chr(65) from dual;select ascii('A') from dual;select lpad('A',5,0),rpad('A',5,0) from dual;--数学函数select ceil(9.1) from dual;select floor(9.1) from dual;select round(1.5),round(1.55,1),round(155,-1) from dual;select trunc(155.55) from dual;--分析函数(用于数据的排名统计)select ename,sal,row_number() over(order by sal desc) row_number,rank() over(order by sal desc) rank,dense_rank() over(order by sal desc) dense_rank from emp;--转换函数select to_char(sysdate,'yyyy-MM-dd hh24:mm:ss day') from dual;select to_char(sysdate,'yyyy"年"fmmm"月"dd"日"') from dual;select to_date('2013-5-1 15:33:40','yyyy-mm-dd hh24:mi:ss') from dual;select to_number('000123') from dual;select nvl(null,'is null'),nvl('lee','is null') from dual;select nvl2(null,'is null','not is null') from dual;select '星期' || decode(7,1,'一',2,'二',3,'三',4,'四',5,'五',6,'六',7,'日') from dual;--case when的使用select ename,sal,case  when sal = (select sal from emp where ename = 'KING') then 'BOSS'  when sal between 3000 and 4999 then '太牛了'  else '太少了'end 工资水准from emp;select ename,sal,case ename  when 'KING' then 'BOSS'  else '员工'end 工资水准from emp;/*----------------------------------------数据库字典----------------------------------------*/--查询当前数据库select * from global_name;--查询当前用户可以访问的数据库字典select * from dict where comments like '%grant%';--查询用户select * from user_users;select * from dba_users;--查看序列select sequence_name,sequence_owner from user_sequences;select sequence_name,sequence_owner from all_sequences [where sequence_owner = 'SCOTT'];--查询表(all_tables当前用户表以及可操作表)select table_name,tablespace_name from user_tables;select table_name,tablespace_name from dba_tables;select table_name,tablespace_name from all_tables;--查看视图select * from user_views;select * from dba_views;--查看索引select * from user_indexes;select * from dba_indexes;--查询表空间select tablespace_name from dba_tablespaces;select tablespace_name from user_tablespaces;--查询当前用户包含的角色select * from user_role_privs;--查询所有系统权限select * from system_privilege_map;--查询对象权限select distinct privilege from user_tab_privs;select distinct privilege from dba_tab_privs [where grantee = 'CONNECT'];--查询所有角色(dba)select * from dba_roles;--查询指定用户包含的角色(dba)select * from dba_role_privs where grantee = 'SCOTT';--查询指定角色包含的系统权限(dba)select * from dba_sys_privs where grantee = 'CONNECT';--查询指定角色包含的对象权限(dba)select * from dba_tab_privs where grantee = 'CONNECT';/*----------------------------------------PL-SQL----------------------------------------*/SQL：结构化查询语言（数据库行业的标准）T-SQL：事务的结构化查询语言，是SQLServer对SQL的扩展PL-SQL：过程语言和结构化查询语言，是Oracle对SQL的扩展：1、使用变量，使用流程控制结构2、使用过程、函数封装复杂的业务逻辑（变量、流程控制、SQL操作）3、对Oracle基本的数据类型进行了扩展（属性类型）数据类型：e_name emp.ename%type; --列类型(引用表中指定列的类型)e_emp_row emp%rowtype; --行类型(引用表中行的类型)v_bool boolean;        --布尔类型 boolean(取值true,false,null,仅用于逻辑判断，不能直接输出)异常处理：exception1、others 能处理所有异常2、select..into.. 如果未找到数据，则引发no_data_found异常3、select..into.. 如果返回值过多，则引发too_many_rows异常4、使用raise_application_error引发应用程序异常1）raise_application_error(错误号,错误信息)2）错误号在[-20999,-20000]之间3）错误信息必须小于2048字节语法：[declare  --任何类型的变量在未赋值之前，默认为null  声明变量、类型、游标...;]begin  实现功能的代码...;  --others可以捕获所有异常  [exception when 异常类型   then 异常处理;]end;--查询员工KING的编号和薪水(匿名块)declare  v_empno number;  v_sal number(10,2);begin  select empno,sal into v_empno,v_sal from emp where ename = 'KING';  dbms_output.put_line('EMPNO:' || v_empno || ' SAL:' || v_sal);end;--loop、while、for循环declare  v_num number(2) := 1;begin  loop    dbms_output.put_line(v_num);  v_num := v_num + 1;    exit when v_num > 10;  --if v_num > 10 then exit; end if;  end loop;end;declare  v_num number(10) := 1;begin  while v_num 1=1 loop    dbms_output.put_line(v_num);    v_num := v_num + 1;  end loop;end;declarebegin  --in后加reverse倒序,i值不可更改  for i in 1..10 loop    dbms_output.put_line(i);  end loop;end;/*----------------------------------------游标使用----------------------------------------*/游标：指向查询结果集的指针（对查询结果集的引用）一、隐式游标(例：SQL%rowcount)1、Oracle自动为增删改操作创建隐式游标，名称统一为SQL2、用于获取最近的增删改操作对数据库的影响（在提交或回滚之前）3、游标属性1）%rowcount 返回受影响的行数2）%found 如果影响了数据，则返回true否则返回false3）%notfound 如果没影响数据，则返回true否则返回false4）%isopen 如果游标打开，则返回true否则返回false（隐式游标始终返回false）二、显式游标(例：cursor my_cursor is select * from emp)1、显示游标必须在声明部分显式声明 2、用于处理返回多行的查询结果集，便于用户逐行处理数据3、游标属性1）%rowcount 返回游标所在的行号2）%found 如果找到了数据（fetch成功），则返回true否则返回false3）%notfound 如果没找到数据（fetch失败），则返回true否则返回false4）%isopen 如果游标打开，则返回true否则返回false三、for循环游标：简化游标的操作（自动打开、提取数据、关闭）declare  cursor mycursor  is  select * from emp;begin--'r'表示游标中结果集对应的行类型变量  for r in mycursor  loop    if(r.sal>=3000) then      dbms_output.put_line(mycursor%rowcount||'-'||r.ename||'-'||r.sal);    end if;  end loop;end;四、带参数的游标：提高游标的灵活性--eg 根据部门编号查询员工信息declare  --声明参数，类型不能指定长度  cursor mycursor(d_no number)   is  select * from emp where deptno=d_no;begin  for r in mycursor('&d_no') --传参  loop    dbms_output.put_line(r.ename);  end loop;end;五、使用游标更新数据1）使用select..for update nowait;给游标加锁2）使用where current of 游标名;限定更新游标所在行--eg 给员工加薪（10+100,20+200,30+300）declare   money number;  cursor mycursor  is  select * from emp for update nowait;--给游标加锁begin  for r in mycursor  loop    if(r.deptno=10) then      money:=100;    elsif(r.deptno=20) then      money:=200;    elsif(r.deptno=30) then      money:=300;    end if;    --where current of 游标名; 限定更新游标所在行    update emp set sal=sal+money where current of mycursor;  end loop;end;六、REF游标（动态游标）1、用于处理运行时才能确定的查询结果集2、REF游标必须在声明部分：1）声明REF游标类型，type 类型名 is ref cursor;2）声明REF游标变量，变量名 类型名;3) 动态游标不能带参数？--查询每个员工的姓名和薪水declare  type my_refcursor_type is ref cursor;  my_refcursor my_refcursor_type;  emp_row emp%rowtype;begin  open my_refcursor for select * from emp;  loop    fetch my_refcursor into emp_row;    exit when my_refcursor%notfound;    dbms_output.put_line(emp_row.ename || ':' ||emp_row.sal);  end loop;  close my_refcursor;end;/*----------------------------------------存储过程、函数、复合类型(record,table)、包----------------------------------------*/一、分类1）过程 procedure，完成特定功能2）函数 function，完成特定功能并返回1个结果2、优点1）模块化，按业务功能进行封装2）重用性好，易于维护3）执行效率高，减少网络流量的占用4）安全性高（涉及权限管理）二、组成1）声明部分（必须的）        create [or replace]...2）可执行部分（必须的）      begin...end;3）异常处理部分（可选的）    exception...三、参数模式1）输入参数 in   接收用户输入，不允许在过程中修改，如果未指定参数模式默认为输入参数2）输出参数 out   向用户返回结果，必须声明变量传参3）输入输出参数 in out   既接收用户输入，又向用户返回结果四、创建语法过程：create [or replace]procedure 过程名[(参数列表)]as | is  [变量列表;]begin  可执行代码;  [exception   when others then null;]end;函数：create [or replace]function 函数名[(参数列表)]return 返回类型as | is  [变量列表;]begin  可执行代码;  return 返回值;  [exception   when others then return null;]end;五、删除子程序drop procedure 过程名;drop function 函数名;--根据员工姓名查询工资(过程)create or replace procedure myproc1(e_ename in varchar2,e_sal out number)is  v_sal emp.sal%type;begin  select sal into e_sal from emp where ename = e_ename;end;declare  sal number(20);begin  myproc1('KING',sal);  dbms_output.put_line('SAL:' || sal);end;--根据员工姓名查询编号(函数)create or replace function myfun1(e_ename varchar2) return numberis  v_empno emp.empno%type;begin  select empno into v_empno from emp where ename = e_ename;  return v_empno;end;declare  empno emp.empno%type;begin  empno := myfun1('KING');  dbms_output.put_line('EMPNO:' || empno);end;六、复合类型记录(record)：--根据编号查询员工姓名和薪水(复合类型——记录)create or replace procedure myproc2(e_empno number)is  type record_type is record(v_ename emp.ename%type,v_sal emp.sal%type);  ename_sal_recode record_type;begin  select ename,sal into ename_sal_recode from emp where empno = e_empno;  dbms_output.put_line('ENAME,SAL:' || ename_sal_recode.v_ename || ' ' || ename_sal_recode.v_sal);end;表(table)：--根据员工姓名查询该员工上级(复合类型——表)create or replace procedure myproc3(e_ename varchar2)is  type emp_table_type is table of emp.ename%type index by binary_integer;  emp_table emp_table_type;begin  select e2.ename into emp_table(0) from emp e1,emp e2 where e2.empno = e1.mgr and e1.ename = upper(e_ename);  dbms_output.put_line('MGR:' || emp_table(0));  exception  when no_data_found then     dbms_output.put_line('该员工不存在或没有上级！');end;--取出emp表所有员工姓名(复合类型——表)create or replace procedure myproc4as  type emp_table_type is table of emp.ename%type index by binary_integer;  emp_table emp_table_type;  num number;begin  select count(*) into num from emp;  dbms_output.put_line('EMP TABLE ALL ENAME:');  for i in 1..num loop     select e2.ename into emp_table(i) from (select rownum rn,e1.* from (select * from emp order by empno) e1) e2 where e2.rn = i;     dbms_output.put_line(emp_table(i));  end loop;end;七、程序包：用于封装子程序、游标、变量等对象1、组成1）包规范 package   用于声明公共成员和子程序规范（定义接口）2）包主体 package body   用于声明私有成员和实现子程序（定义类实现接口）--包规范create or replace package mypack1is  procedure test1;  procedure test2;end;--包主体create or replace package body mypack1 is  --test1  procedure test1  is  begin    dbms_output.put_line('my is test1');  end;  --test2  procedure test2  is  begin    test1();    dbms_output.put_line('my is test2');  end;end;begin  mypack1.test2();end;/*----------------------------------------动态sql----------------------------------------*/注：execute immediate sql执行结果只能取出单行单列或多列,如多行必须使用游标--根据员工姓名查询薪水declare  type my_record_type is record(e varchar2(20),s number);  strSql varchar2(100);  my_record my_record_type;begin  strSql := 'select ename,sal from emp where ename = ''KING''';  execute immediate strSql into my_record;  dbms_output.put_line(my_record.e || ' ' || my_record.s);end;/*----------------------------------------分页存储过程----------------------------------------*/--定义包create or replace package pagePack as  type pageCursorType is ref cursor; --游标类型  procedure pageProc(    tableName varchar2,--表名showField varchar2, --查询字段whereText varchar2, --查询条件orderText varchar2, --排序字段[asc] || [desc]pageIndex number,   --当前页码pageSize number,    --每页显示几条counter out number,     --总记录数pageCount out number,   --总页数pageCursor out pageCursorType --结果集  );end pagePack;--实现包体create or replace package body pagePack as  procedure pageProc(    tableName varchar2,--表名showField varchar2, --查询字段whereText varchar2, --查询条件orderText varchar2, --排序字段[asc] || [desc]pageIndex number,   --当前页码pageSize number,    --每页显示几条counter out number,     --总记录数pageCount out number,   --总页数pageCursor out pageCursorType --结果集  )  as    strSql varchar2(500);wText varchar2(100);oText varchar2(100);  begin--判断是否有where条件if whereText is not null thenwText := ' where ' || whereText;end if;--判断是否有order by排序if orderText is not null thenoText := ' order by ' || orderText;end if;--拼接分页sql语句strSql := 'select * from (select rownum rn,' || showField || ' from (select * from ' || tableName || wText || oText ||' ) t where rownum <= :1) where rn > :2';dbms_output.put_line(strSql);--打开游标并取值(不用关闭)open pageCursor for strSql using pageIndex * pageSize,(pageIndex - 1)* pageSize;--拼接记录数sql语句strSql := 'select count(1) from ' || tableName || wText || oText;dbms_output.put_line(strSql);--执行动态sql获取记录数execute immediate strSql into counter;    --计算总页数pageCount := floor((counter + pageSize - 1) / pageSize);  end;end pagePack;--创建视图(rowtype需要rownun列,因此需要创建视图,视图中其他列需要制定别名否则报错)create or replace view viewEmpasselect rownum rn,emp.* from emp;--调用分页存储过程declarecounter number;  pageCount number;pageCursor pagePack.pageCursorType;  emp viewEmp%rowtype;begin  pagePack.pageProc('emp','t.*','','',1,5,counter,pageCount,pageCursor);dbms_output.put('COUNT:' || counter || ' PAGE_COUNT:' || pageCount);dbms_output.new_line();  --这里使用for循环遍历游标会报异常,因为调用过程返回的游标已是打开的  loop    fetch pageCursor into emp; --类似java中ResultSet中rs.next();    exit when pageCursor%notfound;    dbms_output.put_line(emp.ename);  end loop;  close pageCursor;end;/*----------------------------------------触发器----------------------------------------*/语法：create [or replace] trigger 触发器名称after | before | instead of [insert] [[or] update [of 字段列表]] [[or] delete]    on 表名 | 视图名 --触发器语句（事件）[referencing {OLD [as] old / NEW [as] new}]  --指定old表和new表的别名[for each row]   --行级触发器，每行都触发 [when (条件)]    --触发器条件begin    触发器操作;end;--限制只有scott才能对emp进行DML操作create or replace trigger trig1beforeinsert or update or deleteon empbegin  if user <> 'SCOTT' then    raise_application_error(-20000,'只有SCOTT才能对此表进行操作！');  end if;end;--记录对scott用户下emp表操作(用户、操作时间、操作类型)--扩展：如果是update操作记录该用户操作了哪些列？create or replace trigger trig2afterinsert or update or deleteon empdeclare  action varchar2(10);begin  if inserting then    action := 'insert';  elsif deleting then    action := 'update';  elsif updating then    action := 'update';  end if;  --system用户有一张scott_emp_log表并赋予public  insert into system.scott_emp_log values(user,sysdate,action,orcl_seq.nextval);end;--使用触发器对scott_emp_log表自动生成序号(插入数据id列写0或不指定id列)create or replace trigger trig3before inserton system.scott_emp_logfor each rowbegin  select orcl_seq.nextval into :new.id from dual;end;--创建触发器限制不允许修改员工奖金create or replace trigger trig4before update of common empfor each rowbegin  if :new.comm <> :old.comm then    raise_application_error(-20000,'不允许修改comm字段！');  end if;end;示例6：使用触发器更新视图--创建视图create or replace view view_testasselect empno,empno||ename newname,sal from emp;--创建触发器create or replacetrigger eg_trigger6instead of update on view_testbegin  --将修改视图的操作替换成修改基表的操作  update emp set ename=substr(:new.newname,5) where empno=:new.empno;end;update view_test set newname='7369LISHU' where empno=7369;示例7：记录删除的对象--创建日志表create table droped_objects(  object_name varchar2(30),  object_type varchar2(30),  drop_date date);--创建触发器create or replacetrigger eg_trigger7after drop on scott.schema --谁的操作begin  insert into droped_objects values  (ora_dict_obj_name,ora_dict_obj_type,sysdate);end;create table T(tid number);create sequence sq;drop table T;drop sequence sq;--禁用触发器alter trigger 触发器名称 disable;--启用触发器alter trigger 触发器名称 enable;--查看触发器的内容select line,text from user_source where name=upper('触发器名称');/*----------------------------------------Oracle SQL：经典查询练手第一篇----------------------------------------*/--1.列出至少有一个员工的所有部门。select dname from dept where deptno in(select distinct deptno from emp);select distinct d.dname from dept d,emp e where d.deptno = e.deptno;select distinct d.dname from dept d inner join emp e on d.deptno = e.deptno;--2.列出薪金比“SMITH”多的所有员工。select * from emp where sal > (select sal from emp where ename = 'SMITH');--3.列出所有员工的姓名及其直接上级的姓名。select ename,(select ename from emp b where b.empno = a.mgr) from emp a;select e1.ename,e2.ename from emp e1,emp e2 where e2.empno = e1.mgr--4.列出受雇日期早于其直接上级的所有员工。select * from emp a where a.hiredate < (select hiredate from emp b where b.empno = a.mgr);--5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门select d.dname,e.* from  dept d left join emp e on d.deptno = e.deptno;--6.列出所有“CLERK”(办事员)的姓名及其部门名称。select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.job = 'CLERK';--7.列出最低薪金大于1500的各种工作。select job,min(sal) from emp group by job having min(sal) > 1500;--8.列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。select ename from emp where deptno = (select deptno from dept where dname = 'SALES');select e.ename from emp e,dept d where e.deptno = d.deptno and d.dname = 'SALES';--9.列出薪金高于公司平均薪金的所有员工。select ename from emp where sal > (select avg(sal) from emp);--10.列出与“SCOTT”从事相同工作的所有员工。select ename from emp where job = (select job from emp where ename = 'SMITH');--11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。select ename,sal,deptno from emp where sal in(select sal from emp where deptno = 30) and deptno <> 30;--12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。select ename,sal from emp where sal > (select sum(sal) from emp where deptno = 30);--13.列出在每个部门工作的员工数量、平均工资和平均服务期限。select (select dname from dept where deptno = emp.deptno) dname,count(*),round(avg(sal),2),round(avg(sysdate-hiredate)) from emp group by deptno;--14.列出所有员工的姓名、部门名称和工资。select ename,(select dname from dept where deptno = emp.deptno) dname,sal from emp;--15.列出所有部门的详细信息和部门人数。select deptno,dname,(select count(*) from emp where deptno = dept.deptno group by deptno) dnum from dept;--16.列出各种工作的最低工资。select job,min(sal) from emp group by job;--17.列出各个部门的MANAGER(经理)的最低薪金。select (select dname from dept where deptno = emp.deptno) dname,min(sal) from emp where job = 'MANAGER' group by deptno;--18.列出所有员工的年工资,按年薪从低到高排序。select ename,(sal*12) yearsal from emp order by sal asc;/*----------------------------------------Oracle SQL：经典查询练手第二篇----------------------------------------*/--1.找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。select ename from emp where ename like '__A%';--2.找出EMP表员工名字中含有A 和N的员工姓名。select ename from emp where ename like('%A%') and ename like('%N%');--3.找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。select ename,sal,comm from emp where comm is not null order by sal,comm desc;4.列出部门编号为20的所有职位。select job from emp where deptno = 20;--5.列出不属于SALES 的员工。select ename from emp where deptno in(select deptno from dept where dname <> 'SALES');select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and d.dname <> 'SALES';6.显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。select ename,sal from emp where sal not between 1000 and 1500 order by sal desc;select ename,sal from emp where sal < 1000 or sal > 1500 order by sal desc;7.显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。select ename,job,(sal*12) yearsal from emp where job in('MANAGER','SALESMAN') and (sal*12) between 15000 and 20000;8.说明以下两条SQL语句的输出结果：    SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL; --有结果    SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;  --无结果9.让SELECT 语句的输出结果为1.SELECT * FROM SALGRADE;  2.SELECT * FROM BONUS;  3.SELECT * FROM EMP;  4.SELECT * FROM DEPT;  5.……列出当前用户有多少张数据表，结果集中存在多少条记录。 select 'SELECT * FROM ' || tab.tname || ';' from tab;  select * from tab;10.判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错，为什么？--不会报错，Oracle会检测是否可转为数字格式，如果可以将隐士转换，不可则报错。/*----------------------------------------Oracle SQL：经典查询练手第三篇----------------------------------------*//*Oracle分页查询*/--三层嵌套分页select e2.* from (select e1.*,rownum rn from (select * from emp order by empno) e1 where rownum <= 5) e2 where e2.rn >0;--减集分页(不能排序？)select * from emp where rownum<=5 minus select * from emp where rownum<=0;--多表连接分页查询(第一层需要指定显示列,列名不能用重复)select b.* from   (select rownum rn,a.* from (select e.*,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno order by e.empno) a   where rownum <=5) b where b.rn >=1;/*查询高于自己部门平均工资的员工信息*/select e2.empno,e2.ename,e2.sal,e2.deptno,e1.deptavgsal from emp e2,(select deptno,avg(sal) deptavgsal from emp group by deptno) e1 where e2.deptno = e1.deptno and e2.sal > e1.deptavgsal;