ORACLE
ORACLE ADVANCE
PROCEDURES
FUNCTION
CURSOR
TRIGGER
ORACLE TUTORIAL
Oracle Introduction
What Is Oracle
Create Tables
What Is Oracle
ORACLE TABLES
Create Tables
Create Table As
Alter Table
Drop Table
Global Temorary Tables
Local Temorary Tables
ORACLE VIEWS
Create View
ORACLE QUERIES
SELECT
INSERT
INSERT
INSERT ALL
UPDATE
DELETE
TRUNCATE TABLE
ORACLE CLAUSES
DISTINCT
FROM
ORDER BY
GROUP BY
HAVING
ORACLE OPERATORS
UNION
UNION ALL
INTERSECT
MINUS
ORACLE JOINS
INNER JOIN
OUTER JOIN
EQUI JOIN
SELF JOIN
CROSS JOIN
ANTI JOIN
SEMI JOIN

PROCEDURES



Oracle - Procedures


A Procedure is a subprogram unit that consists of a group of PL/SQL statements. Each procedure in Oracle has its own unique name by which it can be referred. This subprogram unit is stored as a database object.
A 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.


Create Procedure

Syntax :

CREATE [OR REPLACE] PROCEDURE procedure_name  
    [ (parameter [,parameter]) ]  
IS  
    [declaration_section]  
BEGIN  
    executable_section  
[EXCEPTION  
    exception_section]  
END [procedure_name];  

Following are the three types of procedures that must be defined to create a 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.


    Oracle Create procedure example :

    In this example, we are going to insert record in the "user" table. So you need to create user table first.


    Table creation:
    create table user(id number(10) primary key,name varchar2(100));  
    
    

    Now write the procedure code to insert record in user table.


    Procedure Code:

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

    Output :
    Procedure created.
    

    Oracle program to call procedure :

    Let's see the code to call above created procedure.

    BEGIN    
       insertuser(101,'Rahul');  
       dbms_output.put_line('record inserted successfully');    
    END;    
    /    
    

    Now, see the "USER" table, you will see one record is inserted.

    IDName
    101Rahul

    Oracle Drop Procedure :

    Syntax
    DROP PROCEDURE procedure_name;   
    
    

    Example to drop procedure
    DROP PROCEDURE pro1;  
    
    


    What Is Oracle

    What Is Oracle

    posted on 2019-11-29 01:11:21 - ORACLE Tutorials


    TRIGGER

    ORACLE - Trigger

    posted on 2019-11-28 22:14:22 - ORACLE Tutorials


    CURSOR

    ORACLE - Cursor

    posted on 2019-11-28 22:13:54 - ORACLE Tutorials


    Prompt Examples

    ChatGPT Prompt Examples

    posted on 2023-06-21 22:37:19 - ChatGPT Tutorials


    Use Cases

    Chat GPT Key Use Cases

    posted on 2023-06-21 21:03:17 - ChatGPT Tutorials


    Prompt Frameworks

    Prompt Frameworks

    posted on 2023-06-21 19:33:06 - ChatGPT Tutorials