StoredprocedureinOracleplsql

​
                                               Learn Stored procedure concept in Oracle plsql

Here in this blog just we are going to see stored procedure concept in Oracle plsql.
We will include further topics in Oracle plsql training in separate blogs.

1. Stored procedure is also called as PL/SQL block
2. Stored procedure receive input in the form of parameters and performs some task and it may or may not returns a value.
3. Procedures are created to perform one or more DML operations over database.
    (Ex: To create one or more DML operation (Insert or Delete or update) over database, we are creating  
     Stored procedure)

SYNTAX for Stored procedure:

CREATE OR REPLACE PROCEDURE <NAME> (parameters if any)  
IS
          <variables>;
BEGIN
          statements;
END;

Parameters are of 3 types. Parameters is used to pass the values.

IN (Default)   -> procedure receives value from calling program.
OUT              -> Procedure send values to calling program
IN OUT         -> Calling program sends the input to procedure then procedure sends the output to calling 
                          program

CREATE OR REPLACE PROCEDURE
          RAISE_SALARY(EMPID IN NUMBER, AMT IN NUMBER, SAMPLE OUT NUMBER)
IS
BEGIN
          UPDATE EMP SET SALARY=SALARY+1000
                       WHERE EMPNO = 7844;                  (Employee is getting salary INR 2000)
          COMMIT;
          SELECT SALARY INTO SAMPLE FROM EMP WHERE EMPNO=EMPID;
END;

VARIABLE K NUMBER  - - - > Here we are declaring the variable as K.

Run the query:

EXECUTE RAISE_SALARY(7844, 1000, :K);   - - > 2000+1000 = 3000

To see the value, use the below print command

print :k

k - > 3000
                                                         Suggestions are always welcome !!!

Comments

Post a Comment

Popular posts from this blog

Decode function in Oracle pl/sql

Image
DECODE function in Oracle pl/sql As part of Oracle pl sql training , we will see this DECODE function in Oracle plsql. a> This DECODE function is available in Oracle plsql. b> This function is not available in MySQL and SQl server. DECODE syntax : SELECT DECODE ("Column_name", "search_value_1","result_1",                                                                "search_value_2","result_2",                                                                "search_value_3","result_3"                                             ...
Read more

SUBSTR function in Oracle pl/sql 

Image
SUBSTR function in Oracle Pl Sql In real time projects the requirement might come like to extract the length of data from the particular data. In this blog, we will see how we can execute this requirement through Oracle pl sql by using Oracle/PLSQL SUBSTR function.   Syntax of SUBSTR function in Oracle PL SQL is: SUBSTR( string, starting_position [, Total length of the data ] ) Parameters of SUBSTR string Input data which we are going to use for extraction process. starting position It denotes the starting position of length of the data. The position of the string will always start from 1. length Optional. It denotes the length of the character to extract. If this parameter is not given, then the SUBSTR function will return the entire string from the starting position which we given in syntax. The SUBSTR function is used to returns a string value. If length of the data is a negative number, then the SUBSTR function will return a NULL value. Rule of...
Read more