–在system表空间创建用户 –其中,jinanuser是用户名 jinanuser是密码 CREATE USER jinanuser IDENTIFIED BY jinanuser; –将DBA角色赋给jinanuser这个用户 GRANT DBA TO jinanuser; –撤销jinanuser用户的DBA角色 REVOKE DBA FROM jinanuser; –在自己创建
–在system表空间创建用户
–其中,jinanuser是用户名 jinanuser是密码
CREATE USER jinanuser IDENTIFIED BY jinanuser;
–将DBA角色赋给jinanuser这个用户
GRANT DBA TO jinanuser;
–撤销jinanuser用户的DBA角色
REVOKE DBA FROM jinanuser;
–在自己创建的用户下创建jinantest
CREATE USER jinantest IDENTIFIED BY jinantest;
–给jinantest权限CONNECT
GRANT CONNECT TO jinantest;
REVOKE CONNECT FROM jinantest;
1、关于主键：在建表时指定primary key字句即可：
create table test(
id number(6) primary key,
name varchar2(30)
);
如果是对于已经建好的表，想增加主键约束，则类似语法：
alter table test add constraint pk_id primary key(id);
–给jinantest权限resource
GRANT RESOURCE TO jinantest;
REVOKE RESOURCE FROM jinantest;
–使用jinanuser创建创建tb_user表
CREATE TABLE tb_user(
user_id INT PRIMARY KEY,
user_name VARCHAR2(50) NOT NULL,
user_desc VARCHAR2(2000)
)
–删除表
DROP TABLE tb_user;
SELECT * FROM tb_user;
–添加列
ALTER TABLE tb_user ADD user_pwd VARCHAR2(50);
–修改列
ALTER TABLE tb_user MODIFY user_pwd LONG;
SELECT * FROM tb_user;
–删除列
ALTER TABLE tb_user DROP COLUMN user_desc;
–用于产生主键数值的方法,序列:sequence
CREATE SEQUENCE seq_test
START WITH 1
INCREMENT BY 1;
–选取序列的当前值 seq_xxx.currval
–DUAL 虚表
SELECT seq_test.CURRVAL FROM dual;
–sysdate表示Oracle数据库当前系统时间
SELECT SYSDATE FROM dual;
–选取序列的下一个值:seq_xxx.nextval
SELECT seq_test.NEXTVAL FROM dual;
INSERT INTO tb_user VALUES(seq_test.nextval,’张三’,’123456′);
–查询数据
SELECT * FROM tb_user;
SELECT seq_test.NEXTVAL FROM dual;
INSERT INTO tb_user VALUES(seq_test.nextval,’李四’,’654321′);
SELECT * FROM tb_user;
–插入指定的字段
INSERT INTO tb_user(user_id,user_name) VALUES (seq_test.nextval,’王五’);
INSERT INTO tb_user(user_name,user_id) VALUES (‘小明’,seq_test.NEXTVAL);
–删除表的主键
ALTER TABLE tb_user DROP PRIMARY KEY;
–给表添加主键
ALTER TABLE tb_user ADD CONSTRAINT pk_tb_user PRIMARY KEY (user_name);
ALTER TABLE tb_user ADD PRIMARY KEY (user_name);
—
SELECT * FROM tb_user;
–添加唯一性约束
ALTER TABLE tb_user ADD CONSTRAINT uk_tb_user UNIQUE (user_id);
–非空约束
ALTER TABLE tb_user MODIFY user_id NOT NULL;
–添加列
ALTER TABLE tb_user ADD user_grade VARCHAR2(10);
–查询
SELECT * FROM tb_user;
–填充user_grade字段
UPDATE tb_user SET user_grade=’sk’;
–添加非空约束:user_grade
ALTER TABLE tb_user MODIFY user_grade NOT NULL;
–创建表
CREATE TABLE tb_user1 (
user_Id INT PRIMARY KEY,
user_name VARCHAR2(20) DEFAULT(‘小王’)
)
–创建序列
CREATE SEQUENCE seq_test1
START WITH 1
INCREMENT BY 1;
–添加数据
INSERT INTO tb_user1(user_id) VALUES(seq_test1.nextval);
–DUAL 虚表
SELECT seq_test1.CURRVAL FROM dual;
SELECT * FROM tb_user1;
–外键约束
–创建经理表
CREATE TABLE tb_manager(
mgr_id INT PRIMARY KEY,–经理表的主键
mgr_name varchar2(10) NOT NULL–经理姓名
)
CREATE TABLE tb_employee(
epe_id INT PRIMARY KEY, –雇员的主键
epe_name varchar2(10) NOT NULL, –雇员的姓名
mgr_id INT NOT NULL –所属经理的id
)
–外键
ALTER TABLE tb_employee ADD CONSTRAINT fk_epe FOREIGN KEY (mgr_id)
REFERENCES tb_manager (mgr_id);
SELECT * FROM tb_manager;
SELECT * FROM tb_employee;
–向经理表添加记录
INSERT INTO tb_manager values(1,’老兵’);
–向雇员表添加记录
INSERT INTO tb_employee VALUES(seq_test1.nextval,’小兵’,1);
CREATE TABLE tb_employee2(
epe_id INT PRIMARY KEY, –雇员的主键
epe_name varchar2(10) NOT NULL, –雇员的姓名
mgr_id INT –所属经理的id
)
–外键
ALTER TABLE tb_employee2 ADD CONSTRAINT fk_epe2 FOREIGN KEY (mgr_id)
REFERENCES tb_manager (mgr_id);
INSERT INTO tb_employee2(epe_id,epe_name) VALUES(seq_test1.nextval,’小兵’);
COMMIT;
SELECT * FROM tb_employee2;
–外键在添加记录的时候,可以为空
SELECT * FROM tb_user1;
DELETE FROM tb_user1;
–check约束
ALTER TABLE tb_user1
ADD CONSTRAINT ck_tb_user1
CHECK(user_id>10);
INSERT INTO tb_user1 VALUES(11,’小王’);
SELECT * FROM tb_user1;
COMMIT;
–数学函数
–abs取绝对值
SELECT abs(-10) FROM dual;
–ceil 向上取整
SELECT ceil(-3.1) FROM dual;
–floor 向下取整
SELECT floor(-3.1) FROM dual;
–power 幂次方
SELECT power(3,2) FROM dual;
–round四舍五入
SELECT round(2.4) FROM dual;
–sqrt开方
SELECT sqrt(2) FROM dual;
–trunc数据截断
SELECT trunc(15.79,1) FROM dual;
SELECT trunc(15.79,0) FROM dual;
SELECT trunc(15.79,-1) FROM dual;
SELECT trunc(15.79,-2) FROM dual;
–向第一个参数表示要截取的数字,第二个参数,表示从第几位截取
–当第二个参数为正数的时候,表示保留几位小数
–当第二个参数为负数的时候,表示去掉几位整数部分
–相当于一把小刀,向右数几位砍掉,负数表示向左移动几位砍掉,0的时候表示砍掉小数部分
–mod整数取余操作
SELECT mod(10,3) FROM dual;
SELECT mod(10,3) “MOD” FROM dual;
–转换函数
–TOCHAR
SELECT to_char(SYSDATE,’YYYY-MM-DD HH24:MI:SS’) FROM DUAL;
–TO_DATE函数 将字符转换为时间格式数据
SELECT to_DATE(‘2014-12-10 17:21:47′,’YYYY-MM-DD HH24:MI:SS’) FROM DUAL;
–TO_NUMBER
SELECT ‘3’ + ‘1’ FROM dual;
SELECT to_number(‘123456’) – 23456 FROM dual;
–TO_TIMESTAMP
SELECT TO_TIMESTAMP(‘2013-12-2 12:22:32′,’YYYY-MM-DD HH24:MI:SS’) FROM DUAL;
SELECT TO_DATE(‘2013-12-2 12:22:32′,’YYYY-MM-DD HH24:MI:SS’) FROM DUAL;
–TO_TIMESTAMP_TZ
SELECT TO_TIMESTAMP_TZ(‘2013-12-2 12:22:32 8:00′,’YYYY-MM-DD HH24:MI:SS TZH:TZM’) FROM DUAL;
–SYSDATE获取Oracle系统当前时间
SELECT SYSDATE FROM dual;
–extract提取日期中指定单位的数值
SELECT extract (MONTH FROM SYSDATE) FROM dual;
SELECT extract (YEAR FROM SYSDATE) FROM dual;
SELECT extract (DAY FROM SYSDATE) FROM dual;
SELECT extract (HOUR FROM to_timestamp(‘2012-2-12 23:32:21′,’YYYY-MM-DD HH24:MI:SS’)) FROM dual;
SELECT extract (minute FROM to_timestamp(‘2012-2-12 23:32:21′,’YYYY-MM-DD HH24:MI:SS’)) FROM dual;
–Months_between
SELECT Months_between(
to_date(‘2013-3-12 23:32:21′,’YYYY-MM-DD HH24:MI:SS’),
to_date(‘2014-12-12 23:32:21′,’YYYY-MM-DD HH24:MI:SS’)
)FROM dual;
–add_months 添加月份
SELECT add_months(
to_date(‘2013-3-12 23:32:21′,’YYYY-MM-DD HH24:MI:SS’),
10
)FROM dual;
–next_day 下一个星期数
SELECT next_day(
‘2013-3-12′,’YYYY-MM-DD’
)FROM dual;
–round 对日期四舍五入
SELECT round(SYSDATE),SYSDATE FROM dual;
SELECT round(to_date(‘2013-3-12 23:32:21′,’YYYY-MM-DD HH24:MI:SS’)),
to_date(‘2013-3-12 23:32:21′,’YYYY-MM-DD HH24:MI:SS’)
FROM dual;
–last_day 当月的最后一天
SELECT last_day(
SYSDATE
)FROM dual;
–teunc 获取待定时间
SELECT trunc(to_date(‘2013-3-12′,’YYYY-MM-DD’),’day’) FROM dual;
SELECT trunc(to_date(‘2013-3-12′,’YYYY-MM-DD’),’month’) FROM dual;
SELECT trunc(to_date(‘2013-3-12′,’YYYY-MM-DD’),’year’) FROM dual;
–UPPER转化成大写
SELECT UPPER(‘sdsda’) FROM dual;
–LOWER转换成小写
SELECT LOWER(‘SDSDA’) FROM dual;
–INITCAP首字母大写
SELECT INITCAP(‘wqeqe’) FROM dual;
–CONCAT 字符串连接
SELECT concat(‘wqeqe’,’asda’) FROM dual;
–LENGTH获取字符数
SELECT LENGTH(‘wqeqeasda’) FROM dual;
–lpad左填充
SELECT lpad(‘qweq’,5,’fgrty’) FROM dual;
–rpad右填充
SELECT rpad(‘qweq’,5,’fgrty’) FROM dual;
–ltrim去除左空格
SELECT ltrim(‘ qweq’) FROM dual;
–RTRIM去除右空格
SELECT RTRIM(‘qweq ‘) FROM dual;
–INSTR获取查询字符串的索引
SELECT INSTR(‘CORPORATE FLOOR’,’OR’, 3, 2)
“Instring” FROM DUAL;
SELECT INSTR(‘ZXCVBNM’,’M’, 1, 1)
“Instring” FROM DUAL;
–SUBSTR截取字符串
SELECT SUBSTR(‘ABCDEFG’,3,4) “Substring”
FROM DUAL;/*从第三个开始截取一共截取4个*/
–REPLACE替换字符串
SELECT REPLACE(‘JACK and JUE’,’J’,’BL’) “Changes”
FROM DUAL;
/*把J替换成BL*/
–LOOP循环
DECLARE
myindex INT:=0;
BEGIN
LOOP
–输出结果到控制台,字符串拼接使用||,也可使用concat函数
dbms_output.put_line(‘myindex = ‘ || myindex);
— dbms_output.put_line(concat());
myindex := myindex + 1;
IF myindex > 10 THEN
EXIT;
END IF;
END LOOP;
END;
–while循环
DECLARE
myindex INT :=0;
BEGIN
WHILE myindex < 10
LOOP
dbms_output.put_line(concat(‘myindex = ‘,myindex));
myindex :=myindex + 1;
END LOOP;
END;
–自增序列
CREATE SEQUENCE
START WITH 1
INCREMENT BY 1;
–for loop 循环
BEGIN
FOR i IN 0..10
LOOP
dbms_output.put_line(‘index = ‘||i);
END LOOP;
END;
–反转reverse
BEGIN
FOR i IN REVERSE 0..10
LOOP
dbms_output.put_line(‘index = ‘||i);
END LOOP;
END;
–动态sql语句
CREATE TABLE tb_test(
t_id INT PRIMARY KEY,
t_name varchar2(10)
)
DECLARE
mysql VARCHAR2(500);
mydate DATE;
BEGIN
EXECUTE IMMEDIATE ‘select sysdate from dual’ INTO mydate;
dbms_output.put_line(to_char(mydate,’YYYY-MM-DD’));
END;
DECLARE
mysql VARCHAR2(500) : =”;
mytext VARCHAR2(10) := ‘小名的名字’;
BEGIN
EXECUTE IMMEDIATE ‘insert into tb_test values(3,:x)’ USING ‘小明’;
END;
DECLARE
mysql VARCHAR2(500) :=’insert into tb_test(t_id,t_name) values(3,:x)’;
mytext VARCHAR2(10) := ‘小明’;
BEGIN
EXECUTE IMMEDIATE mysql USING mytext;
END;
SELECT * FROM tb_test;
–给tb_test创建一个序列,用来生成主键的值
CREATE SEQUENCE seq_123
START WITH 5
INCREMENT BY 1;
–添加列
ALTER TABLE tb_test ADD t_mony VARCHAR2(50);
DECLARE
mysql VARCHAR2(500) :=’insert into tb_test values(:n,:x,:y)’;
myname VARCHAR2(10) := ‘李四’;
mydesc VARCHAR2(100) :=’这是李四的描述’;
myindex INT;
BEGIN
SELECT seq_123.NEXTVAL INTO myindex FROM dual;
EXECUTE IMMEDIATE mysql USING myindex,myname,mydesc;
END;
–异常的处理
DECLARE
var1 INT :=87;
var2 INT :=0;
BEGIN
var1 := var1/var2;
dbms_output.put_line(‘已经执行了,’);
–异常处理语句
EXCEPTION
–系统定义异常,zero_divide
WHEN zero_divide THEN
dbms_output.put_line(‘不能被0除,异常’);
END;
–自定义异常
BEGIN
raise_application_error(-20001,’我测试用的自定义异常’);
END;
SELECT * FROM tb_user;
SELECT * FROM scott.emp;
SELECT * FROM emp;
CREATE TABLE emp AS SELECT * FROM scott.emp;
CREATE TABLE dept AS SELECT * FROM scott.dept;
–游标的使用
DECLARE
CURSOR mycur IS SELECT * FROM emp;
myrow emp%ROWTYPE;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO myrow;
IF myrow.sal < 2000 THEN
IF myrow.sal + 500 > 2000 THEN
UPDATE emp SET sal =2000 WHERE empno = myrow.empno;
ELSE
UPDATE emp SET sal = myrow.sal + 500 WHERE empno=myrow.empno;
END IF;
END IF;
IF mycur%NOTFOUND THEN
EXIT;
END IF;
END LOOP;
CLOSE mycur;
COMMIT;
END;
—
DECLARE
myvar INT := &mynumber;
mystr varchar2(50) := &mytext;
BEGIN
dbms_output.put_line(mystr||’=’||myvar);
END;
–计算器–
DECLARE
myvar INT:=&mynumber;
mystr varchar2(50):=&mytext;
myvar1 INT:=&mynum;
mysum INT;
myjian INT;
mycheng INT;
mychu INT;
BEGIN
mysum:=myvar+myvar1;
myjian:=myvar-myvar1;
mycheng:=myvar*myvar1;
mychu:=myvar/myvar1;
IF mystr=’+’ THEN
dbms_output.put_line(mysum||’=’||myvar||’+’||myvar1);
ELSIF mystr=’-‘ THEN
dbms_output.put_line(myjian||’=’||myvar||’-‘||myvar1);
ELSIF mystr=’*’ THEN
dbms_output.put_line(mycheng||’=’||myvar||’*’||myvar1);
ELSIF mystr=’/’ THEN
dbms_output.put_line(mychu||’=’||myvar||’/’||myvar1);
END IF;
— dbms_output.put_line(‘结果=’||mysum);
END;
CREATE TABLE dept AS SELECT * FROM scott.dept;
SELECT * FROM dept;
DECLARE
TYPE MyDept IS RECORD (myDeptno dept.deptno%TYPE, myDeptName dept.dname%type);
v_myDept M本文来源gaodai#ma#com搞*!代#%^码$网*yDept;
BEGIN
SELECT deptno,dname INTO v_myDept FROM dept WHERE deptno=10;
dbms_output.put_line(‘部门编号:’||v_myDept.myDeptno||’—-‘||’部门名称:’||v_myDept.myDeptName);
END;
SELECT * FROM dept;
—-savepoint的使用,rollback的使用
DECLARE
myrow dept%ROWTYPE;
BEGIN
SAVEPOINT x;–设置回滚点
UPDATE dept SET dname=’IT’ WHERE deptno=20;
SELECT * INTO myrow FROM dept WHERE deptno=20;
dbms_output.put_line(myrow.deptno||’–‘||myrow.dname||’–‘||myrow.loc);
dbms_output.put_line(‘update已经被执行’);
ROLLBACK TO x;–事务回滚到x点
–也可以直接回滚;
–ROLLBACK;
dbms_output.put_line(‘回滚到x点’);
COMMIT;
END;
COMMIT;
DECLARE
mynum INT;
mydname varchar2(14);
BEGIN
SAVEPOINT x;
UPDATE dept SET dname=’IT’ WHERE deptno=20;
SELECT dname INTO mydname FROM dept WHERE deptno=20;
dbms_output.put_line(‘update之后,提交事务之前:dname=’||mydname);
mynum :=10/0;
COMMIT;
dbms_output.put_line(‘进行顺利,已经提交’);
EXCEPTION
WHEN zero_divide THEN
dbms_output.put_line(‘发生异常,回滚’);
ROLLBACK TO x;
COMMIT;
SELECT dname INTO mydname FROM dept WHERE deptno=20;
dbms_output.put_line(‘rollback之后:dname=’||mydname);
END;
CREATE TABLE myacount(
acc_id INT PRIMARY KEY,
owner_name varchar2(50),
balance NUMBER (10,3)
)
INSERT INTO myacount VALUES(1,’张三’,1000.00);
INSERT INTO myacount VALUES(2,’李四’,2000.00);
SELECT * FROM myacount;
DECLARE
myindex INT :=&myindex;
mynum INT;
BEGIN
SAVEPOINT x1;
UPDATE myacount SET balance=balance-50 WHERE acc_id=1;
UPDATE myacount SET balance=balance+50 WHERE acc_id=2;
IF myindex=1 THEN
mynum :=10/0;
END IF;
COMMIT;
EXCEPTION
WHEN zero_divide THEN
dbms_output.put_line(‘发生异常,事务回滚’);
ROLLBACK TO x1;
COMMIT;
END;
–存储过程
CREATE OR REPLACE PROCEDURE pro_test
AS
BEGIN
dbms_output.put_line(‘此存储过程已执行完毕!’);
END;
–存储过程调用方法一
CALL pro_test();
–存储过程调用方法二 在pl/sql语句块中调用
BEGIN
pro_test();
END;
–带参数的存储过程
CREATE OR REPLACE PROCEDURE pro_test_params(v_num1 IN NUMBER,v_str IN varchar2,v_return OUT varchar2)
AS
BEGIN
v_return :=(v_num1+1)||v_str;
dbms_output.put_line(‘v_return’||v_return);
END;
–带输入输出参数的调用
DECLARE
v_display VARCHAR(100);
BEGIN
pro_test_params(9,’结构’,v_display);
dbms_output.put_line(‘v_display=’||v_display);
END;
–创建一个带参数的存储过程,输入参数同事又是输出参数
–第一个参数为number,输入参数.第二个为varchar2类型的,是输入输出参数
–在参数中,就写varchar2,number,不需要指定长度等
CREATE OR REPLACE PROCEDURE pro_test_params_inout(v_num IN NUMBER,v_str IN OUT varchar2)
AS
–此处用来声明变量
BEGIN
v_str :=(v_num+1)||v_str;
END;
–CALL pro_test_params_inout(99,);
–一个ASCII字符在Oracle中占用一个字节,一个汉字在Oracle中占据两个字节
DECLARE
v_str VARCHAR2(20);
BEGIN
v_str :=’我是字符串’;
pro_test_params_inout(99,v_str);
— pro_test_params_inout(99); 报错:参数个数
dbms_output.put_line(v_str);
END;
——————函数的创建———————
CREATE OR REPLACE FUNCTION func_test(v_num NUMBER,v_str varchar2)
–声明返回值类型
RETURN VARCHAR2
AS
v_return varchar2(100);
BEGIN
v_return :=v_str||(v_num+1);
–返回 返回值
RETURN v_return;
END;
SELECT func_test(99,’我是字符串’) FROM dual;
–模拟字符串拼接函数 concat
SELECT concat(‘aaa’,’ccc’) FROM dual;
–func_concat
CREATE OR REPLACE FUNCTION func_concat(v_str1 VARCHAR2,v_str2 VARCHAR2)
–声明返回值类型
RETURN VARCHAR2
AS
v_return varchar2(50);
BEGIN
–给返回变量赋值
v_return := v_str1||v_str2;
–将处理加过返回
RETURN v_return;
END;
SELECT func_concat(‘a’,’b’) FROM dual;
SELECT * FROM dept;
SELECT * FROM emp;
–游标在存储过程和函数中的使用
–存储过程中的使用
–根据部门的名称查找部门所有的员工
–对于存储过程来讲,如果参数不写明in或者out,系统默认为输入参数
–输出参数为有表的时候,类型是:sys_refcursor
CREATE OR REPLACE PROCEDURE pro_allEmps(v_deptname IN VARCHAR2,v_emps OUT SYS_REFCURSOR)
AS
v_deptno INT;
BEGIN
–根据用户输入的部门查询对应的部门标号
SELECT deptno INTO v_deptno FROM dept WHERE dept.dname=v_deptname;
–使用游标接收查询到的结果集
OPEN v_emps FOR SELECT * FROM emp WHERE emp.deptno=v_deptno;
END;
DECLARE
–声明一个属性行变量,用来接收遍历游标的时候,去除的一条结果
v_temp emp%ROWTYPE;
–声明一个sys_refcursor类型的游标来接收过程的输出参数
v_cursor SYS_REFCURSOR;
BEGIN
–使用声明的游标作为参数,接收存储过程的输出结果:v_cursor
pro_allEmps(‘RESEARCH’,v_cursor);
LOOP
FETCH v_cursor INTO v_temp;
EXIT WHEN v_cursor%NOTFOUND;
dbms_output.put_line(v_temp.empno||’-‘||v_temp.ename||’-‘||v_temp.job||’-‘||v_temp.sal||’-‘||v_temp.deptno);
END LOOP;
END;
–在函数中使用游标
–根据部门名称查找该部门的所有员工
CREATE OR REPLACE FUNCTION func_allemps(v_deptname varchar2)
RETURN SYS_REFCURSOR
AS
v_cursor SYS_REFCURSOR;
v_deptno INT;
BEGIN
SELECT deptno INTO v_deptno FROM dept WHERE dept.dname=v_deptname;
OPEN v_cursor FOR SELECT * FROM emp WHERE emp.deptno=v_deptno;
RETURN v_cursor;
END;
SELECT func_allemps(‘sales’) FROM dual;
CREATE TABLE emp AS SELECT * FROM scott.emp;
ALTER USER scott ACCOUNT UNLOCK;
ALTER USER scott ACCOUNT LOCK;
DECLARE
–声明一个游标用来接收函数的返回结果
v_cursorme SYS_REFCURSOR;
–声明一个属性行变量来接收游标返回的每一条记录
v_temprow emp%ROWTYPE;
BEGIN
–函数的调用，由于函数是有返回值的，所以我们使用声明的游标接收
v_cursorme := func_allemps(‘SALES’);
LOOP
FETCH v_cursorme INTO v_temprow;
EXIT WHEN v_cursorme%NOTFOUND;
dbms_output.put_line(‘员工姓名’||v_temprow.ename);
END LOOP;
END;
——触发器的操作
CREATE OR REPLACE TRIGGER tri_before_test_emp
BEFORE INSERT ON emp FOR EACH ROW
BEGIN
dbms_output.put_line(‘正在向emp表插入数据’);
END;
SELECT * FROM emp;
BEGIN
INSERT INTO emp VALUES(104,’王五’,’会计’,0,SYSDATE,100,0,10);
COMMIT;
END;
CREATE OR REPLACE TRIGGER tri_before_test_emp
BEFORE INSERT ON emp FOR EACH ROW
BEGIN
dbms_output.put_line(‘正在向emp表插入数据’);
dbms_output.put_line(‘新值:’||:NEW.empno||’–‘||:new.ename||’–‘||:NEW.job||’–‘||:new.mgr||’–‘||:NEW.hiredate);
END;
—对于after的一个trigger
CREATE OR REPLACE TRIGGER tri_after_test_emp
BEFORE INSERT ON emp FOR EACH ROW
BEGIN
dbms_output.put_line(‘已经向emp表插入数据’);
dbms_output.put_line(‘新值:’||:NEW.empno||’–‘||:new.ename||’–‘||:NEW.job||’–‘||:new.mgr||’–‘||:NEW.hiredate);
END;
SELECT * FROM emp;
DELETE FROM emp WHERE empno=104;
–设置主键
ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (empno);
–在插入数据的时候,生成主键的值
CREATE OR REPLACE TRIGGER tri_before_test_emp
BEFORE INSERT ON emp FOR EACH ROW
DECLARE
myindex INT;
BEGIN
dbms_output.put_line(‘正在向emp表插入数据’);
SELECT MAX(empno)INTO myindex FROM emp;
:NEW.empno:=myindex +1;
END;
–执行插入数据的sql语句
BEGIN
INSERT INTO emp VALUES(1,’测试’,’测试人’,0,SYSDATE,1000,0,10);
–提交数据
COMMIT;
END;
SELECT MAX(empno) FROM emp;
SELECT *FROM emp ORDER BY empno DESC;
–将insert换为:update delete
–update
CREATE OR REPLACE TRIGGER tri_before_test_emp
BEFORE UPDATE ON emp FOR EACH ROW
BEGIN
dbms_output.put_line(‘将要执行的update操作’);
dbms_output.put_line(‘新值为:’||’–‘||:new.ename||’–‘||:NEW.job||’–‘||:new.mgr||’–‘||:NEW.sal);
dbms_output.put_line(‘旧值为:’||’–‘||:OLD.ename||’–‘||:OLD.job||’–‘||:OLD.mgr||’–‘||:OLD.sal);
END;
SELECT * FROM emp;
BEGIN
UPDATE emp SET ename=’小李’,job=’测试人’,mgr=1000,sal=50 WHERE empno=100;
COMMIT;
END;
–after update
CREATE OR REPLACE TRIGGER tri_before_test_emp
BEFORE UPDATE ON emp FOR EACH ROW
BEGIN
dbms_output.put_line(‘己执行的update操作’);
dbms_output.put_line(‘新值为:’||’–‘||:new.ename||’–‘||:NEW.job||’–‘||:new.mgr||’–‘||:NEW.sal);
dbms_output.put_line(‘旧值为:’||’–‘||:OLD.ename||’–‘||:OLD.job||’–‘||:OLD.mgr||’–‘||:OLD.sal);
END;
SELECT * FROM emp;
BEGIN
UPDATE emp SET ename=’小张’,job=’经理人’,mgr=2000,sal=520 WHERE empno=102;
COMMIT;
转载请注明原文链接：Oracle学习大全
