如何从存储过程中获取数据表？
如何从存储过程中获取数据表？
提问于 2012-06-15 21:36:46
我有一个下面的存储过程：
create or replace
PROCEDURE "SP_UTILITIES_LOG"
(
p_utility_name IN varchar2,
p_r_object_id IN varchar2,
p_platform_name IN varchar2,
p_exported_file_path IN varchar2,
p_Is_binary IN number,
p_extraction_status IN varchar2,
p_extraction_error IN varchar2,
p_extraction_datetime IN VARCHAR2,
p_schema_name IN varchar,
p_publication_path IN varchar,
p_schema_tcm_id IN varchar,
p_component_tcm_id IN varchar,
p_component_name IN varchar,
p_loading_status IN varchar,
p_transformed_file_path IN varchar,
p_transformed_status IN varchar,
p_import_status IN varchar,
p_loading_error IN varchar,
p_transform_error IN varchar,
p_import_error IN varchar,
p_loading_datetime IN TIMESTAMP,
p_transform_datetime IN TIMESTAMP,
p_import_datetime IN TIMESTAMP
)
IS
BEGIN
IF(p_utility_name ='EXTRACTION')
THEN
BEGIN
INSERT INTO utilities_log(R_OBJECT_ID,
PLATFORM_NAME,
EXPORTED_FILE_PATH,
IS_BINARY,
EXTRACTION_STATUS,
EXTRACTION_ERROR,
EXTRACTION_DATETIME
)
VALUES(p_r_object_id, p_platform_name,p_exported_file_path, p_is_binary, p_extraction_status, p_extraction_error, p_extraction_datetime);
END;
END IF;
IF(p_utility_name ='LOADING')
THEN
BEGIN
UPDATE UTILITIES_LOG SET schema_name=p_schema_name,
publication_path= p_publication_path,
schema_tcm_id= p_schema_tcm_id,
component_tcm_id= p_component_tcm_id,
component_name= p_component_name,
loading_status= p_loading_status,
loading_error= p_loading_error,
loading_datetime= current_timestamp
WHERE
r_object_id= p_r_object_id;
END;
END IF;
IF(p_utility_name ='PRE-TRANSFORMATION')
THEN
BEGIN
SELECT exported_file_path,component_tcm_id FROM utilities_log
WHERE
platform_name= p_platform_name and loading_status=p_loading_status
and extraction_status=p_extraction_status;
END;
END IF;
IF(p_utility_name ='TRANSFORMATION')
THEN
BEGIN
UPDATE UTILITIES_LOG SET
transformed_file_path= p_transformed_file_path,
transformed_status= p_transformed_status,
transform_error= p_transform_error,
transform_datetime= current_timestamp
WHERE
exported_file_path= p_exported_file_path;
END;
END IF;
IF(p_utility_name ='RETRIEVE')
THEN
BEGIN
execute immediate 'create global temporary table temp_import as SELECT transformed_file_path,publication_path
FROM utilities_log
WHERE
platform_name= p_platform_name';
END;
END IF;
IF(p_utility_name ='IMPORTER')
THEN
BEGIN
UPDATE UTILITIES_LOG SET
import_status=p_import_status,
import_error=p_import_error,
import_datetime=current_timestamp
WHERE
publication_path= p_publication_path;
END;
END IF;
END;我收到select语句中缺少"Into“子句的错误...有什么帮助吗？谢谢
回答 3
Stack Overflow用户
回答已采纳
发布于 2012-06-15 21:52:53
执行此操作的正确方法是(从sp向c#返回一组查询结果)，即使用oracle提供的引用游标类型。您可以看到它是如何实现here和here的
编辑：
它应该看起来像这样(我这里没有oracle，但你可以很容易地检查它)：
create or replace PROCEDURE "SP_UTILITIES_LOG" (
p_utility_name IN varchar2,
p_r_object_id IN varchar2,
/* your other parameters */
p_refcur out sys_refcursor /* notice the out parameter */
)
AS
BEGIN
/* one sample from your select*/
IF(p_utility_name ='PRE-TRANSFORMATION')
THEN
BEGIN
OPERN p_refcur FOR
SELECT exported_file_path,component_tcm_id FROM utilities_log
WHERE platform_name= p_platform_name
AND loading_status=p_loading_status
AND extraction_status=p_extraction_status;
END;
END IF;
END;Stack Overflow用户
发布于 2012-06-15 21:45:24
IF(p_utility_name ='RETRIEVE') THEN
BEGIN
execute immediate 'create global temporary table temp_import
as SELECT transformed_file_path,publication_path
INTO temp_import
FROM utilities_log
WHERE platform_name= p_platform_name'
END
END IF我认为
Stack Overflow用户
发布于 2012-06-15 23:30:15
错误消息与以下内容相关：
IF(p_utility_name ='PRE-TRANSFORMATION')
THEN
BEGIN
SELECT exported_file_path,component_tcm_id FROM utilities_log
WHERE
platform_name= p_platform_name and loading_status=p_loading_status
and extraction_status=p_extraction_status;
END;
END IF;在PL/SQL块中，必须将数据选择到某个对象中，因此您需要声明一些变量，然后执行以下操作：
SELECT exported_file_path,component_tcm_id
INTO <local_exported_file_path_var>, <local_component_tcm_id_var>
FROM utilities_log
...然后，您可以在SP中的其他地方使用这些变量，您目前似乎不需要这些变量，所以您根本不确定为什么要选择这些变量。或者使用OUT参数，您可以选择它们，这样调用者就可以访问它们，但是您也没有这样做。
在Oracle中不应该动态创建临时表，因此整个概念似乎很混乱。如果您试图向调用者返回一个数据表，那么就像@YavgenyP所说的那样，看看引用游标。不过，我不太确定你想要做什么。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11051716
复制相关文章
相似问题
腾讯云开发者
