You can use exec or call to call a stored procedure.
Use var when defining variables, for example, var username varchar2 (20 );
When calling a variable, double quotation marks must be added before the variable name, for example, exec: username: = 'user1 ';
When calling the stored procedure, double quotation marks must also be added before the variable name, such as exec compute (: n1,: n2 );
You can use show error to output the current error.
View the source code of the stored procedure:
SQL code
Select text from user_source where name = 'compute ';
Create a stored procedure with input and output parameters:
When creating a stored procedure, you can define three parameters: in (input parameter, default), out (output parameter), and in out (input and output parameter ).
SQL code
Create or replace procedure compute (num1 in out number, num2 in out number) is
N1 number (10, 2 );
N2 number (10, 2 );
Begin
N1: = num1/num2;
N2: = mod (num1, num2 );
-- Assign values to the variables to be returned
Num1: = n1;
Num2: = n2;
End;
/
Function Development:
The length of a function parameter cannot be specified. The Return clause is used to specify the Data Type of the function Return value.
IS or as is used to start a PL/SQL block (replacing declare)
A Return clause must be included in the function header, and at least one Return clause must be included in the function body.
When creating a function, you can specify the in (input parameter, default), out (output parameter), and in out (input and output parameter) parameters.
SQL code
-- Create a function with Input and Output Parameters
Create or replace function get_result (num1 number, num2 in out number)
Return number is
N_result number (6 );
N_remainder number;
Begin
N_result: = num1/num2;
N_remainder: = mod (num1, num2 );
Num2: = n_remainder;
Return n_result;
End;
/
Development Kit:
Creating a package specification is equivalent to defining interfaces in Java. the variables, functions, and subprograms defined here are all public.
SQL code
Create or replace package emp_package is
-- Define public variables
N_temp number (888.888): =;
-- Define the stored procedure for adding employees
Procedure add_emp (empno number, name varchar2, job varchar2, manager varchar2, hiredate varchar2, salary number, commision varchar2, deptno number );
-- Define the Stored Procedure for employee dismissal
Procedure fire_emp (n_empno number );
-- Defines the function for obtaining the employee salary of a specified employee number.
Function get_salary (n_empno number) return number;
End emp_package;
/
-- Create a package, which is equivalent to the interface implementation class in Java
Create or replace package body emp_package is
-- Define a private function to verify the existence of employee numbers
Function validate_empno (n_empno number) return boolean is
N_temp employee. empno % type;
Begin
Select empno into n_temp from employee where empno = n_empno;
Return true;
Exception
When no_data_found then return false;
When others then return false;
End;
-- Implement the storage process of adding employees
Procedure add_emp (empno number, name varchar2, job varchar2, manager varchar2, hiredate varchar2,
Salary number, commision varchar2, deptno number) is
Begin
If validate_empno (empno) then
Raise_application_error (-20001, 'Number: '| empno |' the employee already exists! ');
Else
Insert into employee values (empno, name, job, manager, hiredate, salary, commision, deptno );
Commit;
End if;
End;
-- Store employee dismissal
Procedure fire_emp (n_empno number) is
Begin
If validate_empno (n_empno) then
Delete from employee where empno = n_empno;
Commit;
Else
Raise_application_error (-20003, 'Number: '| n_empno |' the employee does not exist! ');
End if;
End;
-- Implement the function of obtaining the employee salary of the specified employee number
Function get_salary (n_empno number) return number is
N_salary employee. salary % type;
Begin
If validate_empno (n_empno) then
Select salary into n_salary from employee where empno = n_empno;
Return n_salary;
Else
Raise_application_error (-20004, 'Number: '| n_empno |' the employee does not exist! ');
End if;
End;
End emp_package;
/
Test Block:
SQL code
Declare
N_1 number (10, 2 );
N_2 number (10, 2 );
Begin
-- Test the Stored Procedure compute
N_1: = 20;
N_2: = 8;
-- Location Transfer
-- Compute (n_1, n_2 );
-- Name transfer
Compute (num1 => n_1, num2 => n_2 );
Dbms_output.put_line ('n' _ 1 = '| n_1 | 'n_2 =' | n_2 );
-- Test function get_result
N_1: = 20;
N_2: = 8;
N_2: = get_result (num1 => 100, num2 => n_1 );
Dbms_output.put_line ('n' _ 1 = '| n_1 | 'n_2 =' | n_2 );
-- Test package
Dbms_output.put_line ('Public variables defined in the package: '| emp_package.n_temp );
Emp_package.add_emp (22, 'Li Ming ', 'personnel assistant', 0, '2017-08-15 ', 2006, 'hr', 1 );
Emp_package.fire_emp (2 );
Dbms_output.put_line ('employee's salary with ID 1 is: '| emp_package.get_salary (1 ));
End;
/
Stored Procedure VS function:
Similarities between storage and processes:
All have input, output, and input and output parameters.
Differences:
The function must have a return value.
Function cannot modify data
Purpose:
Stored Procedures: Mainly used for data modification and Business Processing
Function: can only be used for data computing.
Heavy-load Subroutine:
The two subprograms defined in a package have the same name and different parameters.
During the call, Oracle automatically calls the corresponding subroutine Based on the parameter type.
Restrictions:
If the two subprograms have different parameter names
The parameter type is the same, but the return type is different, not heavy load
The overloaded parameter must be of the basic type.
Author: "Soy Milk"