请大家进来指教几个简单的问题,入门级.
czb 2003-10-29 05:37:32 假设有如下三个表,projectemployee里的两个字段分别指向employee表和project表的关键字
(也不知道这样建表有何不妥的地方?请指教)
employee(employeeid number primary key,name varhcar2(16) not null,salary number default 0);
project(projectid number primary key,name varhcar2(64) not null,startdate date,enddate date);
projectemployee(employeeid number not null references employee(employeeid),projectid number not null references project(projectid),primary key(employeeid,projectid));
如何查询出每个项目的名称和每个项目对应的所有员工工资的总和?
另外下面是一段函数,可以编译运行,结果也正确,但是感觉对varchar2字段的连接有点别扭。如
lsql := lsql || 'where name like ''%' || lName || '%'' ';
关键字lName的两边都用了两个"'",是否有简单点的写法?比如类似下面的
lsql := lsql || 'where name like %lName% ';
另外也不知道下面的函数对于date、varchar2型字段为NULL或""的判断是否正确?
create or replace function getEmployee(pEmployeeid in integer,pName in varchar2, pBirthday in date)
return ref_cursor_package.t_ref_cursor as
result ref_cursor_package.t_ref_cursor;
lSql varchar(1200);
lIsLateWhere boolean := false;
lName employee.name%type; --varchar2字段
lBirthday employee.birthday%type; --date字段
begin
lName := ltrim(rtrim(pName));
lBirthday := pBirthday;
lsql := 'select * from employee ';
if pEmployeeid!= -1 then
lsql := lsql || 'where employeeid=' || pEmployeeid;
else
if lName is not null and length(lName)!=0 then
lsql := lsql || 'where name like ''%' || lName || '%'' ';
lIsLateWhere := true;
end if;
if lBirthday is not null and lBirthday!=to_date('1900-01-01','yyyy-mm-dd') then
if lIsLateWhere then
lsql := lsql || ' and birthday >''' || lBirthday || ''' ';
else
lsql := lsql || 'where birthday >''' || lBirthday || ''' ';
lIsLateWhere := true;
end if;
end if;
end if;
open result for lsql;
return result;
end getEmployee;
又或者上面这段函数根本不符合oracle编程规范,都请大家指出,我刚学出练的.