Procedure Tutorial in SQL | SQL Stored Procedure | Procedure in SQL
techTFQ・2 minutes read
Procedures stored in the database streamline complex logics beyond basic SQL queries, with syntax varying across RDBMS like PostgreSQL, Oracle, Microsoft SQL Server, and MySQL. Creating procedures in different RDBMS involves handling syntax nuances, parameter declarations, and delimiter modifications to ensure successful execution.
Insights
- Procedures in SQL are named code blocks in the database containing various SQL commands and are crucial for handling complex logics, data validations, and data cleanup beyond basic queries.
- Understanding syntax differences among RDBMS like PostgreSQL, Oracle, Microsoft SQL Server, and MySQL is essential for creating procedures tailored to each platform, ensuring successful execution and functionality.
Get key ideas from YouTube videos. It’s free
Recent questions
What is a SQL procedure?
A named block of code with SQL queries.
How do you create a procedure in PostgreSQL?
Use "create or replace procedure" syntax.
What are the syntax differences in Oracle procedures?
No language specification and different quote handling.
How do you handle multiple statements in MySQL procedures?
Change the default delimiter to something else.
What is the purpose of parameters in SQL procedures?
To pass values for analysis and processing.
Related videos
Summary
00:00
Creating Procedures in Various RDBMS: Syntax Differences
- A procedure in SQL is a named block of code stored in the database, containing SQL queries and commands like DML, DDL, DCL, and TCL.
- Procedures can include concepts like collection types, cursors, loop statements, if-else statements, variables, and exception handling.
- They are powerful for complex logics, data validations, and data cleanup beyond simple SQL queries.
- Procedures enhance SQL capabilities for querying data, building software logic, and handling project requirements beyond SQL queries.
- The syntax for creating a procedure varies slightly across different RDBMS like PostgreSQL, Oracle, Microsoft SQL Server, and MySQL.
- In PostgreSQL, the syntax includes specifying parameters, language, declaring variables, defining the procedure body, and handling single quotes using the double dollar symbol.
- Oracle's syntax is similar to PostgreSQL but does not require specifying the language and handles single quotes differently.
- Microsoft SQL Server syntax differs by requiring parameter names to start with an '@' symbol, declaring variables with 'declare' before each, and using 'alter' instead of 'replace' for updating procedures.
- MySQL's syntax is akin to Oracle's but does not need language specification or the double dollar symbol.
- Understanding the syntax differences among RDBMS is crucial for creating procedures in PostgreSQL, Oracle, Microsoft SQL Server, and MySQL.
13:18
Handling Multiple Statements in MySQL and PostgreSQL
- In MySQL, the default delimiter for statements is a semicolon, which can cause issues when handling multiple statements within a procedure.
- To handle multiple statements within a procedure in MySQL, change the default delimiter to something else, like "$$".
- By changing the delimiter in MySQL, statements within a procedure are treated as a whole until the new delimiter is encountered.
- When declaring variables in MySQL, use the "declare" keyword followed by the variable name and data type.
- In PostgreSQL, to create a simple procedure, use the syntax "create or replace procedure" followed by the procedure name and language.
- In PostgreSQL, use the "into" clause to store values fetched from a select query into variables for later use.
- In PostgreSQL, to insert data into a table, use the "insert into" statement with column names and corresponding values.
- In PostgreSQL, to update data in a table, use the "update" statement with the columns to be updated and their new values.
- After executing a procedure in PostgreSQL, use the "raise notice" command to display a success message.
- To run a procedure in PostgreSQL, use the "call" keyword followed by the procedure name within parentheses.
26:45
Creating Procedures in Various RDBMS with Parameters
- The procedure involves creating tables in Oracle and other RDBMS, with scripts and data sets available for download from the creator's Discord channel.
- Syntax differences between PostgreSQL and Oracle are highlighted, such as removing unnecessary symbols and changing the raise command to dbms_output.put_line in Oracle.
- Execution in Oracle involves using exec instead of call, and opening the dbms_output window to view procedure results.
- The same procedure is then executed in Microsoft SQL Server, requiring alterations like using alter instead of create or replace, adding '@' before variables, and using print instead of raise notice.
- In MySQL, the procedure is copied from Oracle with adjustments like using declare for variables and select for output, and delimiters are necessary for execution.
- The procedure with parameters is introduced for checking product availability and modifying tables based on sales orders, with product name and quantity as new parameters.
- The process of creating procedures with parameters is demonstrated, starting with PostgreSQL and moving through Oracle, Microsoft SQL Server, and MySQL, with adjustments made for each RDBMS.
40:34
Database Product Availability Check Procedure
- The procedure involves checking the availability of a product in a database based on required quantity.
- The query checks if the quantity available is sufficient for the requested product.
- Input parameters are used to pass product name and quantity for analysis.
- The procedure uses an IF statement to determine if the product is available in the required quantity.
- If the product is available, the database is updated with the sale details.
- In case of insufficient quantity, an error message is displayed.
- The procedure is executed in PostgreSQL and then replicated in Oracle with minimal changes.
- The process involves creating variables, querying the database, and updating tables.
- The transition from PostgreSQL to Oracle is seamless due to their similarities.
- The procedure successfully executes in both PostgreSQL and Oracle environments.
54:19
Procedures for iPhone Purchase in SQL Servers
- The individual attempted to purchase two iPhones, but upon execution, only four were deducted from the products table, indicating a flaw in the procedure.
- The issue stemmed from hard-coding the quantity as one instead of utilizing the proper parameter, leading to modifications in the procedure to rectify this error.
- After adjusting the procedure to incorporate the parameter correctly, the individual re-executed it, resulting in the correct deduction of two iPhones from the products table.
- Further testing with a quantity of three iPhones revealed an "insufficient quantity" warning message due to the limited availability of products.
- Transitioning to Microsoft SQL Server, the individual encountered syntax differences, requiring parameters to start with an "@" symbol and adjustments in the IF condition structure.
- The procedure was refined to include parameter declarations and checks for product availability, with the individual encountering errors related to undeclared variables and incorrect parameter formats.
- Subsequent corrections, such as declaring variables and adjusting the parameter format, enabled successful execution of the procedure in Microsoft SQL Server.
- Moving to MySQL, the individual adapted the existing procedure to meet the current requirements, encountering errors related to date functions and parameter formats.
- By replacing the date function and adjusting the parameter format, the individual successfully executed the procedure in MySQL, purchasing five AirPods Pro and encountering an "insufficient quantity" warning upon further attempts.
