Simple Oracle functions and stored procedures

1.Function

1) Create a function

1. Create or replace function get_tax (x number)
3. Return number
5. Begin
7. Declare y number;
9. Begin
11. Y:=X-2000;
13. If x<= 0 then
15. Return 0;
17. End if;
19. Returns y * 5/100;
21. End;
23. End get_tax;

2) Execution function

1. SQL>Select get_tax (1000) from dual;

Result:

1. GET_TAX (1, 1000)
3. -------------
5. -50

2.Stored Procedure

1) Stored procedure (In)

Create:

1. Create or replace procedure update_test (uid in varchar2, uname in varchar2)
2. As
3. Begin
4. Update test setUsername=UnameWhereUserid=Uid;
5. Commit;
6. End update_test;

Run:

1. SQL>Execute update_test ('06', 'linuxid ');

2) Stored procedure (Out)

Create:

1. Create or replace procedure test_up (uid out varchar2, uname out varchar2)
3. As
5. Begin
7. Select * into uid, uname from test whereUserid='04'; // The into keyword cannot be missing
9. End test_up;

Run:

1. SQL>Var id varchar2 (10 );
2. SQL>Var name varchar2 (30 );
3. SQL>Exec test_up (: id,: name); // a colon must be added to the brackets, which is different from in.

Result:

1. PL/SQL procedure successfully completed
3. Id
5. ---------
7. 04
9. Name
11. ---------
13. LinuxIDC

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12