plSql学习日志
文章目录
基础语法
plsql的HelloWorld
set serveroutput on--打印Hello Worlddeclare --说明部分(变量，光标或者例外说明)begin --程序体（DML语句） dbms_output.put_line('Hello World');end;基本变量类型的使用
-- 使用基本变量类型declare --定义基本变量类型 --基本数据类型 pnumber number(7,2); --字符串变量 pname varchar(20); --日期变量 pdate date;begin pnumber := 1; DBMS_OUTPUT.PUT_LINE(pnumber); pname := 'Tom'; DBMS_OUTPUT.PUT_LINE(pname); pdate := sysdate; DBMS_OUTPUT.PUT_LINE(sysdate); --计算明天的日期 DBMS_OUTPUT.PUT_LINE(pdate + 1);end;引用型变量和记录型变量
- 引用型变量
引用表中一列的类型作为我定义的变量类型
-- 引用型变量set serveroutput ondeclare --定义引用变量:查询打印7839的姓名和薪水 --pename varchar2（20） --psal number; pename emp.ename%type; psal emp.sal%type;begin --得到7839的姓名和薪水 select ename,sal into pename,psal from emp where empno = 7839; --打印姓名和薪水 dbms_output.put_line(pename||'的薪水是'||psal);end;- 记录型变量
存储一行的数据类型的数组
set serveroutput on --定义记录变量:查询打印7839的姓名和薪水 set serveroutput on declare --定义记录型变量：注意代表一行 emp_rec emp%rowtype;begin --得到7839一行的信息 select * into emp_rec from emp where empno = 7839; --打印姓名和薪水 dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);end;If语句的使用
/*判断用户从键盘输入的数字1.如何使用if语句2.接收一个键盘输入(字符串)*/set serveroutput on--接收一个键盘输入--num:地址值，含义是:该地址上保存了输入的值accept num prompt '请输入一个数字';declare --定义变量保存从键盘上输入的数字 pnum number := #begin --执行if语句进行条件判断 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
--使用while循环打印数字1—10set serveroutput ondeclare --定义循环变量 pnum number := 1;begin while pnum <= 10 loop --循环体 dbms_output.put_line(pnum); --使该变量+1 pnum := pnum + 1; end loop;end;- loop
--使用loop循环打印数字1—10set serveroutput ondeclare --定义循环变量 pnum number := 1;begin loop --退出条件：循环变量大于10 exit when pnum > 10; --打印该变量的值 dbms_output.put_line(pnum); --循环变量+1 pnum := pnum+1; end loop;end;光标
- 光标就是一个结果集(result set)
- 例子1
--光标的使用例子:查询打印所有员工的薪水set serveroutput on/** %found %notfound*/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; - 例子2
--光标的使用例子:给员工涨工资,总裁1000,经理800,其他400set serveroutput on/** %found %notfound*/declare --定义一个光标代表给哪些员工涨工资 cursor cemp is select empno,empjob from emp; --为光标定义对应的变量 pempno emp.empno%type; pjob emp.empjob%type;begin --打开光标 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 = 'manger' 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; --对于oracle，默认的事务隔离级别是read committed commit; dbms_output.put_line('涨工资完成');end;- 例子3：带参数的光标定义
--查询某个部门中员工的姓名set serveroutput ondeclare --定义带参数的光标 cursor cemp(dno number) is select ename from emp where deptno = dno; pename emp.ename%type;begin --打开光标 open cemp(20); loop --取出每个员工的姓名 fetch cemp into pename; exit when cemp%notfound; dbms_output.put_line(pename); end loop;end;光标属性
/**光标的属性 %found %notfound %isopen:判断光标是否打开 %rowcount:影响行数*/set serveroutput ondeclare --定义光标 cursor cemp is select empno,empjob from emp; pempno emp.empno%type; pjob emp.empjob%type;begin open cemp; if cemp%isopen then dbms_output.put_line('光标已经打开'); else dbms_output.put_line('光标没有打开'); end if; loop --取出一条记录 fetch cemp into pempno,pjob; exit when cemp%notfound; --打印rowcount的值 dbms_output.put_line('rowcount:' || cemp%rowcount); end loop; close cemp;end;光标数限制
默认情况下oracle只允许在同一个回话中，打开300个光标。
- 在管理员用户下查询当前允许打开多少个光标
SQL>show parameter cursor
| NAME | TYPE | VALUE |
|---|---|---|
| cursor_bind_capture_destination | string | memory+disk |
| cursor_invalidation | string | IMMEDIATE |
| cursor_sharing | string | EXACT |
| cursor_space_for_time | boolean | FALSE |
| open_cursors | integer | 300 |
| session_cached_cursors | integer | 50 |
- 修改光标数的限制
SQL>alert system set open_cursors = 400 scope = both;
scope的取值:both,memory(只更改当前实例，不更改配置文件),spfile(数据库需要重启)
例外（异常）
-
例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。
-
系统例外
--系统例外:no_data_foundset serveroutput ondeclare pename emp.ename%type;begin --查询员工是1234的员工姓名 select ename into pename from emp where empno = 1234;exception when no_data_found then dbms_output.put_line('没有找到该员工'); when others then dbms_output.put_line('其他例外');end;--系统例外:too_many_rowsset serveroutput ondeclare pename emp.ename%type;begin --查询员工是1234的员工姓名 select ename into pename from emp where deptno = 20;exception when too_many_rows then dbms_output.put_line('select into 匹配多行'); when others then dbms_output.put_line('其他例外');end;--系统例外:zero_divideset serveroutput ondeclare pnum number;begin pnum := 1/0;exception when zero_divide then dbms_output.put_line('0不能作为除数'); dbms_output.put_line('erro x/0'); when others then dbms_output.put_line('其他例外');end;--系统例外:value_errorset serveroutput ondeclare pnum number;begin pnum := 'abc';exception when value_error then dbms_output.put_line('算术或者转换异常'); when others then dbms_output.put_line('其他例外');end;- 自定义例外
--系统例外:value_errorset serveroutput ondeclare --定义光标,代表50号部门的员工姓名 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; dbms_output.put_line('抛出例外了，这里不执行'); --oracle自动启动pmon(process monitor) --关闭光标 close cempp;exception when value_error then dbms_output.put_line('算术或者转换异常'); when others then dbms_output.put_line('其他例外');end;案例
/*需求统计2021年—2022年每年入职的员工人数SQL语句select to_char(hiredate,'yyyy') from emp;->关标->循环->退出条件:notfound变量:1.初始值 2.如何得到每年入职的员工人数:count21 number := 0;count22 number := 0;*/set serverout on;declare cursor cemp is select to_char(hiredate,'yyyy') from emp; phiredate varchar2(4); count21 number := 0; count22 number := 0;begin open cemp; loop fetch cemp into phiredate; exit when cemp%notfound; if phiredate = '2022' then count22 := count22+1; elsif phiredate = '2021' then count21 := count21+1; end if; end loop; dbms_output.put_line('2022年入职的人数有:'||count22); dbms_output.put_line('2021年入职的人数有:'||count21); close cemp;end;/*需求:为员工涨工资。从最低工资涨起每人涨10%，但所有人的工资总额不能超过5w，请计算涨工资的人数和涨工资后的工资总额，并输出涨工资的人数和工资总额。*/set serveroutput ondeclarecursor cemp is select empno,sal from emp order by sal asc;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、工资总额大于5w exit when salTotal > 50000; --取一个员工涨工资 fetch cemp into pempno,psal; --退出循环条件2 %notfound exit when cemp%notfound; --涨后的工资总额=涨前的工资总额 + sal*0.1 salTotal := salTotal + psal*0.1; if salTotal >= 50000 then --把salTotal还原 salTotal := salTotal - psal*0.1; elsif salTotal < 50000 then --涨工资 update emp set sal = sal*1.1 where empno = pempno; --人数 + 1 countEmp := countEmp + 1; end if; end loop; close cemp; commit; dbms_output.put_line('涨工资人数:'||countEmp||' 涨后的工资总额:'||salTotal);end;/**实现按部门分段（6000以上、（6000、3000）、3000以下）统计各个工资的职工人数、以及*各部门的工资总额（工资总额中不包括奖金）*创建表msg存储统计数据* create table msg(deptno number,count1 number,count2 number,count3 number,saltotal number)*/set serveroutput ontruncate table msg;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;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;用plsql语言编写一个程序。按系（系名）分段同(成绩小于60分，60-80分，85分以上)
"大学物理"课程各分数段学生的人数，以及各系学生人数，及各系学生的平均成绩。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iuTnYMe5-1645290725099)(img/1.png)]
创建表存储统计结果
create table msg1( coursename varchar2(20), dname varchar2(20), count1 number, count2 number, count3 number, avggrade number);分析:
1、得到有哪些系
select dno,dname from dep;–>光标–>循环–>退出条件:notfound
2、得到系中，选修了"大学物理"课程学生的成绩
select grade from sc where cno=(select cno from where cname=??)
and sno in (select sno from student where dno=??)
带参数的光标 —> 循环 —> 退出条件:notfound
变量：1.初始值 2.如何得到
每个分数段人数
count number;count2 number; count3 number;
每个系选修了"大学物理"学生的平均成绩
avggrade number;
1、算术运算
select avg(grade) into avggrade from sc where cno = (select cno from course where cname=??)
and sno in (select sno from student where dno=??);
set serveroutput ondeclare --系的光标 cursor cdept is select dno,dname from dep; pdno dep.dno%type; pdname dep.dname%type; --成绩光标 cursor cgrade(coursename varchar2,depno number) is select grade from sc where cno=(select cno from course where cname=coursename) and sno in (select sno from student where dno=depno); pgrade sc.grade%type; --每个分数段的人数 count1 number; count2 number; count3 number; --每个系选修了"大学物理" 学生的平均成绩 avggrade number; --课程名称 pcourseName varchar2(20):='大学物理'; begin --打开系的光标 open cdept; loop --取一个系的信息 fetch cdept into pdno,pdname; exit when cdept%notfound; --初始化工作 count1:=0;count2:=0;count3:=0; --系的平均成绩 select avg(grade) into avggrade from sc where cno=(select cno from course where cname=pcourseName) and sno in (select sno from student where dno=pdno); --取系中,选修了大学物理的学生成绩 open cgrade(pcourseName,pdno); loop --取出一个学生的成绩 fetch cgrade into pgrade; exit when cgrade%notfound; --判断成绩的范围 if pgrade<60 then count1:=count1+1; elsif pgrade>=60 and pgrade<85 then count2:=count2+1; else count3:=count3+1; end if; end loop; close cgrade; --保存当前的结构 insert into msg1 values(pcourseName,pdname,count1,count2,count3,avggrade); end loop; --关闭系光标 close cdept; commit; dbms_output.put_line('统计完成');end;