PL/SQLストアド・プロシージャおよびファンクション

ストアド プロシージャの概要

ストアド プロシージャは、いくつかのタスクを完了できるサブルーチンの一種で、スキーマ オブジェクトとしてデータベースに保存されます。これは、パラメータの受信または非受け入れをサポートし、パラメータ出力もサポートする名前付きのPL/SQLコード・ブロックです。ストアド プロシージャには通常、定義部分、実行部分、例外部分が含まれており、他のサブルーチンから呼び出すことも、再利用することもできます。
プロシージャ定義
CREATE [OR REPLACE] PROCEDURE プロシージャ名
[(引数名 [IN | OUT | IN OUT] 引数の種類)]
AS : 入力パラメータであり、デフォルト値が指定できることを示します。パラメータの型を省略した場合、デフォルトは in 型です
OUT: 出力パラメータを示します
IN OUT: 入力パラメータまたは結果を出力する出力パラメータとして使用できます
プロシージャ呼び出し
EXECUTE |CALL プロシージャ名 [(引数リスト) )]

例: JOB をパラメータとして受け取り、JOB の最高給与、最低給与、平均給与をクエリするプロセスを定義します。

プロシージャ query_sal(v_job in emp.job%type) を作成または置換します
as

v_min_sal emp.sal%type;

v_max_sal emp.sal%type;

v_avg_sal emp.sal%type;

begin
select min(sal) into v_min_sal from emp where job = v_job;
select max(sal) into v_max_sal from emp where job = v_job;
select avg(sal) into v_avg_sal from emp where job = v_job;
dbms_output.put_line('このジョブは最低賃金です' || v_min_sal);
dbms_output.put_line('このジョブの最高給与は ' || v_max_sal);
dbms_output.put_line('このジョブの平均給与は ' || v_avg_sal);
例外
no_data_found の場合
dbms_output.put_line('レコードが見つかりません');
end;

SQL> set serveroutput on
SQL> exec query_sal('営業マン');
この仕事の最低給与は 1250 です
この仕事の最高給与は 1600 です

これ仕事は平均給与 1400 です

PL/SQL プロシージャは正常に完了しました。

パラメータとその配信メソッド
プロシージャを確立するときに、渡されるパラメータはオプションです。パラメータ オプションを省略すると、プロシージャはパラメータなしのプロシージャになります (指定されません)。パラメータを定義する場合、呼び出し時にパラメータは必要ありません)。パラメータオプションが指定されている場合、プロセスはパラメータ化されたプロセスになります（定義時にパラメータ名、モード、およびデータ型を指定する必要があり、調整時に対応するパラメータ値を指定する必要があります）。定義時のパラメータは仮パラメータと呼ばれます。パラメータ、呼び出し時のパラメータは実パラメータと呼ばれます。

パラメータプロシージャはありません

プロシージャdisplay_systime

を作成または置換します
begin

dbms_output.put_line('Current Time is ' || sysdate);

end;
SQL> exec display_systime;
現在の時刻は24-FEB-13です

パラメータ化されたプロシージャ
定義するときは、パラメータの名前、モード、およびデータ型を指定する必要があります
例: レコードを追加するプロセスを定義します(すべての入力パラメータ)
プロシージャadd_empを作成または置換します

(

v_no in emp.empno% type,
v_name in emp.ename%type,
v_dept in emp.deptno%typedefault 20 —デフォルトの部門番号
)
as
begin
emp (empno,ename,deptno) に挿入 value(v_no,v_name,v_dept) ;
例外
when dup_val_on_index then
dbms_output.put_line('Record Exists');
end ;

SQL> exec add_emp(7369,'TEST',20); レコードを変更し、変更された結果を返します。名前と給料）。

プロシージャ ed_emp を作成または置換します
(

v_no in emp.empno%type、--in type 1 つと 2 つの out type パラメータを定義します

v_name out emp.ename%type,
v_sal out emp.sal%type)

as

begin

update emp set sal = sal + 100 where empno = v_no;

select ename,sal into v_name,v_sal from emp where empno = v_no;
例外
when no_data_found then
dbms_output.put_line('Not Data Found' );
end;
/
プロシージャが作成されました。

SQL>VARIABLE t_name varchar2(20);
SQL>VARIABLE t_salnumber;
SQL> exec ed_emp(7369,:t_name,:t_sal);
PL/SQL プロシージャが正常に完了しました。 SQL> print t_name
T_NAME
---------------------------------------- --- -------------------------------------
SMITH

SQL> print t_sal

T_SAL
--- -------
900
例: IN OUT 型パラメータの使用

プロシージャ comp を作成または置換します
(num1 in out number,num2 in out number)
as
v1 番号;
v2 番号;
begin
v1 := num1 + num2;
v2 := num1 * num2;
num1 := v1;
num2 := v2;
end;
SQL> var v1 番号
SQL> var v2 番号
SQL> exec := 3
PL/SQL プロシージャが正常に完了しました。
SQL> PL/SQL プロシージャが正常に完了しました。
SQL & GT Exec Comp (: v1,: v2); Print v1 v2
v1
; -------------------------------------------------- ---------- ----
15lSQL & GT; Exec Comp (: v1,: v2);
Pl/SQL プロシージャが正常に完了しました。
SQL & GT; Print v2
v1
--------
23
v2
----------
120

in out 型パラメータが入力パラメータと出力パラメータの両方として使用されていることがわかります。

ストアドプロシージャパラメータの転送方法:
位置による転送:
実パラメータを仮パラメータに順番に渡す

EXECUTE ED_EMP(7900,:t_name,:t_sal);

EXECUTE ED_EMP(8000,'TEST2',20 ) ;

名前で渡す

EXECUTE ED_EMP(v_name=>'ABCDE',v_dept=>10,v_no=>8003);
混合配信
EXECUTE ED_EMP(8005,v_dept=>20,v_name=>> ' テスト 5');

プロセス管理
システムプロセス情報の表示
DBA_OBJECTS
DBA_PROCEDURES
DBA_SOURCE
descprocedure_nameを使用してストアドプロシージャのパラメータ情報を表示
SQL>desc ed_emp;
PROCEDURE ed_emp
引数名 In/Out デフォルト?
------- ----------------------- ----------------------- ---- ------------_No Number (4) in
v_name varchar2 (10) out
v_sal number (7,2) OUT
DBA_Objects からの情報
SQL & GT; Object_namec、Object_namec t_type 、Status from dba_Objects Where Object_name = 'ED_EMP'
Owner Object_name Object_type ステータス
------------------------------------ -------------------------------------------------- --- --------------------------------------
スコット ED_EMP 手順は有効です
SQL & GT; user_procedures から object_name、procedure_name、interface、authid を選択します;
OBJECT_NAME PROCEDURE_NAME INT AUTHID
---------------------------- ----------------- --------------- ---------------
DISPLAY_SAL
SQL>select line, text from user_source where name='ED_EMP';
LINEテキスト
---------- ---------- ---------------- ---------------------------------- ---------------- ----
1 プロシージャ ED_EMP
2 (
3 v_no in Emp.empno%Type、
4 v_name out Emp.ename%Type、
5 v_sal out emp.sal%type
6)
7 as
8 begin
9 UPDATE emp SET sal=sal+100 WHERE empno=v_no;
10 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;
11 EXCEP TION
12 NO_DATA_FOUND の場合
13 DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD! ');
14エラーメッセージを表示する
エラーを表示する

関数の概要

関数は通常、特定のデータを返すために使用されます。その本質は名前付きの PL/SQL ブロックであり、スキーマ オブジェクトとしてデータベースに保存され、繰り返し実行できます。関数は通常、ストアド プロシージャの式またはパラメータとして呼び出され、戻り値を持ちます。

1. 関数を作成する構文
CREATE [ OR REPLACE ] FUNCTION function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datetype2,
...)
RETURN datatype
IS AS
[local_variable_declarations;... ]
BEGIN
--actions;
RETURN 式;
END [関数名];
関数作成時の注意事項
1. パラメーターのデータ型 (引数) を指定する場合、その長さは指定できません
2.関数ヘッダー句で指定するには、関数本体に少なくとも 1 つの return ステートメントが含まれている必要があります
3. in パラメーター、out パラメーター、および in out パラメーターを指定できます
4. パラメーターにはデフォルト値を指定できます。デフォルト値を指定する場合は、default キーワードを使用します。 arg1 varchar2 デフォルト 'SCOTT' など
関数を使用する利点:
1. コードの柔軟性が向上し、SQL だけでは完了できないタスクだけでなく、より複雑なタスクも完了できます。データをフィルタリングするための where サブ文で直接使用できます
3. ストアド プロシージャの補足であるストアド プロシージャのパラメータとして使用できます

パラメータなしで関数を作成します

関数 get_user を作成または置換します
return varchar2

as

v_user varchar2( 20);
begin
user_users から v_user にユーザー名を選択します;
return v_user;
end;

関数の戻り値を受け取るにはグローバル変数を使用します

SQL> var v1 varchar2(20) );

SQL> exec :v1 := get_user ;

PL/SQL プロシージャが正常に完了しました。

SQL> print v1
V1
------ ----------- -----
SCOTT
関数の戻り値を受け取るにはローカル変数を使用します

SQL>declare user_name varchar2(20);
5 end ;

6 /

現在のユーザー: SCOTT
PL/SQL プロシージャが正常に完了しました。

SQL ステートメントで関数を直接呼び出します

SQL> select get_user from Dual;
GET_USER
------ ------- -------------------------

SCOTT

dbms_output を使用して関数を呼び出します (この呼び出しはストアド プロシージャ呼び出しのパラメーターとして行われます)

SQL> ; set serveroutput on;

SQL> exec dbms_output.put_line('現在のユーザー: '||get_user);

パラメータを使用して関数を作成する

関数 raise_sal(varchar2 の名前)を作成または置換する
return number
as
new_sal emp.sal%type;

begin

select sal * 1.2 into new_sal from emp

where upper(ename) = upper(name);

return new_sal;
例外
when no_data_found then
raise_application_error(-20000, '現在の従業員は存在しません');
end;



SQL> select sal,raise_sal('SCOTT') from emp where ename='SCOTT';
SAL RAISE_SAL('SCOTT')
----- ----- ------------------
3000 3600

SQL> select sal,raise_sal('SCOTTT') from emp where ename ='SCOTT';
select sal,raise_sal('SCOTTT') from emp where ename='SCOTT'
RAISE_SAL"、11行目

outパラメーターを使用して関数を作成

関数get_infoを作成または置換

(name varchar2,titile out varchar2)

return varchar2
as
deptname dept.dname%type;
begin
select e.job, d.dname into titile,deptname
from emp e,dept d

where e.deptno = d.deptno

and upper(e.ename) = upper(name);
return deptname;
Exception
when no_data_found then
raise_application_error( -20000,'現在の従業員が存在しません');
end;
/

out パラメーターを使用する関数は、SQL ステートメントを使用して呼び出すことができないことに注意してください。代わりに、出力パラメータと関数の戻り値を受け取るように変数を定義する必要があります。
通話内容は以下の通りです

SQL> var job varchar2(20);
SQL> var dname varchar2(20);
SQL> exec :dname := get_info('scott',:job);
PL/SQL プロシージャが正常に完了しました。
SQL> dname ジョブの印刷
DNAME
------------------------------------------ -----
研究
仕事
------------------------------------------ --------
アナリスト
SQL> select get_info('scott') from Dual
2 ;
select get_info('scott') from Dual
*
1 行目のエラー:
ORA-06553: PLS-306: 'GET_INFO 呼び出しの引数の数または型が間違っています'

in outパラメータを持つ関数を作成または置換
関数compを作成または置換
(num1number,num2inoutnumber)
数値を返す
as
v_resultnumber(6);
v_remaindernumber;
begin
v_result := num1 * num2;
v_remainder := mod(num1,num2);
num2 := v_remainder;
return v_result;
例外
when zero_divide then
raise_application_error(-20000,'ゼロ除算');
end;
/

SQL> var n1 番号
SQL> var n2 番号
SQL> exec :n2 := 10;
PL/SQL プロシージャが正常に完了しました。
SQL> exec :n1 := comp(16,:n2);
PL/SQL プロシージャが正常に完了しました。
SQL> print n1 n2
N1
----------
160
N2
----------
6
関数の用および制限
1.関数の用(具体的な方法)上の例を参照)
a. 全ローカル量受信関数を使用する戻り値
b. ローカル量受信関数を使用する戻り値
c. SQL 句内で直接使用する関数
d. dbms_output を使用する関数数
注: 関数数在调用その場合、位置指定パラメータが必要であり、プロセス パラメータを保存していないため、関数の実行には制限が必要です。 2.関数は、SQL で使用される主な用途です。関数は必ず関数を返さなければならないため、表式の一部としてのみ使用できます。関数は SQL 句の以下の部分で使用できます。 b. コマンドの選択列表または子スクリプト中を選択します。 条件表达式 ここで、子句中
cを持つ。で接続し、で開始し、句で注文し、グループで子中
dで接続します。コマンドの値子句中
fを挿入します。 update コマンドの set 子句中
3.SQL における関数の制限
a. SQL 文中の句のみがサービス端末に保存されている関数を使用できますが、ゲスト端末に保存されている関数は使用できません。 SQL 文中の句で使用される関数は、入力パラメータ IN のみを使用できますが、出力パラメータ OUT および入出力パラメータ IN OUT は使用できません
c。 SQL 句で使用される関数は、サポートされている SQL の標準データ型のみを使用でき、ブール、テーブル、レコードなどの PL/SQL 固有の型は使用できません。 SQL 句中の関数に、insert、update、delete 句を含めることはできません
创建一张表tb_emp

SQL> create table tb_emp as select * from emp;
テーブルが作成されました。関数の作成または置換 del_emp
2 (数値なし)
3 数値を返す
4 as

5 v_sal emp.sal%type;

6 begin
7 select sal into v_sal from emp where empno = no;

8 delete tb_emp where empno = no;

9 return v_sal;
10 end;
11 /
関数が作成されました。

SQL语句调用時，收到了错误信息，内部查询内でDML操作を完了できません
SQL> select del_emp(7788) from Dual;
select del_emp(7788) from Dual
*
1 行目のエラー:
ORA-14551: クエリ内で DML 操作を実行できません
ORA-06512: "SCOTT.DEL_EMP" の行8

使用exec実行時関数数による成功

SQL> var n1 番号
SQL> exec :n1 := del_emp(7788);
PL/SQL プロシージャが正常に完了しました。
SQL> print n1
N1
----------
3000

関数の管理CE
USER_OBJECTS
USER_SOURCE


查看関数のソースコード

SQL>select line,text from user_source where name='DEL_EMP' order by line

LINEテキスト
---------------------------------------------- ------------ ------------------------
1 関数 del_emp
2 (数値なし)
3 数値を返す
4 as
5 v_sal emp.sal%type;
6 begin
7 select sal into v_sal from emp where empno = no;
8 delete tb_emp where empno = no s selected;

関数のパラメーター情報を表示します

SQL> desc del_emp;

FUNCTION del_emp RETURNS NUMBER

引数名型 --------------------- ------ --------

いいえ いいえ 機能
---------------------------------- ------------ -----
表达式被调用 只能表达式被调用
头部关字は手順戻り値の型を説明するための return キーワードは含まれていません。ヘッダーには return キーワードが含まれている必要があり、PL/SQL ブロックには少なくとも 1 つの有効な return ステートメントが含まれており、out または out の値を通じて AND を返すことができます。ヘッダー宣言内の同じ型を使用して in および out の値を返すこともできます。SQL ステートメントでは関数を呼び出すことができます。これらは主に、削除などのデータベース内の特定の操作を完了するために使用されます。 、更新、挿入、その他の DML 操作は、主に選択などの特定のデータに使用されます

。