oracle storage functions and stored procedures

Posted by mdemetri2 on Sun, 12 May 2019 06:12:47 +0200

stored procedure
Concept: Stored Procedure s are large database system In order to accomplish specific functions, a set of SQL statements is stored in the database. After the first compilation, the first call does not need to be compiled again. The user executes it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters). Stored procedure is an important object in database.
sql statement:
Stored Procedures in sql and related introduction:

CREATE PROCEDURE [Owner.] Stored procedure name [; Program number]

[(Parameter # 1,...) Parameter #1024)]

[WITH

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

]

[FOR REPLICATION]

AS program row

The stored procedure name cannot exceed 128 words. Maximum 1024 parameters per stored procedure

(Version 7.0 or above of SQL Server), the use of parameters is as follows:

@ Parameter Name Data Type [VARYING] [= Internal Fixed Value] [OUTPUT]

Each parameter name should be preceded by a "@" symbol. The parameters of each stored procedure are only used internally by the program. Besides IMAGE, other data types supported by SQL Server can be used.

[Internal setting] is equivalent to setting a default value for a field when we create a database, and here is the default value for this parameter. [OUTPUT] is used to specify that the parameter has both input and output values. That is to say, when calling this stored procedure, if the specified parameter value is the parameter we need to input and output in the result, it must be OUTPUT. If it is only used as an output parameter, it can be used as CURSOR. When using this parameter, VARYING and OUTP must be specified. UT two statements.
Example:
```
create or replace procedure add_sal(dept_id number,temp_num out number)
is
       cursor sal_cursor is select employee_id,salary,hire_date from employees where department_id = dept_id;
       v_1 number(4,2) := 0;       
begin
       temp_num := 0;
       for c in sal_cursor loop
           if to_char(c.hire_date,'yyyy') < '1995' then v_1 := 0.05;
           elsif to_char(c.hire_date,'yyyy') < '1995' then v_1 := 0.03;
           else v_1 := 0.01;
           end if;
           
           update employees set salary = salary*(1+v_1) where employee_id = c.employee_id;
           temp_num := temp_num + c.salary*v_1;
       end loop;
       dbms_output.put_line(temp_num);
end;
//Call method:
declare
  v_temp number(10) := 0;
begin
  add_sal(80,v_temp);
end;
```
Storage function:
Concept: Stored procedures with return values.
Example: helloworld

create or replace function hello_world(v_log varchar2)
return varchar2
is
begin
return 'helloworld '||v_log;
end;

//Call:
declare
v_hell varchar2 := 'nihao';
begin
hello_world(v_hell);
end;

Example 2:

create or replace function get_sal(dept_id number,total out number)
return number
is
       v_sumsal number(10) := 0;
       cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
       total := 0;
       for c in salary_cursor loop
           v_sumsal := v_sumsal + c.salary;
           total := total + 1;
       end loop;
       return v_sumsal;
end;

Call:
declare
v_dept number := 80;
v_num number := 0;
begin

get_sal(v_dept,v_num);

end; Example 3:

create or replace function get_sysDate
return date
is 
v_date date;
begin
v_date := sysdate;
return v_date;
end;

Topics: Stored Procedure SQL Database

Programmer Think

oracle storage functions and stored procedures

Hot Topics