HelloWorld
SQL> declare
2 -- 变量的说明
3 begin
4 --程序体
5 --程序包 package
6 dbms_output.put_line('Hello World');
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> --打开输出开关
SQL> set serveroutput on
SQL> /
Hello World
PL/SQL 过程已成功完成。
Procudure Language/SQL 面向过程得到语言
--引用型变量: 查询并打印7839的姓名和薪水
set serveroutput on
declare
--定义变量
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;
begin
--得到一行
select * into emp_rec from emp where empno=7839;
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
--if语句:判断用户输入的数字
set serveroutput on
/*
1.提示信息
2. 接收键盘输入
num 是一个地址值
SQL优化: num绑定变量(尽量使用绑定变量)
select * from emp where deptno=10; --> 执行计划
select * from emp where deptno=20; --> 执行计划
-->
select * from emp where deptno=#
*/
accept num prompt '请输入一个数字';
declare
--变量保存输入的数字
pnum number := #
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;
/
--循环: 打印1~10
set serveroutput on
declare
pnum number := 1;
begin
loop
--退出 成立退出 不成立循环
exit when pnum > 10;
--隐式转换
dbms_output.put_line(pnum);
pnum := pnum + 1;
end loop;
end;
/
光标
--光标: 使用游标查询员工姓名和工资,并打印
/*
光标的属性:
%isopen 是否被打开
%rowcount 行数
%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 cemp%notfound;
dbms_output.put_line(pename||'的薪水是'||psal);
end loop;
close cemp;
end;
/
--给员工涨工资 总裁1000 经理800 其他400
set serveroutput on
/*
SQL> show parameters cursor
NAME TYPE VALUE
------------------------------------ ----------- --------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 20
*/
declare
--光标代表员工
cursor cemp is select empno,empjob from emp;
pempno emp.empno%type;
pjob emp.empjob%type;
begin
rollback;
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;
--提交: 隔离级别
commit;
dbms_output.put_line('完成');
end;
/
--带参数的光标:查询某个部门的员工姓名
set serveroutput on
declare
cursor cemp(pdno number) is select ename from emp where deptno=pdno;
pename emp.ename%type;
begin
open cemp(20);
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
/
异常
补充一下 java采用的是父类异常处理机制 子类处理不了 抛给父类
/*
Zero_Divide ( 被零除)
*/
set serveroutput on
declare
pnum number;
begin
pnum := 1/0;
exception
when 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 on
declare
cursor cemp is select ename from emp where deptno=50;
pename emp.ename%type;
--自定义例外
no_emp_found exception;
begin
open cemp;
--取一个员工
fetch cemp into pename;
if cemp%notfound then
raise no_emp_found;
end if;
/*
if cemp%isopen then
close no_emp_found;
end if;
*/
close cemp;
exception
when 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;
*/
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('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;
/
/*
为员工长工资。从最低工资调起每人长10%,但工资总额不能超过5万元,
请计算长工资的人数和长工资后的工资总额,并输出输出长工资人数及工资总额。
可能的SQL:
员工: select empno,sal from emp order by sal;
长工资后的工资总额:1. 对sal进行累加: 新的工资总额=旧的工资 + sal*0.1;
2. sum(sal): 查询数据库
练习: 工资不能超过5w
*/
set serveroutput on
declare
--员工
cursor 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
--工资总额>5w
exit when salTotal > 50000;
--取一个员工
fetch cemp into pempno,psal;
exit when cemp%notfound;
--涨工资
update emp set sal=sal*1.1 where empno=pempno;
--人数
countEmp := countEmp +1;
--工资总额
salTotal := salTotal + psal * 0.1;
end loop;
close cemp;
commit;
--输出
dbms_output.put_line('长工资的人数:'|| countEmp);
dbms_output.put_line('工资总额:'|| salTotal);
end;
/
/*
用PL/SQL语言编写一程序,实现按部门分段(6000以上、(6000,3000)、3000元以下)
统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金)
SQL语句:
部门: select deptno from dept;
员工的工资: select sal from emp where deptno=???
工资总额: select sum(sal) from emp where deptno=???
*/
set serveroutput on
declare
--部门
cursor cdept is select deptno from dept;
pdno 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;
begin
open cdept;
loop
--取部门
fetch cdept into pdno;
exit when cdept%notfound;
--初始化
count1 :=0;count2:=0;count3:=0;
select sum(sal) into salTotal from emp where deptno=pdno;
--取部门中的员工
open cemp(pdno);
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 msg1 values(pdno,count1,count2,count3,nvl(salTotal,0));
end loop;
close cdept;
commit;
dbms_output.put_line('完成');
end;
/