PL/SQL学习汇总
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;
3:FOR循环;
- FOR counter IN [REVERSE]–REVERSE代表倒过来
- lover_bound…upper_bound LOOP
- statement1;
- statement2;
- …………
- END LOOP;
- <span style=”color:#cc0000;”>FOR循环的步长必须是1。</span>
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;