Procedures and functions of oracle Database PL/SQL

Source: Internet
Author: User


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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.