{"id":3873,"date":"2023-11-25T12:33:13","date_gmt":"2023-11-25T12:33:13","guid":{"rendered":"https:\/\/www.copahost.com\/blog\/?p=3873"},"modified":"2023-12-02T15:08:31","modified_gmt":"2023-12-02T15:08:31","slug":"pl-sql","status":"publish","type":"post","link":"https:\/\/www.copahost.com\/blog\/pl-sql\/","title":{"rendered":"PL SQL: A Complete Guide"},"content":{"rendered":"\n<p>SQL (Structured Query Language) is widely used in managing and manipulating data in database systems.&nbsp;However, in situations where it is necessary to carry out more complex tasks.&nbsp;Such as, for example, large-scale data manipulation, generation of personalized reports.&nbsp;Or integration with external applications, the SQL language may prove to be limited. In this case, it is common to resort to the use of procedural programming languages.&nbsp;Like&nbsp;<strong>PL in SQL, which allow greater flexibility and control over the operations performed.<\/strong><\/p>\n\n\n\n<p>The PL (Procedural Language) in SQL is a combination of these two languages, providing the benefits of both worlds.&nbsp;SQL syntax and semantics are maintained, making data manipulation and querying easier.&nbsp;On the other hand, PL in SQL adds flow control structures, subroutines, and functions.&nbsp;This allows the development of more sophisticated and efficient procedures.<\/p>\n\n\n\n<p>PL SQL is compatible with Oracle databases.&nbsp;MySQL&nbsp;is currently&nbsp;not supported.<\/p>\n\n\n\n<p>This article aims to present the fundamentals of PL in SQL, covering aspects such as syntax and semantics.&nbsp;Also, data types, flow control structures, subroutines, and functions.&nbsp;And finally, exception handling, interaction with the database, check table, scopes and performance.<\/p>\n\n\n\n<p>So, at the end of this article, you will be prepared to use PL in SQL in your data management activities.&nbsp;Providing more efficient and scalable solutions.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_69_1 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#PL_syntax_and_semantics_in_SQL\" title=\"PL syntax and semantics in SQL\">PL syntax and semantics in SQL<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#PLSQL_Syntax\" title=\"PL\/SQL Syntax\">PL\/SQL Syntax<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#PLSQL_Semantics\" title=\"PL\/SQL Semantics\">PL\/SQL Semantics<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Variables_and_data_types_in_PL_in_SQL\" title=\"Variables and data types in PL in SQL\">Variables and data types in PL in SQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Flow_Control_Structures\" title=\"Flow Control Structures\">Flow Control Structures<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Subroutines_and_functions_in_PL_in_SQL\" title=\"Subroutines and functions in PL in SQL\">Subroutines and functions in PL in SQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Dealing_with_errors_and_exceptions_in_PL\" title=\"Dealing with errors and exceptions in PL\">Dealing with errors and exceptions in PL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Interaction_with_the_database\" title=\"Interaction with the database\">Interaction with the database<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Data_manipulation\" title=\"Data manipulation\">Data manipulation<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Carry_out_consultations\" title=\"Carry out consultations\">Carry out consultations<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Update_data\" title=\"Update data\">Update data<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Understanding_blocks_and_scopes_in_PL_in_SQL\" title=\"Understanding blocks and scopes in PL in SQL\">Understanding blocks and scopes in PL in SQL<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Blocks\" title=\"Blocks\">Blocks<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/www.copahost.com\/blog\/pl-sql\/#Scope\" title=\"Scope\">Scope<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"PL_syntax_and_semantics_in_SQL\"><\/span>PL syntax and semantics in SQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>PL\/SQL (Procedural Language\/Structured Query Language) is a dialect of SQL (Structured Query Language).&nbsp;<strong>It allows writing procedures in addition to queries and data updates<\/strong>&nbsp;.&nbsp;It is mainly used in relational database management systems such as Oracle Database.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"PLSQL_Syntax\"><\/span>PL\/SQL Syntax<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>PL\/SQL syntax is similar to standard SQL.\u00a0However, it includes object-oriented programming elements such as variables,\u00a0<a href=\"https:\/\/www.copahost.com\/blog\/mysql-data-types\/\">data types<\/a>\u00a0, program flow controllers, loops, and functions.\u00a0Some examples of PL\/SQL syntax include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Declaration of variables and data types:&nbsp;<code>DECLARE v_name VARCHAR2(50) := 'John';<\/code><\/li>\n\n\n\n<li>if-then-else conditional:&nbsp;IF v_age &gt; 18 THEN v_status := &#8216;Adult&#8217;; ELSIF v_age &gt; 65 THEN v_status := &#8216;Elderly&#8217;; END IF;<\/li>\n\n\n\n<li>while loop:&nbsp;<code>WHILE v_count &gt; 0 LOOP<\/code><\/li>\n\n\n\n<li>Functions:&nbsp;<code>CREATE OR REPLACE FUNCTION my_function(p_param1 IN VARCHAR2, p_param2 OUT VARCHAR2) RETURN VARCHAR2 IS BEGIN ... END;<\/code><\/li>\n<\/ul>\n\n\n\n<p>PL\/SQL syntax allows developers to create procedures.&nbsp;Everything beyond queries and data updates, making them more efficient and flexible.<\/p>\n\n\n\n<p>Additionally, PL\/SQL supports object-oriented programming.&nbsp;This allows developers to create reusable function libraries and more complex programming modes.<\/p>\n\n\n\n<p>PL\/SQL semantics is the way PL\/SQL interprets and executes code.&nbsp;In this way, the PL in SQL is interpreted and executed by the Oracle Database PL\/SQL engine.&nbsp;It reads the code and generates the corresponding execution code.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"PLSQL_Semantics\"><\/span>PL\/SQL Semantics<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>PL\/SQL semantics include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Data types:<\/strong>&nbsp;PL\/SQL supports many data types, such as numbers, strings, dates, booleans, etc.<\/li>\n\n\n\n<li><strong>Variables:<\/strong>&nbsp;Variables are used to store data temporarily during code execution.<\/li>\n\n\n\n<li><strong>Program flow controllers:<\/strong>&nbsp;PL\/SQL supports program flow controllers.&nbsp;For example, like if-then-else, while, for and&nbsp;<a href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/\">case<\/a>, to control code execution.<\/li>\n\n\n\n<li><strong>Functions:<\/strong>&nbsp;Functions are subroutines that can be called within the main code.&nbsp;This way, they can receive parameters and return values.<\/li>\n\n\n\n<li><strong>Procedures:<\/strong>&nbsp;We can call procedures within the main block, as they are subroutines.&nbsp;In this sense, they can receive parameters and execute a series of instructions.<\/li>\n\n\n\n<li><strong>Triggers:<\/strong>&nbsp;Triggers are subroutines that fire automatically in response to certain actions in the database.&nbsp;How to insert, <a href=\"https:\/\/www.copahost.com\/blog\/update-sql\/\">update<\/a> or <a href=\"https:\/\/www.copahost.com\/blog\/delete-into-sql\/\">delete<\/a> records.<\/li>\n<\/ul>\n\n\n\n<p>Thus, the syntax and semantics of PL\/SQL allow us to create complex procedures and functions, which we can reuse and optimize, making application development more efficient and effective.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Variables_and_data_types_in_PL_in_SQL\"><\/span>Variables and data types in PL in SQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>PL\/SQL supports a wide variety of data types, each with its own semantics and usage.&nbsp;Thus, some examples of supported data types include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Integers:<\/strong>&nbsp;represent whole numbers without precision.&nbsp;Example:&nbsp;<code>NUMBER(5)<\/code>,<code>NUMBER(10)<\/code><\/li>\n\n\n\n<li><strong>Decimal numbers:<\/strong>&nbsp;represent numbers accurately.&nbsp;Example:&nbsp;<code>NUMBER(5,2)<\/code>,<code>NUMBER(10,2)<\/code><\/li>\n\n\n\n<li><strong>Strings:<\/strong>&nbsp;represent sequences of characters.&nbsp;Example:&nbsp;<code>VARCHAR2(50)<\/code>,<code>VARCHAR2(100)<\/code><\/li>\n\n\n\n<li><strong>Dates:<\/strong>&nbsp;represent dates and times.&nbsp;Example:&nbsp;<code>DATE<\/code>,<code>TIMESTAMP<\/code><\/li>\n\n\n\n<li><strong>Booleans:<\/strong>&nbsp;represent Boolean values \u200b\u200b(true or false).&nbsp;Example:&nbsp;<code>BOOLEAN<\/code>,<code>NUMBER(1)<\/code><\/li>\n\n\n\n<li><strong>PL\/SQL type data types:<\/strong>&nbsp;Represent data types specific to PL\/SQL.&nbsp;Such as cursors, generic data types, and range data types.<\/li>\n<\/ul>\n\n\n\n<p>To define a variable in PL\/SQL, we need to specify the data type and a variable.&nbsp;The syntax for defining a variable is:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n   v_variable_name VARCHAR2(maximum_size) := 'initial_value';\nBEGIN\n   -- PL\/SQL code\nEND;<\/code><\/pre>\n\n\n\n<p>For example, to define a variable called \u201cv_age\u201d of type INTEGER with a maximum size of 5, you would use the following code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n   v_age INTEGER(5) := 25;\nBEGIN\n   -- PL\/SQL code\nEND;<\/code><\/pre>\n\n\n\n<p>To use the variable in a SQL or PL\/SQL statement, you can access it directly, as in the example below:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT v_age FROM people;<\/code><\/pre>\n\n\n\n<p>or<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IF v_age &gt; 18 THEN\n   dbms_output.put_line('This person is an adult');\nEND IF;<\/code><\/pre>\n\n\n\n<p>Therefore, it is important to remember that the supported data types may vary depending on the database you are using.&nbsp;But the syntax for defining and using variables is generally similar.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"alignleft size-full is-resized\"><img fetchpriority=\"high\" decoding=\"async\" width=\"740\" height=\"616\" src=\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image-1.png\" alt=\"PL SQL Flow control structures\" class=\"wp-image-3879\" style=\"width:174px;height:auto\" srcset=\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image-1.png 740w, https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image-1-300x250.png 300w\" sizes=\"(max-width: 740px) 100vw, 740px\" \/><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Flow_Control_Structures\"><\/span>Flow Control Structures<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>PL\/SQL supports several flow control structures, which allow you to control code execution according to different conditions.&nbsp;Thus, some of the main PL\/SQL flow control structures include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>IF-THEN-ELSE: allows you to execute a block of code if a condition is true, another block of code if the condition is false.&nbsp;The syntax is as follows:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>IF condition THEN\n   -- code to be executed if condition is true\nELSE\n   -- code to be executed if condition is false\nEND IF;<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>WHILE: allows you to execute a block of code repeatedly while a condition is true.&nbsp;The syntax is as follows:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>WHILE LOOP condition\n   -- code to be executed\nEND LOOP;<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>FOR: allows you to execute a block of code for each value in a sequence.&nbsp;The syntax is as follows:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>FOR i IN sequence LOOP\n   -- code to execute for each string value\nEND LOOP;<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SWITCH: allows you to choose between several options based on a value.&nbsp;The syntax is as follows:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n   v_option NUMBER;\nBEGIN\n   -- code for each option\n   CASE v_option\n      WHEN 1 THEN\n         -- code for option 1\n      WHEN 2 THEN\n         -- code for option 2\n      WHEN 3 THEN\n         -- code for option 3\n      ELSE\n         -- code for invalid option\n   END CASE;\nEND;<\/code><\/pre>\n\n\n\n<p>These are some of the main PL\/SQL flow control structures.&nbsp;They allow you to control the execution of the code according to different conditions.&nbsp;Therefore, they are extremely useful for creating reusable and modular code, and can be combined to create more complex logic.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Subroutines_and_functions_in_PL_in_SQL\"><\/span>Subroutines and functions in PL in SQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In PL\/SQL, subroutines and functions are important programming elements, which allow you to reuse code and simplify program logic.&nbsp;Thus, the main difference between subroutines and functions is that subroutines do not return values \u200b\u200bwhereas functions do return values.<\/p>\n\n\n\n<p>Subroutines are blocks of code that are called within other blocks of code.&nbsp;In this way, they are used to divide the code into smaller, more manageable parts.&nbsp;And they can be called multiple times within the same program.&nbsp;Thus, the syntax for creating a subroutine is as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>PROCEDURE IS subroutine_name\nBEGIN\n   -- subroutine code\nEND;<\/code><\/pre>\n\n\n\n<p>Functions are subroutines that return values.&nbsp;Thus, they are used to calculating and return values \u200b\u200bfrom input data.&nbsp;The syntax for creating a function is as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FUNCTION function_name RETURN return_type IS\nBEGIN\n   -- function code\nEND;<\/code><\/pre>\n\n\n\n<p>To call a subroutine or function, you use the keyword \u201cCALL\u201d followed by the name of the subroutine or function.&nbsp;The syntax for calling a subroutine is as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CALL subroutine_name;<\/code><\/pre>\n\n\n\n<p>And the syntax for calling a function is as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n   v_result return_type;\nBEGIN\n   v_result := function_name(param1, param2, ...);\nEND;<\/code><\/pre>\n\n\n\n<p>In summary, subroutines and functions are important elements in PL\/SQL, which allow you to reuse code and simplify program logic.&nbsp;This way, we use subroutines to divide the code into smaller, more manageable parts.&nbsp;This occurs while we use functions to calculate and return values \u200b\u200bfrom input data.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Dealing_with_errors_and_exceptions_in_PL\"><\/span>Dealing with errors and exceptions in PL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>PL\/SQL supports handling errors and exceptions using the \u201ctry-catch-finally\u201d structure.&nbsp;Thus, the \u201ctry-catch-finally\u201d structure allows you to execute a block of code.&nbsp;Additionally, it allows you to return a specific error value, or execute a block of code regardless of the result of the previous attempt.<\/p>\n\n\n\n<p>The basic syntax of the \u201ctry-catch-finally\u201d structure is as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>BEGIN\n   -- code to be executed\nEXCEPTION\n   -- code to be executed if an exception occurs\n   -- this is only executed if an exception occurs\nEND;<\/code><\/pre>\n\n\n\n<p>The \u201ctry-catch-finally\u201d structure is composed of three parts:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u201ctry\u201d: contains the code that will be executed.<\/li>\n\n\n\n<li>\u201ccatch\u201d: contains the code that will be executed if an exception occurs.<\/li>\n\n\n\n<li>\u201cfinally\u201d: contains the code that will be executed regardless of the result of the previous attempt.<\/li>\n<\/ul>\n\n\n\n<p>The \u201ctry-catch-finally\u201d structure is useful for handling errors and exceptions.&nbsp;Allowing you to execute code regardless of the result of the previous attempt and return a specific error value.<\/p>\n\n\n\n<p>For example, here is an example of how to use the \u201ctry-catch-finally\u201d structure.&nbsp;It is used to deal with errors when trying to access a record in a database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n   v_id NUMBER;\nBEGIN\n   -- Try to access a record\n   SELECT id FROM table WHERE id = 1 INTO v_id;\n   IF v_id IS NULL THEN\n      -- Record not found exception\n      RAISE_APPLICATION_ERROR(-20001, 'Registration not found');\n   END IF;\n   -- Code to be executed successfully\n   dbms_output.put_line(v_id);\nEND;<\/code><\/pre>\n\n\n\n<p>In this example, first the \u201ctry-catch-finally\u201d structure is used to try to access a record in the database.&nbsp;This way, if the record is not found, the \u201cRecord not found\u201d exception is raised and the code inside the \u201ccatch\u201d block is executed.&nbsp;In this sense, if the record is found, the code inside the \u201cfinally\u201d block is executed.<\/p>\n\n\n\n<p>In summary, the \u201ctry-catch-finally\u201d structure is an effective way to handle errors and exceptions in PL\/SQL.&nbsp;It allows you to run code regardless of the result of the previous attempt and return a specific error value.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Interaction_with_the_database\"><\/span>Interaction with the database<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>PL\/SQL is an object-oriented programming language that is widely used to interact with databases.&nbsp;As such, it provides multiple ways to manipulate tables, perform queries, and update data.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Data_manipulation\"><\/span>Data manipulation<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>To manipulate tables, you can use standard SQL syntax to insert, update, and delete records.&nbsp;For example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Add new data to a table<\/li>\n<\/ul>\n\n\n\n<p>Therefore, to insert a new record into a table use the following syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO table (column1, column2, column3) VALUES (value1, value2, value3);\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Update data in a table<\/li>\n<\/ul>\n\n\n\n<p><a href=\"https:\/\/www.copahost.com\/blog\/update-sql\/\">To update an existing record<\/a>&nbsp;, you can use the following syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>UPDATE table SET column1 = value1 WHERE id = 1;<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Add existing data to a table<\/li>\n<\/ul>\n\n\n\n<p>Therefore, we can delete an existing record using the&nbsp;<a href=\"https:\/\/www.copahost.com\/blog\/delete-into-sql\/\">DELETE <\/a>SQL&nbsp;syntax :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DELETE FROM table WHERE id = 1;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Carry_out_consultations\"><\/span>Carry out consultations<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>To perform queries, you can use standard SQL syntax to select, sort, and filter records.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>All data in a table<\/li>\n<\/ul>\n\n\n\n<p>For example, to select all records from a table, you can use the following syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM table;<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Sort data in a table<\/li>\n<\/ul>\n\n\n\n<p>To <a href=\"https:\/\/www.copahost.com\/blog\/order-by-sql\/\">sort records by<\/a> a specific column, you can use the following syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM table ORDER BY column1 ASC;<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Filter data in a table<\/li>\n<\/ul>\n\n\n\n<p>To filter records by a specific condition, you can use the following syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM table WHERE column1 = 'value';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Update_data\"><\/span>Update data<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>To update data, you can use the standard&nbsp;SQL UPADATE&nbsp;syntax to update specific column values.&nbsp;For example, to update the value of a column for all records in a table, you can use the following syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>UPDATE table SET column1 = 'new value';<\/code><\/pre>\n\n\n\n<p>Additionally, PL\/SQL allows you to create procedures in addition to queries and data updates.&nbsp;This makes the code more efficient and flexible.&nbsp;In this way, PL\/SQL supports object-oriented programming.&nbsp;This allows you to create reusable function libraries and more complex programming modes.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Understanding_blocks_and_scopes_in_PL_in_SQL\"><\/span>Understanding blocks and scopes in PL in SQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In PL\/SQL, blocks and scopes are important concepts in organizing and managing resources.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Blocks\"><\/span>Blocks<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>A block is a region of code that executes as a single set of instructions.&nbsp;Thus, blocks are used to group several related instructions in one place.&nbsp;And also to define the specification of a procedure or function.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Example:<\/li>\n<\/ul>\n\n\n\n<p>Suppose we have a customer table with the following columns: ID, name, email and address.&nbsp;We want to create a procedure that allows us to insert a new customer into the table.&nbsp;So in this procedure we need to get the latest client ID.&nbsp;And also, increment it by 1 to create a new ID for the new customer.<\/p>\n\n\n\n<p>Here is the code to implement this procedure, using blocks:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE OR REPLACE PROCEDURE insert_customer(\n   p_name IN VARCHAR,\n   p_email IN VARCHAR,\n   p_address IN VARCHAR\n)\nTO THE\nBEGIN\n   -- Variable declaration block\n   DECLARE\n      l_id customer_id_type;\n      l_new_id customer_id_type;\n   BEGIN\n      -- Main block\n      SELECT MAX(id) INTO l_id\n      FROM customers;\n      l_new_id := l_id + 1;\n\n      -- Insert the new customer into the table\n      INSERT INTO customers (name, email, address, id)\n      VALUES (p_name, p_email, p_address, l_new_id);\n   END;\nEND;<\/code><\/pre>\n\n\n\n<p>In this example, we use two blocks: the variable declaration block and the main block.&nbsp;Therefore, we use the&nbsp;variable&nbsp;<em>declaration block to declare the variables necessary for the procedure.&nbsp;<\/em>Meanwhile, we use the&nbsp;<em>main block<\/em>&nbsp;to execute the procedure instructions.<\/p>\n\n\n\n<p>Inside the main block, we use a SELECT to get the most recent customer ID.&nbsp;And also an INSERT to insert the new customer into the table.&nbsp;Additionally, we create a variable l_new_id to store the new ID generated for the entered customer.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Scope\"><\/span>Scope<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The scope is the region of code in which a variable is active and accessed.&nbsp;Scope is important to ensure that variables are only accessible in certain parts of the code.&nbsp;And also to avoid variable naming conflicts.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Example:<\/li>\n<\/ul>\n\n\n\n<p>Suppose we have a sales table with the following columns: ID, product, price and date.&nbsp;We want to create a procedure that allows us to calculate the total sales value for a specific product on a specific date.&nbsp;In this procedure, we need to create a variable to store the total sales value and restrict access to this variable only within the main block.<\/p>\n\n\n\n<p>Here is the code to implement this procedure, using scope:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE OR REPLACE PROCEDURE total_sales_by_product_on_date(\n   p_product_id IN product_id_type,\n   p_date IN date_type,\n   p_total_sales OUT sales_total_type\n)\nTO THE\nBEGIN\n   -- Variable declaration block\n   DECLARE\n      l_total_sales_on_date sales_total_type;\n   BEGIN\n      -- Main block\n      SELECT SUM(price) INTO l_total_sales_on_date\n      FROM sales_history\n      WHERE product_id = p_product_id AND date = p_date;\n      \n      -- Restrict access to the l_total_sales_on_date variable only within the main block\n      p_total_sales := l_total_sales_on_date;\n   END;\nEND;<\/code><\/pre>\n\n\n\n<p>In this example, we use a variable declaration block and a main block.&nbsp;Thus, the variable declaration block is used to declare the variable l_total_sales_on_date, which stores the total sales value for the specified product on specific date.<\/p>\n\n\n\n<p><strong>The combination of blocks and scopes allows code to be organized clearly and efficiently.&nbsp;<\/strong>Each block can have its own scope, which allows variables to be declared and used only within the block.&nbsp;This increases the readability and maintainability of the code, making it easier to understand and update.<\/p>\n\n\n\n<p>Additionally, using blocks and scopes can help prevent errors and ensure that variables are used correctly.&nbsp;For example, if a variable is declared with too broad a scope, it can be accessed in parts of the code that should not have access to it, which can lead to errors and bugs.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL (Structured Query Language) is widely used in managing and manipulating data in database systems.&nbsp;However, in situations where it is necessary to carry out more complex tasks.&nbsp;Such as, for example, large-scale data manipulation, generation of personalized reports.&nbsp;Or integration with external applications, the SQL language may prove to be limited. In this case, it is common [&hellip;]<\/p>\n","protected":false},"author":17,"featured_media":3876,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[91],"tags":[],"class_list":["post-3873","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>PL SQL: A Complete Guide - Copahost<\/title>\n<meta name=\"description\" content=\"Learn SQL with Procedural Language (PL) for efficient and scalable data management with best performance practices.\" \/>\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.copahost.com\/blog\/pl-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PL SQL: A Complete Guide - Copahost\" \/>\n<meta property=\"og:description\" content=\"Learn SQL with Procedural Language (PL) for efficient and scalable data management with best performance practices.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.copahost.com\/blog\/pl-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Copahost\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-25T12:33:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-02T15:08:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1024\" \/>\n\t<meta property=\"og:image:height\" content=\"613\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Schenia T\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Schenia T\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/\"},\"author\":{\"name\":\"Schenia T\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f\"},\"headline\":\"PL SQL: A Complete Guide\",\"datePublished\":\"2023-11-25T12:33:13+00:00\",\"dateModified\":\"2023-12-02T15:08:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/\"},\"wordCount\":2227,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image.png\",\"articleSection\":[\"MySQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.copahost.com\/blog\/pl-sql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/\",\"url\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/\",\"name\":\"PL SQL: A Complete Guide - Copahost\",\"isPartOf\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image.png\",\"datePublished\":\"2023-11-25T12:33:13+00:00\",\"dateModified\":\"2023-12-02T15:08:31+00:00\",\"description\":\"Learn SQL with Procedural Language (PL) for efficient and scalable data management with best performance practices.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.copahost.com\/blog\/pl-sql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/#primaryimage\",\"url\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image.png\",\"contentUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image.png\",\"width\":1024,\"height\":613,\"caption\":\"PL SQL\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.copahost.com\/blog\/pl-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.copahost.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PL SQL: A Complete Guide\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#website\",\"url\":\"https:\/\/www.copahost.com\/blog\/\",\"name\":\"Copahost\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.copahost.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#organization\",\"name\":\"Copahost\",\"url\":\"https:\/\/www.copahost.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png\",\"contentUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png\",\"width\":223,\"height\":40,\"caption\":\"Copahost\"},\"image\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f\",\"name\":\"Schenia T\",\"description\":\"Data scientist, passionate about technology tools and games. Undergraduate student in Statistics at UFPB. Her hobby is binge-watching series, enjoying good music working or cooking, going to the movies and learning new things!\",\"url\":\"https:\/\/www.copahost.com\/blog\/author\/schenia\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PL SQL: A Complete Guide - Copahost","description":"Learn SQL with Procedural Language (PL) for efficient and scalable data management with best performance practices.","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.copahost.com\/blog\/pl-sql\/","og_locale":"en_US","og_type":"article","og_title":"PL SQL: A Complete Guide - Copahost","og_description":"Learn SQL with Procedural Language (PL) for efficient and scalable data management with best performance practices.","og_url":"https:\/\/www.copahost.com\/blog\/pl-sql\/","og_site_name":"Copahost","article_published_time":"2023-11-25T12:33:13+00:00","article_modified_time":"2023-12-02T15:08:31+00:00","og_image":[{"width":1024,"height":613,"url":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image.png","type":"image\/png"}],"author":"Schenia T","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Schenia T","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.copahost.com\/blog\/pl-sql\/#article","isPartOf":{"@id":"https:\/\/www.copahost.com\/blog\/pl-sql\/"},"author":{"name":"Schenia T","@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f"},"headline":"PL SQL: A Complete Guide","datePublished":"2023-11-25T12:33:13+00:00","dateModified":"2023-12-02T15:08:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.copahost.com\/blog\/pl-sql\/"},"wordCount":2227,"commentCount":0,"publisher":{"@id":"https:\/\/www.copahost.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.copahost.com\/blog\/pl-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image.png","articleSection":["MySQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.copahost.com\/blog\/pl-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.copahost.com\/blog\/pl-sql\/","url":"https:\/\/www.copahost.com\/blog\/pl-sql\/","name":"PL SQL: A Complete Guide - Copahost","isPartOf":{"@id":"https:\/\/www.copahost.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.copahost.com\/blog\/pl-sql\/#primaryimage"},"image":{"@id":"https:\/\/www.copahost.com\/blog\/pl-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image.png","datePublished":"2023-11-25T12:33:13+00:00","dateModified":"2023-12-02T15:08:31+00:00","description":"Learn SQL with Procedural Language (PL) for efficient and scalable data management with best performance practices.","breadcrumb":{"@id":"https:\/\/www.copahost.com\/blog\/pl-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.copahost.com\/blog\/pl-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.copahost.com\/blog\/pl-sql\/#primaryimage","url":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image.png","contentUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/image.png","width":1024,"height":613,"caption":"PL SQL"},{"@type":"BreadcrumbList","@id":"https:\/\/www.copahost.com\/blog\/pl-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.copahost.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PL SQL: A Complete Guide"}]},{"@type":"WebSite","@id":"https:\/\/www.copahost.com\/blog\/#website","url":"https:\/\/www.copahost.com\/blog\/","name":"Copahost","description":"","publisher":{"@id":"https:\/\/www.copahost.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.copahost.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.copahost.com\/blog\/#organization","name":"Copahost","url":"https:\/\/www.copahost.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png","contentUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png","width":223,"height":40,"caption":"Copahost"},"image":{"@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f","name":"Schenia T","description":"Data scientist, passionate about technology tools and games. Undergraduate student in Statistics at UFPB. Her hobby is binge-watching series, enjoying good music working or cooking, going to the movies and learning new things!","url":"https:\/\/www.copahost.com\/blog\/author\/schenia\/"}]}},"_links":{"self":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts\/3873","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/users\/17"}],"replies":[{"embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/comments?post=3873"}],"version-history":[{"count":9,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts\/3873\/revisions"}],"predecessor-version":[{"id":3938,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts\/3873\/revisions\/3938"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/media\/3876"}],"wp:attachment":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/media?parent=3873"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/categories?post=3873"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/tags?post=3873"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}