oracle 存储过程基础

oracle 存储过程基础
-------------------

-- 包与包体
-- 存储过程
-- 设置输出缓冲
SET SERVEROUTPUT ON SIZE 1000000;
-- 编译存储过程
alter procedure procedure_name compile;
-- 执行存储过程
exec plsql_template('100000');
-- 查看错误
SHOW ERRORS
SHOW ERROR procedure plsql_template;
-- 函数
-- 变量


-- 游标
-- 静态游标: 声明时绑定查询语句
-- 动态游标 : 打开时绑定查询语句
-- 强限制返回类型
-- 弱限制返回类型
-- type trec is record(...);
-- type dcursor is ref cursor return trec;
-- dcur dcursor;
-- rec trec;
-- open dcur for select * from t;
-- fetch dcur into rec;
-- while dcur%found loop
-- ...
-- fetch dcur into rec;
-- end loop;

-- 显式游标
-- declare
-- open
-- fetch
-- close
-- 隐式游标
-- dml sql%rowcount
-- for

-- 参数游标
-- 无参数游标

-- 游标属性
-- %FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。
-- %NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。
-- %ROWCOUNT：当前时刻已经从游标中获取的记录数量。
-- %ISOPEN：是否打开。

-- 使用游标更新变量
-- 行共享锁
cursor cursor_name(parameter_name datetype) is select_statement for update [of column_reference] [nowait];
-- 引用当前游标数据
update table_name set column = .. where current of cursor_name;


-- 控制结构
-- 条件
if condition then
expression
end if

if condition then
expression1
else
expression2
end if

if condition1 then
expression1
elsif condition2 then
expression2
else
expression3
end if

case variable
when value1 then expression1
when value2 then expression2
else expression3
end case;

-- 循环
loop
exit when condition;
...
end loop;

while condition loop
...
end loop;

for variable in [reverse] lower .. upper loop
...
end loop;

-- 嵌套循环
-- 标记法
<<outer>>
for i in 1..100 loop
<<inner>>
for j in 1..100 loop
...
exit outer when result=1000;
exit when result=500;
end loop inner;
end loop outer;


-- 类型
-- record 一行多列
-- ref cursor 游标指针
-- 集合
-- table 多行单列 BINARY_INTEGER | PLS_INTEGER
-- first
-- firor
-- next
-- last
-- count
-- exists
-- delete
-- nested table 子表 无元素个数限制 下标从1开始
-- varray 多行单列 数组 有元素个数限制 下标从1开始 需要使用构造函数创建类据
-- first
-- firor
-- next
-- last
-- count
-- exists
-- limit
-- extend
-- trim
-- 组合
-- 记录表 多行多列
-- 多维数组 多行多列


-- 批量处理
-- bulk collect
-- select
-- select * bulk collect into record_table from t;
-- dml
-- delete from emp where deptno=&no;
-- returning ename bulk collect into ename_table;
-- fetch
-- fetch cur bulk collect into t [limit 100];
-- for all
-- for all i in 1..10000
-- sqlstmt;
--
-- 过滤空值
-- for all i in indeces of collection
-- sqlstmt;
-- 批量属性
-- sql%bulk_rowcount 取得在执行批量绑定操作时第i个元素所作用的行数


-- 绑定变量
-- &绑定变量名
-- 调用时绑定变量的使用
-- sql: var name varchar2(20);
-- exec procedure_name(:name);


-- 存储过程语法规则
-- 1 参数部分
-- 1. 参数默认为IN
-- 2. 没有参数列表时, 括号不用写
-- 3. 参数的类型不需要声明长度
-- 4. 参数IN为只读, 可以有默认值; 参数OUT/INOUT为可写, 不能有默认值
-- 5. 可以使用%TYPE, %ROWTYPE
-- 6. 格式: 参数 IN|OUT|INOUT 类型 默认值
-- 2 声明块
-- 1. 声明类型需要长度
-- 2. 可以使用%TYPE, %ROWTYPE
-- 3. 不需要写 declare
-- 4. 赋值不是=,而是:=
-- 5. 字符常量使用单引号 ''
-- 6. 语句块以;结束
-- 7. 不能使用保留字
-- 8. 变量可以有默认值
-- 9. 与JAVA相反先写变量后写类型
-- 10. 支持自治事务
-- 3 代码块
-- 1. 条件分支语句中是 elsif 不是 elseif
-- 2. :绑定变量
-- 3. &绑定变量占位符
-- 4. select * into v
-- 5. return 退出过程 exit 退出循环
-- 6. 静态SQL可以写DML, SELECT, TCL; 动态SQL还可以写 DDL, DCL
-- 7. 游标记得关闭, 多使用隐式游标
-- 8. 事务要记得提交或回滚
-- 9. 多使用批量
-- 10. 少使用动态SQL
-- 11. DML查询子句前没有AS
-- 12. 一定要做校验
-- 13. 一定要写注释
-- 4 异常块
-- 1. 自定义异常处理
-- 2. 记录异常日志(表)
-- 3. 回滚失败操作
-- 4. 清理
-- 5 涉及关键字
-- sql
select
select ... into
insert into
update
delete from
merge

-- dynamic sql
execute immediate

-- tcl
commit
rollback
savepoint
set transaction

-- dcl
grant
revoke

-- ddl
create
alter
drop
truncate

-- type
cursor
type
table
varray
nested table
record
ref cursor
object

-- cursor
open
open for
fetch ... into
close

-- batch
forall
bulk collect

-- other
&
:=
%ROWTYPE
%TYPE
%FOUND
%NOTFOUND
where current of cursor_name
exception when
exit when
return
goto
null
using
of

-- hints
/*+ append */


-- 触发器
行级触发器: 插入,更新,删除操作每一行触发一次.:NEW, :OLD有效,有WHEN子句.
语句级触发器: 插入,更新,删除操作每条SQL触发一次.:NEW, :OLD无效,没有WHEN子句.
系统级触发器: 系统事件触发.

行级更新触发器: 更新可指定列操作触发, 语句级不可指定可指定列.

BIR 触发器: Before Constraint 修改列值
AIR 触发器: After Constraint 拒绝事务, 其它动作

行级触发器
----------
create or replace trigger rowtrigger
[before|after] [insert [or] update [OF Column1,Column2] [or] delete] on t
for each row
[WHEN (exprssion)]
declare
...
begin
...
end rowtrigger;

触发器属性
:NEW 新值
:OLD 原值
inserting 插入
updating 更新
deleting 删除

语句级触发器
------------
create or replace trigger stmttrigger
[before|after] [insert [or] update [or] delete] on t
declare
...
begin
...
end stmttrigger;