Relational Database Management System- 22CTU08
SRI KRISHNA ARTS AND SCIENCE COLLEGE
DEPARTMENT OF COMPUTER TECHNOLOGY AND
DATA SCIENCE
Academic Year 2023-24
Faculty Handling
G. Sumalatha
Assistant Professor
Department of computer Technology
SKASC 1
Relational Database Management System- 22CTU08
SRI KRISHNA ARTS AND SCIENCE COLLEGE
DEPARTMENT OF COMPUTER TECHNOLOGY AND
DATA SCIENCE
COURSE NAME : Relational Database Management System
COURSE CODE : 22CTU08
CLASS ROOM CODE : vcpp3ll
UNIT 5 – Introducing Stored Procedure and Functions
SKASC 2
Relational Database Management System- 22CTU08
• AGENDA
• Creating stored Procedures and functions
• Procedures and functions
• D/B anonymous blocks and subprograms
• Passing a parameters to functions
• Invoking the functions with parameters
SKASC 3
Relational Database Management System- 22CTU08
Procedures and functions
TOPIC LINK
Procedures https://youtu.be/buaSuEMi4lw
Functions https://youtu.be/6OJIrPx61mU
SKASC 4
Relational Database Management System- 22CTU08
Objectives
• After completing this lesson, you should be able to do the
following:
• Differentiate between anonymous blocks and
subprograms
• Create a simple procedure and invoke it from an
anonymous block
• Create a simple function
• Create a simple function that accepts a parameter
• Differentiate between procedures and functions
SKASC 5
Relational Database Management System- 22CTU08
Procedures and functions
• Are named PL/SQL blocks
• Are called PL/SQL subprograms
• Have block structures similar to anonymous
blocks:
• Optional declarative section
• (without the DECLARE keyword)
• Mandatory executable section
• Optional section to handle exceptions
SKASC 6
Relational Database Management System- 22CTU08
Difference b/w anonymous blocks and subprograms
7
SKASC
Relational Database Management System- 22CTU08
Procedure - syntax
8
SKASC
Relational Database Management System- 22CTU08
Procedure - example
...
CREATE TABLE dept AS SELECT * FROM
departments;
CREATE PROCEDURE add_dept IS
v_dept_id dept.department_id%TYPE;
v_dept_name dept.department_name%TYPE;
BEGIN
v_dept_id:=280;
v_dept_name:='ST-Curriculum';
INSERT INTO
dept(department_id,department_name)
VALUES(v_dept_id,v_dept_name);
DBMS_OUTPUT.PUT_LINE(' Inserted '||
SQL%ROWCOUNT ||' row ');
END;
SKASC 9
Relational Database Management System- 22CTU08
Procedure example continued
The source of the procedure is stored in the user_source table.
You can check the source for the procedure by issuing the following command
SELECT * FROM user_source WHERE name='ADD_DEPT';
SKASC 10
Relational Database Management System- 22CTU08
Invoking the procedures
• The following shows how to invoke a procedure from
an anonymous block.
• You have to include the call to the procedure in the
executable section of the anonymous block.
• Similarly, you can invoke the procedure from any
application, such as a Forms application, a Java
application, and so on.
• The SELECT statement in the code checks to see
whether the row was successfully inserted.
• You can also invoke a procedure with the SQL
statement CALL <procedure_name>.
SKASC 11
Relational Database Management System- 22CTU08
Invoking the procedures
SKASC 12
Relational Database Management System- 22CTU08
Function - syntax
The argument list is optional in the function declaration.
The difference between a procedure and a function is
that a function must return a value to the calling program.
Therefore, the syntax contains return_type, which
specifies the data type of the value that the function
returns.
A procedure may return a value via an OUT or IN OUT
parameter.
SKASC 13
Relational Database Management System- 22CTU08
Function - syntax
The following Example shows how to declare the
functions in Oracle
SKASC 14
Relational Database Management System- 22CTU08
Function - syntax
SKASC 15
Relational Database Management System- 22CTU08
Invoking the functions
.
1. You include the call to the function in the
executable section of the anonymous block.
The function is invoked as a part of a statement.
Remember that the check_sal function returns
Boolean or NULL.
2. Thus the call to the function is included as the
conditional expression for the IF block.
3. Note: You can use the DESCRIBE command to
check the arguments and return type of the
function, as in the following example:
4. DESCRIBE check_sal;
SKASC 16
Relational Database Management System- 22CTU08
Invoking the functions
SKASC 17
Relational Database Management System- 22CTU08
Passing a parameters to functions
• Remember that the function was hard-coded to
check the salary of the employee with the
employee ID 205.
• The code shown in the slide removes that
constraint because it is rewritten to accept the
employee number as a parameter.
• You can now pass different employee numbers and
check for the employee’s salary.
• You learn more about functions in the course titled
Oracle Database 11g: Develop PL/SQL Program
Units.
• The output of the code example in the slide is as
follows:
SKASC 18
Relational Database Management System- 22CTU08
Passing a parameters to a function
SKASC 19
Relational Database Management System- 22CTU08
20
Database Management System Using Oracle-19CSS16
SUMMARY
• Creating stored Procedures and functions
• Procedures and functions
• D/B anonymous blocks and subprograms
• Passing a parameters to functions
• Invoking the functions with parameters
SKASC 21
Database Management System Using Oracle-19CSS16
KEYWORDS
• Procedures
• Functions
• D/B Anonymous Blocks
• Subprograms
• Passing A Parameters
• Invoking The Functions
SKASC 22
Database Management System Using Oracle-19CSS16
MCQ
1. Which is a database object that groups logically related PL/SQL
types, objects and subprograms? *
A) Package
B) Module
C) Body
D) Name
SKASC 23
Database Management System Using Oracle-19CSS16
MCQ
2. To call a subprogram directly, users must have the EXECUTE privilege on
that subprogram. By granting the privilege, you allow a user to
A) Call the subprogram directly
B) Compile functions and procedures that call the subprogram
C) Both A & B
SKASC 24
Database Management System Using Oracle-19CSS16
MCQ
3. In which mode parameter lets you pass values to the subprogram being
called? It cannot be assigned a value.
A) Using the IN mode
B) Using the OUT mode
C) Both A & B
SKASC 25
Database Management System Using Oracle-19CSS16
MCQ
4. In which subprogram a RETURN statement does not return a value and so
cannot contain an expression?
A) In Procedures
B)In Functions
C) Both A & B
SKASC 26
Database Management System Using Oracle-19CSS16
MCQ
5. Which of he following is used to input the entry and give the result in a
variable in a procedure ?
A)Put and get
B)Get and put
C)Out and In
D) In and out
SKASC 27
Relational Database Management System- 22CTU08
Topic for next hour
SKASC 28
