oracle高级pl/sql学习

学习视频链接：oracle数据库教程，错过必后悔

create table myemp(id int primary key,ename varchar(20),job varchar(10),hiredate date,sal varchar(10),comm varchar(10),depton varchar(10));
insert into myemp values('1','zhangsan','clerk','1980/12/1','1400','200','20');
insert into myemp values('2','lisi','salesman','1981/2/20','1700','300','30');
insert into myemp values('3','wangwu','president','1979/3/20','5000',null,'10');
insert into myemp values('4','zhaoliu','manager','1981/5/1','2850',null,'30'); --学习时创建的表 

--为所有员工涨100的工资
update myemp set sal = sal+100
------------------------------------
--输出hello world
--declare 
begin
     dbms_output.put_line('hello world');
end;                               
-----------------------------------
--基本数据类型变量和常量
declare 
  pname  varchar2(10);
  age  number(10) :=20;
begin
  pname := 'wangwu';
   dbms_output.put_line(pname);
   dbms_output.put_line(age);
end;                                   
-----------------------------------
 --引用数据类型变量
declare 
  pname myemp.ename%type;
  
begin
  select ename into pname from myemp where id = 1;
   dbms_output.put_line(pname);
end;                             
---------------------------------
--引用记录类型变量
declare 
  prec  myemp%rowtype;
 begin
  select * into prec from myemp where id = 1;
   dbms_output.put_line(prec.ename || ' '||prec.sal);
end;                             
----------------------------------
--if分支
declare
 pno  number(5) := #
begin
  if pno < 5 then
    dbms_output.put_line('编号小于5');
   end if  ;
end;
------------------------------------

declare
  pno number(5) := &num;
begin
  if pno = 1 then
    dbms_output.put_line('我是1');
  else
    dbms_output.put_line('我不是1');
  end if;
end;
----------------------------------

declare
  pno number(5) := &num;
begin
  if pno = 1 then
    dbms_output.put_line('我是1');
  elsif pno = 2 then
    dbms_output.put_line('我是2');
    elsif pno = 3 then
      dbms_output.put_line('我是3');
  else
    dbms_output.put_line('其他');
  end if;
end;
----------------------------------------

-----循环
--while循环
declare
  total number(4) := 0;
begin
  while total < 100 loop
    total := total + 1;
    dbms_output.put_line(total);
  end loop;
end;
------------------------------------

--最常用的循环方式
declare
  total number(4) := 0;
begin
  loop
  exit when total = 100;
  total := total + 1;
  dbms_output.put_line(total);
end loop;
end;
-------------------------------------

--适合于连续的数值的遍历
declare
  total number(4) := 0;
begin
  for total in 1 .. 100 loop
    dbms_output.put_line(total);
  end loop;
end;
-------------------------------------
---游标cursor
declare
  pemp myemp%rowtype;     --定义记录型变量
  cursor pc is
    select * from myemp;      --定义游标
begin
  open pc;
  loop
    fetch pc
      into pemp;
    exit when pc%notfound;
    dbms_output.put_line(pemp.id || ' ' || pemp.ename);
  end loop;
  close pc;        --关闭游标，释放缓存
end;
-------------------------------------------------
--按员工工种涨工资，总裁涨1000元，经理涨800元，其他人员涨400元
declare
  prec myemp%rowtype;
  cursor c1 is
    select * from myemp;
  addsal number(4);
begin
  open c1;
  loop
    fetch c1
      into prec;
    exit when c1%notfound;
    if prec.job = 'president' then
      addsal := 1000;
    elsif prec.job = 'manager' then
      addsal := 800;
    else
      addsal := 800;
    end if;
    update myemp t set t.sal = t.sal + addsal where t.id = prec.id;
  end loop;
  close c1;
  commit;
end;
------------------------------------------------
---带有参数的游标（为部门号为30的员工涨1000工资）
declare
  prec myemp%rowtype;        
  cursor c2(dno myemp.depton%type) is
    select * from myemp t where t.depton = dno;
begin
  open c2(30);     --打开带有参数游标，除了此处其他的地方游标都不用带参数
  loop
    fetch c2
      into prec;
    exit when c2%notfound;
    update myemp t set t.sal = t.sal + 1000 where t.id = prec.id;
  end loop;
  close c2;
  commit;
end;
------------------------------------------------------
----例外（异常）
declare
  pnum number(4) := 5;
begin
  pnum := pnum / 0; --发生异常
exception   --处理异常
  --
  when zero_divide then
    dbms_output.put_line('被零除');
  when value_error then
    dbms_output.put_line('数值转换错误');
  when others then
    dbms_output.put_line('其他异常');
end;
------------------------------------------------------
----查询部门员工编号是50的员工
declare
  prec myemp%rowtype;
  cursor c1 is
    select * from myemp t where t.depton = 50;
    no_date exception;    --异常类型的定义
begin
  open c1;
  loop
    fetch c1
      into prec;
    if c1%notfound then
      raise no_date;
    end if;
  end loop;
exception
  when no_date then
    dbms_output.put_line('没有员工');
  when others then
    dbms_output.put_line('其他异常');
end;
--------------------------------------------------
--------存储过程
--1.创建一个输出helloworld的存储过程
create or replace procedure helloworld
is 
begin
dbms_output.put_line('hello world');  
 end helloworld;
 
--2.给指定的员工涨100工资，并打印出涨前和涨后的工资
create or replace procedure addsal(pid in myemp.id%type) as
--定义变量
  prec myemp%rowtype;
begin
  select * into prec from myemp t where t.id = pid;
  update myemp t set t.sal = t.sal + 100  where t.id = pid;        --事务的提交要在调用端做
  dbms_output.put_line('涨工资前是：  ' || prec.sal || '涨工资后是:' ||
                       (prec.sal + 100));
end;

--3.计算指定员工的年薪
 create or replace procedure addysal(pid in myemp.id%type , ysal out number) is
psal myemp.sal%type;
prcomm myemp.comm%type;
begin
  --多个值的复制，是按照先后顺序来复制的
  select t.sal,t.comm into psal,prcomm from myemp t where t.id = pid;
  ysal := psal*12 + nvl(prcomm,0);
  
end addysal;
---------------------------------------------------
--通过pl/sql调用存储过程
declare
 ysal number;
 begin
 addysal(1,ysal);
 dbms_output.put_line(ysal);
 end;
-------------------------------------------------

--存储函数
create or replace function countysalf(pid in myemp.id%type) return number is
  Result number;
  psal   myemp.sal%type;
  prcomm myemp.comm%type;
begin
  select t.sal, t.comm into psal, prcomm from myemp t where t.id = pid;
  result := psal * 12 + nvl(prcomm, 0);

  return(result);
end countysalf;
--一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数，在过程和函数中实现返回多个值。建议使用存储过程。