[pl / sql]选择并显示结果的问题 [英] [pl/sql] Problem of select and display the result

问题描述

大家好，

i有一个问题，我不知道如何解决它。

我创建了一个这样的程序：


创建或替换

程序Employee_LoadById

（

p_Id NVARCHAR2

）

AS

BEGIN

EXECUTE IMMEDIATE''SELECT DISTINCT" Employee"。" Id"，" Employee"。" Name"，" ; Employee"。" LastWriteTime"，" Employee"。" CreationTime"

WHERE" Employee"。" Id" =：1''使用p_Id;

END;


我的问题是：

1-我试图创建这个程序没有命令EXECUTE IMMEDIATE，但我有一个错误，因为pl / sql不允许在没有此关键字或没有Select INTO的情况下编写select语句。

这就是为什么我使用关键词EXECUTE IMMEDIATE。

我想知道这是一个很好的方法来做或不做这样的程序（加载表中的数据）。 />
2-使用命令编译或执行此过程没有问题。 CALL ......或EXEC ......或并且它分别显示Appelterminé和Appelterminé。 =通话结束或 ProcédurePL/SQLterminéavecsuccès" =PL / SQL过程成功完成。

此过程不会显示结果（表中的行）。

它有一个我的程序是否存在问题。


我在互联网上搜索我的大部分研究结果，它使用没有命令执行立即的select语句。那么有人可以给我任何想法吗？

希望能收到一些关于这个问题的回复，我非常感谢提前。

祝你有愉快的一天。

Hello everybody,
i have a problem that i don''t know how to solve it.
I created a procedure like this:

create or replace
PROCEDURE Employee_LoadById
(
p_Id NVARCHAR2
)
AS
BEGIN
EXECUTE IMMEDIATE ''SELECT DISTINCT "Employee"."Id", "Employee"."Name", "Employee"."LastWriteTime", "Employee"."CreationTime"
FROM "Employee"
WHERE "Employee"."Id" = :1'' USING p_Id;
END;

My questions are:
1- I tried to create this procedure without the command "EXECUTE IMMEDIATE", but i have an error because the pl/sql don''t allow to write the select statement without this key word or without the "Select INTO".
That why i use the key word "EXECUTE IMMEDIATE".
I want to know it''s a good way to do or not for such procedure (to load the data in the table).
2- This procedure is no problem to compile or to exec by using the command " CALL ..." or "EXEC ..." and it display repectively that "Appel terminé" = "call finished" or " Procédure PL/SQL terminé avec succès" = "PL/SQL procedure finished with success".
This procedure doesn''t display the result (the rows in the table) for me.
It has a problem with my procedure or not.

I search in the internet and most of the my result''s research, it use the select statement without the command "EXECUTE IMMEDIATE". So can anyone give me any ideas?
Hope to receive some replies for this problem, i say thanks very much in advance.
Have a nice day.

推荐答案

大家好，

i有一个问题，我不知道如何解决它。

我创建了一个这样的程序：


创建或替换

程序Employee_LoadById

（

p_Id NVARCHAR2

）

AS

BEGIN

EXECUTE IMMEDIATE''SELECT DISTINCTEmployee。Id，Employee。Name，Employee。LastWriteTime，Employee。CreationTime

FROM" ;员工

WHERE员工。Id =：1''使用p_Id;

END;


我的问题是：

1-我试图创建这个程序没有命令EXECUTE IMMEDIATE，但我有一个错误，因为pl / sql不允许在没有此关键字或没有Select INTO的情况下编写select语句。

这就是为什么我使用关键词EXECUTE IMMEDIATE。

我想知道这是一个很好的方法来做或不做这样的程序（加载表中的数据）。 />
2-使用命令编译或执行此过程没有问题。 CALL ......或EXEC ......或并且它分别显示Appelterminé和Appelterminé。 =通话结束或 ProcédurePL/SQLterminéavecsuccès" =PL / SQL过程成功完成。

此过程不会显示结果（表中的行）。

它有一个我的程序是否存在问题。


我在互联网上搜索我的大部分研究结果，它使用没有命令执行立即的select语句。那么有人可以给我任何想法吗？

希望能收到一些关于这个问题的回复，我非常感谢提前。

祝你有愉快的一天。

Hello everybody,
i have a problem that i don''t know how to solve it.
I created a procedure like this:

create or replace
PROCEDURE Employee_LoadById
(
p_Id NVARCHAR2
)
AS
BEGIN
EXECUTE IMMEDIATE ''SELECT DISTINCT "Employee"."Id", "Employee"."Name", "Employee"."LastWriteTime", "Employee"."CreationTime"
FROM "Employee"
WHERE "Employee"."Id" = :1'' USING p_Id;
END;

My questions are:
1- I tried to create this procedure without the command "EXECUTE IMMEDIATE", but i have an error because the pl/sql don''t allow to write the select statement without this key word or without the "Select INTO".
That why i use the key word "EXECUTE IMMEDIATE".
I want to know it''s a good way to do or not for such procedure (to load the data in the table).
2- This procedure is no problem to compile or to exec by using the command " CALL ..." or "EXEC ..." and it display repectively that "Appel terminé" = "call finished" or " Procédure PL/SQL terminé avec succès" = "PL/SQL procedure finished with success".
This procedure doesn''t display the result (the rows in the table) for me.
It has a problem with my procedure or not.

I search in the internet and most of the my result''s research, it use the select statement without the command "EXECUTE IMMEDIATE". So can anyone give me any ideas?
Hope to receive some replies for this problem, i say thanks very much in advance.
Have a nice day.

EXECUTE IMMEDIATE命令用于执行动态SQL语句，而不是您在此处使用的语句。从你的SELECT语句，我可以说因为有很多列，你可以使用CURSOR而不是使用INTO子句。单个CURSOR将保存所有列，您可以通过Cursor循环以对每个记录执行所需的操作。

EXECUTE IMMEDIATE command is used to execute Dynamic SQL statements and not the one that you have used here. From your SELECT statement, I could say since there are many columns, you can use a CURSOR rather using INTO clause. A single CURSOR will hold all the columns and you can LOOP through Cursor to perform required operations on each record.

使用EXECUTE IMMEDIATE命令执行动态SQL语句而不是您在此处使用的语句。从你的SELECT语句，我可以说因为有很多列，你可以使用CURSOR而不是使用INTO子句。单个CURSOR将保存所有列，您可以通过Cursor循环以对每个记录执行所需的操作。

EXECUTE IMMEDIATE command is used to execute Dynamic SQL statements and not the one that you have used here. From your SELECT statement, I could say since there are many columns, you can use a CURSOR rather using INTO clause. A single CURSOR will hold all the columns and you can LOOP through Cursor to perform required operations on each record.

你好amitpatel66

非常感谢您的回答。是的，我可以使用光标。但是我的项目不是使用它，因为这个过程将与C＃编写的应用程序一起使用。

因为我们使用OracleCommand的程序并通过ODP.Net的OracleDataReader读取数据。所以我们不想在服务器（oracle）中创建光标。

你有什么建议吗？

我准备回答你所有的问题，如果我的解释不明白。


提前谢谢

ps：你可以给我一些参考资料，了解更多相关信息吗？

Hello amitpatel66
Thanks alot for your answer. Yes sure, i can use the cursor. But my project is not to use that because this procedure will be used with the application writing by C#.
Because we use the procedure by using the OracleCommand and to read the data by OracleDataReader of ODP.Net. So we don''t want to create the cursor in the server (oracle).
do you have any suggestions?
i''m ready to answer you all the questions, if my explication is not enought to understand.

Thanks in advance
ps: may you send me the reference to understand more about that?

为什么你需要使用EXECUTE IMMEDIATE。


因为你只是选择，为什么你需要一个程序。


为什么不直接执行SQL查询。

why you need to use EXECUTE IMMEDIATE for this.

and since you are only selecting ,why u need a procedure.

why not execute the SQL query directly.

这篇关于[pl / sql]选择并显示结果的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！