Oracle数据笔记-【5】子程序（存储过程）和函数

来源：互联网发布：苹果mac管理员密码忘记编辑：程序博客网时间：2024/04/30 21:49

select * from scott.emp--1、什么是存储过程？--预编译好的程序，简化应用程序的调用--2、语法 （特殊的PL/SQL）create procedure proc_testasbegin       --sql语句       --exceptionend;--例1--无参的存储过程create procedure proc_sel4as       myname varchar(10);       deptname varchar(10);begin       --联表查询       select ename,dname into myname,deptname from scott.emp,scott.dept        where scott.emp.deptno = scott.dept.deptno and empno = 7369;       dbms_output.put_line(myname||deptname);end;--调用存储过程begin       proc_sel4;end;select * from scott.emp;select * from scott.dept;--带参数的存储过程create procedure proc_test(参数1，参数2....)asbegin       --sql语句end;---加工资，manage岗位工资加?(参数1员工编号，参数2工资加多少)create procedure proc_addsal1(myempno number,mysal number)as       myjob varchar(10);begin       select job into myjob  from scott.emp where empno = myempno;       if myjob = 'MANAGER' then             update scott.emp set sal = sal+mysal where empno = myempno;            dbms_output.put_line('恭喜！你加了'||mysal||'工资');       else            dbms_output.put_line('sorry！没有加工资，赶紧往上爬！');       end if;end;--调用存储过程begin            proc_addsal1(7698,1000);end;select * from scott.empcreate procedure proc_addsal3(myempno number,mysal number,jobtest varchar)as       myjob varchar(10);begin       select job into jobtest  from scott.emp where empno = myempno;       if myjob = 'MANAGER' then             update scott.emp set sal = sal+mysal where empno = myempno;            dbms_output.put_line('恭喜！你加了'||mysal||'工资');       else            dbms_output.put_line('sorry！没有加工资，赶紧往上爬！');       end if;end;

--------------------我是分割线------------------------

--函数 = 方法--预编译好的可以带返回值的操作--语法--无参的函数create function fun_test1return numberas mysum number := 0;begin       for i in 1..100           loop             mysum := mysum +i;              end loop;       return mysum;end;select fun_test1() from dual--有参数的函数create function fun_add(a number,b number)return numberasbegin       return a+b;end;select * from scott.emp--查询7499的用户的所有收入（sal+comm）select empno,ename,(sal+comm) as 总收入 from scott.empselect empno,ename,fun_add(sal,1000) as 总收入 from scott.emp

0 0