Oracle Database Cursor Usage Encyclopedia

Source: Internet
Author: User

SQL is the language used to access Oracle databases, and Pl/sql expands and strengthens the functionality of SQL, while introducing stronger program logic. Pl/sql supports DML commands and SQL transaction control statements. DDL is not supported in Pl/sql, which means that a table or any other object cannot be created in a PL/SQL program block. A better Pl/sql program is to execute DDL commands in Pl/sql blocks using built-in packages such as Dbms_sql or execute the Execute immediate command, pl/sql the compiler to guarantee object references and user permissions.

Here we discuss various DDL and TCL statements for accessing the Oracle database.

Inquire

The SELECT statement is used to query data from the database, and when the SELECT statement is used in Pl/sql, it is used with the INTO clause, and the return value of the query is given to the variable in the INTO clause, and the declaration of the variable is in Delcare. The SELECT into syntax is as follows:

SELECT [DISTICT|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 have more than one row of data, use an explicit cursor (we'll do the discussion of the cursor later), and in the INTO clause we want to have a variable with the same number of columns in the SELECT clause. INTO clause can also be a record variable.

%type Property

In Pl/sql, variables and constants can be declared as built-in or user-defined data types to refer to a column name while inheriting his data type and size. This dynamic assignment method is useful, such as the data type and size of the column referenced by the variable, and if the%type is used, the user does not have to modify the code, otherwise the code must be changed.

Cases:

v_empno SCOTT.EMP.EMPNO%TYPE;
v_salary EMP.SALARY%TYPE;

Not only the column names can use%type, but also variables, cursors, records, or declared constants can use%type. This is 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

The DML statements for other operational data are: INSERT, UPDATE, delete, and lock TABLE, which have the same syntax in Pl/sql as in SQL. We've discussed the use of DML statements before and we don't repeat them here. You can use any variable declared in the Declare section in a DML statement, and if it is a nested block, pay attention to the scope of the variable.

Cases:

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!');
 END

Related Article

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.