一、PL/SQL代码基本结构
Header(块头单元)
只有命名代码块(例如存储过程,函数)才有,这个单元确定了这个命名块或者程序的调用方式。
Declaration Section (声明单元,可选)
声明变量、常量、用户自定义的数据类型以及游标等。
Execution Section(执行单元)
主程序体,包含何种合法的PL/SQL语句。
Exception Section(异常处理单元,可选)
异常处理程序,当主程序体中代码出现异常时执行这一部分的代码。
二、基本流程控制结构
IF条件判断
IF condition_1 THEN
statements_1
ELSIF condition_2 THEN
statements_2
......
[ELSIF condition_n THEN
statements_n]
[ELSE
else_statements]
END IF;
CASE表达式匹配
CASE expression
WHEN result_1 THEN
statements_1
WHEN result_2 THEN
statements_2
......
[WHEN result_n THEN
statements_n]
[ELSE
else_statements]
END CASE;
LOOP循环
LOOP
statements
[IF exit_condition THEN
EXIT;]
END LOOP;
或者
LOOP
statements
[EXIT WHEN exit_condition]
END LOOP;
WHILE循环
WHILE loop_condition LOOP
statements
[IF exit_condition THEN
EXIT;
END IF;]
[EXIT WHEN exit_condition]
END LOOP;
数值FOR循环
FOR index IN [REVERSE] lowest_num .. highest_num
LOOP
statements
[IF exit_condition THEN
EXIT;
END IF;]
[EXIT WHEN exit_condition]
END LOOP;
例:
BEGIN
--正向循环
--i从2到10循环执行循环体(自动步进固定为1)
--输出为2345678910
FOR i IN 2 .. 10 LOOP
DBMS_OUTPUT.put(i);
END LOOP;
DBMS_OUTPUT.new_line();
--反向循环
--i从10到2循环执行循环体(自动步进固定为-1)
--与正向循环一样,起始数和结束数必须小的在前
--输出为1098765432
FOR I IN REVERSE 2 .. 10 LOOP
DBMS_OUTPUT.put(I);
END LOOP;
DBMS_OUTPUT.new_line();
END;
游标FOR循环
--cursor_name为游标的名字
--explicit_SELECT_statement为合法的SELECT语句
FOR record IN {cursor_name | explicit_SELECT_statement} LOOP
statements
[IF exit_condition THEN
EXIT;
END IF;]
[EXIT WHEN exit_condition]
END LOOP;
例:
--测试表与测试数据
CREATE TABLE for_test (
col NVARCHAR2(10)
);
INSERT INTO for_test values('1');
INSERT INTO for_test values('2');
INSERT INTO for_test values('3');
DECLARE
CURSOR test_cursor IS
SELECT * FROM for_test;
BEGIN
--遍历游标
--输出123
FOR record IN test_cursor LOOP
DBMS_OUTPUT.put(record.col);
END LOOP;
DBMS_OUTPUT.new_line();
--遍历SELECT语句的结果集
--输出123
FOR record IN (SELECT * FROM for_test) LOOP
DBMS_OUTPUT.put(record.col);
END LOOP;
DBMS_OUTPUT.new_line();
END;
三、存储过程
存储过程(Stored Procedure)是一组为了完成特定功能的PL/SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。
CREATE [OR REPLACE] PROCEDURE sp_name[(param1 [IN|OUT|IN OUT] TYPE1[, param2 [IN|OUT|INOUT] TYPE2...])] IS
[declarations]
BEGIN
statements
[EXCEPTION
statements]
END [sp_name];
例:
创建存储过程:
--计算base的平方并保存在result里
CREATE OR REPLACE PROCEDURE square(base IN NUMBER, result OUT NUMBER) IS
BEGIN
result := base*base;
END square;
调用存储过程:
--调用存储过程
--输出9
DECLARE
result NUMBER;
BEGIN
square(3, result);
DBMS_OUTPUT.PUT_LINE(result);
END;
删除存储过程:
--删除存储过程square
DROP PROCEDURE square;
四、函数
函数(Function)与存储过程类似,也是一组为了完成特定功能的PL/SQL语句集,但是函数是通过RETURN语句而不是通过OUT或者IN OUT参数返回数据。
CREATE [OR REPLACE] FUNCTION func_name[(param1 TYPE1[, param2 TYPE2...])] IS
RETURN return_type
[declarations]
BEGIN
statements
RETURN return_value
[statements]
[RETURN return_value]
[EXCEPTION
statements]
END [func_name];
例:
创建函数:
--计算并返回base的平方
CREATE OR REPLACE FUNCTION square(base NUMBER) RETURN NUMBER IS
BEGIN
RETURN(base * base);
END square;
调用函数:
--调用函数
--输出9
DECLARE
result NUMBER;
BEGIN
result:=square(3);
DBMS_OUTPUT.PUT_LINE(result);
END;
删除函数:
DROP FUNCTION square;
五、游标
游标(cursor)是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录,并赋给主变量,交由主语言进一步处理。
1. 显示游标
基本使用语法
DECLARE
var_1 type_1;
[var_2 type_2;
...
var_n type_n;]
CURSOR cursor_name IS explicit_SELECT_statement
BEGIN
OPEN TEST_CSR;
FETCH cursor_name INTO var_1[, var_2, ..., var_n];
[statements]
CLOSE TEST_CSR;
END;
例:
DROP TABLE for_test;
--测试表与测试数据
CREATE TABLE for_test (
col_1 NUMBER,
col_2 NVARCHAR2(10)
);
INSERT INTO for_test values(1, 'a');
INSERT INTO for_test values(5, 'e');
INSERT INTO for_test values(2, 'b');
INSERT INTO for_test values(4, 'd');
INSERT INTO for_test values(3, 'c');
--输出1:a,5:e,2:b,4:d,3:c,3:c,1:a,5:e,2:b,4:d,3:c,3:c,
DECLARE
var_1 NUMBER;
var_2 NVARCHAR2(10);
var_row for_test%ROWTYPE;
CURSOR my_cursor IS SELECT * FROM for_test;
BEGIN
OPEN my_cursor;
--处理第一行,把数据存到与每一列相对应的变量里
FETCH my_cursor INTO var_1, var_2;
DBMS_OUTPUT.PUT(var_1||':'||var_2||',');
--把数据存到一个代表一行数据的变量里
WHILE my_cursor%FOUND LOOP
FETCH my_cursor INTO var_row;
DBMS_OUTPUT.PUT(var_row.col_1||':'||var_row.col_2||',');
END LOOP;
DBMS_OUTPUT.NEW_LINE();
CLOSE my_cursor;
END;
2. 隐式游标
每一次执行一个SQL DML语句(包括INSERT、UPDATE、MERGE或者DELETE),或者SELECT INTO语句(把数据库的一行返回到一个PL/SQL数据结构中),PL/SQL都会自动声明和使用一个隐式的游标。
DECLARE
variable_definitions;
BEGIN
SELECT column_list INTO variable_list [rest_SELECT_statement]
[statements]
END;
例:
DECLARE
var_1 NUMBER;
var_2 NVARCHAR2(10);
BEGIN
SELECT * INTO var_1, var_2 FROM for_test WHERE col_1=1;
DBMS_OUTPUT.put_line(var_2);
END;
3. 游标属性
%FOUND
若当前游标指向有效的一行,返回TRUE,否则返回FALSE。当游标刚打开还没有FETCH数据时,这个属性会返回FALSE,因此应该在打开游标后先FETCH第一条数据,然后再根据这个属性进行循环获取数据。
%NOTFOUND
与%FOUND相反。
%ISOPEN
如果游标已经打开则返回TRUE,否则返回FALSE。
%ROWCOUNT
返回到目前为止,已经从游标中取出的记录数量。
4. 游标变量
与静态(显式与隐式)游标相比,游标变量不同于特定的查询绑定,而是在打开游标时才能确定所对应的查询,因此游标变量可以依次对应多个查询。
--测试表与测试数据
CREATE TABLE for_test (
col_1 NUMBER,
col_2 NVARCHAR2(10)
);
INSERT INTO for_test values(1, 'a');
INSERT INTO for_test values(5, 'e');
INSERT INTO for_test values(2, 'b');
INSERT INTO for_test values(4, 'd');
INSERT INTO for_test values(3, 'c');
--打印出给定table的数据
--table_name为表名(输入)
CREATE OR REPLACE PROCEDURE my_sp(table_name VARCHAR2) IS
TYPE CSR_REF IS REF CURSOR;
test_cursor CSR_REF;
var_1 NUMBER;
var_2 NVARCHAR2(10);
BEGIN
OPEN test_cursor FOR
'SELECT * FROM '||table_name;
FETCH test_cursor
INTO var_1, var_2;
DBMS_OUTPUT.PUT(var_1||','||var_2||',');
WHILE test_cursor%FOUND LOOP
FETCH test_cursor
INTO var_1, var_2;
DBMS_OUTPUT.PUT(var_1||','||var_2||',');
END LOOP;
CLOSE test_cursor;
DBMS_OUTPUT.NEW_LINE;
END ;
调用
BEGIN
--输出1,a,5,e,2,b,4,d,3,c,3,c,
my_sp('for_test');
END;