这是如何工作的pl / sql？

Please explain how is this working, Thx.

请解释这是如何工作的,Thx。

DECLARE   
CURSOR ab IS
SELECT emp.ename, emp.sal, dept.loc
FROM emp
JOIN dept ON emp.deptno = dept.deptno;
TYPE tbl_join IS TABLE OF cc%ROWTYPE;
l_table tbl_join;
BEGIN
OPEN cc;
FETCH cc BULK COLLECT INTO l_table;
CLOSE cc;
FOR indx IN
1 .. l_table.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(l_table(indx).ename);
DBMS_OUTPUT.PUT_LINE(l_table(indx).sal);
DBMS_OUTPUT.PUT_LINE(l_table(indx).loc);
END LOOP;
END;

3 个解决方案

#1

Fetch employee name, salary and location details into the CURSOR ab.

将员工姓名,薪水和位置详细信息提取到CURSOR ab中。
For every row in the cursor print the details in a loop.

对于光标中的每一行,在循环中打印细节。
The loop is terminated when it reaches the total count of rows.

当循环达到总行数时,循环终止。

For cursors, refer here

对于游标,请参阅此处

A PL/SQL cursor is used to retrieve and process zero or more rows of data. There are a number of steps to use a cursor and depending on how you implement the cursor, you can control each step or have the database perform them. A PL/SQL cursor is based on a SELECT statement, normally declared in the declaration section of a PL/SQL block. The statement is not restricted as to the columns retrieved or number of tables joined.

PL / SQL游标用于检索和处理零行或多行数据。使用游标有许多步骤,并且根据您实现游标的方式,您可以控制每个步骤或让数据库执行它们。 PL / SQL游标基于SELECT语句,通常在PL / SQL块的声明部分中声明。该语句不受限于检索的列或连接的表的数量。

This snippet is using bulk collect, see here.

此代码段正在使用批量收集,请参阅此处。

One method of fetching data is an Oracle bulk collect. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. When rows are retrieved using Oracle bulk collect, they are retrieved with only two context switches. The larger the number of rows you would like to collect with Oracle bulk collect, the more performance improvement you will see using an Oracle bulk collect.

获取数据的一种方法是Oracle批量收集。使用Oracle批量收集,PL / SQL引擎告诉SQL引擎一次收集多行并将它们放在一个集合中。在Oracle批量收集期间,SQL引擎检索所有行并将它们加载到集合中并切换回PL / SQL引擎。使用Oracle批量收集检索行时,仅使用两个上下文切换检索它们。您希望使用Oracle批量收集收集的行数越多,使用Oracle批量收集时您将看到的性能提升越多。

#2

It's pretty simple. Please see the explanation below:

这很简单。请参阅以下说明:

Declaration potion:

'ab' is declared as cursor which retrieves ename, sal & loc data when it is called. It's based on query mentioned as join query.

'ab'被声明为游标,它在调用时检索ename,sal和loc数据。它基于提到的连接查询查询。
'tbl_join' is a custom datatype which is declared as table.

'tbl_join'是一个自定义数据类型,声明为table。
'l_table' is variable declared as tbl_join custom datatype created earlier.

'l_table'是变量,声明为先前创建的tbl_join自定义数据类型。

Execution portion

Open cc means open cursor

打开cc表示打开游标
Fetch cc retrieves data based on cursor query and insert it into l_table

Fetch cc根据游标查询检索数据并将其插入l_table
close cc is closing cursor.

关闭cc正在关闭游标。
For each loop is used to iterate records of l_table. Count is used for retrieving total row of that l_table. So, loop will iterate l_table.count times.

因为每个循环用于迭代l_table的记录。 Count用于检索该l_table的总行数。因此,循环将迭代l_table.count次。
Dbms_output.put_line is to print values of the parameter passed in it. Here table record number is passed with which column value to print.

Dbms_output.put_line用于打印在其中传递的参数的值。这里传递表记录号,以及要打印的列值。
Loop ..... End Loop is the syntax for loop.

循环......结束循环是循环的语法。

#3

I think you cursor should be named 'cc' instead of 'ab'.

我认为你的光标应该被命名为'cc'而不是'ab'。

DECLARE
    CURSOR ab -- This is a corsor. A prepared statement which you can call during executing
    IS
        SELECT emp.ename, emp.sal, dept.loc
          FROM emp JOIN dept ON emp.deptno = dept.deptno;

    TYPE tbl_join IS TABLE OF cc%ROWTYPE; -- this is a table-type. Like an array of antoher type. In this example it is an array of the result of cc. 

    l_table   tbl_join; -- this is a variable of the table-type, which you can fill with data
BEGIN
    OPEN cc; -- here you open the cursor cc. I guess your cursor 'ab' (1st row) should be named 'cc' and opened here.

    FETCH cc BULK COLLECT INTO l_table; -- you read all you data from the cursor and stick it into you variable 'l_table'.

    CLOSE cc; -- you're done with you cursor, because of this you close it.

    FOR indx IN 1 .. l_table.COUNT -- a for-loop to loop through you table-variable.
    LOOP
        -- you access the elements of your table with the loop-variable 'indx'.
        DBMS_OUTPUT.PUT_LINE (l_table (indx).ename); -- you access the fiels of your element.
        DBMS_OUTPUT.PUT_LINE (l_table (indx).sal);
        DBMS_OUTPUT.PUT_LINE (l_table (indx).loc);
    END LOOP;
END;

#1

Fetch employee name, salary and location details into the CURSOR ab.

将员工姓名,薪水和位置详细信息提取到CURSOR ab中。
For every row in the cursor print the details in a loop.

对于光标中的每一行,在循环中打印细节。
The loop is terminated when it reaches the total count of rows.

当循环达到总行数时,循环终止。