alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

drop table T_USERS;
CREATE  table  T_USERS (
		hs_uuid number(20) primary key,
		hs_login varchar(50) not null unique,
		hs_nick_name varchar(200),
		hs_creator varchar(50) default 'luchunli',
		hs_created_time Date,
		hs_modifider varchar(50) default 'luchunli',
		hs_sex varchar(10),
		hs_password varchar(50),
		constraint T_USERS_CHECK_CONS check(hs_sex in ('male', 'female'))
);

drop sequence T_USERS_SEQ;
CREATE sequence T_USERS_SEQ
	start with        1
	increment by   1
	nomaxvalue
	nominvalue
	nocache
	;

CREATE or REPLACE trigger T_USERS_TR
		before insert on T_USERS
		for each row
		begin
			SELECT T_USERS_SEQ.nextval into :new.hs_uuid from dual;
		end;
		/

CREATE or REPLACE function T_USERS_ENCODE_PWD (
		str_pwd in varchar
		)
		return VARCHAR
		as
		raw_pwd RAW(128);
		encode_pwd RAW(128);
	begin
		raw_pwd := UTL_RAW.cast_to_raw(str_pwd);
		DBMS_OBFUSCATION_TOOLKIT.MD5(input => raw_pwd, checksum => encode_pwd);
		return LOWER(RAWTOHEX(encode_pwd));
	end T_USERS_ENCODE_PWD;
	/

SQL> select T_USERS_ENCODE_PWD('luchunli') from dual;
-- 直接通过SELECT测试
T_USERS_ENCODE_PWD('LUCHUNLI')
------------------------------------------------------
edd2c9015c11a0cf2df6a1b5681a374a
-- 通过PL/SQL块测试	
set serveroutput on	
DECLARE
       v_input_pwd VARCHAR2(100):= 'luchunli';
       v_pwd VARCHAR2(100);   
BEGIN
       v_pwd := T_USERS_ENCODE_PWD(v_input_pwd);
       DBMS_OUTPUT.put_line(v_pwd);
END;
/

CREATE or REPLACE procedure T_USERS_PROC (
			login		in 		T_USERS.hs_login%type,
			nick_name	in 		T_USERS.hs_nick_name%type,
			creator		in		T_USERS.hs_creator%type,
			modifider	in		T_USERS.hs_modifider%type,
			password	in		T_USERS.hs_password%type
		)
		as
		t_start number := 1;
		t_total number := 1000;
		t_sex varchar(10) := 'male';
		hs_encode_pwd varchar(50);
		-- temp number := 0;
	begin
		hs_encode_pwd := T_USERS_ENCODE_PWD(password);		
		DBMS_OUTPUT.put_line('原来密码：'||password||'，加密后的密码：'||hs_encode_pwd);
		while t_start < t_total loop
		/**
                -- 不明白的地方
		select floor(dbms_random.value(1,10)) into temp from dual;
		if (temp mod 2) = 0 then
		t_sex := 'male';
		else
		t_sex := 'famale';
		end if;
		*/
		INSERT INTO t_users(hs_login, hs_nick_name, hs_creator, hs_created_time, hs_modifider, hs_sex, hs_password) values (login||t_start, nick_name, creator, to_date('2011-10-11', 'yyyy-mm-dd'), modifider, 'male', hs_encode_pwd);
		-- 在上面的插入语句中，如果将'male'替换成我上面计算出来的t_sex将报错check constraint (SYSTEM.T_USERS_CHECK_CONS) violated
		t_start := t_start + 1;	
		end loop;
	end T_USERS_PROC;
	/

/**
-- 可以接受外部输入，当调用存储过程时将其作为参数传入
accept  login		pro