plsql基础一

时间:2024-02-26 07:28:12

1.什么是pl/sql(Procedure Language & Structured Query Language):

pl/sql是一种高级数据库程序设计语言,能够在各种环境下对oracle 进行访问,能够高效的处理数据。

2.pl/sql结构

declare

声名部分

begin

执行部分

  exception

  异常处理部分

end;

例1:输入一个雇员号,得出该雇员号的名字,工作,薪水
declare
v_emp emp%rowtype;--声名变量接收值 begin --根据输入条件,将emp表中符合条件所有列的数据into到v_emp表中 select * into v_emp from emp where empno = &输入雇员号; --输出,需要||拼接 dbms_output.put_line(v_emp.empno||\',\'||v_emp.ename||\',\'||v_emp.job||\',\'||v_emp.sal); end;

 

3.变量类型

类型

子类

说     明

范   围

ORACLE限制

CHAR

Character

String

Rowid

Nchar

定长字符串

 

 

民族语言字符集

0à32767

可选,确省=1

2000

VARCHAR2

Varchar, String

NVARCHAR2

可变字符串

民族语言字符集

0à32767

4000

4000

BINARY_INTEGER

 

带符号整数,为整数计算优化性能

 

 

NUMBER(p,s)

Dec

Double precision

Integer

Int

Numeric

Real

Small int

小数, NUMBER 的子类型

高精度实数

整数, NUMBER 的子类型

整数, NUMBER 的子类型

与NUMBER等价

与NUMBER等价

整数, 比 integer 小

 

 

LONG

 

变长字符串

0->2147483647

32,767字节

DATE

 

日期型

公元前4712年1月1日至公元后4712年12月31日

 

BOOLEAN

 

布尔型

TRUE, FALSE,NULL

不使用

ROWID

 

存放数据库行号

 

 

UROWID

 

通用行标识符,字符类型

 

 

 

 

 

 

 

3.1直接定义类型

v_job varchar2(20)  --直接定义一个变长字符串类型,跟sql类型全部通用,上表列出了一些

3.2 type类型

简单来说就是利用一个现成的表的一个列的类型

v_sal emp.sal%type   --v_sal用的就是emp表的sal列一摸一样的类型

3.3 rowtype 类型

为了方便,将一个表的所有列的类型全部用于现在定义的表中 ,就可以用这个

v_emp emp%rowtype         --上面也用过

3.4记录类型record

格式:

type 类型名 is record (

  变量  数据类型,

  ...

);

变量名  类型名;--变量名是声名的  类型名就是上面的记录类型

DECLARE
  type rec is record(
   empno emp.empno%TYPE,
   ename emp.ename%TYPE,
   job varchar2(20)
);
   v rec;
BEGIN
  SELECT empno,ename,job INTO v FROM emp WHERE empno = &EMPNO;
  dbms_output.put_line(v.empno||\',\'||v.ename||\',\'||v.job);
END;

4.变量赋值

变量名 := 值    或     & 键盘输入的值  

例2:

v_ename  emp.ename%type := \'SMITH\';

v_empno  emp.empno%type := &雇员号;  (如果输入是字符串的话 需要改加 引号!)

5.begin执行块操作

begin内可以进行sql的增、删、改、查的基本操作

declare
v_emp emp%rowtype;
begin
    --将emp值传入v_emp
    select * into v_emp from emp where empno = &empno;
    --更新sal值,给该员工涨薪10%
    update  emp  set sal = v_emp.sal*1.1 where empno = v_emp.empno;
    --插入一个行,当然这个插入操作跟输入的empno就没啥关系啦
    insert into emp (empno,sal) values(8888,2222);
    delete from emp where empno = v_emp.empno;
    dbms_output.put_line(v_emp.job||\',\'||v_emp.sal);
end;

5.1 execute immediate

declare
   --声名一个变量保存sql语句
   v_sql varchar2(255);
   
   --声名一个rowtype类型变量保存员工信息
   v emp%rowtype;
begin
   v_sql:=\'select * from emp where empno=:1\';
   --打印sql语句
   dbms_output.put_line(v_sql);
   --使用execute immediate 执行select语句
   execute immediate v_sql into v using 7369;
   --打印员工信息
   dbms_output.put_line(v.empno||\',\'||v.ename||\',\'||v.job||\',\'||v.sal||\',\'||v.deptno);
end;

plsql中直接写sql和使用execute immediate的区别:直接写sql时,表名不能使用变量的值,
                       execute immediate:可以使用变量里保存的表名
                          如果数据库中没有表,那么写sql语句在创建存储过程或者函数时,会直接报错
                          execute immediate:中可以执行的sql语句中的表,数据库中是可以没有

6.流程控制语句

每种语言没有流程控制就等于没有灵魂,就算不上是个语言!

6.1 条件语句

1.if语句
条件判断语句
语法:
if 条件表达式 then
plsql代码;
end if;

条件表达式的写法和在sql部分的条件表达式相同
语义:表示当前条件表达式成立进,执行then 和end if之间的语句

declare
--声名一个变量
v_n number(8):=&n;
begin
if v_n >10 then
dbms_output.put_line(v_n);
end if;
end;

 

2.if else语句
语法:
if 条件表达式 then
plsql语句1;
else
plsql语句2;
end if;
语义:表示条件表达式成立时,执行第1个plsql语句,如果条件不成立执行第2个plsql语句

3.if elsif语句
语法:
if 条件表达式1 then
plsql语句;
elsif 条件表达式2 then
plsql语句;
...
else
plsql语句;
end if;
语义:表示当某个条件表达式成立时,执行相应then后面的plsql语句,如果所有条件不成立会执行else后面plsql语句,其中else部分可以省略;

declare
--声名一个变量用来保存从键盘输出的一个成绩
score number(4,1):=&成绩;
begin
--当成绩小于60时输出不及格,成绩在60~70之间时输出及格,成绩在70~80之间输出良好,成绩在80~90之间输出优秀,成绩>90时输出非常好
if score<60 then
dbms_output.put_line(\'不及格\');
elsif score<70 then
dbms_output.put_line(\'及格\');
elsif score<80 then
dbms_output.put_line(\'良好\');
elsif score<90 then
dbms_output.put_line(\'优秀\');
else
dbms_output.put_line(\'非常好\');
end if; 
end;

4.case when语句
语法1:
case
when 条件表达式 then
值;
when 条件表达式 then
值;
...
when 条件表达式 then
值;
else
默认值;
end [case];

--该方式跟if一个样
declare
--声名一个变量用来保存从键盘输出的一个成绩
score number(4,1):=&成绩;
begin
--当成绩小于60时输出不及格,成绩在60~70之间时输出及格,成绩在70~80之间输出良好,成绩在80~90之间输出优秀,成绩>90时输出非常好
case
when score<60 then
dbms_output.put_line(\'不及格\');
when score<70 then
dbms_output.put_line(\'及格\');
when score<80 then
dbms_output.put_line(\'良好\');
when score<90 then
dbms_output.put_line(\'优秀\');
else
dbms_output.put_line(\'非常好\');
end case; 
end;

语法2:
case 表达式
when 值1 then
plsql代码;
when 值2 then
plsql代码;
...
else
plsql代码;
end case;

--该种方式,表达式需要得出确切的结果才可以用,这里瞎举个无意义的例子
declare
   v_empno emp.empno%type :=&empno; 
   v_ename emp.ename%type;
begin
    select empno,ename into v_empno,v_ename from emp where empno = v_empno;
    case v_empno
       when 7369 then
       dbms_output.put_line(v_ename);
       when 7499 then
       dbms_output.put_line(v_ename);
       when 7521 then
       dbms_output.put_line(v_ename);
       when 7566 then
       dbms_output.put_line(v_ename);
       when 7788 then
       dbms_output.put_line(v_ename);
  end case;
end;

5.loop循环
语法:
loop
循环体语句;
exit when 退出循环条件;
循环控制语句;
end loop;

--循环打印1~9
declare
--声名一个变量,作为循环变量
n number(2);
begin
--给循环变量赋值初始值
n:=1;
loop
--循环体语句
dbms_output.put_line(n);
--退出循环条件
exit when n=9;
--循环控制语句
n:=n+1;
end loop;
end;
--循环9-1
declare
--声名一个变量,作为循环变量 n number(2); begin --给循环变量赋值初始值 n:=9; loop --循环体语句 dbms_output.put_line(n); --退出循环条件 exit when n=1; --循环控制语句 n:=n-1; end loop; end;

6.while循环
语法:
while 循环条件 loop
循环体语句;
循环控制语句;
end loop;

--打印1~9
declare
--声名一个变量
n number(3);
begin
--给循环变量赋初始值
n:=1;
while n<10 loop
--循环体语句
dbms_output.put_line(n);
--循环控制语句
n:=n+1;
end loop;
end;

while循环:先判断循环条件,如果循环条件不成立就不会执行循环体,如果条件成立执行循环体和循环控制语句

7.for循环
语法1:
for 循环变量 in [reverse] 集合(连续数字集合|查询语句|游标变量) loop
循环体语句;
end loop;

循环变量:是不需要在declare中声名的,除了遍历数字集合(循环变量就是number类型)外,其它情况下循环变量是一个记录类型变量
数字集合的表示: 最小值..最大值

--打印1~9
begin
for i in 1..9 loop
dbms_output.put_line(i);
end loop;
end;
--打印9~1
begin
for i in reverse 1..9 loop
dbms_output.put_line(i);
end loop;
end;
--打印10号门下所有员工的详细信息
begin
for v in (select * from emp where deptno=&dno) loop
dbms_output.put_line(v.ename||\',\'||v.job||\',\'||v.sal||\',\'||v.deptno);
end loop;
end;

for循环:它用来遍历一个有限的集合(数字集合,select结果集,游标等),它不需要控制循环变量和退出循环条件,只需要写循环体就可以。


loop循环:退出循环条件,先执行循环体后判断退出条件
while循环:循环条件,先判断循环条件后执行循环体
for循环:不需要声名循环变量,不需要去控制循环条件和循环控制语句,遍历一个有限集合

1.编写一个程序块,从emp表中显示名为“SMITH”的雇员的薪水和职位
DECLARE
  v_emp emp%ROWTYPE;
BEGIN
  SELECT * INTO v_emp FROM emp WHERE lower(ename) = \'smith\';
  dbms_output.put_line(v_emp.sal||\',\'||v_emp.job);
END;

2.编写一个程序块,接受用户输入一个部门号,从dept表中显示该部门的名称与所在位置
DECLARE
  vdeptno dept.deptno%TYPE := &部门号;
  vloc dept.loc%TYPE;
  vdname dept.dname%TYPE;
BEGIN
  SELECT  loc,dname INTO vloc,vdname FROM dept WHERE deptno = vdeptno;
  dbms_output.put_line(vdname||\',\'||vloc);
END;
  

3.编写一个程序块,利用%type属性,接受一个雇员号,从emp表中显示该雇员的整体薪水(即,薪水加佣金)
DECLARE
  vempno emp.empno%TYPE := &雇员号;
  vsal emp.sal%TYPE;
BEGIN
  SELECT sal+NVL(comm,0) INTO vsal FROM emp WHERE empno = vempno;
  dbms_output.put_line(vsal);
END;

4.编写一个程序块,利用%rowtype属性,接受一个雇员号,从emp表中显示该雇员的整体薪水
DECLARE
  vemp emp%ROWTYPE;
  wole emp.sal%TYPE;
BEGIN
  SELECT * INTO vemp FROM emp WHERE empno = &雇员号;
  wole := vemp.sal+nvl(vemp.comm,0);
  dbms_output.put_line(wole);
END;



5.某公司要根据雇员的职位来加薪,公司决定按下列加薪结构处理:
   Designation      Raise
   ------------     --------
   clerk            500
   salseman         1000
   analyst          1500
   otherwise        2000
编写一个程序块,接受一个雇员名,从emp表中实现上述加薪处理
--
DECLARE
  vemp emp%ROWTYPE;
BEGIN
  SELECT * INTO vemp FROM emp WHERE ename=\'&ename\';
  IF lower(vemp.job) = \'clerk\' THEN 
    UPDATE emp SET sal=sal+500 WHERE ename = vemp.ename;
  ELSIF lower(vemp.job)= \'salseman\' THEN 
    UPDATE emp SET sal=sal+1000 WHERE ename = vemp.ename;
  ELSIF lower(vemp.job)= \'analyst\' THEN 
    UPDATE emp SET sal=sal+1500 WHERE ename = vemp.ename; 
  ELSE  
    UPDATE emp SET sal=sal+2000 WHERE ename = vemp.ename ;
  END IF;
END;
----

BEGIN
  FOR i IN (SELECT * FROM emp) LOOP
      IF lower(i.job) = \'clerk\' THEN 
        UPDATE emp SET sal=sal+500 WHERE empno = i.empno;
      ELSIF lower(i.job)= \'salseman\' THEN 
        UPDATE emp SET sal=sal+1000 WHERE empno = i.empno;
      ELSIF lower(i.job)= \'analyst\' THEN 
        UPDATE emp SET sal=sal+1500 WHERE empno = i.empno;
      ELSE  
        UPDATE emp SET sal=sal+2000 WHERE empno = i.empno;
      END IF;
  END LOOP;
END;




6.编写一个程序块,将emp表中雇员名全部显示出来
BEGIN 
  FOR I IN (SELECT * FROM EMP ) LOOP
    DBMS_OUTPUT.PUT_LINE(I.ENAME);
    END LOOP;
END;
    

7.编写一个程序块,将emp表中前5人的名字显示出来
---1
BEGIN
  FOR I IN (SELECT * FROM emp WHERE ROWNUM <=5) LOOP
    dbms_output.put_line(I.ename);
    END LOOP;
END;
----2
DECLARE
  N NUMBER(2):=0;
BEGIN
  FOR I IN (SELECT * FROM emp) LOOP

    dbms_output.put_line(I.ename);
    N:=N+1;
    IF N =5 THEN 
      RETURN;
    END IF;
  END LOOP;
END;



8.接受两个数相除并且显示结果,如果第二个数为0,则显示消息“除数不能为0”
DECLARE
  A FLOAT(20):=&输入被除数;
  B FLOAT(20):=&输入除数;
  C FLOAT(50);
BEGIN
  IF B =0 THEN 
    dbms_output.put_line(\'除数不能为0\');
    RETURN;
  ELSE
    C:=A/B;
    DBMS_OUTPUT.PUT_LINE(C);
  END IF;
END;
  


9、计算下面级数当末项小于0.001时的部分和。 
1/(1*2)+1/(2*3)+1/(3*4)++1/(n*(n+1))+ ……
DECLARE 
  n NUMBER(20):=1;
  poi FLOAT(20):=0;
BEGIN
  LOOP
    poi := poi+1/(n*(n+1));
    EXIT WHEN 1/(n*(n+1))<0.001;
    n:=n+1;
  END LOOP;
  dbms_output.put_line(poi);
END;

10、计算s=1*2+2*3++N*(N+1),当N=50的值。
DECLARE
  s NUMBER :=0;
  n NUMBER :=1;
BEGIN
  LOOP
    s := s+N*(n+1);
    EXIT WHEN n = 50;
    n:=n+1;
  END LOOP;
  dbms_output.put_line(s);
END;
  

11.编写一个PL/SQL程序块,从emp表中对名字以“A”或"S"开始的所有雇员按他们基本薪水的10%给他们加薪
DECLARE
  vemp emp%ROWTYPE;
BEGIN
  FOR i IN (SELECT * INTO vemp FROM emp WHERE ename LIKE \'A%\' OR ename LIKE \'S%\') LOOP
      UPDATE emp SET sal = sal+sal*0.01;
  END LOOP;
END;


12、两重循环,计算S=1!+2!++10!。
--------for
DECLARE
  s NUMBER :=0;
  n NUMBER :=1;
BEGIN
  FOR i IN 1..10 LOOP
    n:=1;---初始值需要更改为1
    FOR j IN 1..i LOOP
      n:=n*j;
    END LOOP;
    s:=s+n;
  END LOOP;
  dbms_output.put_line(s);
END;

-------loop
DECLARE
  s NUMBER:=0;
  n NUMBER:=1;
  x NUMBER:=1;
BEGIN
  LOOP 
    x:=x*n;
    s:=s+x;
  EXIT WHEN n=10;
    n:=n+1;
  END LOOP;
  dbms_output.put_line(s);
END;

--------while
DECLARE
  s NUMBER:=0;
  n NUMBER:=1;
  x NUMBER:=1;
BEGIN
  WHILE n<=10 LOOP
    x:=x*n;
    s:=s+x;
    n:=n+1;
  END LOOP;
  dbms_output.put_line(s);
END;


    

13.编程序求满足不等式 1+3^2+5^2++N^2>2000的最小N值。
DECLARE
  n NUMBER:=1;
  s NUMBER:=1;
  m NUMBER:=1;
BEGIN
  LOOP 
    s := s+n*n;
    EXIT WHEN s>2000;
    n:=n+2;
    m:=m+1;
  END LOOP;
  dbms_output.put_line(m);
END;
  

14.将雇员表中的所有工资小于3000增加400,统计出增加工资的人数及增加的工资数量。
DECLARE
  vemp emp%ROWTYPE;
  m NUMBER:=0;
  total NUMBER :=0;
BEGIN 
  FOR i IN (SELECT * INTO vemp FROM emp WHERE sal<3000) LOOP
    i.sal := i.sal+400;
    UPDATE emp SET sal = i.sal WHERE ename = i.ename;
    dbms_output.put_line(i.ename||\',\'||i.sal);
    m:=m+1;
    total:=total+400; 
  END LOOP;
  dbms_output.put_line(\'增加工资总人数为:\'||m);
  dbms_output.put_line(\'增加总工资为:\'||total);
END;
------------------
DECLARE
  n NUMBER(10):=0;
BEGIN
  FOR i IN (SELECT * FROM emp WHERE sal<3000) LOOP
    UPDATE emp SET sal = sal+400 WHERE empno = i.empno;
    n:=n+1;
  END LOOP;
  dbms_output.put_line(\'人数:\'||n||\',总工资\'||n*400);
END;

15.从雇员表中显示工资最高的前五个人的姓名,部门和工资。
DECLARE
  vemp emp%ROWTYPE;
BEGIN
  FOR i IN (SELECT * INTO vemp FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE ROWNUM<=5) LOOP
    dbms_output.put_line(i.ename||\',\'||i.deptno||\',\'||i.sal);
  END LOOP;
END;

1-键盘接入两个值,打印比较大的值
DECLARE
  a NUMBER:=&a;
  b NUMBER :=&b;
BEGIN
  IF a > b THEN 
    dbms_output.put_line(a);
  ELSE
    dbms_output.put_line(b);
  END IF ;
END ;

2-键盘介入三个值,并按照从大到小依次打印
DECLARE
  a NUMBER :=&a;
  b NUMBER :=&b;
  c NUMBER :=&c; 
BEGIN
  IF a>b AND b>c THEN    
      dbms_output.put_line(a||\'>\'||b||\'>\'||c);
  ELSIF a>c AND c>b THEN
      dbms_output.put_line(a||\'>\'||c||\'>\'||b);
  ELSIF b>a AND a>c THEN
      dbms_output.put_line(b||\'>\'||a||\'>\'||c);
  ELSIF b>c AND c>a THEN
      dbms_output.put_line(b||\'>\'||c||\'>\'||a);
  ELSIF c>a AND a>b THEN
      dbms_output.put_line(c||\'>\'||a||\'>\'||b);
  ELSIF c>b AND b>a THEN
      dbms_output.put_line(c||\'>\'||b||\'>\'||a);
  END IF;
END ;

4-判断一个年份是不是闰年
DECLARE
    YEAR NUMBER(4):=&年份;
BEGIN
    IF mod(YEAR,4)=0 THEN 
      IF MOD(YEAR ,100) !=0 THEN
         dbms_output.put_line(YEAR||\'是闰年\');
      ELSIF MOD(YEAR,400)=0 THEN
         dbms_output.put_line(YEAR||\'是闰年\');
      ELSE
         dbms_output.put_line(YEAR||\'是平年\');
      END IF;
    ELSE 
      dbms_output.put_line(YEAR||\'是平年\');
    END IF;
END;
-------------------------
DECLARE
    YEAR NUMBER(4) :=&年份;
BEGIN
  IF MOD(YEAR,4)=0 AND MOD(YEAR,100)!=0 OR MOD(YEAR,100)=0 AND MOD(YEAR,400)=0 THEN
    dbms_output.put_line(YEAR||\'是闰年\');
  ELSE
    dbms_output.put_line(YEAR||\'是平年\');
  END IF;
END;

5-体质指数(BMI)=体重(kg)÷身高^2(m)
偏瘦    <= 18.4
正常    18.5 ~ 23.9
过重    24.0 ~ 27.9
肥胖    >= 28.0
现要求输入体重和身高,求出体质指数所在范围
DECLARE
    hight NUMBER:=&身高米;
    weight NUMBER:=&体重千克;
    bmi NUMBER;
    
BEGIN
    bmi:=weight/(hight*hight);
    dbms_output.put_line(bmi);
    IF bmi<=18.4 THEN
      dbms_output.put_line(\'偏瘦\');
    ELSIF bmi<=23.9 THEN
      dbms_output.put_line(\'正常\');
    ELSIF bmi<=27.9 THEN
      dbms_output.put_line(\'过重\');
    ELSE
      dbms_output.put_line(\'肥胖\');
    END IF;
END;


6-输入一个数,判断是奇数还是偶数
DECLARE
    a NUMBER:=&整数;
BEGIN
    IF MOD(a,2) = 0 THEN
      dbms_output.put_line(a||\'是偶数\');
    else
      dbms_output.put_line(a||\'是奇数\');
    END IF;
END;

7-只含有一个未知数(一元),并且未知数项的最高次数是2(二次)的整式
方程叫做一元二次方程。标准形式为:ax2+bx+c=0(a≠0)。
现输入a b c三个值,求一元二次方程的解
注:平方根函数为 SQRT
DECLARE
   a NUMBER(5,1):=&a;
   b NUMBER(5,1):=&b;
   c NUMBER(5,1):=&c;
   x1 NUMBER(10,3);
   x2 NUMBER(10,3);
BEGIN
   x1:=(-b+SQRT(b*b-4*a*c))/(2*a);
   x2:=(-b-SQRT(b*b-4*a*c))/(2*a);
   IF x1=x2 THEN 
     dbms_output.put_line(\'x1=\'||x1);
   ELSE
     dbms_output.put_line(\'x1=\'||x1||\',\'||\'x2=\'||x2);
   END IF;
END;

8-99乘法表
----1..9
DECLARE
  n NUMBER:=1;
  s NUMBER:=0;
  x NUMBER;
BEGIN
  FOR i IN 1..9 LOOP
    n:=i;
    FOR j IN 1..i LOOP
        x:=n*j;
        s:=x;
        dbms_output.put(j||\'*\'||n||\'=\'||s||CHR(9));
    END LOOP;
    dbms_output.new_line();
  END LOOP; 
END;
---9..1
DECLARE
  n NUMBER:=1;
  s NUMBER:=0;
  x NUMBER;

BEGIN
  FOR i IN REVERSE 1..9 LOOP
    n:=i;
    FOR j IN i..9 LOOP
        x:=n*j;
        s:=x;
        dbms_output.put(j||\'*\'||n||\'=\'||s||\' \');
    END LOOP;
    dbms_output.new_line();
  END LOOP; 
END;
-------
DECLARE
  n NUMBER:=1;
  s NUMBER:=0;
  x NUMBER;

BEGIN
  FOR i IN REVERSE 1..9 LOOP
    n:=i;
    FOR j IN 1..i LOOP
        x:=n*j;
        s:=x;
        dbms_output.put(j||\'*\'||n||\'=\'||s||\' \');
    END LOOP;
    dbms_output.new_line();
  END LOOP; 
END;
------------
DECLARE
  n NUMBER:=1;
  s NUMBER:=0;
  x NUMBER;

BEGIN
  FOR i IN REVERSE 1..9 LOOP
    n:=i;
    FOR j IN REVERSE 1..i LOOP
        x:=n*j;
        s:=x;
        dbms_output.put(j||\'*\'||n||\'=\'||s||\' \');
    END LOOP;
    dbms_output.new_line();
  END LOOP; 
END;
控制语句练习

7.游标

游标比较简单,主要就是定义cursor 然后在begin中open 游标,用fetch into 传个值,再用控制语句做一些事,最后close一下就好了

declare

  cursor 游标名 is select 语句;

  变量  --声名接收游标值

begin

  open 游标名;

  循环语句

    fetch 游标 into 变量;--这个根据循环语句的不同变换位置

  结束循环;

  close 游标

end;

1、定义游标:列出每个员工的姓名、部门名称并编程显示第10个到第20个记录。
---loop-----------
DECLARE
  --定义cursor
  CURSOR cur IS SELECT ename,dname FROM (SELECT ename,dname,ROWNUM r FROM (SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno AND ROWNUM<=20)) WHERE r>=10;
  --定义接收变量
  v_ename emp.ename%TYPE;
  v_dname dept.dname%TYPE;
BEGIN
  --open 游标
  OPEN cur;
  --循环控制
  LOOP
    --导入变量
    FETCH cur INTO v_ename,v_dname;
    --控制器
    EXIT WHEN cur%NOTFOUND;
    --循环体
    dbms_output.put_line(v_ename||\'-\'||v_dname);
    --结束循环
    END LOOP;
  --close cursor
  CLOSE cur;
END;
------------------while------------------------
DECLARE
  CURSOR cur IS SELECT ename,dname FROM (SELECT ename,dname,ROWNUM r FROM (SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno AND ROWNUM<=20)) WHERE r>=10;
  v_ename emp.ename%TYPE;
  v_dname dept.dname%TYPE;
  
BEGIN 
  OPEN cur;
  FETCH cur INTO v_ename,v_dname;  --让cur%found识别第一条记录
  WHILE cur%FOUND LOOP
    dbms_output.put_line(v_ename||\'-\'||v_dname);
    FETCH cur INTO v_ename,v_dname;---需要放在下面
  END LOOP;
  CLOSE cur;
END;
------------------for -------------------------------
DECLARE
  CURSOR cur IS SELECT ename,dname FROM (SELECT ename,dname,ROWNUM r FROM (SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno AND ROWNUM<=20)) WHERE r>=10;
BEGIN----for循环不用开启游标,直接循环
  FOR i IN cur LOOP
    dbms_output.put_line(i.ename||\'-\'||i.dname);
  END LOOP;
END;
2、定义游标:从雇员表中显示工资大于3000的记录,只要姓名、部门编号和工资。编程显示其中的奇数记录。

--------------loop---------------
DECLARE
  --定义游标
  CURSOR cur IS SELECT ename,deptno,sal FROM emp WHERE sal>3000;
  --定义变量
  v_emp emp%ROWTYPE;
BEGIN
  --打开游标
  OPEN cur;
  ---先传cur%rowcount初始值
  FETCH cur INTO v_emp.ename,v_emp.deptno,v_emp.sal;
  --循环
  LOOP
    FETCH cur INTO v_emp.ename,v_emp.deptno,v_emp.sal;---给变量传值
    IF mod(cur%ROWCOUNT,2)=1 THEN           --判断为奇数时打印
       dbms_output.put_line(v_emp.ename||\',\'||v_emp.deptno||\',\'||v_emp.sal);
    END IF;
    EXIT WHEN cur%NOTFOUND;---找不到时结束
  END LOOP;
  --关闭游标
  CLOSE cur;
END;
-------------while----------------
DECLARE
  CURSOR cur IS SELECT ename,deptno,sal FROM emp WHERE sal>3000;
  v_emp emp%ROWTYPE;
BEGIN
  OPEN cur;
  FETCH cur INTO v_emp.ename,v_emp.deptno,v_emp.sal;---赋予cur%rowcount初始值
  WHILE cur%FOUND LOOP
     FETCH cur INTO v_emp.ename,v_emp.deptno,v_emp.sal;
     IF mod(cur%ROWCOUNT,2)=1 THEN --------判断奇数
        dbms_output.put_line(v_emp.ename||\',\'||v_emp.deptno||\',\'||v_emp.sal);
     END IF;
  END LOOP;
  CLOSE cur;
END;
-------------for--------------
DECLARE
  CURSOR cur IS SELECT ename,deptno,sal FROM emp WHERE sal>3000;
  v NUMBER(2):=1;
BEGIN
  FOR i IN cur LOOP
    IF MOD(v,2)=1 THEN -------判断奇数
       dbms_output.put_line(i.ename||\',\'||i.deptno||\',\'||i.sal);
       v:=v+1;
    END IF;
  END LOOP;
END;

3、用游标显示所有部门编号与名称,以及其所拥有的员工人数。
---------------------loop-----------------
DECLARE
  --定义cursor
  CURSOR cur IS SELECT d.deptno,dname,COUNT(*) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.deptno,dname;
  --定义变量
  v_deptno dept.deptno%TYPE;
  v_dname dept.dname%TYPE;
  v_count NUMBER(10);
BEGIN
  --打开游标
  OPEN cur;
  FETCH cur INTO v_deptno,v_dname,v_count;
  LOOP
    dbms_output.put_line(v_deptno||\',\'||v_dname||\',\'||v_count);
    FETCH cur INTO v_deptno,v_dname,v_count;
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  --关闭游标
  CLOSE cur;
END;
--------------------------------while-----------------------
DECLARE
  --定义cursor
  CURSOR cur IS SELECT d.deptno,dname,COUNT(*) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.deptno,dname;
  --定义变量
  v_deptno dept.deptno%TYPE;
  v_dname dept.dname%TYPE;
  v_count NUMBER(10);
BEGIN
  --打开游标
  OPEN cur;
  FETCH cur INTO v_deptno,v_dname,v_count;
  WHILE cur%FOUND LOOP
    dbms_output.put_line(v_deptno||\',\'||v_dname||\',\'||v_count);
    FETCH cur INTO v_deptno,v_dname,v_count;
  END LOOP;
  --关闭游标
  CLOSE cur;
END;


4、用游标属性%rowcount实现输出前十个员工的信息
--------------loop-----------------
DECLARE
  --定义游标
  CURSOR cur IS SELECT * FROM emp;
  vemp emp%ROWTYPE;
BEGIN
  --打开游标
  OPEN cur;
  --循环
  LOOP 
     --传值
    FETCH cur INTO vemp;
    IF cur%ROWCOUNT<=10 THEN ---判断前十个并输出
      dbms_output.put_line(vemp.ename||\',\'||vemp.empno||\',\'||vemp.deptno||\',\'||vemp.sal||\',\'||vemp.comm);
    END IF;
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  --关闭游标
  CLOSE cur;
END;
-------------------------------while--------------------------
DECLARE
  --定义游标
  CURSOR cur IS SELECT * FROM emp;
  vemp emp%ROWTYPE;
BEGIN
  --打开游标
  OPEN cur;
  --第一次传值保证while 后判断条件有值
  FETCH cur INTO vemp;
  --循环
  WHILE cur%FOUND loop
    IF cur%ROWCOUNT<=10 THEN ---判断前十个并输出
      dbms_output.put_line(vemp.ename||\',\'||vemp.empno||\',\'||vemp.deptno||\',\'||vemp.sal||\',\'||vemp.comm);
    END IF;
    FETCH cur INTO vemp; -----while循环下必须要将传值放在后面,让第一次的游标输出
  END LOOP;
  --关闭游标
  CLOSE cur;
END;
--------------------------for--------------------------
DECLARE
  CURSOR cur IS SELECT ename,empno,sal FROM emp;
BEGIN
  OPEN cur;
  fe
  FOR i IN cur LOOP
    dbms_output.put_line(i.ename||\',\'||i.empno||\',\'||i.sal);
  END LOOP;
END;

5、通过使用游标来显示dept表中的部门名称,及其相应的员工列表(提示:可以使用双重循环)。
DECLARE
  CURSOR cur IS SELECT dname,e.ename FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno;
BEGIN
  FOR i IN cur LOOP
    dbms_output.put_line(i.dname||\',\'||i.ename);
  END LOOP;
END;
6、接受一个部门号,使用For循环,从emp表中显示该部门的所有雇员的姓名,工作和薪水。
----------------------for---------------------------
DECLARE
  CURSOR cur IS SELECT ename,job,sal FROM emp WHERE deptno = &部门号;
BEGIN
  FOR i IN cur LOOP
    dbms_output.put_line(i.ename||\',\'||i.job||\',\'||i.sal);
  END LOOP;
END;
-----------------------loop-----------------------
DECLARE
  CURSOR cur IS SELECT ename,job,sal FROM emp WHERE deptno = &部门号;
  vemp emp%ROWTYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO vemp.ename,vemp.job,vemp.sal;
    EXIT WHEN cur%NOTFOUND;
    dbms_output.put_line(vemp.ename||\',\'||vemp.job||\',\'||vemp.sal);
  END LOOP;
  CLOSE cur;
END;
-----------------------while--------------------------
DECLARE
  CURSOR cur IS SELECT ename,job,sal FROM emp WHERE deptno = &部门号;
  vemp emp%ROWTYPE;
BEGIN
  OPEN cur;
  FETCH cur INTO vemp.ename,vemp.job,vemp.sal;
  WHILE cur%FOUND LOOP
    dbms_output.put_line(vemp.ename||\',\'||vemp.job||\',\'||vemp.sal);
    FETCH cur INTO vemp.ename,vemp.job,vemp.sal;
  END LOOP;
  CLOSE cur;
END;
7、编写一个程序块,将emp表中前5人的名字,及其出的工资等级(salgrade)显示出来。
----------------loop----------------
DECLARE
  CURSOR cur IS SELECT ename,grade FROM emp e ,salgrade s WHERE sal BETWEEN losal AND hisal AND ROWNUM <=5;
  vname emp.ename%TYPE;
  vgrade salgrade.grade%TYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO vname,vgrade;
    EXIT WHEN cur%NOTFOUND;
    dbms_output.put_line(vname||\',\'||vgrade);
  END LOOP;
  CLOSE cur;
END;
----------------while----------------
DECLARE
  CURSOR cur IS SELECT ename,grade FROM emp e ,salgrade s WHERE sal BETWEEN losal AND hisal AND ROWNUM <=5;
  vname emp.ename%TYPE;
  vgrade salgrade.grade%TYPE;
BEGIN
  OPEN cur;
  FETCH cur INTO vname,vgrade;
  WHILE cur%FOUND LOOP
    dbms_output.put_line(vname||\',\'||vgrade);
    FETCH cur INTO vname,vgrade;
  END LOOP;
  CLOSE cur;
END;
-----------------for————————————————
DECLARE
  CURSOR cur IS SELECT ename,grade FROM emp e ,salgrade s WHERE sal BETWEEN losal AND hisal AND ROWNUM <=5;
BEGIN
  FOR i IN cur LOOP
    dbms_output.put_line(i.ename||\',\'||i.grade);
  END LOOP;
END;
-------------loop2 ---------------其他同样可以用此种方法,不多写了
DECLARE
  CURSOR cur IS SELECT ename,grade FROM emp e ,salgrade s WHERE sal BETWEEN losal AND hisal;
  vname emp.ename%TYPE;
  vgrade salgrade.grade%TYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO vname,vgrade;
    EXIT WHEN cur%NOTFOUND;
    IF cur%ROWCOUNT<=5 THEN ----------------其他同样可以用此种方法
       dbms_output.put_line(vname||\',\'||vgrade);
    END IF;
  END LOOP;
  CLOSE cur;
END;
8.emp表中对所有雇员按他们基本薪水的10%给他们加薪,如果所增加后的薪水大于5000,则取消加薪。
---------------------loop-------------------
DECLARE 
  CURSOR cur IS SELECT empno,ename,sal FROM emp;
  vemp emp%ROWTYPE;
BEGIN
  OPEN cur;
  LOOP 
    FETCH cur INTO vemp.empno,vemp.ename,vemp.sal;
    EXIT WHEN cur%NOTFOUND;
    --判断加10%之后是否超过5000,如果超过5000,则把元值赋给sal,否则加上基础薪资的10%
    IF (vemp.sal+vemp.sal*0.1)>5000 THEN 
      UPDATE emp SET sal = vemp.sal WHERE empno = vemp.empno;
    ELSE 
      UPDATE emp SET sal = vemp.sal+vemp.sal*0.1 WHERE empno = vemp.empno;
    END IF;
  END LOOP;
  CLOSE cur;
END;
---------------------------------while------------------------
DECLARE 
  CURSOR cur IS SELECT empno,ename,sal FROM emp;
  vemp emp%ROWTYPE;
BEGIN
  OPEN cur;
  FETCH cur INTO vemp.empno,vemp.ename,vemp.sal;
  WHILE cur%FOUND LOOP
    --判断加10%之后是否超过5000,如果超过5000,则把元值赋给sal,否则加上基础薪资的10%
    IF (vemp.sal+vemp.sal*0.1)>5000 THEN 
      UPDATE emp SET sal = vemp.sal WHERE empno = vemp.empno;
    ELSE 
      UPDATE emp SET sal = vemp.sal+vemp.sal*0.1 WHERE empno = vemp.empno;
    END IF;
    FETCH cur INTO vemp.empno,vemp.ename,vemp.sal;
  END LOOP;
  CLOSE cur;
END;
-----------------------for --------------------------------
DECLARE 
  CURSOR cur IS SELECT empno,ename,sal FROM emp;
  vemp emp%ROWTYPE;
BEGIN
 FOR i IN cur LOOP
    --判断加10%之后是否超过5000,如果超过5000,则把元值赋给sal,否则加上基础薪资的10%
    IF (i.sal+i.sal*0.1)>5000 THEN 
      UPDATE emp SET sal = i.sal WHERE empno = i.empno;
    ELSE 
      UPDATE emp SET sal = i.sal+i.sal*0.1 WHERE empno = i.empno;
    END IF;
  END LOOP;
END;

9.按照salgrade表中的标准,给员工加薪,15%24%33%42%51%,并打印输出每个人,加薪前后的工资。
-----------------------loop--------------------其他都一样 不多写了
DECLARE
  CURSOR cur IS SELECT empno,grade,sal FROM emp e,salgrade s WHERE e.sal BETWEEN losal AND hisal;
  vemp emp%ROWTYPE;
  vsal salgrade%ROWTYPE;
  newsal emp.sal%TYPE; --声明一个newsal能接收新的sal值
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO vemp.empno,vsal.grade,vemp.sal;
    EXIT WHEN cur%NOTFOUND;
    IF vsal.grade = 1 THEN -----判读每种等级改增加多少
      newsal:=vemp.sal+vemp.sal*0.05;---------------通过newsal来提供输出的新sal
    ELSIF vsal.grade = 2 THEN 
      newsal:=vemp.sal+vemp.sal*0.04;
    ELSIF vsal.grade = 3 THEN 
      newsal:=vemp.sal+vemp.sal*0.03;
    ELSIF vsal.grade = 4 THEN 
      newsal:=vemp.sal+vemp.sal*0.02;
    ELSIF vsal.grade = 5 THEN  
      newsal:=vemp.sal+vemp.sal*0.01;
    END IF;
    UPDATE emp SET sal = newsal WHERE empno = vemp.empno;
    dbms_output.put_line(vemp.sal||\',\'||newsal);
  END LOOP;
  CLOSE cur;
END;

10、用游标获取所有收入超过2000的 salesman.
-------------------------loop---------------1
DECLARE
  CURSOR cur IS SELECT ename,job,sal FROM emp WHERE job = \'SALESMAN\' AND sal+NVL(comm,0)>2000;
  vemp emp%ROWTYPE;
BEGIN
  OPEN cur;
  LOOP 
    FETCH cur INTO vemp.ename,vemp.job,vemp.sal;
    EXIT WHEN cur%NOTFOUND;
    dbms_output.put_line(vemp.ename||\',\'||vemp.job||\',\'||vemp.sal);
  END LOOP;
  CLOSE cur;
END;
-----------------------------while -----------------2
DECLARE
  CURSOR cur IS SELECT ename,job,sal,comm FROM emp WHERE job = \'SALESMAN\';
  vemp emp%ROWTYPE;
BEGIN
  OPEN cur;
  FETCH cur INTO vemp.ename,vemp.job,vemp.sal,vemp.comm;
  WHILE cur%FOUND LOOP
    IF vemp.sal+NVL(vemp.comm,0)>2000  THEN
       dbms_output.put_line(vemp.ename||\',\'||vemp.job||\',\'||vemp.sal);
    END IF;
    FETCH cur INTO vemp.ename,vemp.job,vemp.sal;
  END LOOP;
  CLOSE cur;
END;
游标练习