Sql views, stored procedure, functions

This is a KT slide to explain SQL Views, Stored Procedure & Custom Functions (MySQL)

1. SQL - Views, SP, Fx


5/03/2020
-
-by Om Vikram Thapa

2. Views



In DB, a view is a virtual table based on the
result-set of an SQL statement.

A view contains rows and columns, just like a
real table. The fields in a view are fields from
one or more real tables in the database.

3. CREATE
& CALL
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;


SELECT * FROM [view_name];

4. SAMPLE
DEMO

5. STORED
PROCEDURE
A stored procedure is a prepared SQL code that
you can save, so the code can be reused over
and over again.


You can also pass parameters to a stored
procedure, so that the stored procedure can act
based on the parameter value(s) that is passed.

6. CREATE
& CALL
CREATE PROCEDURE procedure_name
BEGIN
sql_statement
END;


EXEC procedure_name;
OR
CALL procedure_name;

7. SAMPLE
DEMO

8. FUNCTIONS
SQL Functions are a way to extend MySQL with a new
function that works like a native (built-in) function such
as NOW() or CONCAT()





Functions will have the params with datatype and
always return a value with return datatype too. These
are also called as UDF (User Defined Functions)

9. CREATE
& CALL
CREATE FUNCTION
function_name(param param_data_type)
RETURNS data_type
BEGIN
function_statement
END;


SELECT function_name([column_name]) FROM
[table_name];

10. SAMPLE
DEMO

11. TAKEAWAY
1)VIEWS
2)STORED FUNCTIONS
3)FUNCTIONS