plsql 工具包

create or replace package system_out IS TYPE number_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE VARCHAR2_list IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; TYPE boolean_list IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER; test_exception EXCEPTION; procedure print( out_str IN VARCHAR2 DEFAULT '', out_bool IN BOOLEAN DEFAULT NULL ); procedure println ; procedure println( out_str IN VARCHAR2, out_bool IN BOOLEAN DEFAULT NULL ); procedure println( out_bool IN BOOLEAN ); procedure print( out_bool IN BOOLEAN DEFAULT NULL ); PROCEDURE format_print_attr_(attr_ VARCHAR2); FUNCTION get_random_char RETURN CHAR; FUNCTION get_random_num RETURN CHAR; FUNCTION get_varchar(len_ NUMBER) RETURN VARCHAR2; FUNCTION get_number(len_ NUMBER, sub_len_ NUMBER) RETURN NUMBER; FUNCTION get_number(len_ NUMBER) RETURN NUMBER; FUNCTION get_column_length(table_name VARCHAR2, column_name VARCHAR2) RETURN NUMBER; end system_out; create or replace package body system_out is procedure print( out_str IN VARCHAR2 DEFAULT '', out_bool IN BOOLEAN DEFAULT NULL ) IS BEGIN IF out_bool IS NULL THEN dbms_output.put(out_str); ELSE IF out_bool THEN dbms_output.put(out_str||'true'); ELSE dbms_output.put(out_str||'false'); END IF; END IF; END; procedure println(out_str IN VARCHAR2 , out_bool IN BOOLEAN DEFAULT NULL ) IS BEGIN IF out_str IS NULL THEN dbms_output.put_line('NULL'); ELSE IF out_bool IS NULL THEN dbms_output.put_line(out_str); ELSE IF out_bool THEN dbms_output.put_line(out_str||'true'); ELSE dbms_output.put_line(out_str||'false'); END IF; END IF; END IF; END; procedure println( out_bool IN BOOLEAN ) IS BEGIN IF out_bool IS NULL THEN dbms_output.put_line('null'); END IF; IF out_bool THEN dbms_output.put_line('true'); ELSE dbms_output.put_line('false'); END IF; END; procedure print( out_bool IN BOOLEAN DEFAULT NULL ) IS BEGIN IF out_bool IS NULL THEN dbms_output.put('null'); END IF; IF out_bool THEN dbms_output.put('true'); ELSE dbms_output.put('false'); END IF; END; procedure println IS BEGIN dbms_output.new_line(); END; PROCEDURE format_print_attr_(attr_ IN VARCHAR2) IS name_ VARCHAR2(100) ; value_ VARCHAR2(100); ptr_ NUMBER ; -- lineble_ BOOLEAN := FALSE; BEGIN LOOP EXIT WHEN client_sys.Get_Next_From_Attr(attr_, ptr_, name_, value_) = FALSE; -- IF lineble_ THEN -- print(name_||client_sys.record_separator_||value_||client_sys.field_separator_); -- lineble_ := TRUE; -- ELSE println(name_||client_sys.record_separator_||value_||client_sys.field_separator_); -- lineble_ := FALSE; -- END IF; END LOOP; END; FUNCTION get_random_char RETURN CHAR IS BEGIN RETURN chr(floor(dbms_random.value(65,91))); END; FUNCTION get_random_num RETURN CHAR IS BEGIN RETURN floor(dbms_random.value(0,10))||''; END; FUNCTION get_varchar(len_ NUMBER) RETURN VARCHAR2 IS result_str VARCHAR(10000) := ''; BEGIN IF len_ < 1 OR len_ > 10000 THEN RAISE test_exception; END IF; FOR temp IN 1..len_ LOOP result_str := result_str||get_random_char; END LOOP; RETURN result_str; EXCEPTION WHEN OTHERS THEN RAISE test_exception; END; FUNCTION get_number(len_ NUMBER) RETURN NUMBER IS result_str VARCHAR(10000) := ''; BEGIN IF len_ < 1 OR len_ > 10000 THEN RAISE test_exception; END IF; FOR temp IN 1..len_ LOOP result_str := result_str||get_random_num; END LOOP; RETURN to_number(result_str); EXCEPTION WHEN OTHERS THEN RAISE test_exception; END; FUNCTION get_column_length(table_name VARCHAR2, column_name VARCHAR2) RETURN NUMBER IS length NUMBER; CURSOR get_length IS SELECT u.DATA_LENGTH FROM USER_TAB_COLUMNS u WHERE u.TABLE_NAME = upper(table_name) AND u.COLUMN_NAME = upper(column_name); BEGIN OPEN get_length; FETCH get_length INTO length; IF get_length%Notfound THEN RAISE test_exception; END IF; CLOSE get_length; RETURN length; END; FUNCTION get_number(len_ NUMBER, sub_len_ NUMBER) RETURN NUMBER IS result_str VARCHAR(10000) := ''; sub_result_str VARCHAR(10000) := ''; l_ NUMBER := 0; sl_ NUMBER := 0; BEGIN IF(sub_len_ < 0 OR sub_len_ > len_ OR len_ IS NULL OR sub_len_ IS NULL) THEN RAISE test_exception; END IF; l_ := len_; sl_ := sub_len_; l_ := l_ - sl_; FOR temp IN 1..l_ LOOP result_str := result_str||get_random_num; END LOOP; FOR temp IN 1..sl_ LOOP sub_result_str := sub_result_str||get_random_num; END LOOP; result_str := result_str||'.'||sub_result_str; RETURN to_number(result_str); END; end system_out;