Oracle Series: (29) Stored procedures and storage functions

1. Stored procedure "procedure"

What is a stored procedure?

A piece of business-ready program, written in advance with Oracle syntax, is stored in an Oracle Server for remote access to Oracle clients (for example, Sqlplus) and program languages, similar to functions in Java.

Why use stored procedures?

(1) Plsql each execution of the overall run once, only results

(2) Plsql can not be encapsulated, long-term storage in the Oracle server

(3) Plsql cannot be called by other applications, for example: Java

What is the relationship between stored procedures and plsql?

Stored procedures are an aspect of plsql application, and Plsql is the basis of stored procedures.

That is, the stored procedure needs to use Plsql.

--------------------------------------------------------Stored Procedures

Grammar:

create [or Replace] procedure procedure name [(parameter list)] Asplsql program body;

Note: There is "begin...end;/" in the stored procedure, no declare

Create a non-parametric stored procedure Hello, no return value, syntax: Create or Replace procedure procedure name as Plsql program

Create or Replace procedure Helloasbegin dbms_output.put_line (' This is my first stored procedure '); end;/

Delete stored procedure Hello, Syntax: drop procedure Procedure Name

drop procedure Hello;

Call stored procedure method One, exec stored procedure name

exec Hello;

Call stored procedure mode two, plsql program

Begin Hello; end;/

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/87/22/wKioL1fVT4DQUYZJAABzyHYtUy4172.jpg "title=" 001. JPG "alt=" wkiol1fvt4dquyzjaabzyhytuy4172.jpg "/>

Call stored procedure method Three, Java program

One object in JDBC: callablestatement

Create a parameter stored procedure raisesalary (number), 10% salary for employee No. 7369, demo in usage, default in, case insensitive

--Define process Create or replace procedure Raisesalary (pempno number) asbegin update emp set sal=sal*1.2 where empno=pempno;end;/- -Call procedure exec raisesalary (7369);

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/87/24/wKiom1fVUOXQv5CLAAB9nNBe5-A878.jpg "title=" 002. JPG "alt=" wkiom1fvuoxqv5claab9nnbe5-a878.jpg "/>

Create a parameter stored procedure findempnameandsalandjob (number), query number No. 7788 employee's name, position, monthly salary, return multiple values, demonstrate the use of out

--Define process Create or replace procedure Findempnameandsalandjob (Pempno in Number,pename out Varchar2,pjob out varchar2,psal out Number) Asbegin Select Ename,job,sal to Pename,pjob,psal from EMP where empno=pempno;end;/--call procedure declare Pename EMP.E   Name%type;   Pjob Emp.job%type;  Psal emp.sal%type;begin findempnameandsalandjob (7369,pename,pjob,psal); Dbms_output.put_line (' No. No. 7369 employee's name is ' | | pename | | ', position is ' | | pjob | | ', monthly salary is ' | | PSAL); end;/

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/87/24/wKiom1fVWY7Aidi_AADctoOCqio886.jpg "title=" 003. JPG "alt=" wkiom1fvwy7aidi_aadctoocqio886.jpg "/>

Under what circumstances do I call a stored procedure with Plsql, using exec?

exec is appropriate for calling stored procedures with no return value

Plsql is suitable for calling stored procedures with a return value, regardless of how many

Write a function to calculate personal income tax with stored procedures

--  Defining stored Procedure Create or replace procedure get_rax (sal in number,rax out  number) As  -- sal represents revenue   -- bal  represents tax revenue receivable   bal number; Begin  bal := sal - 3500;  if bal <= 1500 then     rax := bal * 0.03 - 0;  elsif bal  <= 4500 then    rax := bal * 0.1 - 105;   elsif bal <=9000 then    rax := bal * 0.2  - 555;  elsif bal <=35000 then    rax :=  bal * 0.25 - 1005;  elsif bal <= 55000 then     rax := bal * 0.3 - 2755;  elsif bal <=80000  then   &Nbsp;rax := bal * 0.35 - 5505;  else     rax  := bal * 0.45 - 13505;  end if;end;/--  Calling stored procedure declare    --  need to pay tax    rax number;begin   get_rax (&sal,rax);    dbms_output.put_line (' You need to pay tax '  | |  rax);      end;/

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/87/22/wKioL1fVX6mBSFdJAAC0QtxHYjs076.jpg "title=" 004. JPG "alt=" wkiol1fvx6mbsfdjaac0qtxhyjs076.jpg "/>

2. Storage function

Create an getname stored function, with return value, Syntax: Create or Replace function function name return returned type as Plsql program segment

Create or Replace function get_name return varchar2as begin return ' Hello Hello '; end;/

Delete storage function GetName, Syntax: Drop function function name

Drop function get_name;

Call storage function mode one, plsql program

declare name VARCHAR2; begin Name: = Get_name (); Dbms_output.put_line (name); end;/

Call storage function mode two, Java program

Create a parameter stored function findempincome (number), query the annual income of employee number No. 7369, demonstrate in usage, default in

--Define the storage function create or Replace function Findempincome (pempno in number) return Numberas income number;  Begin select SAL*12+NVL (comm,0) into the income from EMP where empno=pempno;   Return income;end;/--calls the stored function declare income number;begin income: = Findempincome (&income); Dbms_output.put_line (' The employee's annual income is ' | | income '); end;/

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/87/25/wKiom1fVY__jL_zbAACZPDIPKxs205.jpg "title=" 005. JPG "alt=" wkiom1fvy__jl_zbaaczpdipkxs205.jpg "/>

Create a parameter stored function findempnameandjobandsal (number), query number No. 7788 employee's name (return), Position (out), monthly salary (out), return multiple values

--Define storage function Create or Replace function findempnameandjobandsal (pempno in Number,pjob out varchar2, psal out number) return VA       Rchar2as pename Emp.ename%type;begin Select Ename,job,sal into Pename,pjob,psal from EMP where empno=pempno;       return pename;   end;/--Call storage function declare Pename emp.ename%type;   Pjob Emp.job%type;   Psal emp.sal%type;begin Pename: = Findempnameandjobandsal (&empno,pjob,psal); Dbms_output.put_line (' No. No. 7369 employee's name is ' | | pename | | ', position is ' | | pjob | | ', monthly salary is ' | | PSAL); end;/

3. Suitable scenarios for stored procedures and storage functions

Note: The fit is not forced to use, just priority

Under what circumstances is the "fit to use" stored procedure? What is the "fit to use" storage function?

"Suitable for use" stored procedure: when no return value or multiple return values are used, the procedure

"Fit to use" storage function: When there is only one return value, it is appropriate to use a function

What is the "fit to use" procedure function and what is "fit for use" SQL?

"Fit to use" procedure function:

"needs to be kept in the database for a long time

"Needs to be repeated by multiple users

"Business logic is the same, but the parameters are different.

Batch operation of large amounts of data, such as: BULK insert a lot of data

"Suitable for use" SQL:

Any of the above, you can use SQL

"For tables, views, sequences, indexes, and so on, or to use SQL

Sample Bulk Add operations:

--Definition process Create or Replace procedure Batchinsertas I number (4): = 1;begin for i in 1..999 loop INSERT into EMP (empno,ename) VALUES (i, ' Employees ' | |           i); End Loop; end;/--call process exec Batchinsert;

Oracle Series: (29) Stored procedures and storage functions