SQL 1

PL/SQL offers the following advantages:
 Reduces network traffic This one is great advantages of PL/SQL. Because

PL/SQL nature is entire block of SQL statements execute into oracle engine all
at once so it's main benefit is reducing the network traffic.
 Procedural language support PL/SQL is a development tools not only for data
manipulation futures but also provide the conditional checking, looping or
branching operations same as like other programming language.
 Error handling PL/SQL is dealing with error handling, It's permits the smart
way handling the errors and giving user friendly error messages, when the
errors are encountered.
 Declare variable PL/SQL gives you control to declare variables and access
them within the block. The declared variables can be used at the time of query
processing.
 Intermediate Calculation Calculations in PL/SQL done quickly and efficiently

without using Oracle engines. This improves the transaction performance.
 Portable application Applications are written in PL/SQL are portable in

any Operating system. PL/SQL applications are independence program to run
any computer.
Following are few more PL/SQL advantages:
 PL/SQL is a very secure functionality tool for manipulating, controlling,

validating, and restricting unauthorized access data from the SQL database.
 Using PL/SQL we can improve application performance. It also allows

to deal with errors so we can provide user friendly error messages.
 PL/SQL have a great functionality to display multiple records from the multiple
tables at the same time.
 PL/SQL is capable to send entire block of statements and execute it in

the Oracle engine at once.
Condition control statements
1. IF Statement
IF condition
Statement;
1
DECLARE
@a int = 200;
BEGIN
-- check the boolean condition using if statement
IF( @a < 10 )
-- if condition is true then print the following
print('a is less than 10 ');
print('value of a is : ' + CAST(@a AS varchar));
END;
2. IF-THEN-ELSE Statement
IF condition
[Statements to execute when condition is TRUE]
ELSE
[Statements to execute when condition is FALSE]
DECLARE
@a int = 200;
BEGIN
IF( @a < 10 )
ELSE
print('a is not less than 10 ');
END;
3. IF-THEN-ELS-IF-ELSE Statement
IF condition1
Statements to execute when condition1 is TRUE
ELSE IF condition2
Statements to execute when condition2 is TRUE
ELSE
Statements to execute when both condition1 and condition2 are FALSE
END;
DECLARE
@a int = 200;
BEGIN
IF( @a < 10 )
2
ELSE IF (@a > 10)
print('a is greater than 10 ');
ELSE
print('a is zero');
END;
Iterative statements
While Loop
WHILE <condition>
LOOP statements;
END;
Important steps to follow when executing a while loop:
1) Initialise a variable before the loop body.

2) Increment the variable in the loop.
While loop
DECLARE @count INT;
SET @count = 1;
WHILE @count<= 10
BEGIN
PRINT @count
SET @count = @count + 1;
END;
SINGLE VALUE FUNCTIONS
 Scalar Function: It is a function that return single value. Generally, we have to define the
function body between BEGIN … END block. We can use any SQL data type as the return
type except text, image, ntext, cursor, and timestamp.
3
The basic syntax behind the SQL Server User defined functions is as shown below:
CREATE FUNCTION Function_Name(@Parameter_Name Data_type,

.... @Parameter_Name Data_type
)
RETURNS Data_Type
AS
BEGIN
-- Function Body
RETURN Data
END
 Return_Type:
 Data Type: Please specify the data type of return value. For example, VARCHAR, INT, FLOAT
etc.
 Data: Please specify the return value, and it should match the Data Type. This can be single
value or Table
 Function_Name: You can specify any name you wish to give other than the system reserved
keywords. Please try to use meaningful names so that you can identify them easily.
 @Parameter_Name: Every function accepts zero or more parameters, it’s completely depends
upon the user requirements. While declaring the parameters don’t forget the appropriate data
type. For example (@name VARCHAR(50), @number INT)
 Function Body: Any SQL query, or any complex mathematical calculations you want to implement
in this particular function.
User defined functions without parameters
Scalar-Valued functions
Select * from emptable
4
Example:1
CREATE FUNCTION average_salary()
RETURNS int
AS
BEGIN
RETURN (SELECT avg(salary) FROM emptable)
END
SELECT id,name,salary,dbo.average_salary() as 'Average salary' from emptable;
Example:2
CREATE FUNCTION max_salary()

RETURNS float
AS
BEGIN
RETURN (SELECT avg(salary) FROM emptable)
END
SELECT id,name,salary from emptable where salary > dbo.max_salary();
5
Table-Valued functions
CREATE FUNCTION TopFiveEmployees()

RETURNS TABLE
AS
RETURN (SELECT TOP 5 id,name,salary FROM emptable)
SELECT * from dbo.TopFiveEmployees();
User defined functions with parameters
CREATE FUNCTION total_sales(

@quantity INT,
@unit_price INT
)RETURNS INT
AS
BEGIN
RETURN @quantity * @unit_price;
END;
SELECT dbo.total_sales(12,100) net_sale

SQL 1

Uploaded by

Document Informationclick to expand document information

Copyright:

Available Formats

SQL 1

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL 1

Uploaded by

Copyright:

Available Formats

PL/SQL offers the following advantages:

 Reduces network traffic This one is great advantages of PL/SQL. Because

 Intermediate Calculation Calculations in PL/SQL done quickly and efficiently

 Portable application Applications are written in PL/SQL are portable in

Following are few more PL/SQL advantages:

 PL/SQL is a very secure functionality tool for manipulating, controlling,

 Using PL/SQL we can improve application performance. It also allows

 PL/SQL is capable to send entire block of statements and execute it in

Condition control statements

Important steps to follow when executing a while loop:

1) Initialise a variable before the loop body.

DECLARE @count INT;

SET @count = @count + 1;

SINGLE VALUE FUNCTIONS

CREATE FUNCTION Function_Name(@Parameter_Name Data_type,

User defined functions without parameters

Select * from emptable

SELECT id,name,salary,dbo.average_salary() as 'Average salary' from emptable;

CREATE FUNCTION max_salary()

SELECT id,name,salary from emptable where salary > dbo.max_salary();

CREATE FUNCTION TopFiveEmployees()

SELECT * from dbo.TopFiveEmployees();

User defined functions with parameters

CREATE FUNCTION total_sales(

SELECT dbo.total_sales(12,100) net_sale

You might also like