Create and call functions and stored Functions

Create and call functions and stored Functions
Function [in out parameter]

-- Function [in out parameter] create or replace function get_test (m_id in number, str1 out varchar2) -- The parameter does not need type length return varchar2 is str2 varchar2 (10 ); -- The variable must be of Type length begin select t. ename, t. job into str1, str2 from scott. emp t where t. empno = m_id; return str2; end get_test; -- call the function [positional notation] declare s1 varchar2 (10); s2 varchar2 (10); s3 number (30) default 7521; -- default value: begin s3: = 7566; s2: = get_test (s3, s1); -- Parameter order: dbms_output.put_line (s1 | ''| s2); end; -- call the function [name representation] declare s1 varchar2 (10); s2 varchar2 (10); begin s2: = get_test (str1 => s1, m_id => 7521 ); -- name ing. The order is not important. dbms_output.put_line (s1 | ''| s2); end;

Stored Procedure 1

-- Stored procedure 1 create or replace procedure get_test1 (m_id in number, str1 out varchar2) -- The parameter does not require the type length isbegin select t. ename into str1 from scott. emp t where t. empno = m_id; end get_test1; -- call declare s1 varchar2 (100); begin get_test1 (7521, s1); dbms_output.put_line (s1); end;

Stored Procedure 2

Create or replace procedure get_test2 (m_id in number) is str1 varchar2 (100); -- variable after is defines begin select t. ename into str1 from scott. emp t where t. empno = m_id; dbms_output.put_line (str1); end; -- EXECUTE begin get_test2 (7521); end; -- EXECUTE get_test2 (7521) in the Command window );