自学PL/SQL 第三讲Interacting with the Oracle Server

曾经终败给现在 2022-02-02 02:36 74阅读 0赞

本讲课程目标  
1.在PL/SQL块中执行select语句  
2.在PL/SQL块中执行DML语句和控制事务  
3.使用隐式游标定义PL/SQL块中DML语句的输出

在PL/SQL块中"END；"表示结束PL/SQL块，但并没有结束事务，一个块可以跨越多个事务，一个事务也可以跨越多个块；PL/SQL块中不支持DDL和DCL语句，例如建表，赋予用户权限等.

一：在PL/SQL块中执行select语句  
select语句在PL/SQL块中的语法结构如下

SELECT select\_list  
INTO \{variable\_name\[, variable\_name\]...  
| record\_name\}  
FROM table  
\[WHERE condition\];  
范例：

1.  SQL**>** set serveroutput on 
2.  SQL**>** DECLARE 
3.   2 v\_first\_name hr.employees.first\_name%TYPE; 
4.   3 v\_last\_name hr.employees.last\_name%TYPE; 
5.   4 BEGIN 
6.   5 SELECT first\_name,last\_name INTO 
7.   6 v\_first\_name,v\_last\_name FROM hr.employees 
8.   7 WHERE employee\_id=100; 
9.   8 DBMS\_OUTPUT.PUT\_LINE(v\_first\_name ||' '||v\_last\_name); 
10.  9\* END; 
11. 
12. SQL**>** / 
13. Steven King 
14. PL/SQL procedure successfully completed

二.在PL/SQL块中执行DML语句和控制事务  
可以在PL/SQL块中使用SQL的函数，自定义的序列号，字段的默认值插入数据

1.  SQL**>** create table t14703a (id number(5),constraint pk\_t14703a primary key(id), 
2.   2 location varchar2(30) default 'fuzhou',salary number(8,2)); 
3.  Table created. 
4.  
5.  SQL**>** create sequence user\_seq 
6.   2 increment by 1 
7.   3 start with 1 
8.   4 maxvalue 99999 
9.   5 nocache 
10.  6\* nocycle 
11. Sequence created. 
12. 
13. SQL**>** select user\_seq.nextval from dual; 
14. 
15.  NEXTVAL 
16. \---------- 
17.  1 
18. 
19. SQL**>** BEGIN 
20.  2 INSERT INTO hr.t14703a 
21.  3 (id,location,salary) VALUES 
22.  4 (user\_seq.nextval,default,'6000'); 
23.  5 COMMIT; 
24.  6\* END; 
25. SQL**>** / 
26. PL/SQL procedure successfully completed. 
27. 
28. SQL**>** select \* from hr.t14703a; 
29. 
30.  ID LOCATION SALARY 
31. \---------- ---------- ---------- 
32.  3 fuzhou 6000 
33. 
34. 
35. SQL**>** DECLARE 
36.  2 v\_salary\_increase hr.t14703a.salary%TYPE := 500; 
37.  3 BEGIN 
38.  4 UPDATE hr.t14703a SET salarysalary= salary + v\_salary\_increase 
39.  5 WHERE id=3; 
40.  6\* END; 
41.  7 / 
42. PL/SQL procedure successfully completed. 
43. 
44. SQL**>** select \* from hr.t14703a; 
45. 
46.  ID LOCATION SALARY 
47. \---------- ---------- ---------- 
48.  3 fuzhou 6500

三.使用隐式游标定义PL/SQL块中DML语句的输出  
SQL游标，游标是私有的SQL工作区，有隐式和显式游标两个类型；oracle server使用隐式的游标来执行SQL语句  
A cursor is a private SQL work area.  
There are two types of cursors:  
– Implicit cursors  
– Explicit cursors  
The Oracle server uses implicit cursors to parse and execute your SQL statements.  
Explicit cursors are explicitly declared by the programmer.

4种游标的属性SQL%COUNT,SQL%FOUND,SQL%NOTFOUND,SQL%ISOPEN

[![154026101.jpg][]][154026101.jpg]

1.  SQL**>** VAR rownums varchar2(30) 
2.  SQL**>** set serveroutput on 
3.  SQL**>** DECLARE 
4.   2 v\_a hr.t04310\_a.a%TYPE := 50000; 
5.   3 BEGIN 
6.   4 delete from hr.t04310\_a where a **<****v\_a**; 
7.   5 :rownums := (SQL%ROWCOUNT ||' ' || 'row deleted'); 
8.   6 DBMS\_OUTPUT.PUT\_LINE(:rownums); 
9.   7\* END; 
10.  8 / 
11. 40000 row deleted 
12. PL/SQL procedure successfully completed.

本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/630144如需转载请自行联系原作者

ylw6006

[154026101.jpg]: /images/20220202/0db226aaecc24883b5486e5a36bb04ba.png