{"id":1525,"date":"2017-11-24T21:15:14","date_gmt":"2017-11-24T14:15:14","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=1525"},"modified":"2025-05-24T20:14:25","modified_gmt":"2025-05-25T03:14:25","slug":"plsql-cursor","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/","title":{"rendered":"PL\/SQL Cursor"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: In this tutorial, you will learn about the PL\/SQL cursor and its usage.<\/p>\n\n\n\n<p>A cursor is a pointer that points to the result of a query. PL\/SQL has two types of cursors:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Implicit cursors.<\/li>\n\n\n\n<li>Explicit cursors.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='implicit-cursors'>Implicit cursors <a href=\"#implicit-cursors\" class=\"anchor\" id=\"implicit-cursors\" title=\"Anchor for Implicit cursors\">#<\/a><\/h2>\n\n\n\n<p>Whenever Oracle executes an SQL statement such as <code><a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-select-into\/\">SELECT INTO<\/a><\/code>, <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-insert-into-select\/\">INSERT<\/a><\/code>, <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-update\/\">UPDATE<\/a><\/code>, and <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-delete\/\">DELETE<\/a><\/code>, it automatically creates an implicit cursor.<\/p>\n\n\n\n<p>Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor&#8217;s information and statuses, such as <code>SQL%ROWCOUNT<\/code>, <code>SQL%ISOPEN<\/code>, <code>SQL%FOUND<\/code>, and <code>SQL%NOTFOUND<\/code>.<\/p>\n\n\n\n<p>The implicit cursor is not elegant when the query returns zero or multiple rows, which causes <code>NO_DATA_FOUND<\/code> or <code>TOO_MANY_ROWS<\/code> exception respectively.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='explicit-cursors'>Explicit cursors <a href=\"#explicit-cursors\" class=\"anchor\" id=\"explicit-cursors\" title=\"Anchor for Explicit cursors\">#<\/a><\/h2>\n\n\n\n<p>An explicit cursor is a <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-select\/\">SELECT<\/a><\/code> statement declared explicitly in the declaration section of the current block or a package specification.<\/p>\n\n\n\n<p>For an explicit cursor, you have control over its execution cycle from <code>OPEN<\/code>, <code>FETCH<\/code>, and <code>CLOSE<\/code>.<\/p>\n\n\n\n<p>Oracle defines an execution cycle that executes an SQL statement and associates a cursor with it.<\/p>\n\n\n\n<p>The following illustration shows the execution cycle of an explicit cursor:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"770\" height=\"202\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/plsql-cursor.png\" alt=\"PL\/SQL Cursor\" class=\"wp-image-1527\" title=\"PL\/SQL Cursor\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/plsql-cursor.png 770w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/plsql-cursor-300x79.png 300w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/plsql-cursor-768x201.png 768w\" sizes=\"auto, (max-width: 770px) 100vw, 770px\" \/><\/figure>\n\n\n\n<p>Let&#8217;s examine each step in detail.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='declare-a-cursor'>Declare a cursor <a href=\"#declare-a-cursor\" class=\"anchor\" id=\"declare-a-cursor\" title=\"Anchor for Declare a cursor\">#<\/a><\/h3>\n\n\n\n<p>Before using an explicit cursor, you must declare it in the declaration section of a block or package as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CURSOR<\/span> cursor_name <span class=\"hljs-keyword\">IS<\/span> query;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, specify the name of the cursor after the <code>CURSOR<\/code> keyword.<\/li>\n\n\n\n<li>Second, define a query to fetch data after the <code>IS<\/code> keyword.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id='open-a-cursor'>Open a cursor <a href=\"#open-a-cursor\" class=\"anchor\" id=\"open-a-cursor\" title=\"Anchor for Open a cursor\">#<\/a><\/h3>\n\n\n\n<p>Before starting to fetch rows from the cursor, you must open it. To open a cursor, you use the following syntax:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">OPEN<\/span> cursor_name;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax, <code>cursor_name<\/code> is the name of the cursor you declare in the declaration section.<\/p>\n\n\n\n<p>When you open a cursor, Oracle parses the query, binds variables, and executes the associated SQL statement.<\/p>\n\n\n\n<p>Oracle also determines an execution plan, associates host variables and <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor-with-parameters\/\">cursor parameters<\/a> with the placeholders in the SQL statement, determines the result set, and sets the cursor to the first row in the result set.<\/p>\n\n\n\n<p>More about <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor-with-parameters\/\">parameterized cursor<\/a> in the next tutorial.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='fetch-from-a-cursor'>Fetch from a cursor <a href=\"#fetch-from-a-cursor\" class=\"anchor\" id=\"fetch-from-a-cursor\" title=\"Anchor for Fetch from a cursor\">#<\/a><\/h3>\n\n\n\n<p>The <code>FETCH<\/code> statement places the contents of the current row into variables. The syntax of <code>FETCH<\/code> statement is as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">FETCH<\/span> cursor_name <span class=\"hljs-keyword\">INTO<\/span> variable_list;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To retrieve all rows in a result set, you must fetch each row until the last one.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='closing-a-cursor'>Closing a cursor <a href=\"#closing-a-cursor\" class=\"anchor\" id=\"closing-a-cursor\" title=\"Anchor for Closing a cursor\">#<\/a><\/h3>\n\n\n\n<p>After fetching all rows, you need to close the cursor with the <code>CLOSE<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CLOSE<\/span> cursor_name;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Closing a cursor instructs Oracle to release allocated memory at an appropriate time.<\/p>\n\n\n\n<p>If you declare a cursor in an <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-anonymous-block\/\">anonymous block<\/a>, <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-procedure\/\">procedure<\/a>, or <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-function\/\">function<\/a>, the cursor will automatically be closed when the execution of these objects ends.<\/p>\n\n\n\n<p>However, you must explicitly close package-based cursors. Note that if you close a cursor that has not been opened yet, Oracle will raise an <code>INVALID_CURSOR<\/code> exception.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='explicit-cursor-attributes'>Explicit Cursor Attributes <a href=\"#explicit-cursor-attributes\" class=\"anchor\" id=\"explicit-cursor-attributes\" title=\"Anchor for Explicit Cursor Attributes\">#<\/a><\/h3>\n\n\n\n<p>A cursor has four attributes, which you can reference in the following format:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">cursor_name%<span class=\"hljs-keyword\">attribute<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>where <code>cursor_name<\/code> is the name of the explicit cursor.<\/p>\n\n\n\n<p><strong>%ISOPEN<\/strong><\/p>\n\n\n\n<p>This attribute is <code>TRUE<\/code> if the cursor is open or <code>FALSE<\/code> if it is not.<\/p>\n\n\n\n<p><strong>%FOUND<\/strong><\/p>\n\n\n\n<p>This attribute has four values:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>NULL<\/code> before the first fetch.<\/li>\n\n\n\n<li><code>TRUE<\/code> if a record was fetched successfully.<\/li>\n\n\n\n<li><code>FALSE<\/code> if no row is returned.<\/li>\n\n\n\n<li><code>INVALID_CURSOR<\/code> if the cursor is not opened.<\/li>\n<\/ul>\n\n\n\n<p><strong>%NOTFOUND<\/strong><\/p>\n\n\n\n<p>This attribute has four values:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>NULL<\/code> before the first fetch.<\/li>\n\n\n\n<li><code>FALSE<\/code> if a record was fetched successfully.<\/li>\n\n\n\n<li><code>TRUE<\/code> if no row is returned.<\/li>\n\n\n\n<li><code>INVALID_CURSOR<\/code> if the cursor is not opened.<\/li>\n<\/ul>\n\n\n\n<p><strong>%ROWCOUNT<\/strong><\/p>\n\n\n\n<p>The <code>%ROWCOUNT<\/code> attribute returns the number of rows fetched from the cursor. If the cursor is not opened, this attribute returns <code>INVALID_CURSOR<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='plsql-cursor-example'>PL\/SQL cursor example <a href=\"#plsql-cursor-example\" class=\"anchor\" id=\"plsql-cursor-example\" title=\"Anchor for PL\/SQL cursor example\">#<\/a><\/h2>\n\n\n\n<p>We&#8217;ll use the&nbsp; <code>orders<\/code> and <code>order_items<\/code> tables from the <a href=\"https:\/\/www.oracletutorial.com\/getting-started\/oracle-sample-database\/\">sample database<\/a> for the demonstration.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"374\" height=\"145\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/08\/orders_order_items_tables.png\" alt=\"Orders and Order_items tables\" class=\"wp-image-649\" title=\"Orders and Order_items tables\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/08\/orders_order_items_tables.png 374w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/08\/orders_order_items_tables-300x116.png 300w\" sizes=\"auto, (max-width: 374px) 100vw, 374px\" \/><\/figure>\n\n\n\n<p>The following statement <a href=\"https:\/\/www.oracletutorial.com\/oracle-view\/oracle-create-view\/\">creates a view<\/a> that returns the sales revenues by customers:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">VIEW<\/span> sales <span class=\"hljs-keyword\">AS<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span>\n  customer_id,\n  SUM(unit_price * quantity) total,\n  ROUND(SUM(unit_price * quantity) * <span class=\"hljs-number\">0.05<\/span>) credit\n<span class=\"hljs-keyword\">FROM<\/span>\n  order_items\n  <span class=\"hljs-keyword\">INNER<\/span> <span class=\"hljs-keyword\">JOIN<\/span> orders <span class=\"hljs-keyword\">USING<\/span> (order_id)\n<span class=\"hljs-keyword\">WHERE<\/span>\n  status = <span class=\"hljs-string\">'Shipped'<\/span>\n<span class=\"hljs-keyword\">GROUP<\/span> <span class=\"hljs-keyword\">BY<\/span>\n  customer_id;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The values of the credit column are 5% of the total sales revenues.<\/p>\n\n\n\n<p>Suppose you need to develop an <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-anonymous-block\/\">anonymous block<\/a> that:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Reset the credit limits of all customers to zero.<\/li>\n\n\n\n<li>Fetch customers sorted by sales in descending order and give them new credit limits from a budget of 1 million.<\/li>\n<\/ol>\n\n\n\n<p>The following anonymous block illustrates the logic:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">DECLARE<\/span>\n  l_budget NUMBER := <span class=\"hljs-number\">1000000<\/span>;\n   <span class=\"hljs-comment\">-- cursor<\/span>\n  <span class=\"hljs-keyword\">CURSOR<\/span> c_sales <span class=\"hljs-keyword\">IS<\/span>\n  <span class=\"hljs-keyword\">SELECT<\/span>  *  <span class=\"hljs-keyword\">FROM<\/span> sales  \n  <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> total <span class=\"hljs-keyword\">DESC<\/span>;\n   <span class=\"hljs-comment\">-- record    <\/span>\n   r_sales c_sales<span class=\"hljs-meta\">%ROWTYPE<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n\n  <span class=\"hljs-comment\">-- reset credit limit of all customers<\/span>\n  <span class=\"hljs-keyword\">UPDATE<\/span> customers <span class=\"hljs-keyword\">SET<\/span> credit_limit = <span class=\"hljs-number\">0<\/span>;\n\n  <span class=\"hljs-keyword\">OPEN<\/span> c_sales;\n\n  <span class=\"hljs-keyword\">LOOP<\/span>\n    <span class=\"hljs-keyword\">FETCH<\/span>  c_sales  <span class=\"hljs-keyword\">INTO<\/span> r_sales;\n    <span class=\"hljs-keyword\">EXIT<\/span> <span class=\"hljs-keyword\">WHEN<\/span> c_sales%NOTFOUND;\n\n    <span class=\"hljs-comment\">-- update credit for the current customer<\/span>\n    <span class=\"hljs-keyword\">UPDATE<\/span> \n        customers\n    <span class=\"hljs-keyword\">SET<\/span>  \n        credit_limit = \n            <span class=\"hljs-keyword\">CASE<\/span> <span class=\"hljs-keyword\">WHEN<\/span> l_budget &gt; r_sales.credit \n                        <span class=\"hljs-keyword\">THEN<\/span> r_sales.credit \n                            <span class=\"hljs-keyword\">ELSE<\/span> l_budget\n            <span class=\"hljs-keyword\">END<\/span>\n    <span class=\"hljs-keyword\">WHERE<\/span> \n        customer_id = r_sales.customer_id;\n\n    <span class=\"hljs-comment\">--  reduce the budget for credit limit<\/span>\n    l_budget := l_budget - r_sales.credit;\n\n    DBMS_OUTPUT.PUT_LINE( <span class=\"hljs-string\">'Customer id: '<\/span> ||r_sales.customer_id || \n<span class=\"hljs-string\">' Credit: '<\/span> || r_sales.credit || <span class=\"hljs-string\">' Remaining Budget: '<\/span> || l_budget );\n\n    <span class=\"hljs-comment\">-- check the budget<\/span>\n    <span class=\"hljs-keyword\">EXIT<\/span> <span class=\"hljs-keyword\">WHEN<\/span> l_budget &lt;= <span class=\"hljs-number\">0<\/span>;\n  <span class=\"hljs-keyword\">END<\/span> <span class=\"hljs-keyword\">LOOP<\/span>;\n\n  <span class=\"hljs-keyword\">CLOSE<\/span> c_sales;\n<span class=\"hljs-keyword\">END<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In the declaration section, we declare three <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/\">variables<\/a>.<\/p>\n\n\n\n<p>The first one is <code>l_budget<\/code> whose initial value is <code>1,000,000<\/code>.<\/p>\n\n\n\n<p>The second variable is an explicit cursor variable named <code>c_sales<\/code> whose <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-select\/\">SELECT<\/a><\/code> statement retrieves data from the <code>sales<\/code> view:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CURSOR<\/span> c_sales <span class=\"hljs-keyword\">IS<\/span>\n      <span class=\"hljs-keyword\">SELECT<\/span>  *  <span class=\"hljs-keyword\">FROM<\/span> sales  \n      <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> total <span class=\"hljs-keyword\">DESC<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The third variable is a cursor-based record named <code>c_sales<\/code>.<\/p>\n\n\n\n<p>In the execution section, we perform the following:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, reset the credit limits of all customers to zero using an <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-update\/\">UPDATE<\/a><\/code> statement.<\/li>\n\n\n\n<li>Second, open the <code>c_sales<\/code> cursor.<\/li>\n\n\n\n<li>Third, fetch each row from the cursor. We updated the credit limit and reduced the budget in each loop iteration. The loop terminates when no row is fetched or the budget is exhausted.<\/li>\n\n\n\n<li>Finally, close the cursor.<\/li>\n<\/ul>\n\n\n\n<p>The following query retrieves data from the &nbsp;<code>customers<\/code> table to verify the update:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> customer_id,\n       <span class=\"hljs-type\">name<\/span>,\n       credit_limit\n<span class=\"hljs-keyword\">FROM<\/span> customers\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> credit_limit <span class=\"hljs-keyword\">DESC<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Result:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"395\" height=\"295\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/PLSQL-Cursor-example.png\" alt=\"PL\/SQL Cursor example\" class=\"wp-image-1526\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/PLSQL-Cursor-example.png 395w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/PLSQL-Cursor-example-300x224.png 300w\" sizes=\"auto, (max-width: 395px) 100vw, 395px\" \/><\/figure>\n\n\n\n<p>The output indicates that only the first few customers have credit limits. If you sum up all credit limits, the total should be 1 million as shown follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  SUM( credit_limit )\n<span class=\"hljs-keyword\">FROM<\/span>\n  customers;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SUM(CREDIT_LIMIT)\n-----------------\n          1000000<\/code><\/span><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A cursor is a pointer that points to the result of a query.<\/li>\n\n\n\n<li>PL\/SQL supports two types of cursors: implicit and explicit cursors.<\/li>\n\n\n\n<li>Oracle automatically creates implicit cursors when executing SQL statements like <code>SELECT INTO<\/code>, <code>INSERT<\/code>, <code>UPDATE<\/code>, and <code>DELETE<\/code>.<\/li>\n\n\n\n<li>Declare explicit cursors in the declaration section of a block.<\/li>\n\n\n\n<li>Use the <code>OPEN<\/code>, <code>FETCH<\/code>, and <code>CLOSE<\/code> to control over the execution cycle.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"1525\"\n\t\t\t\tdata-post-url=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/\"\n\t\t\t\tdata-post-title=\"PL\/SQL Cursor\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"1525\"\n\t\t\t\tdata-post-url=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/\"\n\t\t\t\tdata-post-title=\"PL\/SQL Cursor\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>This tutorial introduces you to the PL\/SQL cursor and how to use it effectively to fetch data from a table.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1418,"menu_order":21,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1525","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PL\/SQL Cursor<\/title>\n<meta name=\"description\" content=\"This tutorial introduces you to the PL\/SQL cursor and how to use it effectively to fetch data from a table.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PL\/SQL Cursor\" \/>\n<meta property=\"og:description\" content=\"This tutorial introduces you to the PL\/SQL cursor and how to use it effectively to fetch data from a table.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/\" \/>\n<meta property=\"og:site_name\" content=\"Oracle Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-05-25T03:14:25+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/plsql-cursor.png\" \/>\n\t<meta property=\"og:image:width\" content=\"770\" \/>\n\t<meta property=\"og:image:height\" content=\"202\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-cursor\\\/\",\"url\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-cursor\\\/\",\"name\":\"PL\\\/SQL Cursor\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-cursor\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-cursor\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.oracletutorial.com\\\/wp-content\\\/uploads\\\/2017\\\/11\\\/plsql-cursor.png\",\"datePublished\":\"2017-11-24T14:15:14+00:00\",\"dateModified\":\"2025-05-25T03:14:25+00:00\",\"description\":\"This tutorial introduces you to the PL\\\/SQL cursor and how to use it effectively to fetch data from a table.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-cursor\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-cursor\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-cursor\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.oracletutorial.com\\\/wp-content\\\/uploads\\\/2017\\\/11\\\/plsql-cursor.png\",\"contentUrl\":\"https:\\\/\\\/www.oracletutorial.com\\\/wp-content\\\/uploads\\\/2017\\\/11\\\/plsql-cursor.png\",\"width\":770,\"height\":202,\"caption\":\"PL\\\/SQL Cursor\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-cursor\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.oracletutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PL\\\/SQL Tutorial\",\"item\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"PL\\\/SQL Cursor\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/#website\",\"url\":\"https:\\\/\\\/www.oracletutorial.com\\\/\",\"name\":\"Oracle Tutorial\",\"description\":\"Oracle Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.oracletutorial.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PL\/SQL Cursor","description":"This tutorial introduces you to the PL\/SQL cursor and how to use it effectively to fetch data from a table.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/","og_locale":"en_US","og_type":"article","og_title":"PL\/SQL Cursor","og_description":"This tutorial introduces you to the PL\/SQL cursor and how to use it effectively to fetch data from a table.","og_url":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/","og_site_name":"Oracle Tutorial","article_modified_time":"2025-05-25T03:14:25+00:00","og_image":[{"width":770,"height":202,"url":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/plsql-cursor.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/","url":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/","name":"PL\/SQL Cursor","isPartOf":{"@id":"https:\/\/www.oracletutorial.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/#primaryimage"},"image":{"@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/#primaryimage"},"thumbnailUrl":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/plsql-cursor.png","datePublished":"2017-11-24T14:15:14+00:00","dateModified":"2025-05-25T03:14:25+00:00","description":"This tutorial introduces you to the PL\/SQL cursor and how to use it effectively to fetch data from a table.","breadcrumb":{"@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/#primaryimage","url":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/plsql-cursor.png","contentUrl":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/11\/plsql-cursor.png","width":770,"height":202,"caption":"PL\/SQL Cursor"},{"@type":"BreadcrumbList","@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.oracletutorial.com\/"},{"@type":"ListItem","position":2,"name":"PL\/SQL Tutorial","item":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/"},{"@type":"ListItem","position":3,"name":"PL\/SQL Cursor"}]},{"@type":"WebSite","@id":"https:\/\/www.oracletutorial.com\/#website","url":"https:\/\/www.oracletutorial.com\/","name":"Oracle Tutorial","description":"Oracle Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.oracletutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages\/1525","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/comments?post=1525"}],"version-history":[{"count":0,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages\/1525\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages\/1418"}],"wp:attachment":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/media?parent=1525"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}