- 浏览: 29237 次
- 性别:
- 来自: 北京
最新评论
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;
发表评论
-
SQL语句 第12章 应用程序结构(过程,函数,包,触发器)
2009-12-03 17:09 111712.1 子程序 运行在服务器端,快;简化应用程序开 ... -
SQL语句 第11章 复合数据类型
2009-12-03 17:08 81211.1.2 在SELECT语句中使用记录1.使用%ROWT ... -
SQL语句 第9章 数据描述语言(DDL)与数据控制语言(DCL)
2009-12-03 17:08 10219.1 数据控制语言(DCL)9.1.1 数据库权限3.用 ... -
SQL语句 第8章 SQL函数
2009-12-03 17:07 9448.1 数字函数8.1.1 数字函数概述1.ABS(x)例 ... -
SQL语句 第7章 DML语句与事物处理
2009-12-03 17:07 7737.1 数据操作语言(DML)7.1.1 插入数据(INS ... -
SQL语句 第6章 连接查询
2009-12-03 17:06 6246.1 内连接查询 6.1.1 简单内连接 ... -
SQL语句 第5章 子查询和集合操作
2009-12-03 02:37 11375.1 子查询 5.1.1 单行子查询 1.在WHE ... -
SQL语句 第4章 单表查询
2009-12-03 00:45 9364.1 简单查询 4.1.1 查询指定列 例4.1_ ... -
数据控制语言DCL和数据定义语言DDL
2009-12-02 15:48 1593数据控制语言DCL 系统权限: create sess ... -
SQL数据操作语言DML和事物控制语言TC
2009-12-01 16:34 1029数据操作语言DML 操作数据库时注意数据库的完整 ... -
数据库系统工程师试题
2009-11-24 18:06 13802004--2009数据库系统工程师试题及答案
相关推荐
第章PLSQL编程基础2022优秀文档.ppt
在plsql把一个sql语句美化后要复制到程序里使用时,就会发现有多余的空格和换行符,此程序将美化后的sql语句还原成1行sql语句
第7章PLSQL编程基础.pptx
第7章PLSQL编程基础.pdf
plsql中 sql 语句自动补全 如 只输入s 即可自动补全为 select * from 使用方法 下载该文件 并在plsql中 选择 tools-perferences-edit-autoreplaces 点击浏览 选择该文件
PLSQL编程基础必看,绝对经典!文件限制大小,只能分割成2部分,一起下载解压就OK!
PLSQL编程基础 pdf版(内部教材),可以作为参数书使用查询。
plsql编程基础,老师上课用的ppt课件,个人觉得初学者可以多看看几遍,会有帮助的
plsql 语句命令
Oracle数据库系统应用开发实用教程电子课件 第7章 PLSQL编程基础
PLSQL编程基础培训.ppt PLSQL编程基础培训.ppt PLSQL编程基础培训.ppt
Oracle PLSQL 编程手册(SQL大全)
附件为Oracle 10g SQL操作和PLSQL编程指南光盘 内涵各章sql语句,其中第三章被我修改过表名,用时建议用ue把前缀去掉,用时注意
PLSQL编程基础.doc
PLSQL编程基础.ppt
Oracle数据库维护常用SQL语句集合(3)-PLSQL
oracle PLSQL编程 基础知识 plsql基础知识 , 存储过程 程序包等教才