存储过程

（1）无参数存储过程创建和调用-统计emp表中职工人数

create or replace procedure emp_count

as

v_total number(10);

begin

select count(*) into v_total from emp;

dbms_output.put_line('total is:'||v_total);

end;

exec emp_count

（2）带输入参数的存储过程创建和调用-输入职工编号查看该职工的信息

create or replace procedure proc_ename (vempno in varchar2)

as

empno number(4);

ename varchar2(10);

begin

select empno,ename into empno,ename from emp where empno=vempno;

dbms_output.put_line(empno||ename);

end;

exec proc_ename(7369);

（3）带输出参数的存储过程创建和调用-部门30的平均工资

create or replace procedure proc_avgsal(avgsal out number)

as

begin

select avg(sal) into avgsal from emp where deptno=30;

end;

 

declare

v_avgsal number;

begin

proc_avgsal(v_avgsal);

dbms_output.put_line('deptno 30 is'||v_avgsal);

end;

 

（4）函数创建和调用

create or replace function fun_sal(inputsal number)

return varchar2

as

salary varchar2(20);

begin

if inputsal>=3000 then

salary:='upper';

else

salary:='lower';

end if;

return(salary);

end;

select emp.*,fun_sal(sal) from emp;

 

（5）程序包封装存储过程和函数

create or replace package emp_package

is

procedure emp_list;

end emp_package;

 

create or replace package body emp_package

is

procedure emp_list

as

cursor emp_cursor is select * from emp;

begin

for emp_record in emp_cursor loop

dbms_output.put_line(emp_record.empno||emp_record.ename||emp_record.sal);

end loop;

end;

end emp_package;

exec emp_package.emp_list;

转载于:https://www.cnblogs.com/Azaimer/p/11093059.html