PLSQL基础学习

/*
简单plsql语句 
set serveroutput on
*/
declare 
  pnumber number(7,2);
  pname varchar2(20);
  pdate date;
begin
  pnumber:=1;
  dbms_output.put_line('数字:'||pnumber);
  
  pname:='飞鸟与蝉';
  dbms_output.put_line(pname);
  
  pdate:=sysdate;
  dbms_output.put_line(pdate);
  
  dbms_output.put_line(pdate+1);
end;
/
--使用引用型变量
declare 
  pjgdm tb_djd_yyxt_yjxx_sj.jgdm%type;
  pjgmc tb_djd_yyxt_yjxx_sj.jgmc%type;
begin
  select jgdm,jgmc into pjgdm,pjgmc from tb_djd_yyxt_yjxx_sj  where tb_djd_yyxt_yjxx_sj.id=1080754;
  dbms_output.put_line(pjgdm||'的名称是:'||pjgmc);
end;
/
--使用记录型变量
declare 
  --定义记录型变量：注意代表一行
  prow tb_djd_yyxt_yjxx_sj%rowtype;
begin
  select * into prow from tb_djd_yyxt_yjxx_sj where tb_djd_yyxt_yjxx_sj.id=1080754;
  dbms_output.put_line(prow.jgdm||'的名称是:'||prow.jgmc);
end;
/

/*
判断用户从键盘输入的数字
1.如何使用if语句
2.接收一个键盘输入(字符串)
set serveroutput on
accept num prompt'请输入一个数字';
*/
declare
  pnum number:=#--num:地址值，含义是该地址上保存输入的值。类似C++
begin
  if pnum=0 then dbms_output.put_line('您输入的数字是0');
   elsif pnum=1 then dbms_output.put_line('您输入的数字是1');
   elsif pnum=2 then dbms_output.put_line('您输入的数字是2');
   else dbms_output.put_line('您输入的数字是'||pnum);
  end if;
end;
/
--使用while循环
declare 
  pnum number:=1;
begin
  while pnum<=5 loop
    dbms_output.put_line('while:'||pnum);
    pnum:=pnum+1;
  end loop;
end;
/
  
--loop循环
declare 
  pnum number:=1;
begin
loop
  exit when pnum>5;
  dbms_output.put_line('loop:'||pnum);
  pnum:=pnum+1;
end loop;
end;
/
--for循环
declare 
begin
  for pnum in 1..5 loop
    dbms_output.put_line('for:'||pnum);
  end loop;
end;
/


--cursor光标(结果集)
declare 
  cursor cs is select jgdm,jgmc from tb_djd_yyxt_yjxx_sj  where tb_djd_yyxt_yjxx_sj.id in (1080754,1080755);
  pjgdm tb_djd_yyxt_yjxx_sj.jgdm%type;
  pjgmc tb_djd_yyxt_yjxx_sj.jgmc%type;
  
begin
  --打开光标
  open cs;
  loop
    fetch cs into pjgdm,pjgmc;
  exit when cs%notfound;
    dbms_output.put_line(pjgdm||'的名称是:'||pjgmc);
  end loop;
  --关闭光标
  close cs;
end;


--带参数的光标
declare 
  cursor cs(pid number) is select yjhm,product from tb_djd_xyd_yjxx_sj_his  where tb_djd_xyd_yjxx_sj_his.id=pid ;
  yjhm varchar2(50);
  product varchar2(50);
  
begin
  --打开光标
  open cs(71854991);
  loop
    fetch cs into yjhm,product;
  exit when cs%notfound;
    dbms_output.put_line(yjhm||'的名称是:'||product);
  end loop;
  --关闭光标
  close cs;
end;
/

--例外,then相当于大括号，可以写多条语句
declare 
  pnum number;
begin
  pnum:='哈哈';
exception
  when value_error then dbms_output.put_line('算数或转换异常');
  when others then dbms_output.put_line('其他异常');
end;
/

--自定义例外
declare 
  cursor cs(pid number) is select yjhm,product from tb_djd_xyd_yjxx_sj_his  where tb_djd_xyd_yjxx_sj_his.id=pid ;
  yjhm varchar2(50);
  product varchar2(50);
  not_found exception;
  
begin
  --打开光标
  open cs(1);
  fetch cs into yjhm,product;
  if cs%notfound then
    raise not_found;
  end if;
  --关闭光标
  close cs;
  exception 
    when not_found then dbms_output.put_line('未找到数据');
    when others then dbms_output.put_line('其他例外');
end;
/