--execute add_data '面向对象系统分析与设计实验信计201','面向对象系统分析与设计实验信计201','101001' create proc add_data @course_id varchar(200),@table_name varchar(200),@teacher_id varchar(200) as if exists(select * from syscursors where cursor_name='prj_Cursor') deallocate prj_Cursor declare @str_sql varchar(1024); declare @num int; SELECT @num=count(NAME)-3 FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@table_name); set @str_sql=CONCAT('insert into tb_Course(course_id,course_name,num_of_project,table_name,teacher_id)values(', '''', @course_id, ''',', '''', left(@course_id,len(@course_id)-3), ''',', cast(@num as varchar), ',''', @table_name, ''',''', @teacher_id, ''')'); print @str_sql EXEC(@str_sql)--课程信息插入到tb_Course DECLARE prj_Cursor SCROLL CURSOR for SELECT NAME FROM sys.columns WHERE [object_id]=OBJECT_ID(@table_name) order by column_id; OPEN prj_Cursor declare @prj varchar(128) FETCH ABSOLUTE 4 FROM prj_Cursor into @prj while (@@fetch_status=0) begin--把实验项目加入到tb_Project表 print(@prj) set @str_sql='insert into tb_Project(project_id,project_name,course_id,project_open) values('''+ right(@course_id,3)+@prj+''','+ ''''+@prj+''','''+@course_id+''','+'0'+')' print @str_sql EXEC(@str_sql)--项目信息插入到tb_Project FETCH next FROM prj_Cursor into @prj end set @str_sql=CONCAT('insert into tb_User(user_name,user_password,full_name) select ', 'SNO', ',substring(sys.fn_sqlvarbasetostr(HASHBYTES(''MD5'',','SNO)),3,32),Sname', ' from ', @table_name, ' where SNO not in(select user_name from tb_User)' ); --如果字段的类型是nvarchar的,md5加密前需要强制转化一下类型CAST([SNO] as varchar(50)) print @str_sql EXEC(@str_sql)--学生账号插入到tb_User set @str_sql='insert into tb_SC(SNO,course_id) select SNO,''' +@course_id+ ''' from '+@table_name print @str_sql EXEC(@str_sql)--选课信息插入到tb_SC go
时间: 2023-08-05 11:09:41 浏览: 136
这段代码是用来将面向对象系统分析与设计实验信计201这门课程的信息插入到数据库中,并且创建相应的实验项目和学生账号。其中,先向tb_Course表中插入课程信息,然后通过游标遍历实验项目的字段,将每个实验项目插入到tb_Project表中。接着,将学生账号信息插入到tb_User表中,并将学生选课信息插入到tb_SC表中。
相关问题
--#建立存储过程,把数据导入到相应的表中 --execute add_data '面向对象系统分析与设计实验信计201','面向对象系统分析与设计实验信计201','101001' create proc add_data @course_id varchar(200),@table_name varchar(200),@teacher_id varchar(200) as if exists(select * from syscursors where cursor_name='prj_Cursor') deallocate prj_Cursor declare @str_sql varchar(1024); declare @num int; SELECT @num=count(NAME)-3 FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@table_name); set @str_sql=CONCAT('insert into tb_Course(course_id,course_name,num_of_project,table_name,teacher_id)values(', '''', @course_id, ''',', '''', left(@course_id,len(@course_id)-3), ''',', cast(@num as varchar), ',''', @table_name, ''',''', @teacher_id, ''')'); print @str_sql EXEC(@str_sql)--课程信息插入到tb_Course DECLARE prj_Cursor SCROLL CURSOR for SELECT NAME FROM sys.columns WHERE [object_id]=OBJECT_ID(@table_name) order by column_id; OPEN prj_Cursor declare @prj varchar(128) FETCH ABSOLUTE 4 FROM prj_Cursor into @prj while (@@fetch_status=0) begin--把实验项目加入到tb_Project表 print(@prj) set @str_sql='insert into tb_Project(project_id,project_name,course_id,project_open) values('''+ right(@course_id,3)+@prj+''','+ ''''+@prj+''','''+@course_id+''','+'0'+')' print @str_sql EXEC(@str_sql)--项目信息插入到tb_Project FETCH next FROM prj_Cursor into @prj end set @str_sql=CONCAT('insert into tb_User(user_name,user_password,full_name) select ', 'SNO', ',substring(sys.fn_sqlvarbasetostr(HASHBYTES(''MD5'',','SNO)),3,32),Sname', ' from ', @table_name, ' where SNO not in(select user_name from tb_User)' ); --如果字段的类型是nvarchar的,md5加密前需要强制转化一下类型CAST([SNO] as varchar(50)) print @str_sql EXEC(@str_sql)--学生账号插入到tb_User set @str_sql='insert into tb_SC(SNO,course_id) select SNO,''' +@course_id+ ''' from '+@table_name print @str_sql EXEC(@str_sql)--选课信息插入到tb_SC go 怎么分段写入SQL
在 SQL Server 中,可以使用 GO 命令将 SQL 脚本分段执行。GO 命令用于将脚本中的多个批次分隔开来,每个批次之间用 GO 分隔符分隔。例如,你可以将存储过程的创建脚本和执行脚本分别放在两个批次中,然后用 GO 分隔符分隔开来,如下所示:
```
-- 创建存储过程
CREATE PROCEDURE add_data
@course_id varchar(200),
@table_name varchar(200),
@teacher_id varchar(200)
AS
BEGIN
-- 存储过程代码
END
GO
-- 执行存储过程
EXEC add_data '面向对象系统分析与设计实验信计201','面向对象系统分析与设计实验信计201','101001'
GO
```
注意,GO 命令不能作为 T-SQL 语句的一部分,它只是 SQL Server Management Studio 和 sqlcmd 工具中的一个批处理命令。在使用 GO 命令时,要确保每个批次的语法都是正确的,否则会导致执行失败。
def add_audit_columns(self, dataframe, write_params): try: print(write_params) audit_source_sys_name = write_params.get("audit_src_sys_name", "") or write_params.get( "table_full_name") or write_params.get("data_source_name", "") print("audit_source_sys_name - " + audit_source_sys_name) if ("audit_src_sys_name".upper() in (name.upper() for name in dataframe.columns)): print("audit_src_sys_name column is present. Adding present layer details....") audit_dataframe = DataSink_with_audit(self.spark).add_audit_col_append(dataframe, audit_source_sys_name, write_params) print("Tables is updated.") else: print("Adding column - audit_src_sys_name.") audit_dataframe = DataSink_with_audit(self.spark).add_audit_col_fill(dataframe, audit_source_sys_name, write_params) print("audit_src_sys_name added.") print("Added Audit Cols") return audit_dataframe except Exception as e: raise Exception("job failed with error {}".format(e))
根据您提供的Python函数,我为您编写了一个MySQL存储过程的示例,但是需要根据您的具体需求进行修改和调整。这个存储过程的功能是向数据表中添加一个名为audit_src_sys_name的列,同时向这个列中填充相应的值。
```
CREATE PROCEDURE add_audit_columns(
IN df_column_names VARCHAR(255),
IN df_values VARCHAR(255),
IN write_params TEXT,
OUT audit_dataframe TEXT
)
BEGIN
DECLARE audit_source_sys_name VARCHAR(255);
DECLARE column_present BOOLEAN;
DECLARE sql_stmt TEXT;
SET audit_source_sys_name = JSON_EXTRACT(write_params, '$.audit_src_sys_name');
IF audit_source_sys_name IS NULL OR audit_source_sys_name = '' THEN
SET audit_source_sys_name = JSON_EXTRACT(write_params, '$.table_full_name');
END IF;
SET column_present = FALSE;
SET sql_stmt = CONCAT('SELECT COUNT(*) INTO @exists FROM information_schema.columns WHERE table_name = \'', df_column_names, '\' AND column_name = \'audit_src_sys_name\'');
PREPARE stmt FROM sql_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @exists > 0 THEN
SET column_present = TRUE;
SET sql_stmt = CONCAT('UPDATE ', df_column_names, ' SET audit_src_sys_name = \'', audit_source_sys_name, '\'');
ELSE
SET sql_stmt = CONCAT('ALTER TABLE ', df_column_names, ' ADD COLUMN audit_src_sys_name VARCHAR(255) DEFAULT NULL AFTER ', df_values);
PREPARE stmt FROM sql_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
IF column_present THEN
SET audit_dataframe = CONCAT('Audit source system name column is present. Adding present layer details....\n');
SET sql_stmt = CONCAT('UPDATE ', df_column_names, ' SET audit_src_sys_name = CONCAT(audit_src_sys_name, \', \', \'', audit_source_sys_name, '\')');
ELSE
SET audit_dataframe = CONCAT('Adding column - audit_src_sys_name.\n');
SET sql_stmt = CONCAT('UPDATE ', df_column_names, ' SET audit_src_sys_name = \'', audit_source_sys_name, '\'');
END IF;
PREPARE stmt FROM sql_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET audit_dataframe = CONCAT(audit_dataframe, 'Tables is updated.\n');
END
```
这个存储过程的输入参数包括数据表的列名、值、以及一个JSON格式的字符串write_params,其中包含各种写入参数信息。输出参数是一个JSON格式的字符串audit_dataframe,包含存储过程的执行结果信息。请注意,这只是一个示例,您需要根据实际情况进行修改和调整。
阅读全文