Function VS Procedure:
Procedure:
procedure is a group of PL/SQL statements that can be called by name. The call specification (sometimes called call spec) specifies a java method or a third-generation language routine so that it can be called from SQL and PL/SQL.
The procedure contains a header and a body.
- Header: The header contains the name of the procedure and the parameters or variables passed to the procedure.
- Body: The body contains a declaration section, execution section and exception section similar to a general PL/SQL block.
How to pass parameters in procedure:
When you want to create a procedure or function, you have to define parameters .There is three ways to pass parameters in procedure:
- IN: It is a default parameter. It passes the value to the subprogram.
- OUT: It must be specified. It returns a value to the caller.
- IN OUT: It must be specified. It passes an initial value to the subprogram and returns an updated value to the caller.
| Sl No |
Function |
Procedure |
| 1 |
A function has a return type and returns a value. |
A procedure does not have a return type. But it returns values using the OUT parameters. |
| 2 |
You cannot use a function with Data Manipulation queries. Only Select queries are allowed in functions. |
You can use DML queries such as insert, update, select etc… with procedures. |
| 3 |
A function does not allow output parameters |
A procedure allows both input and output parameters. |
| 4 |
You cannot manage transactions inside a function. |
You can manage transactions inside a procedure.. |
| 5 |
You cannot call stored procedures from a function |
You can call a function from a stored procedure.. |
| 6 |
You can call a function using a select statement. |
You cannot call a procedure using select statements. |
Procedure code :
create or replace procedure ddlUktable is
ddl_qry varchar2(4000);
begin
ddl_qry := 'CREATE TABLE UKACCOUNT.uktable(
id NUMBER(3),
name VARCHAR2(50)
primary key(id)
)';
EXECUTE IMMEDIATE ddl_qry;
end ddlUktable;
CREATE OR REPLACE Procedure UpdateCourse
( name_in IN varchar2 )
IS
cnumber number;
cursor c1 is
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
INSERT INTO student_courses
( course_name,
course_number )
VALUES
( name_in,
cnumber );
commit;
close c1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Row query parsing in pl/sql using procedure
create or replace procedure VerifyQuerySyntax(p_query in clob, isvalid out integer) is
c number;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (c, p_query, DBMS_SQL.Native);
DBMS_SQL.CLOSE_CURSOR (c);
DBMS_OUTPUT.put_line('Correct' );
isvalid:=1;
--Statement parsed successfully
EXCEPTION
WHEN OTHERS THEN
--statement invalid
DBMS_OUTPUT.put_line('Error : ' || Sqlcode||' - '|| sqlerrm );
DBMS_SQL.CLOSE_CURSOR(c);
isvalid:=0;
END VerifyQuerySyntax;
/
Declare
isValid integer;
query varchar2(32767) := 'SELECT * FROM Customer';
Begin
VerifyQuerySyntax(query , isValid );
dbms_output.put_line( 'isValid : '||isValid );
End;
/
