user_tbl.sql

来自「开源中间件 导入Hibernate相关的包进该项目： 选中项目名称后单击鼠标」· SQL 代码 · 共 70 行

alter table USER_TBL drop constraint PK_USER_TBL;
alter table USER_TBL drop constraint unique_user_name;
drop table USER_TBL;
drop function add_user_func;
drop procedure update_user_salary_prc;
drop sequence user_sequence;



create table USER_TBL (
   id number(10,0) not null,
   USER_NAME varchar2(255) constraint unique_user_name unique,
   PASSWORD varchar2(255),
   NAME varchar2(255),
   GENDER number(1,0),
   age number(10,0),
   BIRTH_DATE date,
   DESCRIPTION varchar2(255),
   SALARY double precision,
  constraint PK_USER_TBL primary key (id)
);

INSERT INTO USER_TBL VALUES(200, 'kevin', 'kevin', 'kevin ding', 1, 6, to_date('2000-06-26','yyyy/mm/dd'), 
'manager of tarena', 40.0);

create sequence user_sequence;

CREATE PROCEDURE UPDATE_USER_SALARY_PRC(
	P_ID IN USER_TBL.ID%TYPE,
	P_FACTOR IN NUMBER
)AS USER_COUNT INTEGER;
BEGIN
	SELECT COUNT(*)  INTO USER_COUNT FROM USER_TBL WHERE ID=P_ID;	
	IF USER_COUNT = 1 THEN
		UPDATE USER_TBL SET SALARY = SALARY * P_FACTOR WHERE ID = P_ID;
		COMMIT;
	END IF;
END UPDATE_USER_SALARY_PRC;
/

CREATE FUNCTION ADD_USER_FUNC(
	P_ID			IN	USER_TBL.ID%TYPE,
	P_USER_NAME 	IN USER_TBL.USER_NAME%TYPE,
	P_PASSWORD 		IN USER_TBL.PASSWORD%TYPE,
	P_NAME				IN	USER_TBL.NAME%TYPE,
	P_GENDER			IN	USER_TBL.GENDER%TYPE,
	P_AGE					IN	USER_TBL.AGE%TYPE,
	P_BIRTH_DATE		IN	USER_TBL.BIRTH_DATE%TYPE,
	P_DESCRIPTION	IN	USER_TBL.DESCRIPTION%TYPE,
	P_SALARY			IN	USER_TBL.SALARY%TYPE
)RETURN INTEGER AS USER_COUNT INTEGER;
BEGIN
	SELECT COUNT(*)  INTO USER_COUNT FROM USER_TBL  
	WHERE ID = P_ID;
	IF USER_COUNT > 0 THEN
		RETURN 0;
	ELSE
		INSERT INTO USER_TBL (ID, USER_NAME, PASSWORD, NAME, GENDER, AGE, BIRTH_DATE, DESCRIPTION, SALARY) 
		VALUES(P_ID, P_USER_NAME, P_PASSWORD, P_NAME, P_GENDER, P_AGE, P_BIRTH_DATE, P_DESCRIPTION, P_SALARY);
		COMMIT;
		RETURN 1;
	END IF;
END ADD_USER_FUNC;	
/