PL/SQL学习笔记_set serveroutput on declare cursor cemp is select -CSDN博客

本文链接：https://blog.csdn.net/u013287668/article/details/51428195

一、PL/SQL变量

1、变量类型

基本类型：

char varchar2,date,number,boolean,long

declare

pnumber number(7,2);

pname varchar(20);

pdate date;

begin

pnumber:=20; ---- 赋值 := => into

pname:='scott';

pdate:=sysdate;

dbms_output.put_line(pnumber||':'||pname||':'||pdate);

end;

/

引用型变量：

my_name emp.ename%type

declare

  pname emp.ename%type;

  psala emp.sal%type;

begin

  select ename,sal into pname,psala from emp where empno=7369;

  DBMS_OUTPUT.put_line(pname||':'||psala);

end;

/

记录型变量：

set SERVEROUTPUT ON;

declare

  emp_rec emp%rowtype;

begin

   select * into emp_rec from emp where empno=7369;

   dbms_output.put_line(emp_rec.ename||':'||emp_rec.sal);

end;

/

2、流程控制

判断:

set SERVEROUTPUT ON;

accept num prompt '请输入一个数字';

declare

pnum number:=&num;

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('您输入的是其他数字');

end if;

end;

/

循环:

while:

set SERVEROUTPUT ON;

declare

  pnum number:=1;

begin

  while pnum<11 loop

    DBMS_OUTPUT.PUT_line(pnum);

    pnum:=pnum+1;

  end loop;

end;

/

loop:

set SERVEROUTPUT ON;

declare

  pnum number:=1;

begin

loop

   exit when pnum>10;

   DBMS_OUTPUT.PUT_line(pnum);

   pnum:=pnum+1;

end loop;

end;

/

for:

declare pnum number:=1;

begin

  for pnum in 1..10 loop

    DBMS_OUTPUT.PUT_LINE(pnum);

  end loop;

end;

/

二、光标

cursor c1 is select ename from emp;

open c1;

fetch c1 to pename;

close c1;

参数设置，数据库默认是200,

alter system set open_cursors=400 scope=both;

scope:both,memory,spfile

打印出所有员工的姓名和薪水

set serveroutput on;

declare

cursor c1 is select ename,sal from emp;

pname emp.ename%type;

psal emp.sal%type;

begin

  open c1;

  loop

    fetch c1 into pname,psal;

    exit WHEN c1%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(pname||':'||psal);

  end loop;

  close c1;

end;

/

给员工涨工资,总裁1000，经理400，其他200

set serveroutput on;

declare

cursor c1 is select empno,job from emp;

pname emp.ename%type;

pjob emp.sal%type;

begin

  open c1;

  loop

    fetch c1 into pempno,pjob;

    exit WHEN c1%NOTFOUND;

if pjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;

elsif pjob='MANAGER' then update emp set sal=sal+400 where empno=pempno;

esle update emp set sal=sal+200 where empno=pempno;

end if;

  end loop;

  close c1;

commit;

end;

/

带参数的光标：

----带参数的光标

set SERVEROUTPUT ON;

declare

cursor c1(depno number)is select deptno from emp;

pname emp.ename%type;

psala emp.sal%type;

begin

open c1(10);

loop

exit when c1%notfound;

dbms_output.put_line(pname||':'||psala);

end loop;

close c1;

end;

/

三、异常

exception

when exption then ........

when others then .........

系统列外：

No_data_found

too_many_rows

自定义例外:

raise 抛出异常

if c1%notfound then raise exception

declare

声明异常

begin

定义异常

使用异常

end;

举例：

每年入职的员工人数

set serveroutput on;

declare

cursor cemp is select to_char(hiredate,'yyyy') from emp;

phiredate varchar2(4);

count80 number:=0;

count81 number:=0;

count82 number:=0;

count87 number:=0;

begin

open cemp;

loop

fetch cemp into phiredate;

exit when cemp%notfound;

if phiredate='1980' then count80:=count80+1;

elsif phiredate='1981' then count81:=count81+1;

elsif phiredate='1982' then count82:=count82+1;

else count87:=count87+1;

end if;

end loop;

close cemp;

dbms_output.put_line('1980'||':'||count80);

dbms_output.put_line('1981'||':'||count81);

dbms_output.put_line('1982'||':'||count82);

dbms_output.put_line('1987'||':'||count87);

end;

为员工涨工资，从最低工资涨起每人涨10%，但工资总额不能超过5万，请计算涨工资的人数和涨工资后的工资总额，并输出涨工资人数和工资总额

set serveroutput on;

declare

cursor cemp is select empno,sal from emp order by sal;

pempno emp.empno%type;

psal emp.sal%type;

totalsla number;

totalcnt number;

begin

select sum(sal) into totalsal from emp;

open cemp;

loop

fetch cemp into pempno,psal;

exit when totalsal>50000;

exit when cemp%notfound;

update set sal=sal*1.1 where empno=pempno;

totalcnt:=total+1;

end loop;

end cemp;

dbms_output.put_line('共有'||totalcnt||'涨工资'||','||'涨完后工资总额为'||totalsal)

end;

按部门分段统计(6000+,3000-6000,3000-)统计各工资段的职工人数，以及各部门的工资总额(工资总额中不包含奖金)

set serveroutput on;

declare

cursor cdept is select deptno from dept;

cursor cemp(depnum number) is select empno,sal from emp where deptno=depnum;

pempno emp.empno%type;

psal emp.sal%type;

pdeptno emp.deptno%type;

totalsal number;

count1 number;

count2 number;

count3 number;

begin

open cdept;

loop

fetch cdept into pdeptno;

select sum(sal) into totalsal from emp where deptno=pdeptno;

open cemp(pdeptno);

loop

fetch cemp into pempno,psal;

if psal<3000 then count1:=count1+1;

elsif 3000<psal<6000 then count2:count2+1;

else count3:=count3+1;

end if;

end loop;

close cemp;

insert into mssg(deptno,count1,count2,count3,sal)values(pdeptno,count1,count2,count3,totalsal);

end loop;

close cdept;

end;

四、存储过程

create or replace procedure name(parameter)

procedure_body;

eg:不带参数的helloworld

create or replace procedure sayhello

begin

DBMS_OUTPUT.PUT_LINE('hello world');

end;

执行存储过程：

1、 SQL> set serveroutput on; ----打开屏幕输出开关

SQL> exec sayhello();

2、

begin

sayhello();

end;

eg:带参数的存储过程

为指定的员工涨100块的工资，打印出涨前和涨后的薪水

--- 为指定的员工涨100块的工资，打印出涨前和涨后的薪水

create or replace procedure raisesalary(eno in number)

psala emp.sal%type;

begin

select sal into psala from emp where empno=eno;

update emp set sal=sal+100 where empno=eno;

dbms_output.put_line('涨前'||psala||' 涨后：'||(psala+100));

end;

五、函数

create or replace function name(param)

return type

plsql body;

eg：计算员工的年收入

create or replace function sumsalary(eno in number)

return number
as
psala emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psala,pcomm from emp where empno=eno;
return psala*12+pcomm;
end;
/

out参数

----查询员工的姓名、月薪和职位

create or replace procedure queryinfo(eno in number,pename out varchar2,psala out number,pjob out varchar2)

as

begin

select ename,sal,job into pename,psala,pjob from emp where empno=eno;

end;

/

六、触发器

应用场景：数据库审计，备份和恢复

create or replace trigger testtrigger

after | before

insert|delete|update[of 列名]

on table

for each row[where 条件] ---- -- 说明创建的是行级触发器

declare

begin

plsql_body

end;

触发器类型：