PL/SQL (5): Stored Procedure

I. Stored Procedure

Purpose
It is used to execute an operation, named PL/SQL block, and can receive passed parameters and be called.
Brief Introduction
Named PL/SQL Block
Acceptable parameters
Can be called repeatedly
Used to perform an operation
Stored in the database

Ii. Process Parameters

There are three types
1. in Mode
Default Mode
Constants or variables can be passed.
Constants cannot be assigned within the process.
Default values are allowed, such as p_id in number default 7369.
2. out Mode
Variables must be used when calling, but the variable values are ignored.
Assign values to variables after the call is completed
3. in out mode
Input the parameter value and return the changed value.
Note:
The size of the Data Type in the parameter cannot be limited.
The out and in out parameters cannot have default values.

 

Iv. Sample Code

 

1. in Mode
Parameter mode Description: in, input mode. The default value is in. It indicates the received value. No value can be assigned in the Process Code.

-- In mode create or replace procedure param_test_in (num int, num2 in int) asbegin -- num: = 12; -- in mode parameter. After receiving the value, it becomes a constant and cannot be assigned a value. dbms_output.put_line (num + num2); end; declare n1 int; n2 int; begin param_test_in (); -- transmits a constant value to the in mode parameter. n1: = 200; n2: = 300; param_test_in (n1, n2); -- pass the variable to the in mode parameter. end;

2. out Mode
Parameter mode Description: out, output mode. It indicates the output value. In the process code, you can assign a value again. variables must be used for calling.

-- In + out mode/* writes a stored procedure, which contains two parameters: p_sal (salary parameter) and p_mes (outgoing parameter) when the incoming salary is greater than the average salary, the 'salary too high 'information is passed to the Information Parameter; otherwise, the 'salary normal' is passed '. */Create or replace procedure sal_proc (p_sal in emp. sal % type, p_mes out varchar) is v_sal emp. sal % type; begin select avg (sal) into v_sal from emp; if p_sal> v_sal then p_mes: = 'salary too high '; else p_mes: = 'salary normal '; end if; end;

3. in out mode
1) variables must be used for calling.
2) The process can obtain the value of the input variable,
3) variable values can be re-assigned during the process.
4) after the process is completed, the value will also be output to the variable during the call.

-In + out + in out mode create or replace procedure raise_sal (p_id IN emp. empno % type, p_name OUT varchar2, p_sal in out number) isbegin update emp SET sal = sal + p_sal WHERE empno = p_id; SELECT sal, ename into p_sal, p_name FROM emp WHERE empno = p_id; END raise_sal; DECLARE v_sal emp. sal % type: = 500; v_name emp. ename % type; BEGIN raise_sal (7369, v_name, v_sal); dbms_output.put_line ('employee: '| v_name |' current salary '| v_sal); END;

4. Other common statements

-- Check for errors in the command line. show errors PROCEDURE sal_proc; -- drop procedure sal_proc; -- grant permissions -- 1) grant select on emp to system directly; -- table name emp, User Name system -- 2) indirectly grant execute on sal_proc to system; -- stored procedure sal_proc, User Name system -- view the object name, object type, and status SELECT object_name, object_type, statusFROM user_objectsWHERE object_type in ('Procedure ', 'function') order by object_name; -- view the source code of the [process] type, capital SELECT * FROM user_sourceWHERE type = 'Procedure ';