Oracle Notes (v)

1. Oracle's Custom functions

2. Oracle triggers

3. Oracle's Stored procedures

Knowledge Point one: Custom functions

Syntax: Create [or replace] function name (parameter)

Return value type-must be

Is

--Declaration of variables

Begin

--EXECUTE statement

--return return variable

--exception exception Block (return exception number)

End

Java:

int add (int no1,int NO2)

{

int sum = NO1+NO2;

return sum;

}

Oracle

-- Write a simple custom function Createorreplacefunction Fun_add (No1 Number,no2 number) Returnnumber Is V_sum number; Begin V_sum: = No1+no2; return v_sum; exception Whenothersthen return-1; End

Calling functions

Way One:

Select Fun_add (50,50) from dual;

Way two:

Declare V_no1 number (2); V_no2 number (2); V_sum number (3); Begin V_no1: = 50; V_no2: = 50; V_sum: = Fun_add (V_NO1,V_NO2); If V_sum =-1then Dbms_output.put_line (' input data is wrong! ‘); Else Dbms_output.put_line (v_sum); endif exception Whenothersthen Dbms_output.put_line (' input data is wrong! ‘); End

Knowledge point two: Trigger Trigger (DML trigger)

Grammar:

Create[or Replace] Trigger Tri_ name

After|before

Insert|delete|update

On table name

[For each row]-row-level trigger

Begin

--EXECUTE statement block

End

Case: When you delete the EMP table data, let the Emp_bak table automatically back up

-- Delete Emp_bak table data, let emp_bak01 Table Automatic Backup Createorreplacetrigger tri_delemp AfterDelete On Emp_bak Foreachrow Begin Insertinto emp_bak01 (Empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (: Old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal, : Old.comm,:old.deptno); End

Case: Create UserInfo (USERID,UNAME,UPW), UserID automatic +1 implementation

Step one: Create a table CreateTable UserInfo ( UserID Number (4) primary key, Uname varchar2 (Ten) Notnullunique, UPW Varchar2 (Ten) Notnull )

Step two: Create a sequence Create sequence Seq_userid;

Step three: Create a trigger Createorreplacetrigger Tri_userid BeforeInsert On UserInfo Foreachrow Begin Select Seq_userid.nextval Into:new.userid from dual; End

Step four: Start the trigger Insert into UserInfo (UNAME,UPW) VALUES (' Lily ', ' 123456 ');

Case: Implement change the name of the UserInfo table This field, back up the original data, back up to the Userinfo_bak table

Knowledge Point Three: Stored procedure procedure

Grammar:

Create or replace procedure Pro_ stored procedure name (input parameter in, output parameter out)

As

--Declaration of variables

Begin

--Execution process

--exception Exception Handling Process

End

Case: Define the stored procedure, complete the UserInfo table Insert function (complete the account registration function)

--Complete the insertion function of the UserInfo table Createorreplaceprocedure Pro_userreg ( V_uname in USERINFO.UNAME%TYPE,V_UPW in Userinfo.upw%type) As Begin Insertinto userinfo (UNAME,UPW) values (V_UNAME,V_UPW); End

Executing stored procedures Method one: Command line execution Execute Pro_userreg (' Jack ', ' 123456 ');

Method Two: Plsql Execution Begin Pro_userreg (' Jack ', ' 123456 '); End

Method Three: Application java,c#,php call stored procedure

Case two: Define the stored procedure to complete the login

-- define a stored procedure to complete the login Createorreplaceprocedure Pro_userlog ( V_uname USERINFO.UNAME%TYPE,V_UPW Userinfo.upw%type, V_result outnumber) As Begin SelectCount (*) into V_result from UserInfo where uname = v_uname and UPW = V_UPW; End

Declare V_uname Userinfo.uname%type; V_UPW Userinfo.upw%type; V_result number (1); Begin V_uname: = ' Jack '; V_UPW: = ' 123456 '; Pro_userlog (V_uname,v_upw,v_result); If V_result =1then Dbms_output.put_line (' Landing success! ‘); Else Dbms_output.put_line (' Login failed! ‘); endif End

2017-10-31 18:37:14

Oracle Notes (v)