我觉得最难的部分在于设计逻辑部分…如何将现实意义抽象为一个类，或者变量、字段？哪种类型是最适合的一种？
本篇只是一些归纳笔记，并不详细，也没有实战示例代码。但对PL/SQL数据库常用的概念、语句做了记录，以便有个全局了解、要用时的快速查询。
更细节的理论知识可以本篇引用过的参考书籍：
资源可见附件。
（一）查询Select
1. 条件查询
（1）精确/模糊查询
--精确查询
select * from tableA where column1='value1'
--模糊查询
--通配符
--"%":表示任何字符出现任意次数（可以是0次）
--"_": 表示只能匹配单个字符，但可以持续添加，数量不限。
--like：如果like后没有出现通配符，则在SQL执行优化时将like默认为"="执行
select * from tableA where column1 like '%value1%'
（2）条件运算符
--并的关系
select * from tableA where column1='value1' and column2='value2'
--或的关系
select * from tableA where column1='value1' or column2='value2'
--混合限制，and优先级高于or
select * from tableA where (column1='value1' or column2='value2') and column3='value3'
--范围查询
select * from tableA where column1 between 100 and 200
--空值查询
select * from tableA where column1 is null/not null
（3）去重
select DISTINCT column1,column2 from table1
--针对的是所有的查询列，也就是column1与column2的组合不重复
（4）排序
--升序
select * from tableA order by column1 (ASC)
--降序
select * from tableA order by column1 DESC
--伪列
Rowid，相当于表主键的唯一标识AAdgakjf1287e78...
物理定位，实际上主键也要再次搜索rowid进行定位
Rownum结果集的序列号1,2,3...
（5）聚合函数
--分组聚合函数
--select后一定是分组聚合的条件或者是聚合函数
select sum(column1),max/min(column2),... from tableA group by column3
--分组后的条件查询
select sum(column1) from tableA group by column3 having sum(column1)>1000
2. 连接查询
查询结果来自多张表。
（1）内连接查询
--两表关联
select * from tableA, tableB
where tableA.column1=tableB.column2
--查询两张表中值相同的列，剔除不等的数据
--这个在Oracle数据库中比较常用，所使用的一般是主键
--三表关联
select * from tableA, tableB,tableC
where tableA.column1=tableB.column2
and tableA.column1=tableC.column3
（2）左外连接查询
以左表字段为主，右表字段为空也依然列出
--SQL1999标准
select * from tableA left join tableB
on tableA.column1=tableB.column2
--Oracle语法
select * from tableA, tableB
where tableA.column1=tableB.column2(+)
--左外连接在右表（+），右外连接在左表（+)
（3）右外连接查询
--SQL1999标准
select * from tableA right join tableB
on tableA.column1=tableB.column2
--Oracle语法
select * from tableA, tableB
where tableA.column1(+)=tableB.column2
3. 嵌套查询
（1）单行子查询
只返回一条数据，单行操作符
select * from tableA
where tableA.columnA> (select max(tableB.column2) from tableB)
（2）多行子查询
查询结果是单列多行，操作符 in, any, all
--集合
select * from tableA where columnA in (1,2,3)
select ,from, where后都可以有子查询；但select后只能是单行子查询
select column1,(select column2 from tableB where column3='value2') alias1 from tableA
4. 分页查询
select rownum, t.* from (select rownum alia1,t.* from tableA t)
where alias1<=20 and alais>10
--rownum后的运算符只能是"<"或"<="，不能>,>=,=
--原理是逐行扫描表的过程中才产生rownum。
--基于排序的分页查询
如果只一层嵌套，rowid实际上在排序前已经生成
所以需要两层嵌套
![[Image]](https://i-blog.csdnimg.cn/direct/013a915011f142e993d6969356b36862.png)
（二）视图View
理解这一概念对于需求分析与程序设计有帮助。
比如说我们拉取某些表的字段作为Inbound/outbound报表等的结果，这就是视图的应用。
概念
视图（View）是一种虚拟表，一种数据库对象。它基于一个或多个实际表（或其他视图）的查询结果。视图并不在数据库中存储实际的数据，而是根据需要动态生成。视图允许用户以一种特定的方式查看数据库中的数据，同时隐藏了底层表的复杂性。
作用
1. 数据安全性： 视图可以限制用户对数据库中数据的访问权限。通过只允许用户访问视图而不是直接访问表，可以确保用户只能看到他们被授权查看的数据。
2. 简化复杂性： 数据库中的表可能包含大量的列和行，而有时用户只关心表中的某一部分数据。通过创建视图，可以将复杂的查询和过滤操作封装在一个视图中，用户只需关注视图的结构而不必处理底层表的复杂性。
3. 数据独立性： 视图提供了一种逻辑数据独立性，允许数据库管理员更改底层表的结构而不影响用户对视图的访问。这使得数据库的维护更加灵活，而不会破坏已有的查询和报表。
4. 简化查询： 视图可以用于封装复杂的查询，使得用户可以通过简单的查询访问所需的数据。这样的封装也有助于提高查询的可维护性和可重用性。
5. 聚焦业务逻辑： 通过创建特定的视图，可以使数据库中的数据更符合业务需求。视图允许将数据呈现为业务实体，而不仅仅是底层表中的原始数据。
创建视图的语法通常包括对一个或多个表进行查询，然后将查询结果保存（封装）为一个虚拟表。视图可以被其他查询使用，就好像它是一个实际存在的表一样。
视图（View）和普通表在数据库中有一些关键区别：
1. 存储方式：
- 表：
表中存储了实际的数据，这些数据以行和列的形式组织，可以直接修改、插入和删除。
- 视图：
视图不存储实际的数据，它是一个虚拟表，其内容是根据查询实时生成的，不能直接修改。
2. 数据来源：
- 表：
表包含实际数据，通常是数据库的基本存储单元。
- 视图：
视图基于一个或多个表的查询结果，是对表的一种逻辑上的封装。
3. 修改操作：
- 表：
可以直接对表进行插入、更新和删除等操作，修改操作会直接影响实际存储的数据。
- 视图：
通常是只读的，不能直接对视图进行插入、更新和删除操作。但在某些情况下，可以通过触发器（Triggers）或者特定的视图定义允许更新的情况下进行修改。
4. 结构和复杂性：
- 表：
表通常具有相对简单的结构，是数据库中的基本组成单位。
- 视图：
视图可以根据需要封装复杂的查询，对用户隐藏底层表的结构，提供更简单和高层次的接口。
5. 数据独立性：
- 表：
表的结构变化可能会直接影响与之相关的应用程序或查询。
- 视图：
视图提供了一定程度的数据独立性，允许数据库管理员修改底层表的结构，而不影响用户对视图的访问。
6. 安全性和权限：
- 表：
用户对表的访问权限通常是直接授予的，需要小心管理以确保数据的安全性。
- 视图：
视图可以通过授予对视图的访问权限来实现更细粒度的访问控制，允许用户只能看到特定的数据子集。
总体而言，视图是对表的一种抽象，提供了更高层次的数据访问和管理方式，用于简化复杂性、提高安全性和实现数据独立性。
注：以上概念内容引自某篇博文，忘记原文链接了…侵删。
语法
--创建
CREATE VIEW view_name
AS SELECT column_name(s)FROM table_nameWHERE condition
--修改
CREATE OR REPLACE VIEW view_name
AS SELECT column_name(s)FROM table_nameWHERE condition
--检查约束的视图
--无法修改condition限制的条件
CREATE OR REPLACE VIEW view_name
AS SELECT column_name(s)FROM table_nameWHERE condition
with check option
--修改
update view_name set name='value'
commit;
注：修改基表/视图的数据，视图/基表的数据也会变化。
--只读视图
CREATE OR REPLACE VIEW view_name
AS SELECT column_name(s)FROM table_nameWHERE condition
with read only
--带错误的视图
--即使基表不存在，因为可能是动态生成，为存储语句而执行
CREATE FORCE VIEW view_name
AS SELECT column_name(s)FROM table_nameWHERE condition
--物化视图（实体化视图）
--物化视图是建立的副本，占用存储空间，更接近于一张真实表，在查询效率上更高。
--而与一张真实表的区别在于..是否能动态存储SQL语句？
CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE| BUILD DEFERRED]
--IMMEDIATE创建时即生成数据（默认）
--DEFERED创建时暂不生成数据
REFRESH [FAST | COMPLETE | FORCE]
--Refresh:基表发生DML更新后，view与基表同步的方式
--FAST：快速/增量刷新，只刷新自上次刷新后更新的修改
--将依据日志更新，这种速度会比较快。
--COMPLETE：完全更新
--FORCE判断：能快速刷新时优先快速刷新，否则完全刷新
ON [ COMMIT | DEMAND ]
START with (start_time) next (next_time)
--Commit基表做提交操作时自动刷新物化视图
--demand手动刷新
（三）序列
概念
序列是PostgreSQL中的一个特殊数据库对象，它按照指定的步长递增或递减生成数值。这些数值通常用作表的主键或唯一标识符，以确保每条记录都能被唯一识别。序列可以手动调用，也可以自动与表的插入操作相关联。
语法
CREATE SEQUENCE my_sequence
START WITH 1
INCREMENT BY 1
MINVALUE m | NO MINVALUE
MAXVALUE n
CYCLE | NO CYCLE--循环或不循环，如果循环那么不能运用于主键
CACHE n | NO CACHE;
- START WITH 1 指定序列的起始值。
- INCREMENT BY 1 指定序列每次递增的值。（步长）
- MINVALUE 和 MAXVALUE 表示对序列的最小值和最大值进行限制。
- CACHE 1 表示序列值在内存中缓存的数量，以优化性能。
函数 - nextval(‘sequence_name’)：获取序列的下一个值，并将序列向前推进。
- currval(‘sequence_name’)：获取序列的当前值（注意，在调用currval之前，必须在该会话中至少调用一次nextval）。
（四）同义词
概念
也即指定方案对象的一个别名，通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的SQL语句复杂度。
同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。
你可以创建公共同义词和私有同义词。其中，公共同义词属于PUBLIC特殊用户组，数据库的所有用户都能访问；而私有同义词包含在特定用户的方案中，只允许特定用户或者有基对象访问权限的用户进行访问。
语法
CREATE [public] SYNONYM synonym for object
--synonym表示要创建的同义词的名称，object表示表、视图、序列等我们要创建同义词的对象的名称
--私有同义词仅限于当前用户
（五）索引
概念
- 帮助MySQL高效获取数据的数据结构，通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。 - 一种能帮助mysql提高了查询效率的数据结构：索引数据结构。 索引的存储原理大致可以概括为一句话：以空间换时间。
- 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。
语法
详见文档：
https://developer.aliyun.com/article/831250
--显示索引
show index from tableA
--创建普通索引
CREATE INDEX index_owner_name on T_owners(column1)
--创建唯一索引
CREATE UNIQUE INDEX index_name on table_name(column1)
1、普通索引
普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。
因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列
创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。
2、唯一索引
普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，
所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE
把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；
二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；
如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。
事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
--复合索引
--复合索引由多个列组成
create index name_age_index on user(name,age);
--反向键索引
当某个字段的值为连续增长的值，如果构建标准索引，会形成单边树。
这样会增加查询的层数，性能下降。建立反向键索引，可以使索引的值变得不规则，从而使索引数能够均匀分布。
详情请参考引文，这涉及到二叉树高度的问题。虽然太复杂了我看不懂啦...
create index name_age_index on user(name) reverse;
--位图索引
--位图：像素图？可以理解为Rowid在一般索引中被存储在树的一个结点中，而位图索引则存储在图的其中一个点 。
适合创建在低基数列上，位图索引不直接创建Rowid，而是创建字节位在Rowid的映射
但缺陷在于不能进行范围查询，位图索引实际上是分散的。
create bitmap index name_age_index on user(name)
（六）PL/SQL
1. 基本语法结构
[DECLARE--声明变量
]
begin
--程序主体开始
[
exception
--异常处理
]
end;
2. 变量
--声明变量
变量名 类型（长度）;
--变量赋值
变量名:=变量值
declare price number(10,2)--声明长度为10，小数位为2的price变量
begin
price:=0.26
end
--其他赋值方式
如果不是直接赋值而是来自于查询的结果
select column1 into value2 from tableA where (condition...)
3.属性类型
（1）引用型
变量是从一个已有的表字段中引用而来的。
表名.列名%type
（2）记录型
相当于记录了查询结果。
表名%rowtype
4. 异常
发生异常后，当前语句将停止执行，控制权转移到PL/SQL块的异常处理部分。
异常有两种类型：
- 预定义异常：
当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发。
可见
https://www.cnblogs.com/lonelywolfmoutain/p/4234325.html，可以理解为标准的一般性异常。ORA-xxxxx是错误编码，可以在meta-link中查询到具体原因与解决方式。
- 用户定义异常：
用户可以在PL/SQL块的声明部分定义异常，自定义的异常通常通过RAISE语句显式引发。
exception
when first_exception then executable statement
--在exception里填入异常名（CASE_NOT_FOUND等）
--使用处理句柄处理异常
when second_exception then
when other then
end;
异常有两种引用方式：
- 在程序的执行单元中的RAISE语句引用（抛出异常）：
RAISE invalid_company_id;
- 在异常处理单元的when 语句（处理抛出的异常时）：
when invalid_company_id then...
5. 条件语句
if 条件 then
logic1...
elsif 条件2 then
logic2...
else
logic3...
end if;
循环
（1） 无条件循环
loop
--循环语句
end loop
--示例
--从1开始输出到100
declare
v_num number:=1;
begin
loop
dbms_output_line(v_num);
v_num:=v_num+1;
exit when v_num>100;
end loop;
end;
（2）条件循环
while (condition)
loop
end loop;
--示例
--从1开始输出到100
declare
v_num number:=1;
begin
while v_num<=100
loop
dbms_output_line(v_num);
v_num:=v_num+1;
end loop;
end;
（3）For循环
for var1 in 1..n--1到n
loop
end loop;
--示例
begin
for v_num in 1..100
loop
dbms_output_line(v_num);
v_num:=v_num+1;
end loop;
end;
- Continue语句
![[Image]](https://i-blog.csdnimg.cn/direct/7571c43eb6f041b590beb41ca137971f.png)
6. 游标
可见《程序设计》15.1游标基础
概念
条记录，每个记录都有3列。那么这个游标所起到的作用就是指向这20条记录的指针。
执行一个SQL的必要操作：解析、绑定、打开、执行、提取、关闭
隐式游标如上文所述，存在于基本的SQL语句中，常见的用法是根据主键进行查找。
语法
--显示游标
CURSOR cursor_name[([parameter[,parameter...])]
[RETURN return_specification]
IS SELECT_statement
[FOR UPDATE [OF [column_list]];
示例
FUNCTION jealousy_level(
NAME_IN IN friends.NAME%TYPE) RETURN NUBER
AS
CURSOR jealousy_cur--声明显示游标
IS
SELECT location FROM friends
WHERE NAME=UPPER(NAME_IN);
jealousy_rec jealousy_cur%ROWTYPE;--基于游标类型声明了一个记录
retval NUMBER;--声明变量
BEGIN
OPEN jealousy_cur;--打开游标
FETCH jealousy_cur INTO jealousy_rec;--从游标中取出一行
IF jealousy_cur%FOUND--检查游标属性以确认是否获得了一行
THEN
IF jealousy_rec.location='PUERTO RICO'--检查取得的记录内容并计算程度。
THEN retval:=10;
ELSIF jealousy_rec.location='CHICAGO'
THEN retval:=1;
END IF;
END IF;
CLOSE jealousy_cur;--关闭游标
RETURN retal;
EXCEPTION--预警代码，即使发生异常也可以清理现场。
WHEN OTHERS THEN
IF jealousy_cur%ISOPEN THEN
CLOSE jealousy_cur;
END IF;
END
--带有参数的游标
PROCEDURE explain_joke(main_category_in IN joke_category_id%
IS
CURSOR joke_cur(category_in IN VARCHAR2)--使这个游标可以接受不同的类别并返回对应记录行。
IS
SELECT name, category, last_used_date
FROM joke
WHERE category=UPPER(category_in);
joke_rec joke_cur%ROWTYPE;
BEGIN
--再次打开游标时也可以写入参数
OPEN joke_cur (main_category_in);--这时候可以填入不同的category值，得到不同的结果记录。
FETCH joke_cur INTO joke_rec;
--游标For循环
FOR record IN { cursor_name | (explicit SELECT statement)}
LOOP
executive statement(s)
END LOOP;
7. 过程、函数与参数
（1） 过程Procedure
概念
过程是执行一个或者多个动作的模块。由于在PL/SQL中，对于过程的调用是一个单独的可执行语句，一个PL/SQL代码块中可以只有一个过程调用语句。过程是模块化代码的关键组成部分，通过过程我们可以把程序逻辑进行强化并重用。
语法
PROCEDURE [schema.]name[(parameter[,parameter...])]
/*schema-拥有这个过程的模式的名字
name-过程名
*/
[AUTHID DEFINER | CURRENT_USER]--定义该过程的权限运行。
IS
[declarations]--声明过程的本地标识符。
BEGIN
executable statements--过程被调用时要执行的语句。
[EXCEPTION
exception handlers]--异常处理句柄。
END [name];
（2） 函数FUNCTION
概念
函数是通过RETURN语句而不是通过OUT或者IN OUT参数返回数据的模块。和调用过程不同的是，前者可以作为一个可执行语句单独存在，而对于函数的调用只能作为一个可执行语句的一部分，比如一个表达式中的元素或者在变量声明时用于赋予默认值。由于函数有返回值，因此就需要有数据类型。函数可以用来替换PL/SQL语句中具有相同的数据类型的表达式。
对应于内置函数，也即自定义函数。这个也常用。
语法
FUNCTION [schema.]name[(parameter[,parameter...])]
/*schema-拥有这个过程的模式的名字
name-过程名
*/
RETURN return_datatype--返回数据类型
[AUTHID DEFINER | CURRENT_USER]--定义该过程的权限运行。
[DETERMINISTIC]--优化器提示，系统可以为函数的返回值保留一个复制。查询优化器可以决定是使用保留的拷贝还是重新执行这个函数。
[PARALLEL_ENABLE...]--也是优化器提示，启用了这个特性的函数当在SELECT语句中调用时可以并行的处理。
[PIPELINED]--指定这个表函数的结果应该通过PIPE ROW命令多次返回
[RESULT_CACHE...]--该函数的输入值和返回结果是否应该保留在一个新的函数结果缓存中。
IS
[declarations]--声明过程的本地标识符。
BEGIN
executable statements--过程被调用时要执行的语句。
[EXCEPTION
exception handlers]--异常处理句柄。
END [name];
函数调用
示例：
1. 定义函数
![[Image]](https://i-blog.csdnimg.cn/direct/a869c23ad71b43a88095f3bd62ea69a2.png)
2. 调用函数
对象名[如对象、包名、记录等].函数名(参数)
参数模式
![[Image]](https://i-blog.csdnimg.cn/direct/8084eb8e9bf640bd801fc820de884066.png)
（3）包Package
概念
包将一组PL/SQL的代码元素组织在一个命名空间下。PL/SQL为我们提供了一种可以把程序或者其他PL/SQL元素比如游标、类型、变量的结构组织在一起的结构（包括逻辑结构和物理结构）。它们还提供了非常重要的、独一无二的功能，包括隐藏逻辑或者隐藏数据的能力，以及定义和定义操作“全局”或者会话持久数据的能力。
包的规范部分列出了这个包的可以供应用程序使用的全部元素，也要给使用这些元素的开发人员提供了所有需要的信息（通常叫做API或者应用编程接口）。
包的初始化单元是由位于包结尾的BEGIN语句（位于所有过程或者函数定义之外的BEGIN语句）直到整个包体最后的END语句之间的所有语句组成。
包的调用
![[Image]](https://i-blog.csdnimg.cn/direct/4b26b345a065454296449c17944d7f3a.png)
语法
--（1）包规范Specification
--包的规范部分在结构上和一个PL/SQL块的声明部分是一样的。唯一的差别在于：包规范中没有任何实现代码。
/*File on web:favorites.sql*/
PACKAGE favorites_pkg
AUTHID CURRENT USER
IS /* Or AS */
--以下是两个常量
c_chocolate CONSTANT PLS_INTEGER:=16;
c_strawberry CONSTANT PLS_INTEGER:=29;
--用TYPE声明了一个嵌套表类型
TYPE codes_nt IS TABLE OF INTEGER;
--根据之前那个类型声明的一个嵌套表变量
my_favorites codes_nt;
--一个返回收藏夹信息的REF CURSOR 游标类型
TYPE fav_info_rct IS REF CURSOR RETURN favorites%ROWTYPE;
--一个过程，接收一个收藏夹列表
--(用的是上面定义的类型)然后显示
--这个列表中的收藏夹的信息
PROCEDURE show favorites(list in IN codes nt);
--一个函数，返回收藏表中最喜欢的内容的信息
FUNCTION most_popular RETURN fav_info rct;
END favorites_pkg; --包的结束标签
--（2）包体Body
--包体部分包含了实现所需要的全部代码。包体并不总是必需的。
PACKAGE BODY favorates_pkg
IS
--一个私有变量
g_most_popular PLS_INTEGER :=c_strawberry;
--函数的实现部分
FUNCTION most_popular RETURN fav_info_rct
IS
retval fav_info_rct;
null_cv fav_info_rct;
BEGIN
OPEN retval FOR
SELECT*
FROM favorates
WHERE code= g_most_popular;
RETURN retval;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN null_cv;
END most_popular;
--过程的实现部分
PROCEDURE show_favorites(list_in IN codes_nt) IS
BEGIN
FOR index IN list_in.FIRST .. list_in.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(list_in(index));
END LOOP;
END favorites_pkg;--包的结束标签
（4）触发器Trigger
可见《程序设计》第19章
概念
数据库触发器是一个当数据库发生某种事件时作为对这个事件的响应而执行的一个被命名的程序单元。对于一个基于Oracle数据库构建的设计良好的应用程序来说，触发器是一个关键组件。
语法
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER}
{INSERT | DELETE | UPDATE | UPDATE OF column_list} ON table_name
[FOR EACH ROW]
[WHEN (...)]
[DECLARE ...]
BEGIN
...executable statement...
[EXCEPTION...]
END [trigger_name];
![[Image]](https://i-blog.csdnimg.cn/direct/56e19120456445d6b07a076f50b759b2.png)
程序示例
这是一段摘自项目一个程序包中的代码片段：
cux_wip_lot_reserve_wip001_pkg
![[Image]](https://i-blog.csdnimg.cn/direct/598e790c2b91426dac771a698c850c97.png)
查看包规范与包体：
1. 包规范（未打开的盒子）
CREATE OR REPLACE PACKAGE cux_wip_lot_reserve_wip001_pkg AS
PROCEDURE submit_request(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_organization_id IN NUMBER,
p_wip_entity_id_from IN NUMBER,
p_wip_entity_id_to IN NUMBER,
p_wip_status IN NUMBER,
p_operation_seq IN NUMBER,
p_department_id IN NUMBER);
--设定值
g_table_width NUMBER := 1400;
g_table_column NUMBER := 13;
g_print_header0 VARCHAR2(300) := '<html>
<head>
<meta http-equiv="Content-Language" content="zh-cn">
<meta http-equiv="Content-Type" content="text/html; charset=G_CHARSET">';
g_print_header1 CONSTANT VARCHAR2(5000) := '<title>Missing Reservation Quantity Report</title>
<style type="text/css">
<-- $header: porstyl2.css 115.9 2011/05/31 09:21:42 Hand ship ${ }
<!--
body {background-color: #ffffff;}
.subheader1 {font-family: Arial, Helvetica, Geneva, sans-serif;
font-size: 13pt;
font-weight: bold;
color: #336699;}
.subheader2 {font-family: Arial, Helvetica, Geneva, sans-serif;
font-size: 10pt;
font-weight: bold;
color: #336699;}
.tableheader {font-family: Arial, Helvetica, Geneva, sans-serif;
font-size: 10pt;
font-weight: bold;
background: #E0ECF8;
color: #336699;
text-align: center;}
.tabledata {font-family: Arial, Helvetica, Geneva, sans-serif;
font-size: 9pt;
background: #EFF5FB;
color: #000000;
mso-number-format: "\@"}
.tablenumber {font-family: Arial, Helvetica, Geneva, sans-serif;
font-size: 9pt;
background: #EFF5FB;
color: #000000;
text-align: right}
.footer {font-family: Arial, Helvetica, Geneva, sans-serif;
font-size: 10pt;
font-weight: bold;
color: #336699;}
-->
</style>
</head>';
（...删节了类似以上的部分）
PROCEDURE create_reservation_supply(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_reservation_id OUT NOCOPY NUMBER,
p_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_primary_uom_code IN VARCHAR2,
p_parent_lot IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_wip_entity_id IN NUMBER,
p_repetitive_schedule_id IN NUMBER,
p_operation_seq_num IN NUMBER,
p_lot_rsv_id IN NUMBER,
x_full_reserve_flag OUT NOCOPY VARCHAR2);
（...删节其他过程代码）
END cux_wip_lot_reserve_wip001_pkg;
2. 包体（打开的盒子）
CREATE OR REPLACE PACKAGE BODY cux_wip_lot_reserve_wip001_pkg AS--创建包体
-- Global variable
g_pkg_name CONSTANT VARCHAR2(30) := 'cux_wip_lot_reserve_wip001_pkg';
g_login_id NUMBER := fnd_global.login_id;
g_user_id NUMBER := fnd_global.user_id;
PROCEDURE log_msg(p_msg IN VARCHAR2) IS
BEGIN
cux_conc_utl.log_msg(p_msg);
END log_msg;
PROCEDURE raise_exception(x_return_status VARCHAR2) IS
BEGIN
IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
RAISE fnd_api.g_exc_unexpected_error;
ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN
RAISE fnd_api.g_exc_error;
END IF;
END raise_exception;--抛出自定义异常，fnd_api里应当已经封装好错误提示。
--定义主过程
PROCEDURE create_reservation_supply(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_reservation_id OUT NOCOPY NUMBER,
p_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_primary_uom_code IN VARCHAR2,
p_parent_lot IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_wip_entity_id IN NUMBER,
p_repetitive_schedule_id IN NUMBER,
p_operation_seq_num IN NUMBER,
p_lot_rsv_id IN NUMBER,
x_full_reserve_flag OUT NOCOPY VARCHAR2) IS
--OUT/IN 详见参数格式。IN是只读，OUT是只写
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESERVATION_SUPPLY';
l_savepoint_name CONSTANT VARCHAR2(30) := 'sp_001';
l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
l_new_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
l_new_serial_number inv_reservation_global.serial_number_tbl_type;
l_serial_number inv_reservation_global.serial_number_tbl_type;
l_partial_reservation_flag VARCHAR2(2) := fnd_api.g_false;
l_force_reservation_flag VARCHAR2(2) := fnd_api.g_false;
l_validation_flag VARCHAR2(2) := fnd_api.g_false;
l_partial_reservation_exists BOOLEAN := FALSE;
l_quantity_reserved NUMBER;
l_statuts VARCHAR2(10);
-- l_trolin_tb inv_move_order_pub.trolin_tbl_type;
l_reservation_qty NUMBER;
--定义变量
CURSOR csr_lot_reserve(c_subinv IN VARCHAR2,
c_locator_id IN NUMBER,
c_ranks IN NUMBER) IS
--创建游标，这里还使用了嵌套查询
SELECT inventory_item_id,
organization_id,
lot_number,
expiration_date,
parent_lot_number
FROM (SELECT mln.inventory_item_id,
mln.organization_id,
mln.lot_number,
mln.expiration_date,
mln.parent_lot_number
FROM mtl_lot_numbers mln
WHERE 1 = 1
AND EXISTS
(SELECT 'X'
FROM mtl_onhand_quantities_detail mnq
WHERE mln.inventory_item_id = mnq.inventory_item_id
AND mln.organization_id = mnq.organization_id
AND mln.lot_number = mnq.lot_number
AND (mnq.subinventory_code = c_subinv OR
c_subinv IS NULL)
AND (mnq.locator_id = c_locator_id OR
c_locator_id IS NULL))
--批次控制
AND EXISTS
(SELECT 'X'
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = p_organization_id
AND msi.lot_control_code = 2
)
AND mln.disable_flag IS NULL
AND mln.inventory_item_id = p_item_id
AND mln.organization_id = p_organization_id
AND (mln.lot_number = p_lot_number OR p_lot_number IS NULL)
AND (mln.parent_lot_number = p_parent_lot OR
p_parent_lot IS NULL)
AND (c_ranks IN (1, 2))
UNION ALL
SELECT wro.inventory_item_id,
wro.organization_id,
NULL lot_number,
NULL expiration_date,
NULL parent_lot_number
FROM wip.wip_requirement_operations wro
WHERE wro.wip_entity_id = p_wip_entity_id
AND (wro.repetitive_schedule_id = p_repetitive_schedule_id OR
p_repetitive_schedule_id IS NULL)
AND wro.operation_seq_num = p_operation_seq_num
AND wro.inventory_item_id = p_item_id
AND wro.organization_id = p_organization_id
AND (c_ranks = 1)
AND EXISTS
(SELECT 'X'
FROM mtl_onhand_quantities_detail mnq
WHERE wro.inventory_item_id = mnq.inventory_item_id
AND wro.organization_id = mnq.organization_id
AND (mnq.subinventory_code = c_subinv OR
c_subinv IS NULL)
AND (mnq.locator_id = c_locator_id OR
c_locator_id IS NULL))
AND EXISTS (SELECT 'X'
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = p_organization_id
AND msi.lot_control_code <> 2
)
AND wro.supply_subinventory = c_subinv
UNION ALL
SELECT wro.inventory_item_id,
wro.organization_id,
NULL lot_number,
NULL expiration_date,
NULL parent_lot_number
FROM wip.wip_requirement_operations wro
WHERE wro.wip_entity_id = p_wip_entity_id
AND (wro.repetitive_schedule_id = p_repetitive_schedule_id OR
p_repetitive_schedule_id IS NULL)
AND wro.operation_seq_num = p_operation_seq_num
AND wro.inventory_item_id = p_item_id
AND wro.organization_id = p_organization_id
AND (c_ranks = 3)
AND EXISTS
(SELECT 'X'
FROM mtl_onhand_quantities_detail mnq
WHERE wro.inventory_item_id = mnq.inventory_item_id
AND wro.organization_id = mnq.organization_id
AND (mnq.subinventory_code = c_subinv OR
c_subinv IS NULL)
AND (mnq.locator_id = c_locator_id OR
c_locator_id IS NULL))
AND wro.supply_subinventory <> nvl(c_subinv, '####')
)
ORDER BY inventory_item_id ASC,
organization_id ASC,
expiration_date ASC
;
l_attr_qty NUMBER := 0;
IF x_return_status != fnd_api.g_ret_sts_success THEN
--没有成功就报错误，抛出异常
IF x_msg_count > 1 THEN
x_msg_data := fnd_msg_pub.get(p_msg_index => 1, p_encoded => 'F');
END IF;
dbms_output.put_line('x_msg_data:=' || x_msg_data);
x_return_status := fnd_api.g_ret_sts_error;
cux_api.set_message(p_app_name => 'CUX',
p_msg_name => 'CUX_WIP_LOT_RSV_ERROR',
p_token1 => 'ERROR_MSG',
p_token1_value => x_msg_data);
raise_exception(x_return_status);
参考资料
2429
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
