PL/SQL PROCEDURE

-

A Procedure is simply a block which performs one or more specific tasks. 

A procedure contains a header and a body:

  • Header: The header contains name of the procedure and the variables passed to the procedure.
  • Body: The body contains a declaration section, execution section and exception section.
We can pass the parameters in the procedure in three ways:
  • IN parameter: It can be referenced by procedure or function. The value of the parameters can't be overwritten by the procedure or the function.
  • OUT parameter: It can't be referenced by procedure or function. The values of the parameters can be overwritten by the procedure or the function.
  • INOUT parameter: It can referenced by the procedure or function. The values of the parameters can be overwritten by the procedure or function.
Syntax:

CREATE Procedure_name
    [parameters]
IS
    [declaration section]
BEGIN
    [executable section]
[EXCEPTION
    Exception_section]
END[procedure_name]

Example:

create procedure "INSERTUSER"(
id IN NUMBER,
name IN VARCHAR2)
is
begin
insert into values(id, name);
end;

PL/SQL program to call procedure:

BEGIN
    insertuser(101, 'AKSHAY');
    dbms_output.pu_line('record inserted succesfully');
END;

Syntax to DROP Procedure:
DROP PROCEDURE procedure_name


Source: javatpoint

Comments

Post a Comment

Popular posts from this blog

THREE LEVELS OF DATA INDEPENDENCE

-
Image
THREE LEVELS OF DATA INDEPENDENCE It divides the entire system into three inter-related but independent modules: Physical Level: Here, information about the location of database objects in the data store is kept. Various DBMS users are unaware of the locations of these objects. The Physical level of database describes how the data is stored in secondary storage devices such as disks and tapes. Conceptual Level: Here, data is represented in the form of various tables. For Example, STUDENT database may contain NAME and COURSE tables, but the users are unaware of their storage. It refers to what kind of data to be stored in a database. External Level: Here, it specifies the view of data in terms of conceptual level tables. It can generate different views of the database for different users. It focuses main on data abstraction.  Data Independence: It means change of data at one level must not affect the another level. Physical Data Independence: Any change in the physical location of the t
Read more

Python-HackerRank Problem List Comprehensions

-
Problem Print a list of all possible coordinates given by (i,j,k) on a 3D grid where the sum of i+j+k is not equal to  n. Here, 0<=i<=x;0<=j<=y;0<=k<=z. Please use list comprehensions rather than multiple loops, as a learning exercise.  Source Code   if  __name__ ==  '__main__' :     x =  int ( input ())     y =  int ( input ())     z =  int ( input ())     n =  int ( input ())      print   ([[ a , b , c ]   for  a  in   range ( 0 , x+1 )   for  b  in   range ( 0 , y+1 )   for  c  in   range ( 0 , z+1 )     if  a + b + c != n  ])                 
Read more

Python Problem Solving - Lonely Integer

-
Given an array of integers, where all elements but one occur twice, find the unique element. Solution: n  =  int ( input ()) array  =  list ( map ( int ,   input () . rstrip () . split ())) for   element   in   array :      count  =  array . count ( element )      if   count  ==  1 :          print ( element )
Read more