Oracle函数和存储过程简单实例
Oracle函数和存储过程简单实例
1.函数
1)创建函数
- create or replace function get_tax(x number)
- return number as
- begin
- declare y number;
- begin
- y:=x-2000;
- if x <= 0 then
- return 0;
- end if;
- return y*5/100;
- end;
- end get_tax;
2)执行函数
- SQL> select get_tax(1000) from dual;
结果显示:
- GET_TAX(1000)
- -------------
- -50
2.存储过程
1)存储过程(in)
创建:
- create or replace procedure update_test(uid in varchar2,uname in varchar2)
- as
- begin
- update test set username=uname where userid=uid;
- commit;
- end update_test;
- SQL> execute update_test('06','LinuxIDC');
2)存储过程(out)
创建:
- create or replace procedure test_up(uid out varchar2,uname out varchar2)
- as
- begin
- select * into uid,uname from test where userid='04';//不能缺少into关键字
- end test_up;
- SQL> var id varchar2(10);
- SQL> var name varchar2(30);
- SQL> exec test_up(:id,:name);//括号里必须加上冒号,这和in的不同
- PL/SQL procedure successfully completed
- id
- ---------
- 04
- name
- ---------
- LinuxIDC
更多Oracle相关信息见Oracle 专题页面 http://www.bkjia.com/topicnews.aspx?tid=12
评论暂时关闭