Scribd
Upload
8 views8 pages

Creating and Managing Views in PL/SQL

The document provides a detailed overview of creating functions and views in SQL, including syntax and examples for defining functions, their parameters, and return types. It also discusses the advantages and disadvantages of using views, along with how to create and delete views from single or multiple tables. Additionally, sample tables and queries illustrate the practical application of these concepts.

Uploaded by

abhiramdonupati
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views8 pages

Creating and Managing Views in PL/SQL

The document provides a detailed overview of creating functions and views in SQL, including syntax and examples for defining functions, their parameters, and return types. It also discusses the advantages and disadvantages of using views, along with how to create and delete views from single or multiple tables. Additionally, sample tables and queries illustrate the practical application of these concepts.

Uploaded by

abhiramdonupati
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Creating a Function

CREATE [OR REPLACE] FUNCTION function_name


[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];

Where,

 function-name specifies the name of the function.


 [OR REPLACE] option allows the modification of an existing function.
 The optional parameter list contains name, mode and types of the parameters. IN
represents the value that will be passed from outside and OUT represents the parameter
that will be used to return a value outside of the procedure.
 The function must contain a return statement.
 The RETURN clause specifies the data type you are going to return from the function.
 function-body contains the executable part.
 The AS keyword is used instead of the IS keyword for creating a standalone function.
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/
DECLARE
num number;
factorial number;

FUNCTION fact(x number)


RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;

BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/
Views in SQL
o Views in SQL are considered as a virtual table. A view also contains rows and columns.
o To create the view, we can select the fields from one or more tables present in the
database.
o A view can either have specific rows based on certain condition or all the rows of a table.

Advantages of View:
1. Complexity: Views help to reduce the complexity. Different views can be created on the
same base table for different users.
2. Security: It increases the security by excluding the sensitive information from the view.
3. Query Simplicity: It helps to simplify commands from the user. A view can draw data
from several different tables and present it as a single table.
4. Consistency: A view can present a consistent, unchanged image of the structure of the
database. Views can be used to rename the columns without affecting the base table.
5. Data Integrity: If data is accessed and entered through a view, the DBMS can
automatically check the data to ensure that it meets the specified integrity constraints.
6. Storage Capacity: Views take very little space to store the data.
7. Logical Data Independence: View can make the application and database tables to a
certain extent independent.

Disadvantages of View:
The DML statements which can be performed on a view created using single base table
have certain restrictions are:

1. You cannot INSERT if the base table has any not null column that do not appear in view.
2. You cannot INSERT or UPDATE if any of the column referenced in the INSERT or UPDATE
contains group functions or columns defined by expression.
3. You can't execute INSERT, UPDATE, DELETE statements on a view if with read only option
is enabled.
4. You can't be created view on temporary tables.
5. You cannot INSERT, UPDATE, DELETE if the view contains group functions GROUP BY,
DISTINCT or a reference to a psuedocolumn rownum.
6. You can't pass parameters to the SQL server views.
7. You can't associate rules and defaults with views.

Sample table:
Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi

2 Kathrin Noida

3 David Ghaziabad

4 Alina Gurugram

Student_Marks

STU_ID NAME MARKS AGE

1 Stephan 97 19

2 Kathrin 86 21

3 David 74 18

4 Alina 90 20
5 John 96 18

1. Creating view
A view can be created using the CREATE VIEW statement. We can create a view from a
single table or multiple tables.

Syntax:

1. CREATE VIEW view_name AS


2. SELECT column1, column2.....
3. FROM table_name
4. WHERE condition;

2. Creating View from a single table


In this example, we create a View named DetailsView from the table Student_Detail.

Query:

1. CREATE VIEW DetailsView AS


2. SELECT NAME, ADDRESS
3. FROM Student_Details
4. WHERE STU_ID < 4;

Just like table query, we can query the view to view the data.

1. SELECT * FROM DetailsView;

Output:

NAME ADDRESS

Stephan Delhi
Kathrin Noida

David Ghaziabad

3. Creating View from multiple tables


View from multiple tables can be created by simply include multiple tables in the SELECT
statement.

In the given example, a view is created named MarksView from two tables
Student_Detail and Student_Marks.

Query:

1. CREATE VIEW MarksView AS


2. SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
3. FROM Student_Detail, Student_Mark
4. WHERE Student_Detail.NAME = Student_Marks.NAME;

To display data of View MarksView:

1. SELECT * FROM MarksView;

NAME ADDRESS MARKS

Stephan Delhi 97

Kathrin Noida 86

David Ghaziabad 74

Alina Gurugram 90

4. Deleting View
A view can be deleted using the Drop View statement.

Syntax

1. DROP VIEW view_name;

Example:

If we want to delete the View MarksView, we can do this as:

1. DROP VIEW MarksView;

Common questions

Powered by AI

From a performance perspective, views can result in inefficient query execution if not properly indexed, as they are virtual tables constructed dynamically upon query execution, which can lead to higher computation loads. Additionally, complex views that involve multiple joins or aggregations can lead to increased execution time and resource consumption due to the additional processing required to construct the view's dataset every time it is queried .

The recursive factorial function works by calling itself with a decremented argument until it reaches the base case, which is 0, where it returns 1. For each call, the function multiplies the current number by the result of the factorial of the next smaller number until it reaches the base. The advantage of this approach is its simplicity and clear expression of the algorithm compared to iterative methods, although it can be less efficient in terms of execution time and memory usage for large numbers .

DML operations such as INSERT, UPDATE, or DELETE have restrictions on views from a single base table. These include prohibitions on INSERT if the base table includes NOT NULL columns absent in the view, and restrictions on INSERT or UPDATE if columns included have group functions or defined by expressions. Furthermore, such DML operations can't be executed on views if the 'WITH READ ONLY' option is enabled, or if the view includes GROUP BY, DISTINCT, or references a pseudocolumn like ROWNUM .

The 'IS' and 'AS' keywords can both be used to declare the start of a function body, but 'AS' is specifically used when creating a standalone function. This implies that while both keywords serve a similar purpose in demarcating the function body, 'AS' is typically preferred for standalone function declarations to enhance readability and clarity in standalone scripts .

Logical data independence through views is achieved by using views to abstract and encapsulate the underlying schema and table structures. Applications can interact with the views, which serve as interfaces hiding the complexities of the actual tables. This means changes to the table structures, like adding or renaming columns, do not necessarily impact applications if they interact only with views, thereby achieving a separation between the logical and physical data structure .

Views in SQL simplify complex queries by abstracting the underlying joins and aggregations within the view definition. This allows users to interact with a simplified single table structure even though it may involve data from multiple tables. This not only reduces complexity but also enhances query simplicity, security, consistency, and potentially improves maintainability by centralizing logic that might be redundant across different queries .

SQL views cannot be created on temporary tables. This constraint is imposed because views are designed to provide a persistent and consistent snapshot of data over time, whereas temporary tables' data scope is limited to the session in which they are created. Using views on temporary data doesn't align with their purpose of providing a stable and reusable interface for long-term data interactions .

Views enhance security by excluding sensitive information from the view itself, thereby controlling access to underlying data that might be sensitive. In terms of data integrity, views allow data to be accessed and entered in a way where the Database Management System (DBMS) can automatically enforce integrity constraints, ensuring data accuracy and consistency .

To create a view from multiple tables, the SQL command starts with 'CREATE VIEW view_name AS' followed by a SELECT statement that specifies the columns and tables involved; these tables should be included in the FROM clause with any necessary WHERE conditions. To delete a view, you use the 'DROP VIEW view_name;' command. An example given involves creating MarksView from Student_Detail and Student_Marks tables by joining them on the NAME attribute .

The findMax function takes two IN type number parameters and compares them using an IF-ELSE statement. If the first parameter x is greater than the second y, it assigns the value of x to the local variable z; otherwise, it assigns y to z. The function then returns z, which holds the maximum value between x and y .

You might also like