Oracle数据库开发必备利器之PL/SQL基础_技术笔记

哈德司 2022-02-13

/*
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
declare
--定义光标
cursor c_emp is select to_char(hiredate,'yyyy') from emp;
p_hiredate varchar2(4);
--每年入职的员工人数
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
begin
--打开光标
open c_emp;

loop
--取出一个员工的入职年份
fetch c_emp into p_hiredate;
exit when c_emp%notfound;

--判断入职年份
if p_hiredate = '1980' then count80 :=count80+1;
elsif p_hiredate = '1981' then count81 :=count81+1;
elsif p_hiredate = '1982' then count82 :=count82+1;
else count87 := count87+1;
end if

end loop;

--输出结果
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);
--关闭光标
close c_emp;
end;
/

0赞 · 0采集
斯巴达汉子 2022-02-06

/**
统计每年入职的员工
*/
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;
/

0赞 · 0采集
BZzzzzzz 2019-12-12

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 count80:=count81+1;
elsif phiredate = '1982' then count80:=count82+1;
else count87:=count87+1;
end if;
close cemp;

dbms.output.put_line();
end;
/

0赞 · 0采集
慕斯2513316 2019-09-02

set serveroutput on

declare
cursor cm1 is select to_char(e.hiredate,'yyyy') from emp e;
pdate VARCHAR2(4);
count80 number:=0;
count81 number:=0;
count82 number:=0;

begin
open cm1;
loop
fetch cm1 into pdate;
exit when cm1%notfound;
if pdate='1980' then count80:=count80+1;
elsif pdate='1981' then count81:=count81+1;
elsif pdate='1982' then count82:=count82+1;
else count82:=count82+1;

end if;

end loop;

close cm1;
dbms_output.put_line('总共入职：'||(count80+count81+count82));
dbms_output.put_line('1980年入职：'||count80);

end;
/

0赞 · 0采集
碧水清风_0001 2018-11-19

统计每年入职的员工人数：需求分析过程

截图
0赞 · 0采集
象牙山四大金刚_尼古拉斯赵四 2018-04-07

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;
/

1赞 · 1采集
慕粉3289534 2017-10-02

-- 每年入职的员工人数 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; /

0赞 · 1采集
molmolmol 2017-08-05

先分析，后code

0赞 · 0采集
遵从我心 2017-04-14

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;

截图
0赞 · 0采集
遵从我心 2017-04-13

统计每年入职的员工人数分析

截图
0赞 · 0采集
浅笑心柔 2017-03-15

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('count80:'||count80); end; /

0赞 · 0采集
冲啊 2017-01-16

-- 统计每年员工的入职人数 DECLARE -- 每年员工入职人数 pcount NUMBER := 0; -- 年份 pyear VARCHAR2(4); -- 员工总人数 pnum NUMBER := 0; -- 定义游标 CURSOR cemp IS SELECT to_char(hiredate,'YYYY') AS hireyear,COUNT(*) FROM emp GROUP BY to_char(hiredate,'YYYY'); BEGIN -- 打开游标 OPEN cemp; --遍历 LOOP FETCH cemp INTO pyear,pcount; -- 循环开始立即检测数据 EXIT WHEN cemp%NOTFOUND; -- 打印每年员工入职人数 dbms_output.put_line(pyear || ' -> ' || pcount); -- 统计总人数 pnum := pnum + pcount; END LOOP; dbms_output.put_line('员工总人数:' || pnum); -- 关闭游标 CLOSE cemp; END; /

3赞 · 3采集
慕粉4273217 2016-10-26

dbms_output.put_line部分输出total时一定要带括号

0赞 · 0采集
乐乐201588 2016-09-28

--案例1：统计每年入职的员工人数，80,81,82,87年的 declare --定义一个游标存放所有员工的入职年份 cursor c_dates is select to_char(hiredate,'yyyy') from emp; p_hiredate varchar2(20); p_80num number :=0; p_81num number :=0; p_82num number :=0; p_87num number :=0; begin open c_dates; loop fetch c_dates into p_hiredate; exit when c_dates%notfound; if p_hiredate ='1980' then p_80num:=p_80num+1; elsif p_hiredate ='1981' then p_81num:=p_81num+1; elsif p_hiredate ='1982' then p_82num:=p_82num+1; elsif p_hiredate ='1987' then p_87num:=p_87num+1; end if; end loop; close c_dates; dbms_output.put_line('Total:'(p_80num+p_81num+p_82num+p_87num)); dbms_output.put_line('80入职的:'p_80num); dbms_output.put_line('81入职的:'p_81num); dbms_output.put_line('82入职的:'p_82num); dbms_output.put_line('87入职的:'p_87num); end; /

1赞 · 0采集
NeedForSpeed 2016-07-19

to_char(hiredate,'yyyy'); 将日期hiredate 抽取年的部分

0赞 · 0采集
canfeng918 2016-07-06

--案例1：统计每年入职的员工人数，80,81,82,87年的 declare --定义一个游标存放所有员工的入职年份 cursor c_dates is select to_char(hiredate,'yyyy') from emp; p_hiredate varchar2(20); p_80num number :=0; p_81num number :=0; p_82num number :=0; p_87num number :=0; begin open c_dates; loop fetch c_dates into p_hiredate; exit when c_dates%notfound; if p_hiredate ='1980' then p_80num:=p_80num+1; elsif p_hiredate ='1981' then p_81num:=p_81num+1; elsif p_hiredate ='1982' then p_82num:=p_82num+1; elsif p_hiredate ='1987' then p_87num:=p_87num+1; end if; end loop; close c_dates; dbms_output.put_line('Total:'(p_80num+p_81num+p_82num+p_87num)); dbms_output.put_line('80入职的:'p_80num); dbms_output.put_line('81入职的:'p_81num); dbms_output.put_line('82入职的:'p_82num); dbms_output.put_line('87入职的:'p_87num); end; /

0赞 · 0采集
默溟and沧桑 2016-04-21

获取结果

截图
0赞 · 0采集
默溟and沧桑 2016-04-21

后面部分

截图
0赞 · 0采集
默溟and沧桑 2016-04-21

实例每年入职员工

截图
0赞 · 0采集
纠结的小猴子 2016-04-21

代码编写

截图
0赞 · 0采集
纠结的小猴子 2016-04-21

代码编写

截图
0赞 · 0采集
纠结的小猴子 2016-04-21

代码编写

截图
0赞 · 0采集
人生二十如梦如幻 2015-11-06

统计入职年份的循环体

截图
0赞 · 0采集
人生二十如梦如幻 2015-11-06

统计入职时间（变量的定义以及光标的设定）

截图
0赞 · 0采集
smartsean 2015-11-04

在update前面加上 exit when (salTotal + psal*1.1) > 50000;

0赞 · 0采集
cc2424 2015-09-03

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:' || (count81+count82+count80+count87)); dbms_output.put_line('1980:'||count81); dbms_output.put_line('1982:'||count80); dbms_output.put_line('1981:'||count82); dbms_output.put_line('1987:'||count87); END; /

0赞 · 0采集
小陆豪客02 2015-08-09

代码编写

截图
0赞 · 1采集
小陆豪客02 2015-08-09

代码编写

截图
0赞 · 1采集
小陆豪客02 2015-08-09

统计分析

截图
0赞 · 1采集

数据加载中...