Shulou(Shulou.com)06/01 Report--

This paper illustrates the use of oracle stored procedures, functions and triggers with examples. Share with you for your reference, the details are as follows:

Stored procedures and stored functions

Subroutines stored in the database for all user programs to call are called stored procedures and stored functions.

Create a stored procedure

Use the CREATE PROCEDURE command to create a stored procedure.

Syntax:

Create [or replace] procedure process name (parameter list) asPLSQL subroutine body;-- salary increase for designated employees create procedure addSal (empid in number) as psal emp.sal%type;begin select sal into psal from emp where empno=empid; update emp set sal = sal * 1.1 where empno=empid; dbms_output.put_line (empid | before salary increase | | psal | | after salary increase | | (psal * 1.1); end

Call stored procedure

-- method 1 beginaddSal (7369); end;-- method 2 exec addSal (7369)

Storage function

The function is a named stored program that takes parameters and returns a calculated value. The structure of a function is similar to that of a procedure, but there must be a return clause that returns the value of the function. The function states that the function name, the type of the resulting value, and the parameter type should be specified.

Create syntax:

CREATE [OR REPLACE] FUNCTION function name (parameter list) RETURN function value type ASPLSQL subroutine body;-- query the annual income of specified employees create function queryEmpSal (empid in number) return numberas psal emp.sal%type; pcomm emp.comm%type;begin select sal,comm into psal,pcomm from emp where empno=empid; return (psal*12) + nvl (pcomm,0); end

Function call

Declare psal number;begin psal:=queryEmpSal (7369); dbms_output.put_line (psal); end

Or

Begin dbms_output.put_line (queryEmpSal (7369); end

IN and OUT in procedures and functions

Generally speaking, the difference between a procedure and a function is that a function can have a return value, while a procedure does not.

However, both procedures and functions can specify one or more output parameters through out. We can use the out parameter to return multiple values in procedures and functions.

When do you use stored procedures or functions?

Principle: if there is only one return value, use a stored function, otherwise, use a stored procedure.

Create packages and package bodies

What is the bag and the package body?

A package is a combination of PL/SQL programming elements such as related procedures, functions, variables, constants, types, and cursors. The package has the characteristics of object-oriented design and is the encapsulation of these PL/SQL programming elements.

The package body is the concrete implementation of the package definition part.

The package consists of two parts: the package definition and the package body.

-- package definition create [or replace] package package name as [public data type definition] [public cursor declaration] [public variable, constant declaration] [public subroutine declaration] end package name;-- package body create [or replace] package body package name as [private data type definition] [private variable, constant declaration] [private subroutine declaration and definition] [public subroutine definition] beginPL/ subroutine body; end package name -- create mypackage package create or replace package mypackage as procedure total (num1 in number, num2 in number, num3 out number); end mypackage;--mypackage packet create or replace package body mypackage as-- calculates the total process of cumulative sum procedure total (num1 in number, num2 in number, num3 out number) as tmp number: = num1;begin if num2

Num2; tmp: = tmp + 1; num3: = num3 + tmp; end loop; end if;end total;end mypackage

(* Note: package definition and package body should be created separately)

Call package

Declare num1 number;begin mypackage.total (1,5, num1); dbms_output.put_line (num1); end

More readers who are interested in Oracle-related content can check out this site's special topic: "Summary of Oracle common functions", "summary of Oracle date and time operation skills" and "summary of php+Oracle database programming skills".

It is hoped that what is described in this article will be helpful to the programming of Oracle database.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.