-
plsql语句 逻辑查看全部
-
@+路径名 就可以执行sql语句脚本查看全部
-
plsql 语言 设计时需要 首先想明白的两个问题 sql语句; 变量:1.初始值是多少 2.最终值如何得到查看全部
-
瀑布模型: 需求分析->设计->编码->测试->上线查看全部
-
申明的时候 自定义 例外 (例外的类型 为exception) 然后 raise 抛出例外 然后和其他例外一样 捕获意外 就可以了查看全部
-
开发5部曲查看全部
-
错误信息查看全部
-
begin open cdno; loop fetch cdno into pdno,pdname; exit when cdno%notfound; count1:=0;count2:=0;count3:=0;csum:=0;avggrade:=0; open cgrade(pcourseName,pdno); loop fetch cgrade into pgrade; exit when cgrade%notfound; if pgrade<60 then count1:=count1+1; elseif pgrade>=60 and pgrade<=85 then count2:=count2+1; else count3:=count3+1; end if; csum:=csum+pgrade; end loop; avggrade:=csum/(count1+count2+count3); exception when zero_divide then avggrade:=0; close cgrade; insert into msg1 values(pdno,pdname,count1,count2,count3,avggrade); end loop; close cdno; commit; dbms_output.put_line('统计完毕'); end; /查看全部
-
--按系名分段统计(成绩小于60分，60-85分，85分以上)“大学物理”课程各分数段的学生人数，及各系学生的平均成绩. create table msg1(coursename varchar2(20),dname varchar2(20),count1 number, count2 number,count3 number,avggrade number); set serveroutput on declare cursor cdno is select dno,dname from dep; pdno dep.dno%type; pdname dep.dname%type; cursor cgrade(ccname cno.cname%type,cdno student.dno) is select grade from sc where cno=(select cno from course where cname=ccname) and sno in (select sno from student where dno=cdno); pgrade sc.grade%type; count1 number; count2 number; count3 number; csum number; avggrade number; pcourseName varchar2:='大学物理';查看全部
-
create table msg (deptno number,count1 number,count2 number,count3 number,saltotal number); -- 实现按部门分段(6000以上、(6000,3000)、3000元以下)统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金) set serveroutput on declare cursor cDept is select deptno from dept; cursor cSal(dno number) is select sal from emp where deptno=dno; pdeptno dept.deptno%type;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;salTotal=0; open cSal(pdeptno); loop fetch cSal into psal; exit when cSal%notfound; if psal >=6000 then count1:=count1+1; elseif psal>3000 and psal<6000 then count2:=count2+1; else count3:=count3+1;end if; end loop; salTotal:=salTotal+psal;close cSal; insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0)); --若salTotal为null则设为0 end loop;close cDept; commit; dbms_output.put_line('统计完成'); end; /查看全部
-
-- 涨工资，从最低工资开始涨，涨后工资不能超过50000 set serveroutput on declare cursor cemp is select empno,sal from emp order by sal; pempno emp.empno%type; psal emp.empno%type; countEmp number:=0; salTotal number; salTemp number; begin select sum(sal) into salTotal from emp; open cemp; loop exit when salTotal>50000; fetch cemp into pempno,psal; exit when cemp%notfound; salTemp:=psal*1.1; exit when salTemp+salTotal>50000; 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||'涨后的工资总额：'||salTotal); 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 cout80:= count80+1; else if phiredate='1981' then count81:=count81+1; else if 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; /查看全部
-
-- 自定义例外: 1. 定义变量，类型是 exception 2. 使用raise抛出自定义例外 -- 查询50号部门的员工姓名 set serveroutput on declare -- 定义光标，代表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%nofound then -- 抛出例外 raise no_emp_found; end if; -- oracle自动启动 pmon(process monitor)处理系统遗留的垃圾和资源 close cemp; exception when no_emp_found then dbms_output.put_line('没有找到员工'); when others then dbms_output.put_line('其他例外'); end; /查看全部
-
-- 系统例外: value_error set serveroutput on declare pnum number; begin pnum := 'abc'; exception when value_error then dbms_output.put_line('算术或者转换错误'); when others then dbms_output.put_line('其他例外'); end; /查看全部
-
-- 系统例外：被0除：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('1:0不能作除数'); when others then dbms_output.put_line('其他例外'); end; /查看全部
举报
0/150
提交
取消