Oracle帶輸入輸出參數的預存程序

標籤：SQ   current   in out   插入   complete   驗證   com   輸入輸出   ted   

（一）使用輸入參數
需求：在emp_copy中添加一條記錄，empno為已有empno的最大值+1，ename不可為空且長度必須大於0，deptno為60。
建立預存程序：

create or replace procedure insert_emp(emp_name in varchar2, dept_no in number) asbegin  declare max_empno number;  begin    if(emp_name is null or length(emp_name) = 0) then      return;    end if;    if(dept_no != 60) then      return;    end if;    select max(empno) into max_empno from emp_copy;    insert into emp_copy(empno, ename, deptno) values(max_empno + 1, emp_name, dept_no);  end;end insert_emp;/

調用預存程序並驗證：
(1)

SQL>execute insert_emp(‘Li Si‘, 60);PL/SQL procedure successfully completed.SQL>col empno format 99999;col ename format a15;col deptno format 99999;select empno, ename, deptno from emp_copy where deptno = 60; EMPNO ENAME           DEPTNO------ --------------- ------  7981 Li Si           60

(2)

SQL> execute insert_emp(‘‘, 6);PL/SQL procedure successfully completed.SQL> select empno, ename, deptno from emp_copy where deptno = 6;SQL> 

（二）使用輸出參數
需求：在上個需求的基礎上，要分別統計表emp_copy插入資料前後的記錄數。
建立預存程序：

create or replace procedure insert_emp(emp_name in varchar2, dept_no in number, original_count out number, current_count out number) asbegin  declare max_empno number;  begin    if(emp_name is null or length(emp_name) = 0) then      return;    end if;    if(dept_no != 60) then      return;    end if;    select count(1) into original_count from emp_copy;    select max(empno) into max_empno from emp_copy;    insert into emp_copy(empno, ename, deptno) values(max_empno + 1, emp_name, dept_no);    select count(1) into current_count from emp_copy;  end;end insert_emp;/

調用預存程序：

declare count1 number;        count2 number;begin  insert_emp(‘Wang Wu‘, 60, count1, count2);  dbms_output.put_line(‘Original count of table emp_copy is ‘ || count1);  dbms_output.put_line(‘Current count of table emp_copy is ‘ || count2);end;/Original count of table emp_copy is 15Current count of table emp_copy is 16PL/SQL procedure successfully completed.

（三）使用輸入輸出參數
in out參數綜合了上述兩種參數類型，既向過程體傳值，也被賦值而傳到過程體外。in out參數既可以用作輸入也可以用作輸出。
需求:實現兩數交換。
建立預存程序：

create or replace procedure swap(value1 in out number, value2 in out number) asbegin  value1 := value1 + value2;  value2 := value1 - value2;  value1 := value1 - value2;end swap;/

調用預存程序：

declare a number := 22;        b number := 33;begin  dbms_output.put_line(‘Before swap: a = ‘ || a || ‘, b = ‘ || b);  swap(a, b);  dbms_output.put_line(‘After swap: a = ‘ || a || ‘, b = ‘ || b);end;/Before swap: a = 22, b = 33After swap: a = 33, b = 22PL/SQL procedure successfully completed.

 

Oracle帶輸入輸出參數的預存程序