SQL第五天

select rn,ename,salary
from(
     select rownum rn,ename,salary
     from(
          select ename,salary
          from emp_hiloo
          order by salary desc)
     where rownum <= 6)
where rn >= 4


PL/SQL procedural language/SQL 


SQL engine
PL/SQL engine


PL/SQL程序
匿名块
有名块（数据块对象）
函数funtion create or replace function
过程procedure 
create or replace procedure
包 package create or replace package
触发器 trigger
create or replace trigger


匿名块
declare 申明 exception 异常


declare
 申明部分
 定义变量 数据类型 cursor（游标）     exception
begin
 程序主体 SQL
exception
 异常处理部分
end


begin
end


函数的调用
能放值的地方就能放函数


dbms_output 系统提供的package
dbms_output.put_line dbms_output包中有个过程put_line


函数本身有返回值，过程本身没有返回值，所以调用方式不一样。
v_d1 := sysdate;
dbms_output.put_line()


set serveroutput on 在sqlplus中设置


PL/SQL中的SQL语句
DML和TCL不需要任何变化
insert into sql语句
pl/sql程序
begin
  insert into test values (1,1);--静态sql
  commit;
end;
/


pl/sql中的DDL的写法改变了
begin
  execute immediate 'drop table test   purge'; --动态sql
end；


pl/sql中的DQL（select）的写法改变了
根据select语句的返回结果的多少不同处理方式不一样
1 当且仅当返回一条记录，select into语句，若没有记录返回报错，若返回多条记录报错。
declare
 v_ename emp_hiloo.ename%type;
begin
 select ename into v_ename
 from emp_hiloo
 where empno = 1001;
 dbms_output.put_line('1001''s name is '||
                      v_ename);
end;




declare
 --
 type r_emp is record (
 ename emp_hiloo.ename%type,
 salary emp_hiloo.salary%type,
 job emp_hiloo.job%type);
 --定义记录类型 


 v_emp r_emp;
 v_emp_1 r_emp;
 v_emp_2 emp_hiloo%rowtype;--记录类型


begin
 select ename,salary,job into v_emp_1
 from emp_hiloo
 where empno = 1001;
 v_emp := v_emp_1;
 select * into v_emp_2
 from emp_hiloo
 where empno = 1001;
 dbms_output.put_line('1001''s name is '||
                      v_emp.ename);
 dbms_output.put_line('1001''s name is '||
                      v_emp_2.salary);
end;


2在pl/sql中，若select语句返回多条或不返回记录该如何处理。
cursor就是一条SQL语句。数据库是通过cursor完成一条SQL语句的处理过程。


declare
  定义一个cursor
begin
  open cursor 执行select语句，并且获得      结果集；
  fetch cursor 放入循环 取结果集里的数据
  close cursor
end


cursor的属性
如果不先fetch cursor，那么c_emp%found的值为null。
必须先fetch cursor，c_emp%found才有值，若fetch取到记录，c_emp%found的值是true，否则c_emp%found的值是false。


循环方式
loop
  exit when 条件表达式
end loop;


while 条件表达式 loop
end loop；


declare
 v_name emp_hiloo.ename%type;
 cursor c_emp is
   select ename from emp_hiloo;
begin
   open c_emp;
   loop
     fetch c_emp into v_name;
     exit when c_emp%notfound;
     dbms_output.put_line(v_name);
   end loop;
   close c_emp;
end;


如何运行sql脚本，里面sql语句或pl/sql程序
sqlplus只到当前目录下找test.sql，若当前目录下没有该文件，写绝对路径。
/user/openlab/
sqlplus openlab/open123 @test.sql


SQL>@test.sql


集合类型
index-by table


declare
 --
 type t_dept_indtab is table of varchar2(20) --元素的数据类型
     index by binary_integer;--下标
 --定义集合类型（index-by table）
 v_dept_indtab t_dept_indtab;
 cursor c_dept is
   select deptno,dname from dept_hiloo;
begin
   for i in c_dept loop
      v_dept_indtab(i.deptno) := i.dname; --给集合类型的变量赋值
      dbms_output.put_line(v_dept_indtab(i.deptno));
   end loop;




   v_index := v_dept_indtab.first;--first表示第一个下标的值
while v_index <= v_dept_indtab.last loop --last表示最后一个下标的值
      dbms_output.put_line(v_dept_indtab(v_index));
      v_index := v_dept_indtab.next(v_index); --next表示当前元素的下一个值的下标
end loop;
end;


declare
 type t_dept_indtab is table of varchar2(20)
     index by binary_integer;
 v_dept_indtab t_dept_indtab;
 --cursor c_dept is
 --  select deptno,dname from dept_hiloo;
 v_index binary_integer;
begin
   --for i in c_dept loop
   --   v_dept_indtab(i.deptno) := i.dname;
   --end loop;
   select dname bulk collect into v_dept_indtab
   from dept_hiloo;
   for i in v_dept_indtab.first
                  .. v_dept_indtab.last loop
       dbms_output.put_line(v_dept_indtab(i));
   end loop;
   --v_index := v_dept_indtab.first;
   --while v_index <= v_dept_indtab.last loop
   --      dbms_output.put_line(v_dept_indtab(v_index));
dept_indtab.next(v_index);
   --end loop;
end;


select
当且仅当返回一条记录
select into
返回多条或不返回
cursor
select bulk collect into


关于集合类型的变量迭代
for i in v.first .. v.last loop
end loop
i := v.first;
while i<= v.last loop
  i := v.next(i);
end loop;


if 条件表达式 then
elsif 条件表达式 then
else
end if；


open_cursors 


cursor
隐式cursor 
select into DML 
select
显式cursor cursor c1 is select




cursor 集合类型变量


exception类型
定义异常 declare和begin之间
抛异常 begin和exception之间
捕获异常 exception和end之间


1预定义异常 no_data_found（捕获异常）
2用户自己将oracle错误和异常绑定 ora-2291（定义异常 捕获异常）
declare
 v_name varchar2(20);
 e_noparent exception;
 pragma exception_init(e_noparent,-2291); --把ora-2291错误定义为异常
begin
 insert into child values (2,2);--当发生2291错误系统会自动抛异常e_noparent
 select ename into v_name from emp_hiloo
 where empno = 1111;
 commit;
exception
  when no_data_found then
   dbms_output.put_line('no employee');
  when e_noparent then
   dbms_output.put_line('no parent key');
end;
3 自定义异常
declare
 v_name varchar2(20);
 e_noparent exception;
 pragma exception_init(e_noparent,-2291);
 e_low100 exception;
 v_n1 binary_integer :=99;
begin
 if v_n1 < 100 then
    raise e_low100;
 end if;
 insert into child values (2,2);
 select ename into v_name from emp_hiloo
 where empno = 1111;
 commit;
exception
  when no_data_found then
   dbms_output.put_line('no employee');
   when e_noparent then
    dbms_output.put_line('no parent key');
   when e_low100 then
    dbms_output.put_line('<100');
 end;


有名块
在数据库中创建存储过程（数据库对象）不带参数
 create or replace procedure proc1
 is
  v_d1 date :=sysdate;
 begin
  dbms_output.put_line(
      to_char(v_d1,'yyyy mm dd hh24:mi:ss'));
 end;  出错 show error
调用存储过程
1 exec proc1；
2 begin proc1 end；


作为in参数可以被赋值，out和in out可以
create or replace procedure proc1
(p_c1 in varchar2,p_c2 out varchar2,
 p_c3 in out varchar2)
is
 v_c1 varchar2(10);
begin
  v_c1 := p_c1 ||'d';
  p_c2 := p_c2 ||'d';
  p_c3 := p_c3 ||'d';
end;


declare
 v_c3 varchar2(10) := 'abc';
begin
 proc1('abc',v_c2,v_c3);
 dbms_output.put_line(v_c2); d
 dbms_output.put_line(v_c3); abcd
end;
in 接受外面传进来的值，在过程中参数的值不能改变，可以用常量调用
out 外面的值传不进来，在过程中参数的值可以改变，可以将值传给实参
in out 即可以传进来，也可以传出去
若参数是out或in out，必须拿变量调用。
过程本身没有返回值，过程可以返回值。


function 函数
create or replace function f_avgsal
(p_deptno number)
return number
is
  v_avgsal number(8,2);
begin
  select avg(salary) into v_avgsal
  from emp_hiloo
  where deptno = p_deptno;
  return v_avgsal;
end;
调用函数
select f_avgsal(10) from dual;
exec dbms_output.put_line(f_avgsal(10));


package 包
有名字的申明块
create or replace package pkg1
is
  type t_emp is record (
  ename varchar2(10),
  salary number(8,2));
  v_emp t_emp;
  cursor c_emp is
   select ename,salary from emp_hiloo;
  function f_emp(empno number) return t_emp;
  procedure p_printemp(p_emp t_emp);
end;


包体是对包里的过程和函数的实现
create or replace package body pkg1
 is
   function f_emp(p_empno number)
   return t_emp
   is
   begin
      select ename,salary into v_emp
      from emp_hiloo
      where empno = p_empno;
      return v_emp;
   end;
   procedure p_printemp(p_emp t_emp)
   is
   begin
     dbms_output.put_line(p_emp.ename);
     dbms_output.put_line(p_emp.salary);
    end;
 end;


调用
declare
  v_emp pkg1.t_emp;
begin
  v_emp :=pkg1.f_emp(1001);
  dbms_output.put_line(v_emp.ename);
  pkg1.p_printemp(v_emp);
end;


exec pkg1.p_printemp(pkg1.f_emp(1001))


function nvl(p1 date,p2 date) return date;
function nvl(p1 number,p2 nuymber) return number;
function nvl(p1 varchar2,p2 varchar2) return varchar2


把变量定义在包里，该变量在session里是全局的。pkg.v_n1


dbms_output
standard nvl upper 


trigger 触发器
insert的before行级触发器
create table test(
c1 number primary key,
c2 number);
create sequence s1;
create or replace trigger tri_test
before insert on test
for each row
declare
begin
  select s1.nextval into :new.c1
  from dual;
end;


insert into test(c2) values (1);调用trigger
insert into test values (100,1);100插不进去，会被s1产生的唯一值（s1.nextval）覆盖。


insert
update
delete


update
before
after
statement语句级
row行级


before update on test before的语句级触发器
before update on test for each row before的行级触发器
after update on test for each row after的行级触发器
after update on test after的语句级触发器 


一张表可以定义12中触发器
update语句修改了3条记录，statement 1 row 3