ORACLE Stored Procedure

ORACLE Stored Procedure

Prelude: 1: A Necessary concept: ORACLE provides the ability to store PL/SQL programs in databases and run them anywhere. In this way, stored procedures or functions are called. Procedures and functions are collectively referred to as PL/SQL subprograms. They are named PL/SQL blocks and are stored in the database, and exchange information with the caller through input/output parameters or input/output parameters. The only difference between a process and a function is that a function always returns data to the caller, while a process does not return data. 2: Create a stored procedure create a stored procedure on the oracle server. It can be called by multiple applications. You can pass parameters to the stored procedure, or send parameters to the stored procedure. Syntax: CREATE [or replace] PROCEDURE Procedure_name [(argment [{IN | in out}] Type, argment [{IN | OUT | in out}] Type] [authid definer | CURRENT_USER] {IS | AS} <Type. description of variables> BEGIN <execution part> EXCEPTION <optional EXCEPTION error handling program> END; 3: Example: -- Define a stored procedure, obtain the total salary of a given department (using the out parameter). The Department number and total salary must be defined as the parameter create or replace procedure get_sal3 (dept_id number, sum_salar Y out number) iscursor salary_cursor is select salaryfrom employees where department_id = dept_id; beginsum_salary: = 0; for c in salary_cursor loop sum_salary: = sum_salary + c. salary; end loop; dbms_output.put_line (sum_salary); end; select get_sal3 (60,) -- perform a salary increase operation on employees in a given department (? -95) during the period, raise the salary by 5% -- (3%) raise the salary by 1%, (98, now) raise the salary by. The following result is returned: -- How much extra the company pays each month for this salary increase (define an out-type output parameter) create or replace procedure get_money (dept_id number, temp_sal out number) isv_ I number (): = 0; cursor salary_cursor is select employee_id, salary, hire_datefrom employees where department_id = dept_id; begintemp_sal: = 0; for c in salary_cursor loop if to_char (c. hire_date, 'yyyy') <'000000' then v_ I: = 1995; elsif to_char (c. hire_date, 'yyyy') <'000000' then v_ I: = 1998; else v_ I: = 0.03; end if; -- update salary update employees set salary = salary * (1 + v_ I) where employee_id = c. employee_id; -- calculate the cost temp_sal: = temp_sal + c. salary * v_ I; end loop; dbms_output.put_line (temp_sal); end; 4: Call the stored procedure: declare v_temp number (10): = 0; beginget_money (80, v_temp); end;