java

触发器

包

 

 

 

你可以编写用户定义的函数（用pl/sql,java,c）来提供在sql中或sql内置函数中不可用的功能

有时，我们会发现有些功能通过PL/SQL完成会很麻烦，而通过C/C++语言编程则会容易很多。因此，oracle提供了在PL/SQL程序里直接调用外部函数（包括C函数或Java方法）的功能，从而扩展了PL/SQL的程序功能。

 

 

在Oracle的存储过程和函数中，其实IS和AS是同义词，没有什么区别。

还有在自定义类型（TPYE）和包（PACKAGE）时，使用IS和AS也并没有什么区别。

但是在创建视图（VIEW）时，只能使用AS而不能使用IS。

在声明游标（CURSOR）时，只能使用IS而不能使用AS。

 

 

用java的方法

例子：一个简单的需求，用Java代码实现求圆的面积，圆周率PI为3.14，输入的参数为圆的半径R，输出圆的面积S，要求可以在oracle中用PL/SQL代码调用该Java类实现求圆的面积的功能。Step 1：编写Java代码，PL/SQL Developer本身虽然支持编写Java代码，但是毕竟不是专业的，对Java的工具提供的不是特别好，我喜欢用MyEclipse开发好以后复制过来，下面是我在MyEclipse中开发好的代码。// 圆工具类，计算圆的面积public class CircleTools {  // 定义常量PI  public static final double PI = 3.14;   // 计算面积  public static double calcSquare(double r) {    return PI * r * r;  }}注意在写Java代码的时候，为了能够直接能够在oracle中被调用，所以这里在需要调用的方法前要加上public和static。Step 2：写好Java代码，下面就是要将Java导入到oracle数据库中，导入的方法在前文提到过可以用loadJava或者直接写。如果要用loadJava导入，先把上面的代码文件保存为CircleTools.java，然后在cmd命令行中进入该目录下，然后执行如下命令：loadjava是安装数据库自带的工具[oracle@oracle1 ~]$ which loadjava/u01/app/oracle/product/11.2.0/dbhome_1/bin/loadjavaloadjava –u username/userpassword –v -resolve CircleTools.java执行上面的命令就OK了，下面提供另一种方法，就是直接在PL/SQL中写，写法如下：CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED circletools AS// 圆工具类，计算圆的面积public class CircleTools {  // 定义常量PI  public static final double PI = 3.14;  // 计算面积  public static double calcSquare(double r) {    return PI * r * r;  }}查看这个数据字典发现已经进入数据库中了select * from user_objectswhere length(object_type) >5;Step 3：编写存储过程（procedure）或者函数（function）来封装，以实现以后的调用，一般来说，如果一个Java方法没有返回值，那么就封装成存储过程，如果有返回值，就封装成函数，这里我只给出一个封装成函数的例子：CREATE OR REPLACE FUNCTION calc_square(r IN NUMBER) RETURN NUMBER AS  LANGUAGE JAVA NAME 'CircleTools.calcSquare(double) return double';Step 4：测试函数，2是半径select calc_square(2) from dual;Step 5：如果不想用那个Java类了，可以用dropjava命令删除掉系统中的Java，这个跟loadjava很相似，dropjava -user username/userpassword@db -v javasourcename注意上面删除的是Java的source name，不是class name，即对象的OBJECT_TYPE是JAVA SOURCE，不是JAVA CLASS。

 

触发器

===============================

创建触发器

create or replace trigger test.login_log  

  after create or drop on schema

 BEGIN

 -- 以下使用的是事件属性

 DBMS_OUTPUT.PUT_LINE('I believe you have created a ' ||

 ORA_DICT_OBJ_TYPE || ' called ' ||

 ORA_DICT_OBJ_NAME);

 END;

测试触发器

set serveroutput on

CREATE TABLE "TEST"."T3"

  (

    "PRODUCT_ID"   NUMBER,

    "PRODUCT_NAME" VARCHAR2(80 BYTE),

    "MONTH"        NUMBER

  );

drop table t3 purge;

===========================

 

记录登录数据库历史记录，使用触发器

建表可以在普通用户之下，但创建触发器需要在sys用户下

create table login_history  

(  

  username   varchar2(60),  --用户名  

  machine    varchar2(60),  --机器名  

  login_time date,          --登录时间  

  ip         varchar2(50)   --ip地址  

);

create or replace trigger login_log  

  after logon on database  

begin  

  insert into test.login_history

     select username, machine, sysdate, sys_context('userenv', 'ip_address')  

      from v$session  

     where audsid = userenv('sessionid');  

  commit;  

end;  

CREATE OR REPLACE TRIGGER TRIGGER1

BEFORE DELETE OR INSERT OR UPDATE OF IP,LOGIN_TIME,MACHINE,USERNAME ON LOGIN_HISTORY

BEGIN

  NULL;

END;

可用的DDL事件DDL事件 			触发时机ALTER 				对数据库中的任何一个对象使用SQL的ALTER命令时触发ANALYZE 			对数据库中的任何一个对象使用SQL的ANALYZE命令时触发ASSOCIATE STATISTICS 统计数据关联到数据库对象时触发AUDIT 				通过SQL的AUDIT命令打开审计时触发COMMENT 			对数据库对象做注释时触发CREATE 				通过SQL的CREATE命令创建数据库对象时触发DDL 				列表中所用的事件都会触发DISASSOCIATE STATISTICS 去掉统计数据和数据库对象的关联时触发DROP 				通过SQL的DROP命令删除数据库对象时触发GRANT 				通过SQL的GRANT命令赋权时触发NOAUDIT 			通过SQL的NOAUDIT关闭审计时触发RENAME 				通过SQL的RENAME命令对对象重命名时触发REVOKE 				通过SQL的REVOKE语句撤销授权时触发TRUNCATE 			通过SQL的TRUNCATE语句截断表时触发

 

可用属性Oracle 提供了一系列的函数用来提供关于什么触发了DDL触发器以及触发器的状态灯信息。上面那个触发器的例子就使用了属性。DDL触发器事件以及属性函数函数名 							返回值ORA_CLIENT_IP_ADDRESS 			客户端IP地址ORA_DATABASE_NAME 				数据库名称ORA_DES_ENCRYPTED_PASSWORD 		当前用户的DES算法加密后的密码ORA_DICT_OBJ_NAME 				触发DDL的数据库对象名称ORA_DICT_OBJ_NAME_LIST 			受影响的对象数量和名称列表ORA_DICT_OBJ_OWNER 				触发DDL的数据库对象属主ORA_DICT_OBJ_OWNER_LIST 		受影响的对象数量和名称列表ORA_DICT_OBJ_TYPE 				触发DDL的数据库对象类型ORA_GRANTEE 					被授权人数量ORA_INSTANCE_NUM 				数据库实例数量ORA_IS_ALTER_COLUMN 			如果操作的参数column_name指定的列，返回true,否则falseORA_IS_CREATING_NESTED_TABLE 	如果正在创建一个嵌套表则返回true,否则falseORA_IS_DROP_COLUMN 				如果删除的参数column_name指定的列，返回true,否则falseORA_LOGIN_USER 					触发器所在的用户名ORA_PARTITION_POS 				SQL命令中可以正确添加分区子句位置ORA_PRIVILEGE_LIST 				授予或者回收的权限的数量。ORA_REVOKEE 					被回收者的数量ORA_SQL_TXT 					触发了触发器的SQL语句的行数。ORA_SYSEVENT 					导致DDL触发器被触发的时间ORA_WITH_GRANT_OPTION 			如果授权带有grant选项，返回true。否则false更多属性函数请参考官方文档PL/SQL Language Reference -> Triggers and Oracle Database Data Transfer Utilities

 

创建触发器 CREATE OR REPLACE TRIGGER test.no_dropBEFORE DDL ON DATABASEBEGINIF ORA_SYSEVENT = 'CREATE'THENDBMS_OUTPUT.PUT_LINE('Warning !!! You have created a '||ORA_DICT_OBJ_TYPE ||' called '||ORA_DICT_OBJ_NAME|| '; UserName(creater):'||ORA_DICT_OBJ_OWNER||'; IP:'||ORA_CLIENT_IP_ADDRESS||'; event:'||ORA_SYSEVENT); ELSIF ORA_SYSEVENT = 'DROP'THENRAISE_APPLICATION_ERROR (-20000,'Cannot create the ' || ORA_DICT_OBJ_TYPE ||' named ' || ORA_DICT_OBJ_NAME ||' as requested by ' || ORA_DICT_OBJ_OWNER);END IF;END; 测试触发器 set serveroutput on CREATE TABLE "TEST"."T4"   (     "PRODUCT_ID"   NUMBER,     "PRODUCT_NAME" VARCHAR2(80 BYTE),     "MONTH"        NUMBER   ); drop table t4 purge;

 

 

package包的使用

SET SERVEROUTPUT ON SIZE 40000

DECLARE

req UTL_HTTP.REQ;

resp UTL_HTTP.RESP;

value VARCHAR2(1024);

BEGIN

--UTL_HTTP.SET_PROXY('proxy.my-company.com', 'corp.my-company.com');

req := UTL_HTTP.BEGIN_REQUEST('http://www.ka1che.com');

UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');

resp := UTL_HTTP.GET_RESPONSE(req);

LOOP

UTL_HTTP.READ_LINE(resp, value, TRUE);

DBMS_OUTPUT.PUT_LINE(value);

END LOOP;

UTL_HTTP.END_RESPONSE(resp);

EXCEPTION

WHEN UTL_HTTP.END_OF_BODY THEN

UTL_HTTP.END_RESPONSE(resp);

END;

 

 

==================================================

http://blog.csdn.net/ls_man/article/details/11981967

创建过程来自动杀死相关session

GRANT create view to PERSONAL;

grant alter system to personal;

create view mysession as  

select SID,SERIAL# from v$session

where schemaname in ('TEST1','TEST2');

create user test1 identified by 123456;

create user test2 identified by 123456;

grant connect,resource to test1,test2;

create or replace procedure kill_session is  

  v_sid    varchar2(30);  

  v_serial varchar2(30);  

  v_sql    varchar2(1000);  

  TYPE DyData IS REF CURSOR;  

  rows DyData;  

begin  

 

  v_sql := 'select * from mysession ';  

  OPEN rows FOR v_sql;  

  LOOP  

    FETCH rows  

      into v_sid, v_serial;  

    

    v_sql := 'alter system kill session ''' || v_sid || ',' || v_serial || '''';  

    execute immediate v_sql;  

    --dbms_output.put_line(v_userid || '__' || v_user || '__' || v_pwd);  

    

    EXIT WHEN rows%NOTFOUND;  

  END LOOP;  

 

end kill_session;  

======================================

Oracle wrap 和 unwrap（ 加密与解密） 说明

http://blog.chinaunix.net/uid-7589639-id-3767585.html

wrap 的使用步骤如下：

（1）将我们要加密的sql 语句保存到一个sql文本里。

（2）用wrap 进行处理，指定输入的sql，即我们第一步的问题，然后指定输出的路径和文件名，默认扩展名是plb。

（3）执行我们第二步进过wrap 处理的sql，即plb文件，创建我们的对象.

命令行界面

cat dave.sql

wrap iname=dave.sql

cat dave.plb

sqlplus界面

@dave.plb

select F_DAVE(4) from dual;

select text from dba_source where name='F_DAVE';

wrap的目的是为了加密，所以Oracle并没有提供unwrap 的方法。 itpub上的一些牛人研究了一下这个问题，写了一些unwrap的代码。 具体讨论的过程，参考itpub的2个帖子：

阿里巴巴的张端弄了一个界面的Unwrap软件，下载地址：