关于存储过程编写的一点总结(转)
一. 数据类型和相关函数
1. 数字
PLS_INTEGER: -2147483647 ~ 2147483647
NUMBER:十进制或者整数
相关函数:
ABS(n) 绝对值
CEIL(n) 向上取整
FLOOR(n) 向下取整
ROUND(m[,n]) 四舍五入
TRUNC(m[,n]) 截取
GREATEST(n[,n…]) 最大值
LEAST(n[,n…]) 最小值
MOD(m,n) 余数
SIGN(n) 符号
2. 字符
CHAR:定长字符
VARCHAR2:变长字符
相关函数:
INITCAP / LOWER / UPPER(c) 大小写格式化
INSTR / INSTRB(c1,c2[,n[,m]]) 搜索指定字符/字节的位置索引
LENGTH / LENGTHB(c) 字符/字节数
TRIM([LEADING | TRAILING | BOTH][c2 FROM] c1) 从左/右/全部截掉指定字符
SUBSTR(c,m[,n]) 截取
REPLACE(c1, c2[,c3]) 替代
3. 日期
DATE
相关函数
ADD_MONTHS(d,n) 增加月份
LAST_DAY(d) 当月最后一天
MONTHS_BETWEEN(d1,d2) 两个日期之间的月数
其它函数:
TO_CHAR(d | n[,fmt]) / TO_NUMBER(c[,fmt]) / TO_DATE(c[,fmt]) 转换函数
DECODE(expr, case, result[, case, result…][, default]) 嵌入式IF条件判断函数
二. 匿名块和存储过程结构
存储过程应该是一组经过压缩处理的经常使用的一组命令，如交叉表的查询、更新和插入操作。存储过程允许程序员简单地将该过程作为一个函数来调用，而不是重复地执行过程内部的语句。而且存储过程还有些附加的优点。
当存储过程执行时，SQL 语句将在服务器中继续运行，一些数据信息将会返回给用户的电脑直至过程执行完毕。这会极大地提高性能并带来了附加的好处。存储过程在第一次执行时，在数据库经过了编译操作，编译的映象将存储在服务器的过程中。因此你不必在每一次执行它的时候都对它进行优化，这也使性能得到了提高。
匿名块就是将头部分改成DECLARE的存储过程.
语法格式:
create procedure procedure_name
[[()@parameter_name
datatype [(length) | (precision [, scale])
[= default][output]
[, @parameter_name
datatype [(length) | (precision [, scale])
[= default][output]]...[)]]
[with recompile]
as SQL_statements
运行存贮过程的EXECUTE 命令的语法如下:
execute [@return_status = ]
procedure_name
[[@parameter_name =] value |
[@parameter_name =] @variable [output]...]]
[with recompile]
创建存储过程的例子
create procedure Match_Names_To_Media @description char(30)
as
select ARTISTS.name from ARTISTS, MEDIA, RECORDINGS
where MEDIA.description = @description and
MEDIA.media_type = RECORDINGS.media_type and
RECORDINGS.artist_id = ARTISTS.artist_id
执行存储过程的例子:
execute Match_Names_To_Media "CD"
存储过程概述
存储过程(Stored Procedure) 是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中.用户通过指定存储过程的名字,并给出参数(如果该存储过程带有参数)来执行它.
存贮过程是一个专业数据库编程人员必须掌握的概念.存贮过程可以在最大程度上发挥出SQL 的潜能.该功能可以被如java等编程语言,像执行自己的函数一样地调用或执行.
存储过程的优点:
A 存储过程允许标准组件式编程
存储过程在被创建以后可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句.而且数据库专业人员可随时对存储过程进行修改,但对应用程序源代码毫无影响.因为应用程序源代码只包含存储过程的调用语句,从而极大地提高了程序的可移植性.
B 存储过程能够实现较快的执行速度
如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多.因为存储过程是预编译的,在首次运行一个存储过程时查询优化器对其进行分析优化,并给出最终被存在系统表中的执行计划.
C 存储过程能够减少网络流量
对于同一个针对数据数据库对象的操作,如查询,修改,如果这一操作所涉及到的Transaction-SQL 语句被组织成一存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,降低网络负载.
D 存储过程可被作为一种安全机制来充分利用
系统管理员通过对执行某一存储过程的权限进行限制,从而能够实现对相应的数据访问权限的限制.避免非授权用户对数据的访问保证数据的安全.
三. 变量的声明和使用
一般格式:
variable_name type [CONSTANT] [NOT NULL] [:= initial_value];
强类型声明
variable_name [schema.] table_name.column_name%TYPE;
弱类型声明
关键字
variable_name 指定变量名。最长30个字符，必须用字母开始，可以包含字母、数字、下划线、＄和#等等。变量名除了放在双引号中的之外是不区分大小写的，其它地方(如表名，列名等等)也基本如此，除了放在双引号之中，不可以使用保留字(如SELECT等等)作为变量名。
type
指定变量数据类型。
CONSTANT
保留字，指定一个初始化值用于该变量，而且不可以改变该值。
NOT NULL
指定一个初始化值用于该变量，而且不可以设置为NULL。
initial_value
初始化值。省略则默认为NULL。指定了CONSTANT或者NOT NULL则必须指定初始化值。
schema
数据库中的架构(用户)名。省略则使用当前架构(用户)名。
table_name
特定架构中的表名。
column_name
特定表中的列名。
关于声明记录类型和索引表类型
PL/SQL中,想要将一定数量的相关变量作为单元操作时，可以声明记录类型。该类型与C语言中的结构相似。要使用记录，必须首先定义记录类型。然后基于该类型声明记录变量。
TYPE type_name IS RECORD (
variable_name type [NOT NULL] [:= initial_value]
[, variable_name type [NOT NULL] [:= initial_value] …]);
record_name type_name;
记录的独立字段可以使用点符号(.)
record_name. variable_name
PL/SQL中需要一个变量多次出现时候，可以定义索引表。类似与C语言中的一维数组。同样，必须先定义类型，然后基于该类型声明索引表变量。
TYPE tabletype_name IS TABLE OF {type | [schema.] table_name%ROWTYPE} INDEX BY BINARY_INTEGER;
table_name type_name;
声明的结果是具有两列的内存表:
KEY (BINARY_INTEGER)
VALUE(在声明语句中定义的类型type)
可以使用table_name(key)来引用单个元素。
四. EXECUTE IMMEDIATE
在存储过程内使用的SQL语句只能是下列之一:
SELECT
INSERT
UPDATE
DELETE
COMMIT
ROLLBACK
(……还有几个很不常用的,没有列出)
PL/SQL中不允许数据定义语言(DDL)，包括会话和系统控制语句。
Oracle自扩展的统计分析函数(例如MAX … over..)也不被允许。
当因程序需要而必须在PL/SQL中执行这些不被允许的语句时候，就可以使用EXECUTE IMMEDIATE。
EXECUTE IMMEDIATE string;
EXECUTE IMMEDIATE后面跟一个由可执行语句组成的字符串，在编译的时候由于是字符串所以不会被编译，只有在执行存储过程的时候才会把这个字符串提交编译执行。利用这点可以执行例如TRUNCATE TABLE，CREATE TABLE，DROP TABLE等等语句。
还有一个功能是可以动态生成SQL执行语句。可以在存储过程内部将sql语句分割成数个字符串，实现sql语句的拼接，然后利用EXECUTE IMMEDIATE执行。
五. IF条件控制
IF expression THEN
statement;[statement;…]
[ELSIF expression THEN
ststement; [statement;…]…]
[ELSE
ststement; [statement;…]]
END IF;
六. LOOP循环控制
1. LOOP循环
LOOP
[EXIT;]
[EXIT WHEN condition;]
statement; [statement;…]
END LOOP;
2. WHILE循环
WHILE condition LOOP
statement; [statement;…]
END LOOP;
3. FOR循环
FOR counter IN [REVERSE] start .. end LOOP
statement; [statement;…]
END LOOP;
七. 异常处理
1. 异常的类型
异常 Oracle错误号 描述
CURSOR_ALREADY_OPEN ORA-06511 当游标已经处于打开状态时，试图再次打开游标
DUP_VAL_ON_INDEX ORA-00001 违反唯一约束条件
INVALID_CURSOR ORA-01001 非法操作，如试图关闭已经关闭的游标
INVALID_NUMBER ORA-01722 在SQL中将字符进行显式或隐式转换为数字时失败
LOGIN_DENIED ORA-01017 无效的用户名或者口令
NO_DATA_FOUND ORA-01404 查询没有返回行
NOT_LOGGED_ON ORA-01012 会话没有连接到Oracle上
OTHERS 非预期错误
PROGRAM_ERROR ORA-06501 内部PL/SQL错误
ROWTYPE_MISMATCH ORA-06504 主机游标变量与PL/SQL游标变量的类型不匹配
STORAGE_ERROR ORA-06500 进程超出了内存范围
TIMEOUT_ON_RESOURCE ORA-00051 需要资源，但是操作超时
TOO_MANY_ROWS ORA-01422 SELECT查询返回不止一行
TRANSACTION_BACKED_OUT ORA-00061 死锁条件强制ROLLBACK
VALUE_ERROR ORA-06502 在过程语句中发生了转换错误
ZERO_DIVIDE ORA-01476 试图除以0
2. 异常的处理
EXCEPTION
[WHEN exception_name THEN
pl/sql_statements;
[WHEN exception_name THEN
pl/sql_statements;]]
[WHEN OTHERS THEN
pl/sql_statements;]]
END;
异常部分可以使用两个异常函数:
SQLCODE 返回Oracle错误号
SQLERRM 返回Oracle错误文本
3. 抛出异常和自定义异常
RAISE
RAISE exception_name;
RAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR(error_numer, error_text[, keep_errors]);
error_number
指定提供的错误号。该错误号应该在-20000和-20999之间提供。该范围是Oracle为用户定义的错误号设置的。
error_text
长度为1~2048字节的字符串，提供与错误相关的文本。
keep_errors
布尔值，为TRUE或FALSE。如果指定为TRUE，则将错误添加到已经提供的错误清单中。如果为FALSE，则使用以前的错误代替堆栈中的错误。默认为FALSE。
八. 游标的声明和使用
PL/SQL游标提供了从Oracle数据库中访问多行数据，并在每一行上执行程序的完全控制方法。
1. 使用游标步骤
声明游标
CURSOR cursor_name IS select_statement;
声明动态游标
CURSOR cursor_type IS REF CURSOR;
cursor_name cursor_type;
打开游标
OPEN cursor_name;
打开动态游标
OPEN cursor_name FOR select_statement;
执行FETCH语句提取数据
FETCH cursor_name INTO {variable_list | record};
关闭游标
CLOSE cursor_name;
2. 游标FOR循环
FOR {variable_list | record} IN {cursor_name | (select_statement)} LOOP
pl/sql_statement;
END LOOP;
3. 游标的属性和使用
%FOUND
显示是否返回数据行
%NOTFOUND
显示数据行未返回
%ISOPEN
显示游标是否被打开
%ROWCOUNT
返回游标迄今为止返回的行数
九. 关于优化
1. SELECT子句中避免使用 ‘ *‘
2. 使连接查询的表减小数据量
3. 减少访问数据库的次数
4. 使用DECODE函数
5. 使用TRUNCATE代替DELETE
6. 尽量多使用COMMIT
7. 使用内部函数代替多表连接
8. 使用EXISTS代替IN
9. 使用NOT EXISTS代替NOT IN
10. 使用表连接代替EXISTS
11. 使用EXISTS代替DISTINCT
12. 避免对索引列进行计算,包括数学计算,函数引用以及NOT, IS NULL, IS NOT NULL, LIKE ‘%…’, != 等等
13. 带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序
