没有合适的资源?快使用搜索试试~ 我知道了~
cusor的使用以及函数的创建和存储过程
需积分: 18 2 下载量 117 浏览量
2017-06-03
15:24:27
上传
评论
收藏 17KB TXT 举报
温馨提示
试读
17页
cusor的使用以及函数的创建和存储过程,包含有创建方法
资源推荐
资源详情
资源评论
cursor的使用
declare CURSOR cusor is select * from table_表名;
empInfo emp%ROWTYPE;
begin
open cursor ;//打开游标
fetch cursor into empInfo;---使游标向下一行
---判断此行是否有数据被发现
while(cursor%FOUND) LOOP
操作
fetch cusor into empInfo;//修改游标,继续向下
end loop;
end;
二:
declare cursor cusor is select from table_表名;
empInfo emp%ROWTYPE;
begin
for empInfo in cursor loop
操作
end loop;
end;
---------------------------------函数function的创建---------------
ALTER FUNCTION [dbo].[GET_SPECIAL_NAME]
(
@SPECIALTY_ID INT
)
RETURNS NVARCHAR(50)
AS
BEGIN
declare CURSOR cusor is select * from table_表名;
empInfo emp%ROWTYPE;
begin
open cursor ;//打开游标
fetch cursor into empInfo;---使游标向下一行
---判断此行是否有数据被发现
while(cursor%FOUND) LOOP
操作
fetch cusor into empInfo;//修改游标,继续向下
end loop;
end;
二:
declare cursor cusor is select from table_表名;
empInfo emp%ROWTYPE;
begin
for empInfo in cursor loop
操作
end loop;
end;
---------------------------------函数function的创建---------------
ALTER FUNCTION [dbo].[GET_SPECIAL_NAME]
(
@SPECIALTY_ID INT
)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @DATA_COUNT INT;
DECLARE @SPECIAL_NAME NVARCHAR(50) ;
SET @DATA_COUNT=(
SELECT
COUNT(1) AS DATA_COUNT
FROM
PS_EXPERT_SPECIALTY PES
WHERE
PES.SPECIALTY_ID = @SPECIALTY_ID
AND PES.PARENT_ID IS NULL);
IF @DATA_COUNT > 0
SET @SPECIAL_NAME=(
SELECT
PES.SPECIALTY_NAME
FROM
PS_EXPERT_SPECIALTY PES
WHERE
PES.SPECIALTY_ID = @SPECIALTY_ID
AND PES.PARENT_ID IS NULL);
ELSE
SET @SPECIAL_NAME=(
SELECT
PES.SPECIALTY_NAME
FROM
PS_EXPERT_SPECIALTY PES,
PS_EXPERT_SPECIALTY PES2
WHERE
PES.SPECIALTY_ID = PES2.PARENT_ID
DECLARE @SPECIAL_NAME NVARCHAR(50) ;
SET @DATA_COUNT=(
SELECT
COUNT(1) AS DATA_COUNT
FROM
PS_EXPERT_SPECIALTY PES
WHERE
PES.SPECIALTY_ID = @SPECIALTY_ID
AND PES.PARENT_ID IS NULL);
IF @DATA_COUNT > 0
SET @SPECIAL_NAME=(
SELECT
PES.SPECIALTY_NAME
FROM
PS_EXPERT_SPECIALTY PES
WHERE
PES.SPECIALTY_ID = @SPECIALTY_ID
AND PES.PARENT_ID IS NULL);
ELSE
SET @SPECIAL_NAME=(
SELECT
PES.SPECIALTY_NAME
FROM
PS_EXPERT_SPECIALTY PES,
PS_EXPERT_SPECIALTY PES2
WHERE
PES.SPECIALTY_ID = PES2.PARENT_ID
剩余16页未读,继续阅读
资源评论
qq_21131561
- 粉丝: 0
- 资源: 25
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功