Oracle subroutines (stored procedures, functions)

1. subprograms are named PL/SQL statement blocks, including stored procedures and functions, and their advantages (modular, reusable, and maintainability security ).

2. Stored Procedure:

create [or replace] procedure_name(argument1 [mode1] datatype1,argument2 [mode2] datatype2..)is[as]PL/SQL Block;

3. Call the stored procedure:.
SQL> set serveroutput onsql> exec procedure_name; (parentheses are required when parameters exist)
B.
SQL>set serveroutput onSQL>call procedure_name();
C.
Declarebegin procedure_name; (parentheses are required when a parameter exists.) end;
Example: -- contains the in parameter in the process
Create or replace procedure add_employee (ENO number, ename varchar2, Sal number, job varchar2 default 'cler', DNO number) is e_integrity exception; Pragma prediction_init (e_integrity,-2291 ); begin insert into scott. EMP (empno, ename, Sal, job, deptno) values (Eno, ename, Sal, job, DNO); Exception when dup_val_on_index then raise_application_error (-20000, 'employee ID cannot be repeated '); When e_integrity then raise_application_error (-20001, 'department ID does not exist'); end; declarebegin add_employee (1112, 'cler', 2000, 'manager ', 11); end;
-- The process has an out Parameter
Create or replace procedure query_employee (ENO number, sname out varchar2, salary out number) isbegin select ename, Sal into sname, salary from Scott. EMP where empno = Eno; Exception when no_data_found then raise_application_error (-20000, 'this employee does not exist'); end; declare dname Scott. EMP. ename % type; salary Scott. EMP. sal % type; begin query_employee (7788, dname, salary); dbms_output.put_line (dname | ''| salary); end; SQL> V Ar name varchar2 (10) SQL> var salary numbersql> exec query_employee (7788,: name,: salary); (: name indicates the global session, Exec: Name: = 'A ').
-- The Stored Procedure contains the in out Parameter
create or replace procedure computer(num1 in out number,num2 in out number)       is        v1 number;       v2 number;begin       v1:=num1/num2;       v2:=mod(num1,num2);       num1:=v1;       num2:=v2;end;       declare       num1 number;       num2 number;begin       num1:=4;       num2:=2;       computer(num1,num2);       dbms_output.put_line(num1);       dbms_output.put_line(num2);end;
4. input parameters (in) cannot be assigned values during the process, and output parameters can be assigned values (values can be assigned outside, but they are not received in the process ).
5. parameter transfer variables and data: Location transfer names are transferred in combination (in and out parameters, dname => 'sales' can be supported '). -- Procedure parameter transfer
Create or replace procedure add_dept (DNO number, dname varchar2 default null, Loc varchar2 default null) is begin insert into scott. dept values (DNO, dname, Loc); Exception when dup_val_on_index then raise_application_error ('-20000', 'department number cannot be repeated '); end; declare begin -- add_dept (50, 'sales', 'New York '); -- add_dept (dname => 'sales', DNO => 70); add_dept (76, dname => 'sales ', loc => 'New York '); end;
Note: When a parameter data type is specified, the length (number, Scott. Dept. deptno % Type) of the parameter type cannot be specified ). 6. Deletion process: Drop procedure procedure_name. 7. functions:
Create [or replace] function function_name (arugment1 [mode1] datatype1, argument2 [mode2] datatype2..) return datatype (required) is | aspl/SQL block;
-- Function without Parameters
create or replace function get_userreturn varchar2is       v_user varchar2(100);begin       select username into v_user from user_users;       return v_user;end; 
-- Call
declare       v1 varchar2(100);begin       --v1:=get_user;       --v1:=get_user();       dbms_output.put_line(get_user());end;select get_user from dual;
-- Function in Parameters
Create or replace function get_sal (name1 in varchar2) return numberis v_sal Scott. EMP. sal % type; begin select Sal into v_sal from Scott. EMP where upper (ename) = upper (name1); Return v_sal; Exception when no_data_found then raise_application_error (-20000, 'this employee does not exist'); end; select get_sal ('Scott ') from dual;
-- Function out parameter (multiple values will be returned)
Create or replace function get_info (name1 varchar2, title out varchar2) return varchar2is deptname Scott. dept. dname % type; begin select. job, B. dname into title, deptname from Scott. emp a, Scott. dept B where. deptno = B. deptno and upper (. ename) = upper (name1); Return deptname; Exception when no_data_found then raise_application_error (-20000, 'this employee does not exist'); end;
-- Function in out Parameter
Create or replace function result1 (num1 number, num2 in out number) return number as v_result number (6); v_remainder number; begin v_result: = num1/num2; v_remainder: = Mod (num1, num2); num2: = v_remainder; return v_result; Exception when zero_divide then raise_application_error (-20000, 'Do not divide 0'); end; declare num2 number; begin num2: = 2; dbms_output.put_line (result1 (100, num2) | ''| num2); end; select * From user_source where name = 'result1'; (View Source Code) select * From user_source where name = 'add _ dept ';
Note: Both procedures and functions are objects. They cannot be the same name when defined. function calls are restricted and can only be called as part of an expression. A function called in an SQL statement can only contain input parameters, but not other functions. It can only use the standard data types supported by SQL, rather than the special data types in PL/SQL (Boolean, table, record, etc ). Select * From user_errors; (locate the cause and location of the error or show errors procedure procedure_name ).