Oracle stored procedure parameters introduction in, out, in out, and execute (ii)

Stored Procedure Parameters
1.IN Default
2.OUT
3.IN out can be modified

Formal parameters and arguments
--Formal parameters

1 CREATE PROCEDURE  Number  Number  2is3BEGIN  ... 4 END raise_sal;

--Practical Parameters

1 emp_id:=+; 2 raise_sal (emp_id,);

--In Parameter type demo salary gain percent

1 CREATE OR REPLACE PROCEDUREraise_salary2 (3Idinchemployees.employee_id%TYPE,4    PERCENT inch  Number5 )6  is7 BEGIN8   UPDATEEmployeesSETSalary=Salary*(1 + PERCENT/ -)WHEREemployee_id=ID;9 ENDraise_salary;Ten 

--Validation of results

1 SELECT *  fromEmployeesWHEREemployee_id= 206;2 --execute in PL/SQL3 BEGIN4Raise_salary (206,Ten);5 END;6 EXECUTERaise_salary (206,Ten);--SQL command line execution

--Out Parameter type Demo query specify employee's name and salary

1 CREATE OR REPLACE PROCEDUREquery_emp2 (3Idinchemployees.employee_id%TYPE,4NAME out Employees.last_name%TYPE,5Salary out Employees.salary%TYPE6 )7  is 8 BEGIN9   SELECTLast_name,salary intoName,salary fromEmployeesWHEREemployee_id=ID;Ten END;

--Results Demo

1 --executing at the command line2 SETServeroutput on;3 4 DECLARE5Emp_name Employees.last_name%TYPE;6Emp_sal employees.salary%TYPE;7 BEGIN8Query_emp (206, emp_name,emp_sal);9Dbms_output.put_line ('Name:' ||emp_name);TenDbms_output.put_line ('Salary:' ||emp_sal); One END; A  - --command line Execution (ii) -VARIABLE NAMEVARCHAR2( -) theVARIABLE Sal Number - EXECUTEQuery_emp (206,: Name,:sal);

--In Out type parameter
--Parameter transmission mode

1. Location Transfer
2. Name Transfer Name=>value
3. Mixing mode (up to)

1 CREATE OR REPLACE PROCEDUREadd_dept2 (3NAMEinchDepartments.department_name%TYPE,4Locinchdepartments.location_id%TYPE5 )6  is7 BEGIN8   INSERT  intodepartments (department_id,department_name,location_id)9   VALUESTen (departments_seq.nextval,name,loc); One   COMMIT; A ENDadd_dept;

--Validation of results

1 --Execution2 EXECUTEAdd_dept ('Training',2500);3 --Validation4 SELECT *  fromDepartmentsWHERElocation_id= 2500;5 --mode of execution two6 EXECUTEAdd_dept (Loc=2400, NAME='Education');7 --Validation8 SELECT *  fromDepartmentsWHERElocation_id= 2400;

--Use default values to define
--NAME Departments.department_name%type = ' Unknow ',
--Loc Departments.location_id%type = 1800

1 CREATE  OR  REPLACE PROCEDUREadd_dept2 (3NAME Departments.department_name%TYPE:= 'Unknow',4Loc departments.location_id%TYPE:= 18005 )6  is7 BEGIN8   INSERT  intodepartments (department_id,department_name,location_id)9   VALUESTen (departments_seq.nextval,name,loc); One   COMMIT; A ENDadd_dept;

--Results Demo

1 -- Execution 2 EXECUTE add_dept (); 3 -- Validation 4 SELECT *  from WHERE location_id=1800;

Oracle stored procedure parameters introduction in, out, in out, and execute (ii)