PL/SQL, stored procedures, triggers, functions, packages (learning notes) in Oracle

what is PL/SQL?

PL/SQL (procedural language/sql) is an extension of Oracle's on-the-loop, which allows you to define constants and variables, allows you to use conditional and circular statements, and allows you to use exceptions to handle various errors. This makes it a very powerful feature. But transplant is not good.

1. Instance 1 includes only the PL/SQL block of the execution part

Set Serveroutput on;begindbms_output.put_line (' Asdasdasdas '); end;/

The Set serverout option is open for display.

Dbms_output the packages provided for Oracle

2. Instance 2 contains the input PL/SQL block

Declare v_name varchar2, v_sal number (7,2); Begin select Ename,sal into V_name,v_sal from EMP where empno=&no; Dbms_output.put_line (' Employee Name: ' | |  V_name); Dbms_output.put_line (' Salary: ' | |  V_sal);  End /

Description: The v_sal of the V_name variable and number of type VARCHAR2 are declared first, the search is performed in the PL/SQL statement block, the ename of the EMP table and the value of the sal field are searched by Empno,& No indicates that the EMPNO parameter is to be entered during execution. The ename value of the query is stored in the V_name, and Sal is placed in the V_sal and then displayed.

3. Example 3 PL/SQL block with exception handling

Declare v_name varchar2, v_sal number (7,2); Begin select Ename,sal into V_name,v_sal from EMP where empno=&no; Dbms_output.put_line (' Employee Name: ' | |  V_name); Dbms_output.put_line (' Salary: ' | | v_sal); Exceptionwhen No_data_found then Dbms_output.put_line (' input data wrong!  ‘);  End /

This example is exactly the same as instance 2, except for the exception handling section. This is a full PL/SQL block.

II. process (stored procedure)

Stored procedures are used to perform specific operations. When you establish a procedure, you can specify either an input parameter (in) or an output parameter (out). You can pass data to the execution section by using input parameters in the process, and you can pass the execution part of the data to the application environment by using the output parameters. Use the CREATE PROCEDURE command in the Sqlplus process.

CREATE PROCEDURE procedure_name (name Varchar2,sala number) isbegin update emp set Sal=sala where Ename=name;  End /

Description: Create a procedure named Procedure_name, which has 2 input parameters, and a plsql block between Sala,begin and end of the VARCHAR2 type name and number type.

This article is from the "Flying Fish Technology" blog, please be sure to keep this source http://flyingfish.blog.51cto.com/9580339/1583453

PL/SQL, stored procedures, triggers, functions, packages (learning notes) in Oracle