为什么在sql中使用函数而不是存储过程 [英] Why use functions instead of stored procedure in sql

问题描述

最近，在一次采访中，我被问到了这个问题。

为什么在sql中使用函数而不是存储过程



我回答说我们无法使用函数执行DML操作，我们只能使用它来检索数据。



但是访谈员问我，存储过程还可以检索数据然后使用功能？



我回答：不知道，我只使用程序。



我想要从你们所有人那里了解到你对这些问题的看法。



谢谢

解决方案

参考 -



1. [ ^ ]。

3. 程序和功能之间的差异 [ ^ ]。

4. 用户定义函数和存储过程之间的区别 [ ^ ]。

< blockquote>如果我正在编写一个sql表达式并且我想在该sql表达式中计算某个值，那么我可以使用一个函数。这样可以避免在sql表达式中使用数学表达式造成的混乱。



例如：



创建或替换功能CALC_TAX（P_SAL IN EMPLOYEES.SALARY％TYPE）

RETURN NUMBER

IS

BEGIN

RETURN（P_SAL * .15）;

END CALC_TAX;

/



EMPLOYEE_ID = 101的员工选择EMPLOYEE_ID，LAST_NAME，SALARY，CALC_TAX（SALARY）;



所以这里不是计算sql表达式中的税我们是在一个函数的帮助下完成的！



但是如果我想返回多个值，那么我肯定会去处理程序。

但如果我使用过程，我将无法在我的sql查询的select语句中使用它。这是因为程序是作为声明执行的。



所以在上述情况下，上述功能将是更好的选择。





然而，是否使用函数或程序完全取决于手头问题的背景。



Best好运！并告诉我们你的面试结果。



谢谢。

粗略地说，你不能调用存储过程一个 SELECT 语句。我想这可能是使用函数的原因。

Recently, in a interview, i have been asked this question.
"Why use functions instead of stored procedure in sql"

I answered we cannot perform DML operations using functions, We can use it to retrieve data only.

But Interviewer asked me, Stored procedures could also retrieve data then why use functions?

I answered : Don't know, I only use Procedures.

I would like to know from all u guys, what u think about this questions.

Thanks

解决方案

Refer -

1. Function vs. Stored Procedure in SQL Server[^].

Quote:

The difference between SP and UDF is listed below:

2. SQL SERVER – Question to You – When to use Function and When to use Stored Procedure[^].
3. Differences Between Procedures and Functions[^].
4. Difference Between User Defined Function and Stored Procedure[^].

If i am writing a sql expression and i want to compute a certain value within that sql expression then i can use a function.This would avoid the cluttering caused by using mathematical expressions within the sql expression.

eg :

CREATE OR REPLACE FUNCTION CALC_TAX (P_SAL IN EMPLOYEES.SALARY%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN (P_SAL*.15);
END CALC_TAX;
/

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,CALC_TAX(SALARY) FROM EMPLOYEES WHERE EMPLOYEE_ID = 101;

so here instead of calculating the tax within the sql expression we have done it using the help of a function!

however if i wanted to return more than one values then i would have certainly gone for procedures.
but if i used procedures i wouldn't have been able to use it within the select statement of my sql query. this is because the procedure is executed as a statement.

So in such scenarios as above functions would be a better choice.


However whether to use functions or procedures would depend purely on the context of the problem in hand.

Best of luck! and let us know the result of your interview.

Thank you.

Roughly speaking, you cannot call a stored procedure in a SELECT statement. I guess this could be a reason for using functions.

这篇关于为什么在sql中使用函数而不是存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！