Procedures and functions of oracle Database PL/SQL

Oracle Database PL/SQL process and function 4) process www.2cto.com 1. basic concept: oracle allows you to create and store compiled PL/SQL programs in the database, including processes, functions, packages, and triggers. We can save the business logic, business rules, and other written procedures or functions to the database and call them by name for better sharing and use. There are three types of parameters in the process: in/out/in out; 2. CREATE process 1) syntax CREATE [or replace] PROCEDURE <process name> (<parameter 1>, [method 1] <Data Type 1>, <parameter 2>, [method 2] <Data Type 2> ,...) IS/ASPL/SQL process body; 2) example CREATE OR REPLACE PROCEDURE count_num (in_sex in TEACHERS. SEX % TYPE) AS out_num NUMBER; begin if in_sex = 'M' then select count (SEX) INTO out_num from teachers where sex = 'M'; dbms_output.put_line ('number of Male Teachers: '| out_num); else select count (SEX) INTO out_num from teachers where sex = 'F'; dbms_output.put_line ('number of Female Teachers:' | out_num); end if; END count_num; 3. EXECUTE count_num ('M'); EXECUTE count_num ('F'); 4. drop procedure count_num;
5) function 1. the basic concept is used to calculate and return a value. An expression is required for calling. 2. CREATE [or replace] FUNCTION <FUNCTION Name> (<parameter 1>, [method 1] <Data Type 1>, <parameter 2>, [method 2] <Data Type 2> ,...) RETURN <expression> IS/ASPL/SQL process body; -- a RETURN clause CREATE OR REPLACE FUNCTION count_num (in_sex in TEACHERS. SEX % TYPE) AS return NUMBER; begin if in_sex = 'M' then select count (SEX) INTO out_num from teachers where sex = 'M'; else select count (SEX) INTO out_num from teachers where sex = 'F'; end if; RETURN (out_num); END count_num;
3. call the DECLARE m_num NUMBER; f_num NUMBER; BEGIN m_num: = count_num ('M'); f_num: = count_num ('F'); END; 4. delete the function drop function count_num;