（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;