oracle PL-SQL学习案例（一）

最新推荐文章于 2023-02-16 16:09:31 发布

sytWeibo

最新推荐文章于 2023-02-16 16:09:31 发布

阅读量2.3k

点赞数 1

分类专栏： Oracle 文章标签： oracle null table manager integer insert

本文链接：https://blog.csdn.net/sytweibo/article/details/5534516

版权

Oracle 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

【示例1.1】查询雇员编号为7788的雇员姓名和工资。
  步骤1：用SCOTT/TIGER账户登录SQL*Plus。
  步骤2：在输入区输入以下程序：
   /*这是一个简单的示例程序*/
  SET SERVEROUTPUT ON
  DECLARE--定义部分标识
    v_name VARCHAR2(10); --定义字符串变量v_name
    v_sal   NUMBER(5); --定义数值变量v_sal
  BEGIN   --可执行部分标识
  SELECT ename,sal
    INTO v_name,v_sal
  FROM emp
    WHERE empno=7788;
  --在程序中插入的SQL语句
  DBMS_OUTPUT.PUT_LINE('7788号雇员是：'||v_name||'，工资为：'||to_char(v_sal));
  --输出雇员名和工资
  END;        --结束标识

  步骤3：按执行按钮或F5快捷键执行程序。

  输出的结果是：
  7788号雇员是：SCOTT，工资为：3000
  PL/SQL 过程已成功完成。

======================================================================================================================

【示例2.1】变量的定义和初始化。
  输入和运行以下程序：
  SET SERVEROUTPUT ON
  DECLARE  --声明部分标识
  v_job  VARCHAR2(9);
  v_count BINARY_INTEGER DEFAULT 0;
  v_total_sal NUMBER(9,2) := 0;
  v_date  DATE := SYSDATE + 7;
  c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
  v_valid  BOOLEAN NOT NULL := TRUE;
  BEGIN
  v_job:='MANAGER';
  --在程序中赋值
    DBMS_OUTPUT.PUT_LINE(v_job);
  --输出变量v_job的值
    DBMS_OUTPUT.PUT_LINE(v_count);
  --输出变量v_count的值
   DBMS_OUTPUT.PUT_LINE(v_date);
  --输出变量v_date的值
   DBMS_OUTPUT.PUT_LINE(c_tax_rate);
  --输出变量c_tax_rate的值
  END;

  执行结果：
  MANAGER
  0
  18-4月 -03
  8.25
  PL/SQL 过程已成功完成。

说明：示例2共定义了6个变量，分别用“:=”赋值运算符或DEFAULT 关键字对变量进行了初始化或赋值。
其中：c_tax_rate为常量，在数据类型前加了 “CONSTANT”
关键字；v_valid变量在赋值运算符前面加了关键字“NOT NULL”，强制不能为空。
如果变量是布尔型，它的值只能是“TRUE”、“FALSE”或“NULL”。
本练习中的变量v_valid布尔变量的值只能取“TRUE”或“FALSE”。

=============================================================

【训练2.2】根据表的字段定义变量。
  输入并执行以下程序：
  SET SERVEROUTPUT ON
  DECLARE
     v_ename emp.ename%TYPE;--根据字段定义变量
  BEGIN
     SELECT ename
   INTO  v_ename
   FROM  emp
   WHERE empno = 7788;
  DBMS_OUTPUT.PUT_LINE(v_ename);
  --输出变量的值
  END;
  执行结果：
  SCOTT
  PL/SQL 过程已成功完成。
说明：变量v_ename是根据表emp的ename字段定义的，两者的数据类型总是一致的。

=============================================================================

【训练2.3】定义并使用结合变量。
  步骤1：输入和执行下列命令，定义结合变量g_ename：
  VARIABLE g_ename VARCHAR2(100)
  步骤2：输入和执行下列程序：
  SET SERVEROUTPUT ON
  BEGIN
   :g_ename:=:g_ename|| 'Hello~ ';
  --在程序中使用结合变量
   DBMS_OUTPUT.PUT_LINE(:g_ename);
  --输出结合变量的值
  END;
  输出结果：
  Hello~
  PL/SQL 过程已成功完成。
  步骤3：重新执行程序。
  输出结果：
  Hello~ Hello~
  PL/SQL 过程已成功完成。
  步骤4：程序结束后用命令显示结合变量的内容：
  PRINT g_ename
  输出结果：
  G_ENAME
  -----------------------------------------------
  Hello~ Hello~ ?
  说明：g_ename为结合变量，可以在程序中引用或赋值，引用时在结合变量前面要加上“∶”。在程序结束后该变量的值仍然存在，其他程序可以继续引用。
=====================================================================================

  【训练2.4】根据表定义记录变量。
  输入并执行如下程序：
  SET SERVEROUTPUT ON
  DECLARE
  emp_record  emp%ROWTYPE;--定义记录变量
  BEGIN
    SELECT * INTO emp_record
    FROM   emp
    WHERE  mpno = 7788;--取出一条记录
  DBMS_OUTPUT.PUT_LINE(emp_record.ename); --输出记录变量的某个字段
  END;
  执行结果为：
  SCOTT
  PL/SQL 过程已成功完成。?
  说明：在以上的练习中定义了记录变量emp_record，它是根据表emp的全部字段定义的。SELECT语句将编号为7788的雇员的全部字段对应地存入该记录变量，最后输出记录变量的雇员名称字段emp_record.ename的内容。如果要获得其他字段的内容，比如要获得编号为7788的雇员的工资，可以通过变量emp_record.sal获得，依此类推。

=====================================================================================
varray(10)
=====================================================================================
  【训练2.5】定义和使用TABLE变量：
  SET SERVEROUTPUT ON
  DECLARE
   TYPE type_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; --类型说明
   v_t   type_table;     --定义TABLE变量
  BEGIN
   v_t(1):='MONDAY';
   v_t(2):='TUESDAY';
   v_t(3):='WEDNESDAY';
   v_t(4):='THURSDAY';
   v_t(5):='FRIDAY';
  DBMS_OUTPUT.PUT_LINE(v_t(3)); --输出变量的内容
  END;
  执行结果为：
  WEDNESDAY
  PL/SQL 过程已成功完成。
  说明：本例定义了长度为10的字符型TABLE变量，通过赋值语句为前五个元素赋值，最后输出第三个元素。
==========================================================================================
  【训练3.1】如果温度大于30℃，则显示“温度偏高”。
  输入并执行以下程序：
  SET SERVEROUTPUT ON
  DECLARE
    V_temprature  NUMBER(5):=32;
    V_result         BOOLEAN:=false;
  BEGIN
    V_result:= v_temprature >30;
    IF V_result THEN
      DBMS_OUTPUT.PUT_LINE('温度'|| V_temprature ||'度,偏高');
    END IF;
  END;
  执行结果为：
  温度32度，偏高
  PL/SQL过程已成功完成。

说明：该程序中使用了布尔变量，初值为false，表示温度低于30℃。表达式v_temprature >30返回值为布尔型，赋给逻辑变量V_result。如果变量v_temprature的值大于30，则返回值为真，否则为假。V_result值为真就会执行IF到 END IF之间的输出语句，否则没有输出结果。
    试修改温度的初值为25℃，重新执行，观察结果。

==================================================================================
  【训练3.2】根据性别，显示尊称。
  输入并执行以下程序：
  SET SERVEROUTPUT ON
  DECLARE
    v_sex VARCHAR2(2);
  v_titil VARCHAR2(10);
   BEGIN
     v_sex:='男';
     IF v_sex ='男' THEN
       v_titil:='先生';
     ELSE
       v_titil:='女士';
     END IF;
     DBMS_OUTPUT.PUT_LINE(v_titil||'您好！');
   END;
  执行结果为：
  先生您好！
  PL/SQL 过程已成功完成。

说明：该程序根据性别显示尊称和问候，无论性别的值为何，总会有显示结果输出。如果V_sex的值不是‘男’和‘女’，那么输出结果会是什么？
【练习1】对以上程序进行补充修改，在ELSE部分嵌入一个IF结构，如果V_sex的值不是'女'，则显示“朋友你好”。

==================================================================================
  【训练3.3】根据雇员工资分级显示税金。
输入并运行以下程序：
SET SERVEROUTPUT ON
DECLARE
v_sal NUMBER(5);
v_tax NUMBER(5,2);
BEGIN
SELECT sal INTO v_sal
FROM emp
WHERE empno=7788;
  IF v_sal >=3000 THEN
  V_tax:= v_sal*0.08;--税率8%
    ELSIF v_sal>=1500 THEN
    V_tax:= v_sal*0.06; --税率6%
  ELSE
    V_tax:= v_sal*0.04; --税率4%
    END IF;
  DBMS_OUTPUT.PUT_LINE('应缴税金:'||V_tax);
  END;
  执行结果为：
  应缴税金:240
  PL/SQL 过程已成功完成。

说明：该程序根据工资计算7788号雇员应缴税金，不同工资级别的税率不同。
==================================================
  【训练4.1】使用CASE结构实现职务转换。
  输入并执行程序：
SET SERVEROUTPUT ON
DECLARE
v_job VARCHAR2(10);
BEGIN
SELECT job INTO v_job
FROM emp
WHERE empno=7788;
CASE v_job
WHEN 'PRESIDENT' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:总裁');
WHEN 'MANAGER' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:经理');
WHEN 'SALESMAN' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:推销员');
WHEN 'ANALYST' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:系统分析员');
WHEN 'CLERK' THEN
DBMS_OUTPUT.PUT_LINE('雇员职务:职员');
ELSE
DBMS_OUTPUT.PUT_LINE('雇员职务:未知');
END CASE;
END;
  执行结果：
  雇员职务:系统分析员
  PL/SQL 过程已成功完成。

说明：以上实例检索雇员7788的职务，通过CASE结构转换成中文输出。

【练习1】将雇员号修改成其他已知雇员号，重新执行。
=======================================================

【训练4.2】使用CASE的表达式结构。

DECLARE
    v_grade    VARCHAR2(10);
    v_result    VARCHAR2(10);
BEGIN
    v_grade:='B';
    v_result:=CASE v_grade
          WHEN 'A' THEN '优'
          WHEN 'B' THEN '良'
          WHEN 'C' THEN '中'
          WHEN 'D' THEN '差'
        ELSE '未知'
    END;
     DBMS_OUTPUT.PUT_LINE('评价等级:'||V_result);
END;
    执行结果为：
    评价等级:良
    PL/SQL 过程已成功完成。

说明：该CASE表达式通过判断变量v_grade的值，对变量V_result赋予不同的值。


============================================================
  【训练4.3】使用CASE的搜索结构。
DECLARE
   v_sal    NUMBER(5);
BEGIN
   SELECT sal INTO v_sal FROM emp
       WHERE empno=7788;
   CASE
       WHEN v_sal>=3000 THEN
            DBMS_OUTPUT.PUT_LINE('工资等级:高');
       WHEN v_sal>=1500 THEN
            DBMS_OUTPUT.PUT_LINE('工资等级:中');
       ELSE
            DBMS_OUTPUT.PUT_LINE('工资等级:低');
    END CASE;
END;
  执行结果为：
  工资等级：高
  PL/SQL 过程已成功完成。

  说明：此结构类似于IF-THEN-ELSIF-ELSE-END IF结构。本训练判断7788雇员的工资等级。
============================================================
  【训练5.1】　求：1２+3２+5２+...+15２的值。
  输入并执行以下程序：
DECLARE
v_total        NUMBER(5):=0;
v_count        NUMBER(5):=1;
BEGIN
    LOOP
        v_total:=v_total+v_count**2;
        EXIT WHEN v_count=15;--条件退出
        v_count:=v_count+2;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_total);
END;
  输出结果为：
  680
  PL/SQL 过程已成功完成。

说明：基本循环一定要使用EXIT退出，否则就会成为死循环。

  【练习1】求1*2*3*4*...*10的值。
===============================================================
  【训练5.2】用FOR循环输出图形。
BEGIN
    FOR I IN 1..8
        LOOP
        DBMS_OUTPUT.PUT_LINE(to_char(i)||rpad('*',I,'*'));
    END LOOP;
END;
  输出结果为：
1*
2**
3***
4****
5*****
6******
7*******
8********
  PL/SQL 过程已成功完成。

  说明：该程序在循环中使用了循环控制变量I，该变量隐含定义。在每次循环中根据循环控制变量I的值，使用RPAD函数控制显示相应个数的“*”。

  【练习2】为以上程序增加REVERSE关键字，观察执行结果。

==============================================================
  【训练5.2.2】输出一个空心三角形。
BEGIN
    FOR I IN 1..9
        LOOP
        IF I=1 OR I=9 THEN
            DBMS_OUTPUT.PUT_LINE(to_char(I)||rpad(' ',12-I,' ')||rpad('*',2*i-1,'*'));
        ELSE
            DBMS_OUTPUT.PUT_LINE(to_char(I)||rpad(' ',12-I,' ')||'*'||rpad(' ',I*2-3,' ')||'*');
        END IF;
    END LOOP;
END;

  输出结果为：
1           *
2          * *
3         *   *
4        *     *
5       *       *
6      *         *
7     *           *
8    *             *
9   *****************
PL/SQL 过程已成功完成。

【练习3】修改程序，输出一个实心三角形。

  说明：该实例采用循环和IF结构相结合，对第1行和第9行(I=1 OR I=9)执行同样的输出语句，其他行执行另外的输出语句。
=====================================================================
  【训练5.3】　使用WHILE 循环向emp表连续插入5个记录。
  步骤1：执行下面的程序：

DECLARE
v_count NUMBER(2) := 1;
BEGIN
WHILE v_count <6 LOOP
    INSERT INTO emp(empno, ename)
    VALUES (5000+v_count, '临时');
    v_count := v_count + 1;
END LOOP;
COMMIT;
END;
输出结果为：
PL/SQL 过程已成功完成。

-----------------------------------------------------------------
  步骤2：显示插入的记录：
  SELECT empno,ename FROM emp WHERE ename='临时';
  输出结果为：
       EMPNO ENAME
  ------------------ ----------
      5001 临时
      5002 临时
      5003 临时
      5004 临时
      5005 临时
  已选择5行。
----------------------------------------------------------------
  步骤3：删除插入的记录：
  DELETE FROM emp WHERE ename='临时';
  COMMIT;
  输出结果为：
  已删除5行。
  提交完成。
  说明：该练习使用WHILE循环向emp表插入5个新记录(雇员编号根据循环变量生成)，并通过查询语句显示新插入的记录，然后删除。

============================================================================
  【训练5.4】　使用二重循环求1！+2！+...+10！的值。
  步骤1：第1种算法：
DECLARE
v_total    NUMBER(8):=0;
v_ni    NUMBER(8):=0;
J        NUMBER(5);
BEGIN
FOR I IN 1..10
LOOP
    J:=1;
      v_ni:=1;
    WHILE J<=I
    LOOP
      v_ni:= v_ni*J;
      J:=J+1;
    END LOOP;--内循环求n!
        v_total:=v_total+v_ni;
END LOOP;--外循环求总和
DBMS_OUTPUT.PUT_LINE(v_total);
END;

输出结果为：
4037913
PL/SQL 过程已成功完成。

-----------------------------------------------------------------
  步骤2：第2种算法：
SET SERVEROUTPUT ON
DECLARE
v_total  NUMBER(8):=0;
v_ni  NUMBER(8):=1;
BEGIN
FOR I IN 1..10
LOOP
    v_ni:= v_ni*I; --求n!
    v_total:= v_total+v_ni;
END LOOP;  --循环求总和
DBMS_OUTPUT.PUT_LINE(v_total);
END;
  输出结果为：
  409114
  PL/SQL 过程已成功完成。

   说明：第1种算法的程序内循环使用WHILE循环求阶层，外循环使用FOR循环求总和。
         第2种算法是简化的算法，根据是：n!=n*(n?1)!。
=================================================================
    阶段训练
=================================================================
  【训练1】插入雇员，如果雇员已经存在，则输出提示信息。
  SET SERVEROUTPUT ON
  DECLARE
  v_empno NUMBER(5):=7788;
  v_num VARCHAR2(10);
  i NUMBER(3):=0;
  BEGIN
    SELECT count(*) INTO v_num FROM SCOTT.emp WHERE empno=v_empno;
=================================================================
IF v_num=1 THEN
DBMS_OUTPUT.PUT_LINE('雇员'||v_empno||'已经存在！');
ELSE
INSERT INTO emp(empno,ename) VALUES(v_empno,'TOM');
COMMIT;
DBMS_OUTPUT.PUT_LINE('成功插入新雇员！');
END IF;
END;
=================================================================
  说明：在本程序中，使用了一个技巧来判断一个雇员是否存在。
  如果一个雇员不存在，那么使用SELECT...INTO来获取雇员信息就会失败，
  因为SELECT...INTO形式要求查询必须返回一行。但如果使用COUNT统计查询，
  返回满足条件的雇员人数，则该查询总是返回一行，所以任何情况都不会失败。
  COUNT返回的统计人数为0说明雇员不存在，返回的统计人数为1说明雇员存在，
  返回的统计人数大于1说明有多个满足条件的雇员存在。
  本例在雇员不存在时进行插入操作，如果雇员已经存在则不进行插入。
=================================================================
  【训练2】输出由符号“*”构成的正弦曲线的一个周期(0～360°)。
  SET SERVEROUTPUT ON SIZE 10000
SET LINESIZE 100
SET PAGESIZE 100
DECLARE
v_a NUMBER(8,3);
v_p NUMBER(8,3);
BEGIN
FOR I IN 1..18
  LOOP
      v_a:=I*20*3.14159/180;--生成角度，并转换为弧度
      v_p:=SIN(v_a)*20+25;--求SIN函数值，20为放大倍数，25为水平位移
      DBMS_OUTPUT.PUT_LINE(to_char(i)||lpad('*',v_p,' '));--输出记录变量的某个字段
  END LOOP;
  END;
  输出结果如下：
1                               *
2                                     *
3                                         *
4                                            *
5                                            *
6                                         *
7                                     *
8                               *
9                        *
10                 *
11           *
12       *
13    *
14    *
15       *
16           *
17                 *
18                        *
PL/SQL 过程已成功完成。
说明：在本程序中使用到了固定次数的循环以及SIN和LPAD函数，通过正确地设置步长、幅度和位移的参数，在屏幕上可正确地显示图形

=================================================================
6.4 练习
=================================================================
  1. 用来存放可变长度字符串的函数是：
  A. CHAR  B. VARCHAR2
  C. NUMBER  D. BOOLEAN
  2. 在程序中必须书写的语句是：
  A. SET SERVEROUTPUT ON
  B. DECLARE
  C. BEGIN       D. EXCEPTION
=================================================================
  3. 在程序中正确的变量定义语句是：
      A. emp_record emp.ename%ROWTYPE
      B. emp_record emp%ROWTYPE
      C. v_ename emp%TYPE
      D. v_ename ename%TYPE
  4. 在程序中最有可能发生错误的语句是：
      A. INSERT INTO emp(empno,ename) VALUES(8888,'Jone')
      B. UPDATE emp SET sal=sal+100
      C. DELETE FROM emp
      D. SELECT * FROM emp
=================================================================
  5. 关于以下分支结构，如果i的初值是15，环循结束后j的值是：
IF i>20 THEN
j:= i*2;
ELSIF i>15 THEN
j:= i*3;
ELSE
j:= i*4;
END IF;
A. 15  B. 30  C. 45  D. 60
=================================================================
  6. 关于以下循环，如果I的初值是3，则循环的次数是：
  WHILE I<6 LOOP
    I:= I + 1;
  END LOOP;
      A. 3    B. 4        C. 5    D. 6
  7. 以下表达式的结果非空的是：
      A. NULL||NULL            B. 'NULL'||NULL
      C. 3+NULL              D. (5>NULL)

sytWeibo

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
oracle PL-SQL学习案例（一）

【示例1.1】查询雇员编号为7788的雇员姓名和工资。步骤1：用SCOTT/TIGER账户登录SQL*Plus。步骤2：在输入区输入以下程序： /*这是一个简单的示例程序*/ SET SERVEROUTPUT ON DECLARE--定义部分标识 v_name VARCHAR2(10); --定义字符串变量v_name v_sal NUMBER(5); --定
复制链接

扫一扫