plsql笔记

wm1990315

浏览: 4989 次
性别:
来自: 上海

最近访客更多访客>>

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

C C++C#SQL

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;