⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 升级脚本.sql

📁 CallSun 人才招聘求职系统 V2.20
💻 SQL
📖 第 1 页 / 共 4 页
字号:
🔍
1234 
	[p_marry] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_health] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_workplace] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_worktype] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_experience] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_wage] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_graduation] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_education] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_professionname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_profession] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_language] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_language_level] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_liveplace] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_place] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_linkman] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_telephone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_email] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_mobile] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_qq] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_address] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_code] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_prefer_jobclass1] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_prefer_jobtitle1] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_prefer_jobclass2] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_prefer_jobtitle2] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[p_introduce] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	[p_training] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	[p_skill] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	[p_award] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	[p_countview] [int] NULL ,
	[p_state] [int] NULL ,
	[p_update] [datetime] NULL ,
	[p_expire] [datetime] NULL ,
	[p_settings] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
end
GO

-- 创建新增加的表

CREATE TABLE [dbo].[ai_AdInfoClass] (
	[cl_id] [int] IDENTITY (1, 1) NOT NULL ,
	[cl_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[cl_areacode] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ai_adinfo] (
	[ai_id] [int] IDENTITY (1, 1) NOT NULL ,
	[ai_title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[ai_content] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[ai_image] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[ai_datebegin] [datetime] NOT NULL ,
	[ai_dateend] [datetime] NOT NULL ,
	[ai_checked] [int] NOT NULL ,
	[ai_adinfoclassid] [int] NOT NULL ,
	[ai_userid] [int] NOT NULL ,
	[ai_countview] [int] NOT NULL ,
	[ai_countcomplaint] [int] NOT NULL ,
	[ai_linkman] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[ai_address] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[ai_telephone] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[ai_email] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[ai_sort] [int] NOT NULL ,
	[ai_istop] [int] NOT NULL ,
	[ai_areacode] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ai_memo] [text] COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[ai_complaintmessage] (
	[cm_id] [int] IDENTITY (1, 1) NOT NULL ,
	[cm_title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[cm_content] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[cm_username] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[cm_userid] [int] NOT NULL ,
	[cm_adinfoid] [int] NOT NULL ,
	[cm_email] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[cm_telephone] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[cm_areacode] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[cm_date] [datetime] NOT NULL ,
	[cm_ip] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[cm_linkway] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[cm_type] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[ai_leavemessage] (
	[lm_id] [int] IDENTITY (1, 1) NOT NULL ,
	[lm_title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[lm_content] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[lm_username] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[lm_userid] [int] NOT NULL ,
	[lm_adinfoid] [int] NOT NULL ,
	[lm_email] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[lm_linkway] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[lm_type] [int] NOT NULL ,
	[lm_areacode] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[lm_state] [int] NOT NULL ,
	[lm_date] [datetime] NOT NULL ,
	[lm_contentreply] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[lm_datereply] [datetime] NOT NULL ,
	[lm_ip] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[lm_usernamereply] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[mp_meetingplace] (
	[mp_id] [int] IDENTITY (1, 1) NOT NULL ,
	[mp_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[mp_date] [datetime] NOT NULL ,
	[sc_id] [int] NULL ,
	[mp_linkman] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[mp_telephone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[mp_address] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[mp_state] [int] NOT NULL ,
	[mp_memo] [text] COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[mp_scene] (
	[sc_id] [int] IDENTITY (1, 1) NOT NULL ,
	[sc_name] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[sc_description] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[sc_linkman] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[sc_telephone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[sc_address] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[sc_img] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[sc_memo] [text] COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[mp_showposition] (
	[sp_id] [int] IDENTITY (1, 1) NOT NULL ,
	[sc_id] [int] NOT NULL ,
	[sp_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[sp_description] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[sp_positionX] [int] NOT NULL ,
	[sp_positionY] [int] NOT NULL ,
	[sp_width] [int] NOT NULL ,
	[sp_height] [int] NOT NULL ,
	[sp_price] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[mp_showposition_order] (
	[so_id] [int] IDENTITY (1, 1) NOT NULL ,
	[sc_id] [int] NOT NULL ,
	[sp_id] [int] NOT NULL ,
	[mp_id] [int] NOT NULL ,
	[so_linkman] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[so_telephone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[so_address] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[so_company] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[so_state] [int] NOT NULL ,
	[so_date] [datetime] NOT NULL ,
	[so_memo] [text] COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ai_AdInfoClass] WITH NOCHECK ADD 
	CONSTRAINT [PK_ai_AdInfoClass] PRIMARY KEY  CLUSTERED 
	(
		[cl_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ai_adinfo] WITH NOCHECK ADD 
	CONSTRAINT [PK_ai_adinfo] PRIMARY KEY  CLUSTERED 
	(
		[ai_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ai_complaintmessage] WITH NOCHECK ADD 
	CONSTRAINT [PK_ai_complainmessage] PRIMARY KEY  CLUSTERED 
	(
		[cm_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ai_leavemessage] WITH NOCHECK ADD 
	CONSTRAINT [PK_ai_leavemessage] PRIMARY KEY  CLUSTERED 
	(
		[lm_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[mp_meetingplace] WITH NOCHECK ADD 
	CONSTRAINT [PK_mp_meetingplace] PRIMARY KEY  CLUSTERED 
	(
		[mp_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[mp_scene] WITH NOCHECK ADD 
	CONSTRAINT [PK_mp_scene] PRIMARY KEY  CLUSTERED 
	(
		[sc_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[mp_showposition] WITH NOCHECK ADD 
	CONSTRAINT [PK_mp_showposition] PRIMARY KEY  CLUSTERED 
	(
		[sp_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[mp_showposition_order] WITH NOCHECK ADD 
	CONSTRAINT [PK_mp_showposition_order] PRIMARY KEY  CLUSTERED 
	(
		[so_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ai_adinfo] WITH NOCHECK ADD 
	CONSTRAINT [DF_ai_adinfo_ai_datebegin] DEFAULT (getdate()) FOR [ai_datebegin],
	CONSTRAINT [DF_ai_adinfo_ai_dateend] DEFAULT (getdate()) FOR [ai_dateend],
	CONSTRAINT [DF_ai_adinfo_ai_checked] DEFAULT (0) FOR [ai_checked],
	CONSTRAINT [DF_ai_adinfo_ai_adinfoclassid] DEFAULT (0) FOR [ai_adinfoclassid],
	CONSTRAINT [DF_ai_adinfo_ai_userid] DEFAULT (0) FOR [ai_userid],
	CONSTRAINT [DF_ai_adinfo_ai_countview] DEFAULT (0) FOR [ai_countview],
	CONSTRAINT [DF_ai_adinfo_ai_countcomplaint] DEFAULT (0) FOR [ai_countcomplaint],
	CONSTRAINT [DF_ai_adinfo_ai_sort] DEFAULT (0) FOR [ai_sort],
	CONSTRAINT [DF_ai_adinfo_ai_istop] DEFAULT (0) FOR [ai_istop]
GO

ALTER TABLE [dbo].[ai_complaintmessage] WITH NOCHECK ADD 
	CONSTRAINT [DF_ai_complainmessage_cm_userid] DEFAULT (0) FOR [cm_userid],
	CONSTRAINT [DF_ai_complainmessage_cm_adinfoid] DEFAULT (0) FOR [cm_adinfoid],
	CONSTRAINT [DF_ai_complainmessage_cm_date] DEFAULT (getdate()) FOR [cm_date],
	CONSTRAINT [DF_ai_complainmessage_cm_type] DEFAULT (0) FOR [cm_type]
GO

ALTER TABLE [dbo].[ai_leavemessage] WITH NOCHECK ADD 
	CONSTRAINT [DF_ai_leavemessage_lm_userid] DEFAULT (0) FOR [lm_userid],
	CONSTRAINT [DF_ai_leavemessage_lm_adinfoid] DEFAULT (0) FOR [lm_adinfoid],
	CONSTRAINT [DF_ai_leavemessage_lm_type] DEFAULT (0) FOR [lm_type],
	CONSTRAINT [DF_ai_leavemessage_lm_state] DEFAULT (0) FOR [lm_state],
	CONSTRAINT [DF_ai_leavemessage_lm_date] DEFAULT (getdate()) FOR [lm_date],
	CONSTRAINT [DF_ai_leavemessage_lm_datereply] DEFAULT (getdate()) FOR [lm_datereply]
GO

ALTER TABLE [dbo].[mp_meetingplace] WITH NOCHECK ADD 
	CONSTRAINT [DF_mp_meetingplace_mp_date] DEFAULT (getdate()) FOR [mp_date],
	CONSTRAINT [DF_mp_meetingplace_sc_id] DEFAULT (0) FOR [sc_id],
	CONSTRAINT [DF_mp_meetingplace_mp_state] DEFAULT (0) FOR [mp_state]
GO

ALTER TABLE [dbo].[mp_showposition] WITH NOCHECK ADD 
	CONSTRAINT [DF_mp_showposition_sc_id] DEFAULT (0) FOR [sc_id],
	CONSTRAINT [DF_mp_showposition_sp_positionX] DEFAULT (0) FOR [sp_positionX],
	CONSTRAINT [DF_mp_showposition_sp_positionY] DEFAULT (0) FOR [sp_positionY],
	CONSTRAINT [DF_mp_showposition_sp_width] DEFAULT (0) FOR [sp_width],
	CONSTRAINT [DF_mp_showposition_sp_height] DEFAULT (0) FOR [sp_height],
	CONSTRAINT [DF_mp_showposition_sp_price] DEFAULT (0) FOR [sp_price]
GO

ALTER TABLE [dbo].[mp_showposition_order] WITH NOCHECK ADD 
	CONSTRAINT [DF_mp_showposition_order_sc_id] DEFAULT (0) FOR [sc_id],
	CONSTRAINT [DF_mp_showposition_order_sp_id] DEFAULT (0) FOR [sp_id],
	CONSTRAINT [DF_mp_showposition_order_mp_id] DEFAULT (0) FOR [mp_id],
	CONSTRAINT [DF_mp_showposition_order_so_state] DEFAULT (0) FOR [so_state],
	CONSTRAINT [DF_mp_showposition_order_so_date] DEFAULT (getdate()) FOR [so_date]
GO

ALTER TABLE [dbo].[rc_company] DROP CONSTRAINT [PK_rc_company]
ALTER TABLE [dbo].[rc_company] WITH NOCHECK ADD 
	CONSTRAINT [PK_rc_company] PRIMARY KEY  NONCLUSTERED 
	(
		[c_id]
	)  ON [PRIMARY] 
GO

 CREATE  CLUSTERED  INDEX [IX_rc_company] ON [dbo].[rc_company]([c_lastjobdate] DESC ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[rc_person] DROP CONSTRAINT [PK_rc_person]
ALTER TABLE [dbo].[rc_person] WITH NOCHECK ADD 
	CONSTRAINT [DF_rc_person_p_countview] DEFAULT (0) FOR [p_countview],
	CONSTRAINT [DF_rc_person_p_state] DEFAULT (0) FOR [p_state],
	CONSTRAINT [DF_rc_person_p_update] DEFAULT (getdate()) FOR [p_update],
	CONSTRAINT [PK_rc_person] PRIMARY KEY  NONCLUSTERED 
	(
		[p_id]
	)  ON [PRIMARY] 
GO

 CREATE  CLUSTERED  INDEX [IX_rc_person] ON [dbo].[rc_person]([p_update] DESC ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[rc_user] DROP CONSTRAINT [PK_rc_user]
ALTER TABLE [dbo].[rc_user] WITH NOCHECK ADD 
	CONSTRAINT [PK_rc_user] PRIMARY KEY  CLUSTERED 
	(
		[u_id]
	)  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.sp_ApplyForAJob
(
  @pUID int,              -- 申请职位的个人ID
  @cUID int,              -- 职位的企业的ID(是企业表的ID)
  @comName varchar(100),  -- 公司名称
  @jID int,               -- 职位的ID
  @jobTitle varchar(100), -- 职位名称
  @result int output      -- 处理结果(-1=出错，1=正常，2=已经应聘该职位了)
)
AS

-- 检查是否重复应聘
  declare @tmp int
  SELECT @tmp=pa_id FROM rc_per_apply WHERE u_id_per=@pUID AND u_id_com=@cUID AND j_id=@jID
  if @tmp <> NULL
  BEGIN
    set @result = 2
    return
  END

-- 简历资料，是否允许过期简历投出？
  declare @telephone varchar(100)
  SELECT @telephone=p_telephone FROM rc_person WHERE u_id=@pUID -- AND (pr_expire>getdate())
  if @telephone IS NULL
  BEGIN
    set @result = -1
    return
  END

-- 开始记录应聘数据
  BEGIN TRANSACTION ApplyForAJob

-- 删除重复的应聘
  DELETE FROM rc_com_apply WHERE u_id_com=@cUID AND u_id_per=@pUID AND j_id=@jID

-- 公司收到应聘，职位增加点击，个人增加历史记录
1234

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -