搜索
首页数据库mysql教程Oracle学习大全

Oracle学习大全

Jun 07, 2016 pm 02:51 PM
oraclesystem创建大全学习用户空间

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




声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
您可以使用哪些工具来监视MySQL性能?您可以使用哪些工具来监视MySQL性能?Apr 23, 2025 am 12:21 AM

如何有效监控MySQL性能?使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。1.使用mysqladmin查看连接数。2.用SHOWGLOBALSTATUS查看查询数。3.PMM提供详细性能数据和图形化界面。4.MySQLEnterpriseMonitor提供丰富的监控功能和报警机制。

MySQL与SQL Server有何不同?MySQL与SQL Server有何不同?Apr 23, 2025 am 12:20 AM

MySQL和SQLServer的区别在于:1)MySQL是开源的,适用于Web和嵌入式系统,2)SQLServer是微软的商业产品,适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显着差异,选择时需考虑项目规模和未来扩展性。

在哪些情况下,您可以选择SQL Server而不是MySQL?在哪些情况下,您可以选择SQL Server而不是MySQL?Apr 23, 2025 am 12:20 AM

在需要高可用性、高级安全性和良好集成性的企业级应用场景下,应选择SQLServer而不是MySQL。1)SQLServer提供企业级功能,如高可用性和高级安全性。2)它与微软生态系统如VisualStudio和PowerBI紧密集成。3)SQLServer在性能优化方面表现出色,支持内存优化表和列存储索引。

MySQL如何处理角色集和碰撞?MySQL如何处理角色集和碰撞?Apr 23, 2025 am 12:19 AM

mySqlManagesCharacterSetsetSandCollat​​ionsyutusututf-8asthEdeFault,允许ConfigurationAtdataBase,table和columnlevels,AndrequiringCarefullageLignmentToavoidMismatches.1)setDefeaultCharactersetTercharactersetEtCollacterSeteTandColletationForAdataBase.2)conformentcollecharactersettersetertersetcollat​​ertersetcollat​​ioncollat​​ion

MySQL中有什么触发器?MySQL中有什么触发器?Apr 23, 2025 am 12:11 AM

MySQL触发器是与表相关联的自动执行的存储过程,用于在特定数据操作时执行一系列操作。1)触发器定义与作用:用于数据校验、日志记录等。2)工作原理:分为BEFORE和AFTER,支持行级触发。3)使用示例:可用于记录薪资变更或更新库存。4)调试技巧:使用SHOWTRIGGERS和SHOWCREATETRIGGER命令。5)性能优化:避免复杂操作,使用索引,管理事务。

您如何在MySQL中创建和管理用户帐户?您如何在MySQL中创建和管理用户帐户?Apr 22, 2025 pm 06:05 PM

在MySQL中创建和管理用户账户的步骤如下:1.创建用户:使用CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';2.分配权限:使用GRANTSELECT,INSERT,UPDATEONmydatabase.TO'newuser'@'localhost';3.修正权限错误:使用REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost';然后重新分配权限;4.优化权限:使用SHOWGRA

MySQL与Oracle有何不同?MySQL与Oracle有何不同?Apr 22, 2025 pm 05:57 PM

MySQL适合快速开发和中小型应用,Oracle适合大型企业和高可用性需求。1)MySQL开源、易用,适用于Web应用和中小型企业。2)Oracle功能强大,适合大型企业和政府机构。3)MySQL支持多种存储引擎,Oracle提供丰富的企业级功能。

与其他关系数据库相比,使用MySQL的缺点是什么?与其他关系数据库相比,使用MySQL的缺点是什么?Apr 22, 2025 pm 05:49 PM

MySQL相比其他关系型数据库的劣势包括:1.性能问题:在处理大规模数据时可能遇到瓶颈,PostgreSQL在复杂查询和大数据处理上表现更优。2.扩展性:水平扩展能力不如GoogleSpanner和AmazonAurora。3.功能限制:在高级功能上不如PostgreSQL和Oracle,某些功能需要更多自定义代码和维护。

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

Atom编辑器mac版下载

Atom编辑器mac版下载

最流行的的开源编辑器

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3 英文版

SublimeText3 英文版

推荐:为Win版本,支持代码提示!