sql execute的利用方式与实例教程

模块被标志为 EXECUTE AS CALLE奥德赛,况且您对目的具有相应权限。

商业事务函数 (举例 SUM卡塔尔(قطر‎ 常常要求增多 GROUP BY 语句。GROUP BY 语句GROUP BY
语句用于结合合计函数,依据一个或多个列对结果集举办分组。

运作 EXECUTE 语句无需权限。不过,必要对 EXECUTE
字符串内引用的安全目的具备权限。比如,若是字符串满含 INSERT 语句,则
EXECUTE 语句的调用方对目的表必需有所 INSERT 权限。在境遇 EXECUTE
语句时,即便 EXECUTE 语句满含于模块内,也将检查权限。

SELECT Customer,OrderDate,SUM(OrderPrice) FROM OrdersGROUP BY
Customer,OrderDate

Execute a stored procedure or function[ { EXEC | EXECUTE } ] { [
@return_status = ] { module_name [ ;number ] | @module_name_var
} [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT
] } ] [ ,…n ] [ WITH RECOMPILE ] }[;]

Execute a character string{ EXEC | EXECUTE } ( { @string_variable |
[ N ]’tsql_string’ } [ + …n ] ) [ AS { LOGIN | USER } = ‘ name
‘ ][;]

Execute a pass-through command against a linked server{ EXEC | EXECUTE
} ( { @string_variable | [ N ] ‘command_string [ ? ]’ } [ +
…n ] [ { , { value | @variable [ OUTPUT ] } } [ …n ] ] ) [
AS { LOGIN | USER } = ‘ name ‘ ] [ AT linked_server_name ][;]

SQL GROUP BY 语法SELECT column_name,
aggregate_function(column_name)FROM table_nameWHERE column_name
operator valueGROUP BY column_nameSQL GROUP BY 实例大家全部上面那些Orders 表:

SQL Server 扩充了 EXECUTE
语句,以使其可用以向链接服务器发送传递命令。别的,还是能显式设置实行字符串或指令的上下文。看三个实例使用excute

我们想要使用 GROUP BY 语句对顾客扩充重新整合。

模块被标识为 EXECUTE AS user_name,并且 user_name 对指标具有相应权限。

那正是说为何不能够动用方面那条 SELECT 语句呢?解释如下:上边的 SELECT
语句钦赐了两列。SUM(Order普赖斯卡塔尔(قطر‎ 重返三个独立的值,而 Customer 重返 6
个值。因而,大家得不到科学的结果。不过,您已经看见了,GROUP BY
语句扼杀了这一个主题素材。GROUP BY 叁个之上的列我们也得以对二个上述的列应用
GROUP BY 语句,就疑似这么:

模块的 EXECUTE
权限暗中认可赋予该模块的主人,该全体者可以将此权限转让给其他客商。当运维一个实践字符串的模块时,系统会在施行该模块的顾客上下文中并不是在成立该模块的客商上下文中检查权限。但是,若是豆蔻梢头致客户具有调用模块和被调用模块,则不对世世代代施行EXECUTE 权限检查

当今,大家目的在于物色每一个客商的总金额。

调用三个经过

让大家看一下假诺省略 GROUP BY 会现身什么样情状:

create table employee(2 ID int,3 name nvarchar (10),4 salary int,5
start_date datetime,6 city nvarchar (10),7 region char (1))8 GO12
insert into employee (ID, name, salary, start_date, city, region3
values (1, ‘Jason’, 40420, ’02/01/94′, ‘New York’, ‘W’)4 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region2 values (2, ‘Robert’,14420, ’01/02/95′,
‘Vancouver’,’N’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region2 values (3, ‘Celia’, 24020, ’12/03/96′,
‘Toronto’, ‘W’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region2 values (4, ‘Linda’, 40620, ’11/04/97′, ‘New
York’, ‘N’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region2 values (5, ‘David’, 80026, ’10/05/98′,
‘Vancouver’,’W’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region2 values (6, ‘James’, 70060, ’09/06/99′,
‘Toronto’, ‘N’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region2 values (7, ‘Alison’,90620, ’08/07/00′, ‘New
York’, ‘W’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region2 values (8, ‘Chris’, 26020, ’07/08/01′,
‘Vancouver’,’N’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region2 values (9, ‘Mary’, 60020, ’06/09/02′,
‘Toronto’, ‘W’)3 GO

(1 rows affected)12 select * from employee3 GOID name salary
start_date city region———– ———- ———–
———————– ———- —— 1 Jason 40420 1994-02-01
00:00:00.000 New York W 2 Robert 14420 1995-01-02 00:00:00.000
Vancouver N 3 Celia 24020 1996-12-03 00:00:00.000 Toronto W 4 Linda
40620 1997-11-04 00:00:00.000 New York N 5 David 80026 1998-10-05
00:00:00.000 Vancouver W 6 James 70060 1999-09-06 00:00:00.000 Toronto
N 7 Alison 90620 2000-08-07 00:00:00.000 New York W 8 Chris 26020
2001-07-08 00:00:00.000 Vancouver N 9 Mary 60020 2002-06-09
00:00:00.000 Toronto W

(9 rows affected)12 — example to execute the store procedure –
valid34 IF EXISTS (SELECT name5 FROM sysobjects6 WHERE name =
N’sp_Output_Salary’7 AND type = ‘P’)8 DROP PROCEDURE
sp_Output_Salary9 GO12 CREATE PROCEDURE sp_Output_Salary3 @ID
int,4 @OutSalary money OUTPUT5 AS6 SELECT @OutSalary = salary7 FROM
employee8 WHERE Id = @ID910 IF @@ROWCOUNT = 111 RETURN12 SET
@OutSalary = 013 RETURN 114 GO123 GRANT EXECUTE ON sp_Output_Salary
TO PUBLIC4 GO123 DECLARE @myMoney money4 DECLARE @Ret_Status int5
EXECUTE @Ret_Status = sp_Output_Salary 1, @myMoney OUTPUT6 SELECT
@myMoney7 SELECT @Ret_Status8 GO

——————— 40420.0000

(1 rows affected)

———– 0

(1 rows affected)12 drop table employee3 GO1

SELECT Customer,SUM(OrderPrice卡塔尔国 FROM Orders结果集雷同那样:

试行 Transact-SQL
批中的命令字符串、字符串或实践下列模块之豆蔻年华:系统存款和储蓄进程、顾客定义存款和储蓄进程、标量值客户定义函数或扩大存款和储蓄进度。

上边的结果集不是我们供给的。

发表评论

电子邮件地址不会被公开。 必填项已用*标注