(window.webpackJsonp=window.webpackJsonp||[]).push([[2349],{2757:function(t,s,a){"use strict";a.r(s);var e=a(31),n=Object(e.a)({},(function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("ContentSlotsDistributor",{attrs:{"slot-key":t.$parent.slotKey}},[a("h1",{attrs:{id:"getting-started-with-plsql"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#getting-started-with-plsql"}},[t._v("#")]),t._v(" Getting started with plsql")]),t._v(" "),a("h2",{attrs:{id:"hello-world"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#hello-world"}},[t._v("#")]),t._v(" Hello World")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("set")]),t._v(" serveroutput "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("on")]),t._v("\n\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DECLARE")]),t._v("\n   message constant varchar2"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("32767")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'Hello, World!'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BEGIN")]),t._v("\n   dbms_output"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("put_line"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("message"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("END")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),t._v("\n\n")])])]),a("p",[t._v("Command "),a("code",[t._v("set serveroutput on")]),t._v(" is required in SQL*Plus and SQL Developer clients to enable the output of "),a("code",[t._v("dbms_output")]),t._v(". Without the command nothing is displayed.")]),t._v(" "),a("p",[t._v("The "),a("code",[t._v("end;")]),t._v(" line signals the end of the anonymous PL/SQL block. To run the code from SQL command line, you may need to type "),a("code",[t._v("/")]),t._v(" at the beginning of the first blank line after the last line of the code. When the above code is executed at SQL prompt, it produces the following result:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("Hello"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" World"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("!")]),t._v("\n\nPL"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SQL")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("procedure")]),t._v(" successfully completed"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("\n\n")])])]),a("h2",{attrs:{id:"definition-of-plsql"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#definition-of-plsql"}},[t._v("#")]),t._v(" Definition of PLSQL")]),t._v(" "),a("p",[t._v("PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).")]),t._v(" "),a("p",[t._v("The basic unit in PL/SQL is called a block, which is made up of three parts: a declarative part, an executable part, and an exception-building part.")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DECLARE")]),t._v("\n   "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),t._v("declarations section"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BEGIN")]),t._v("\n   "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),t._v("executable command"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("s"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\nEXCEPTION\n   "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),t._v("exception handling"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("END")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[a("strong",[t._v("Declarations")]),t._v(" - This section starts with the keyword DECLARE. It is an optional   section and defines all variables, cursors, subprograms, and other elements to be used in the program.")]),t._v(" "),a("p",[a("strong",[t._v("Executable Commands")]),t._v(" - This section is enclosed between the keywords BEGIN and END and it is a mandatory section. It consists of the executable PL/SQL statements of the program. It should have at least one executable line of code, which may be just a NULL command to indicate that nothing should be executed.")]),t._v(" "),a("p",[a("strong",[t._v("Exception Handling")]),t._v(" - This section starts with the keyword EXCEPTION. This section is again optional and contains exception(s) that handle errors in the program.")]),t._v(" "),a("p",[t._v("Every PL/SQL statement ends with a semicolon (😉. PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and END.")]),t._v(" "),a("p",[t._v("In anonymous block, only executable part of block is required, other parts are not nessesary. Below is example of simple anonymous code, which does not do anything but perform without error reporting.")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BEGIN")]),t._v("\n    "),a("span",{pre:!0,attrs:{class:"token boolean"}},[t._v("NULL")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("END")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),t._v(" \n\n")])])]),a("p",[t._v("Missing excecutable instruction leads to an error, becouse PL/SQL does not support empty blocks. For example, excecution of code below leads to an error:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BEGIN")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("END")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),t._v(" \n\n")])])]),a("p",[t._v("Application will raise error:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("END")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v("\nERROR at line "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2")]),t._v(":\nORA"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("06550")]),t._v(": line "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("column")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v(":\nPLS"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("00103")]),t._v(": Encountered the symbol "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"END"')]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("when")]),t._v(" expecting one "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("of")]),t._v(" the "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("following")]),t._v(":\n"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("begin")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("case")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("declare")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("exit")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("for")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("goto")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("if")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("loop")]),t._v(" mod "),a("span",{pre:!0,attrs:{class:"token boolean"}},[t._v("null")]),t._v(" pragma\nraise "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("return")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("select")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("update")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("while")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("with")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),t._v("an identifier"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),t._v("a "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("double")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v("quoted delimited"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v("identifier"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<")]),t._v("a bind variable"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("<<")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("continue")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("close")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("current")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("delete")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("fetch")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("lock")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("insert")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("open")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("rollback")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("savepoint")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("set")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("sql")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("execute")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("commit")]),t._v(" forall "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("merge")]),t._v(" pipe "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("purge")]),t._v("\n\n")])])]),a("p",[t._v('Symbol " * " in line below keyword "END;" means, that the block which ends with this block is empty or bad constructed. Every execution block needs instructions to do, even if it does nothing, like in our example.')]),t._v(" "),a("h2",{attrs:{id:"difference-between-type-and-rowtype"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#difference-between-type-and-rowtype"}},[t._v("#")]),t._v(" Difference between %TYPE and %ROWTYPE.")]),t._v(" "),a("p",[a("code",[t._v("%TYPE")]),t._v(": Used to declare a field with the same type as that of a specified table's column. "),a("br")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DECLARE")]),t._v("\n        vEmployeeName   Employee"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Name"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("%")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TYPE")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BEGIN")]),t._v("\n        "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" Name \n        "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INTO")]),t._v("   vEmployeeName\n        "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   Employee\n        "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v("  RowNum "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n        \n        DBMS_OUTPUT"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("PUT_LINE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("vEmployeeName"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("END")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),t._v("\n\n")])])]),a("p",[t._v("%ROWTYPE: Used to declare a record with the same types as found in the specified table, view or cursor (= multiple columns). "),a("br")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DECLARE")]),t._v("\n        rEmployee     Employee"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("%")]),t._v("ROWTYPE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BEGIN")]),t._v("\n        rEmployee"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Name :"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'Matt'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n        rEmployee"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Age :"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("31")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n        \n        DBMS_OUTPUT"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("PUT_LINE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("rEmployee"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n        DBMS_OUTPUT"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("PUT_LINE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("rEmployee"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Age"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("END")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("/")]),t._v("\n\n")])])]),a("h2",{attrs:{id:"about-plsql"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#about-plsql"}},[t._v("#")]),t._v(" About PLSQL")]),t._v(" "),a("p",[t._v('PL/SQL stands for Procedural Language extensions to SQL. PL/SQL is available only as an "enabling technology" within other software products; it does not exist as a standalone language. You can use PL/SQL in the Oracle relational database, in the Oracle Server, and in client-side application development tools, such as Oracle Forms. Here are some of the ways you might use PL/SQL:')]),t._v(" "),a("ol",[a("li",[t._v("To build stored procedures.     .")]),t._v(" "),a("li",[t._v("To create database triggers.")]),t._v(" "),a("li",[t._v("To implement client-side logic in your Oracle Forms application.")]),t._v(" "),a("li",[t._v("To link a World Wide Web home page to an Oracle database.")])]),t._v(" "),a("h2",{attrs:{id:"create-or-replace-a-view"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#create-or-replace-a-view"}},[t._v("#")]),t._v(" Create or replace a view")]),t._v(" "),a("p",[t._v("In this example we are going to create a view. "),a("br"),t._v("\nA view is mostly used as a simple way of fetching data from multiple tables. "),a("br")]),t._v(" "),a("p",[t._v("Example 1: "),a("br"),t._v("\nView with a select on one table.")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("OR")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("REPLACE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("VIEW")]),t._v(" LessonView "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v("\n       "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v("     L"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v("\n       "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("       Lesson L"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Example 2: "),a("br"),t._v("\nView with a select on multiple tables.")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("OR")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("REPLACE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("VIEW")]),t._v(" ClassRoomLessonView "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v("\n       "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v("     C"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Id"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" \n                  C"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" \n                  L"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Subject"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" \n                  L"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Teacher \n       "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("       ClassRoom C"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" \n                  Lesson L \n       "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v("      C"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Id "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" L"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("ClassRoomId"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("To call this views in a query you can use a select statement. "),a("br")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" LessonView"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" ClassRoomLessonView"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("h2",{attrs:{id:"create-a-table"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#create-a-table"}},[t._v("#")]),t._v(" Create a table")]),t._v(" "),a("p",[t._v("Below we are going to create a table with 3 columns. "),a("br"),t._v("\nThe column "),a("code",[t._v("Id")]),t._v(" must be filled is, so we define it "),a("code",[t._v("NOT NULL")]),t._v(". "),a("br"),t._v("\nOn the column "),a("code",[t._v("Contract")]),t._v(" we also add a check so that the only value allowed is 'Y' or 'N'. If an insert in done and this column is not specified during the insert then default a 'N' is inserted.")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TABLE")]),t._v(" Employee "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n        Id            NUMBER "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token boolean"}},[t._v("NULL")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n        Name          VARCHAR2"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("60")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n        Contract      "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("CHAR")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DEFAULT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'N'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token boolean"}},[t._v("NULL")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n        "),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---")]),t._v("\n        "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("CONSTRAINT")]),t._v(" p_Id "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("PRIMARY")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("KEY")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("Id"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n        "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("CONSTRAINT")]),t._v(" c_Contract "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("CHECK")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("Contract "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("IN")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'Y'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'N'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("h4",{attrs:{id:"remarks"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#remarks"}},[t._v("#")]),t._v(" Remarks")]),t._v(" "),a("p",[t._v("This section provides an overview of what plsql is, and why a developer might want to use it.")]),t._v(" "),a("p",[t._v("It should also mention any large subjects within plsql, and link out to the related topics.  Since the Documentation for plsql is new, you may need to create initial versions of those related topics.")])])}),[],!1,null,null,null);s.default=n.exports}}]);