plsql基础一

港控/mmm° 2021-10-24 02:32 501阅读 0赞

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.变量类型

<table> 
 <tbody> 
 <tr> 
 <td> 类型 </td> 
 <td> 子类 </td> 
 <td> 说&nbsp;&nbsp;&nbsp;&nbsp; 明 </td> 
 <td> 范&nbsp;&nbsp; 围 </td> 
 <td> ORACLE限制 </td> 
 </tr> 
 <tr> 
 <td> CHAR </td> 
 <td> Character String Rowid Nchar </td> 
 <td> 定长字符串 &nbsp; &nbsp; 民族语言字符集 </td> 
 <td> 0à32767 可选,确省=1 </td> 
 <td> 2000 </td> 
 </tr> 
 <tr> 
 <td> VARCHAR2 </td> 
 <td> Varchar, String NVARCHAR2 </td> 
 <td> 可变字符串 民族语言字符集 </td> 
 <td> 0à32767 4000 </td> 
 <td> 4000 </td> 
 </tr> 
 <tr> 
 <td> BINARY_INTEGER </td> 
 <td> &nbsp; </td> 
 <td> 带符号整数,为整数计算优化性能 </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> NUMBER(p,s) </td> 
 <td> Dec Double precision Integer Int Numeric Real Small int </td> 
 <td> 小数, NUMBER 的子类型 高精度实数 整数, NUMBER 的子类型 整数, NUMBER 的子类型 与NUMBER等价 与NUMBER等价 整数, 比 integer 小 </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> LONG </td> 
 <td> &nbsp; </td> 
 <td> 变长字符串 </td> 
 <td> 0-&gt;2147483647 </td> 
 <td> 32,767字节 </td> 
 </tr> 
 <tr> 
 <td> DATE </td> 
 <td> &nbsp; </td> 
 <td> 日期型 </td> 
 <td> 公元前4712年1月1日至公元后4712年12月31日 </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> BOOLEAN </td> 
 <td> &nbsp; </td> 
 <td> 布尔型 </td> 
 <td> TRUE, FALSE,NULL </td> 
 <td> 不使用 </td> 
 </tr> 
 <tr> 
 <td> ROWID </td> 
 <td> &nbsp; </td> 
 <td> 存放数据库行号 </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> UROWID </td> 
 <td> &nbsp; </td> 
 <td> 通用行标识符，字符类型 </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

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循环：不需要声名循环变量，不需要去控制循环条件和循环控制语句，遍历一个有限集合

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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；

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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表中的标准，给员工加薪，1：5%，2：4%，3：3%，4：2%，5：1%，并打印输出每个人，加薪前后的工资。
 -----------------------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;

游标练习

转载于:https://www.cnblogs.com/zsf-note/p/11090984.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211023/68506f4ef3ed4aa29ab2ba277e9c1f17.png