SQL语句 第10章 PLSQL编程基础

最新推荐文章于 2022-09-03 10:58:01 发布
最新推荐文章于 2022-09-03 10:58:01 发布
阅读量233 收藏
点赞数
分类专栏： ORACLE 文章标签： 编程 SQL 数据结构 D语言

10.1  PL/SQL语言基础

10.1.1  PL/SQL块简介
例10.1_1 最简单的PL/SQL块A bare minimum anonymous block:
    SET SERVEROUTPUT ON
    BEGIN
       DBMS_OUTPUT.PUT_LINE('This a minimum anonymous block');
    END;
/
例10.1_2

    DECLARE
       v_sname VARCHAR2(10);
    BEGIN
       SELECT name INTO v_sname
         FROM Students WHERE student_id = 10318;
       DBMS_OUTPUT.PUT_LINE ('学生姓名：'||v_sname);
    END;
/
例10.1_3
    DECLARE
       v_sname VARCHAR2(10);
    BEGIN
       SELECT name INTO v_sname
         FROM Students WHERE student_id = &student_id;
       DBMS_OUTPUT.PUT_LINE ('学生姓名：'||v_sname);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE ('输入的学号不存在！');
    END;
/
10.2  在PL/SQL中执行SQL语句
10.2.1  执行SELECT语句
例10.2_1
   DECLARE
     v_id Departments.department_id%type;
     v_name Departments.department_name%type;
     v_address Departments.address%type;
   BEGIN
       SELECT * INTO v_id,v_name,v_address
         FROM Departments WHERE department_id = 101;
       DBMS_OUTPUT.PUT_LINE ('系部名称：'||v_name);
       DBMS_OUTPUT.PUT_LINE ('系部地址：'||v_address);
    END;
/

例10.2_2
   DECLARE
     v_student Students%ROWTYPE;
    BEGIN
       SELECT * INTO v_student
         FROM Students WHERE student_id = 10212;
       DBMS_OUTPUT.PUT_LINE ('姓名  性别  出生日期');
       DBMS_OUTPUT.PUT_LINE (v_student.name
         ||v_student.sex||v_student.dob);
    END;
/

例10.2_3
   DECLARE
      v_student students%ROWTYPE;
   BEGIN
      SELECT * INTO v_student FROM students WHERE name LIKE '王%';
      DBMS_OUTPUT.PUT_LINE ('姓名  性别  出生日期');
      DBMS_OUTPUT.PUT_LINE (v_student.name
        ||v_student.sex||v_student.dob);
   END;
/

例10.2_4
   DECLARE
      v_student students%ROWTYPE;
   BEGIN
      SELECT * INTO v_student
        FROM students WHERE dob = '31-12月-1989';
      DBMS_OUTPUT.PUT_LINE ('姓名  性别  出生日期');
      DBMS_OUTPUT.PUT_LINE (v_student.name
        ||v_student.sex||v_student.dob);
   END;
/

10.2.2  执行DML语句
1．执行INSERT语句
例10.2_5 使用常量
    BEGIN
       INSERT INTO students
         VALUES(10188,NULL,'王一', '女', '07-5月-1988','计算机');
    END;

例10.2_6 使用变量
 DECLARE
   v_id students.student_id%TYPE := 10199;
   v_monitorid students.monitor_id%TYPE := NULL;
   v_name students.name%TYPE:='张三';
   v_sex students.sex%TYPE:='女';
   v_dob students.dob%TYPE:='07-5月-1988';
   v_specialty students.specialty%TYPE:='计算机';
   BEGIN
     INSERT INTO students
       VALUES(v_id,v_monitorid,v_name,v_sex,v_dob,v_specialty);
   END;

例10.2_7 使用子查询
    BEGIN
       INSERT INTO students_computer
         (SELECT * FROM students WHERE specialty='计算机');
    END;

2．执行UPDATE语句
例10.2_8 使用常量   
    BEGIN
       UPDATE students
          SET student_id = 10288,
              dob = '07-5月-1988',
              specialty ='自动化'
       WHERE student_id = 10188;
    END;
例10.2_9 使用变量
DECLARE
   v_id students.student_id%TYPE := 10188;
   v_monitorid students.monitor_id%TYPE := NULL;
   v_dob students.dob%TYPE := '17-5月-1988';
   v_specialty students.specialty%TYPE := '计算机';
   BEGIN
     UPDATE students
          SET student_id = v_id,
              dob = v_dob,
              specialty = v_specialty
       WHERE student_id = 10288;
   END;

例10.2_10
BEGIN
  UPDATE teachers
    SET bonus =
        (SELECT AVG(bonus)
  FROM teachers)
  WHERE bonus IS NULL;
END;

3．执行DELETE语句
例10.2_11 使用常量
BEGIN
   DELETE FROM students
     WHERE student_id = 10188;
END;

例10.2_12 使用变量
DECLARE
   v_specialty students.specialty%TYPE := '计算机';
   BEGIN
     DELETE FROM students
       WHERE specialty = v_specialty;
   END;

例10.2_13 使用子查询
BEGIN
  DELETE FROM teachers
    WHERE wage >
      (SELECT 1.1*AVG(wage)
        FROM teachers);
END;

10.2.3  执行事物处理语句
例10.2_14
BEGIN
   INSERT INTO students
     VALUES(10101,NULL,'王晓芳', '女', '07-5月-1988','计算机');
   COMMIT;
   DELETE FROM students
     WHERE specialty = '计算机';
   ROLLBACK;
  UPDATE students
     SET student_id = 10288,
         dob = '07-5月-1988',
         specialty ='自动化'
   WHERE student_id = 10101;
   SAVEPOINT sp1;
   DELETE FROM students
     WHERE student_id = 10101;
   SAVEPOINT sp2;
   ROLLBACK TO sp1;
 COMMIT;
END;

10.3  PL/SQL程序控制结构
10.3.1  顺序结构
例10.3_1
   DECLARE
     v_student students%ROWTYPE;
    BEGIN
       SELECT * INTO v_student
         FROM students WHERE student_id = 10213;
       DBMS_OUTPUT.PUT_LINE ('姓名：'||v_student.name);
       DBMS_OUTPUT.PUT_LINE ('性别：'||v_student.sex);
       DBMS_OUTPUT.PUT_LINE ('出生日期：'||v_student.dob);
       DBMS_OUTPUT.PUT_LINE ('专业：'||v_student.specialty);
    END;
/
10.3.2  分支结构
1．IF语句
（1）IF—THEN—END IF;
例10.3_2
DECLARE
   v_id teachers.teacher_id%TYPE;
   v_title teachers.title%TYPE;
BEGIN
   v_id := &teacher_id;
   SELECT title INTO v_title
     FROM Teachers
     WHERE teacher_id = v_id;
   IF v_title = '讲师' THEN
      UPDATE Teachers
        SET wage = 1.1*wage
        WHERE teacher_id = v_id;
   END IF;
END;

（2）IF—THEN—ELSE—END IF;
例10.3_3
DECLARE
   v_id Teachers.teacher_id%TYPE;
   v_title Teachers.title%TYPE;
BEGIN
  v_id := &teacher_id;
  SELECT title INTO v_title
    FROM Teachers WHERE teacher_id = v_id;
  IF v_title = '教授' THEN
     UPDATE Teachers
       SET wage = 1.1*wage WHERE teacher_id = v_id;
  ELSE
     UPDATE Teachers
       SET wage = wage+100 WHERE teacher_id = v_id;
  END IF;
END;

（3）IF—THEN—ELSIF—THEN—ELSE—END IF;
例10.3_4
DECLARE
   v_id Teachers.teacher_id%TYPE;
   v_title Teachers.title%TYPE;
BEGIN
  v_id := &teacher_id;
  SELECT title INTO v_title
    FROM Teachers WHERE teacher_id = v_id;
  IF v_title = '教授' THEN
     UPDATE Teachers
       SET wage = 1.1*wage WHERE teacher_id=v_id;
  ELSIF v_title = '高工' OR v_title= '副教授' THEN
     UPDATE Teachers
       SET wage = 1.05*wage WHERE teacher_id = v_id;
  ELSE
     UPDATE Teachers
       SET wage = wage+100 WHERE teacher_id = v_id;
  END IF;
END;
2．CASE语句
（1）
例10.3_5
DECLARE
   v_id Teachers.teacher_id%TYPE;
   v_title Teachers.title%TYPE;
BEGIN
  v_id := &teacher_id;
  SELECT title INTO v_title
    FROM Teachers WHERE teacher_id = v_id;
  CASE v_title
    WHEN '教授' THEN
       UPDATE Teachers
         SET wage = 1.15*wage WHERE teacher_id = v_id;
    WHEN '高工' THEN
       UPDATE Teachers
         SET wage = 1.05*wage WHERE teacher_id = v_id;
    WHEN '副教授' THEN
       UPDATE Teachers
         SET wage = 1.1*wage WHERE teacher_id = v_id;
    ELSE
       UPDATE Teachers
         SET wage = wage+100 WHERE teacher_id = v_id;
  END CASE;
END;

（2）
例10.3_6
DECLARE
   v_id Teachers.teacher_id%TYPE;
   v_title Teachers.title%TYPE;
BEGIN
  v_id := &teacher_id;
  SELECT title INTO v_title
    FROM Teachers WHERE teacher_id = v_id;
  CASE
    WHEN v_title = '教授' THEN
       UPDATE Teachers
         SET wage = 1.1*wage WHERE teacher_id = v_id;
    WHEN v_title = '高工' OR v_title= '副教授' THEN
       UPDATE Teachers
         SET wage = 1.05*wage WHERE teacher_id = v_id;
    ELSE
       UPDATE Teachers
         SET wage = wage+100 WHERE teacher_id = v_id;
  END CASE;
END;

例10.3_7
DECLARE
   v_id teachers.teacher_id%TYPE;
   v_bonus teachers.bonus%TYPE;
   v_wage teachers.wage%TYPE;
   v_income NUMBER(7,2);
BEGIN
  v_id := &teacher_id;
  SELECT bonus, wage INTO v_bonus, v_wage
     FROM teachers WHERE teacher_id = v_id;
  v_income := v_bonus + v_wage;
  CASE
    WHEN v_income <= 1000 THEN
       DBMS_OUTPUT.PUT_LINE ('个人所得税：0');
    WHEN v_income >1000 AND v_income < 3000 THEN
       DBMS_OUTPUT.PUT_LINE ('个人所得税：'||v_income*0.03);
    WHEN v_income >= 3000 THEN
       DBMS_OUTPUT.PUT_LINE ('个人所得税：'||v_income*0.05);
  END CASE;
END;
10.3.3  循环结构
CREATE TABLE total(n INT,result INT);
1．LOOP循环
例10.3_8
DECLARE
   v_i INT:=1;
   v_sum INT:=0;
BEGIN
   LOOP 
     v_sum := v_sum + v_i;
     INSERT INTO TOTAL VALUES(v_i, v_sum);
     EXIT WHEN v_i = 10;
     v_i := v_i+1;
   END LOOP;
END;

2．WHILE循环
例10.3_9
DECLARE
   v_i INT:=1;
   v_sum INT:=0;
BEGIN
  WHILE v_i <= 10  LOOP
     v_sum := v_sum + v_i*v_i;
     INSERT INTO TOTAL VALUES(v_i,v_sum);
     v_i := v_i+1;
  END LOOP;
END;

3．FOR循环
例10.3_10
DECLARE
   v_i INT:=1;
   v_factorial INT:=1;
BEGIN
  FOR v_i IN 1..10 LOOP
     v_factorial := v_factorial*v_i;
     INSERT INTO TOTAL VALUES(v_i,v_factorial);
  END LOOP;
END;

10.3.4  顺序控制
1. GOTO
例10.3_11
SET SERVEROUTPUT ON
DECLARE
   v_i INT:=1;
   v_sum INT:=0;
BEGIN
   LOOP 
     v_sum := v_sum + v_i;
     INSERT INTO TOTAL VALUES(v_i,v_sum);
     IF v_i = 10 THEN
        GOTO output;
     END IF;   
     v_i := v_i+1;
   END LOOP;
<<output>>
DBMS_OUTPUT.PUT_LINE ('v_sum = '||v_sum);
END;

2. NULL
例10.3_12
DECLARE
   v_i INT:=1;
   v_sum INT:=0;
BEGIN
   LOOP 
     v_sum := v_sum + v_i;
     INSERT INTO TOTAL VALUES(v_i,v_sum);
     IF v_i = 10 THEN
        GOTO output;
     END IF;   
     v_i := v_i+1;
   END LOOP;
<<output>>
   NULL;
END;

10.4  异常处理

10.4.1  异常的基本概念

10.4.2  系统异常处理

1．预定义异常
例10.4_1
SET SERVEROUTPUT ON
DECLARE
   v_dividend NUMBER:=50;
   v_divisor NUMBER:=0;
   v_quotient NUMBER;
BEGIN
v_quotient := v_dividend/v_divisor;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE ('除数为零！');
END;

例10.4_2 NO_DATA_FOUND
    SET SERVEROUTPUT ON
    DECLARE
       v_id Students.student_id%TYPE;      
       v_sname Students.name%TYPE;
    BEGIN
       v_id := &student_id;
       SELECT name INTO v_sname FROM Students WHERE student_id=v_id;
       DBMS_OUTPUT.PUT_LINE ('学生姓名：'||v_sname);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE ('输入的学号不存在！');
    END;

例10.4_3 TOO_MANY_ROWS
SET SERVEROUTPUT ON   
DECLARE
       v_specialty Students.specialty%TYPE;      
       v_sname Students.name%TYPE;
    BEGIN
       v_specialty := '&specialty';
       SELECT name INTO v_sname
         FROM Students WHERE specialty=v_specialty;
       DBMS_OUTPUT.PUT_LINE ('学生姓名：'||v_sname);
    EXCEPTION
      WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE ('返回的学生记录多于一行！');
    END;

例10.4_4 NO_DATA_FOUND TOO_MANY_ROWS
SET SERVEROUTPUT ON   
DECLARE
       v_specialty Students.specialty%TYPE;      
       v_sname Students.name%TYPE;
    BEGIN
       v_specialty := '&specialty';
       SELECT name INTO v_sname
         FROM Students WHERE specialty=v_specialty;
       DBMS_OUTPUT.PUT_LINE ('学生姓名：'||v_sname);
    EXCEPTION
      WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE ('返回的学生记录多于一行！');
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE ('输入的专业不存在！');
    END;

2．非预定义异常
例10.4_5
SET SERVEROUTPUT ON   
DECLARE
       e_deptid  EXCEPTION;
       PRAGMA  EXCEPTION_INIT(e_deptid, -2292);
    BEGIN
       DELETE FROM Departments
         WHERE department_id = 101;
    EXCEPTION
      WHEN e_deptid THEN
        DBMS_OUTPUT.PUT_LINE ('在教师表中存在子记录！');
    END;

例10.4_6
SET SERVEROUTPUT ON   
DECLARE
       e_deptid  EXCEPTION;
       PRAGMA  EXCEPTION_INIT(e_deptid, -2291);
    BEGIN
      INSERT INTO Teachers
        VALUES(11101,'王彤', '教授', '01-9月-1990',1000,3000,999);
    EXCEPTION
      WHEN e_deptid THEN
        DBMS_OUTPUT.PUT_LINE ('插入记录的部门号在父表中不存在！');
    END;

例10.4_7
SET SERVEROUTPUT ON   
DECLARE
       e_studentid EXCEPTION;
       PRAGMA EXCEPTION_INIT(e_studentid, -0001);
    BEGIN
       INSERT INTO Students
         VALUES(10205,NULL,'王三', '男', '26-12月-1989','自动化');
    EXCEPTION
       WHEN e_studentid THEN
          DBMS_OUTPUT.PUT_LINE ('插入学生记录的学号在表中已存在！');
    END;

10.4.3  用户自定义异常处理
例10.4_8
SET SERVEROUTPUT ON   
DECLARE
       e_wage EXCEPTION;
       v_wage Teachers.wage%TYPE;
    BEGIN
       v_wage := &wage;
       INSERT INTO Teachers
         VALUES(10111,'王彤', '教授', '01-9月-1990',1000,v_wage,101);
       IF v_wage < 0 THEN
          RAISE e_wage;
       END IF;
    EXCEPTION
       WHEN e_wage THEN
          DBMS_OUTPUT.PUT_LINE ('教师工资不能为负值！');
          ROLLBACK;
    END;

例10.4_9 带WHEN OTHERS THEN可选项
SET SERVEROUTPUT ON
DECLARE
       e_wage EXCEPTION;
       v_wage Teachers.wage%TYPE;
       v_deptid Teachers.department_id%TYPE;
       v_bonus Teachers.bonus%TYPE;
    BEGIN
       v_wage := &wage;
       v_deptid := &department_id;
       INSERT INTO Teachers
         VALUES(10111,'王彤', '教授', '01-9月-1990',1000,v_wage,101);
       SELECT bonus INTO v_bonus
         FROM Teachers WHERE department_id = v_deptid;
       IF v_wage < 0 THEN
          RAISE e_wage;
       END IF;
    EXCEPTION
       WHEN e_wage THEN
          DBMS_OUTPUT.PUT_LINE ('教师工资不能为负值！');
          ROLLBACK;
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('查询教师奖金时出错！');
    END;

10.4.4  使用异常函数
例10.4_10 使用SQLCODE和SQLERRM异常函数
    SET SERVEROUTPUT ON   
    DECLARE
       e_wage EXCEPTION;
       v_wage Teachers.wage%TYPE;
       v_deptid Teachers.department_id%TYPE;
       v_bonus Teachers.bonus%TYPE;
    BEGIN
       v_wage := &wage;
       v_deptid := &department_id;
       INSERT INTO Teachers
         VALUES(10111,'王彤', '教授', '01-9月-1990',1000,v_wage,101);
       SELECT bonus INTO v_bonus
         FROM Teachers WHERE department_id = v_deptid;
       IF v_wage < 0 THEN
          RAISE e_wage;
       END IF;
    EXCEPTION
       WHEN e_wage THEN
          DBMS_OUTPUT.PUT_LINE ('教师工资不能为负值！');
          ROLLBACK;
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('错误代码：'||SQLCODE);
          DBMS_OUTPUT.PUT_LINE ('错误描述：'||SQLERRM);
    END;

10.5  游标
10.5.2  游标应用
1．浏览数据
例10.5_1
     SET SERVEROUTPUT ON  
     DECLARE
       v_specialty Students.specialty%TYPE;      
       v_sname Students.name%TYPE;
       v_dob Students.dob%TYPE;
       CURSOR Students_cur
       IS
          SELECT name,dob
            FROM Students
           WHERE specialty = v_specialty;
     BEGIN
        v_specialty := '&specialty';
        OPEN Students_cur;
        DBMS_OUTPUT.PUT_LINE ('学生姓名   出生日期');
        LOOP
           FETCH Students_cur INTO v_sname,v_dob;
           EXIT WHEN Students_cur%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE (v_sname||'     '||v_dob);
        END LOOP;
        CLOSE Students_cur;
    END;

2．修改数据
例10.5_2
     DECLARE
       v_title Teachers.title%TYPE;
       CURSOR Teachers_cur
       IS
          SELECT title
            FROM Teachers
            FOR UPDATE;
     BEGIN
        OPEN Teachers_cur;
        LOOP
           FETCH Teachers_cur INTO v_title;
           EXIT WHEN Teachers_cur%NOTFOUND;
           CASE
             WHEN v_title = '教授' THEN
                UPDATE Teachers
                  SET wage = 1.1*wage WHERE CURRENT OF Teachers_cur;
             WHEN v_title = '高工' OR v_title= '副教授' THEN
                UPDATE Teachers
                  SET wage = 1.05*wage WHERE CURRENT OF Teachers_cur;
             ELSE
                UPDATE Teachers
                  SET wage = wage+100 WHERE CURRENT OF Teachers_cur;
           END CASE;
        END LOOP;
        CLOSE Teachers_cur;
    END;

3．删除数据
例10.5_3
     DECLARE
       v_specialty Students.specialty%TYPE;      
       v_sname Students.name%TYPE;
       CURSOR Students_cur
       IS
          SELECT name,specialty
            FROM Students
            FOR UPDATE;
     BEGIN
        OPEN Students_cur;
        FETCH Students_cur INTO v_sname,v_specialty;
        WHILE Students_cur%FOUND LOOP
           IF v_specialty = '计算机' THEN
              DELETE FROM Students WHERE CURRENT OF Students_cur;
           END IF;
           FETCH Students_cur INTO v_sname,v_specialty;
        END LOOP;
        CLOSE Students_cur;
     END;

10.5.3  游标FOR循环
1．语句格式一
例10.5_4
SET SERVEROUTPUT ON  
     DECLARE
     v_specialty Students.specialty%TYPE;
       CURSOR Students_cur
       IS
          SELECT name,dob
            FROM Students
           WHERE specialty = v_specialty;
     BEGIN
        v_specialty := '&specialty';
        DBMS_OUTPUT.PUT_LINE ('序号  学生姓名   出生日期');
        FOR Students_record IN Students_cur LOOP
           DBMS_OUTPUT.PUT_LINE (Students_cur%ROWCOUNT||'     '||Students_record.name||'     '||Students_record.dob);
        END LOOP;
     END;

2．语句格式二
例10.5_5
SET SERVEROUTPUT ON  
     DECLARE
     v_specialty Students.specialty%TYPE;
       CURSOR Students_cur
       IS
          SELECT name,dob
            FROM Students
           WHERE specialty = v_specialty;
     BEGIN
        v_specialty := '&specialty';
        DBMS_OUTPUT.PUT_LINE ('学生姓名   出生日期');
        FOR Students_record IN
           (SELECT name,dob FROM Students WHERE specialty = v_specialty) LOOP
           DBMS_OUTPUT.PUT_LINE (Students_record.name||'     '||Students_record.dob);
        END LOOP;
     END;
10.5.4  游标的复杂应用
1．参数游标
例10.5_6
     SET SERVEROUTPUT ON
     DECLARE
       v_dob Students.dob%TYPE;      
       v_sname Students.name%TYPE;
       CURSOR Students_cur(v_specialty Students.specialty%TYPE)
       IS
          SELECT name, dob
            FROM Students WHERE specialty = v_specialty;
     BEGIN
        OPEN Students_cur('机电工程');
        FETCH Students_cur INTO v_sname,v_dob;
        WHILE Students_cur%FOUND LOOP
           DBMS_OUTPUT.PUT_LINE (v_sname||'     '||v_dob);
           FETCH Students_cur INTO v_sname,v_dob;
        END LOOP;
        CLOSE Students_cur;
     END;


例10.5_7
     SET SERVEROUTPUT ON
     DECLARE
       v_tname Teachers.name%TYPE;
       v_wage Teachers.wage%TYPE;      
       CURSOR Teachers_cur(t_title VARCHAR2,t_wage NUMBER)
       IS
          SELECT name, wage
            FROM Teachers WHERE title = t_title AND wage > t_wage;
     BEGIN
        OPEN Teachers_cur('副教授',2000);
        FETCH Teachers_cur INTO v_tname,v_wage;
        WHILE Teachers_cur%FOUND LOOP
           DBMS_OUTPUT.PUT_LINE (v_tname||'     '||v_wage);
           FETCH Teachers_cur INTO v_tname,v_wage;
        END LOOP;
        CLOSE Teachers_cur;
     END;

2．游标变量
例10.5_8 游标变量无返回值。

     SET SERVEROUTPUT ON
     DECLARE
        TYPE Students_cur IS REF CURSOR;
        StuCursor Students_cur;
        Students_record Students%ROWTYPE;
     BEGIN
     IF NOT StuCursor%ISOPEN THEN
        OPEN StuCursor FOR SELECT * FROM Students;
     END IF;
     DBMS_OUTPUT.PUT_LINE ('学生姓名   出生日期');
     LOOP
       FETCH StuCursor INTO Students_record;
       EXIT WHEN StuCursor%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE (Students_record.name||'     '||Students_record.dob);
     END LOOP;
     CLOSE StuCursor;
     END;

例10.5_9 游标变量具有返回值。
     SET SERVEROUTPUT ON
     DECLARE
        TYPE Students_record IS RECORD(
          StuName VARCHAR2(10),
          StuDOB DATE
        );
        StuRecord Students_record;
        TYPE Students_cur IS REF CURSOR RETURN Students_record;
        StuCursor Students_cur;
     BEGIN
        IF NOT StuCursor%ISOPEN THEN
           OPEN StuCursor FOR SELECT name,dob FROM Students;
        END IF;
        DBMS_OUTPUT.PUT_LINE ('学生姓名   出生日期');
        LOOP
          FETCH StuCursor INTO StuRecord;
          EXIT WHEN StuCursor%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE (StuRecord.StuName||'     '||StuRecord.StuDOB);
        END LOOP;
     CLOSE StuCursor;
     END;

3．游标表达式
10.5_10 定义游标Departments_cur。在游标Departments_cur中使用了游标表达式CURSOR(SELECT name, title FROM Teachers WHERE department_id = d.department_id)。
     SET SERVEROUTPUT ON
     DECLARE
        v_tname Teachers.name%TYPE;
        v_title Teachers.title%TYPE;
        v_dname Departments.department_name%TYPE;        
        TYPE cursor_type IS REF CURSOR;
        CURSOR Departments_cur(dept_id NUMBER) IS
        SELECT d.department_name, CURSOR(SELECT name, title
        FROM Teachers WHERE department_id = d.department_id)
        FROM Departments d WHERE d.department_id = dept_id;
        Teachers_cur cursor_type;
     BEGIN
        OPEN Departments_cur('101');
        LOOP
          FETCH Departments_cur INTO v_dname, Teachers_cur;
          EXIT WHEN Departments_cur%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE ('系部名称：'||v_dname);  
          DBMS_OUTPUT.PUT_LINE ('教师姓名   职称');
          LOOP
            FETCH Teachers_cur INTO v_tname, v_title;
            EXIT WHEN Teachers_cur%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE (v_tname||'     '||v_title);  
          END LOOP;
        END LOOP;
     END;

 

lilin745997788
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle备考复习
wangzhen的专栏
05-12 622
1、建表 字段 数据类型(常用的) create table students ( sid number(5) constraint stu_pk primary key, name varchar2(10) not null, sex varchar2(6) constraint sex_chk_s check(sex in ('m','w')), dob date , s
oracle查询姓王的学生,Oracle之SQL 和 PL/SQL编程指南
weixin_39854326的博客
04-04 2217
1.定义表的结构：sql>createtablestu(student_idnumber(5)constrantstudent_pkprimarykey,--定义主键约束moitor_idnumber(5),namevarchar2(10)notnull,sexvarchar2(6),constraintsex_chkcheck(sex...
第6天：数据类型详解(整型、字符串）
孜孜淑淑的博客
02-04 3999
2021年12月21 日 文章目录一、int：整型1.整数的进制2.进制之间的转换3.数字分隔符4.需要掌握的一个方法int()二、string：字符串类型1.将其它数据类型转成字符型2.字符型用引号包裹3.字符串可以相加相乘4.序列int 和 string 类型的相关重点 一、int：整型 1.整数的进制 ①二进制 ​ 逢二进一，由 0 和 1 两个数字组成，书写时以0b或0B开头 bin1 = 0b101 print('bin1Value: ', bin1) #运行结果：bin1Value:5 bin2
高并发学生选课系统设计
Skyed_blue的博客
02-27 5253
高并发选课系统设计 简述 学生选课系统类似于秒杀系统，都是要在一个时间段内抗住高并发。因此我们的学生选课系统可以按照秒杀系统来设计。 学生选课系统的QPS其实远远不及秒杀系统，毕竟一个学校最多也就同时几千个人选课，因此在架构上可以不用设计的太麻烦，只要能抗住高并发，让学生都有操作空间就可。但本文想抛开现实，按照秒杀系统来好好设计一下选课系统。 开发技术 SpringCloud，Mybatis-plus，MySQL，Redis，redisson，RabbitMQ，Quartz 数据表设计 teacher(教师
oracle循环结构计算10,Oracle中的pl/sql
weixin_29496633的博客
04-13 306
什么是pl/sqlpl/sql是一种编程语言，是oracle对sql语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。PL/SQL的作用使用PL/SQL可以编写具有很多高级功能的程序，虽然通过多个SQL语句可能也能实现同样的功能，但是相比而言，PL/SQL...
第章PLSQL编程基础2022优秀文档.ppt
11-17
第章PLSQL编程基础2022优秀文档.ppt
Sql语句_SQL语句_plsql_
09-29
plsql 语句命令
第7章PLSQL编程基础.pptx
最新发布
11-03
第7章PLSQL编程基础.pptx
第7章PLSQL编程基础.pdf
11-01
第7章PLSQL编程基础.pdf
Oracle 10g SQL操作.zip
01-04
简单的介绍了一个常用的SQL 语句 3.2 例3.2_1 执行SQL语句。 SELECT sysdate FROM dual; 例3.2_2 执行PL/SQL程序。 SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE('这是第一个PL/SQL程序'); END; / 3.3.2 定义表的结构 例3.3_1 students表结构 CREATE TABLE students ( student_id NUMBER(5) CONSTRAINT student_pk PRIMARY KEY, monitor_id NUMBER(5), name VARCHAR2(10) NOT NULL, sex VARCHAR2(6) CONSTRAINT sex_chk CHECK(sex IN ('男','女')), dob DATE, specialty VARCHAR2(64) ); 例3.3_2 departments表结构 CREATE TABLE departments( department_id NUMBER(3) CONSTRAINT department_pk PRIMARY KEY, department_name VARCHAR2(8) NOT NULL, address VARCHAR2(40) ); 例3.3_3 teachers表结构 CREATE TABLE teachers ( teacher_id NUMBER(5) CONSTRAINT teacher_pk PRIMARY KEY, name VARCHAR2(8) NOT NULL, title VARCHAR2(6), hire_date DATE DEFAULT SYSDATE, bonus NUMBER(7,2), wage NUMBER(7,2), department_id NUMBER(3) CONSTRAINT teachers_fk_departments REFERENCES departments(department_id ) ); 例3.3_4 courses表结构 CREATE TABLE courses( course_id NUMBER(5) CONSTRAINT course_pk PRIMARY KEY, course_name VARCHAR2(30) NOT NULL, credit_hour NUMBER(2) ); 例3.3_5 students_grade表结构 CREATE TABLE students_grade( student_id NUMBER(5) CONSTRAINT students_grade_fk_students REFERENCES students(student_id), course_id NUMBER(5) CONSTRAINT students_grade_fk_courses REFERENCES courses(course_id), score NUMBER(4,1) ); 例3.3_6 grades表结构 CREATE TABLE grades ( grade_id NUMBER(1) CONSTRAINT grade_pk PRIMARY KEY, low_score NUMBER(4, 1), high_score NUMBER(4, 1), grade VARCHAR2(6) ); 3.3.3 查看表结构 例3.3_7 查看students表结构 DESCRIBE students; 例3.3_8 查看teachers表结构 DESCRIBE teachers; 例3.3_9 查看departments表结构 DESCRIBE departments; 例3.3_10 查看courses表结构 DESCRIBE courses; 例3.3_11 查看students_grade表结构 DESCRIBE students_grade; 例3.3_12 查看Grades表结构 DESCRIBE grades; 3.3.4 删除表 例3.3_13 删除teachers表。 DROP TABLE teachers; 例3.3_14 删除departments表。 DROP TABLE departments; 例3.3_15 删除students_grade表。 DROP TABLE students_grade; 例3.3_16 删除students表。 DROP TABLE students; 例3.3_17 删除courses表。 DROP TABLE courses; 例3.3_18 删除grades表。 DROP TABLE grades; 3.3.5 添加数据 同名,同生日(参加工作时间),时间数据为NULL. 例3.3_19 添加students记录 学生记录： INSERT INTO students VALUES(10101,NULL,'王晓芳', '女', '07-5月-1988','计算机'); INSERT INTO students VALUES(10205,NULL,'李秋枫', '男', '25-11月-1990','自动化'); INSERT INTO students VALUES(10102,10101,'刘春苹', '女', '12-8月-1991','计算机'); INSERT INTO students VALUES(10301,NULL,'高山', '男', '08-10月-1990','机电工程'); INSERT INTO students VALUES(10207,10205,'王刚', '男', '03-4月-1987','自动化'); INSERT INTO students VALUES(10112,10101,'张纯玉', '男', '21-7月-1989','计算机'); INSERT INTO students VALUES(10318,10301,'张冬云', '女', '26-12月-1989','机电工程'); INSERT INTO students VALUES(10103,10101,'王天仪', '男', '26-12月-1989','计算机'); INSERT INTO students VALUES(10201,10205,'赵风雨', '男', '25-10月-1990','自动化'); INSERT INTO students VALUES(10105,10101,'韩刘', '男', '3-8月-1991','计算机'); INSERT INTO students VALUES(10311,10301,'张杨', '男', '08-5月-1990','机电工程'); INSERT INTO students VALUES(10213,10205,'高淼', '男', '11-3月-1987','自动化'); INSERT INTO students VALUES(10212,10205,'欧阳春岚', '女', '12-3月-1989','自动化'); INSERT INTO students VALUES(10314,10301,'赵迪帆', '男', '22-9月-1989','机电工程'); INSERT INTO students VALUES(10312,10301,'白菲菲', '女', '07-5月-1988','机电工程'); INSERT INTO students VALUES(10328,10301,'曾程程', '男', NULL,'机电工程'); INSERT INTO students VALUES(10128,10101,'白昕', '男', NULL, '计算机'); INSERT INTO students VALUES(10228,10205,'林紫寒', '女', NULL, '自动化'); 例3.3_20 添加departments记录 系部记录： INSERT INTO departments VALUES(101,'信息工程','1号教学楼'); INSERT INTO departments VALUES(102,'电气工程','2号教学楼'); INSERT INTO departments VALUES(103,'机电工程','3号教学楼'); 连接查询时使用： INSERT INTO departments VALUES(104,'工商管理','4号教学楼'); 例3.3_21 添加teachers记录 教师记录： INSERT INTO teachers VALUES(10101,'王彤', '教授', '01-9月-1990',1000,3000,101); INSERT INTO teachers VALUES(10104,'孔世杰', '副教授', '06-7月-1994',800,2700,101); INSERT INTO teachers VALUES(10103,'邹人文', '讲师', '21-1月-1996',600,2400,101); INSERT INTO teachers VALUES(10106,'韩冬梅', '助教', '01-8月-2002',500,1800,101); INSERT INTO teachers VALUES(10210,'杨文化', '教授', '03-10月-1989',1000,3100, 102); INSERT INTO teachers VALUES(10206,'崔天', '助教', '05-9月-2000',500,1900, 102); INSERT INTO teachers VALUES(10209,'孙晴碧','讲师', '11-5月-1998',600,2500, 102); INSERT INTO teachers VALUES(10207,'张珂', '讲师', '16-8月-1997',700,2700, 102); INSERT INTO teachers VALUES(10308,'齐沈阳', '高工', '03-10月-1989',1000,3100, 103); INSERT INTO teachers VALUES(10306,'车东日', '助教', '05-9月-2001',500,1900, 103); INSERT INTO teachers VALUES(10309,'臧海涛','工程师', '29-6月-1999',600,2400, 103); INSERT INTO teachers VALUES(10307,'赵昆', '讲师', '18-2月-1996',800,2700, 103); 教师记录（NULL）： INSERT INTO teachers VALUES(10128,'王晓', NULL,'05-9月-2007',NULL,1000, 101); INSERT INTO teachers VALUES(10328,'张笑', NULL, '29-9月-2007',NULL,1000, 103); INSERT INTO teachers VALUES(10228,'赵天宇', NULL, '18-9月-2007',NULL,1000, 102); 连接查询时使用： INSERT INTO teachers VALUES(11111,'林飞', NULL, '11-10月-2007',NULL,1000, NULL); 例3.3_22 添加Courses记录 INSERT INTO courses VALUES(10101,'计算机组成原理',4); INSERT INTO courses VALUES(10201,'自动控制原理',4); INSERT INTO courses VALUES(10301,'工程制图',3); INSERT INTO Courses VALUES(10102,'C++语言程序设计',3); INSERT INTO courses VALUES(10202,'模拟电子技术',4); INSERT INTO courses VALUES(10302,'理论力学',3); INSERT INTO courses VALUES(10103,'离散数学',3); INSERT INTO courses VALUES(10203,'数字电子技术',4); INSERT INTO courses VALUES(10303,'材料力学',3); 例3.3_23 添加students_grade记录 INSERT INTO students_grade VALUES(10101,10101,87); INSERT INTO students_grade VALUES(10101,10201,100); INSERT INTO students_grade VALUES(10101,10301,79); 例3.3_24 添加grades记录 INSERT INTO grades VALUES(1,0,59,'不及格'); INSERT INTO grades VALUES(2,60,69,'及格'); INSERT INTO grades VALUES(3,70,79,'中等'); INSERT INTO grades VALUES(4,80,89,'良好'); INSERT INTO grades VALUES(5,90,100,'优秀'); 3.3.6 查看数据 例3.3_25 查看students数据 SELECT * FROM students; 例3.3_26 查看teachers数据 SELECT * FROM teachers; 例3.3_27 查看departments数据 SELECT * FROM departments; 例3.3_28 查看courses数据 SELECT * FROM courses; 例3.3_29 查看students_grade数据 SELECT * FROM students_grade; 例3.3_30 查看grades数据 SELECT * FROM grades; 3.3.7 删除数据 例3.3_31 删除students_grade数据 DELETE FROM students_grade; 例3.3_32 删除teachers数据 DELETE FROM teachers; 例3.3_33 删除departments数据 DELETE FROM departments; 例3.3_34 删除courses数据 DELETE FROM courses; 例3.3_35 删除students数据 DELETE FROM students; 例3.3_36 删除grades数据 DELETE FROM grades;
基础学习（二）：PL/SQL编程
qq_37118044的博客
07-08 750
最近在看PL/SQL开发的基础知识，以下博文的作者总结的简洁明了，作为初步了解够用了。 原文链接：https://blog.csdn.net/yujikui1/article/details/80701817 首先，先介绍一下PL/SQL 是什么？ PL/SQL是是由甲骨文公司在90年代初开发，以提高SQL的功能。PL/SQL是嵌入在Oracle数据库中的编程语言之一。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句
PLSQL编程-循环结构
weixin_45867975的博客
09-03 508
Oracle数据库技术，PLSQL编程-循环结构，使用循环输出1~10的数值，分别使用了loop循环，while循环，for循环
[Oracle 学习笔记] 06 DML
weixin_30951743的博客
12-08 53
DML包括 INSERT DELETE UPDATE 　　SQL> INSERT INTO students VALUES(10138,10101, '王一'，‘20-12 月-1989’) 　　SQL> UPDATE students SET student_id = 10195 WHERE student_id = 15252; 　　SQL> DE...
SQL数据操作语言DML和事物控制语言TC
相信我，没错的！
12-01 143
数据操作语言DML   操作数据库时注意数据库的完整性   insert into students values(1001,1011,'张三','男',26-12月-1989','计算机');   insert into teachers(teacher_id,name,department_id) values(10001,'张三',101);   insert into ...
oracle中的%type和%rowtype的区别
吕伟东(Java)
08-22 1128
%TYPE 是定义变量的一种方法 V_StudentID students.id%TYPE 表示的含义：变量V_StudentID 与students表中id的字段数据类型一致，长度也一致，当students中id的数据类型改变时，那么V_StudentID的数据类型也会跟着改变。 %rowtype     表示该类型为行数据类型，存储的是一行数据，一行数据里可以有多列，
PLSQL基本语法
cuikuo8363的博客
03-09 136
plsql 也称过程化的sql ,是oracle对sql的扩充，在普通 sql 的基础上增加了编程语言的特点plsql 语言的组成：块结构，一个plsql一般有以下几个部分 DECLARE --变量定义部...
pl/sql编程学习
dzdffg的博客
12-08 347
pl/sql编程学习 1、入门 pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。 pl/sql编程语言比一般的过程化编程语言，更加高效灵活。 pl/sql编程语言主要要来编写存储过程和存储函数等。 pl/sql语法比较严密，书写时注意结尾的"；" PL/SQL块语法结构： [DECLARE] 声明语句... BEGIN 执行语句... [EXCEPTION] 异常处理语句... END; 语法解析： 声明部分是可选部分，由DECLARE开始，声明执行部分所..
oracle PL/SQL编程笔记
gly_ustb的博客
08-07 158
1.第一范式：实体的所有属性都是不可分割的，称该表满足1NF； 第二范式：允许用多个属性作为主码 第三范式：不存在传递函数依赖关系，即存在一个属性，其函数依赖的属性既不是主码也不是候选码 Boyce-Codd范式：在表中，可以将其中一列或多列指定为主码，也可以指定其他某些列为候选码，表中也存在着其他属性。不考虑候选码， 唯一的函数依赖关系存在于表中每个属性和整个主码之间 2....
plsql跟踪sql语句
10-08
我们可以使用Oracle提供的PL/SQL开发工具来跟踪SQL语句。...另外，还有一些第三方工具，如Statement Tracer for Oracle，可以用于跟踪C/S模式下应用程序执行的SQL语句。这些工具可以提供更详细的跟踪和调试功能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值