oracle 預存程序參數介紹 in ，out ， in out 以及 執行(二)

標籤：oracle   output   code   修改   命令列   from   arch   混合   nextval   

預存程序參數
1.IN DEFAULT (預設)
2.OUT
3.IN OUT 可修改

形參和實參
-- 形參

1 CREATE PROCEDURE raise_sal(ID NUMBER ,sal NUMBER) 2 IS3 BEGIN ...4 END raise_sal;

-- 實參

1 emp_id := 100;2 raise_sal(emp_id,2000);

 

-- in參數類型示範  薪水漲幅 percent

 1 CREATE OR REPLACE PROCEDURE raise_salary 2 ( 3    ID IN employees.employee_id%TYPE , 4    PERCENT IN NUMBER 5 ) 6 IS 7 BEGIN 8   UPDATE employees SET salary = salary * (1 + PERCENT/100) WHERE employee_id = ID; 9 END raise_salary;10 

-- 結果驗證

1 SELECT * FROM employees WHERE employee_id = 206;2 -- PL/SQL中執行3 BEGIN4 raise_salary(206,10);5 END;6 EXECUTE raise_salary(206,10); -- SQL命令列執行

 

-- out參數類型示範   查詢指定員工的名字和薪水

 1 CREATE OR REPLACE PROCEDURE query_emp  2 ( 3     ID IN employees.employee_id%TYPE, 4     NAME OUT employees.last_name%TYPE, 5     salary OUT employees.salary%TYPE 6 ) 7 IS  8 BEGIN 9   SELECT last_name,salary INTO NAME,salary FROM employees WHERE employee_id = ID;10 END;

 

--結果示範

 1 -- 在命令列執行 2 SET serveroutput ON; 3  4 DECLARE 5   emp_name employees.last_name%TYPE; 6   emp_sal employees.salary%TYPE; 7 BEGIN 8   query_emp(206,emp_name,emp_sal); 9   dbms_output.put_line(‘name :‘ || emp_name );10   dbms_output.put_line(‘salary :‘ || emp_sal );11 END;12 13 -- 命令列執行(二)14 VARIABLE NAME VARCHAR2(25)15 VARIABLE sal NUMBER16 EXECUTE query_emp(206,:NAME,:sal);

 

-- in out 型別參數
-- 參數傳輸方式

1.位置傳輸
2.名字傳輸 NAME=>VALUE
3.混合方式(1,2)

 1 CREATE OR REPLACE PROCEDURE add_dept 2 ( 3     NAME IN departments.department_name%TYPE, 4     loc IN departments.location_id%TYPE 5 ) 6 IS 7 BEGIN 8   INSERT INTO departments (department_id,department_name,location_id) 9   VALUES10   (departments_seq.nextval,NAME,loc);11   COMMIT;12 END add_dept;

 

--結果驗證 

1 --執行2 EXECUTE add_dept(‘training‘,2500);3 --驗證4 SELECT * FROM departments WHERE location_id= 2500;5 --執行方式二6 EXECUTE add_dept(loc=>2400,NAME=>‘education‘);7 --驗證8 SELECT * FROM departments WHERE location_id= 2400;

 

-- 使用預設值定義
-- NAME departments.department_name%TYPE = ‘UNKNOW‘,
-- loc departments.location_id%TYPE = 1800

 

 1 CREATE  OR  REPLACE PROCEDURE add_dept 2 ( 3     NAME departments.department_name%TYPE := ‘UNKNOW‘, 4     loc  departments.location_id%TYPE := 1800 5 ) 6 IS 7 BEGIN 8   INSERT INTO departments (department_id,department_name,location_id) 9   VALUES10   (departments_seq.nextval,NAME,loc);11   COMMIT;12 END add_dept;

 

-- 結果示範

 

1 --執行2 EXECUTE add_dept();3 --驗證4 SELECT * FROM departments WHERE location_id= 1800;

 

oracle 預存程序參數介紹 in ，out ， in out 以及 執行(二)