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;
IF myindex > 10 THEN
EXIT;
END IF;
END LOOP;
END;
--while循环
DECLARE
myindex INT :=0;
BEGIN
WHILE myindex 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 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 MyDept;
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;

END;

--Oracle的分页
select t1.*,rownum rn from (select * from emp) t1
--取出前5条
select t1.*,rownum rn from (select * from emp) t1 where rownum --取出10条之内大于等于6的
select * from(select t1.*,rownum rn from (select * from emp) t1 where rownum=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_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;