{"id":23005,"date":"2024-03-16T00:41:38","date_gmt":"2024-03-16T00:41:38","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/"},"modified":"2024-03-22T00:13:57","modified_gmt":"2024-03-22T00:13:57","slug":"how-to-iterate-through-each-row-of-a-table-in-oracle","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/","title":{"rendered":"How to iterate through each row of a table in Oracle?"},"content":{"rendered":"<p>In Oracle, Cursors can be used to iterate through each record in a table. Below is an example code that loops through each record in a table.<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">DECLARE<\/span>\r\n  <span class=\"hljs-comment\">-- \u58f0\u660e\u6e38\u6807\u53d8\u91cf<\/span>\r\n  <span class=\"hljs-keyword\">CURSOR<\/span> cur_data <span class=\"hljs-keyword\">IS<\/span>\r\n    <span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-operator\">*<\/span> <span class=\"hljs-keyword\">FROM<\/span> your_table; <span class=\"hljs-comment\">-- \u5c06 your_table \u66ff\u6362\u4e3a\u4f60\u8981\u64cd\u4f5c\u7684\u8868\u540d<\/span>\r\n  \r\n  <span class=\"hljs-comment\">-- \u58f0\u660e\u53d8\u91cf\u6765\u5b58\u50a8\u53d6\u51fa\u7684\u6570\u636e<\/span>\r\n  <span class=\"hljs-comment\">-- \u6839\u636e\u8868\u7684\u7ed3\u6784\u5b9a\u4e49\u5bf9\u5e94\u7684\u53d8\u91cf\u7c7b\u578b\u548c\u540d\u79f0<\/span>\r\n  <span class=\"hljs-comment\">-- \u4f8b\u5982\uff0c\u5982\u679c\u8868\u4e2d\u6709\u4e24\u4e2a\u5217\uff0c\u5206\u522b\u662f id\uff08\u6574\u6570\u7c7b\u578b\uff09\u548c name\uff08\u5b57\u7b26\u4e32\u7c7b\u578b\uff09\uff0c\u5219\u5b9a\u4e49\u4e24\u4e2a\u53d8\u91cf<\/span>\r\n  v_id your_table.id<span class=\"hljs-operator\">%<\/span>TYPE;\r\n  v_name your_table.name<span class=\"hljs-operator\">%<\/span>TYPE;\r\n<span class=\"hljs-keyword\">BEGIN<\/span>\r\n  <span class=\"hljs-comment\">-- \u6253\u5f00\u6e38\u6807<\/span>\r\n  <span class=\"hljs-keyword\">OPEN<\/span> cur_data;\r\n  \r\n  <span class=\"hljs-comment\">-- \u5faa\u73af\u53d6\u51fa\u6570\u636e<\/span>\r\n  LOOP\r\n    <span class=\"hljs-comment\">-- \u4f7f\u7528 FETCH \u8bed\u53e5\u4ece\u6e38\u6807\u4e2d\u53d6\u51fa\u4e00\u884c\u6570\u636e\uff0c\u5e76\u5c06\u6570\u636e\u5b58\u50a8\u5230\u58f0\u660e\u7684\u53d8\u91cf\u4e2d<\/span>\r\n    <span class=\"hljs-keyword\">FETCH<\/span> cur_data <span class=\"hljs-keyword\">INTO<\/span> v_id, v_name;\r\n    \r\n    <span class=\"hljs-comment\">-- \u5224\u65ad\u662f\u5426\u53d6\u51fa\u4e86\u6570\u636e<\/span>\r\n    EXIT <span class=\"hljs-keyword\">WHEN<\/span> cur_data<span class=\"hljs-operator\">%<\/span>NOTFOUND;\r\n    \r\n    <span class=\"hljs-comment\">-- \u5728\u8fd9\u91cc\u53ef\u4ee5\u5bf9\u53d6\u51fa\u7684\u6570\u636e\u8fdb\u884c\u64cd\u4f5c<\/span>\r\n    <span class=\"hljs-comment\">-- \u4f8b\u5982\uff0c\u8f93\u51fa\u6570\u636e<\/span>\r\n    DBMS_OUTPUT.PUT_LINE(<span class=\"hljs-string\">'ID: '<\/span> <span class=\"hljs-operator\">||<\/span> v_id <span class=\"hljs-operator\">||<\/span> <span class=\"hljs-string\">', Name: '<\/span> <span class=\"hljs-operator\">||<\/span> v_name);\r\n  <span class=\"hljs-keyword\">END<\/span> LOOP;\r\n\r\n  <span class=\"hljs-comment\">-- \u5173\u95ed\u6e38\u6807<\/span>\r\n  <span class=\"hljs-keyword\">CLOSE<\/span> cur_data;\r\n<span class=\"hljs-keyword\">END<\/span>;\r\n<\/code><\/pre>\n<p>The code above uses a cursor to fetch a row of data from the table and store it in the respective variables. The fetched data can then be manipulated, for example, printed to the console. The loop will continue until there is no more data to fetch, then it will exit the loop.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle, Cursors can be used to iterate through each record in a table. Below is an example code that loops through each record in a table. DECLARE &#8212; \u58f0\u660e\u6e38\u6807\u53d8\u91cf CURSOR cur_data IS SELECT * FROM your_table; &#8212; \u5c06 your_table \u66ff\u6362\u4e3a\u4f60\u8981\u64cd\u4f5c\u7684\u8868\u540d &#8212; \u58f0\u660e\u53d8\u91cf\u6765\u5b58\u50a8\u53d6\u51fa\u7684\u6570\u636e &#8212; \u6839\u636e\u8868\u7684\u7ed3\u6784\u5b9a\u4e49\u5bf9\u5e94\u7684\u53d8\u91cf\u7c7b\u578b\u548c\u540d\u79f0 &#8212; \u4f8b\u5982\uff0c\u5982\u679c\u8868\u4e2d\u6709\u4e24\u4e2a\u5217\uff0c\u5206\u522b\u662f id\uff08\u6574\u6570\u7c7b\u578b\uff09\u548c name\uff08\u5b57\u7b26\u4e32\u7c7b\u578b\uff09\uff0c\u5219\u5b9a\u4e49\u4e24\u4e2a\u53d8\u91cf v_id your_table.id%TYPE; v_name your_table.name%TYPE; BEGIN &#8212; [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-23005","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to iterate through each row of a table in Oracle? - Blog - Silicon Cloud<\/title>\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.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to iterate through each row of a table in Oracle?\" \/>\n<meta property=\"og:description\" content=\"In Oracle, Cursors can be used to iterate through each record in a table. Below is an example code that loops through each record in a table. DECLARE -- \u58f0\u660e\u6e38\u6807\u53d8\u91cf CURSOR cur_data IS SELECT * FROM your_table; -- \u5c06 your_table \u66ff\u6362\u4e3a\u4f60\u8981\u64cd\u4f5c\u7684\u8868\u540d -- \u58f0\u660e\u53d8\u91cf\u6765\u5b58\u50a8\u53d6\u51fa\u7684\u6570\u636e -- \u6839\u636e\u8868\u7684\u7ed3\u6784\u5b9a\u4e49\u5bf9\u5e94\u7684\u53d8\u91cf\u7c7b\u578b\u548c\u540d\u79f0 -- \u4f8b\u5982\uff0c\u5982\u679c\u8868\u4e2d\u6709\u4e24\u4e2a\u5217\uff0c\u5206\u522b\u662f id\uff08\u6574\u6570\u7c7b\u578b\uff09\u548c name\uff08\u5b57\u7b26\u4e32\u7c7b\u578b\uff09\uff0c\u5219\u5b9a\u4e49\u4e24\u4e2a\u53d8\u91cf v_id your_table.id%TYPE; v_name your_table.name%TYPE; BEGIN -- [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-16T00:41:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-22T00:13:57+00:00\" \/>\n<meta name=\"author\" content=\"Olivia Parker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Olivia Parker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/\"},\"author\":{\"name\":\"Olivia Parker\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9\"},\"headline\":\"How to iterate through each row of a table in Oracle?\",\"datePublished\":\"2024-03-16T00:41:38+00:00\",\"dateModified\":\"2024-03-22T00:13:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/\"},\"wordCount\":91,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/\",\"name\":\"How to iterate through each row of a table in Oracle? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-16T00:41:38+00:00\",\"dateModified\":\"2024-03-22T00:13:57+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to iterate through each row of a table in Oracle?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9\",\"name\":\"Olivia Parker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g\",\"caption\":\"Olivia Parker\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/oliviaparker\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to iterate through each row of a table in Oracle? - Blog - Silicon Cloud","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.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/","og_locale":"en_US","og_type":"article","og_title":"How to iterate through each row of a table in Oracle?","og_description":"In Oracle, Cursors can be used to iterate through each record in a table. Below is an example code that loops through each record in a table. DECLARE -- \u58f0\u660e\u6e38\u6807\u53d8\u91cf CURSOR cur_data IS SELECT * FROM your_table; -- \u5c06 your_table \u66ff\u6362\u4e3a\u4f60\u8981\u64cd\u4f5c\u7684\u8868\u540d -- \u58f0\u660e\u53d8\u91cf\u6765\u5b58\u50a8\u53d6\u51fa\u7684\u6570\u636e -- \u6839\u636e\u8868\u7684\u7ed3\u6784\u5b9a\u4e49\u5bf9\u5e94\u7684\u53d8\u91cf\u7c7b\u578b\u548c\u540d\u79f0 -- \u4f8b\u5982\uff0c\u5982\u679c\u8868\u4e2d\u6709\u4e24\u4e2a\u5217\uff0c\u5206\u522b\u662f id\uff08\u6574\u6570\u7c7b\u578b\uff09\u548c name\uff08\u5b57\u7b26\u4e32\u7c7b\u578b\uff09\uff0c\u5219\u5b9a\u4e49\u4e24\u4e2a\u53d8\u91cf v_id your_table.id%TYPE; v_name your_table.name%TYPE; BEGIN -- [&hellip;]","og_url":"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-16T00:41:38+00:00","article_modified_time":"2024-03-22T00:13:57+00:00","author":"Olivia Parker","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Olivia Parker","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/"},"author":{"name":"Olivia Parker","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9"},"headline":"How to iterate through each row of a table in Oracle?","datePublished":"2024-03-16T00:41:38+00:00","dateModified":"2024-03-22T00:13:57+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/"},"wordCount":91,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/","url":"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/","name":"How to iterate through each row of a table in Oracle? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-16T00:41:38+00:00","dateModified":"2024-03-22T00:13:57+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-to-iterate-through-each-row-of-a-table-in-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to iterate through each row of a table in Oracle?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9","name":"Olivia Parker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g","caption":"Olivia Parker"},"url":"https:\/\/www.silicloud.com\/blog\/author\/oliviaparker\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/23005","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=23005"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/23005\/revisions"}],"predecessor-version":[{"id":56958,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/23005\/revisions\/56958"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=23005"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=23005"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=23005"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}