The PLSQL Beginning and Key Concepts

colorful pen marker art drawing school office supplies white table work desk business


Initial PL/SQL versions were not sequenced with the version of the database. For example, PL/SQL 1.0 shipped with the Oracle 6 Database. PL/SQL 2.x shipped with the Oracle 7.x Databases, beginning with Oracle 8, PL/SQL versions correspond to the database release numbers, like PL/SQL 11.1 in the Oracle 11g Release 1 Database.

In PL/SQL, C, C++, or Java. Java programs can be directly stored inside the Oracle 11g Database in all releases except the Oracle Express Edition.
  • PL/SQL is a structured programming.
  • PL/SQL supports dynamic datatypes by mapping them at run time against types defined in the Oracle 11g Database catalog. Matching operators and string delimiters means simplified parsing because SQL statements are natively embedded in PL/SQL programming units.
  • The PL/SQL run-time engine exists as a resource inside the SQL*Plus environment. The SQL*Plus environment is both interactive and callable. Every time you connect to the Oracle 11g Database, the database creates a new session. In that session, you can run SQL or PL/SQL statements from the SQL*Plus environment. PL/SQL program units can then run SQL statements or external procedures.
  • PL/SQL also supports building SQL statements at run time. Run-time SQL statements are dynamic SQL. You can use two approaches for dynamic SQL: one is Native Dynamic SQL (NDS) and the other is the DBMS_SQL package.
Simple PL/SQL structure
(Highlighted ones are mandatory)

[DECLARE]
  declaration_statements
BEGIN
  execution_statements
[EXCEPTION]
  exception_handling_statements
END;
/

What are stored functions
  • They use only for data retrieval. They cannot be used for DML operation.
  • Two kinds of exceptions in Stored procedures:
  • Curosrs - Again two types. One is implicit cursors and the second one is explicit cursors.

Comments

Popular posts from this blog

Top myths for NULL value in SQL Queries