PLSQL学习笔记

新建emp表

create table emp(
  eno number,
  ename varchar2(20),
  sal number,
  job varchar2(20),'
  dept varchar2(20)
);

存储过程基本内容

--条件判断
  --1、if ... then elseif then ... else ... end if;
  --2、case ... when ... then ... end;

--循环结构
  --1、loop ... exit when ... end loop;
  --2、while ... loop ... end loop;
  --3、for i in ... loop ... end loop;
  --4、goto exit;

--游标(类似java中的Iterator)

--异常的处理(三种方式)

--会写存储函数和存储过程

--会写触发器

hello world代码

set serveroutput on

declare
  --声明的变量、类型、游标
begin
  --程序执行部分(类似于java里的main)
  dbms_output.put_line('helloworld');
--exception
   --针对begin块中的异常，提供处理机制
   --when......then......
   --when......then......
end;

declare声明变量

declare
  --声明的变量、类型、游标
  v_ename emp.ename%type;
  v_sal emp.sal%type;
  v_job emp.job%type;
begin
  --程序执行部分(类似于java里的main)
  select ename,sal,job into v_ename,v_sal,v_job from emp where eno=1;
  dbms_output.put_line(v_ename||','||v_sal||','||v_job);
--exception
   --针对begin块中的异常，提供处理机制
   --when......then......
   --when......then......
end;

声明记录类型

declare
  --声明一个记录类型
  type emp_record is record(
  v_ename emp.ename%type,
  v_sal emp.sal%type,
  v_job emp.job%type
  );
  --定义一个记录类型的成员变量
  v_emp_record emp_record;
begin
  --程序执行部分(类似于java里的main)
  select ename,sal,job into v_emp_record from emp where eno=1;
  dbms_output.put_line(v_emp_record.v_ename||','||v_emp_record.v_sal||','||v_emp_record.v_job);
--exception
   --针对begin块中的异常，提供处理机制
   --when......then......
   --when......then......
end;

变量或记录初始化赋值

declare
  --v_sal number(8,2):=0;
  --v_eno number(10);

  type emp_record is record(
       v_sal number(8,2):=0,
       v_eno number(10)
  );
  v_emp_record emp_record;
begin
  select sal,eno into v_emp_record from emp where eno=1;
  --dbms_output.put_line('sal:'||v_sal||','||'eno:'||v_eno);
  dbms_output.put_line('sal:'||v_emp_record.v_sal||','||'eno:'||v_emp_record.v_eno);
--exception

end;

if条件判断

declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where eno=1;
  v_sal:=3000;
  if v_sal>5000
  then
    dbms_output.put_line('中等工资');
  elsif v_sal>4000 and v_sal<=5000
  then
    dbms_output.put_line('工资真低');
  else
    dbms_output.put_line('活不起了');
  end if;
end;

case when条件判断

declare
  v_sal emp.sal%type;
  v_temp varchar2(30);
begin
  select sal into v_sal from emp where eno=1;
  v_sal:=12000;
  v_temp:=
  case
    trunc(v_sal/5000) when 0 then '活不起了'
                      when 1 then '工资真低'
                      when 2 then '中等工资'
  end;
  dbms_output.put_line(v_temp);
end;

while循环

declare
  v_i number(5):=1;
begin
  while v_i <= 100
    loop
        dbms_output.put_line(v_i);
        v_i:=v_i+1;
    end loop;
end;

--输出2-100之间的质数
declare
  v_i number(3):=2;
  v_j number(3):=2;
  v_flag number(1):=1;

begin
  while v_i <=100
    loop
      while v_j<=sqrt(v_i)
        loop
          if
            mod(v_i,v_j)=0 then v_flag:=0;
          end if;
          v_j:=v_j+1;
        end loop;
        if
          v_flag=1 then dbms_output.put_line(v_i);
        end if;
        v_j:=2;
        v_i:=v_i+1;
        v_flag:=1;
   end loop;
end;

for循环

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

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

loop循环

declare
  v_i number(5):=1;
begin
  loop
    dbms_output.put_line(v_i);

    v_i:=v_i+1;

  exit when v_i >100;

  end loop;
end;

游标for循环

--游标可以打印出多条记录
declare
  --定义游标
  cursor emp_cursor is select * from emp;
begin
  --for循环会自动进行 打开游标、提取游标、关闭游标
  for i in emp_cursor
  loop
    dbms_output.put_line(i.eno||','||i.ename||','||i.sal||','||i.job||','||i.dept);
  end loop;
end;

--利用游标调整工资员工的工资
declare
  cursor emp_cursor is select ename,sal from emp;
  v_temp number(4,2);
begin

  for i in emp_cursor
  loop
    if i.sal<5000 then v_temp:=0.05;
    elsif i.sal<10000 then v_temp:=0.03;
    elsif i.sal<15000 then v_temp:=0.02;
    else v_temp:=0.01;
    end if;
    dbms_output.put_line(i.sal);
    update emp set sal=sal*(1+v_temp);
  end loop;
end;

游标while循环

--游标可以打印出多条记录
declare
  type emp_record is record(
       v_eno emp.eno%type,
       v_ename emp.ename%type,
       v_sal emp.sal%type,
       v_job emp.job%type,
       v_dept emp.dept%type
  );
  v_emp_record emp_record;
  --定义游标
  cursor emp_cursor is select * from emp;
begin
  --打开游标
  open emp_cursor;
  --提取游标
  fetch emp_cursor into v_emp_record;

  while emp_cursor%found
  loop
    dbms_output.put_line(v_emp_record.v_eno||','||v_emp_record.v_ename||','||v_emp_record.v_sal||','||v_emp_record.v_job||','||v_emp_record.v_dept);
    fetch emp_cursor into v_emp_record;
  end loop;
  --关闭游标
  close emp_cursor;
end;

--利用游标调整工资员工的工资
declare
  cursor emp_cursor is select ename,sal from emp;
  v_temp number(4,2);
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  open emp_cursor;
  fetch emp_cursor into v_ename,v_sal;
  while emp_cursor%found
  loop
    if v_sal<5000 then v_temp:=0.05;
    elsif v_sal<10000 then v_temp:=0.03;
    elsif v_sal<15000 then v_temp:=0.02;
    else v_temp:=0.01;
    end if;
    dbms_output.put_line(v_sal);
    update emp set sal=sal*(1+v_temp);
    fetch emp_cursor into v_ename,v_sal;
  end loop;
end;

隐式游标

--隐式游标
begin
  update emp set sal=sal*(1+0.05) where eno=10;
  if sql%notfound then dbms_output.put_line('查无此人');
  end if;
end;

预定义异常

declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where sal>100;
  dbms_output.put_line(v_sal);
exception
  when too_many_rows then dbms_output.put_line('输出行数太多了');
  when others then dbms_output.put_line('出现其他类型的异常了');
end;

非预定义异常

--非预定义异常
declare
  e_delete_exception exception;
  pragma exception_init(e_delete_exception,-2292);
begin
  delete from emp where eno=100;
exception
  when e_delete_exception then dbms_output.put_line('违反完整性的约束条件，不可删除');
end;

函数

--函数有返回值
--存储过程没有返回值
create or replace function hello
--返回类型
return varchar2
is
   --函数使用过程中，需要声明的变量、记录类型、游标
begin
   return 'hello world';
end;

带参数的函数

--函数有返回值
--存储过程没有返回值
create or replace function hello(v_hname varchar2)
--返回类型
return varchar2
is
   --函数使用过程中，需要声明的变量、记录类型、游标
begin
   return 'hello world'||v_hname;
end;

--计算部门工资和
create or replace function get_sal(d varchar2)
return number
is
  v_sumsal number(10):=0;
  cursor emp_cursor is select sal from emp where dept=d;
begin
  for i in emp_cursor
  loop
    v_sumsal:=v_sumsal+i.sal;
  end loop;
  return v_sumsal;
end;

存储过程

create or replace procedure getsal(dept varchar2,sumsal out number)
is
  cursor sal_cursor is select sal from emp where dept=dept;
begin
  sumsal:=0;
  for i in sal_cursor
  loop
    sumsal:=sumsal+i.sal;
  end loop;
  dbms_output.put_line(sumsal);
end;

/*
declare
  v_sal number(10):=0;
begin
  dbms_output.put_line(getsal('crm',v_sal));
end;
*/

create or replace procedure add_sal(dept varchar2,temp_sal out number)
is
  cursor emp_cursor is select ename,sal from emp where dept=dept;
begin
  temp_sal:=0;
  for i in emp_cursor
  loop
    --更新工资
    update emp set sal=sal*(1+0.05) where ename=i.ename;
    --付出的成本
    temp_sal:=temp_sal+i.sal*0.05;
  end loop;
  dbms_output.put_line(temp_sal);
end;

/*
declare
  v_temp number(10):=0;
begin
  add_sal('crm',v_temp);
end;
*/

触发器

--触发器helloworld
create or replace trigger hello_trigger
after
  update on emp
for each row
begin
  dbms_output.put_line('helloworld');
end;

create or replace trigger del_trigger
before
delete on emp
for each row
begin
  insert into warn values('有数据被删了');
end;