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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.