Using OUT Parameters In Procedure

Sql>  CREATE OR REPLACE PROCEDURE query_emp
 (id     IN  employees.employee_id%TYPE,
  name   OUT employees.last_name%TYPE,
  salary OUT employees.salary%TYPE) IS
BEGIN
  SELECT   last_name, salary INTO name, salary
   FROM    employees
   WHERE   employee_id = id;
END query_emp;


DECLARE
  emp_name employees.last_name%TYPE;
  emp_sal  employees.salary%TYPE;
BEGIN
  query_emp(171, emp_name, emp_sal);
  DBMS_OUTPUT.PUT_LINE(emp_name||' earns '||to_char(emp_sal, '$999,999.00'));
END;

Read More

Procedure with Parameters

An IN parameter (the default) provides values for a subprogram to process.
An OUT parameter returns a value to the caller.
An IN OUT parameter supplies an input value, which may be returned (output) as a modified value.


CREATE OR REPLACE PROCEDURE raise_salary
  (id      IN employees.employee_id%TYPE,
   percent IN NUMBER)
IS
BEGIN
  UPDATE employees
  SET    salary = salary * (1 + percent/100)
  WHERE  employee_id = id;
END raise_salary;
/

BEGIN
  raise_salary(176, 10);
END;
/

Read More

What Is a Procedure?

What Is a Procedure?

A procedure: Is a type of subprogram that performs an action
Can be stored in the database as a schema object
Promotes reusability and maintainability

CREATE [OR REPLACE] PROCEDURE procedure_name
 [(parameter1 [mode] datatype1,
   parameter2 [mode] datatype2, ...)]
IS|AS
  [local_variable_declarations; …]
BEGIN  -- actions;
END [procedure_name];


sql> create table dept as select * from departments;


CREATE PROCEDURE add_dept IS
 dept_id dept.department_id%TYPE;
 dept_name dept.department_name%TYPE;
BEGIN
 dept_id:=280;
 dept_name:='ST-Curriculum';
 INSERT INTO dept(department_id,department_name)
 VALUES(dept_id,dept_name);
 DBMS_OUTPUT.PUT_LINE(' Inserted '|| SQL%ROWCOUNT ||' row ');
END;

To invoke a procedure in iSQL*Plus, use the following EXECUTE command:

begin

add_dept;

End;

Read More