我看了很多关于在循环中重复使用Select语句的东西,但是我仍然有些困难,因为我到目前为止还没有找到清楚的解释。我想要执行一些查询(Select查询)多次,就像在FOR循环中一样。请问有人可以给出一些示例吗?
我看了很多关于在循环中重复使用Select语句的东西,但是我仍然有些困难,因为我到目前为止还没有找到清楚的解释。我想要执行一些查询(Select查询)多次,就像在FOR循环中一样。请问有人可以给出一些示例吗?
DECLARE
l_output NUMBER;
BEGIN
FOR i IN 1..10 LOOP
SELECT 1
INTO l_output
FROM dual;
DBMS_OUTPUT.PUT_LINE('Result: ' || l_output);
END LOOP;
END;
PS:如果您需要在SQL*Plus中启用输出功能,则可能需要运行以下命令:
SET SERVEROUTPUT ON
要将结果插入到另一个表中:
DECLARE
-- Store the SELECT query in a cursor
CURSOR l_cur IS SELECT SYSDATE DT FROM DUAL;
--Create a variable that will hold each result from the cursor
l_cur_rec l_cur%ROWTYPE;
BEGIN
-- Open the Cursor so that we may retrieve results
OPEN l_cur;
LOOP
-- Get a result from the SELECT query and store it in the variable
FETCH l_cur INTO l_cur_rec;
-- EXIT the loop if there are no more results
EXIT WHEN l_cur%NOTFOUND;
-- INSERT INTO another table that has the same structure as your results
INSERT INTO a_table VALUES l_cur_rec;
END LOOP;
-- Close the cursor to release the memory
CLOSE l_cur;
END;
要创建结果的视图,请参见以下示例:
CREATE VIEW scott.my_view AS
SELECT * FROM scott.emp;
使用视图查看您的结果:
SELECT * FROM scott.my_view;