************************************************************************************************常用设置********************************---on-log 触发器编写示范Declare flag varchar2(80):=null; cou number:=1; n number;Begin Loop logon(USERNAME,PASSWORD||'@'||CONNECT,PROPERTY_FALSE....); flag:=Get_Application_Property(DATASOURCE); Exit when cou>8 or flag='ORACLE'; cou:=cou+1; End Loop; If flag<>'ORACLE' then set_alert_property('a_1',alert_message_text, '登录失败,请返回重试'); n:=show_alert('a_1'); raise form_trigger_failure; --中断 FORM End if;End;---对基表执行查询(只对基表) Set_Block_Property('Block_Name',Default_Where,'where ......'); Go_Block('Block_Name'); Execute_Query; *************************************************** 变量： 局部变量； 全局变量--------------1.:block.item 2.:parameter.v_name 3.:global.V_name ***************************************************---同步发生显示 synchronize; ---实施'TRIGGER'触发 EXECUTE_TRIGGER(TRIGGER_NAME);---清除模块 clear_block(NO_VALIDATE); 'NO_VALIDATE'不生效
--建立警告栏并由警告栏选择
Declare n number;Begin Set_Alert_Property('Alert_Name',Alert_Message_Text,'message'); n:=Show_Alert('Alert_Name'); If n=Alert_Button1 then ...; ElsIf n=Alert_Button2 then ...; End if;End;---WINDOW设置 --运行时最大化,最小化 Set_Window_Property(FORMS_MDI_WINDOW, WINDOW_STATE, MAXIMIZE|MINIMIZE); --WINDOW标题 Set_Window_Property(FORMS_MDI_WINDOW, title,'TEXT'); --退出是否为真 Set_Window_Property(FORMS_MDI_WINDOW, REMOVE_ON_EXIT,PROPERTY_FALSE|TRUE);
---设置系统提示信息等级 :System.Message_Level:= '5|10|15|20';
---设置ITEM属性 --设置ITEM属性ENABLED Set_Item_Property('Block_name.Item_name',ENABLED,PROPERTY_TRUE|FALSE); --设置ITEM属性NAVIGABLE Set_Item_Property('Block_name.Item_name',NAVIGABLE,PROPERTY_TRUE|FALSE); --设置ITEM属性VISUAL_ATTRIBUTE Set_Item_Property('Block_name.Item_name',visual_attribute,'vname'); --'vname'由导航器中(VISUAL_ATTRIBUTES)定义 --设置ITEM属性DISPLAYED Set_Item_Property('Block_name.Item_name',displayed,TRUE|FALSE); --设置ITEM属性POSITION Set_Item_Property('Block_name.Item_name',position,x,y); --设置ITEM_SIZE Set_Item_Property('Block_name.Item_name',item_size,x,y); --设置ITEM属性LABLE Set_Item_Property('Block_name.Item_name',LABEL,'MESSAGE')---设置LIST ITEM示范Declare n number;Begin clear_list('b1.fkfs'); m:=populate_group('fkfs'); populate_list('b1.fkfs','fkfs');/* 其中FKFS 为 record group ;*/
End;
---增加'LIST ITEM' Add_List_Element(list_name, list_index, list_label, list_value); Add_List_Element(list_id, list_index, list_label, list_value);---删除'LIST ITEM'项 Delete_List_Element(list_name, list_index); Delete_List_Element(list_id, list_index); 例: BEGIN Delete_List_Element('years',1); Add_List_Element('years', 1, '1994', '1994'); END; ---获得'LIST ITEM'项的组成 1.获得'LIST ITEM'的总和 GET_LIST_ELEMENT_COUNT(list_id); GET_LIST_ELEMENT_COUNT(list_name);
2.获得'LIST ITEM'的标签 GET_LIST_ELEMENT_LABEL(list_id, list_name, list_index); GET_LIST_ELEMENT_LABEL(list_name, list_index);
3.获得'LIST ITEM'的值 GET_LIST_ELEMENT_VALUE(list_id, list_index); GET_LIST_ELEMENT_VALUE(list_name, list_index);
---设置'时间'DECLARE timer_id Timer; one_minute NUMBER(5) := 60000; BEGIN timer_id := CREATE_TIMER('emp_timer', one_minute, REPEAT|NO_REPEAT); END;---产生一个'EDITER'框DECLARE ed_id Editor; status BOOLEAN; BEGIN ed_id:=Find_Editor('edit_name'); ---由'edit_name'导航器定义 IF NOT Id_Null(ed_id) THEN Show_Editor(ed_id, NULL, :block_name.item_name, status); ELSE Message('Editor "Happy_Edit_Window" not found'); RAISE Form_Trigger_Failure; END IF; END; ----产生一个'LOV'框DECLARE lv_id LOV; status BOOLEAN; BEGIN lv_id := Find_LOV('lov_name'); ---'lov_name' 由导航器定义 -- IF Id_Null(lv_id) THEN -- lv_id := Find_LOV('lov_name1'); ---'lov_name1' 由导航器定义 -- END IF; status := Show_LOV(lv_id,10,20); END; ---定义一个'EXCEPTION'例外Declare err_1 exception;Begin If ... then Raise err-1; End if;Exception When err_1 then ....END;
---设置应用特性(光标类型) SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'CROSSHAIR'|'BUSY'|'HELP'|'DEFAULT'|'INSERTION');
*********************************************************************** **********************************函数*********************************
---把字符串的字符变成全大写(UPPER)全小写(LOWER)第一个字母大写(INITCAP) UPPER|LOWER|INITCAP(STRING)
---在文件的左('LPAD')右('RPAD')粘贴字符 LPAD|RPAD(STRING,LENGTH,'SET') "LENGTH"为总字符长"SET"为粘贴字符---在文件的左('LTRIM')右('RTRIM')删除字符 LTRIM|RTRIM(SRTING,'SET') "SET"为待删除字符---找出'字符集'在字符串中的位置 INSTR('STRING','SET',N,M) 从'STRING'中找出'SET'从'N'位起第'M'个 ---数的绝对值 ABS(VALUE)
---'MOD'模 MOD(VALUE,除数) 返回'除数'除'VALUE'的余数常用判断'VALUE'是否为整数
---把'VALUE'从'N'位四舍五入'ROUND' 或从'N'位截断'TRUNC' ROUND|TRUNC(VALUE,N)
---返回'VALUE'的符号 SIGN(VALUE)
---列表的最大值 GREATEST(N1,N1...); 列表的最小值 LEAST(N1,N1...);---返回小于或等于数的最大整数 FLOOR(VALUE) floor(1.3)=1 floor(-1.3)=-2---返回大于或等于数的最小整数 CEIL(VALUE) cell (1.3)=2 cell (-1.3)=-1
---取字符串长度 substr(string,start,number) number为string长度,start为string起点
---DECODE函数,多重(if,then,else) decode(value,if1,then1,if2,then2,.....,else)
---判断'VALUE'是否为空(空值替换) NVL(UALUE,'WKFHZ') 'WKFHZ'是为空返回值,不为空则为原值
---字段长度 length(:block_name.item_id)
---返回字符串的第一(最左)个字符的ascII值 ascII(string)
---多行'VALUE'的 (作用于多'行') AVG(VALUE)平均值 COUNT(VALUE)行数 MAX(VALUE)最大值 MIN(VALUE)最小值 SUM(VALUE)和
---字符转换 TRANSLATE(STRING,'待转字符','转换字符'); 如 TEANSLATE('AAABBB','AB','BA') 返回'BBBAAA' ---比较单行中多个列的值获得最大('GREATEST'最小('LEAST') GREATEST|LEAST(列名,列名,...)---按表达式或位置排序 ORDER BY '表达式'OR'位置' ASC|DESC ASC'升',DESC'降' 默认'ASC'**********************************************************************************************************************************************---游标的属性 (1) %ISOPEN 打开属性 布尔型 打开为TRUE 判断'光标'是否打开如未打开则打开'光标' IF NOT(CORSOR_NAME%ISOPEN) THEN OPEN CORSOR_NAME; END IF; FETCH CORSOR_NAME INTO ... (2) %NOTFOUND 布尔型 最近一次'FETCH'返回无结果 则为TRUE OPEN CORSOR_NAME; LOOP FETCH CORSOR_NAME INTO ... EXIT WHEN CORSOR_NAEM%NOTFOUND; END LOOP; (3) %FOUND 布尔型 最近一次'FETCH'返回无结果 则为FALSE OPEN CORSOR_NAME;
WHILE CORSOR_NAME%FOUND LOOP ...... FETCH CORSOR_NAME INTO ... END LOOP; CLOSE CORSOR_NAME; (4) %ROWCOUNT NUMVER型 为游标取出的行数 OPEN CORSOR_NAME; LOOP FETCH CORSOR_NAME INTO ... EXIT WHEN CORSOR_NAME%ROWCOUNT>5; ...... END LOOP; CLOSE CORSOR_NAME;
---循环语句 (1)基本循环 LOOP ..... EXIT WHILE; 如(EXIT WHEN X>100) END LOOP; (2)WHILE循环 WHILE 如( WHEN X>100) LOOP ..... END LOOP; (3)数值型FOR循环 'X'为计数器 FOR X IN (第减值) Y..Z LOOP ..... END LOOP; (4)游标FOR循环
---Exception(例外)在最近的'BEGIN'和'END'之间 Exception 语法1 当'没有数据找到'时 when no_data_found then 语法2 当'发生任何错误'时 when others then 语法3 当'发现多行'时 WHEN TOO_MANY_ROWS THEN 语法4 当'字符向数字转换失败'时 WHEN INVALID_NUMBER THEN 语法5 当'被零除'时 WHEN ZERO_DIVIDE THEN 语法6 当'向唯一索引中插入重复数据'时 WHEN DUP_VAL_ON_INDEX THEN 语法7 当'非法游标操作'时 WHEN INVALID_CURSOR THEN 语法8 当'数字的,数据转换,截字符串或强制性的错误'时 WHEN VALUE_ERROR THEN ****************************************************************************************************************************************************--常用TEXT_IODelcare out_file text_io.file_type;Begin out_file:=text_io.fopen('prn','w'); text_io.new_line(out_file,' '); text_io.put_line(out_file,' ') text_io.fclose(out_file);End;---文本输入输出 TEXT_IO TEXT_IO PACKAGE TEXT_IO FCLOSE TEXT_IO.FILE_TYPE TEXT_IO.FOPEN TEXT_IO.IS_OPEN TEXT_IO.GET_LINE TEXT_IO.NEW_LINE TEXT_IO.PUT TEXT_IO.PUTF TEXT_IO.PUT_LINE USING TEXT_IO CONSTRUCTS----------------------------Declare Out_file Text_io.file_type; L Varchar2(100); L1 Varchar2(100); L2 Varchar2(100);Begin Out_file :=text_io.fopen('c:/ll/login.txt','r'); If text_io.is_open(Out_file) thentext_io.get_line(Out_file,L);text_io.get_line(Out_file,L1);text_io.get_line(Out_file,L2); Else Null; End if;End; ---清除全局变量 erase('global.var_name');---隐藏'WINDOW','VIEW','MENU' HIDE_WINDOW|VIEW|MENU(WINDOW|VIEW|MENU_name);
--- 增加参数add_parameterDeclare pl_id ParamList; BEGIN pl_id:=Get_Parameter_List('tempdata'); IF NOT Id_Null(pl_id) THEN Destroy_Parameter_List(pl_id); END IF; pl_id:=Create_Parameter_List('tempdata'); Add_Parameter(pl_id,'EMP_QUERY',DATA_PARAMETER,'EMP_RECS'); Run_Product(REPORTS,'empreport',SYNCHRONOUS,RUNTIME, FILESYSTEM,pl_id,NULL); END; ---DECLARE lArgs OLE2.LIST_TYPE;BEGIN word.hApp:=OLE2.CREATE_OBJ('Word.Basic'); lArgs:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(lArgs,:ole.word_doc); OLE2.INVOKE(Word.hApp,'fileopen',lArgs); OLE2.DESTROY_ARGLIST(lArgs);END;***********************删除重复记录************** Delete from emp e where e.rowid > (select min(f.rowid) from emp f 4 where f.empno=e.empno);
------谁正在访问数据库?Select c.sid, c.serial#,c.username,a.object_id,b.object_name,c.program,c.status,d.name,c.osuser from v$Locked_object a, All_objects b, v$session c, audit_actions dwhere a.object_id=b.object_id and a.session_id =c.sid(+) and c.command=d.action;alter system kill session '&1,&2'; Select a.sid,a.serial#,a.username,a.status,a.program,b.name,a.osuserfrom v$session a,audit_actions b where a.command=b.actionAnd username='&1';------谁被锁住?Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.namefrom v$session a,audit_actions b where a.command=b.actionAND LOCKWAIT IS NOT NULL;------谁在锁表?Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.namefrom v$session a,audit_actions b where a.command=b.actionAND STATUS='ACTIVE';Select sid, serial#, object_name, row_wait_block#, row_wait_row#, row_wait_file# from all_objects, v$session where row_wait_obj#=object_id and type='USER' and lockwait is not null ;Select sl.username, sl.sid, sl.serial# from v_$lock l1, v$session s1 where exists (select * from v_$lock l2, v$session s2 where l2.sid=s2.sid and l2.id1=l1 and s2.lockwait=l2.kaddr and request=0 and l1.sid=s1.sid) ;select count(*) from v$session;select count(*) from sys.v_$process;select count(*) from sys.v_$transaction; ZYP_35.98------查看哪些包要固定COLUMN OWNER FORMAT A10Select owner, name, type, source_size+code_size+parsed_size+error_size BYPES from dba_object_size where type='PACKAGE BODY' ORDER BY 4 DESC ;------查看一个用户拥有哪些表空间的实体信息:Select tablespace_name, owner, segment_name,segment_type from dba_segments where owner-'SyS' and segment_type_-'ROLLBACK' order by tablespace_name, owner, segment_name ;break on owner on segment_nameCOLUMN segment_name FORMAT A15cOLUMN tablespace_name FORMAT A15COLUMN file_name FORMAT A20SELECT A.owner, a.segment_name, b.tablespace_name, b.file_name, sum(a.bytes) bytes from dba_extents a, dba_data_files b where a.file_id-b.file_id group by a.owner, a.segment_name, b.tablespace_name, b.file_name ;------看内存缓冲区使用效率的指数是命中率HITS: Hits=Logical_reads/(logical_reads+physical_reads)其中：logical_reads=db_block_gets+consistent_readsselect cur.value db, con.value con, phy.value phy, (cur.value+con.value)/cur.value+con.value+phy.value)*100 HITS from v$sysstat cur, v$sysstat con, v$sysstat phy where CUR.NAME='db block gets' AND CON.NAME='consistent gets' AND PHY.NAME='physical reads' ;------如何检测ROLLBACK SEGMENT竞争?select class, count from v$waitstat where class in ('system undo header', 'system undo block', 'undo header', 'undo block') ;select sum(value) from v$sysstat where name in ('db block gets', 'consistents gets') ; 若count/sum(value)大于1%，则应考虑增加ROLLBACK SEGMENT------查看有事务在哪几个回退段中：COLUMN u FORMAT A15COLUMN s FORMAT A15COLUMN s FORMAT A80select osuser o, username u, segment_name s, sa.sql_text from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa where s.taddr=t.addr and t.sidusn=r.segmant_id(+) and s.sql_address=sa.address(+) ;
---------------------.3
DBMS_SQL封装过程中主要函数
1、OPEN_CURSOR：返回新游标的ID值
2、PARSE：解析要执行的语句
3、BIND_VARIABLE：将给定的数量与特定的变量相连接
4、DEFINE_COLOUMN：定义字段变量，其值对应于指定游标中某个位置元素的值
（仅用于SELECT语句）
5、EXECUTE：执行指定的游标
6、EXECUTE_AND_FETCH：执行指定的游标并取记录
7、FETCH_ROWS：从指定的游标中取出记录
8、COLUMN_VALUE：返回游标中指定位置的元素
9、IS_OPEN：当指定的游标状态为OPEN时返回真值
10、CLOSE_CURSOR：关闭指定的游标并释放内存
11、LAST_ERROR_POSITION：返回出错SQL语句的字节偏移量
12、LAST_ROW_ID：返回最后一条记录的ROWID
13、LAST_SQL_FUNCTION_CODE：返回语句的SQL FUNCTION CODE
例：
CREATE OR REPLACE
procedure dml_sql (the_rq varchar2) as
The_c1 Integer;
The_result Integer;--dml_sql_result
M_jls number;
The_xh varchar2(2);
Begin
The_xh:=lpad(ltrim(the_rq),2,'0');
The_C1 :=Dbms_sql.open_cursor;
Dbms_sql.parse(the_C1,'select count(*) from user_indexes
where table_name =''ZDCHB'''||the_xh,dbms_sql.v7);
Dbms_sql.define_column(the_c1,1,M_jls);
The_result:=Dbms_sql.execute(The_c1);
If dbms_sql.fetch_rows(cursor1) > 0 then
Dbms_sql.column_value(cursor1,1,M_jls);
End if;
Dbms_sql.close_cursor(The_C1);
End;
-------------1. 加密程序数据库触发子scjmmm----------------------
REM 对GHXXB制立数据库触发子(当INSERT OR UPDATE GHXXB时触发)
drop trigger scjmmm;
create or replace trigger scjmmm
before insert or update of mm On ghxxb For each Row
Begin
:new.mm:=ENCRYPT(:new.mm,:NEW.GH,TO_CHAR(SYSDATE,'SS'));
End;
/
-------------2. 密码的加密程序ENCRYPT----------------------
Create or Replace
Function ENCRYPT (Inpass In Varchar2,IN_GH In Varchar2,IN_SS In Varchar2)
Return Varchar2 Is
bcs varchar2(20);
bcs1 number;
cs number;
jg number;
m_gh VARCHAR2(4);
m_mm VARCHAR2(20);
Begin
m_gh:=IN_GH;
m_mm:=INPASS;
cs:=TO_NUMBER(IN_SS);
If cs<=1 then cs:=77 ;end if;
bcs:=substr(to_char(ascii(substr(m_gh,1,1))),1,2);
If bcs<'1' then bcs:='7' ;end if;
m_gh:=substr(m_gh,2);
Loop EXIT WHEN nvl(length(m_gh),0)=0 ;
bcs:=bcs||substr(to_char(ascii(substr(m_gh,1,1))),-1,1);
m_gh:=substr(m_gh,2);
End loop;
Loop EXIT WHEN nvl(length(m_mm),0)=0 ;
bcs:=bcs||substr(to_char(ascii(substr(m_mm,1,1))),-1,1);
m_mm:=substr(m_mm,2);
End loop;
bcs1:=to_number(bcs);
jg:=cs*bcs1;
Loop EXIT WHEN length(to_char(jg))>13;
jg:=jg*cs ;
End loop;
RETURN(IN_SS||substr(to_char(jg),1,14));
End;
/
grant execute on ENCRYPT to public;
一、进入oracle下# su - oracleas4101> lsnrctl startas4101> svrmgrlsvrmgrl> connect internal;
svrmgrl> shutdown immediate;
svrmgrl> startup
二、查看有哪些表空间svrmgrl> SELECT * FROM DBA_TABLESPACES;
SYSTEM RBS TEMP TOOLS USERS
三、将USERS表空间DROP
svrmgrl> ALTER TABLESPACE USERS OFFLINE;
svrmgrl> DROP TABLESPACE USERS;
四、查看表空间的空余大小
svrmgrl> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;TABLESPACE_NAME MB------------------------------ ---------DD_DATA 1136.3672DD_IDX 787.18164JX_DATA 827.94531JX_IDX 503.16016RBS 371.9668SYSTEM 457.81445TEMP 1499.9961TOOLS 36.462891五、查看数据文件放置的路径svrmgrl> SELECT TABLESPACE_NAME,BYTES/1024/1024 MB,FILE_NAME FROM DBA_DATA_FILES;TABLESPACE_NAME MB FILE_NAME------------------------------ --------- ---------------SYSTEM 500 /dev/rdrd/drd4RBS 500 /dev/rdrd/drd14RBS 1000 /dev/rdrd/drd15RBS 500 /dev/rdrd/drd32TOOLS 50 /dev/rdrd/drd5TEMP 1000 /dev/rdrd/drd22TEMP 500 /dev/rdrd/drd23JX_DATA 500 /dev/rdrd/drd33六、对应SYSTEM表空间有一个回退段，为SYSTEM，另有一些回退段是属于RBS的， 先将RBS下的回退段都OFFLINE，并DROP，然后将RBS表空间DROP并重新创建， 最后，创建回退段。回退段4个，每个大小为RBS/4，这个值可以当作OPTIMAL值， 即等于INITIAL+NEXT*MAXEXTENTSsvrmgrl> ALTER ROLLBACK SEGMENT R01 OFFLINE;svrmgrl> DROP ROLLBACK SEGMENT R01;svrmgrl> alter tablespace rbs offline;svrmgrl> drop tablespace rbs;svrmgrl> Create TABLESPACE "RBS" DATAFILE '/dev/rdrd/rbs01.ora' SIZE 500M, '/dev/rdrd/rbs02.ora' SIZE 500M;svrmgrl> CREATE ROLLBACK SEGMENT "R01" TABLESPACE "RBS" STORAGE ( INITIAL 200M NEXT 2M OPTIMAL 250M MINEXTENTS 2 MAXEXTENTS 25);七、查看回退段及表空间的状态,若为ONLINE，即结束，为OFFLINE，要ONLINEsvrmgrl> select SEGMENT_NAME,TABLESPACE_NAME,status from DBA_ROLLBACK_SEGS;svrmgrl> ALTER ROLLBACK SEGMENT R01 ONLINE;八、临时表空间TEMP，先DROP，再重建。svrmgrl> alter tablespace temp offline;svrmgrl> drop tablespace temp;svrmgrl> CREATE TABLESPACE temp DATAFILE '/dev/rdrd/drd22' SIZE 1000M storage (initial 300m next 20m minextens 2 maxextents 35 pctincrease 0);九、工具表空间TOOLS大小为50M足够用，系统表空间SYSTEM为100M足够用。十、创建数据表空间： DD_DATA、DD_IDX、JX_DATA、JX_IDX、SF_DATA、SF_IDX、JF_DATA、JF_IDXsvrmgrl> CREATE TABLESPACE dd_data DATAFILE '/dev/rdrd/drd9' SIZE 1000M, '/dev/rdrd/drd10' SIZE 1000M, '/dev/rdrd/drd26' SIZE 1000M, '/dev/rdrd/drd35' SIZE 1000M, '/dev/rdrd/drd42' SIZE 500M;十一、创建用户svrmgrl> CREATE USER ddbh IDENTIFIED BY ddbh DEFAULT TABLESPACE dd_data TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON dd_data QUOTA UNLIMITED ON dd_idx QUOTA UNLIMITED ON rbs QUOTA UNLIMITED ON temp;十二、用户权限svrmgrl> grant connect,resources,imp_full_database,exp_full_database, create public synonym,drop public synonym to ddbh; 若要查看V$SESSION，KILL SESSION, DROP USER,CREATE USER等,则svrmgrl> grant select on v_$session to public;svrmgrl> grant alter system,drop user,create user to "*******"
