Oracle PL/SQL (procedure Language/sql) programming functions + procedures + Packages

Anonymous PL/SQL block review

DECLARE (optional) defines the object to be used in a PL/SQL block BEGIN (MUST) EXECUTE statement EXCEPTION (optional) error-handling statement END; Must Anonymous blocks (starting with declare or begin) are compiled each time they are used. anonymous blocks are not stored in the database, and they cannot be called from other PL/SQL blocks.

procedures, functions, packages, and triggers: All with a name block. ( please pay attention to the comparison with anonymous blocks ) procedures, functions, packages, and triggers: Can be stored in the database, and can be run when needed. procedures, functions, packages, and triggers: can be called from other PL/SQL blocks. subroutines: procedures, Functions

A subroutine is a named PL/SQL block that can accept parameters and be called by the environment. Two types of subroutines in PL/sql: procedures and Functions

The sub-Program Description (header) determines: The type of a PL/SQL subroutine is a procedure or a function. The name parameter list of the subroutine when the subroutine is a function it must have a return value (using the return clause) the keyword is or as is required. subroutine body: is a PL/SQL block that has a declaration, execution, and exception handling section. declaration part is between is| Between as and begin. In an anonymous block, you must use the DECLARE keyword to indicate the part of the Declaration, and there is no declare keyword in the subroutine. The execution part is between the Begin and end keywords, which must exist. The exception handling section is an optional part between exception and end

CREATE PROCEDURE (syntax)

example  Create stored procedure, output system date and time create OR REPLACE PROCEDURE display_time is BEGIN     Dbms_output.put_line (Systimestamp); END Display_time; way one: Use Sqlplus command Execute (abbreviated EXEC) to invoke     EXECUTE Display_time; way two: call     calls Display_ Time (); way three: Call     BEGIN         Display_time;     END; parameters and Modes

formal parameters (formal parameters) : The arguments declared in the procedure are formal parameters (P_firstname, P_lastname, P_major).

create OR REPLACE PROCEDURE Insert _emp  [ formal parameters] (no  emp.empno%type,             name  Emp.ename%type, job  emp.job%type,                 mgr  Emp.mgr%type, HireDate Emp.hiredate%type,     salary Emp.sal%type, comm Emp.comm%type,        Deptno Emp.deptno%type) is the BEGIN INSERT into EMP VALUES (NO,NAME,JOB,MGR,HIREDATE,SALARY,COMM,DEPTNO); END; Call the stored procedure above [anonymous block to invoke the stored procedure (the stored procedure is with a name block. )]

DECLARE v_no emp.empno%type:=10000, v_name emp.ename%type: = ' Jones ', v_job emp.job%type: = ' salesman ', V_mgr Emp.mgr%T YPE: =7369, v_hiredate emp.hiredate%type: =sysdate, v_salary emp.sal%type: = +, V_comm emp.comm%type: =NULL, V_deptno em P.deptno%type: =10 BEGIN insert_emp (v_no, V_name, V_job, V_mgr, V_hiredate, V_salary, V_comm, V_deptno); END;

parameters and Patterns the process can pass data through parameters and the calling environment. There are three delivery modes-in, out, and in out. If no pattern is specified for the formal parameter, the default mode in is used.

constraints on formal parameters: In a procedure declaration, it is illegal to limit the length of the char and VARCHAR2 parameters and to limit the precision and/or scale range of the number parameter. CREATE OR REPLACE PROCEDURE parameterlength ( p_parameter1 in Out VARCHAR2 (Ten), P_parameter2 in Out Numbe   R (3,2)) as BEGIN p_parameter1: = ' ABCDEFGHIJKLM '; P_parameter2: = 12.3; END parameterlength; /Process Declaration Illegal

Parameters and patterns--default values for parameters

create OR REPLACE PROCEDURE add_dept   (P_name in Dept.dname%type DEFAULT ' Unknown ',    p_loc in dept.loc%type  DEFAULT ' NEW YORK ') is begin    INSERT into Dept (Deptno,dname, loc)    VALUES (Dept_seq. Nextval, P_name, P_loc); end add_dept; execute: (with anonymous blocks to execute a named block)

Sql>begin add_dept; add_dept (' TRAINING '); add_dept (p_loc = ' BOSTON ', p_name = ' education '); add_dept (p_loc = ' CHICAGO ');          END; Sql>select * from dept; Delete process drop PROCEDURE raise_salary;

create function

create [OR REPLACE] FUNCTION function_name Span style= "color: #0000ff;" >[(argument1 [in | Out | In out] data_type, argument2 [in | Out | In out] data_type,...)] return data_type is |as    [Declaration_section;] begin    executable_section;    RETURN expression; [exception    exception_handlers; return expression;] end [function_name];

or The Replace keyword indicates that if a procedure exists, it is first deleted and then created with a new version. parameter, mode, and datatype have the same meaning as the process, but avoid using out and in Out parameter mode. Return_datatype is the type of numeric value returned by the function and cannot be qualified for the size of the data type.

example: ..... 。。。。。 create OR REPLACE FUNCTION get_sal       (p_id in Emp.empno%type) Span style= "color: #0000ff;" >return number is       v_ Salary Emp.sal%type: = 0; begin      Select sal      into v_salary     from emp     WHERE empno = p_id;      RETURN v_salary; end get_sal;

Execute function Note: Because the function has a return value, the calling function is part of an expression and cannot be used as a separate statement as the calling procedure. method One: Use the variable to receive the return value VAR salary number; exec:salary:=get_sal (7369); PRINT salary; Mode two: Call function directly in SQL statement SELECT get_sal (7369) from DUAL; way three: Use Dbms_output call function SET serveroutput on EXEC Dbms_output.put_line (' Wages are: ' | | get_sal (7369));
Delete functions drop function get_sal;

Create a Package

A package is a PL/SQL structure that can store related objects together, and is a schema object for Oracle databases. The package has two separate parts-the specification and the body of the package, which are stored separately in the data dictionary. A package contains program objects that are procedures, functions, variables, constants, cursors, and exceptions.

packages have more advantages than independent processes and functions: Packages make organization application development more efficient. packages make authorization more efficient. Packages allow you to modify the object of a package without recompiling dependent objects. The package allows Oracle to read multiple package objects into memory at once. A package can contain global variables and cursors that can be used for all procedures and functions in a package. Packages allow overloading of procedures and functions.

Create a Package

set up a package to perform two steps: (1) Create the package specification with the Create Packages command. The program objects are described in the package specification, which are called public objects. Public objects can be referenced outside the package, or other objects in the package. (2) Create package body with the Create packages body, describe and define program objects in the package body: Define the common objects described in the package specification, define additional package objects (private objects), because private objects are described in the package body, it cannot be referenced outside the package. create [OR REPLACE] Package package_name is|as     public type and item declarations     subprogram Specifications end package_name; specification

or REPLACE: Deletes and re-creates the package specification if the package specification exists. Package_name: The name of the package public type and item declarations: Declare common variables, constants, cursors, exceptions, and data types. Subprogram Specifications: Declares the PL/SQL subroutine. The default initialization value for a variable declared in the package specification is null.

specification for creating packages
create OR REPLACE Package comm_package is      g_comm number: = 0; nitialized to 0      PROCEDURE reset_comm  (p_comm in number); Span style= "color: #800000;" >end Comm_package; G_comm is a global variable with an initialization value of 0.10. Reset_comm is a public process that defines its execution code in the package body. Create package body

CREATE OR REPLACE Package BODY Comm_package isFUNCTION Validate_comm (P_comm in number) RETURN BOOLEAN (Create function in package) is V_max_comm number;        BEGIN SELECT MAX (comm) into V_max_comm from EMP;        IF p_comm > V_max_comm then RETURN (FALSE);        ELSE RETURN (TRUE);    END IF; END Validate_comm;PROCEDURE Reset_comm (p_comm in number) is (Create stored procedure in package) BEGIN IF Validate_comm (p_comm) then g_comm:=p _comm;         --reset global variable ELSE raise_application_error ( -20210, ' Invalid Commission ');    END IF; END Reset_comm; END Comm_package; Execution

Create a package without packages create OR REPLACE package global_consts is mile_2_kilo CONSTANT number: = 1.6093; kilo_2_mile CONSTANT Number: = 0.6214; yard_2_meter CONSTANT Number: = 0.9144; meter_2_yard CONSTANT Number: = 1.0936; END global_consts; perform Sql>execute dbms_output. Put_Line (' Miles = ' | | 20*global_consts.mile_2_kilo| | ' km ')

Http://www.cnblogs.com/pacoson/p/3523467.html

Oracle PL/SQL (procedure Language/sql) programming function + Process + package (RPM)