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