Oracle備忘錄1

Oracle備忘錄1

數據庫管理員：
安裝升級Oracle數據庫
建庫，表空間，表，視圖，索引。。。
制定並實施備份和修復計劃
數據庫權限管理，調優，故障排除
對於高級DBA，要求參與項目開發，會編寫SQL語句，存儲過程，觸發器，規則，約束，包

CREATE TABLE EMP
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(4)
);

CREATE TABLE DEPT(
DEPTNO NUMBER(4),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);

CREATE TABLE SALGRADE
(
GRADE NUMBER,
LOSAL  NUMBER,
HISAL NUMBER 
);

CREATE TABLE BONUS
(
ENAME VARCHAR(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','dd-mm-yyyy'),800,null,20);   
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,TO_DATE('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,TO_DATE('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,TO_DATE('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,TO_DATE('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,TO_DATE('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,TO_DATE('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP
VALUES(7788,'SCOTT','ANALYST',7566,'19-4月-1987',3000.00,NULL,20);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,TO_DATE('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);


SELECT * FROM DEPT;
SELECT * FROM EMP;
SELECT * FROM SALGRADE;

CREATE USER  DB_USER IDENTIFIED BY DB_USER_PW;   ——創建數據庫/用戶，要在system下進行操作
DROP USER  DB_USER (CASCADE);
passw;   ——修改用戶密碼 
passw DB_USER;    ——管理員修改用戶密碼
ALTER USER  DB_USER IDENTIFIED BY DB_USER_PW;   ——管理員修改用戶密碼
shutdown;
startup;
SHOW parameter;

賦予權限：前兩個要在system下進行操作
方法1：GRANT DBA TO DB_USER;       ——賦予用戶數據庫管理權限
方法2：GRANT CONNECT TO DB_USER;       ——賦予用戶連接權限
       GRANT RESOURCE TO DB_USER;         ——賦予用戶資源權限
方法3：CONNECT DB_USER/DB_USER_PW as sysdba或者as sysoper;   ——連接數據庫並賦予權限
GRANT CREATE SESSION TO DB_USER;    ——賦予用戶創建會話的權限,對Oracle數據庫暫時不知道怎麼用

CREATE USER  role NOT IDENTIFIED; ——創建角色時不需要輸入密碼即可登錄 
顯示數據庫中所有的用戶：
SELECT USER NAME FROM dba_USER s;
顯示數據庫中用戶的系統權限：
SELECT * FROM dba_sys_privs WHERE GRANTee='角色名;
SELECT * FROM role_sys_privs WHERE role='角色名;
顯示數據庫中用戶的對象權限：
SELECT * FROM dba_tab_privs WHERE GRANTee='角色名';    
顯示數據庫中用戶的角色：
SELECT * FROM dba_role_privs WHERE GRANTee='用戶名';
顯示數據庫中用戶的權限
SELECT * FROM dba_sys_privs 
Oracle究竟有多少中角色
SELECT * FROM dba_roles;
Oracle的所有系統權限
SELECT * FROM system_privilege_map order BY NAME;
Oracle的所有對象權限
SELECT DISTINCT privilege FROM dba_tab_privs;

顯示當前數據庫的全稱：
SELECT * FROM global_NAME;

conn sys/change_on_install as sysdba;——作為系統數據庫管理員登錄

DROP TABLE r;       ——刪除表
RENAME r TO r1;   ——重命名表名

ALTER TABLE r RENAME column A1 TO A2;    ——修改表中的列名
ALTER TABLE r ADD A D;     ——增加列的屬性
ALTER TABLE r MODIFY A D;     ——修改列的屬性
ALTER TABLE r DROP A;        ——刪除列的屬性

INSERT INTO r VALUES();          ——插入元組（表中內容）
UPDATE TABLE r SET A WHERE ...;  ——更新元組（表中內容）

DELETE FROM r WHERE ...;  ——刪除元組（表中內容），寫日志，可恢復
TRUNCATE TABLE r;＿＿刪除元組（表中內容），速度快，不寫日志，不可恢復

SHOW linesize;  ——顯示行的寬度，默認值為80
SET linesize NUMBER; ——設置行的寬度為NUMBER    
SET pagesize NUMBER;  ——設置每頁顯示的行數，默認值為14

ALTER SESSION SET nls_date_format='yyyy-mm-dd'; ＿＿日期設置成中國人習慣的方式
NVL(D,0)  ——如何處理空值null


--增加約束
ALTER TABLE r MODIFY A NOT NULL;--非空
ALTER TABLE r ADD CONSTRAINT cardunique UNIQUE(A); --身份證唯一
ALTER TABLE r ADD CONSTRAINT ADDresscheck CHECK(A in('北京','上海','廣州'));
--刪除約束
ALTER TABLE r DROP CONSTRAINT A UNIQUE/CHECK;
ALTER TABLE r DROP CONSTRAINT primary key CASCADE;

UPDATE  r SET A WHERE ...;  ——更新元組（表中內容）
UPDATE  r SET (a1,a2,a3)=(SELECT (A1,A2,A3) FROM r WHERE...) WHERE...;
DELETE FROM r WHERE ...;  ——刪除元組（表中內容）

顯示當前用戶下的所有表：
SELECT TABLE_NAME FROM USER _TABLEs;
顯示當前用戶可以訪問的所有的表：
SELECT TABLE_NAME FROM all_TABLEs;
顯示數據庫中全部的表，必須是dba或者具有SELECT any TABLE的權限
SELECT TABLE_NAME FROM dba_TABLEs;

CREATE profile failed_login_lock_NAME limit failed_login_attEMPs num1 password_lock_time num2;   ——建立輸入密碼錯誤次數達到num1時鎖定規定時間num2
CREATE profile password_life_time_NAME limit password_life_time num1 password_grace_time num2 password_reuse_time NUMBER3;  ——建立在num1修改密碼，寬限天為num2,在NUMBER3天後可以重用
DROP profile profile_NAME(CASCADE);    ——刪除文件
ALTER USER  DB_USER profile profile_NAME;     ——對某用戶實行這一鎖定規則 
ALTER USER  DB_USER account unclok;  ——進行解鎖 

savapoint a;——設置回滾點，保存點，可預防數據庫重要信息的丟失，保存點個數沒有限制（可以設置多個回滾點）
ROLLBACK TO a;——回滾到這個點
ROLLBACK;——回滾到最初的狀態
COMMIT;——提交，每天都必須的操作，但是會刪除全部保存點，釋放鎖，謹慎使用，使用時確保數據庫已經不存在問題！

分頁：
ROWNUM
經典例句：SELECT * FROM (SELECT a.*,rownum rn FROM (SELECT * FROM EMP) a WHERE rownum<=10)  WHERE rn>=6;
復制一部分：CREATE TABLE myTABLE(id,NAME) as SELECT EMPno,ename FROM EMP;

UNION 合並查詢去重 UNION ALL  不去重
INTERSECT 交集查詢
MINUS  差集查詢,前面減去兩者的交集
TO_DATE('1991-12-12','yyyy-mm-dd')     TO_DATE('1991/12/12','yyyy/mm/dd') ——指定日期格式
INSERT INTO EMP VALUES(1235,'Test_2','MANAGER',7782,TO_DATE('1998-9-9 12:10:10','yyyy-mm-dd hh24:mi:ss'),1000,100,10);
INSERT INTO EMP VALUES(1236,'Test_3','MANAGER',7782,sysdate,1000,100,10);
SELECT ename,TO_CHAR(hiredate,'yyyy-mm-dd hh24:mi:ss') FROM EMP; ——查詢具體時間，小時，分鐘，秒，bbs系統常備
SELECT ename,TO_CHAR(hiredate,'yyyy-mm-dd hh24:mi:ss'),TO_CHAR(sal,'L99,999.99') FROM EMP;  ——顯示本地貨幣
SELECT * FROM EMP WHERE TO_CHAR(hiredate,'yyyy')=1980;  ——顯示某一年入職的員工，某一月mm，某一年中的某一月同理yyyy-mm

SET TRANSLATE READ ONLY; ——事務處理集

函數：
LOWER(A);
UPPER(A);
LENGTH(A);
SUBSTR(A,m,n); ——m代表起始位置，n代表取得字符個數
完成首字母大寫 ——SELECT UPPER(SUBSTR(A,1,1)) FROM EMP;
完成後面字母小寫——SELECT lower(SUBSTR(A,2,LENGTH(A)-1)) FROM r;
合並：SELECT UPPER(SUBSTR(A,1,1))||lower(SUBSTR(A,2,LENGTH(A)-1)) FROM r;
REPLACE(A,search_string,REPLACE_string); ——替換字符
INSTR(char1,char2,[,n[,m]]; ——取字串在字符串的位置

數學函數：
ROUND(A,m); ——四捨五入，保留m位
TRUNC(A,m);——截取數字，保留m位,忽略余數
MOD(A,n);——對A取模n
FLOOR(A);——返回小於或者等於n的最大整數，忽略余數
CEIL(A);——返回大於或者等於n的最小整數

SELECT SYSDATE FROM dual;——返回系統時間
SELECT * FROM EMP WHERE sysdate>ADD_months(hiredate,8);返回系統時間8個月之前的員工
SELECT trunc(sysdate-hiredate) "入職天數" FROM EMP;      ——返回入職天數
SELECT hiredate,ename FROM EMP WHERE last_day(hiredate)-2=hiredate;  ——返回每月倒數第2天的員工

轉換函數：
TO_CHAR(A,'');   
TO_DATE(A,'');

系統函數：
SELECT sys_context('USER env','db_NAME') FROM dual;               ——language：當前語言,SESSION_USER ：當前用戶,current_schema：當前方案

進行行遷移：
INSERT INTO r2(A1,A2,A3) SELECT a1,a2,a3 FROM r1 WHERE ...;

導出：
導出表，導出方案，導出數據庫
EXP命令,要從命令行進入oracle中的bin 目錄
USER id,TABLEs,owner,full=y,inctype,rows,file
導出表的結構而不導入數據：
EXP USER id=learning/123456@XE TABLEs=(EMP) file=E:\DeskTop\EMP.dmp rows=n
導出表：
EXP USER id=learning/123456@XE TABLEs=(EMP) file=E:\DeskTop\EMP.dmp
下面這種方式導出快
EXP USER id=learning/123456@XE TABLEs=(EMP) file=E:\DeskTop\EMP.dmp direct=y
導出方案：
EXP USER id=learning/123456@XE owner=learning file=E:\DeskTop\EMP.dmp
導出全部：
EXP USER id=system/123456@XE full=y inctype=complete file=E:\DeskTop\EMP.dmp
導入表的結構，而不導入表的數據：
IMP USER id=learning/123456@XE TABLEs=(EMP) file=E:\DeskTop\EMP.dmp rows=n
導入表：
IMP USER id=learning/123456@XE TABLEs=(EMP) file=E:\DeskTop\EMP.dmp
導入數據,如果表已經存在只導入數據：
IMP USER id=learning/123456@XE TABLEs=(EMP) file=E:\DeskTop\EMP.dmp ignore y
導入表到其他用戶
IMP USER id=system/123456@XE TABLE=EMP file=E:\DeskTop\EMP.dmp TOUSER =learning;
導入數據庫：
IMP USER id=system/123456@XE full=y file=E:\DeskTop\EMP.dmp TOUSER =learning;

建立表空間：
CREATE TABLESPACE data01 datafile 'E:\data01.dbf' size 20m uniform size 128k;
增加數據文件：
ALTER TABLESPACE data01 ADD datafile 'E:\data02.dbf' size 20m;
增加數據文件的大小：
ALTER TABLESPACE data01 'E:\data01.dbf' resize 20m;
設置文件的自動增長：
ALTER TABLESPACE data01 'E:\data01.dbf' auTOextEND on next 10m maxsize 500m;

磁盤損壞時的做法：
確定數據文件所在的表空間：
SELECT TABLESPACE_NAME FROM dba_data_files WHERE file_NAME='E:\data01.dbf'; 
使表空間脫機：
ALTER TABLESPACE data01 offline;
移動表空間：
host move E:\data01.dbf D:\data01.dbf
對數據庫文件進行邏輯修改：
ALTER TABLESPACE data01 RENAME datafile 'E:\data01.dbf TO D:\data01.dbf';
使得表空間聯機：
ALTER TABLESPACE data01 online;

使用數據庫表空間：
CREATE TABLE r(deptno NUMBER(4),dNAME varchar2(14),loc varchar2(13)) TABLESPACE data01;
查詢表空間中的表：
 SELECT * FROM all_TABLEs WHERE TABLESPACE_NAME='DATA01';//注意大寫
查詢表處於哪個表空間：
SELECT TABLESPACE_NAME,TABLE_NAME FROM USER _TABLEs WHERE TABLE_NAME='EMP'; //注意大寫
刪除表空間：
DROP TABLESPACE DATA01 including contents and datafiles;

表級定義：
在定義了列之後添加約束
列級定義：
在定義列的同時添加約束

創建一個存儲過程，該過程可以向某表中添加記錄
CREATE TABLE mytest(
name VARCHAR2(30),
password VARCHAR2(30)
);
CREATE OR REPLACE  PROCEDURE b IS
BEGIN
--執行部分
INSERT INTO mytest VALUES('jack','123456');
END;
/
CREATE OR REPLACE  PROCEDURE b IS
BEGIN
--執行部分
DELETE FROM mytest WHERE name='jack';
END;
/
REPLACE表示如果有相同的過程名就替換,b表示過程名
查看錯誤信息：
SHOW error;
如何調用該過程
1:EXEC 過程名(參數值1，參數值2);
2:CALL 過程名(參數值1，參數值2);
定義常量時，建議用v_作為前綴v_sal
定義常量時，建議用c_作為前綴c_rate
當定義游標時，建議用_cursor作為後綴EMP_cursor
當定義例外時，建議用e_作為前綴e_error

SET serveroutput ON;--開啟顯示內容  
SET serveroutput OFF--關閉顯示內容

BEGIN
DBMS_OUTPUT.PUT_LINE('helloworld');
	END; 
	/--這個最後在加上

--顯示雇員名，薪水
DECLARE
v_ename VARCHAR2(5);
v_sal NUMBER(7,2);
BEGIN 
SELECT ename，sal INTO v_ename,v_sal FROM EMP WHERE EMPno=&no;
--輸出
DBMS_OUTPUT.PUT_LINE('雇員是:'||v_ename||'，他的薪水是：'||v_sal);
--異常處理
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('輸入有誤，請重新輸入！');
END;
/

案例
CREATE PROCEDURE a1(a1name VARCHAR2,a1sal NUMBER) IS
BEGIN
--執行,根據故原名修改工資
UPDATE EMP SET sal=a1sal WHERE ename=a1name; 
END;
/
EXEC a1('SCOTT',150);--這樣SCOTT的工資就被修改成了150

--采用function來查詢某個雇員的工資
CREATE FUNCTION fj_fun1(fjname VARCHAR2) 
RETURN NUMBER IS yearsal NUMBER(7,2);
BEGIN
--執行部分
SELECT sal*12+NVL(comm,0)*12 INTO yearsal FROM EMP WHERE ename=fjname;
RETURN yearsal;
END;
/
--調用
VAR abc NUMBER;
CALL  fj_fun1('SCOTT') INTO:abc;

--創建包。聲明該包有一個過程update_sal，聲明該包有一個函數annual_income：
CREATE PACKAGE fj_package IS
PROCEDURE update_sal(name VARCHAR2,newsal NUMBER);
FUNCTION annual_income(name VARCHAR2) return NUMBER;
END;
/
--給包sp_package實現包體 ，來修改某個雇員的工資或者查詢某個雇員的工資
CREATE OR REPLACE PACKAGE BODY fj_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; 
/ 
--調用
CALL fj_package.update_sal('SCOTT', 1400);

--輸入員工號，顯示雇員姓名、工資、個人所得稅，稅率為0.03為例
DECLARE 
c_tax_rate NUMBER(3,2):=0.03;
v_name VARCHAR2(5);--EMP.ename%TYPE
v_sal NUMBER(7,2);--EMP.sal%TYPE
v_tax_sal NUMBER(7,2);
BEGIN
--執行
SELECT ename,sal INTO v_name,v_sal FROM EMP WHERE EMPno=&no;
--計算個人所得稅
v_tax_sal:=v_sal*c_tax_rate;--注意冒號
--輸出
DBMS_OUTPUT.PUT_LINE('雇員是：'||v_name||'，工資是：'||v_sal||'，需要交的個人所得稅是：'||v_tax_sal);
END;
/

--類似於高級語言中的結構體
--PL/SQL記錄實例
DECLARE 
--表示要定義一個PL/SQL記錄類型 EMP_record_type，類型包含name,salary,title
TYPE EMP_record_type IS RECORD(
name EMP.ename%TYPE,
salary EMP.sal%TYPE,
title EMP.job%TYPE
);
--定義了一個變量，這個變量的類型是EMP_record_type
fj_record EMP_record_type;--v_name VARCHAR2(45);
BEGIN
SELECT ename,sal,job INTO fj_record FROM EMP WHERE EMPno=7788;
DBMS_OUTPUT.PUT_LINE('員工名：'||fj_record.name||',工資是：'||fj_record.salary);
END;
/

--相當於高級語言中的數組
DECLARE
--定義了一個PL/SLQL表類型，該類型是用於存放EMP.ename%TYPE
TYPE fj_TABLE_type IS TABLE OF EMP.ename%TYPE
INDEX BY BINARY_INTEGER;--代表下標是按整數排序的，可以為負數
fj_TABLE fj_TABLE_type;
BEGIN 
SELECT ename INTO fj_TABLE(0) FROM EMP WHERE EMPno=7788;
DBMS_OUTPUT.PUT_LINE('員工名：'||fj_TABLE(0));--下標跟上面的下標一樣
END;
/

DECLARE
--定義了一個PL/SLQL表類型，該類型是用於存放EMP.ename%TYPE
TYPE fj_TABLE_type IS TABLE OF EMP.ename%TYPE
INDEX BY BINARY_INTEGER;--代表下標是按整數排序的，可以為負數
fj_TABLE fj_TABLE_type;
BEGIN 
SELECT ename INTO fj_TABLE(0) FROM EMP;
DBMS_OUTPUT.PUT_LINE('員工名：'||fj_TABLE(0));--下標跟上面的下標一樣
END;
/

--輸入部門號，顯示該部門所有員工姓名和他的工資
DECLARE 
--定義游標類型fj_EMP_cursor
TYPE fj_EMP_cursor IS REF CURSOR;
--定義游標變量
test_cursor fj_EMP_cursor;
--定義變量
v_ename EMP.ename%TYPE;
v_sal EMP.sal%TYPE;
BEGIN
--執行
--把test_cursor和一個SELECT結合
OPEN test_cursor FOR SELECT ename,sal FROM EMP WHERE deptno=&no;
--循環取出
LOOP
FETCH test_cursor INTO v_ename,v_sal;
--判斷是否test_cursor是否為空
EXIT WHEN test_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('雇員名是：'||v_ename||',薪水'||v_sal);
END LOOP;
END;
/


--在上面的基礎上，如果某個雇員的工資低於200元，就增加100元
DECLARE 
--定義游標類型fj_EMP_cursor
TYPE fj_EMP_cursor IS REF CURSOR;
--定義游標變量
test_cursor fj_EMP_cursor;
--定義變量
v_ename EMP.ename%TYPE;
v_sal EMP.sal%TYPE;
BEGIN
--執行
--把test_cursor和一個SELECT結合
OPEN test_cursor FOR SELECT ename,sal FROM EMP WHERE deptno=&no;
--循環取出
LOOP
FETCH test_cursor INTO v_ename,v_sal;
--判斷工資高低，決定是否更新
--判斷是否test_cursor是否為空
EXIT WHEN test_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('雇員名是：'||v_ename||',薪水'||v_sal);
END LOOP;
END;
/	

--編寫一個過程，輸入雇員名，工資低於2000的雇員工資增加10%
CREATE OR REPLACE PROCEDURE fj_pro6(fjname VARCHAR2) IS
--定義
v_sal EMP.sal%TYPE;
BEGIN
--執行
SELECT sal INTO v_sal FROM EMP WHERE ename=fjname;
--判斷
IF v_sal<2000 THEN
UPDATE EMP SET sal=sal*1.1 WHERE ename=fjname;
END IF;
END;
/
--調用
EXEC fj_pro6('SCOTT');

--雇員如果補助為零則加200，如果不為零則加100
CREATE OR REPLACE PROCEDURE fj_pro7(fjname VARCHAR2) IS
--定義
v_comm EMP.comm%TYPE;
BEGIN
--執行
SELECT comm INTO v_comm FROM EMP WHERE ename=fjname;
--判斷
IF v_comm<>0 THEN
UPDATE EMP SET comm=comm+100 WHERE ename=fjname;
ELSE 
UPDATE EMP SET comm=comm+200 WHERE ename=fjname;
END IF;
END;
/
--調用
EXEC fj_pro7('SCOTT');

--三個條件分支 IF-THEN-ELSEIF-ELSE
--輸入雇員編號，如果該雇員的職位是PRESIDENT就給他的工資增加1000，如果該雇員的職位是MANAGER就給他的工資增加500，其它職位的雇員工資增加200。 
CREATE OR REPLACE PROCEDURE fj_pro8(fjno NUMBER) IS
--定義
v_job EMP.job%TYPE;
BEGIN
--執行
SELECT job INTO v_job FROM EMP WHERE EMPno=fjno;
IF v_job='PRESIDENT' THEN
UPDATE EMP SET sal=sal+1000 WHERE EMPno=fjno;
ELSIF v_job='MANAGER' THEN
UPDATE EMP SET sal=sal+500 WHERE EMPno=fjno;
ELSE
UPDATE EMP SET sal=sal+200 WHERE EMPno=fjno;
END IF;
END;
/
--調用
EXEC fj_pro8(7839);

--循環語句 LOOP
--users表中，用戶編號從1開始增加
CREATE TABLE users(
userno NUMBER,
username VARCHAR2(40)
);
CREATE OR REPLACE PROCEDURE fj_pro9(fjname VARCHAR2) IS
--定義:=表示賦值
v_num NUMBER:=1;
BEGIN
LOOP
INSERT INTO users VALUES(v_num,fjname);
--判斷退出條件
EXIT WHEN v_num=10;
--自增
v_num:=v_num+1;
END LOOP;
END;
/
--調用
EXEC fj_pro9('root1');
--這樣表中會有十個username為root1的元組

CREATE OR REPLACE PROCEDURE fj_pro10(fjname VARCHAR2) IS
--定義:=表示賦值
v_num NUMBER:=11;
BEGIN
WHILE v_num<=20 LOOP
INSERT INTO users VALUES(v_num,fjname);
v_num:=v_num+1;
END LOOP;
END;
/
--調用
EXEC fj_pro10('root1');