--demo1
create or replace procedure proc_query_emp
( p_empno emp.empno%type)
is
v_emp
emp%rowtype;
begin
select *
into v_emp
from emp
where empno = p_empno;
dbms_output.put_line(v_emp.empno);
dbms_output.put_line(v_emp.ename);
dbms_output.put_line(v_emp.sal);
end;
--demo2
create or replace procedure proc_i_dept
(p_deptno dept.deptno%type,
p_dname
dept.dname%type,
p_loc
dept.loc%type
)
is
begin
insert into
dept(deptno,dname,loc)
values(p_deptno,p_dname,p_loc);
commit;
end;
--demo3
create or replace procedure proc_test_par
( p_i in varchar2,
p_j out
varchar2,
p_m in out
varchar2
)
is
begin
p_j := '2';
dbms_output.put_line(p_i);
dbms_output.put_line(p_j);
dbms_output.put_line(p_m);
end;
--demo4
--step1
create table pos_info
( pid char(3),
pnum number
)
insert into pos_info values('001',0);
insert into pos_info values('002',0);
create table sales
( sid char(16),
sdate date
)
--step2
create or replace procedure proc_i_sales
(p_pid char)
is
v_pnum
pos_info.pnum%type;
begin
select pnum
into v_pnum
from
pos_info where pid = p_pid;
insert into
sales(sid,sdate)
values(p_pid || to_char(sysdate,'YYYYMMDD')
|| lpad(v_pnum + 1,5,'0'),sysdate);
update
pos_info
set pnum =
pnum + 1
where pid =
p_pid;
commit;
end;
--demo5
create or replace procedure proc_getnum
( p_pid in pos_info.pid%type,
p_pnum out
pos_info.pnum%type
)
is
begin
select pnum
into p_pnum
from
pos_info where pid = p_pid;
end;
create or replace procedure proc_new_sales
(p_pid char)
is
v_pnum
pos_info.pnum%type;
begin
proc_getnum(p_pid,v_pnum);
insert into
sales(sid,sdate)
values(p_pid || to_char(sysdate,'YYYYMMDD')
|| lpad(v_pnum + 1,5,'0'),sysdate);
update
pos_info
set pnum =
pnum + 1
where pid =
p_pid;
commit;
end;
--demo6
create or replace function fun_empsal
( p_empno emp.empno%type)
return varchar2
is
v_emp
emp%rowtype;
begin
select *
into v_emp
from emp
where empno = p_empno;
if
v_emp.sal >= 3000 then
return
'OK';
else
return
'NO';
end
if;
end;
--demo7
--創建在dept表中插入和刪除一個記錄的數據包，它且有一個函數(返回插入或刪除的部門名稱)和兩個過程。然后調用包。
CREATE OR REPLACE PACKAGE deptpack
AS
PROCEDURE
inser(dno IN NUMBER,NAME IN VARCHAR2,location IN VARCHAR2);
PROCEDURE
de(dno IN NUMBER);
FUNCTION
getdname(num IN NUMBER) RETURN VARCHAR2;
END deptpack;
CREATE OR REPLACE PACKAGE BODY deptpack
AS
PROCEDURE
inser(dno IN NUMBER,NAME IN VARCHAR2,location IN VARCHAR2)
AS
BEGIN
INSERT
INTO dept VALUES(dno,NAME,location);
dbms_output.put_line('1 record inserted!');
END inser;
PROCEDURE
de(dno IN NUMBER)
AS
BEGIN
DELETE
FROM dept WHERE deptno=dno;
END de;
FUNCTION
getdname(num IN NUMBER)
RETURN
VARCHAR2
AS
vname
VARCHAR2(10);
BEGIN
SELECT
dname INTO vname FROM dept WHERE deptno=num;
RETURN
vname;
EXCEPTION
WHEN
no_data_found THEN
dbms_output.put_line('No such deptno exists!');
END
getdname;
END deptpack;
-- 
學海無涯