功能:实现学生成绩 学分等的查询
CREATE PROCEDURE [dbo].[query_byStudentNum]
@student_num char(8)
AS
set nocount on
declare @sqlStr as nvarchar(400)
if exists(select * from Student where StudentNum=@student_num)
drop view dbo.tempview
set @sqlStr='create view dbo.tempview as select *,(Grade*CourseCredit) as TempGrade from Student
where
StudentNum='+@student_num
exec sp_executesql @sqlStr
select * from tempview order by CourseName
select CourseType,count(*) as TotalCount,sum(CourseCredit) as SumCredit,convert([decimal](4,1),
sum(TempGrade)/sum(CourseCredit)) as Average
from tempview
group by CourseType
return
GO
CREATE PROCEDURE [dbo].[query_byStudentNum]
@student_num char(8)
AS
set nocount on
declare @sqlStr as nvarchar(400)
if exists(select * from Student where StudentNum=@student_num)
drop view dbo.tempview
set @sqlStr='create view dbo.tempview as select *,(Grade*CourseCredit) as TempGrade from Student
where
StudentNum='+@student_num
exec sp_executesql @sqlStr
select * from tempview order by CourseName
select CourseType,count(*) as TotalCount,sum(CourseCredit) as SumCredit,convert([decimal](4,1),
sum(TempGrade)/sum(CourseCredit)) as Average
from tempview
group by CourseType
return
GO
解决方案 »
- 红帽+oracle 11g:数据库安装用户可以conn usr/pwd;同一个dba组的用户连接时报ora-12547:tns:lost contact
- 用sql语句实现查询出所有的每一门功课都大于75分的学生的信息?急
- 請教:怎樣在命令行下以sysdba登陸sqlplus 解決立即送分
- 不小心删除了表空间(物理上),现在数据库启动不起来,应该怎么恢复啊
- [求助]表空间为何FREE EXTENTS TOO LOW?
- import (不支持要求的字符集转换(从类型850至852)
- trunc(hire_date,'day') 函数怎么会比原来的日期差上好几天?
- 为什么我在进oms的时候看不到[数据库]目录?但在进入oem却能看到数据库
- Oracle9i Jdeveloper 中怎么连接Oracle的数据库服务器?
- PL/SQL Developer使用之郁闷
- 关于sqlplus dbms_output.putline(var)参数
- 导入导出问题
1: 不主张在存储过程里面使用DDL,
2: 尽量避免在存储过程里面返回记录集按照你这样,要先用动态的EXECUTE IMMEIATE ....
然后返回CURSOR才行.
1: 不主张在存储过程里面使用DDL,
2: 尽量避免在存储过程里面返回记录集按照你这样,要先用动态的EXECUTE IMMEIATE ....
然后返回CURSOR才行.-----------------------------
那oralce要处理一个复杂的过程,并返回一个记录集,那该怎办?ms sql中采用存储过程比较明智方法,不知道oralce如何?