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. . SELECT INTO syntax is as follows: SELECT [DISTICT | All] {* | Column [, Column, ...]} Into (variable [, variable, ...] | record) from {table | (sub-query)} [alias ] Where .......... PL / SQL SELECT statement only returns a line 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 the column name can use% TYPE, but also the constant of variables, cursors, records, or declarations can use% TYPE. This is very useful for variables that define the same data type. DELCARE V_A Number (5): = 10; v_b v_a% Type: = 15; V_C v_A% Type; begin DBMS_OUTPUT.PUT_LINE ('v_a =' || v_a || 'v_b =' || v_b || 'v_c =' || v_c); END SQL> / V_A = 10 v_b = 15 v_c = PL / SQL Procedure SuccessFully Completed. SQL> Other DML Statements Other Actions DML statements are: INSERT, UPDATE, DELETE, and LOCK TABLE, these statements in PL The syntax in 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_deleted = SYSDATE WHERE empno = p_empno; EXCEPTION wHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ( 'Employee Number Not Found!'); results after performing the END DML statement when a DML statement, stored DML statement results In four cursor properties, these properties 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 values for SQL% Found and SQL% notfound before performing any DML statement, and after executing the DML statement, the attribute value of SQL% Found will be:. True: INSERT. TRUE Elete and Update At least one line is delete or update.. True: SELECT INTO at least returns a 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. 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; In the middle of the PL / SQL is a no declarge variable, cannot assign a values or in the expression. Example: DELCARE CURSOR C_EMP IS SELECT Empno, ENAME, SALARY from Emp Where; ........ begin does not have to have a view in the SELECT statement in the cursor definition can be a view, or from Columns selected in multiple tables or views can even use * to select all columns. Open the cursor using the value in the cursor, you should first open the cursor, open the cursor initialization query processing. The grammar of opening a cursor is: Open cursor_name cursor_name is a cursor name defined in the declaration section. Example: Open c_emp; Close Cursor Syntax: Close Cursor_Name Example: Close C_EMP; extract data from the cursor to get a row of data from the cursor to use 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 the corresponding, the type of variable is also the same.
Example: SET SERVERIUTPUT ON DECLARE v_ename EMP.ENAME% TYPE; v_salary EMP.SALARY% TYPE; CURSOR c_emp IS SELECT ename, salary FROM emp; BEGIN OPEN 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 ||); Close C_EMP; END This code is undoubtedly very troublesome, if there is a multi-line return result, you can use a loop and use the cursor property to end cycles. Conditions, in this way, the readability and simpleness of the program are greatly improved, and we use the loop to rewrite the program: SET Serveriutput on Declare v_ename Emp.Ename% Type; v_salary Emp.salary% Type CURSOR C_EMP IS SELECT ENAME, SALARY from Emp; Begin Open C_EMP; LOOP FETCH C_EMP INTO V_ENAME, V_SAlary; Exit when c_emp% Notfound; DBMS_OUTPUT.PUT_LINE ('Salary Of Employee' || V_ENA ME || 'Is' || v_salary; End Record Variable Defines a recording variable using type command and% ROWTYPE, please refer to relevant information about more information about% ROWSTYPE. 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 ON DECLARE R_emp EMP% ROWTYPE; CURSOR c_emp IS SELECT * FROM emp; BEGIN OPEN c_emp; LOOP FETCH 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;% rowType can also be defined by the trump name, such words must first declare the cursor: SET Serveriutput on Declare Cursor C_Emp is SELECT ENAME, salary FROM emp; r_emp c_emp% ROWTYPE; BEGIN OPEN c_emp; LOOP FETCH 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; END; cursor with parameters is similar to the storage procedure and function, can pass the parameters to the cursor and 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; defining the syntax of the parameter is as follows: parameter_name [in] Data_Type [{: = | default} value] Different from the storage process, Cursors can only accept values that are passed without returning values. 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 a text or variable.
Example: DECALRE CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR2) IS SELECT ename, salary FROM emp WHERE deptno = p_dept ORDER BY ename r_dept DEPT% ROWTYPE; v_ename EMP.ENAME% TYPE; v_salary EMP.SALARY % TYPE; v_tot_salary EMP.SALARY% TYPE; BEGIN OPEN c_dept; LOOP FETCH 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); loop fetch 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 times we are designing programs All follow the steps: 1, open the cursor 2, start loop 3, take the value from the cursor 4, check that one The row is returned 5, processing 6, shut down the loop 7, and 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 (parameter [, parameter] ...) | (query_difinition) loop statements end loop; let's override the above example with for loop: DECALRE CURSOR C_DEPT IS SELECT DePTNO , dname FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR2) IS SELECT ename, salary FROM emp WHERE deptno = p_dept ORDER BY ename v_tot_salary EMP.SALARY% TYPE; BEGIN FOR r_dept IN c_dept LOOP DBMS_OUTPUT.PUT_LINE ( 'Department:' | | R_dept.deptno || '-' || r_dept.dname); v_tot_salary: = 0; for r_emp in c_emp (r_dept.deptno) 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; use query in the cursor for loop can define queries in the cursor for loop, Since there is no explicit declaration, the cursor has no name, 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_dep T.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_tot_salary: = v_tot_salary v_salary; end loop; dbms_output.put.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 Emp WHERE DEPTNO NOT INAME! = '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. Syntax: for update [of [schema.] Table.column [, [schema.] Table.column] .. [noWait] In multi-table queries, use the OF clause to lock a specific table, if ignore the OF clause Then, the data rows 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 of the WHERE CURRENT OF substrs in Update and Delete is as follows: where {current of cursor_name | search_condition}: DELCARE CURSOR C1 IS SELECT Empno, Salary from Emp where Comm is Null for Update of Comm; V_comm Number (10, 2 Begin for r1 in c1 loop if r1.salary <500 Then v_comm: = r1.salary * 0.25; Elseif R1.SAlary <1000 Then v_comm: = r1.salary * 0.20; elseif r1.salary <3000 Then v_comm: = R1.SAlary * 0.15; Else v_comm: = r1.salary * 0.12; end if; Update Emp; SET COMM = v_comm where current of c1l; end loop; end Source: Jimijiao
