My PL/SQL practice --8/2/10
来源:互联网 发布:淘宝宝贝发布视频教程 编辑:程序博客网 时间:2024/06/10 20:20
SQLPlus :http://www.orafaq.com/wiki/SQL*Plus_FAQ
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm
1. Transfer values from a sql scripts:
CNT=`sqlplus -s username/password1@dbname @getUVQuery_NULLCNT`;
Note : Remeber to use option "-s" (-s or -silent -- start sqlplus in silent mode. Not recommended for beginners!)
if not use "-s" , will return something as below :
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Feb 9 11:03:08 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
0
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2.getUVQuery_NULLCNT.sql
set term off
column cnt noprint new_value total
SELECT count(inquiry_id) cnt
FROM inquire_all_current a
WHERE FORM_COMPLETE_STATUS = 'Y'
and ITEM_POPULATE_STATUS = 'N'
and expand_type='uvall'
and uv_query is null
ORDER BY INQUIRY_ID ASC;
set term on
prompt &total
exit
3. Define cursor and use cursor:
DECLARE
CURSOR cur IS
SELECT inquiry_id
FROM inquire_all_current a
WHERE FORM_COMPLETE_STATUS = 'Y'
and ITEM_POPULATE_STATUS = 'N'
and expand_type='uvall'
and uv_query is null
ORDER BY INQUIRY_ID ASC;
BEGIN
For rec in cur
LOOP
UPDATE INQUIRE_ALL_CURRENT
SET ITEM_POPULATE_STATUS='K'
WHERE inquiry_id = rec.inquiry_id;
COMMIT;
END LOOP;
END;
/
EXIT;
4.Define cursor with parameter :
DECLARE
v_cnt NUMBER DEFAULT 1;
v_source_name agg_product.source_name%TYPE;
CURSOR c_check_outstand_data (in_source_name agg_product.source_name%TYPE)IS
SELECT COUNT(1) cnt
FROM agg_pdt_image_name_nonali_stg a
WHERE NOT EXISTS (SELECT 1
FROM agg_pdt_image_name_nonali b
WHERE b.source_name = in_source_name
AND b.product_image_name = a.product_image_name);
BEGIN
v_source_name := '&1';
LOOP
OPEN c_check_outstand_data(v_source_name);
FETCH c_check_outstand_data INTO v_cnt;
CLOSE c_check_outstand_data;
INSERT INTO agg_pdt_image_name_nonali
SELECT v_source_name, product_image_name
FROM agg_pdt_image_name_nonali_stg a
WHERE NOT EXISTS (SELECT 1
FROM agg_pdt_image_name_nonali b
WHERE b.source_name = v_source_name
AND b.product_image_name = a.product_image_name);
COMMIT;
EXIT;
END LOOP;
END;
/
EXIT;
5.output file :
SET FEEDBACK OFF
CLEAR COLUMNS
CLEAR BREAKS
SET TERMOUT OFF
SET AUTOPRINT OFF
SET VERIFY OFF
SET TRIMSPOOL ON
spool kill_RFI_list.lst;
SELECT CAST( inquiry_id as varchar(50) )
FROM inquire_all_current a
WHERE FORM_COMPLETE_STATUS = 'Y'
and ITEM_POPULATE_STATUS = 'N'
and expand_type='uvall'
and uv_query is null
ORDER BY INQUIRY_ID ASC;
spool off;
6. convert Integer to String in sql :
SELECT CAST( inquiry_id as varchar(50) )
FROM inquire_all_current a
- My PL/SQL practice --8/2/10
- Best practice in PL/SQL
- My First PL/SQL Procedure
- My Second PL/SQL Procedure
- PL/SQL Best Practice----On BULK COLLECT
- PL/SQL Developer登录后默认选中My Objects
- Actual Practice : Scalar Valued Functions in my work - 2
- Practice.3_YU My Queue
- pl/sql 10 注册码
- PL/SQL 10注册码
- pl/sql(2)
- PL/SQL笔记2
- PL/SQL编程2
- pl/sql(2)
- pl/sql学习2——pl/sql记录
- PL/SQL专家指南2——PL/SQL精髓
- 小技巧--进入PL/SQL DEVELOPER时自动切换到My objects
- my sql
- 尝试用Gearman实现分布式处理(PHP)
- 用JavaScript获取Gridview中某个触发事件控件的ID
- 六、创建SQLServerDAL,实现接口里的方法
- Visual Studio 2008系列教程(一):VS 2008安装详解!
- 定制IE浏览器的尖兵利器 - BHO
- My PL/SQL practice --8/2/10
- sql server 2005分页存储过程和sql server 2000分页存储过程
- 七、增加web.config里的配置信息
- 自学笔记 织梦CMS文件结构
- 下拉菜单 js自动跳转
- 好运来!
- 八、创建DALFactory
- 通过bat文件运行jar包程序
- 用命令行(CMD)中启动和关闭ORACLE服务