SQL语句PLSQL编程基础.docVIP

SQL语句PLSQL编程基础 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