{"id":1493,"date":"2017-11-18T22:02:54","date_gmt":"2017-11-18T15:02:54","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=1493"},"modified":"2025-06-09T00:09:09","modified_gmt":"2025-06-09T07:09:09","slug":"plsql-variables","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/","title":{"rendered":"PL\/SQL Variables"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn about PL\/SQL variables and how to use them effectively.<\/p>\n\n\n\n<p>In PL\/SQL, a variable is named storage location that stores a value of a particular <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-data-types\/\">data type<\/a>. The value of the variable may change through out the execution of the program. Before using a variable, you must declare it in the declaration section of a <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-anonymous-block\/\">block<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='declaring-variables'>Declaring variables <a href=\"#declaring-variables\" class=\"anchor\" id=\"declaring-variables\" title=\"Anchor for Declaring variables\">#<\/a><\/h2>\n\n\n\n<p>The syntax for a variable declaration is 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\">variable_name datatype &#91;<span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span>] &#91;:= initial_value];<\/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 variable. The name of the variable should be as descriptive as possible, such as <code>l_total_sales<\/code>, <code>l_credit_limit<\/code>, and <code>l_sales_revenue<\/code>.<\/li>\n\n\n\n<li>Second, choose an appropriate <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-data-types\/\">data type<\/a> for the variable. The data type depends on the kind of value that you want the variable to store, for example, number, character, Boolean, and datetime.<\/li>\n<\/ul>\n\n\n\n<p>By convention, local variable names should start with <code>l_<\/code> and global variable names should have a prefix of <code>g_<\/code> .<\/p>\n\n\n\n<p>The following example declares three variables <code>l_total_sales<\/code>, <code>l_credit_limit<\/code>, and <code>l_contact_name<\/code>:<\/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\">DECLARE<\/span>\n    l_total_sales NUMBER(<span class=\"hljs-number\">15<\/span>,<span class=\"hljs-number\">2<\/span>);\n    l_credit_limit NUMBER (<span class=\"hljs-number\">10<\/span>,<span class=\"hljs-number\">0<\/span>);    \n    l_contact_name VARCHAR2(<span class=\"hljs-number\">255<\/span>);\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">NULL<\/span>;\n<span class=\"hljs-keyword\">END<\/span>;<\/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<h3 class=\"wp-block-heading\" id='default-values'>Default values <a href=\"#default-values\" class=\"anchor\" id=\"default-values\" title=\"Anchor for Default values\">#<\/a><\/h3>\n\n\n\n<p>PL\/SQL allows you to set a default value for a variable at the declaration time. To assign a default value to a variable, you use the assignment operator (<code>:=<\/code>) or the <code>DEFAULT<\/code> keyword.<\/p>\n\n\n\n<p>The following example declares a variable named <code>l_product_name<\/code> with an initial value <code>'Laptop'<\/code>:<\/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\">DECLARE<\/span>\n  l_product_name VARCHAR2( <span class=\"hljs-number\">100<\/span> ) := <span class=\"hljs-string\">'Laptop'<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n  <span class=\"hljs-keyword\">NULL<\/span>;\n<span class=\"hljs-keyword\">END<\/span>;<\/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>It is equivalent to the following block:<\/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\">DECLARE<\/span> \n   l_product_name VARCHAR2(<span class=\"hljs-number\">100<\/span>) <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-string\">'Laptop'<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span> \n   <span class=\"hljs-keyword\">NULL<\/span>; \n<span class=\"hljs-keyword\">END<\/span>;<\/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>In this example, instead of using the assignment operator <code>:=<\/code> , we used the <code>DEFAULT<\/code> keyword to initialize a variable.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='not-null-constraint'>NOT NULL constraint <a href=\"#not-null-constraint\" class=\"anchor\" id=\"not-null-constraint\" title=\"Anchor for NOT NULL constraint\">#<\/a><\/h3>\n\n\n\n<p>If you impose the <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-not-null\/\">NOT NULL<\/a><\/code> constraint on a value, then the variable cannot accept <code>NULL<\/code>. Additionally, a variable declared with the <code>NOT NULL<\/code> must be initialized with a non-null value. <\/p>\n\n\n\n<p class=\"note\">Note that PL\/SQL treats an empty string <code>''<\/code> as <code>NULL<\/code>.<\/p>\n\n\n\n<p>For example:<\/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\"><span class=\"hljs-keyword\">DECLARE<\/span>\n  l_shipping_status VARCHAR2( <span class=\"hljs-number\">25<\/span> ) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span> := <span class=\"hljs-string\">'Shipped'<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n  l_shipping_status := <span class=\"hljs-string\">''<\/span>;\n<span class=\"hljs-keyword\">END<\/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>How it works:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, declare a variable named <code>l_shipping_status<\/code> with the <code>NOT NULL<\/code> constraint. <\/li>\n\n\n\n<li>Then, assign a zero-length string to the variable.<\/li>\n<\/ul>\n\n\n\n<p>PL\/SQL issued the following error:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"plaintext\" data-shcb-language-slug=\"plaintext\"><span><code class=\"hljs language-plaintext\">ORA-06502: PL\/SQL: value or conversion error<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">plaintext<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">plaintext<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Since the variable <code>l_shipping_status<\/code> has a <code>NOT NULL<\/code> constraint, it cannot accept <code>NULL<\/code> or zero-length string in this case.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='variable-assignments'>Variable assignments <a href=\"#variable-assignments\" class=\"anchor\" id=\"variable-assignments\" title=\"Anchor for Variable assignments\">#<\/a><\/h2>\n\n\n\n<p>To assign a value to a variable, you use the assignment operator (<code>:=<\/code>), for example:<\/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_customer_group VARCHAR2(<span class=\"hljs-number\">100<\/span>) := <span class=\"hljs-string\">'Silver'<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    l_customer_group := <span class=\"hljs-string\">'Gold'<\/span>;\n    DBMS_OUTPUT.PUT_LINE(l_customer_group);\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 class=\"note\">To display the message on the script output, you should execute the <code>SET SERVEROUPUT ON<\/code> command for  your session.<\/p>\n\n\n\n<p>You can assign a value of a variable to another as shown in the following example:<\/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\">DECLARE<\/span>\n    l_business_partner VARCHAR2(<span class=\"hljs-number\">100<\/span>) := <span class=\"hljs-string\">'Distributor'<\/span>;\n    l_lead_for VARCHAR2(<span class=\"hljs-number\">100<\/span>);\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    l_lead_for := l_business_partner; \n    DBMS_OUTPUT.PUT_LINE(l_lead_for);\n<span class=\"hljs-keyword\">END<\/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>Additionally, you can select a value from a table and assign it to a variable using the <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-select-into\/\">SELECT INTO<\/a> statement. For example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">DECLARE<\/span>\n   <span class=\"hljs-selector-tag\">l_order_count<\/span> <span class=\"hljs-selector-tag\">INT<\/span>;\n<span class=\"hljs-selector-tag\">BEGIN<\/span>\n  <span class=\"hljs-selector-tag\">SELECT<\/span> <span class=\"hljs-selector-tag\">COUNT<\/span>(*) <span class=\"hljs-selector-tag\">INTO<\/span> <span class=\"hljs-selector-tag\">l_order_count<\/span> <span class=\"hljs-selector-tag\">FROM<\/span> <span class=\"hljs-selector-tag\">orders<\/span>;\n  \n  <span class=\"hljs-selector-tag\">DBMS_OUTPUT<\/span><span class=\"hljs-selector-class\">.PUT_LINE<\/span>(<span class=\"hljs-selector-tag\">l_order_count<\/span>);\n<span class=\"hljs-selector-tag\">END<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">105<\/code><\/span><\/pre>\n\n\n<p>How it works:<\/p>\n\n\n\n<p>First, declare a variable <code>l_order_count<\/code> with type <code>INT<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">l_order_count INT;<\/code><\/span><\/pre>\n\n\n<p>Second, count the orders in the <code>orders<\/code> table from the <a href=\"https:\/\/www.oracletutorial.com\/getting-started\/oracle-sample-database\/\">sample database<\/a> using the <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-aggregate-functions\/oracle-count\/\">COUNT()<\/a><\/code> aggregate function and assign its result to the <code>l_order_count<\/code> variable:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT COUNT(*) INTO l_order_count FROM orders;<\/code><\/span><\/pre>\n\n\n<p>Third, display value of the <code>l_order_count<\/code> variable:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">DBMS_OUTPUT<\/span><span class=\"hljs-selector-class\">.PUT_LINE<\/span>(<span class=\"hljs-selector-tag\">l_order_count<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='anchored-declarations'>Anchored declarations <a href=\"#anchored-declarations\" class=\"anchor\" id=\"anchored-declarations\" title=\"Anchor for Anchored declarations\">#<\/a><\/h2>\n\n\n\n<p>Typically, you declare a variable and select a value from a table column for this variable. If the <a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-data-types\/\">data type<\/a> of the table column changes, you must adjust the program to make it work with the new type.<\/p>\n\n\n\n<p>PL\/SQL allows you to declare a variable whose data type anchor to a table column or another variable. Consider the following example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" 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_customer_name customers.name<span class=\"hljs-meta\">%TYPE<\/span>;\n  l_credit_limit customers.credit_limit<span class=\"hljs-meta\">%TYPE<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n  <span class=\"hljs-keyword\">SELECT<\/span>\n    <span class=\"hljs-type\">name<\/span>, credit_limit\n  <span class=\"hljs-keyword\">INTO<\/span>\n    l_customer_name, l_credit_limit\n  <span class=\"hljs-keyword\">FROM<\/span>\n    customers\n  <span class=\"hljs-keyword\">WHERE<\/span>\n    customer_id = <span class=\"hljs-number\">38<\/span>;\n\n  DBMS_OUTPUT.PUT_LINE(l_customer_name || <span class=\"hljs-string\">':'<\/span> || l_credit_limit );\n<span class=\"hljs-keyword\">END<\/span>;\n\/<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><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 example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First,&nbsp; declare two variables <code>l_customer_name<\/code> and <code>l_credit_limit<\/code> whose data type anchors to the <code>name<\/code> and <code>credit_limit<\/code> columns respectively, in the declaration section of the block.<\/li>\n\n\n\n<li>Second, query the customer name and credit limit of the customer id <code>38<\/code> and assign these column values to the <code>l_customer_name<\/code> and <code>l_credit_limit<\/code> variables in the execution block.<\/li>\n\n\n\n<li>Third, display the customer name and credit limit.<\/li>\n<\/ul>\n\n\n\n<p>PL\/SQL returned the following output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">Kraft Heinz:500<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This example illustrates how to declare variables that anchor to another variable:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" 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_credit_limit   customers.credit_limit<span class=\"hljs-meta\">%TYPE<\/span>;\n    l_average_credit l_credit_limit<span class=\"hljs-meta\">%TYPE<\/span>;\n    l_max_credit     l_credit_limit<span class=\"hljs-meta\">%TYPE<\/span>;\n    l_min_credit     l_credit_limit<span class=\"hljs-meta\">%TYPE<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">SELECT<\/span> \n        MIN(credit_limit), \n        MAX(credit_limit), \n        AVG(credit_limit)\n    <span class=\"hljs-keyword\">INTO<\/span> \n        l_min_credit,\n        l_max_credit, \n        l_average_credit\n    <span class=\"hljs-keyword\">FROM<\/span> customers;\n    \n    \n    <span class=\"hljs-keyword\">SELECT<\/span> \n        credit_limit\n    <span class=\"hljs-keyword\">INTO<\/span> \n        l_credit_limit\n    <span class=\"hljs-keyword\">FROM<\/span> \n        customers\n    <span class=\"hljs-keyword\">WHERE<\/span> \n        customer_id = <span class=\"hljs-number\">100<\/span>;\n\n    dbms_output.put_line(<span class=\"hljs-string\">'Min Credit: '<\/span> || l_min_credit);\n    dbms_output.put_line(<span class=\"hljs-string\">'Max Credit: '<\/span> || l_max_credit);\n    dbms_output.put_line(<span class=\"hljs-string\">'Avg Credit: '<\/span> || l_average_credit);\n\n    dbms_output.put_line(<span class=\"hljs-string\">'Customer Credit: '<\/span> || l_credit_limit);\n<span class=\"hljs-keyword\">END<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><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>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"plaintext\" data-shcb-language-slug=\"plaintext\"><span><code class=\"hljs language-plaintext\">Min Credit: 100\nMax Credit: 5000\nAvg Credit: 1894.67\nCustomer Credit: 1894.67<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">plaintext<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">plaintext<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/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>Use a variable to store a value of a specific type.<\/li>\n\n\n\n<li>Use the <code>:=<\/code> operator to assign a value to a variable.<\/li>\n\n\n\n<li>Use the <code>NOT NULL<\/code> constraint to ensure that a variable will not hold <code>NULL<\/code>.<\/li>\n\n\n\n<li>Use the <code>%TYPE<\/code> to declare a variable with the same data type as a table column or another variable.<\/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=\"1493\"\n\t\t\t\tdata-post-url=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/\"\n\t\t\t\tdata-post-title=\"PL\/SQL Variables\"\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=\"1493\"\n\t\t\t\tdata-post-url=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/\"\n\t\t\t\tdata-post-title=\"PL\/SQL Variables\"\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 PL\/SQL variables and shows you how to manipulate the variables effectively.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1418,"menu_order":3,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1493","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>The Overview of PL\/SQL Variables<\/title>\n<meta name=\"description\" content=\"This tutorial introduces you to PL\/SQL variables and shows you how to manipulate the variables effectively.\" \/>\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-variables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Overview of PL\/SQL Variables\" \/>\n<meta property=\"og:description\" content=\"This tutorial introduces you to PL\/SQL variables and shows you how to manipulate the variables effectively.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/\" \/>\n<meta property=\"og:site_name\" content=\"Oracle Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-06-09T07:09:09+00:00\" \/>\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=\"3 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-variables\\\/\",\"url\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-variables\\\/\",\"name\":\"The Overview of PL\\\/SQL Variables\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/#website\"},\"datePublished\":\"2017-11-18T15:02:54+00:00\",\"dateModified\":\"2025-06-09T07:09:09+00:00\",\"description\":\"This tutorial introduces you to PL\\\/SQL variables and shows you how to manipulate the variables effectively.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-variables\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-variables\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-variables\\\/#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 Variables\"}]},{\"@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":"The Overview of PL\/SQL Variables","description":"This tutorial introduces you to PL\/SQL variables and shows you how to manipulate the variables effectively.","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-variables\/","og_locale":"en_US","og_type":"article","og_title":"The Overview of PL\/SQL Variables","og_description":"This tutorial introduces you to PL\/SQL variables and shows you how to manipulate the variables effectively.","og_url":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/","og_site_name":"Oracle Tutorial","article_modified_time":"2025-06-09T07:09:09+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/","url":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/","name":"The Overview of PL\/SQL Variables","isPartOf":{"@id":"https:\/\/www.oracletutorial.com\/#website"},"datePublished":"2017-11-18T15:02:54+00:00","dateModified":"2025-06-09T07:09:09+00:00","description":"This tutorial introduces you to PL\/SQL variables and shows you how to manipulate the variables effectively.","breadcrumb":{"@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/#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 Variables"}]},{"@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\/1493","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=1493"}],"version-history":[{"count":0,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages\/1493\/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=1493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}