Orcale存储过程demo和走的坑

DataBase

orcale的坑
1、定义的存储过程的名一定要和最后END的时候的名字一样

1
2
3
4
5
6
create or replace procedure "Pro_Sprk_Cancel"(
)
as
...
begin
end "Pro_Sprk_Cancel";

2、存储过程中用的一切参数一定要定义

1
2
3
4
5
6
7
8
9
10
11
12
create or replace procedure "Pro_Sprk_Cancel"(
)
as
 --定义变量
 num number;
drug_num number;
begin
  begin
     select count(1) into drug_num from "his_system_drug_info" ;     
     select count(1) into num from "his_pharmacy_storage" ;
     end;    
end "Pro_Sprk_Cancel";

3、定义的游标，需要 .出来的，必须在定义游标的时候写出来。
并且游标变量点出来的，必须和必须在定义游标的时候的一样。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create or replace procedure "Pro_Sprk_Cancel"(
)
as
 --定义变量
 num number;
drug_num number;
 --定义游标
cursor emp_cursor is select "DrugCode","Total","Count" as "Count1","SellPrice" ,"Batch"  from "HPD_INSTOCK_TEMP" where "InStockCode"=InStockCode;
 --定义游标变量
emp_c emp_cursor%rowtype;

//必须一样 。如果写成emp_c.DrugCode是会报错
begin
  begin
     select count(1) into drug_num from "his_system_drug_info"  where "drug_no"=emp_c."DrugCode";    
     select count(1) into num from "his_pharmacy_storage" where "drug_no" = emp_c."DrugCode" and"batch_group_no"=emp_c."Batch" and "stock_qty">=emp_c."Count1";
     end;    
end "Pro_Sprk_Cancel";

4、这个没有试，但是我觉得每个sql都要用begin end框起来
5、orcale的存储过程中用到的循环可在一下看到，写的 很详细。其中注意的是for循环不需要打开游标和关闭游标，但是fetch循环则需要，这一点在下面的链接中写的很清楚
https://blog.csdn.net/qq_26222859/article/details/52261787

完成demo
demo1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE OR REPLACE 
procedure Pro_Sprk_Cancel(
StoreHouseID varchar2,  
   InStockCode varchar2,  
    Registrar varchar2,    
Operator1 varchar2  
)
as 
num number;
drug_num number;
cursor emp_cursor is select "DrugCode","Total","Count" as "Count1","SellPrice" ,"Batch"  from "HPD_INSTOCK_TEMP" where "InStockCode"=InStockCode;
begin
   for emp_c in emp_cursor LOOP
   
     begin
     select count(1) into drug_num from "his_system_drug_info"  where "drug_no"=emp_c."DrugCode";     
     select count(1) into num from "his_pharmacy_storage" where "drug_no" = emp_c."DrugCode" and"batch_group_no"=emp_c."Batch" and "stock_qty">=emp_c."Count1";
     end;    
  
     if drug_num=0 or num =0 then
      BEGIN
       dbms_output.put_line('药品表中没有这个药品、库存明细表没有这个药品或数量大于库存数量');
       rollback;  
      END;
     else
        begin
        update "his_pharmacy_storage"
        set "stock_qty" = "stock_qty" - emp_c."Count1",
            "stock_total_price" = "stock_total_price" - emp_c."Total"
         where
         "drug_no" = emp_c."DrugCode"
         and
         "batch_group_no"=emp_c."Batch"; 

         update  "his_pharmacy_stockinfo"
         set "stock_qty" = "stock_qty" - emp_c."Count1",
         "stock_total_price" = "stock_total_price" - emp_c."Total"
           where
           "drug_no" = emp_c."DrugCode";
         end;
     end if;
     
   END LOOP; 
commit; //没有错误就commit提交
EXCEPTION  //捕捉错误，rollback ，返回提示信息
 when others THEN 
 rollback;
 dbms_output.put_line('退库失败');
end Pro_Sprk_Cancel;

demo2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
CREATE OR REPLACE 
PROCEDURE Pro_Sprk(
    
     InStockCode  in varchar2 ,
     Operator1    in varchar2   
) as
drug_num number;
num number;
storage_num number;
cursor emp_cursor is select "DrugCode","Total","Count" as "Count1","SellPrice","Manufacture" ,"Batch" from "HPD_PHARMACY_DETAIL" where "InStockCode"= InStockCode;
 emp_c emp_cursor%rowtype;
BEGIN
 dbms_output.put_line('遍历游标，将游标中的数据放入变量');
 --遍历游标，将游标中的数据放入变量
 for  emp_c in emp_cursor LOOP
--判断药品表中是否有这个药品
begin
select count(1) into drug_num from "his_system_drug_info" where "drug_no"=emp_c."DrugCode";
end;
if drug_num=0 then
   --药品表中没有这一条数据,回滚
  begin
   dbms_output.put_line('药品表中没有这个药品');
   rollback;
  end;
end if;
--判断库存表中是否有这个编号的药品
begin
select count(1) into num from "his_pharmacy_stockinfo" where "drug_no"= emp_c."DrugCode";
end;
--有的话更新库存表的金额和数量
if num>0 then
  --更新库存表金额和数量
dbms_output.put_line('更新库存表金额和数量');
  begin
update  "his_pharmacy_stockinfo"
set "stock_qty" = "stock_qty" + emp_c."Count1",
      "stock_total_price" = "stock_total_price" + emp_c."Total"
    where
      "drug_no" = emp_c."DrugCode";
end;  
else
  --没有就新增一条库存记录
begin
dbms_output.put_line('1');
insert into "his_pharmacy_stockinfo"
(
"id" ,
"drug_no",
"drug_name",
"drug_type",
"system_type",
"retail_price",
"stock_qty",
"stock_total_price",
"preout_sum",
"preout_total_price",
"lack_flag",
"unit_flag",
"min_sum",
"operator_name",
"operator_time"
)select
HIS_PHARMACY_STOCKINFO_SEQ.NEXTVAL,
"drug_no",
"drug_name",
"drug_type",
"system_type",
emp_c."SellPrice",
 emp_c."Count1",
 emp_c."Total",
 0,
 0,
 0,
 0,
 1,
 Operator1,
 SYSDATE
 from
 "his_system_drug_info"
 where "drug_no"= emp_c."DrugCode";
end;
end if;
begin
  select count(1) into storage_num from "his_pharmacy_storage" where "drug_no"= emp_c."DrugCode" and "batch_group_no"=emp_c."Batch";
end;
if storage_num>0 then
  begin
  update "his_pharmacy_storage"
  set "stock_qty" = "stock_qty" + emp_c."Count1",
      "stock_total_price" = "stock_total_price" + emp_c."Total"
    where
      "drug_no" = emp_c."DrugCode"
      and
      "batch_group_no"=emp_c."Batch";
   end;
else
begin
dbms_output.put_line('2');
insert into "his_pharmacy_storage" (
"id",--  ID
"drug_no",-- 药品编码
"drug_name",-- 药品名称
"drug_spell_no",-- 商品名拼音码
"drug_wb_no",--  商品名五笔码
"drug_define_no",  --商品名自定义码
"drug_regular_name", --通用名
"drug_regular_spell_no", --通用名拼音码
"drug_regular_wb_no",--  通用名五笔码
"drug_regular_define_no",--  通用名自定义码
"drug_formal_name",--  学名
"drug_formal_spell_no",--  学名拼音码
"drug_formal_wb_no",-- 学名五笔码
"drug_formal_define_no",-- 学名自定义码
"drug_alias_name",-- 别名
"drug_alias_spell_no",-- 别名拼音码
"drug_alias_wb_no",--  别名五笔码
"drug_alias_define_no",--  别名自定义码
"drug_regular_english_name",-- 英文通用名
"drug_english_alias_name",-- 英文别名
"drug_english_name",-- 英文名
"international_no",--  国际编码
"country_no",--  国家编码
"specs",
"producer",
"drug_type",
"system_type",
"batch_group_no",
"retail_price",--  零售价
"base_dose",-- 基本剂量
"drug_split_type",-- 拆分类型
"valid_state",-- 有效性标志
"madeself_flag",-- 自制标志
"test_flag",-- 是否需要试敏
"gmp_flag",--  GMP标志
"otc_flag",--  OTC标志
"show_flag",-- 大屏幕显示标记
"special_restriction_flag",--  特限药品标记
"lastest_flag",--  新药标记
"lack_flag",-- 缺药标志
"phy_effect_level_one",--  一级药理作用
"phy_effect_level_two",--  二级药理作用
"phy_effect_level_three",--  三级药理作用
"stock_qty",--  库存数量
"stock_total_price",--  库存金额
"preout_sum",-- 预扣库存数量
"preout_total_price",-- 预扣库存金额
"stock_state",--  药品库存状态
"operator_id",--  盘点人ID
"operator_name",--  盘点人姓名
"operate_date",-- 盘点时间
"expiration_warn_flag"  --有效期预警标志
 )select 
 HIS_PHARMACY_STORAGE_SEQ.NEXTVAL,
 "drug_no",-- 药品编码
"drug_name",-- 药品名称
"drug_spell_no",-- 商品名拼音码
"drug_wb_no",--  商品名五笔码
"drug_define_no",  --商品名自定义码
"drug_regular_name", --通用名
"drug_regular_spell_no", --通用名拼音码
"drug_regular_wb_no",--  通用名五笔码
"drug_regular_define_no",--  通用名自定义码
"drug_formal_name",--  学名
"drug_formal_spell_no",--  学名拼音码
"drug_formal_wb_no",-- 学名五笔码
"drug_formal_define_no",-- 学名自定义码
"drug_alias_name",-- 别名
"drug_alias_spell_no",-- 别名拼音码
"drug_alias_wb_no",--  别名五笔码
"drug_alias_define_no",--  别名自定义码
"drug_regular_english_name",-- 英文通用名
"drug_english_alias_name",-- 英文别名
"drug_english_name",-- 英文名
"international_no",--  国际编码
"country_no",--  国家编码
"specs",
emp_c."Manufacture",
"drug_type",
"system_type",
emp_c."Batch",
emp_c."SellPrice",
"base_dose",-- 基本剂量
"drug_split_type",-- 拆分类型
"valid_state",-- 有效性标志
"madeself_flag",-- 自制标志
"hypotest",-- 是否需要试敏
"gmp_flag",--  GMP标志
"otc_flag",--  OTC标志
"show_flag",-- 大屏幕显示标记
"special_restriction_flag",--  特限药品标记
"lastest_flag",--  新药标记
0,-- 缺药标志
"phy_effect_level_one",--  一级药理作用
"phy_effect_level_two",--  二级药理作用
"phy_effect_level_three",--  三级药理作用
emp_c."Count1",
emp_c."Total",
0,
0,
1,
'',
Operator1,
SYSDATE,
0
from
 "his_system_drug_info"
 where "drug_no"= emp_c."DrugCode";
end;
end if;
END LOOP;
commit;
EXCEPTION 
 WHEN others THEN
	rollback;
	dbms_output.put_line('入库失败');
END Pro_Sprk;