pl/sql语句

***********************************************************************************************************************




************************************************************************************************************************


--视图：只是提供一个查询的窗口，所有数据来自于原表。前提是，本地用户有该表。
--同义词：只是为了方便跨用户查询，本用户查询其他用户表方便。


--pl/sql编程语言，是对sql语言的封装，使其具有过程化编程的特性。
--存储过程和存储函数都是用pl/sql编程语言来写的。


---declare begin end类似于java中的main方法
---:=给变量赋值
---into给变量赋值
declare
    i number(2) := 1;
    s varchar2(10) := '小明';
    ena emp.ename%type;---引用型变量 
    emprow emp%rowtype;---记录型变量
    isgood boolean := true;---布尔值只能当条件，不能输出
begin
    dbms_output.put_line(i);
    dbms_output.put_line(s);
    select ename into ena from emp where empno = 7788;
    dbms_output.put_line(ena);
    select * into emprow from emp where empno = 7788;
    dbms_output.put_line(emprow.ename || '的工作为：' || emprow.job);
    if isgood then
      dbms_output.put_line('true');
    end if;
end;




declare 


   i number(2) :=25;
   s varchar2(25):='辛忠原';
   via1 emp.ename%type;
   viarow emp%rowtype ;
   isgood boolean :=true;
begin 
   dbms_output.put_line(i);
   dbms_output.put_line(s);
   select  ename into via1  from emp where  empno=7788;
   dbms_output.put_line(via1);
   select * into viarow from emp where empno=7788;
   dbms_output.put_line(viarow.ename || '工作为' || viarow.job);
if isgood then 
     dbms_output.put_line('true');
end if;


end;






 select  * into m  from emp where empno=7369;
  dbms_output.put_line(m.ename);
  if isgood then
    dbms_output.put_line('true');
    end if;
    
declare 
  n emp.ename%type;
begin
  select  ename into n  from emp where  empno=7788;
   dbms_output.put_line(n);   
end;


 select  *   from emp where deptno=10 ;
  select  *   from emp where empno=7369 ;
select  dname  from dept where deptno=10;




















-----条件语句 if
-----输入小于18的数字，输出未成年
-----输入18到40之间的数字，输出中年人
-----输入40以上的数字，输出老年人
declare
     i number(3) := ⅈ---&加一个变量表示输入一个值
begin
     if i<18 then
          dbms_output.put_line('未成年');
     elsif i<40 then
          dbms_output.put_line('中年人');
     else
          dbms_output.put_line('老年人');
     end if;
end;






declare


   i number(3) :=&ii;




begin
  if  i<18  then 
    dbms_output.put_line(i);
   elsif  i<40 then 
     dbms_output.put_line(i || '2');
   else 
     dbms_output.put_line(i || '3');
     end if;
end;






























---循环 loop
---用三种循环输出1到10十个数字


---while循环
declare
  i number(2) := 1;
begin
  while i<11 loop
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;


---退出循环 exit
declare
   i number(2) := 1;
begin
   loop
       exit when i>10;
       dbms_output.put_line(i);
       i := i+1;
   end loop;
end;


---for循环
declare
begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;












declare 


   i number(2)  :=2;


begin 
  
  while i<50 loop
    dbms_output.put_line(i);
    i:=i+2;
    end loop;
end;




declare 


  
begin 
   for i  in  1..100  loop
     
     dbms_output.put_line(i);
      
     end loop;
  end;
  
  
  declare 
  i number(2) :=2;
  begin
    loop
      exit  when i>10; 
        dbms_output.put_line(i);
       i:=i+2;
       end loop;
  end;




declare 
  i  number(2) :=2;


begin 
  
   while i<20    loop
     
      dbms_output.put_line(i);
      i:=i+2;
   end loop;
end;


declare 
  
   i number(2):=2; 


begin 
  loop
     exit when i>20;
        dbms_output.put_line(i);  
        i:=i+2;
  end loop;


end;


declare


begin
  for  i in 1..20 loop
      dbms_output.put_line(i);
    
  end loop;
end;






----游标：其实就是集合，类似java中的list，set。
----输出emp表中所有员工信息
----定义并且赋值游标格式：cursor 变量名 is 查询语句。
declare
   cursor c1 is select * from emp;
   emprow emp%rowtype;
begin
   open c1;---开启游标
       loop
           fetch c1 into emprow;--fetch如果在loop循环里面表示依次拿出集合中每一个对象，可以不放在循环中，表示只拿集合中第一个。
           exit when c1%notfound;---退出条件
           dbms_output.put_line(emprow.ename || '的工作为：' || emprow.job);
       end loop;
   close c1;----关闭游标
end;




declare 
   cursor c1 is select  * from emp;
   emprow emp%rowtype;
begin 
   open c1;
      loop
         fetch c1 into emprow;      
         dbms_output.put_line(emprow.ename);
         exit when c1%notfound;
      end loop;
   close c1;  
end;


























----为指定部门员工涨工资
declare
   cursor c2(dno emp.deptno%type) 
   is select empno from emp where deptno = dno;
   eno emp.empno%type;
begin
   open c2(10);---带参数的游标只在打开的时候传一次值。
      loop
          fetch c2 into eno;
          exit when c2%notfound;
          update emp set sal=sal+100 where empno = eno;
          commit;
      end loop;
   close c2;
end;






--为指定部门增加工资


declare 
cursor   c1(dno emp.deptno%type) is select  empno  from emp  where deptno=dno;
emo  emp.empno%type;
begin
 open c1(10);
    loop
        fetch c1 into emo;
         exit when c1%notfound; 
          update emp set sal=sal+100 where empno=emo;
          commit;
      end loop;
      close c1;  


end;












































----例外，就是异常
declare
   i number(2);
begin
   i := 1/0;
exception
   when zero_divide then
      dbms_output.put_line('不能被0除');
   when others then
      dbms_output.put_line('未知异常');
end;




































---自定义异常
---查询每个部门的员工信息，如果该部门没有人，抛出一个no_data异常。
declare
    cursor c3(dno emp.deptno%type) 
    is select * from emp where deptno = dno;
    emprow emp%rowtype;
    no_data exception;---自定义一个no_data异常
begin
    open c3(40);
         fetch c3 into emprow;
         if c3%notfound then
              raise no_data;---抛出异常
         end if;
    close c3;
exception
    when no_data then
      dbms_output.put_line('该部门没有人');
    when others then
      dbms_output.put_line('未知异常');
end;










-----用存储过程给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
is
   s emp.sal%type;
begin
   select sal into s from emp where empno = eno;
   dbms_output.put_line(s);
   update emp set sal=sal+100 where empno = eno;
   commit;
   select sal into s from emp where empno = eno;
   dbms_output.put_line(s);
end;


----调用p1
declare
   i  number(2):=25;
begin
   p1(7788);
end;






--存储过程给制定员工增加100元钱
--游标集合可以传参 第一次open时候
--参数局部可用即行为可用
--游标定义的特点是 类型在前 除了游标之外 所有的参数定义都是 名称在前
 create or replace  procedure p1(eno emp.empno%type)
 is 
   s emp.sal%type;
 begin
    select  sal into s from emp where empno=eno;
    dbms_output.put_line(s);
    update emp set sal=sal+100 where empno=eno;
    commit;
    select sal into s from emp where empno=eno;    
    dbms_output.put_line(s);
 end;
 
 
 declare
 
 begin
   p1(7788);
 end;








































-----用存储函数算出指定员工的年薪
----存储函数return的类型以及参数类型都不能带长度
create or replace function f_yearsal(eno emp.empno%type) return number
is
    yearsal number(10);
begin
    select sal*12+nvl(comm, 0) into yearsal from emp where empno = eno;
    return yearsal;
end;




--存储函数  添加了 return 返回值类型 






create or replace function yearsla(emo emp.empno%type) return number
is


  yearsal number(20);
begin 
  select sal into yearsal  from  emp  where empno =emo;
  return yearsal;
end;


--调用时必须定义接受类型
declare 
yearss number(5);
begin
yearss :=yearsla(7788);  
dbms_output.put_line(yearss);
end;
















---调用f_yearsal
declare
  yearsal number(10);
begin
  yearsal := f_yearsal(7788);
  dbms_output.put_line(yearsal);
end;














----使用存储过程和存储函数的好处？
----存储过程和存储函数是已经编译好的一段plsql语言，放在数据库中，供人调用。
----比如：java代码中有十次数据库调用，我们就要链接数据库十次。十次，在数据库中要编译十次。
--------如果用存储过程或者存储函数，可以把十次数据库操作都放在一个过程和函数中，java代码只用调用一次！




--存储过程算年薪
create or replace procedure p_yearsal(eno in emp.empno%type, yearsal out number)
is
   s emp.sal%type;
   c emp.comm%type;
begin
   select sal, nvl(comm, 0) into s, c from emp where empno = eno;
   yearsal := s*12+c;
end;


-----调用p_yearsal
declare
  yearsal number(15);
begin
  p_yearsal(7788, yearsal);
  dbms_output.put_line(yearsal);
end;








--存储过程通过out 关键字 增加返回值类型
--此时为了区分传入参数和必须用in关键字
--接受类型因为大小不确定 此时不能确定长度所以后面不能有定长修饰
create or replace  procedure yeae(emo  emp.empno%type,en out number)
is
  s emp.sal%type;
  c emp.comm%type;
begin
  select sal,nvl(comm,0) into s,c  from emp where  empno=emo; 
  en:=s*12+c;
end;




declare
  
ya number(20) ; 
begin
  yeae(7788,ya);
  dbms_output.put_line(ya);


end;
































---in和out类型参数的区别：所有需要赋值操作【:=或者into】的参数，都用out。
---存储过程和存储函数的区别
---存储函数比存储过程多了两个return，
---存储过程如果想返回值，的用out类型的参数返回。
---因为存储函数有返回值，我们可以用存储函数来自定义函数。


----查询出员工姓名和员工部门名称
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;


----先定义一个存储函数，传入一个部门编号，返回一个部门名称
create or replace function p_dept(dno dept.deptno%type) return dept.dname%type
is
  dna dept.dname%type;
begin
  select dname into dna from dept where deptno = dno;
  return dna;
end;


----用自定义函数来实现查询出员工姓名和员工部门名称
select e.ename, p_dept(e.deptno) from emp e;


----存储过程返回指定部门员工信息
---单定义游标sys_refcursor
---单赋值游标open c for 查询语句
create or replace procedure p2(dno emp.deptno%type, c out sys_refcursor)
is


begin
  open c for select * from emp where deptno = dno;
end;


---调用
declare
   c sys_refcursor;
   emprow emp%rowtype;
begin
   p2(20, c);
      loop
         fetch c into emprow;
         exit when c%notfound;
         dbms_output.put_line(emprow.ename);
      end loop;
   close c;
end;












create or replace procedure   pr(dep in dept.deptno%type,yd out sys_refcursor)
is 
begin
    open  yd for  select  * from dept where  deptno=dep;
end;


declare 


 ud sys_refcursor;
 emprow dept%rowtype;
begin
  pr(20,ud);
  loop 
    fetch ud  into emprow;
    exit when ud%notfound;
    dbms_output.put_line(emprow.dname);
    end loop;
    close ud;


end;




---总结
--1存储过程和存储函数都可以有返回值
--2存储过程可以有返回值也可以没有存储函数必须有返回值
--3返回值类型在定义时不能有具体长度,在调用时候 才有具体长度
--4在具有返回值的时候.函数用的是 return  类型  过程通过 关键字 out 来表示输出的内容
--5接受的时候是一样的,必须定义返回值类型
--6游标类型的范返回值 用 过程实现  类型是ref_syscursor,接受类型也是syscursor
--7复制语句 open  游标  for  
--8取出的时候 fetch  游标  into  rowtype
              exit when 游标%notfound