关于存储过程编写的一点总结(转)

一. 数据类型和相关函数
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需要一次排序操作, 而其他的至少需要执行两次排序

posted @ 2010-11-27 12:37  Daniel_Lu  阅读(254)  评论(0编辑  收藏  举报