1:介绍
sql是非过程语言,在编程中总是会用到过程化控制,PL/SQL就是提供了过程化控制的语言来弥补SQL语言的这一点缺陷。
PL/SQL 语句在后面的执行中 pl/sql语句由 PL/SQL引擎执行,SQL语句由 sql引擎执行。PL/SQL 集成了过程化与SQL一起使用, 提高了性能,因为一个过程执行完成后,
统一返回结果。减少了数据的传输。如下图所示:
2:PL/SQL块结构
PL/SQL块由四个部分组成;
1:DECLARE(optional)可有可无 用于生命 变量(variables) , 游标(cursors),用户自定义异常( user-defined exceptions)等。
2:BEGIN(mandatory) 必有:begin里面是 SQL 语句和PL/SQL语句
3:EXCEPTION (optional)可选择的,当出现错误的时候执行,
4:END; (mandatory)必有
函数和过程的区别是: function 需要有返回值, 而 procedure 没有返回值
PL/SQL的变量的规则查询官方文档
注意:非空变量和常量必须对其进行初始化,例如:
v_myname varchar2(20) := 'Hooo'
v_myname varchar2(20) default 'HOOO'
关于 %TYPE类型声明
%TYPE 用法:identifier table.column_name %TYPE
ex:
emp_lname employees.last_name%TYPE
也可以和声明的变量的名称相同:
balance NUMBER(7,2);
min_balance balance%TYPE := 1000;
注意: 关于oracle中的boolean: boolean 变量可以有三种类型: TRUE,FALSE,NULL
关于绑定变量(又叫session变量)
绑定变量:
和环境相关:
ex:
VARIABLE b_emp_salary NUMBER
BEGIN
select salary into :b_emp_salary
from employees where employee_id=178;
END;
PL/SQL支持嵌入块;
ex:
DECLARE
BEGIN
DECLARE
BEGIN
....................
END;
END;
3: Cursor 游标
游标是一个指针指向一个私有的内存区,私有内存区被 oracle 服务器分配。A cursors is a pointer used to fetch rows from a result set. One can think of a cursor as a data structure that describes the results returned from a SQL SELECT statement. One of the variables in this structure is a pointer to the next record to be fetched from the query results.
Note that if you do repetitive stuff inside a loop and you fail to close your cursors, you would soon run into theORA-01000: maximum number of open cursors exceeded error.
游标有两种类型: 隐式游标和显式游标 ,隐式游标: 有oracle服务器进行创建和管理,显式游标: 编程人员要进行声明和创建;游标的属性:
SQL%FOUND 至少一条记录在sql 语句的操作中受到影响
SQL%NOTFOUND 和%FOUND相反
SQL%ROWCOUNT
关于游标需要单独深入研究
4:书写控制结构
1:IF 控制
IF condition
THEN statements
ELSIF condition
THEN statements
ELSE
statements
END IF;
condition有三种类型 : TRUE ,FALSE,NULL
SQL> declare
2 v_myage number;
3 begin
4 IF v_myage < 11 THEN
5 DBMS_OUTPUT.PUT_LINE('I am a child !');
6 ELSE
7 DBMS_OUTPUT.PUT_LINE('I am not a a child');
8 END IF;
9 END;
10 /
PL/SQL 过程已成功完成。
SQL> set serveroutput on
SQL> /
I am not a a child
PL/SQL 过程已成功完成。
初始化时为null:
SQL> declare
2 v_myage number;
3 begin
4 IF v_myage IS NULL THEN
5 DBMS_OUTPUT.PUT_LINE('I am a child !');
6 ELSE
7 DBMS_OUTPUT.PUT_LINE('I am not a a child');
8 END IF;
9 END;
10 /
I am a child !
PL/SQL 过程已成功完成。
2:CASE
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
.........
WHEN expressionN THEN resultN
[ELSE resultN+1]
END;
简单的一个例子:
SQL> SET VERIFY OFF
SQL> DECLARE
2 v_grade CHAR(1) := UPPER('&grade');
3 v_appraisal VARCHAR2(20);
4
5 BEGIN
6 v_appraisal := CASE v_grade
7 WHEN 'A' THEN 'Excellent'
8 WHEN 'B' THEN 'Very Good'
9 WHEN 'C' THEN 'Good'
10 ELSE 'NO such grde'
11 END;
12 DBMS_OUTPUT.PUT_LINE('Grade: '|| v_grade || ' Apprasisal ' || v_appraisal)
;
13 END;
14 /
输入 grade 的值: A
Grade: A Apprasisal Excellent
PL/SQL 过程已成功完成。
第二种 CASE 同样的例子比较一下,第二种CASE没有选择符 WHEN后面直接加判断即可
有时候需要判断多个字段时候,比较灵活。
SQL> DECLARE
2 v_grade CHAR(1) := UPPER('&grade');
3 v_appraisal VARCHAR2(20);
4
5 BEGIN
6 v_appraisal := CASE
7 WHEN v_grade = 'A' THEN 'Excellent'
8 WHEN v_grade IN ('B','C') THEN 'Very Good'
9
10 ELSE 'NO such grde'
11 END;
12 DBMS_OUTPUT.PUT_LINE('Grade: '|| v_grade || ' Apprasisal ' || v_appraisal)
;
13 END;
14 /
输入 grade 的值: B
Grade: B Apprasisal Very Good
PL/SQL 过程已成功完成。
以上是CASE的表达式的介绍
下面介绍CASE 语句;
CASE
END CASE;
在CASE语句中 要注意用END CASE结尾;
CASE表达式和CASE语句的区别: 表达式可以赋值
CASE语句是一个语句,不能再赋值给其他结果了,CASE语句需要用 END CASE结尾;
或者 if a or b
应该把比较好运算的,耗内存比较少的,放在a位置,
因为 a先计算,对于 and来说 a为false 后面的b就不用计算了,
节省了指令和内存。
同or a为true时一样。
if condition
then
else
then null;
null的另一个用法:
DECLARE
.....
BEGIN
IF condition then goto lastpoint END IF;
.......
<<lastpoint>> --这个标签后必须有语句,所以 可以用null来表示。
null;
END;
3:循环控制
BASIC LOOP
FOR LOOP
WHILE LOOP
LOOP
statement1;
EXIT [WHEN condition];
END LOOP
EXIT相当于 其他语言的break;
WHILE condition LOOP
statement1;
statement2;
......
END LOOP;
FOR counter IN [REVERSE]--REVERSE代表倒过来
lover_bound...upper_bound LOOP
statement1;
statement2;
............
END LOOP;
FOR循环的步长必须是1。
11g版本 增加了continue关键字,这个和java中的continue用法一致。
continue 和 exit的语法一致。
三种方式。
exit ;
exit when condition;
exit 标签 when condition; 退出到标签为位置。
SQL> DECLARE
2 v_total NUMBER :=0;
3 BEGIN
4 <<BeforeTopLoop>>
5 FOR i IN 1..10 LOOP
6 v_total := v_total+1;
7 DBMS_OUTPUT.PUT_LINE('Total is:' || v_total);
8 FOR j in 1..10 LOOP
9 CONTINUE BeforeTopLoop WHEN i+j > 5;
10 v_total := v_total +1;
11 END LOOP;
12 END LOOP;
13 END;
14 /
Total is:1
Total is:6
Total is:10
Total is:13
Total is:15
Total is:16
Total is:17
Total is:18
Total is:19
Total is:20
PL/SQL 过程已成功完成。
4:GOTO 语句:
语法: GOTO label_name;SQL> BEGIN
2 GOTO second_output;
3 DBMS_OUTPUT.PUT_LINE('This line will never execute.');
4 <<second_output>>
5 DBMS_OUTPUT.PUT_LINE('We are here!');
6 END;
7 /
We are here!
PL/SQL 过程已成功完成。
以上语句第一个输出永远不被执行。
5:复合数据类型
记录中存储不同的数据类型,而集合中存储相同的数据类型。
记录相当于java语言中的实体类.
集合相当于数组,java的集合的底层都是采用数组实现的。
1:创建PL/SQL Record 语法:
记录声明;
TYPE type_name IS RECORD
(field_declaration[,field_declaration]......);
identifier type_name;
域的声明
field_declaration;
field_name { field_type|variable%TYPE| table.column%TYPE|table%ROWTYPE}
[[NOT NULL]{:= | DEFAULT} expr]
}
2:%ROWTYPE类型
SQL> DECLARE
2 person employees%ROWTYPE;
3 BEGIN
4 select * INTO person FROM employees WHERE employee_id = 100;
5 DBMS_OUTPUT.PUT_LINE('Name: '||person.first_name);
6 END;
7 /
Name: Steven
PL/SQL 过程已成功完成。
%TYPE %ROWTYPE 都是采用 锚锁定技术,
锚引用是在编译期间解析,如果数据变量类型或者表结构列类型以及列数
发生改变, 含有 %TYPE %ROWTYPE 的代码要重新进行编译。
关于锚声明的一些了解:
相当于 A---->B A引用B B被改变了,要进行重新编译,保持同步。
使用 %ROWTYPE
首先创建表:
SQL> create table retired_emps(empno number(6),ename varchar(25),job varchar(10)
,mgr number(6) , hiredate date,leavedate date,sal number(8,2),comm number(2,2),d
eptno number(4));
表已创建。
SQL> select * from retired_emps;
未选定行
SQL> DECLARE
2 v_employee_number number :=124;
3 v_emp_rec retired_emps%ROWTYPE;
4
5 BEGIN
6 SELECT employee_id,last_name,job_id,manager_id,
7 hire_date,sysdate,salary,commission_pct,department_id INTO
8 v_emp_rec FROM employees WHERE employee_id = v_employee_number;
9 INSERT INTO retired_emps VALUES v_emp_rec;
10 END;
11 /
PL/SQL 过程已成功完成。
SQL> select * from retired_emps;
EMPNO ENAME JOB MGR HIREDATE
---------- ------------------------- ---------- ---------- --------------
LEAVEDATE SAL COMM DEPTNO
-------------- ---------- ---------- ----------
124 Mourgos ST_MAN 100 16-11月-99
08-6月 -13 5800 50
更新的时候也可以使用 都是一样
这个例子说明了 通过一个表的列声明 ROWTYPE
通过显式游标声明,
通过 TYPE 直接声明三种方式 。
create table cust_sales_roundup(
customer_id NUMBER(5),
customer_name VARCHAR2(100),
total_sales NUMBER(15,2)
);
SQL> DECLARE
2 cust_sales_roundup_rec cust_sales_roundup%ROWTYPE;
3 CURSOR cust_sales_cur is SELECT * FROM cust_sales_roundup;
4 cust_sales_rec cust_sales_cur % ROWTYPE;
5
6 TYPE customer_sales_rectype is RECORD
7 (
8 customer_id NUMBER(5),
9 customer_name cust_sales_roundup.customer_name%TYPE,
10 total_sales NUMBER(15,2)
11 );
12
13 prefererred_cust_rec customer_sales_rectype;
14
15 BEGIN
16 --Assign one recored to another
17 cust_sales_roundup_rec := cust_sales_rec;
18 prefererred_cust_rec := cust_sales_rec;
19 END;
20 /
PL/SQL 过程已成功完成。
3:关联数组
Key Values
values:是一个scalar标量 或者 record
关联数组的顺序:
如何定义关联数组:
TYPE type_name IS TABLE OF
{ column_type | variable%TYPE
|table.column%TYPE} [NOT NULL]
|INDEX BY PLS_INTEGER | BINARY_INTEGER
|VARCHAR2(<size>);
}
identifier type_name;
1:ex: 一个很综合的例子: 说明:
happyfamily.FIRST 第一个索引
happyfamily.NEXT 下一个索引
happyfamily.EXISTS(key) 判断key是否存在。
SQL> DECLARE
2 TYPE list_of_names_t IS TABLE OF employees.first_name%TYPE
3 INDEX BY PLS_INTEGER;
4 happyfamily list_of_names_t;
5 l_row PLS_INTEGER;
6
7 BEGIN
8 happyfamily(2020202) := 'topwqp';
9 happyfamily(-15070) := 'Steven';
10 happyfamily(-90900) :='Chris';
11 happyfamily(88) := 'Veva';
12
13 l_row := happyfamily.FIRST;
14 WHILE(l_row IS NOT NULL)
15 LOOP
16 DBMS_OUTPUT.PUT_LINE(l_row || '-->' || happyfamily(l_row));
17 l_row := happyfamily.NEXT(l_row);
18 END LOOP;
19
20 l_row := 88;
21
22 IF happyfamily.EXISTS(l_row) THEN
23 DBMS_OUTPUT.PUT_LINE('It is here!---->' || happyfamily(l_row));
24 ELSE
25 DBMS_OUTPUT.PUT_LINE('It is not here !---->'|| happyfamily(l_row))
;
26 END IF;
27 END;
28 /
-90900-->Chris
-15070-->Steven
88-->Veva
2020202-->topwqp
It is here!---->Veva
PL/SQL 过程已成功完成。
关联数组相关的方法:
EXISTS PRIOR COUNT NEXT FIRST DELETE LAST
这些方法需要自己查PL/SQL推荐的书籍学习。
2: ex2:关联数组的使用:这也是一个很好的例子:
SQL> DECLARE
2 TYPE emp_table_type IS TABLE OF
3 employees%ROWTYPE INDEX BY PLS_INTEGER;
4 my_emp_table emp_table_type;
5 max_count NUMBER(3) := 104;
6 BEGIN
7 FOR i IN 100..max_count
8 LOOP
9 SELECT * INTO my_emp_table(i) FROM employees
10 WHERE employee_id = i;
11 END LOOP;
12
13 FOR i IN my_emp_table.FIRST..my_emp_table.LAST
14 LOOP
15 DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
16 END LOOP;
17 END;
18 /
King
Kochhar
De Haan
Hunold
Ernst
PL/SQL 过程已成功完成。
3:关于key为string的关联数组的使用:
SQL> DECLARE
2 SUBTYPE location_t IS VARCHAR2(64);
3 TYPE population_type IS TABLE OF NUMBER INDEX BY location_t;
4
5 l_country_population population_type;
6 l_count PLS_INTEGER;
7 l_location location_t;
8 BEGIN
9 l_country_population('Greeland') := 100000;
10 l_country_population('USA') := 3000000000;
11 l_country_population('Iceland') := 750000;
12 l_country_population('Australia') := 230000000;
13 l_country_population('usa') := 40000000;
14
15 l_count := l_country_population.COUNT;
16 DBMS_OUTPUT.PUT_LINE('COUNT='||l_count);
17
18 l_location := l_country_population.FIRST;
19 DBMS_OUTPUT.PUT_LINE('First Row ='||l_location);
20 DBMS_OUTPUT.PUT_LINE('First Value='||l_country_population(l_location)
);
21
22 l_location := l_country_population.LAST;
23 DBMS_OUTPUT.PUT_LINE('LAST Row ='||l_location);
24 DBMS_OUTPUT.PUT_LINE('LAST Value='||l_country_population(l_location))
;
25 END;
26 /
COUNT=5
First Row =Australia
First Value=230000000
LAST Row =usa
LAST Value=40000000
PL/SQL 过程已成功完成。
4:嵌套表
5:显式游标
CURSOR cursor_name is select statement;
SQL> DECLARE
2 CURSOR c_emp_cursor IS
3 SELECT employee_id, last_name FROM employees
4 WHERE department_id =30;
5 v_emp_record c_emp_cursor%ROWTYPE;
6 BEGIN
7 OPEN c_emp_cursor;
8 LOOP
9 FETCH c_emp_cursor INTO v_emp_record;
10 EXIT WHEN c_emp_cursor%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id ||' ' ||v_emp_record.la
st_name);
12 END LOOP;
13 CLOSE c_emp_cursor;
14 END;
15 /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
PL/SQL 过程已成功完成。
另一种变种:很简单的形式:
SQL> BEGIN
2 FOR i IN (SELECT employee_id,last_name FROM employees WHERE department_i
d =30 )
3 LOOP
4 DBMS_OUTPUT.PUT_LINE(i.employee_id ||'---->'||i.last_name);
5 END LOOP;
6 END;
7 /
114---->Raphaely
115---->Khoo
116---->Baida
117---->Tobias
118---->Himuro
119---->Colmenares
PL/SQL 过程已成功完成。
游标的属性:
%ISOPEN
%NOTFOUND
%FOUND
%ROWCOUNT 不是固定值,随着fetch的次数的增加而增加;
通过这个可以写如下语句判断fetch的装载次数:
EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR c_emp_cursor %NOTFOUND;
6:带参数的游标:
SQL> DECLARE
2 TYPE emp_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
3 l_emp emp_type;
4 l_row PLS_INTEGER;
5 BEGIN
6 SELECT * BULK COLLECT INTO l_EMP FROM employees;
7 DBMS_OUTPUT.PUT_LINE('The count is: '|| l_emp.COUNT);
8 l_row := l_emp.FIRST;
9 WHILE(l_row IS NOT NULL)
10 LOOP
11 DBMS_OUTPUT.PUT_LINE(l_row || ':'||l_emp(l_row).employee_id||'---->'|
|l_emp(l_row).first_name);
12 l_row:=l_emp.NEXT(l_row);
13 END LOOP;
14 END;
15 /
The count is: 107
1:198---->Donald
2:199---->Douglas
3:200---->Jennifer
4:201---->Michael
5:202---->Pat
6:203---->Susan
7:204---->Hermann
8:205---->Shelley
9:206---->William
10:100---->Steven
11:101---->Neena
12:102---->Lex
13:103---->Alexander
14:104---->Bruce
15:105---->David
16:106---->Valli
17:107---->Diana
18:108---->Nancy
19:109---->Daniel
20:110---->John
21:111---->Ismael
22:112---->Jose Manuel
23:113---->Luis
24:114---->Den
25:115---->Alexander
26:116---->Shelli
27:117---->Sigal
28:118---->Guy
29:119---->Karen
30:120---->Matthew
31:121---->Adam
32:122---->Payam
33:123---->Shanta
34:124---->Kevin
35:125---->Julia
36:126---->Irene
37:127---->James
38:128---->Steven
39:129---->Laura
40:130---->Mozhe
41:131---->James
42:132---->TJ
43:133---->Jason
44:134---->Michael
45:135---->Ki
46:136---->Hazel
47:137---->Renske
48:138---->Stephen
49:139---->John
50:140---->Joshua
51:141---->Trenna
52:142---->Curtis
53:143---->Randall
54:144---->Peter
55:145---->John
56:146---->Karen
57:147---->Alberto
58:148---->Gerald
59:149---->Eleni
60:150---->Peter
61:151---->David
62:152---->Peter
63:153---->Christopher
64:154---->Nanette
65:155---->Oliver
66:156---->Janette
67:157---->Patrick
68:158---->Allan
69:159---->Lindsey
70:160---->Louise
71:161---->Sarath
72:162---->Clara
73:163---->Danielle
74:164---->Mattea
75:165---->David
76:166---->Sundar
77:167---->Amit
78:168---->Lisa
79:169---->Harrison
80:170---->Tayler
81:171---->William
82:172---->Elizabeth
83:173---->Sundita
84:174---->Ellen
85:175---->Alyssa
86:176---->Jonathon
87:177---->Jack
88:178---->Kimberely
89:179---->Charles
90:180---->Winston
91:181---->Jean
92:182---->Martha
93:183---->Girard
94:184---->Nandita
95:185---->Alexis
96:186---->Julia
97:187---->Anthony
98:188---->Kelly
99:189---->Jennifer
100:190---->Timothy
101:191---->Randall
102:192---->Sarah
103:193---->Britney
104:194---->Samuel
105:195---->Vance
106:196---->Alana
107:197---->Kevin
PL/SQL 过程已成功完成。
例子2: 用游标
DECLARE
CURSOR ee IS SELECT * FROM employees;
TYPE emp_type IS TABLE OF ee%ROWTYPE INDEX BY PLS_INTEGER;
l_emp emp_type;
l_row PLS_INTEGER;
BEGIN
OPEN ee;
FETCH ee BULK COLLECT INTO l_emp;
CLOSE ee;
DBMS_OUTPUT.PUT_LINE('The count is: '|| l_emp.COUNT);
l_row := l_emp.FIRST;
WHILE(l_row IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE(l_row || ':'||l_emp(l_row).employee_id||'---->'||l_emp(l_row).first_name);
l_row:=l_emp.NEXT(l_row);
END LOOP;
END;