月归档：十一月 2010

使用sqlplus生产批量plsql

发表于 2010 年 11 月 26 日 由 惜分飞

要点
chr(10)–表示换行
set heading off–不显示表格头部
set pagesize 0–不分页
set linesize 800–行宽
spool filename–写入到文件中
sql语句

select 'declare'||chr(10)||' i   integer:=0;'||chr(10)||'cursor cur is select rowid as rid from '||t1 ||' WHERE DATASOURCE=''WN'' OR MODIFYSOURCE=''WN'';'||chr(10)||'begin for c in cur loop '||chr(10)||'i:=i+1;'||chr(10)|| 'update '||t1||chr(10)||'SET DATASOURCE=F_BYF_UPDATE_WN(datasource),MODIFYSOURCE=F_BYF_UPDATE_WN(MODIFYSOURCE),REMARK=F_BYF_UPDATE_WN(REMARK) WHERE rowid=c.rid;'||chr(10)|| ' if mod(i,200)=0 then'||chr(10)|| '  commit;'||chr(10)||'end if;'||chr(10)||' if mod(i,20000)=0 then'||chr(10)||'  dbms_lock.sleep(40);'||chr(10)||' if i>30000 then'||chr(10)|| ' dbms_lock.sleep(40);'||chr(10)||' end if;'||chr(10)||' end if;'||chr(10)|| ' if i>500000 then'||chr(10)||'  exit;'||chr(10)||' end if;'||chr(10)||' end loop;'||chr(10)||'  commit;'||chr(10)||'end;'||chr(10)||'/'||chr(10)||'--------'||t1||'-------------' from t1;

效果

declare                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
 i      integer:=0;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
cursor cur is select rowid as rid from FUND294
 WHERE DATASOURCE='WN' OR MODIFYSOURCE='WN';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
begin for c in cur loop                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
i:=i+1;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
update FUND294                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
SET DATASOURCE=F_BYF_UPDATE_WN(datasource),MODIFYSOURCE=F_BYF_UPDATE_WN(MODIFYSOURCE),REMARK=F_BYF_UPDATE_WN(REMARK) WHERE rowid=c.rid;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
 if mod(i,200)=0 then                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  commit;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
end if;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
 if mod(i,20000)=0 then                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  dbms_lock.sleep(40);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
 if i>30000 then                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
 dbms_lock.sleep(40);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
 end if;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
 end if;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
 if i>500000 then                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  exit;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
 end if;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
 end loop;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
  commit;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
end;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
/                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
--------FUND294-------------
发表在 Oracle | 评论关闭

pl/sql 常用语法

发表于 2010 年 11 月 10 日 由 惜分飞

1、procedure

CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name
[parameter_lister]
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name]
--举例
create or replace
procedure dos_fx
is
cursor c1 is 
select get from dos_gj;              
begin
 for c2 in c1 loop
 insert into dos_gj_1(ip,gettime,get)values(REGEXP_SUBSTR( c2.get, '(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])(\.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])){3} '),
 REGEXP_SUBSTR( c2.get, '^(\d{4})-(\d{2})-(\d{2}) (\d{2}:\d{2}:\d{2})'),c2.get);
 end loop;
 commit;
end;

2、function 

CREATE [ OR REPLACE] FINCTION [schema.]function_name
[parameter_list]
RETURN returning_datatype
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION]
exception_section
END [procedure_name]
--举例
CREATE OR REPLACE function fn_md5(input_string VARCHAR2) return varchar2
IS
raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
decrypted_raw RAW(2048);
error_in_input_buffer_length EXCEPTION;
BEGIN
sys.dbms_obfuscation_toolkit.MD5(input => raw_input,checksum => decrypted_raw);
return rawtohex(decrypted_raw);
END;

3、package

--header
CREATE [OR REPLACE] PACKAGE package_name
{AS|IS}
public_variable_declarations |
public_type_declarations |
public_exception_declarations |
public_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
--body
CREATE [OR REPLACE] PACKAGE BODY package_name
{AS|IS}
private_variable_declarations |
private_type_declarations |
private_exception_declarations |
private_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
--举例
--header
create or replace
package pk_t1
as
procedure get_num(getnum in number,aname varchar2);
end pk_t1;
--body
create or replace
package body pk_t1
as
procedure get_num(getnum in number,aname varchar2)
is 
begin
 insert into shell_1 values(getnum,aname);
end;
end pk_t1;

4、trigger

CREATE [OR REPLACE] TRIGGER trigger_name
{before|after|instead of} event
ON {table_or_view_name|DATABASE}
[FOR EACH ROW[WHEN condition]]
trigger_body
--举例
create or replace trigger add_shell
before update 
on shell_1 for each row
declare
begin
 if :OLD.name!=:new.name then
  :new.name:='ggggg';
 end if;
end;
发表在 Oracle 开发 | 评论关闭

oracle 开机自动启动

发表于 2010 年 11 月 7 日 由 惜分飞

一、使用root用户修改/etc/oratab 文件：
$ vi /etc/oratab
orcl:/u01/oracle:N (ORACLE_SID:ORACLE_HOME:N)
改为：
orcl:/u01/oracle:Y 也就是将最后的N改为Y

二、使用Oracle用户修改$ORACLE_HOME/bin/dbstart和dbstop文件：
ORACLE_HOME_LISTNER=$1
改为：
ORACLE_HOME_LISTNER=$ORACLE_HOME

三、创建服务
$su – root
# cd /etc/rc.d/init.d/
# vi oradb
在oradb中添加文本

#!/bin/bash
# chkconfig: 345 99 10
# description: Startup Script for Oracle Databases
export ORACLE_BASE=/u01
export ORACLE_HOME=/u01/oracle
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
ORA_OWNR="oracle"
# if the executables do not exist -- display error
if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ]
then
echo "Oracle startup: cannot start"
exit 1
fi
# depending on parameter -- startup, shutdown, restart
# of the instance and listener or usage display
case "$1" in
start)
# Oracle listener and instance startup and oem
echo -n "Starting Oracle: "
# 启动listener and instance
su - $ORA_OWNR -c "$ORACLE_HOME/bin/dbstart"
touch /var/lock/Oracle
# 启动oem
su - $ORA_OWNR -c "$ORACLE_HOME/bin/emctl start dbconsole"
echo "OK"
;;
stop)
# Oracle listener and instance shutdown and oem
echo -n "Shutdown Oracle: "
# 关闭oem
su - $ORA_OWNR -c "$ORACLE_HOME/bin/emctl stop dbconsole"
# 关闭数据库和listener
su - $ORA_OWNR -c "$ORACLE_HOME/bin/dbshut"
rm -f /var/lock/Oracle
echo "OK"
;;
reload|restart)
$0 stop
$0 start
;;
*)
echo "Usage: `basename $0` start|stop|restart|reload"
exit 1
esac
exit 0

修改所属组和用户
#chown oracle.oinstall /etc/rc.d/init.d/oradb
修改访问权限
#chmod 775 /etc/rc.d/init.d/oradb
添加服务到自动启动中
#chkconfig –add oradb
查看数据库服务开机启动状态
#chkconfig –list oradb

四、# chkconfig: 345 99 10说明
指出3，4，5级别启动这个服务，99是在相应的/etc/rc.d/rcN.d(N为前面指定的级别，这里是345)目录下生成的链接文件的序号(启动优先级别)S99oradbstart，10为在除前面指出的级别对应的/etc/rc.d/rcN.d(N为除345之外的级别)目录生成的链接文件的序号(服务停止的优先级别)

发表在 Linux, Oracle | 评论关闭