The optimal writing for a stored procedure-CodePudding

Needs through stored procedures for the table B supply with material with customers in the assigned to list A number of data, the need to supply data to assign A date less than demand, eventually hopes to achieve the following results:
A table:

Table B:

CodePudding user response:

Some complex storage process, need to use for the temporary table, ORACLE must use dynamic statement to execute the CREATE statement, that is, incredibly still really created tables in a database, each time to drop, very troublesome, in addition, as long as stored procedures used in the temporary table, behind all the SQL statements, using dynamic SQL calls, if encounter quotes more SQL or transfer some parameters to the SQL, joining together really trouble, why such a big company, ORACLE to DB2 that is especially low-end special RZ, DB2 temporary tables directly in the storage process to CREATE, SQL statements directly refer to the table name, reply to close after the temporary table drop automatically, don't know how ORACLE's great god how temporary table mechanism,

CodePudding user response:

reference 1st floor a29374963 response:

some complex storage process, need to use for the temporary table, ORACLE must use dynamic statement to execute the CREATE statement, that is, incredibly still really created tables in a database, each time to drop, very troublesome, in addition, as long as the stored procedure to the temporary table, behind all the SQL statements, using dynamic SQL calls, if encounter quotes more SQL or transfer some parameters to the SQL, joining together really trouble, why such a big company, ORACLE to DB2 that is especially low-end special RZ, DB2 temporary tables directly in the storage process to CREATE, SQL statements directly refer to the table name, reply to close after the temporary table drop automatically, don't know how ORACLE's great god how temporary table mechanism,

Oracle can directly create a temporary table in a database, inside a stored procedure can be directly used, submitted to automatically delete temporary table data

CodePudding user response:

In the end want to achieve the following results
https://img.codepudding.com/202009/90685210503293.png
Recorded in A item01 2018/04/01 why allocated supply is 0, 100 instead of 100??????? And 2018/05/01 allocated supply is 100???????

CodePudding user response:

The create or replace procedure proc_tab_sjfp is
- add parameters
V_user varchar2 (10);
V_wlbm varchar2 (10);
V_xqrq date;
V_gjrq date;
V_xql_a number;
V_gjl_b number;
V_yfpgjl_b number;
Type cur_type is ref cursor;
Cur_sjfp cur_type;
The begin
Commit;
Open cur_sjfp for select customers, material code, date of demand, demand from tab_sjfp_a;
Loop
The fetch cur_sjfp into v_user v_wlbm, v_xqrq, v_xql_a;
Exit the when cur_sjfp % notfound;

Select supply, allocated supply, supply date into v_gjl_b v_yfpgjl_b, v_gjrq from
(select decode (supply, null, 0, supply) - decode (allocated supply, null, 0, allocated supply) supplies,
Decode (allocated supply, null, 0, allocated supply) allocated supply, supply date, rownum rn
The from tab_sjfp_b
Where the customer=v_user and material code=v_wlbm and supply date & lt; V_xqrq and decode (supply, null, 0, supply) & lt;> Decode (allocated supply, null, 0, allocated supply)
) where a a.r n=1;

If (v_xql_a & lt;=v_gjl_b) then
The update tab_sjfp_a set allocated supply=v_xql_a
Where the customer=v_user and material code=v_wlbm and demand date=v_xqrq;

The update tab_sjfp_b set allocated supply=decode (allocated supply, null, 0, allocated supply) + v_xql_a
Where the customer=v_user and material code=v_wlbm and supply date=v_gjrq;
end if;
If (v_gjl_b & lt; Then v_xql_a)
The update tab_sjfp_a set allocated supply=v_gjl_b
Where the customer=v_user and material code=v_wlbm and demand date=v_xqrq;

The update tab_sjfp_b set allocated supply=decode (allocated supply, null, 0, allocated supply) + v_gjl_b
Where the customer=v_user and material code=v_wlbm and supply date=v_gjrq;
end if;
If (v_gjl_b is null) then
Dbms_output. Put_line (' is allocated, ');
end if;
Commit;
End loop;
The exception
The when others then
Dbms_output. Put_line (sqlcode);
Dbms_output. Put_line (sqlerrm);
The close cur_sjfp;
End proc_tab_sjfp;

- test
/*
The begin
Proc_tab_sjfp;
end;

The update tab_sjfp_a set allocated supply=null;

The update tab_sjfp_b set allocated supply=null;

Select * from tab_sjfp_a;

Select * from tab_sjfp_b;

*/

Has personally test, no problem!

CodePudding user response:

B give the wrong table data, should be 20171210 20180410 20180310

CodePudding user response:

The fastest way to this demand is a SQL directly found out all of the results, is after you update, or delete + insert, or real-time query can be directly,

 with tab1 as (
Select the 'a' ty, 20180101 dt, gj 100 xq, 0 from dual union all 
Select the 'a' ty, 20180201 dt, gj 100 xq, 0 from dual union all 
Select the 'a' ty, 20180301 dt, gj 100 xq, 0 from dual union all 
Select the 'a' ty, 20180401 dt, gj 100 xq, 0 from dual union all 
Select the 'a' ty, 20180501 dt, gj 100 xq, 0 from dual union all 
Select the 'a' ty, 20180601 dt, gj 100 xq, 0 from dual union all 
Select the 'a' ty, 20180701 dt, gj 100 xq, 0 from dual union all 
Select the 'a' ty, 20180801 dt, gj 100 xq, 0 from dual union all 
Select 'b' ty, 20180101 dt, gj 80 xq, 0 from dual union all 
Select 'b' ty, 20180201 dt, gj 80 xq, 0 from dual union all 
Select 'b' ty, 20180301 dt, gj 80 xq, 0 from dual union all 
Select 'b' ty, 20180401 dt, gj 80 xq, 0 from dual union all 
Select 'b' ty, 20180501 dt, gj 80 xq, 0 from dual 
), 
Tab2 as (
Select the 'a' ty, 20171210 dt, 300 in_gj from dual union all 
Select the 'a' ty, 20180410 dt, 150 in_gj from dual union all 
Select 'b' ty, 20170310 dt, 100 in_gj from dual 
), 
Tab3 as (select t1. *, sum (t1. Xq) over (partition by t1. Ty order by t1. Dt) sum_xq from tab1 t1) 
, 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull