- #Store procedure with cursor in adventureworks2012 example how to
- #Store procedure with cursor in adventureworks2012 example code
Database-resident program units also ensure that the same processing is applied to the data when the code is invoked, making the development of applications easier and providing consistency between developers. This functionality enables commonly required code to be written and tested once and then accessed by any application that requires the code. Oracle Database XE offers the capability to store programs in the database. 1> DECLARE 2> CURSOR updated_sal is 3> SELECT empID,salary 4> FROM emp_tbl 5> pre_sal number 6> BEGIN 7> FOR emp_rec IN updated_sal LOOP 8> pre_sal := emp_rec.salary 9> emp_salary_increase(emp_rec.empID, emp_rec.salary) 10> dbms_output.put_line('The salary of ' || emp_rec.empID || 11> ' increased from '|| pre_sal || ' to '||emp_rec.Overview of Procedures, Functions, and Packages
#Store procedure with cursor in adventureworks2012 example how to
The below PL/SQL block shows how to execute the above 'emp_salary_increase' procedure. Using IN OUT parameter in procedures: 1> CREATE OR REPLACE PROCEDURE emp_salary_increase 2> (emp_id IN emptbl.empID%type, salary_inc IN OUT emptbl.salary%type) 3> IS 4> tmp_sal number 5> BEGIN 6> SELECT salary 7> INTO tmp_sal 8> FROM emp_tbl 9> WHERE empID = emp_id 10> IF tmp_sal between 1000 THEN 11> salary_inout := tmp_sal * 1.2 12> ELSIF tmp_sal between 2000 THEN 13> salary_inout := tmp_sal * 1.3 14> ELSIF tmp_sal > 30000 THEN 15> salary_inout := tmp_sal * 1.4 16> END IF 17> END 18> / In line no 8 we are displaying the id and the employee name which we got from the procedure ‘emp_name’. In line no 7 we are calling the procedure ‘emp_name’, we are passing the ‘id’ as IN parameter and ‘empName’ as OUT parameter. In line no 3 we are creating a cursor ‘id_cur’ which contains the employee id. 1> DECLARE 2> empName varchar(20) 3> CURSOR id_cur SELECT id FROM emp_ids 4> BEGIN 5> FOR emp_rec in id_cur 6> LOOP 7> emp_name(emp_rec.id, empName) 8> dbms_output.putline('The employee ' || empName || ' has id ' || emp-rec.id) 9> END LOOP 10> END 11> / We can call the procedure ‘emp_name’ in this way from a PL/SQL Block. 1> CREATE OR REPLACE PROCEDURE emp_name (id IN NUMBER, emp_name OUT NUMBER) 2> IS 3> BEGIN 4> SELECT first_name INTO emp_name 5> FROM emp_tbl WHERE empID = id 6> END 7> / Let’s create a procedure which gets the name of the employee when the employee id is passed. The below examples show how to create stored procedures using the above three types of parameters. The General syntax to create an IN OUT parameter is CREATE PROCEDURE proc3 (param_name IN OUT datatype) This parameter is used if the value of the parameter will be changed in the procedure. But this is possible only if the value passed to the procedure and output value have a same datatype. This parameter is used if the value of the IN parameter can be changed in the calling program.īy using IN OUT parameter we can pass values into a parameter and return a value to the calling program using the same parameter. The IN OUT parameter allows us to pass values into a procedure and get output values from the procedure. The parameter should be explicity declared as OUT parameter. The General syntax to create an OUT parameter is CREATE PROCEDURE proc2 (param_name OUT datatype) This is a write-only parameter i.e, we cannot pass values to OUT paramters while executing the stored procedure, but we can assign values to OUT parameter inside the stored procedure and the calling program can recieve this output value. The OUT parameters are used to send the OUTPUT from a procedure or a function.
We can pass values to the stored procedure through these parameters or variables. This is similar to passing parameters in programming languages. NOTE: If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter. This is similar to a return type in functions.ģ) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures. In PL/SQL, we can pass parameters to procedures and functions in three ways.ġ) IN type parameter: These types of parameters are used to send values to stored procedures.Ģ) OUT type parameter: These types of parameters are used to get values from stored procedures. How to pass parameters to Procedures and Functions in PL/SQL?