"ORACLE" Common scripts

--ifelse

DECLARE

V_num number;  BEGIN v_num: = 100; If V_num > then--elsif v_num > and then--ELSE--END IF; END; --with parameter stored procedure create OR REPLACE PROCEDURE delete_subscriber (p_subid in number) is V_mem VARCHAR2 (+): = ' <sql5.4.4.11r2.p1 4121911_6861> '; BEGIN--。。。 END delete_subscriber;/--no parameter stored procedure create OR REPLACE PROCEDURE delete_subscriber is v_mem VARCHAR2: = ' <sql5.4.4.1 1r2.p14121911_6861> '; BEGIN--。。。 END delete_subscriber;/--function with parameters where the return sentence must exist, a function will have an error if the return sentence is not executedCREATE OR REPLACE FUNCTION get_productstr (p_subid in number, P_paydt DATE)RETURN VARCHAR2 isRESULT VARCHAR2 (1024); BEGIN merge_str: = "; For v_p in C_productinfo LOOP merge_str: = Merge_str | | V_p.productnamestr | | ' [R: ' | | V_p.monthfee | | ‘].  ‘; END LOOP;RETURN RESULT;end;/--function can have default value create OR REPLACE function Countrynumber (n_max number default 10000,n_min number default) RETURN VARCHAR 2 Isv_resultvalue VACHARR2 (50); BEGIN RETURN V_resultvalue; END; --Exception Handling example declare v_num number (2): = 10;  V_zero Number (2): = 0; V_result number (5); BEGIN V_result: = V_num/v_zero; EXCEPTION when Zero_divide and then Dbms_output. Put_Line (' DIVIDE ZERO '); END; Exception Description: Dup_val_index: Violation of uniqueness Login_denied: Login failed, user name password error no_data_found: No data found too_many_rows: There are too many data rows, Zero_divide: Divisor is 0 error value_error: algorithm or conversion error Case_not_found: found mismatch in case statement when statement DECLARE E_overnumber EXCEPTION;    BEGIN IF V_max < V_num then--deal ....  RAISE E_overnumber; END IF EXCEPTION when E_overnumber and then Dbms_output. Put_Line ("ERR MSG"); END;

"ORACLE" Common scripts