好的,表结构如下
CREATE TABLE "ZYM"."RS_EMP"("EMP_ID" NUMBER(10),
"EMP_NAME" VARCHAR2(30),
"AGE" NUMBER(3),
"TEL" VARCHAR2(20),
"EMAIL" VARCHAR2(40)) ;
CREATE TABLE "ZYM"."T_USERS_NAME"("USER_NAME" VARCHAR2(20),
"PASSWORD" VARCHAR2(10),
"EMP_ID" NUMBER(10)) ;
CREATE TABLE "ZYM"."T_USER_OPS"("INTERNAL_ID" NUMBER(10),
"USER_NAME" VARCHAR2(20),
"LOGON_TIME" DATE,
"LOGOUT_TIME" DATE,
"SESSION_ID" NUMBER(10))
CREATE TABLE "ZYM"."T_USER_OPS_DETAIL"("INTERNAL_ID" NUMBER(10),
"OP_INTERNAL_ID" NUMBER(10),
"ACTION" VARCHAR2(16),
"ACTION_TAB" VARCHAR2(30),
"ACTION_FIELD" VARCHAR2(40))
用到的序列如下CREATE SEQUENCE "ZYM"."T_USER_OPS_DETAIL_SEQ"
INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E27
MINVALUE 1
NOCYCLE CACHE 20 NOORDER ;CREATE SEQUENCE "ZYM"."T_USER_OPS_SEQ"
INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E27
MINVALUE 1
NOCYCLE CACHE 20 NOORDER;
CREATE TABLE "ZYM"."RS_EMP"("EMP_ID" NUMBER(10),
"EMP_NAME" VARCHAR2(30),
"AGE" NUMBER(3),
"TEL" VARCHAR2(20),
"EMAIL" VARCHAR2(40)) ;
CREATE TABLE "ZYM"."T_USERS_NAME"("USER_NAME" VARCHAR2(20),
"PASSWORD" VARCHAR2(10),
"EMP_ID" NUMBER(10)) ;
CREATE TABLE "ZYM"."T_USER_OPS"("INTERNAL_ID" NUMBER(10),
"USER_NAME" VARCHAR2(20),
"LOGON_TIME" DATE,
"LOGOUT_TIME" DATE,
"SESSION_ID" NUMBER(10))
CREATE TABLE "ZYM"."T_USER_OPS_DETAIL"("INTERNAL_ID" NUMBER(10),
"OP_INTERNAL_ID" NUMBER(10),
"ACTION" VARCHAR2(16),
"ACTION_TAB" VARCHAR2(30),
"ACTION_FIELD" VARCHAR2(40))
用到的序列如下CREATE SEQUENCE "ZYM"."T_USER_OPS_DETAIL_SEQ"
INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E27
MINVALUE 1
NOCYCLE CACHE 20 NOORDER ;CREATE SEQUENCE "ZYM"."T_USER_OPS_SEQ"
INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E27
MINVALUE 1
NOCYCLE CACHE 20 NOORDER;
而是你的数据窗口的update属性中的
key modification设置为 use delete then insert,
你改成 use update即可.
呵呵.
SQL> set serveroutput onSQL> select * from t_users_name;
USER_NAME PASSWORD EMP_ID
-------------------- ---------- ----------
QSUN QSUN 100
--未执行logon过程之前,包中的变量为初始值
SQL> begin
2 dbms_output.put_line(xymis_package.g_ops_id);
3 dbms_output.put_line(xymis_package.g_log_username);
4 end;
5 /
0PL/SQL procedure successfully completed.
--执行logon过程
SQL> declare
2 v_result integer;
3 v_pass varchar2(100);
4 v_id number(10);
5 begin
6 user_logon('QSUN','QSUN',v_result, v_pass,v_id);
7 end;
8 /
PL/SQL procedure successfully completed.
--执行logon过程后,看到包中的变量被赋值了
SQL> begin
2 dbms_output.put_line(xymis_package.g_ops_id);
3 dbms_output.put_line(xymis_package.g_log_username);
4 end;
5 /
2
QSUN
PL/SQL procedure successfully completed.--检察t_user_ops_detail表,没有纪录
SQL> select * from t_user_ops_detail;
no rows selected
--在rs_emp表中插入纪录
SQL> insert into rs_emp
2 values (100,'QSUN',32,'123','123');
1 row created.
--在t_user_ops_detail表中出现了纪录,而且OP_INTERNAL_ID为2,正确。
SQL> select * from t_user_ops_detail;
INTERNAL_ID OP_INTERNAL_ID ACTION ACTION_TAB
----------- -------------- ---------------- ------------------------------
ACTION_FIELD
----------------------------------------
3 2 INSERT rs_emp你的代码中有点问题,我在编译的时候改正过来了。比如:
op_seq_id :=xymis_package.ops_id;
这句,包中没有ops_id这个变量,应该是g_ops_id。
你的两个触发器也没有问题,至于你说的情况,可能是没有提交的原因,也可能是权限的问题,要确定修改rs_emp数据的用户对包:xymis_package 有执行的权限,当然如果是同一个用户就没有这种现象了。
另外说明一下,不同连接中取到的包中变量的值是独立的,所以你要确定对包中变量已经赋值。