PLSQL_（2）执行SQL语句

一、执行SELECT语句
在PL/SQL程序中，使用SELECT INTO语句查询一条记录的信息。
语法格式：

SELECT expression_list INTO variable_list | record_ variable 
  FROM table_name     
  WHERE condition;

 --expression_list指定选择的列或表达式；variable_list指定接收查询结果的标量变量名
--record_variable用于指定接收查询结果的记录变量名，接收查询结果可以使用标量变量也可以
--使用记录变量，当使用标量变量时，变量的个数、顺序应该与查询的目标数据相匹配。
--注意：在PL/SQL块中直接使用SELECT INTO语句时，该语句只能返回一行数据，如果SELECT语句
--返回多行数据，会产生TOO_MANY_ROW异常；如果没有返回数据，则会产生NO_DATA_FOUND异常。

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;
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;
DECLARE
 v_student students%ROWTYPE;
BEGIN
 SELECT * INTO v_student
  FROM students
  WHERE name LIKE '王%';   --产生TOO_MANY_ROW异常
 DBMS_OUTPUT.PUT_LINE('姓名 性别 出生日期');
 DBMS_OUTPUT.PUT_LINE(v_student.name || v_student.sex || v_student.dob);
END;
DECLARE
 v_student students%ROWTYPE;
BEGIN
 SELECT * INTO v_student
  FROM students
  WHERE dob = '31-12月-2015';   --产生NO_DATA_FOUND异常
 DBMS_OUTPUT.PUT_LINE('姓名 性别 出生日期');
 DBMS_OUTPUT.PUT_LINE(v_student.name || v_student.sex || v_student.dob);
END;

 

 

 

二、执行DML语句
1.执行INSERT语句
语法格式：

 INSERT INTO table_name [(col1, col2, ..., coln)] --插入一条记录
  VALUES(val1,val2,...valn);
 INSERT INTO table_name [(col1, col2,..., coln)]
  AS SubQuery;
BEGIN
 INSERT INTO students
  VALUES(10188, NULL, '王一', '女', '07-5月-1988', '计算机');
END;
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;
BEGIN
 INSERT INTO students_computer  --没AS的吗？
  (SELECT * FROM students WHERE specialty = '计算机');
END;

 

 

2.执行UPDATE语句
语法格式：

UPDATE table_name SET col1 = val1 [, col2 =val2, ..., coln = valn]
  [WHERE condition(s)];
BEGIN
 UPDATE students
  SET student_id = 10288,m
   dob = '07-5月-1988',
   specialty = '自动化'
  WHERE student_id = 10188;
END;  

 

 

3.执行DELETE语句
语法格式：

delete from table_name [where condition(s)];
begin
 delete from students
  where student_id = 10188;
end;
declare
 v_specialty students.specialty%Type := '计算机';
begin
 delete from students
  where specialty = v_specialty;
end;

 

 

三、执行事务处理语句
 在pl/sql程序中，可以使用DML语句。这些DML语句构成了Oracle数据库事务。
与sql操作中可以使用commit、rollback、savepoint等语句处理事务一样，在pl/sql
程序中，同样可以使用上述事务处理语句处理Oracle数据库事务。

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月-1998';
      specialty = '自动化'
  where student_id = 10101;
 savepoint spl;
 delete from students
  where student_id = 10101;
 savepoint sp2;
 rollback to sp1;
 commit;
end;