PL / SQL函数或过程,需要帮助或建议 [英] PL/SQL functions or procedure, need help or advice
问题描述
我是PL / SQL的新手。我需要有关如何解决问题的帮助或建议。
i有函数1和2,输入只返回count(*),
现在问题是当我wane编写第三个函数或过程并调用它2.
i想要第三个函数或过程来选择表并调用函数来查看具有特定id的行数,以便稍后我可以做一些验证。
这是可能的DOTO:?
/ * function 1 * /
CREATE OR REPLACE FUNCTION total_SV_rows(val number)
RETURN number < span class =code-keyword> IS
总数:= 0 ;
BEGIN
SELECT DISTINCT count(*) into 总计
FROM TMP_SV TSV
WHERE TSV.CODE_LABEL = ' 47' || VAL;
返回总计;
END ;
/
/ * function 2 * /
CREATE 或 REPLACE FUNCTION total_IRIS_rows(val数字)
RETURN 数字 IS
总数:= 0 跨度>;
BEGIN
SELECT DISTINCT count(*) into 总计
FROM TMP_SUBPRODUCT_IRIS TSI
WHERE TSI.MSISDN = val;
返回总计;
END ;
/
/ * wane call function 1和2此处* /
创建 或替换 PROCEDURE get_TMP_SUBPRODUCT_IRIS AS
BEGIN
for i IN (
select TSI.MSISDN msisdn,TSI.PRODUCT_NAME p_name,TSI.EXT_ID_REF ref_id,TSI.ACTIVATION_DATE act_date
from TMP_SUBPRODUCT_IRIS TSI
其中 MSISDN = 97198252
)
LOOP
DBMS_OUTPUT.PUT_LINE(' write output');
end 循环;
END get_TMP_SUBPRODUCT_IRIS;
Sql Function有一些限制,而不是使用Function你可以使用Store Procedure。
Sp VS Fun [ ^ ]
这可以使用Store程序。
创建 PROCEDURE Sp1(
@ par1 varcahr( 50 )
)
开始
- - 做你的w ork
结束
创建 PROCEDURE Sp2(
@ par1 varcahr( 50 )
)
开始
- - 做你的工作
结束
创建 PROCEDURE Sp3(
@ par1 varcahr( 50 )
)
开始
声明 @ anyparams as varchar ( 50 )
set @ anyparams = ' value' - 在调用被叫SP之前设置参数值
执行 Sp2 @ anyparams - 从此处调用存储过程sp2
- - 做你的工作
结束
ref。
http://stackoverflow.com/questions/15802511/execute-a-stored-procedure-in-another-stored-procedure-in-sql-服务器 [ ^ ]
http://blog.sqlauthority .com / 2013/04/07 / sql-server-pass-one-stored-procedures-result-as-another-stored-procedures-parameter / [ ^ ]
http://www.mssqltips.com/sqlservertutorial/163/returning-stored-procedure-parameter-values-to-a-calling-stored-procedure/ [ ^ ]
hi guys.
im new on PL/SQL. i need help or advice on how to solve a problem.
i have functions 1 and 2 with a input who simply returns count(*),
now the problem is when i wane write a third function or procedure and call this 2.
i want the third function or procedure to select the table and call the functions to see how many rows with the specific id has so i can later do some validation.
is this possible DOTO:?
/* function 1 */
CREATE OR REPLACE FUNCTION total_SV_rows(val number)
RETURN number IS
total number := 0;
BEGIN
SELECT DISTINCT count(*) into total
FROM TMP_SV TSV
WHERE TSV.CODE_LABEL = '47' || val;
RETURN total;
END;
/
/* function 2 */
CREATE OR REPLACE FUNCTION total_IRIS_rows(val number)
RETURN number IS
total number := 0;
BEGIN
SELECT DISTINCT count(*) into total
FROM TMP_SUBPRODUCT_IRIS TSI
WHERE TSI.MSISDN = val;
RETURN total;
END;
/
/* wane call function 1 and 2 here */
create or replace PROCEDURE get_TMP_SUBPRODUCT_IRIS AS
BEGIN
for i IN (
select TSI.MSISDN msisdn, TSI.PRODUCT_NAME p_name, TSI.EXT_ID_REF ref_id, TSI.ACTIVATION_DATE act_date
from TMP_SUBPRODUCT_IRIS TSI
where MSISDN = 97198252
)
LOOP
DBMS_OUTPUT.PUT_LINE ('write output');
end loop;
END get_TMP_SUBPRODUCT_IRIS;
Sql Function has some limitation,instead of using Function you can use Store Procedure.
Sp VS Fun[^]
This is possible using Store procedure.
Create PROCEDURE Sp1( @par1 varcahr(50) ) begin ---do your work End Create PROCEDURE Sp2( @par1 varcahr(50) ) begin ---do your work End Create PROCEDURE Sp3( @par1 varcahr(50) ) begin declare @anyparams as varchar(50) set @anyparams ='value'-- set parameter value before calling the called SP Exec Sp2 @anyparams --calling Store procedure sp2 from this one ---do your work End
ref.
http://stackoverflow.com/questions/15802511/execute-a-stored-procedure-in-another-stored-procedure-in-sql-server[^]
http://blog.sqlauthority.com/2013/04/07/sql-server-pass-one-stored-procedures-result-as-another-stored-procedures-parameter/[^]
http://www.mssqltips.com/sqlservertutorial/163/returning-stored-procedure-parameter-values-to-a-calling-stored-procedure/[^]
这篇关于PL / SQL函数或过程,需要帮助或建议的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!