Oracle storage process, job, view, trigger

Oracle storage process, job, view, trigger
Stored Procedure

Create or replace procedure TestPro (Descerr out varchar2) isbegin select * from test; exception when others then Descerr: = 'interface table data generation failed! '| Sqlerrm; end TestPro;

Job task

Declare Descerr varchar2 (2000); begin -- stored procedure TestPro (Descerr => descerr); commit; end

View

create or replace view TestView as select id,name from test1;

Trigger

Create or replace trigger TestTrigger alter insert or update on test1 -- two tables test1 and test2 for each rowdeclare -- declare the variable lenNum Number (); status varchar2 (50 ); begin select count (1) into lenNum from test2 t where t. id =: new. id; -- judge the status case: new. ORDERSTATE when 'Wait for payment 'then begin statusnum: = 'wait _ BUYER_PAY '; end; when' waiting for shipment 'then begin statusnum: = 'wait _ SELLER_SEND_GOODS'; end case; -- determine the number if (lenNum <1) then insert into test2 (name, stat) -- or use values (: new. name,: new. stat) select name, stat from test1 t where id =: new. id; else -- update statement end if; end TestTrigger;