--打开输出开关
set serveroutput on--PLSQL语法格式
declare--说明部分
begin--程序dbms_output.put_line('Hello World');
end;
/
--if 语句
--判断用户从键盘上输入的数字
set serveroutput on--接收键盘输入
--num: 地址值，在地址上保存了输入的数字
accept num prompt '请输入一个数字';declare--定义变量，保存输入的数字pnum number := #
beginif 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;
/
--循环语句
--打印1~10
set serveroutput on
declarepnum number := 1;
beginloop--退出条件exit when pnum > 10;dbms_output.put_line(pnum);--加一pnum := pnum + 1;end loop;
end;
/
--引用型变量
--查询并打印7839的姓名和薪水
set serveroutput ondeclare--定义变量保存姓名和薪水--pename varchar(20);--psal number;pename emp.ename%type;psal emp.sal%type;
begin--得到姓名和薪水select ename,sal into pename,psal from emp where empno=7839;dbms_output.put_line(pename||'的薪水是'||psal);
end;
/
--记录型变量
--查询并打印7839的姓名和薪水
set serveroutput on
declare--定义记录型变量：代表一行emp_rec emp%rowtype;
beginselect * into emp_rec from emp where empno=7839;dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
--光标
--查询并打印员工的姓名和薪水
/*
1. 光标的属性%isopen %rowcount (影响的行数)%found %notfound*/
set serveroutput on
declare-- 定义光标cursor cemp is select ename,sal from emp;pename emp.ename%type;psal emp.sal%type;
begin--打开光标open cemp;loop--取一条记录fetch cemp into pename,psal;--exit when 没有取到记录;exit when cemp%notfound;dbms_output.put_line(pename||'的薪水是'||psal);end loop;--关闭光标close cemp;
end;
/
--带参数的光标
--查询某个部门的员工姓名
set serveroutput on
declarecursor cemp(dno number) is select ename from emp where deptno=dno;pename emp.ename%type;
beginopen cemp(20);loopfetch cemp into pename;exit when cemp%notfound;dbms_output.put_line(pename);end loop;close cemp;
end;
/
--涨工资，总裁1000 经理800 其他400
set serveroutput ondeclare--员工的集合--alter table "SCOTT"."EMP" rename column "JOB" to empjobcursor cemp is select empno,empjob from emp;pempno emp.empno%type;pjob emp.empjob%type;
beginrollback;open cemp;loop--取一个员工fetch cemp into pempno,pjob;exit when cemp%notfound;--判断职位if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;else update emp set sal=sal+400 where empno=pempno;end if;end loop;close cemp;--提交 ----> ACIDcommit;dbms_output.put_line('完成');
end;
/
--系统例外
--被0除
set serveroutput ondeclare pnum number;
beginpnum := 1/0;exceptionwhen zero_divide then dbms_output.put_line('1:0不能做被除数');dbms_output.put_line('2:0不能做被除数');when value_error then dbms_output.put_line('算术或者转换错误');when others then dbms_output.put_line('其他例外');
end;
/
--自定义例外
--查询并打印50号部门的员工姓名
set serveroutput ondeclarecursor cemp is select ename from emp where deptno=50;pename emp.ename%type;--自定义例外no_emp_found exception;
beginopen cemp;--取一条记录fetch cemp into pename;if cemp%notfound then --抛出例外raise no_emp_found;end if; --自动启动进程：pmon(process monitor) 清理现场close cemp;exceptionwhen no_emp_found then dbms_output.put_line('没有找到员工');when others then dbms_output.put_line('其他例外');
end;
/
--实例1
/*
SQL语句
select to_char(hiredate,'yyyy') from emp;
-->光标 --> 循环 --> 退出条件:notfound变量：1. 初始值 2. 最终如何得到
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
*/
set serveroutput on
declarecursor cemp is select to_char(hiredate,'yyyy') from emp;phiredate varchar2(4);count80 number := 0;count81 number := 0;count82 number := 0;count87 number := 0;
beginopen 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('Total:'||(count80+count81+count82+count87));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;
/
--实例2
/*
SQL语句
select empno,sal from emp order by sal;
--> 光标 --> 循环 --> 退出：1. 总额 > 5w 2. notfound变量：1. 初始值 2. 最终如何得到
涨工资的人数: countemp number := 0;
涨后的工资总额： salTotal number;
1. select sum(sal) into salTotal from emp;
2. 涨后= 涨前 + sal * 0.1练习：工资总额不能超过5w
*/
set serveroutput on
declarecursor cemp is select empno,sal from emp order by sal;pempno emp.empno%type;psal emp.sal%type;--涨工资的人数: countemp number := 0;--涨后的工资总额： salTotal number;
begin--工资总额的初始值select sum(sal) into salTotal from emp;open cemp;loop--1. 总额> 5wexit when salTotal>50000;--取一个员工fetch cemp into pempno,psal;--2. notfoundexit when cemp%notfound;--涨工资update emp set sal=sal*1.1 where empno=pempno;countemp := countemp + 1;--2. 涨后= 涨前 + sal * 0.1salTotal := salTotal + psal * 0.1;end loop;close cemp;commit;dbms_output.put_line('人数:'||countemp||' 涨后的工资总额:'||salTotal);
end;
/
--实例3
/*
SQL语句
部门：select deptno from dept; --> 循环
部门中员工的薪水：select sal from emp where deptno=?? --> 循环变量：1. 初始值 2. 最终如何得到
每个段的人数
count1 number; count2 number; count3 number;
部门的工资总额
saltotal number := 0;
1. select sum(sal) into salTotal from emp where deptno=??
2. 累加
*/
set serveroutput on
declare--部门cursor cdept is select deptno from dept;pdeptno dept.deptno%type;--部门中员工的薪水cursor cemp(dno number) is select sal from emp where deptno=dno;psal emp.sal%type;--每个段的人数count1 number; count2 number; count3 number;--部门的工资总额saltotal number := 0;
begin--打开部门open cdept;loop--取一个部门fetch cdept into pdeptno;exit when cdept%notfound;--初始化count1:=0;count2:=0;count3:=0;--部门的工资总额select sum(sal) into salTotal from emp where deptno=pdeptno;--取部门中员工的薪水open cemp(pdeptno);loop-- 取一个员工的薪水fetch cemp into psal;exit when cemp%notfound;--判断if psal < 3000 then count1:=count1+1;elsif psal>=3000 and psal<6000 then count2:=count2 + 1;else count3:=count3+1;end if;end loop;close cemp;--保存当前的结果insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));end loop;close cdept;commit;dbms_output.put_line('完成');end;
/