Oracle学习大全

--在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;