--在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;
如果我的索引> 10 那么
退出；
结束如果；
结束循环；
结束；
--循环
声明
myindex INT :=0;
开始
虽然 myindex
循环
dbms_output.put_line(concat('myindex = ',myindex));
myindex :=myindex + 1;
结束循环；
结束；
--自增序列
创建序列
从 1 开始
增加 1；
--for 循环
开始
对于 i 在 0..10
循环
dbms_output.put_line('index = '||i);
结束循环；
结束；
--反向食品
开始
对于 i 倒转 0..10
循环
dbms_output.put_line('index = '||i);
结束循环；
结束；
--动态sql语句
创建表 tb_test(
t_id INT 主键，
t_name varchar2(10)
)
声明
mysql VARCHAR2(500);
mydate 日期；
开始
立即执行 'select sysdate from Dual' INTO mydate;
dbms_output.put_line(to_char(mydate,'YYYY-MM-DD'));
结束；
声明
mysql VARCHAR2(500) : ='';
mytext VARCHAR2(10) := '小名的名字';
开始
立即执行 'insert into tb_test value(3,:x)' USING '小明';
结束；
声明
mysql VARCHAR2(500) :='插入 tb_test(t_id,t_name) 值(3,:x)';
mytext VARCHAR2(10) := '小明';
开始
使用 mytext 立即执行 mysql；
结束；
从 tb_test 选择*；
-- 给tb_test创建一个序列，用于生成主键的值
创建序列 seq_123
从 5 开始
增加 1；
--添加列
更改表 tb_test 添加 t_mony VARCHAR2(50);
声明
mysql VARCHAR2(500) :='插入 tb_test 值(:n,:x,:y)';
myname VARCHAR2(10) := '李四';
mydesc VARCHAR2(100) :='这是李四的描述';
myindex INT;
开始
SELECT seq_123.NEXTVAL INTO myindex FROM Dual;
使用 myindex、myname、mydesc 立即执行 mysql；
结束；
--异常的处理
声明
var1 INT :=87;
var2 INT :=0;
开始
var1 := var1/var2;
dbms_output.put_line('已经执行了,');
--异常处理语句
异常
--系统定义异常,zero_divide
当零除时
dbms_output.put_line('不能被0除,异常');
结束；
--自定义异常
开始
raise_application_error(-20001,'我测试用的自定义异常');
结束；
从 tb_user 中选择*；
从 scott.emp 中选择*；
从 emp 中选择*；
创建表 emp AS SELECT * FROM scott.emp;
创建表部门 AS SELECT * FROM scott.dept;
--游标的使用
声明
CURSOR mycur IS SELECT * FROM emp;
myrow emp%ROWTYPE;
开始
打开 mycur；
循环
将 mycur 提取到 myrow 中；
IF myrow.sal
如果 myrow.sal + 500 > 2000 那时
UPDATE emp SET sal =2000 WHERE empno = myrow.empno;
其他
更新 emp SET sal = myrow.sal + 500 WHERE empno=myrow.empno;
结束如果；
结束如果；
如果 mycur%NOTFOUND 那么
退出；
结束如果；
结束循环；
关闭 mycur；
提交；
结束；
--
声明
myvar INT := &mynumber;
mystr varchar2(50) := &mytext;
开始
dbms_output.put_line(mystr||'='||myvar);
结束；
--外汇--
声明
myvar INT:=&mynumber;
mystr varchar2(50):=&mytext;
myvar1 INT:=&mynum;
mysum INT;
myjian INT；
myheng INT；
mychu INT；
开始
mysum:=myvar+myvar1;
myjian:=myvar-myvar1;
myheng:=myvar*myvar1;
mychu:=myvar/myvar1;
IF mystr='+' THEN
dbms_output.put_line(mysum||'='||myvar||'+'||myvar1);
ELSIF mystr='-' 那么
dbms_output.put_line(myjian||'='||myvar||'-'||myvar1);
ELSIF mystr='*' 那么
dbms_output.put_line(my Cheng||'='||myvar||'*'||myvar1);
ELSIF mystr='/' 那么
dbms_output.put_line(mychu||'='||myvar||'/'||myvar1);
结束如果；
-- dbms_output.put_line('结果='||mysum);
结尾;
创建表部门 AS SELECT * FROM scott.dept;
从部门选择*；
声明
TYPE MyDept IS RECORD (myDeptno dept.deptno%TYPE, myDeptName dept.dname%type);
v_myDept 我的部门；
开始
SELECT deptno,dname INTO v_myDept FROM dept WHERE deptno=10;
dbms_output.put_line('部门编号:'||v_myDept.myDeptno||'----'||'部门名称:'||v_myDept.myDeptName);
结束；
从部门选择*；
----保存点的使用，回滚的使用
声明
myrow 部门%ROWTYPE;
开始
SAVEPOINT x;--设置回滚点
更新部门 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点
--也可以直接回滚;
--回滚;
dbms_output.put_line('回滚到x点');
提交；
结束；
提交；
声明
mynum INT;
mydname varchar2(14);
开始
保存点 x;
UPDATE dept SET dname='IT' WHERE deptno=20;
SELECT dname INTO mydname FROM dept WHERE deptno=20;
dbms_output.put_line('更新之后,提交事务之前:dname='||mydname);
mynum :=10/0;
提交；
dbms_output.put_line('进行顺利,已经提交');
异常
当零除时
dbms_output.put_line('发生异常,回滚');
回滚到 x;
提交；
SELECT dname INTO mydname FROM dept WHERE deptno=20;
dbms_output.put_line('回滚之后:dname='||mydname);
结束；
创建表 myacount(
acc_id INT 主键，
所有者名称 varchar2(50),
余额编号 (10,3)
)
INSERT INTO myacount VALUES(1,'张三',1000.00);
INSERT INTO myacount VALUES(2,'李四',2000.00);
从 myacount 选择 *；
声明
myindex INT :=&myindex;
mynum INT;
开始
保存点 x1；
UPDATE myacount SET Balance=balance-50 WHERE acc_id=1;
UPDATE myacount SET 余额=余额+50 WHERE acc_id=2;
如果 myindex=1 那么
mynum :=10/0;
结束如果；
提交；
异常
当零除时
dbms_output.put_line('发生异常,事务回滚');
回滚到 x1；
提交；
结束；
--存储过程
创建或替换过程 pro_test
作为
开始
dbms_output.put_line('此存储过程已执行完毕！');
结束；
--存储过程调用方法一
调用 pro_test();
-- 存储过程调用方法二 在pl/sql语句块中调用
开始
pro_test();
结束；
-- 带参数的存储过程
创建或替换过程 pro_test_params(v_num1 IN NUMBER,v_str IN varchar2,v_return OUT varchar2)
作为
开始
v_return :=(v_num1+1)||v_str;
dbms_output.put_line('v_return'||v_return);
结束；
-- 带输入输出参数的调用
声明
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;
END;
--Oracle的分页
select t1.*,rownum rn from (select * from emp) t1
--取出前5条
select t1.*,rownum rn from (select * from emp) t1 where rownum<=5;
--取出10条之内大于等于6的
select * from(select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6;
--开发一个包
--创建一个包,在该包中,我定义类型test_cursor,是个游标
create or replace package tespackage as
type test_cursor is ref cursor;
end tespackage;
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
---创建包体
create package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
begin
update emp set sal = newsal where ename=name;
end;
function annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp
where ename=name;
return annual_salary;
end;
end;
--开始编写分页的过程
create or replace procedure fenye
(tableName in varchar2,
Pagesize in number, --一页记录数
pageNow in number,
myrows out number, --总记录数
myPageCount out number, --总页数
p_cursor out tespackage.test_cursor --返回记录集
) is
--定义部分
--定义sql语句 字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:= (pageNow-1)*Pagesize+1;
v_end number:=pageNow*Pagesize;
begin
--执行部分
v_sql:='select * from select * from(select t1.*,rownum rn from (select * from '|| tableName
||') t1 where rownum<='||v_end||') where rn>='||v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算myrows和myPageCount
--组织一个sql
v_sql:='select count(*) from '||tableName;
--执行sql,并把返回的值,赋给myrows
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,Pagesize)=0 then
myPagecount := myrows/Pagesize;
else
myPageCount := myrows/Pagesize+1;
end if;
--关闭游标
--close p_cursor;
end;
