PL/SQL Practice named blocks: stored procedures, functions, triggers, packages

Define Name 2 at creation time, can be saved by Oracle Server 3, can be called by any program 4, can be shared

Stored procedures:

1. Stored Procedures without parameters:

Sql> Create or Replace procedureProc1 is  2    3V_ename Emp.ename%type; 4V_sal Emp.sal%type; 5    6    begin  7        SelectEname,sal intoV_ename,v_sal fromEmpwhereEmpno=&No; 8Dbms_output.put_line ('Name is:'||V_ename||' , '||'Salary is:'||v_sal); 9ExceptionTen         whenNo_data_found Then  OneDbms_output.put_line ('You are not crrect, please input again!');  A         whenOthers Then  -Dbms_output.put_line ('Others Error!');  -    End;

SQL>exec proc1;         // Execute stored Procedure

Sql> Create or Replace procedureProc1 is  2    3V_ename Emp.ename%type; 4V_sal Emp.sal%type; 5    6    begin  7        SelectEname,sal intoV_ename,v_sal fromEmpwhereEmpno=&No; 8Dbms_output.put_line ('Name is:'||V_ename||' , '||'Salary is:'||v_sal); 9ExceptionTen         whenNo_data_found Then  OneDbms_output.put_line ('You are not crrect, please input again!');  A         whenOthers Then  -Dbms_output.put_line ('Others Error!');  -    End;

View procedure information through a data dictionary:

Stored procedures with parameters: parameter definitions, in, out, and out represent three different modes of the parameter:

In: When a stored procedure is called, the parameter of the pattern receives the value of the corresponding argument and is read-only, which cannot be modified.   Default is in. Out: The formal parameter is considered to be writable and can be assigned only. Its value cannot be read in a stored procedure. When returned, passes the parameter value to the corresponding argument.

In out: both allow

①in

Sql> Create or Replace procedureins_dept2(V_deptnoinch  Number, V_dnamevarchar2, V_locinch varchar2) is  3    4E_dept_err exception; 5pragma exception_init (E_dept_err,-0001); 6  begin  7    Insert  intoDeptValues(V_deptno,v_dname,v_loc); 8    Commit; 9ExceptionTen     whenE_dept_err Then  OneDbms_output.put_line ('You Deptno are not unique, please input unique DEPTNO number!');  A     whenOthers Then  -Dbms_output.put_line ('Others Error!');  -  End;

②out:

Sql> Create or Replace procedureproc22(V_empnoinch  Number, V_ename outvarchar2, v_sal out Number)  3   is  4    5  begin  6    7    SelectEname,sal intoV_ename,v_sal fromEmpwhereEmpno=V_empno; 8    9Dbms_output.put_line ('Employee name is:'||v_ename); Ten    OneDbms_output.put_line ('Employee Salary is:'||v_sal);  AException -      whenNo_data_found Then  -Dbms_output.put_line ('Employee ID is error!');  the      whenOthers Then  -Dbms_output.put_line ('Others Error!');  -  End;

running under the system: SQL> varNamevarchar2(Ten); SQL> varSal Number; SQL> execPROC2 (7369,: name,:sal);P l/Sqlproceduresuccessfully completedname---------Smithsal---------6800Run via Plsql block: SQL> Declare  2V_name Emp.ename%type; 3V_sal Emp.sal%type; 4      begin  5PROC2 (7369, v_name,v_sal); 6      End;

3.in-out:

Sql> Create or Replace procedurePROC32(V_empnoinchOut Number, V_ename outvarchar2, v_sal out Number)  3   as  4  begin  5    SelectEmpno,ename,sal intoV_empno,v_ename,v_sal fromEmpwhereEmpno=V_empno; 6Dbms_output.put_line ('Employee ID is:'||v_empno); 7Dbms_output.put_line ('Employee name is:'||v_ename); 8Dbms_output.put_line ('Employee Salary is:'||v_sal); 9ExceptionTen        whenNo_data_found Then  OneDbms_output.put_line ('Employee ID is error!');  A        whenOthers Then  -Dbms_output.put_line ('Others Error!');  -  End;  the  /Procedurecreated executing stored procedures: SQL> Declare  2V_empno Emp.empno%type; 3V_ename Emp.ename%type; 4V_sal Emp.sal%type; 5    6  begin  7V_empno:= &N; 8proc3 (v_empno,v_ename,v_sal); 9   Ten  End;

PL/SQL Practice named blocks: stored procedures, functions, triggers, packages