SELECT * FROM user_tables
SELECT table_name FROM user_tables
SELECT view_name FROM user_views
SELECT constraint_name FROM user_constraints
SELECT * FROM DICTIONARY
SELECT COUNT(*) FROM DICTIONARY
SELECT index_name FROM user_indexes
CREATE TABLE article(
ID NUMBER,
title VARCHAR2(1024),
cont LONG
);
-- long 文本类型,2G max
CREATE SEQUENCE seq;
-- 线程同步(安全)
SELECT * FROM seq;
SELECT seq.NEXTVAL FROM dual
DECLARE
v_deptno emp2.deptno%TYPE;
v_count NUMBER;
BEGIN
UPDATE emp2 SET sal = SQL/2 WHERE deptno = v_deptno;
dbms_outputput_line(SQL%ROWCOUNT || '条记录被影响');
COMMIT;
END;
--打印刚刚执行的语句影响的记录数目
SQL 代表刚刚执行的语句
ROWCOUNT 是sql的属性,表示所影响的条数
-- 误区:一直以为oracle 没有 else if, 原来elsif, 是这样写
SET serveroutput ON
DECLARE
a VARCHAR(1):='1';
BEGIN
IF(a = '1') THEN
dbms_output.put_line(a);
ELSIF(a = '2')THEN
dbms_output.put_line('2');
ELSE
dbms_output.put_line('0');
END IF;
END;
-- 学习 reverse
BEGIN
FOR k IN 1..10 LOOP
dbms_output.put_line(k);
END LOOP;
FOR j IN REVERSE 1..10 LOOP
dbms_output.put_line(j);
END LOOP;
END;
-- 错误处理
too_many_rows: 返回了多行记录
OTHERS 所有的错误
no_data_found 没找到记录
-- errlog
CREATE TABLE errorlog(
ID NUMBER PRIMARY KEY,
errcode NUMBER,
errmsg VARCHAR2(1024),
errdate DATE
);
CREATE SEQUENCE seq_errorlog_id START WITH 1 INCREMENT BY 1;
SQLCODE -- 出错代码
SQLERRM -- 出错信息
--游标的4个属性
ISOPEN
NOTFOUND
FOUND
ROWCOUNT
-- 可更新游标
DECLARE
CURSOR c
IS SELECT * FROM emp2 FOR UPDATE;
BEGIN
FOR v_temp IN c LOOP
IF<v_temp.SQL<2000> THEN
UPDATE emp2 SET SQL = SQL * 2 WHERE CURRENT OF c;
-- current of c :表示更新当前行
ELSIF <v_temp.SQL = 5000> THEN
DELETE FROM emp2 WHERE CURRENT OF c;
END IF;
END LOOP;
COMMIT;
END;
-- oracle 里即使有语法错误,这个过程依然会被创建
-- 明天验证一下
-- drop
CREATE OR REPLACE PROCEDURE p IS
BEGIN
dbms_output.put_line('aaaa');
END;
-- 银行里德转账的记录就是这样的
CREATE OR REPLACE TRIGGER trig
AFTER INSERT OR DELETE OR UPDATE ON emp2 FOR EACH ROW
BEGIN
IF inserting THEN
INSERT INTO emp2_log VALUES(USER, 'insert', SYSDATE);
ELSIF updating THEN
INSERT INTO emp2_log VALUES(USER,'update', SYSDATE);
ELSIF deleting THEN
INSERT INTO emp2_log VALUES (USER,'delete', SYSDATE);
END IF;
END;
关键字:
inserting
updating
deleting 代表动作
USER 代表当前用户
FOR EACH ROW -- 每一行
UPDATE dept SET deptno = 99 WHERE deptno = 10;
-- 这样写是不行的
CREATE OR REPLACE TRIGGER trig
AFTER UPDATE ON dept
FOR EACH ROW
BEGIN
UPDATE emp SET deptno = :NEW.deptno WHERE deptno = :OLD.deptno;
-- :NEW 新的状态
-- :OLD 旧的状态
END;
/
UPDATE dept SET deptno = 99 WHERE deptno = 10;
--有了触发器,再执行这条语句就可以了