Oracle stored procedures, anonymous blocks, functions, packages

Principles for using procedures and functions:

1. If you need to return multiple values and do not return a value, use the procedure, or use a function if you only need to return a value.

2, the procedure is generally used to perform a specified action, the function is generally used to calculate and return a value.

3. You can call a function inside an SQL statement (such as an expression) to complete a complex computational problem, but you cannot invoke a procedure. So this is the feature of the function.

One, stored procedures

1. Initial storage procedure

--Stored procedures: implementation of the historical table create or replace procedure movetohistory_1  (                                                                                          o_count out number ,                                          &NBSP;&NBSP;&NBSP;&NBSP;ERROR&NBSP;OUT&NBSP;VARCHAR2)  ISV_COU-NT number; V_subscribe_id ti_c_iom_busiform. subscribe_id%  Type;v_time date  := to_date (  ' 2015/3/20 16:24:23 ', ' yyyy-mm-dd hh24:mi:ss '  ); cursor c_move2his is select subscribe_id from ti_c_iom_busiform where  accept_date <= v_time; begin  o_count :=0;  open c_move2his;   LOOP    FETCH C_MOVE2HIS INTO V_SUBSCRIBE_ID;     EXIT WHEN C_MOVE2HIS%NOTFOUND;    BEGIN        SELECT COUNT  (*)  INTO V_COUNT from  TI_C_IOM_BUSIFORM  Where accept_date <= v_time;       if (V_COUNT >  0 )  THEN            INSERT  into ti_ch_iom_busiform             (BUSIFORM_ Id,day,trade_id,tRadegroup_id,tradegroup_num,tradegroup_index,busiform_type,subscribe_id,busiform_oper_type,business_type, STATUS,NET_TYPE_CODE,ADDRESS_CODE_A,ADDRESS_CODE_Z,FLOW_TEMPLET_ID,IN_MODE_CODE,DBSRC,BPM_MAIN_ID,ORDER_ID, Priority,suggest_time,confirm_res_no,prod_spec_id,res_templet_id,accept_date,exec_time,exec_month,latest_ Finish_time,plan_finish_time,finish_time,src_type,busiform_extend,user_id,trade_staff_id,trade_eparchy_code, Trade_city_code,trade_depart_id,area_code,serial_number,busiform_cust_type,term_ip,cust_class_id,cust_id,cust_ Name,cust_link_name,cust_link_phone,product_id,product_name,error_type, error_code,error_desc,close_ Satisfy_degree,close_desc,rsrv_str1,rsrv_str2,rsrv_str3,rsrv_date1,rsrv_date2, remark)               ( select busiform_id,day,trade_id,tradegroup_ Id,tradegroup_num,tradegroup_index,busiform_type,subscribe_id,busiform_oper_type,business_type,status,net_type _code,address_code_a,address_codE_Z,FLOW_TEMPLET_ID,IN_MODE_CODE,DBSRC,BPM_MAIN_ID,ORDER_ID,PRIORITY,SUGGEST_TIME,CONFIRM_RES_NO,PROD_SPEC_ID, Res_templet_id,accept_date,exec_time,exec_month,latest_finish_time,plan_finish_time,finish_time,src_type, Busiform_extend,user_id,trade_staff_id,trade_eparchy_code,trade_city_code,trade_depart_id,area_code,serial_ NUMBER,BUSIFORM_CUST_TYPE,TERM_IP,CUST_CLASS_ID,CUST_ID,CUST_NAME,CUST_LINK_NAME,CUST_LINK_PHONE,PRODUCT_ID, Product_name,error_type, error_code,error_desc,close_satisfy_degree,close_desc,rsrv_str1,rsrv_str2,rsrv_ str3,rsrv_date1,rsrv_date2, remark              from  TI_C_IOM_BUSIFORM              where SUBSCRIBE_ID =  V_SUBSCRIBE_ID                and accept_date <= v_time);              delete FROM TI_C_IOM_BUSIFORM              where SUBSCRIBE_ID =  V_SUBSCRIBE_ID              and accept_date <= v _time;            o_count := o_count  + 1;        END IF ;       commit;      EXCEPTION         WHEN OTHERS THEN           rollback;            delete from ti_ch_iom_busiform             where SUBSCRIBE_ID =   v_subscribe_id;  &nbsP;         commit;    end;  end  LOOP;  close C_MOVE2HIS;end movetohistory_1;

2, stored procedures, add custom exception, and improve, by external parameters

--Stored Procedure create or replaceprocedure movetohistory  ( o_time in date,                            o_count out number )  ISV_COUNT number; V_subscribe_id ti_c_iom_busiform. subscribe_id% type;v_time date := o_time;v_error exception; --Custom Exception Cursor c _move2his is select subscribe_id from ti_c_iom_busiform where accept_date  <= v_time; begin  o_count :=0;  select count  (*)  into v_count from   ti_c_iom_busiform where accept_date <= v_time;  if (V_COUNT  <= 0 )  then   raise v_error;  end if;  open  c_move2his;  loop    fetch c_move2his into v_subscribe_id;    exit when c_move2his%notfound;     BEGIN    SELECT COUNT  (*)  into v_count from  ti_c _iom_busiform where accept_date <= v_time;    if (V_COUNT >  0 )  then         insert into ti_ch_iom_ busiform         (Busiform_id,day,trade_id,tradegroup_id,tradegroup_num , Tradegroup_index,busiform_type,subscribe_id,busiform_oper_type,business_type,status,net_type_code,address_ Code_a,address_code_z,flow_templet_id,in_mode_code,dbsrc,bpm_main_id,order_id,priority,suggest_time,confirm_ Res_no,prod_spec_id,res_templet_id,accept_date,exec_time,exec_month,latest_finish_time,plan_finish_time,finish _time,src_type,busiform_extend,user_id,trade_staff_id,trade_eparchy_code,trade_city_code,trade_depart_id,area_ Code,serial_numbeR,busiform_cust_type,term_ip,cust_class_id,cust_id,cust_name,cust_link_name,cust_link_phone,product_id,product _NAME,ERROR_TYPE,&NBSP;ERROR_CODE,ERROR_DESC,CLOSE_SATISFY_DEGREE,CLOSE_DESC,RSRV_STR1,RSRV_STR2,RSRV_STR3, Rsrv_date1,rsrv_date2, remark)          ( select busiform _id,day,trade_id,tradegroup_id,tradegroup_num,tradegroup_index,busiform_type,subscribe_id,busiform_oper_type, Business_type,status,net_type_code,address_code_a,address_code_z,flow_templet_id,in_mode_code,dbsrc,bpm_main_ Id,order_id,priority,suggest_time,confirm_res_no,prod_spec_id,res_templet_id,accept_date,exec_time,exec_month, Latest_finish_time,plan_finish_time,finish_time,src_type,busiform_extend,user_id,trade_staff_id,trade_eparchy_ CODE,TRADE_CITY_CODE,TRADE_DEPART_ID,AREA_CODE,SERIAL_NUMBER,BUSIFORM_CUST_TYPE,TERM_IP,CUST_CLASS_ID,CUST_ID, Cust_name,cust_link_name,cust_link_phone,product_id,product_name,error_type, error_code,error_desc,close_ Satisfy_degree,close_desc,rsrv_str1,rsrv_str2,rsrv_str3,rsrv_date1,rsrv_date2, remark          from  TI_C_IOM_BUSIFORM          where SUBSCRIBE_ID =  V_SUBSCRIBE_ID          and accept_date <= v_time);          delete from ti_c_iom_busiform         where  subscribe_id =  v_subscribe_id         and  accept_date <= v_time;         o_count :=  o_count + 1;    end if ;    commit;      EXCEPTION        WHEN OTHERS THEN            rollback;           delete  From ti_ch_iom_busiform            where  SUBSCRIBE_ID =  V_SUBSCRIBE_ID;             commit;    end;  end loop;  close c_ move2his;  exception   when v_error then           raise_application_error (- 20010,  ' data is not exists! '  );end movetohistory;

--Stored procedure call set Serveroutput on;declarev_date Date: = To_date (' 2015/3/24 19:19:21 ', ' yyyy-mm-dd hh24:mi:ss '); O_count number ; Begino_count: = 0;movetohistory (V_date,o_count);d bms_output.put_line (' o_count: ' | | O_count); end;

exec stored procedure name;

--Stored Procedure Assign permissions grant create any table to username;grant create any procedure to username;grant execute any procedure to username ;

Second, anonymous block

--Anonymous BLOCK: Implement simple output on console (input temporarily not implemented) SET Serveroutput on;declarev_flow_templet_id td_m_node_templet.flow_templet_id% type;v_   node_templet_id td_m_node_templet.node_templet_id% type;begin--v_node_templet_id: = & Please enter the node name; -This piece has not been realized, always reported the wrong v_node_templet_id: = ' bizopporder '; SELECT flow_templet_id to v_flow_templet_id from Td_m_node_templet a WHERE node_templet_id = V_node_templet_id;dbms_ Output.put_line (v_flow_templet_id); Exception--when no_data_found then--dbms_output.put_line (' No data found '); When OTHERS thendbms_output.put_line (' @SQLCODE is ' | | SQLCODE | | ' and @SQLERRM is ' | | SQLERRM); end;

Third, function

1. Simple Function Example

Create or Replace function Tomorrowreturn Date--Must have a return is today date;  --return value in the Declaration section Nextdate date;begin Today: = Sysdate; Nextdate: = today + 1;return nextdate;  --returnexceptionwhen others then return '-1 '; -Abnormal parts have returnend;

2. Calling functions by passing arguments to functions

--Function: Has the Enter parameter create or Replace function Find_flow_name (Node_temid in varchar2) return varchar2isv_flow_templet_id Td_m_node_ templet.flow_templet_id% Type;beginselect flow_templet_id to v_flow_templet_id from Td_m_node_templet a WHERE node_ templet_id = Node_temid;dbms_output.put_line (v_flow_templet_id); return v_flow_templet_id; Exception--when no_data_found then--dbms_output.put_line (' No data found '); When OTHERS thendbms_output.put_line (' @SQLCODE is ' | | SQLCODE | | ' and @SQLERRM is ' | | SQLERRM); return '-1 '; end find_flow_name;

--Call function set Serveroutput on;declarev_node_name varchar2: = ' toorder_preorderfzx1 '; v_flow_name VARCHAR2 (20); Begin--v_node_name: = ' toorder_preorderfzx1 '; V_flow_name: = Find_flow_name (v_node_name);d bms_output.put_line (' Process name: ' | | V_flow_name); exceptionwhen others thendbms_output.put_line (sqlcode| | ' and ' | | SQLERRM); end;

Iv. use of Sqlcode and SQLERRM

Set Serveroutput On;declarev_error VARCHAR2 (500); Beginv_error:=sqlerrm;dbms_output.put_line (' @SQLCODE is ' | | SQLCODE | | ' and @SQLERRM is ' | | SQLERRM); END;

Oracle stored procedures, anonymous blocks, functions, packages