SQL is a language used to access the Oracle database, PL / SQL extension, and strengthen SQL functionality, which introduces stronger program logic. PL / SQL supports the DML command and the SQL transaction control statement. DDL is not supported in PL / SQL, which means that the table or any other object cannot be created in the PL / SQL block. The preferred PL / SQL program is designed to perform DDL commands, and the PL / SQL compiler guarantees the DDL command, the PL / SQL compiler guarantees object references and user permissions to perform DDL commands. Below we will discuss various DDLs and TCL statements used to access the Oracle database. Query the SELECT statement to query the data from the database. When using the SELECT statement in PL / SQL, use the INTO clause with the INTO clause, the return value of the query is given the variable in the INTO clause, and the variable declaration is in DELCARE. . The SELECT INTO syntax is as follows:
SELECT [DATICT | All] {* | Column [, Column, ...]} INTO (variable [, variable, ...] | record) from {table | (sub-query)} [alias] Where ... .........
The SELECT statement in PL / SQL returns only one row of data. If you exceed a row of data, then use explicit cursors (discussions to the cursor will be done later), the INTO clause must have the same column number in the SELECT clause. The INTO clause can also be a record variable. % Type property can declare the variables and constants in the PL / SQL as built-in or user-defined data types to reference a column name while inheriting his data type and size. This dynamic assignment method is very useful, such as the data type and size of the columns referenced by the variable, if the% TYPE is used, then the user does not have to modify the code, otherwise the code must be modified. example:
v_empno scott.emp.empno% type; v_salary Emp.salary% TYPE;
Not only can the column names can use% TYPE, but also the constants of variables, cursors, records, or declarations can use% TYPE. This is very useful for variables that define the same data type.
DELCAREV_A NUMBER (5): = 10; v_b v_a% type: = 15; V_C v_A% Type; begindbms_output.put_line ('v_a =' || v_a || 'v_b =' || v_b || 'v_c =' || V_c); endsql> / v_a = 10 v_b = 15 v_c = PL / SQL Procedure SuccessFully Completed.SQL>
Other DML statements The DML statements of other operational data are: INSERT, UPDATE, DELETE, and LOCK TABLE, these statements are the same as the syntax in PL / SQL is the same as the syntax in SQL. We have discussed the use of DML statements in front of the DML statement. In the DML statement, any variables declared in the DECLARE section can be used if it is a nest block, then pay attention to the scope of the variable. example:
CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number) AS v_ename EMP.ENAME% TYPE; BEGIN SELECT ename INTO v_ename FROM emp WHERE empno = p_empno; INSERT INTO FORMER_EMP (EMPNO, ENAME) VALUES (p_empno, v_ename); DELETE FROM emp WHERE empno = p_empno; Update former_emp set date_delete = sysdate where Empno = p_empno; Exception When no_data_found the dbms_output.put_line ('Employee Number Not Found!'); END
The result of the DML statement When a DML statement is executed, the result of the DML statement is saved in four cursor properties, which are used to control the program flow or the status of the program. When running the DML statement, PL / SQL opens an internal cursor and processes the result. The cursor is a region in the memory of the results of the query. The cursor is turned on when the DML statement is running, and then shuts down after completion. Implicit cursor only uses SQL% FOUND, SQL% NOTFOUND, SQL% ROWCOUNT three properties. SQL% FOUND, SQL% notfound is a Boolean value, SQL% ROWCOUNT is an integer value. SQL% Found and SQL% NotFound are NULL before performing any DML statement, and after executing the DML statement, the attribute value of SQL% Found will be:. True: INSERT. True: delete and Update, at least one line is delete or update.. True: SELECT INTO At least one line When SQL% FOUND is true, SQL% Notfound is false. SQL% ROWCOUNT before performing any DML statement, the value of SQL% ROWCOUNT is null, for the SELECT INTO statement, if the execution is successful, the value of SQL% ROWCOUNT is 1, if there is no success, the value of SQL% ROWCOUNT is 0, and at the same time An exception NO_DATA_FOUND. SQL% Isopen SQL% isopen is a Boolean value. If the cursor is open, TRUE is true if the cursor is turned off, and SQL% isopen is always false for implicit cursors. This is because implicit cursors When the DML statement is executed, turn it off immediately when the end is ended. Transaction Control Statement Transactions is a logical unit that can include one or more DML statements that help users guarantee the consistency of data. If any of the DML statements in the transaction control logic fail, the entire transaction will roll back, users in PL / SQL can explicitly use COMMIT, ROLLBACK, SavePoint, and SET Transaction statements. CommT statement terminates the transaction, permanently saving the database change, simultaneously releases all LOCK, ROLLBACK terminates the current transaction release all Lock, but does not save any changes in the database, savePoint is used to set the intermediate point, when the transaction calls too much database operation, middle Point is very useful, set transaction is used to set transaction properties, such as read-write and isolation levels. Explicit Cursor When the query return result exceeds one line, you need an explicit cursor. At this time, the user cannot use the SELECT INTO statement. PL / SQL manages implicit cursor, when the query starts, the implicit cursor is opened, and the implicit cursor is automatically turned off. The explicit cursor declares in the declaration of the PL / SQL block, and the data is opened, the data is turned off in the execution section or the abnormality processing portion. The following table shows the difference between explicit cursors and implicit cursors: Table 1 Implicit cursor and explicit cursor implicit cursor explicit cursor PL / SQL maintenance, automatically open and close when the query is executed, explicitly defined in the program, open , Close, the cursor has a name. Cursor Properties Prefix is the prefix of SQL cursor attributes is that the cursor name property% isopen always has an INTO substring for false% isopen according to the status of the cursor, and only one line of data can handle multi-line data, set in the program. Take each row of data.
Using a cursor here to do a statement, what we said is usually referring to the strange cursor, so we have no special indication from now, and the cursors we say are all the strange cursors. To use a cursor in the program, you must first declare the cursor. Declaring Cursor Syntax: Cursor Cursor_Name Is Select_Statement;
The marking in the PL / SQL is a no declarge variable, which cannot assign a values or used in the expression. example:
Delcarecursor c_emp is select Empno, ENAME, SALYFROM Empwhere Salry> 2000Order by eName; ........ begin
In the cursor definition, it is not necessarily that the SELECT statement may be a view, or the columns selected from multiple tables or views can even select all columns. Open the cursor using the value in the cursor, you should first open the cursor, open the cursor initialization query processing. Opening the syntax of the cursor is:
Open cursor_name
Cursor_name is a cursor name defined in a declared part. example:
Open c_emp;
Close the cursor grammar:
Close Cursor_name
example:
Close C_EMP;
Extract data from the cursor from the cursor to get a line of data using the fetch command. After each extraction data, the cursor points to the next line of the result set. The syntax is as follows:
Fetch Cursor_name Into Variable [, variable, ...]
For each column of the cursor defined by SELECT, the FETCH variable list should have a variable corresponding to it, and the type of variable is also the same. example:
SET SERVERIUTPUT ONDECLAREv_ename EMP.ENAME% TYPE; v_salary EMP.SALARY% TYPE; CURSOR c_emp IS SELECT ename, salary FROM emp; BEGINOPEN c_emp; FETCH c_emp INTO v_ename, v_salary; DBMS_OUTPUT.PUT_LINE ( 'Salary of Employee' || v_ename || 'is' || v_salary); FETCH c_emp INTO v_ename, v_salary; DBMS_OUTPUT.PUT_LINE (' Salary of Employee '|| v_ename ||' is' || v_salary); FETCH c_emp INTO v_ename, v_salary; DBMS_OUTPUT.PUT_LINE ( 'Salary Of Employee '|| V_EName ||' Is' || V_SAlary; Close C_EMP; End
This code is undoubtedly very troublesome. If there is a multi-line return result, you can use the loop and use the cursor properties to end the cycle, in this way, the readability and simpleness of the program are greatly improved, below Use the loop to rewrote the program:
SET SERVERIUTPUT ONDECLAREv_ename EMP.ENAME% TYPE; v_salary EMP.SALARY% TYPE; CURSOR c_emp IS SELECT ename, salary FROM emp; BEGINOPEN c_emp; LOOPFETCH c_emp INTO v_ename, v_salary; EXIT WHEN c_emp% NOTFOUND; DBMS_OUTPUT.PUT_LINE ( 'Salary of Employee '|| v_ename ||' Is' || v_salary; End
Record variables Define a recording variable Use type commands and% ROWTYPE, for more information on% ROWSTYPE, see the relevant information. The record variable is used to extract data lines from the cursor. When the cursor chooses a lot of columns, then the record ratio is much easier to declare a variable for each column. When using% ROWTYPE in the table and puts the value taken from the cursor into the record, if you want to select all columns in the table, then use * in the SELECT clause to list all the column names. Example: SET SERVERIUTPUT ONDECLARER_emp EMP% ROWTYPE; CURSOR c_emp IS SELECT * FROM emp; BEGINOPEN c_emp; LOOPFETCH c_emp INTO r_emp; EXIT WHEN c_emp% NOTFOUND; DBMS_OUT.PUT.PUT_LINE ( 'Salary of Employee' || r_emp.ename || ' IS '|| r_emp.salary; end loop; close c_emp;
% RowType can also be defined by a tag name, such words must first declare the cursor:
SET SERVERIUTPUT ONDECLARECURSOR c_emp IS SELECT ename, salary FROM emp; R_emp c_emp% ROWTYPE; BEGINOPEN c_emp; LOOPFETCH c_emp INTO r_emp; EXIT WHEN c_emp% NOTFOUND; DBMS_OUT.PUT.PUT_LINE ( 'Salary of Employee' || r_emp.ename || ' IS '|| r_emp.salary; end loop; close c_emp;
The cursor with parameters is similar to the stored procedures and functions, and the parameters can be passed to the cursor and is used in the query. This is very useful for handling the situation of opening a cursor under certain conditions. Its syntax is as follows:
Cursor Cursor_name [(Parameter [, Parameter], ...)] is SELECT_STATEMENT;
The syntax of the definition parameter is as follows:
Parameter_name [in] Data_Type [{: = | default} Value]
Unlike the stored procedure, the cursor can only accept the value passed, and the value cannot be returned. The parameters only define the data type, no size. Alternatively, a default value can be set to the cursor without the parameter value, the default value is used. The parameters defined in the cursor are just a placeholder, and this parameter is not necessarily reliable elsewhere. Assign the value to the parameter when the cursor is opened, the syntax is as follows:
Open cursor_name [value [, value] ....];
The parameter value can be text or variable. example:
DECALRECURSOR c_dept IS SELECT * FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR2) ISSELECT ename, salaryFROM empWHERE deptno = p_deptORDER BY enamer_dept DEPT% ROWTYPE; v_ename EMP.ENAME% TYPE; v_salary EMP.SALARY% TYPE; v_tot_salary EMP.SALARY% TYPE; BEGINOPEN c_dept; LOOPFETCH c_dept INTO r_dept; EXIT WHEN c_dept% NOTFOUND; DBMS_OUTPUT.PUT_LINE ( 'Department:' || r_dept.deptno || '-' || r_dept.dname); v_tot_salary: = 0; OPEN c_emp (r_dept .deptno); LOOPFETCH c_emp INTO v_ename, v_salary; EXIT WHEN c_emp% NOTFOUND; DBMS_OUTPUT.PUT_LINE ( 'Name:' || v_ename || 'salary:' || v_salary); v_tot_salary: = v_tot_salary v_salary; END LOOP; CLOSE C_EMP; DBMS_OUTPUT.PUT_LINE ('Toltal Salary for Dept:' || V_tot_salary); end loop; close c_dept; end; Cursor for loop in most of us follow the steps below when designing program: 1, open the cursor 2 Start cycling 3, take the value from the cursor 4, check the row is returned 5, process 6, shut down the loop 7, close the cursor can be simply referred to as a cursor for loops. However, there is still a loop that is different from this type, which is for loop. The cursor for the for loop is declared in accordance with normal declarations. It has the advantage that there is no need to explicitly open, close, taking data, and test data. , Define variables that store data, and so on. The syntax for the cursor for loop is as follows: for record_name in (Corsor_name [(parameter [, parameter] ...) | (query_difinition) loopStatementSend loop; Let's override the above example with for loop:
DECALRECURSOR c_dept IS SELECT deptno, dname FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR2) ISSELECT ename, salaryFROM empWHERE deptno = p_deptORDER BY enamev_tot_salary EMP.SALARY% TYPE; BEGINFOR r_dept IN c_dept LOOPDBMS_OUTPUT.PUT_LINE ( 'Department:' || r_dept .deptno || '-' || r_dept.dname); v_tot_salary: = 0; for r_emp in c_emp (r_dept.deptno) loopdbms_output.put_line ('name:' || v_ename || 'Salary:' || v_salary; v_tot_salary: = v_tot_salary v_salary; end loop; dbms_output.put_line ('Toltal Salary for dept:' || v_tot_salary); end loop; end; use query in the cursor for loop can define queries in the cursor FOR loop, due to not display The cursor has no name, and the record name is defined by the cursor query. DECALRE v_tot_salary EMP.SALARY% TYPE; BEGIN FOR r_dept IN (SELECT deptno, dname FROM dept ORDER BY deptno) LOOP DBMS_OUTPUT.PUT_LINE ( 'Department:' || r_dept.deptno || '-' || r_dept.dname); v_tot_salary : = 0; FOR r_emp IN (SELECT ename, salary FROM emp WHERE deptno = p_dept ORDER BY ename) LOOP DBMS_OUTPUT.PUT_LINE ( 'Name:' || v_ename || 'salary:' || v_salary); v_tot_salary: = v_tot_salary v_salary; end loop; dbms_output.put_line ('Toltal Salary for Dept:' || V_tot_salary); end loop; end; the subquery syntax in the cursor is as follows:
Cursor c1 is select * from Empwhere Deptno Not in (Select Deptnofrom Deptwhere DName! = 'Accounting'); It can be seen that there is no difference in subqueries in SQL. Updates and deletes in the cursor are still available in PL / SQL to update or delete data lines using the Update and DELETE statements in PL / SQL. Explicit cursors are only used in the case where multi-line data is needed. PL / SQL provides a way to perform delete or update records only using a cursor. The WHERE CURRENT OF sub-string in the UPDATE or DELETE statement depends on the most recent data taken in the table to perform the Update or Delete operation. To use this method, you must use the for Update substring when the cursor is declared. When the conversation uses the for Update substring to open a camper, all the data rows that return to the set will be in a row-level, other objects. You can only query these data lines, you cannot perform Update, DELETE, or SELECT ... for Update operations. Grammar:
For update [of [schema.] Table.column [, [schema.] Table.column] .. [noWait] In multi-table queries, use the OF clause to lock a specific table, if ignored the OF classes, then The data lines selected in all tables will be locked. If these data lines have been locked by other sessions, Oracle will wait until the data line is unlocked until the data line is unlocked. The syntax using WHERE CURRENT OF substrs in Update and Delete is as follows: where {current of cursor_name | search_condition}:
DELCARECURSOR c1 IS SELECT empno, salaryFROM empWHERE comm IS NULLFOR UPDATE OF comm; v_comm NUMBER (10,2); BEGINFOR r1 IN c1 LOOPIF r1.salary <500 THENv_comm: = r1.salary * 0.25; ELSEIF r1.salary <1000 THENv_comm: = r1.salary * 0.20; elseif r1.salary <3000 Thenv_comm: = r1.salary * 0.15; Elsev_comm: = r1.salary * 0.12; end if; Update Emp; SET COMM = V_commwhere Current of C1L; end loop; end
