[Dan JIU Jian] Oracle knowledge point sorting (6) Procedure, function, Sequence, and oracleprocedure of common database objects

[Dan JIU Jian] Oracle knowledge point sorting (6) Procedure, function, Sequence, and oracleprocedure of common database objects
Link navigation for this series:

[Lone sword] Oracle knowledge point sorting (1) Table space and users

[Gu JIU Jian] Oracle knowledge point sorting (2) database connection

[Gu JIU Jian] Oracle knowledge point sorting (3) Import and Export

[Dan JIU Jian] Oracle knowledge point sorting (4) SQL statement DML and DDL

[Dan JIU Jian] Oracle knowledge point sorting (5) Table and View of common database objects

[Lone sword] Oracle knowledge point sorting (6) Procedure, function, and Sequence of common database objects

[Dan JIU Jian] Oracle knowledge point sorting (7) database Common Object Cursor

[Lone sword] Oracle knowledge point sorting (8) Common exceptions

[Lone sword] Oracle knowledge point sorting (9) package of common database objects

[Gu JIU Jian] Oracle knowledge point sorting (10) % type and % rowtype and common functions

5.3 procedure operations 5.3.1. Create a stored procedure

1 create or replace procedure proc_GetPerson (argName in varchar2, argMsg out varchar2, argTable out pkg. RefCur)
-- Parameter, no need to define the length of 2 as 3 vName varchar2 (64); -- Define the variable, to set the length of 4 begin 5 vName: = argName; -- assign a value, each statement should end 6 open pke with a semicolon. refCur is select * from person where name like vName | '%'; -- Record the query result to the returned cursor 7 -- of course, other complex judgments can be made, conversion and other operations 8 argMsg: = 'normal'; 9 commit; 10 11 Exception12 when others then argMsg = 'exception'; 13 rollback; -- if the previous execution is a transactional statement, it is necessary to ensure data security 14 end proc_GetPerson;

5.3.2 delete a stored procedure

drop procedure pro_GetPerson;

5.4 function operations 5.4.1. Create a function

• With in Parameters

1 create or replace function fun_GetPersonName (argName in varchar2, argGender in varchar2) return varchar22 as3 Result varchar2 (64 ); -- Define the variable 4 begin5 select name into Result from person where name = argName and gender = argGender and rownum = 1; 6 return Result; -- return value 8 end fun_GetPersonName;

• Out Parameter

1 create or replace function fun_GetPersonName (argName in varchar2, argGender out varchar2) return varchar22 as3 Result varchar2 (64); -- Define variable 4 begin5 select name, gender into Result, argGender from person where name = argName and rownum = 1; 6 return Result; -- return value 7 end fun_GetPersonName;

5.4.2 Delete A Function

drop function fun_GetPersonName;

5.5 sequence operations

In Oracle, sequence is the sequence number, which is automatically increased each time it is obtained; sequence is irrelevant to the table.
First, you must have the create sequence or create any sequence permission.

5.5.1 create Sequence

1 create sequence seqPersonID2 increment by 1 -- add 13 start with 1 each time -- count the sequence from 1 to 4 nomaxvalue -- do not set the maximum value of 5 nocycle -- consistent accumulation, 6 cache 10 without loop; set cache sequence. If the system goes down, or otherwise, the sequence is not continuous. 7 -- nocache; -- is not the most cache.

5.5.2. Obtain the Sequence value.

CurrVal: returns the current sequence value.
NextVal: returns the next value of sequence.

1 select seqPersonID. currVal from dual; -- Obtain the current value of the sequence, which must be used after NextVal is called; otherwise, 2 select seqPersonID is returned. nextVal from dual; -- Obtain the next value of the sequence. If it is used for the first time, the initial value set during creation is returned.

Sequence can be used in SQL statements:

For example, in the insert statement

Insert into person (id, name) values (seqPersonID. Nextval, 'sequence insert test ');

5.5.3 modify the sequence

You can modify all sequence parameters other than start. to modify the value of start, you must drop the parameter and recreate it.

alter sequence seqPersonID maxValue 99999999;

5.5.4. Delete Sequence

drop sequence seqPersonID;