plsql笔记

declare
 v_name varchar2(20);
begin
 select ename into v_name from emp where empno = 7369;
 dbms_output.put_line(v_name);
end;

declare
 varr varchar2(100);
 begin
 varr := 'hello'' world!';
 dbms_output.put_line(varr);
 end;
 
 declare
 v_name emp.ename%type;
begin
 select ename into v_name from emp where empno = 7369;
 dbms_output.put_line(v_name);
end;

var v_ename varchar2(20);
declare
begin
 select ename into :v_ename from emp where empno = 7369;
 dbms_output.put_line(:v_ename);
end;

declare
vaa varchar2(200) :='hello girl';
begin
   vaa:=initcap(vaa);
   dbms_output.put_line(vaa);
end;

declare
vaa varchar2(200) :='hello girl';
begin
   vaa:=replace(vaa,' ','_');
   dbms_output.put_line(vaa);
end;

declare
vaa date := sysdate;
begin
   dbms_output.put_line(to_char(vaa,'yyyy-mm-dd hh24:mi:ss'));
end;

--取最大值,--最小值 least()---
declare
begin
   dbms_output.put_line(greatest(1,2,3));
end;

declare
    x number;
  begin
  x:=10;
   declare
   y number;
   begin
   y:=15;
   x:=x+y;
   dbms_output.put_line('x='||x);
 end;
   dbms_output.put_line('y='||y);
end;

<<outerr>>
declare
    x number :=10;
  begin
   declare
   x number;
   begin
   x:=15;
   x:=x+10;
   dbms_output.put_line('x='||x);
   dbms_output.put_line('x='||outerr.x);
 end;
end;

--求次方--
declare
vaa number :=2;
begin
    dbms_output.put_line(vaa**3);
end;

--select into 查询结果只能返回一条数据 --
declare
    v_sal emp.sal%type;
    v_empno emp.empno%type :=7369;
    v_count number;
begin
 select count(sal) into v_count from emp where empno = v_empno;
 if v_count=1 then
   select sal into v_sal from emp where empno = v_empno;
   dbms_output.put_line(v_sal);
 end if; 
end;


--游标 --
declare
v_empno emp.empno%type := 7369;
begin
    delete emp where empno=v_empno;
 if sql%notfound then
    dbms_output.put_line("not found!");
 end if; 
end;

declare
v_empno emp.empno%type := 7369;
v_sal emp.sal%type;
begin
    select sal into v_sal from emp where empno = v_empno;
 if v_sal<400 then
    dbms_output.put_line('aaaaaa');
 elsif v_sal <800 then
    dbms_output.put_line('bbbbbbbbb');   
 else
  dbms_output.put_line('xxxxxxx');
 end if; 
end;


declare
v_empno emp.empno%type := 7369;
v_sal emp.sal%type;
begin
    select sal into v_sal from emp where empno = v_empno;
  if nvl(v_sal,0)<400 then
    dbms_output.put_line('aaaaaa');
   else if v_sal <800 then
           dbms_output.put_line('bbbbbbbbb');      
        else
           dbms_output.put_line('xxxxxxx');
        end if; 
  end if;
end;

--case语句--
declare
v_deptno emp.deptno%type;
v_empno emp.empno%type := 7369;
begin
    select deptno into v_deptno from emp where empno = v_empno;
 case v_deptno
 when 30 then
   dbms_output.put_line('bbbbbbbbb');
 when 20 then
   dbms_output.put_line('ccccccccc');    
    else
    dbms_output.put_line('xxxxx');
   end case;   
end;
--case表达式--
declare
v_deptno emp.deptno%type;
v_empno emp.empno%type := 7369;
v_sal emp.sal%type;
begin
    select deptno ,sal into v_deptno, v_sal from emp where empno = v_empno;
v_sal := case v_deptno
            when 30 then v_sal*1.5
            when 20 then v_sal*2
            else v_sal*0.5 
         end;   
dbms_output.put_line(v_sal);
end;

--loop--
declare
v_deptno emp.deptno%type;
v_empno emp.empno%type := 7369;
v_sal emp.sal%type;
begin
    loop
        v_empno:=v_empno+1;
       exit when v_empno >7372;
    end loop;
dbms_output.put_line(v_empno);    
      
end;

--while--
declare
 i number :=10;
 begin
  while i>0 loop
      i:=i-1;
      dbms_output.put_line(i);  
  end loop;
  end;
     
--for loops--
declare
 begin
  for i in 1..10 loop
    dbms_output.put_line(i);  
end loop;
  end;

--record--
declare
 type emp_rec_type is record
    (ename emp.ename%type,
     job emp.job%type,
     sal emp.sal%type);
     emp_rec emp_rec_type;
begin
    emp_rec.ename :='3455';
    emp_rec.job :='324';  
    emp_rec.sal :='345'; 
    dbms_output.put_line('emp_rec.sal ='||emp_rec.sal);   
end;

--rowtype--
declare
  emp_rec emp%rowtype;
  v_empno number := 7369;
begin
   select * into emp_rec from emp where emp.empno = v_empno; 
   dbms_output.put_line(emp_rec.sal);   
end;

--index 数组--
declare
   type emp_ename is table of
     emp.ename%type index  by pls_integer;
     v_emp_ename emp_ename;
begin
   for i in 1..14 loop
       select ename into v_emp_ename(i) from emp where empno = 7369;
   end loop;
   for i in v_emp_ename.first..v_emp_ename.last loop
      dbms_output.put_line(v_emp_ename(i)); 
   end loop;
   
end;

declare
   type emp_ename is table of
     emp%rowtype index  by pls_integer;
     v_emp_ename emp_ename;
begin
   for i in 1..14 loop
       select * into v_emp_ename(i) from emp where empno = 7369;
   end loop;
   for i in v_emp_ename.first..v_emp_ename.last loop
      dbms_output.put_line(v_emp_ename(i).ename); 
   end loop;
   
end;

--游标 cursor 定义
--open<分配内存，执行sql，指向第一条数据 
--fetch<把游标的数据放入变量,游标下移--
--close<关闭游标 c_emp.ename is error
--loop
declare
   cursor c_emp is
     select * from emp;
   v_emp emp%rowtype;
begin
   open c_emp;
   loop   
       dbms_output.put_line(v_emp.ename);
       dbms_output.put_line(c_emp%rowcount);
       fetch c_emp into v_emp;
       exit when c_emp%notfound;
   end loop;
   close c_emp;
end;  

--while
declare
   cursor c_emp is
     select * from emp;
   v_emp emp%rowtype;
begin
   open c_emp;
   fetch c_emp into v_emp;
   while c_emp%found loop
      dbms_output.put_line(v_emp.ename);
      fetch c_emp into v_emp;    
   end loop; 
   close c_emp;
end;
--if
declare
   cursor c_emp is
     select * from emp;
begin
  --if c_emp%isopen=false then
  --  open c_emp;
 -- end if;
  for v_emp in c_emp loop
      dbms_output.put_line(v_emp.ename);   
   end loop; 
   --close c_emp;
end;

--带参数的游标

declare
   cursor c_emp(c_empno number) is
      select * from emp where empno = c_empno;
begin
   for v_emp in c_emp(7369) loop
       dbms_output.put_line(v_emp.ename);     
   end loop;
end;  

--exception--

declare
  emp_rec emp%rowtype;
  v_empno number := 7369;
begin
   select * into emp_rec from emp where emp.empno = v_empno; 
   dbms_output.put_line(emp_rec.sal);   
   exception
    when too_many_rows then
    null;
end;

---过程
create or replace procedure getsal(v_empno number)
is
 v_sal emp.sal%type;
 begin
  select sal into v_sal from emp where empno=v_empno;
  dbms_output.put_line(v_sal);

end;
--函数,在sql里调用不能有输出参数，只能返回sql支持的数据类型
create or replace function getename(v_empno number ,v_ename out emp.ename%type)
return emp.ename%type
is
 begin
  select ename into v_ename from emp where empno=v_empno;
  return v_ename;
end;
begin

dbms_output.put_line();
end;


--package----

create or replace package changeemp
   is
    function test(v_enpno number);
end changeemp;    

create or replace package body changeemp
 is
   function test(v_enpno number)
   is
     begin
     end;
  end changeemp;  

-- 重载--

create or replace procedure test1(v_enpno number)
is
 begin
 getsal(7369);
 end;
 
--读写文件
grant create any directory to scortt;
create  directory   "DIR_PATH"   AS 'c:\';
grant  read  on  directory  DIR_PATH  TO user ;
grant  write on directory   DIR_PATHE  TO user ;
declare
  cursor c1 is select * from emp;
  filehandle utl_file.file_type;
  begin
   for rec in c1 loop
    dbms_output.put_line(rec.ename);
  filehandle :=  utl_file.fopen('c:\','1.txt','w');
  utl_file.put_line(filehandle,rec.ename);
   end loop;
    utl_file.fclose(filehandle);
 end;  
 
 --
create or replace procedure proc2 is
 pragma
  autonomous_transaction;
  dept_id number := 90;
 begin
  update
  insert
 commit;
end proc2;

--触发器
create or replace trigger trigger1
 before
  insert
  on emp
  declare
    v_msg varchar2(40);
  begin
    if to_char(sysdate,'hh24') not between '08' and '18' then
         v_msg:='cant insert';
         raise_application_error(-20201,v_msg);
   end if;
   end;