技术心得
一、SQL查询：
1、”列出同部门中工资高于1000的员工数量超过2人的部门，显示部门名字、地区名称”.查询语句如下： select distinct dept.department_name,loc.city
from employees emp,departments dept,locations loc where emp.department_id=dept.department_id and dept.location_id=loc.location_id and EXISTS( select 1
from employees emp2
where emp2.department_id=emp.department_id and emp2.salary>1000
group by emp2.department_id
having count(emp2.department_id)>2 )
通常我们只需要对记录逐行的筛选，对于分组数据我们只能使用分组语句avg、max等,也就是说如果你想在select中得到这个属性那么“它们“也必须出现在group by中或者你只想得到一个统计数据.回顾上面的例子,我们也可以用以下语句完成: select dept.department_name,loc.city,count(*) from employees emp,departments dept,locations loc where emp.department_id=dept.department_id and dept.location_id=loc.location_id and emp.salary>1000
group by dept.department_name,loc.city having count(*) > 2
对于group by来说每一条emp.department_id必对应唯一dept.department_id、dept.department_name因此不论group by department_id还是group by department_name,loc.city达到的效果是一样的
2、用一条语句查询出scott.emp表中每个部门工资前三位的数据:
select department_id,max(salary) max_salary,max(decode (rank,2,salary,salary))mid_salary,min(salary) min_salary from (
select department_id,salary,rank from
(select emp.department_id,emp.employee_id,emp.salary,row_number()over(partition by emp.department_id order by emp.salary) as rank
from employees emp) E where E.rank<=3 )
group by department_id
rownumber()over(patition by column1 order by column2),表示以column1分组对column2排序,row_number可用于筛选重复项.
3、哪些员工跟Den(FIRST_NAME)、Rephaely(Last_Name)不在同一个部门.
此处可能存在没有部门的员工，应该用No Exists筛选deptno与该员工不等的记录.若要求空值可使用NO EXISTS若不要求空值可用EXISTS,尽量用EXISTS取代IN、ANY、ALL等操作(可提高性能),注意空值的处理!!!
4、在多表连接查询中，子查询最多只可嵌套一层否则Oracle无法识别
5、for handle in:游标
二、PL/SQl存储过程 1、 游标的使用:
显式游标的使用分为四步,声明、打开、循环、关闭. 打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识结果
集合。如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。如下所示:
DECLARE
CURSOR c4(dept_id NUMBER, j_id VARCHAR2) IS
SELECT first_name f_name, hire_date FROM employees WHERE department_id = dept_id AND job_id = j_id; --声明游标定义记录变量，比声明记录类型变量要方便，不容易出错 v_emp_record c4%ROWTYPE; BEGIN
-- OPEN c4(90, 'AD_VP'); /* LOOP
FETCH c4 INTO v_emp_record; IF c4%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是' ||v_emp_record.hire_date); ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); EXIT; END IF; END LOOP;*/
/* CLOSE c4; ---关闭游标*/ FOR c1 IN c4(90,'AD_VP') LOOP
v_emp_record.f_name := c1.f_name;
v_emp_record.hire_date := c1.hire_date;
DBMS_OUTPUT.put_line(c1.f_name||'的雇佣日期是'||c1.hire_date); END LOOP; END;
以FOR c1 IN c_cursor使用游标，c1会自动遍历每行记录,不用像显式游标一样打开游标后在循环中使用FETCH将表征多行记录的游标的值传递出来,FOR语句相当于OPEN与LOOP、FETCH的综合使用,且不必人为的关闭.
2、 隐式游标的处理:
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区.
隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。 格式调用为： SQL% 注：INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。 隐式游标属性 属性 SQL%ISOPEN SQL%FOUND SQL%FOUND SQL%NOTFUOND SQL%NOTFOUND SQL%ROWCOUNT
值 SELECT FALSE 有结果 没结果 没结果 有结果 返回行数，只为1 INSERT FALSE
UPDATE FALSE 成功 失败 失败 成功 修改的行数
DELETE FALSE 成功 失败 失败 失败 删除的行数
TRUE FALSE TRUE FALSE
插入的行数
3、 异常处理: 分类:
(1)、系统预定义异常,直接引用异常名，并处理即可
(2)、非预定义的异常处理:将定义好的异常情况与标准的Oracle错误联系起来,使用EXCEPTION_INIT语,PRAGMA EXCEPTION_INIT(<异常情况>,<错误代码>). (3)、用户自定义的异常处理:无错误代码，需要在本程序块完成捕捉与处理.
DECLARE
v_empno employees.employee_id%TYPE :=&empno; no_result EXCEPTION; BEGIN
UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno; IF SQL%NOTFOUND THEN RAISE no_result; END IF; EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!'); WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END;
(4)、用户定义的异常处理, RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] ),
重新定义异常错误消息，为应用程序提供了一种与Oracle交互的方法，这里的error_number是从-20,000到-20,999之间的参数.可在程序块中自定义异常，并捕捉在其他函数或存储过程中RAISE_APPLICATION_ERROR抛出的异常，与Oracle交互.
4、PRAGMA AUTONOMOUS_TRANSACTION
ORACLE8i 可以支持事务处理中的事务处理的概念．这种子事务处理可以完成它自己的工作，独立于父事务处理进行提交或者回滚．通过使用这种方法，开发者就能够这样的过程，无论父事务处理是提交还是回滚，它都可以成功执行．
(1)使用自动事务处理:
DROP TABLE logtable; CREATE TABLE logtable( Username varchar2(20), Dassate_time date, Mege varchar2(60) );
CREATE TABLE temp_table(N number);
CREATE OR REPLACE PROCEDURE log_message(p_message varchar2) AS
PRAGMA AUTONOMOUS_TRANSACTION; BEGIN
INSERT INTO logtable VALUES(user,sysdate,p_message); COMMIT;
END log_message; BEGIN
Log_message('About to insert into temp_table'); INSERT INTO temp_table VALUES(1);
LOG_message('Rollback to insert into temp_table'); ROLLBACK; END;
select * from logtable;
select * from temp_table(查询结果无数据)
(2)不使用自动事务处理:
CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
AS BEGIN
INSERT INTO logtable VALUES(user,sysdate,p_message); COMMIT;
END log_message;
(select * from temp_table查询结果有数据)
三、报表开发: 流程:
1、 产生数据源:
以 PL/SQL或SQl获取数据源，并用Reports Developer工具创建数据模型并分组.
(Reports Builder生成rdf文件，或直接由PL/SQL程序包直接输出xml、html等文件格式) 2、 注册并发程序，输出的格式为XML.这样服务器上便保存了数据定义的源文件. 3、 设计rtf报表模板，调整模板布局
4、 注册数据源和模板，数据源的代码需与并发请求的代码一致 实例:
数量帐报表-进出存明细表
1、编写报表前需了解以下五个表结构:CUX_INV_LINES_ALL、CUX_INV_HEADERS_ALL、CUX_INV_ONHAND_SHIPS_AL、CUX_INV_MATERIAL_ALL、
GL_CODE_COMBINATIONS,其中行表CUX_INV_LINES_ALL存储了报表所需的主要数据,为查询语句的主表，从CUX_INV_ONHAND_SHIPS_ALL表中取出期初数据（保存为距今最近的月份中的一条现有量）,并关联船表的 四个唯一性索引,注意不可加上条件CUX_INV_HEADERS_ALL.PERIOD_NAME=CUX_INV_SHIPS_ALL.PERIOD_NAME,因为只有TYPE_CODE为’GL’类型的记录及总账才存在期间。因此加上条件 CUX_INV_ONHAND_SHIPS_ALL.period_name <= to_char(to_date(substr(p_Start_Date,1,7),'yyyy-mm'),'yyyy-mm')
此外加上报表要求查询的日期条件: and decode(cila.type_code,'GL',ciha.gl_date,'AP',ciha.invoice_date,NULL)between to_date(p_Start_Date,'yyyy-mm-dd HH24:MI:SS'))and(
to_date(p_End_Date,'yyyy-mm-dd HH24:MI:SS')); ！！!注意’HH24:MI:SS’必不可少，因为请求程序要求的数据一定是带时分秒的数据否则解析报表时会报错.如下所示:
**Starts**23-08-2011 11:53:42 **Ends**23-08-2011 11:53:42
ORA-01830: 日期格式图片在转换整个输入字符串之前结束
2、根据借方数量与借方金额求出借方单价同理求贷方单价，每发生一笔，统计当前数量与当前金额，然后得出当前数量当前总价与当前单价