afic写的:create or replace procedure p_getdate
(
i_date IN varchar2,
o_date1 OUT varchar2,
o_date2 OUT varchar2
)
as
begin
o_date1 := i_date||'01';
o_date2 := to_char(to_date(i_date,'yyyymm') + 32,'yyyymm')||'01';
end;命令嘛:
begin
p_getdate('20060102');
end
(
i_date IN varchar2,
o_date1 OUT varchar2,
o_date2 OUT varchar2
)
as
begin
o_date1 := i_date||'01';
o_date2 := to_char(to_date(i_date,'yyyymm') + 32,'yyyymm')||'01';
end;命令嘛:
begin
p_getdate('20060102');
end
CREATE OR REPLACE FUNCTION get_c2(empid NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT code FROM empcode WHERE id = empid) LOOP
Col_c2 := Col_c2|| ',' || cur.code;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/ --获得rolename
CREATE OR REPLACE FUNCTION get_c3(empid NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT r.rolename FROM emprole er, role r WHERE er.roleid = r.roleid(+) and er.id = empid) LOOP
Col_c2 := Col_c2|| ',' || cur.code;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/ 运行sql的到结果:
select e.empid, e.name, e.age, get_c2(code) code, get_c3(rolename) rolename
from employee e, empcode ec, emprole er, role r
where e.empid(+) = ec.empid and e.empid(+) = er.empid and er.roleid = r.roleid(+)
group by e.empid