[Oracle]语法备忘]

• 建表
• 1. create table STUDENT
  2. (
  3. ID VARCHAR2(10) not null,
  4. NAME VARCHAR2(20)
  5. )

1. create table CLASS
2. (
3. ID NUMBER not null,
4. SID VARCHAR2(10) not null
5. )

• 添加外键约束

1. ALTER TABLE course ADD CONSTRAINT fk_course_sid FOREIGN key(sid) REFERENCES student(id);

• 字符串函数

1. SQL> select substr('123,456',0,instr('123,456',',')-1) from dual;
3. SUBSTR('123,456',0,INSTR('123,
4. ------------------------------
5. 123

• 创建FUNCTION

1. CREATE OR REPLACE FUNCTION studentsInClass
2. (cid number) -- parameter
3. return number-- return value
4. is
5. --declare variables
6. returnVal number;
7. --end declare variables
8. begin
9. select count(*) into returnVal from class where id=cid;
10. return returnVal;
11. end studentsInClass;

• 调用FUNCTION

1. select studentsinclass(1) from dual;

1. -- Created on 2008-9-26 by FENG
2. declare
3. -- Local variables here
4. i integer;
5. begin
6. -- Test statements here
7. i := studentsinclass(1);
8. dbms_output.put_line('t1表中数据的总量是:'||i);
9. end;

• 创建STORED PROCEDURE

1. CREATE OR REPLACE TYPE myTable AS TABLE OF varchar(10);
2. CREATE OR REPLACE PROCEDURE addtoclass(cid IN number, sids IN MYTABLE, n out int) IS --n:total student in class(cid)
3. BEGIN
4. FOR I IN 1..sids.COUNT LOOP
5. -- DBMS_OUTPUT.PUT_LINE(P_T(I));
6. INSERT INTO class VALUES (cid, sids(I));
7. END LOOP;
8. COMMIT;
10. select count(*) into n from class where id=cid;
11. END addtoclass;

• 调用STORED PROCEDURE

1. -- Created on 2008-9-26 by FENG
2. declare
3. -- Local variables here
4. i integer;
5. begin
6. -- Test statements here
7. addtoclass(1, mytable('001','002'), i);
8. dbms_output.put_line('number of students in class 1:'||i);
9. end;

• DECLEARE, BEGIN END

1. DECLARE
2. id NUMBER;
4. BEGIN
5. id := 0;
6. DELETE FROM A_a WHERE aid=id;
7. DELETE FROM A_b WHERE aid=id;
8. DELETE FROM A_c WHERE aid=id;
9. DELETE FROM A_d WHERE aid=id;
10. DELETE FROM A WHERE aid=id;
11. COMMIT;
12. END;

http://blog.youkuaiyun.com/believefym/article/details/1836162