What Are the Differences Between Functions and Stored Procedures?
Functions and stored procedures are two common objects in database management systems. They have similarities and differences in implementing specific functions. Understanding their characteristics and application scenarios is important for properly designing the database structure and improving database performance.
Function |
Stored procedures |
---|---|
Both can be used to implement specific functions. Both functions and stored procedures can encapsulate a series of SQL statements to complete certain specific operations. |
|
Both can receive input parameters and perform corresponding operations based on the parameters. |
|
The identifier of a function is FUNCTION. |
The identifier of the stored procedure is PROCEDURE. |
A function must return a specific value of the specified numeric type. |
A stored procedure can have no return value, one return value, or multiple return values. You can use output parameters to return results or directly use the SELECT statement in a stored procedure to return result sets. |
Functions are used to return single values, for example, a number calculation result, a string processing result, or a table. |
Stored procedures are used for DML operations, for example, inserting, updating, and deleting data in batches. |
- Creating and Invoking a Function
Create the emp table and insert data into the table. The table data is as follows:
1 2 3 4 5 6 7 8
SELECT * FROM emp; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+----------+------+---------------------+---------+--------+-------- 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | | 20 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | | 20 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 (4 rows)
Create the emp_comp function to accept two numbers as input and return the calculated value.
1 2 3 4 5 6 7 8 9
CREATE OR REPLACE FUNCTION emp_comp ( p_sal NUMBER, p_comm NUMBER ) RETURN NUMBER IS BEGIN RETURN (p_sal + NVL(p_comm, 0)) * 24; END; /
Run the SELECT command to invoke the function:
1 2 3 4 5 6 7 8
SELECT ename "Name", sal "Salary", comm "Commission", emp_comp(sal, comm) "Total Compensation" FROM emp; Name | Salary | Commission | Total Compensation -------+---------+------------+-------------------- SMITH | 800.00 | | 19200.00 ALLEN | 1600.00 | 300.00 | 45600.00 JONES | 2975.00 | | 71400.00 WARD | 1250.00 | 500.00 | 42000.00 (4 rows)
- Creating and Invoking a Stored Procedure
Create the MATCHES table and insert data into the table. The table data is as follows:
1 2 3 4 5 6 7 8 9
SELECT * FROM MATCHES; matchno | teamno | playerno | won | lost ---------+--------+----------+-----+------ 1 | 1 | 6 | 3 | 1 7 | 1 | 57 | 3 | 0 8 | 1 | 8 | 0 | 3 9 | 2 | 27 | 3 | 2 11 | 2 | 112 | 2 | 3 (5 rows)
Create the stored procedure delete_matches to delete all matches that a specified player participates in.
1 2 3 4 5 6
CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) AS BEGIN DELETE FROM MATCHES WHERE playerno = p_playerno; END; /
Invoke the stored procedure delete_matches.
1
CALL delete_matches(57);
Query the MATCHES table again. The returned result indicates that the data of the player whose playerno is 57 has been deleted.
1 2 3 4 5 6 7 8
SELECT * FROM MATCHES; matchno | teamno | playerno | won | lost ---------+--------+----------+-----+------ 11 | 2 | 112 | 2 | 3 8 | 1 | 8 | 0 | 3 1 | 1 | 6 | 3 | 1 9 | 2 | 27 | 3 | 2 (4 rows)
Database Usage FAQs
- How Do I Change Distribution Columns?
- How Do I View and Set the Database Character Encoding?
- What Do I Do If Date Type Is Automatically Converted to the Timestamp Type During Table Creation?
- Do I Need to Run VACUUM FULL and ANALYZE on Common Tables Periodically?
- Do I Need to Set a Distribution Key After Setting a Primary Key?
- Is GaussDB(DWS) Compatible with PostgreSQL Stored Procedures?
- What Are Partitioned Tables, Partitions, and Partition Keys?
- How Can I Export the Table Structure?
- How Do I Delete Table Data Efficiently?
- How Do I View Foreign Table Information?
- If No Distribution Column Is Specified, How Will Data Be Stored?
- How Do I Replace the Null Result with 0?
- How Do I Check Whether a Table Is Row-Stored or Column-Stored?
- How Do I Query the Information About GaussDB(DWS) Column-Store Tables?
- Why Sometimes the GaussDB(DWS) Query Indexes Become Invalid?
- How Do I Use a User-Defined Function to Rewrite the CRC32() Function?
- What Are the Schemas Starting with pg_toast_temp* or pg_temp*?
- Solutions to Inconsistent GaussDB(DWS) Query Results
- Which System Catalogs That the VACUUM FULL Operation Cannot Be Performed on?
- In Which Scenarios Would a Statement Be "idle in transaction"?
- How Does GaussDB(DWS) Implement Row-to-Column and Column-to-Row Conversion?
- What Are the Differences Between Unique Constraints and Unique Indexes?
- What Are the Differences Between Functions and Stored Procedures?
- How Do I Delete Duplicate Table Data?
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbotmore