plsql语法编程实例

11.1.2 在SELECT语句中使用记录 1．使用%ROWTYPE属性定义记录 例11.1_1 SET SERVEROUTPUT ON DECLARE v_student Students%ROWTYPE; BEGIN SELECT * INTO v_student FROM Students WHERE student_id = 10201; DBMS_OUTPUT.PUT_LINE ('姓名 性别 专业'); DBMS_OUTPUT.PUT_LINE (v_student.name||' '||v_student.sex||' '||v_student.specialty); END; 例11.1_2 CREATE VIEW Students_view AS SELECT * FROM Students WHERE sex='男'; SET SERVEROUTPUT ON DECLARE v_student Students_view%ROWTYPE; BEGIN SELECT * INTO v_student FROM Students_view WHERE student_id = 10201; DBMS_OUTPUT.PUT_LINE ('姓名 性别 专业'); DBMS_OUTPUT.PUT_LINE (v_student.name||' '||v_student.sex||' '||v_student.specialty); END; 例11.1_3 SET SERVEROUTPUT ON DECLARE CURSOR students_cur IS SELECT name,dob FROM Students WHERE specialty = '计算机'; v_student students_cur%ROWTYPE; BEGIN DBMS_OUTPUT.PUT_LINE ('序号 学生姓名 出生日期'); FOR Students_record IN Students_cur LOOP v_student.name := Students_record.name; v_student.dob := Students_record.dob; DBMS_OUTPUT.PUT_LINE (Students_cur%ROWCOUNT||' '||v_student.name||' '||v_student.dob); END LOOP; END; 2．使用显式方法定义记录 例11.1_4 SET SERVEROUT ON DECLARE TYPE s_record IS RECORD (name Students.name%TYPE, sex Students.sex%TYPE, dob Students.dob%TYPE); students_record s_record; v_id Students.student_id%TYPE; BEGIN v_id := &student_id; DBMS_OUTPUT.PUT_LINE ('学生姓名 性别 出生日期'); SELECT name,sex,dob INTO students_record FROM Students WHERE student_id = v_id; DBMS_OUTPUT.PUT_LINE (students_record.name||' '||Students_record.sex||' '||Students_record.dob); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (sqlcode||sqlerrm); END; 3．使用记录成员 例11.1_5 SET SERVEROUT ON DECLARE TYPE s_record IS RECORD (name Students.name%TYPE, sex Students.sex%TYPE, dob Students.dob%TYPE); students_record s_record; v_specialty Students.specialty%TYPE; i INT := 0; BEGIN v_specialty := '&specialty'; DBMS_OUTPUT.PUT_LINE ('序号 学生姓名 性别 出生日期'); FOR students_row IN (SELECT * FROM Students WHERE specialty=v_specialty) LOOP i:=i+1; students_record.name := students_row.name; students_record.sex := students_row.sex; students_record.dob := students_row.dob; DBMS_OUTPUT.PUT_LINE (i||' '||students_record.name||' '||Students_record.sex||' '||Students_record.dob); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (sqlcode||sqlerrm); END; 11.1.3 在DML语句中使用记录 1．在UPDATE语句中 例11.1_6 DECLARE TYPE s_record IS RECORD (id Students.student_id%TYPE, dob Students.dob%TYPE); students_record s_record; BEGIN students_record.id := 10101; students_record.dob := '25-11月-1990'; UPDATE Students SET dob = students_record.dob WHERE student_id = students_record.id; END; 例11.1_7 DECLARE TYPE s_record IS RECORD (id Students.student_id%TYPE, monitor_id Students.monitor_id%TYPE, name Students.name%TYPE, sex Students.sex%TYPE, dob Students.dob%TYPE, specialty Students.specialty%TYPE); students_record s_record; BEGIN students_record.id := 10288; students_record.monitor_id := 10205; students_record.name := '王天仪'; students_record.sex := '男'; students_record.dob := '25-11月-1990'; students_record.specialty := '自动化'; UPDATE Students SET ROW = students_record WHERE student_id = 10103; END; 2．在INSERT语句中 例11.1_8 DECLARE students_record Students%ROWTYPE; BEGIN students_record.student_id := 10288; students_record.monitor_id := 10205; students_record.name := '王一'; students_record.sex := '男'; students_record.dob := '25-11月-1990'; students_record.specialty := '自动化'; INSERT INTO Students VALUES students_record; END; 例11.1_9 DECLARE TYPE s_record IS RECORD (id Departments.department_id%TYPE, name Departments.department_name%TYPE); departments_record s_record; BEGIN departments_record.id := 111; departments_record.name := '地球物理'; INSERT INTO Departments(department_id,department_name) VALUES (departments_record.id,departments_record.name); END; 3．在DELETE语句中 例11.1_10 DECLARE TYPE d_record IS RECORD (id Departments.department_id%TYPE); departments_record d_record; BEGIN departments_record.id := 111; DELETE FROM Departments WHERE department_id = departments_record.id; END; 11.2.2 使用记录表类型 例11.2_1 SET SERVEROUT ON DECLARE TYPE student_tab_type IS TABLE OF Students%ROWTYPE INDEX BY BINARY_INTEGER; student_tab student_tab_type; v_id Students.student_id%TYPE; BEGIN v_id := &student_id; SELECT * INTO student_tab(999) FROM Students WHERE student_id = v_id; DBMS_OUTPUT.PUT_LINE ('学生姓名：'||student_tab(999).name); DBMS_OUTPUT.PUT_LINE ('学生性别：'||student_tab(999).sex); DBMS_OUTPUT.PUT_LINE ('出生日期：'||student_tab(999).dob); DBMS_OUTPUT.PUT_LINE ('专 业：'||student_tab(999).specialty); END; 例11.2_2 SET SERVEROUT ON DECLARE TYPE student_tab_type IS TABLE OF students%ROWTYPE INDEX BY BINARY_INTEGER; student_tab student_tab_type; v_specialty students.specialty%TYPE; CURSOR students_cur IS SELECT * FROM students WHERE specialty = v_specialty; i INT := 1; BEGIN v_specialty := '&specialty'; OPEN students_cur; DBMS_OUTPUT.PUT_LINE ('学生姓名 出生日期'); LOOP FETCH Students_cur INTO student_tab(i); EXIT WHEN Students_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE (student_tab(i).name||' '||student_tab(i).dob); i := i+1; END LOOP; CLOSE Students_cur; END; 例11.2_3 SET SERVEROUT ON DECLARE TYPE sname_tab_type IS TABLE OF Students.name%TYPE INDEX BY BINARY_INTEGER; sname_tab sname_tab_type; TYPE sdob_tab_type IS TABLE OF Students.dob%TYPE INDEX BY BINARY_INTEGER; sdob_tab sdob_tab_type; v_specialty Students.specialty%TYPE; CURSOR Students_cur IS SELECT name,dob FROM Students WHERE specialty = v_specialty; i INT:=1; BEGIN v_specialty := '&specialty'; OPEN Students_cur; DBMS_OUTPUT.PUT_LINE ('学生姓名 出生日期'); LOOP FETCH Students_cur INTO sname_tab(i),sdob_tab(i); EXIT WHEN Students_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE (sname_tab(i)||' '||sdob_tab(i)); i := i+1; END LOOP; CLOSE Students_cur; END; 11.3.2 使用联合数组 例11.3_1 SET SERVEROUT ON DECLARE TYPE sname_tab_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; sname_tab sname_tab_ty