Oracle practice -- PL/SQL basic functions

Basic PL/SQL Functions

/*

Function: You can have PL/SQL subprograms that are named, and must have returned values.

Keyword: function return

*/

-- Function 1

Create or replace function helloworldreturn varchar2 -- specifies the return type. The length as v_hello varchar2 (50) cannot be specified. begin v_hello: = 'helloworld! '; Return v_hello; -- returnend;

-- Function call method:

select helloworld from dual;select helloworld() from dual;

Declare v_re varchar2 (50); begin v_re: = helloworld (); -- Remember to receive the return value of the function. Oh, dbms_output.put_line (v_re); end;

-- Function 2

Create or replace function my_func (v_sal number) return varchar2 -- note that ';' as v_ SQL varchar2 (200); v_msg varchar2 (50); v_min number; v_max number; begin v_ SQL: = 'select max (sal), min (sal) from emp'; execute immediate v_ SQL into v_max, v_min; if v_sal> v_min and v_sal <v_max then v_msg: = 'salary is within the normal range! '; Else v_msg: = 'Abnormal'; -- if it is not written, the initialization in java is equivalent to ''end if; return v_msg; end;

-- CallFunction 2

Select my_func (1500) from dual; declare v_sal number: = '& Salary:'; v_result varchar2 (50); begin v_result: = my_func (v_sal); dbms_output.put_line (v_result ); end;