|
ERS WHERE TABLE_OWNER='MYORCL' AND TABLE_NAME =UPPER('PERSON');
select * from user_triggers where table_owner = 'xxx' and table_name = upper('table_name');
//查看这个用户的所有触发器
select * from dba_triggers where owner=用户名；
//查看这个用户中所有的外键约束，table_name显示了建立在哪个表上
select * from dba_constraints T where owner=用户名 AND T.CONSTRAINT_TYPE='F';
//清空?存
10g以上
alter system flush buffer_cache;
9i
ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
// ?中文???境更改?英文
ALTER SESSION SET NLS_LANGUAGE='AMERICAN'
//
select * from nls_database_parameters;
alter database NLS_CHARACTERSET = ZHS16GBK;
//
??表 NLS_LANG
SIMPLIFIED CHINESE_CHINA.ZHS16GBK 中文值
CHINESE_CHINA.ZHS16GBK
AMERICAN_AMERICA.UTF8 英文值
AMERICAN_AMERICA;
AMERICAN_AMERICA.WE8ISO8859P1
SIMPLIFIED AMERICAN_AMERICA.UTF8
//提示?英文，???中文
AMERICAN_AMERICA.ZHS16GBK
//CT TABLE
CREATE TABLE CT(
ID NUMBER PRIMARY KEY NOT NULL,
NAME VARCHAR(10) NOT NULL,
AGE NUMBER NOT NULL,
DEP NUMBER NOT NULL);
// PROCEDURE
CREATE PROCEDURE PROL(ID NUMBER)
AS
V NUMBER;
BEGIN
INSERT INTO CT
VALUES(V,'ZHANGSAN',1,1);
COMMIT;
END;
/
//execute?行
//EXECUTE PROL(2)
//??行
DECLARE
BEGIN
PROL(2);
END
//常量定?constant
ID CONSTANT NUMBER :=30;
//??存??程
CREATE OR REPLACE PROCEDURE PRO_PERSON(PARAMENT1 VARCHAR2,PARAMENT2 NUMBER,PARAMENT3 DATE,PARAMENT4 FLOAT) AS
BEGIN
INSERT INTO PERSON(NAME,AGE,BRITHDAY,MONEY) VALUES(PARAMENT1,PARAMENT2,PARAMENT3,PARAMENT4);
END;
//返回一???
CREATE OR REPLACE PROCEDURE PRO_PERSON_RETURE(PARAMENT1 VARCHAR2,PARAMENT2 OUT NUMBER) AS
BEGIN
SELECT AGE INTO PARAMENT2 FROM PERSON WHERE NAME=PARAMENT1;
END;
//插入 时取得当前ID
//第一种
CREATE OR REPLACE PROCEDURE PRO_LAST_ID_PERSON(
PARA5 OUT NUMBER,PARA1 VARCHAR2,PARA2 NUMBER,PARA3 DATE,PARA4 FLOAT) AS
BEGIN
INSERT INTO PERSON(ID,NAME,AGE,BRITHDAY,MONEY) VALUES(
SEQ_ID_PERSON.NEXTVAL,PARA1,PARA2,PARA3,PARA4);
SELECT SEQ_ID_PERSON.CURRVAL INTO PARA5 FROM DUAL;
END;
/
//第二种
CREATE OR REPLACE PROCEDURE PRO_LAST_ID_PERSON1(
PARA5 OUT NUMBER,PARA1 VARCHAR2,PARA2 NUMBER,PARA3 DATE,PARA4 FLOAT) AS
BEGIN
INSERT INTO PERSON(NAME,AGE,BRITHDAY,MONEY) VALUES(PARA1,PARA2,PARA3,PARA4);
SELECT SEQ_ID_PERSON.CURRVAL INTO PARA5 FROM DUAL;
END;
/
//存??程返回列表
//造包
CREATE OR REPLACE PACKAGE PERSON_PACKAGE AS
TYPE PERSON_CURSOR IS REF CURSOR;
PROCEDURE PRO_PERSON_GET(C_REF OUT PERSON_CURSOR);
END;
/
//存??程
CREATE OR REPLACE PACKAGE BODY PERSON_PACKAGE AS
PROCEDURE PRO_PERSON_GET(C_REF OUT PERSON_CURSOR) IS
BEGIN
OPEN C_REF FOR SELECT * FROM PERSON;
END PRO_PERSON_GET;
END PERSON_PACKAGE;
/
//查看触发器
set long 50000;
set heading off;
set pagesize 2000;
select
'create or replace trigger "' ||
trigger_name || '"' || chr(10)||
decode( substr( trigger_type, 1, 1 ),
'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||
chr(10) ||
triggering_event || chr(10) ||
'ON "' || table_owner || '"."' ||
table_name || '"' || chr(10) ||
decode( instr( trigger_type, 'EACH ROW' ), 0, null,
'FOR EACH ROW' ) || chr(10) ,
trigger_body
from user_triggers;
// 查看函数和过程的状态
select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER';
// 查看函数和过程的源代码
select text from all_source where owner=user and name=upper('名?') |