1. Create a function to the taxpayer identification number as a parameter and return the name of the taxpayers,
2. Create a user password ecode_user Ecode1234, the default table space ecode_user default SIZE 5 g on the largest SIZE 10 g, temporary table space for TMP,
3. Create a custom function, realizes the extraction parameters in the string function of the first set of figures, it returns null if string figures,
For example: string "ABC535D12D", return the result of 53512; The string "ECODE" returns null,
4. The staff salary table is as follows:
The table name field type description
EMP_SALARY EMP_ID varchr2 (32) employee ID
EMP_SALARY EMP_NAME varchr2 (32) employee name
EMP_SALARY SAL number pay
Create a stored procedure, analyze the programmer's wages, salary range from 1500 to 10000, if there is a fifty percent salary less than 2000 yuan, give everyone a raise, plus 100 at a time, again carries on the analysis, until in 2000 yuan more than half of the National People's Congress, after the stored procedure execution, output eventually add how much?
The Create or replace procedure
5. Write a stored procedure, according to the net incoming data update table data, according to the incremental updating, in the middle table structure is as follows:
Table name field name field type data, for example,
ECODE_ZJB_FHY NSRSBH taxpayer identification number VARCHAR2 (32) 91370105677281000 a
Jinan ECODE_ZJB_FHY NSRMC taxpayer name VARCHAR2 (200) optimal kay trade co., LTD.
ECODE_ZJB_FHY RKRQ warehousing date VARCHAR2 (16) in 2018-06
ECODE_ZJB_FHY HYML industry VARCHAR2 (16) construction
ECODE_ZJB_FHY SJJE paid-in amount number 13798.09
ECODE_ZJB_FHY QXJE district amount number 235.7

Table name field name field type data, for example,
ECODE_ZJB_FSZ NSRSBH taxpayer identification number VARCHAR2 (32) 91370105677281000 a
Jinan ECODE_ZJB_FSZ NSRMC taxpayer name VARCHAR2 (200) optimal kay trade co., LTD.
ECODE_ZJB_FSZ RKRQ warehousing date VARCHAR2 (16) in 2018-06
ECODE_ZJB_FSZ SZMC tax name VARCHAR2 (16) VAT
ECODE_ZJB_FSZ SJJE paid-in amount number 13798.09
ECODE_ZJB_FSZ QXJE district amount number 235.7
(logging data processing)

6. Report the middle table name and table structure is as follows:
Table name field name field type data, for example,
ECODE_ZJB_FHY NSRSBH taxpayer identification number VARCHAR2 (32) 91370105677281000 a
Jinan ECODE_ZJB_FHY NSRMC taxpayer name VARCHAR2 (200) optimal kay trade co., LTD.
ECODE_ZJB_FHY RKRQ warehousing date VARCHAR2 (16) in 2018-06
ECODE_ZJB_FHY HYML industry VARCHAR2 (16) construction
ECODE_ZJB_FHY SJJE paid-in amount number 13798.09
ECODE_ZJB_FHY QXJE district amount number 235.7

Table name field name field type data, for example,
ECODE_ZJB_FSZ NSRSBH taxpayer identification number VARCHAR2 (32) 91370105677281000 a
Jinan ECODE_ZJB_FSZ NSRMC taxpayer name VARCHAR2 (200) optimal kay trade co., LTD.
ECODE_ZJB_FSZ RKRQ warehousing date VARCHAR2 (16) in 2018-06
ECODE_ZJB_FSZ SZMC tax name VARCHAR2 (16) VAT
ECODE_ZJB_FSZ SJJE paid-in amount number 13798.09
ECODE_ZJB_FSZ QXJE district amount number 235.7
Write a stored procedure, the net incoming data and above middle, comparing the table data contrast once a month, the contrast results stored the results in the table, the table data show: taxpayers' name, date of warehousing, table between the actually paid amount, net storage amount paid in the actually paid amount differences, middle table district-level amount, the net amount of incoming district, the district difference, (form a stored procedure logging data processing, records stored procedure execution time, duration, article number, if successful, exception information (if abnormal display abnormal information, no abnormal display treatment "success")),

7. Will SXT_DJ_NSRXX table according to the competent tax authorities where the subordinate cities partition (SXT_DJ_NSRXX table has the province's 17 cities and data, according to the 17 cities partition), and in the common fields (according to the usual practice subject fields) is commonly used to determine which create indexes on,
8. Create a stored procedure, output the name of the taxpayers,
9. Create a stored procedure, according to the taxpayer identification number, modify the production and business address; The content of the production and business address is determined by the incoming parameters,
10. Create a stored procedure, query warehousing date within a certain range (date range is determined by the incoming parameters), the income of each area county district's top ten enterprise basic information (taxpayer identification number, name of the taxpayer, industry, production and business address, registered address, competent point branch, taxpayer status) and the actually paid amount, amount of the district, and the query results in the table in the middle of inserting a table self-built (middle),
11. To create a stored procedure, will be a net put in storage the data according to the summary in jinan in the table, and the result is inserted into the middle table (create), call a stored procedure after the middle table data automatically according to the net incoming data update,
12. Create a stored procedure, the net will be put in storage the data in the table according to the summary in February, and will result in the table is inserted into the middle, at the same time can be different according to the incoming tax authorities, tax authorities to generate different data and table name ends in the corresponding tax authority code,
13. Use function, to judge SXT_ZS_JRK NSRSBH contains letters, if it is contains letters will return after excluding letters Numbers, if does not contain letters directly back Numbers,
14. To create a stored procedure, according to the view VIEW_SXT_ZS_JRK SXT_ZS_JRK table data, if exceptions exist in the process, the rollback, and record the error code and error messages,
SXT_ZS_JRK_MAP 15. Create a table, table structure in complete accord with SXT_ZS_JRK table structure, keep the SXT_ZS_JRK_MAP table data and SXT_ZS_JRK table data real-time synchronization,

CodePudding user response:

The original poster is this topic, or taking notes?

CodePudding user response:

This is rank test

reference 1st floor selling fruit net reply:

the original poster is this topic, or recording notes?

CodePudding user response:

Posting a Posting points not borrow floor, in the hope that the great god look - I hope I don't mind

 create or replace procedure auto_update_qlrlb_fwid (v_slid varchar2) is 
The begin 
Declare 
Slid v_slid2 bdc_rec. Type: %=rpad (v_slid, 20, "); 
S an INTEGER; 
C bdc_qlrlb. Bs % type; 
- u number; 
- t number; 
- x number; 
V_gyfs bdc_qlr. Gyfs % type; 
The begin 
Select gyfs into v_gyfs from bdc_qlr where qlrid=(select qlrid from bdc_qlrlb where bz=0 and rownum=1 and slid out=v_slid2); 
Select count (*) into s from bdc_h_dj slid the where=v_slid2; 
If s=1 then 
The update bdc_qlrlb a set dyid=(select fwid from bdc_h_dj b where a.s lid=b.s lid) where bz=0 and slid out=v_slid2; 
end if; 
If s> 1 and v_gyfs=0 then 
Select count (*) into c from bdc_qlrlb where bs is null and bz=0 and slid xh=1 and=v_slid2; 
While c> 0 loop 
 update bdc_qlrlb set bs slid the where==c v_slid and bs is null and rownum=1;  
commit; 
C:=c - 1; 
end loop; 
The update bdc_qlrlb a set dyid=(select fwid from bdc_h_dj b where a.s lid=b.s lid and a. s=b.o id) where bz=0 and slid out=v_slid2; 
end if; 
end; 
- if s=2 and v_gyfs=1 then 
- update bdc_qlrlb set SXH=rownum slid the where=v_slid2 order by bz. 

End auto_update_qlrlb_fwid; 

This compilation process execution cycle inside the update statement execution is not an error, but the results don't come into effect, alone with a SQL statement execution is normal, is in the process of execution is not effective,

CodePudding user response:

reference weixin_44276900 reply: 3/f

post points Posting a borrow enough floor, hope god help see - hope I don't mind

 create or replace procedure auto_update_qlrlb_fwid (v_slid varchar2) is 
The begin 
Declare 
Slid v_slid2 bdc_rec. Type: %=rpad (v_slid, 20, "); 
S an INTEGER; 
C bdc_qlrlb. Bs % type; 
- u number; 
- t number; 
- x number; 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull