[DECLARE]
--declaration statements (optional)
BEGIN
--execution statements
[EXCEPTION]
--exception handling statements (optional)
END;​
CHAR, DATE, LONG, VARCHAR2, NUMBER, BOOLEAN are some examples of scalar data types.RECORD, TABLE, VARRAY, and so on.REF, CURSOR is an example of a reference data type.BLOB, BFILE, CLOB, NCLOB, and others are examples of large object data types.UPDATE table_name SET field=new_value WHERE CURRENT OF cursor_name% ROWTYPE is used when a query returns an entire row of a table or view.TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)%ROWTYPEsmpno,dept from smp;%ROWTYPECursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows. %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.FOR smp_rec IN C1 LOOP
totalsal = totalsal + smp_recsal;
ENDLOOP;​
EXCEPTION_INIT.DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT (exception_name, error_code);
BEGIN
// PL/SQL Logic
EXCEPTION
WHEN exception_name THEN
// Steps to handle exception
END;COMMIT command saves changes to a database permanently during the current transaction.ROLLBACK command is used at the end of a transaction to undo any modifications made since the start of the transaction.SAVEPOINT command saves the current point with a unique name.PL/SQL can be adjusted. When an error occurs, the program’s error handling code is included. Trigger’ means to activate. In PL/SQL, the trigger is a stored procedure that defines an action taken by the database when the database-related event is performed.DECLARE
num NUMBER(2);
sq NUMBER(3);
BEGIN
num:= &Number1;
sq := num*num;
DBMS_OUTPUT.PUT_LINE(‘Square:’ ||sq);
END;FUNCTION sqr (num IN NUMBER)
RETURN NUMBER is sq NUMBER(2);
BEGIN
sq:= num*num;
RETURN sq;
END;Data Definition Language(DDL) helps in the creation of a database structure or schema. CREATE, DROP, ALTER, RENAME, and TRUNCATE are the five types of DDL commands in SQL.ata Manipulation Language (DML) allows you to insert, change, and delete data from a database instance. It’s incharge of making all kinds of changes to a database’s data. The database application and the user can insert data and information using three basic commands which are: INSERT, UPDATE, DELETE.GRANT and REVOKE are commands in the DCL (Data Control Language) that can be used to grant “rights and permissions.” The database system’s parameters are controlled by other permissions.Transaction Control Language(TCL), deal with database transactions. COMMIT, ROLLBACK, SAVEPOINT are the commands of TCL.Data Query Language (DQL). It just has one command, which is SELECTDBMS_APPLICATION_INFODBMS_TRACEDBMS_SESSION and DBMS_MONITORtrcsess and tkprof utilitiesraise_sal(emp_num, merit+ amount);emp_num and amount are the two actual parameters.PROCEDURE raise_sal( emp_id INTEGER) IS
curr_sal REAL:
………..
BEGIN
SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;
…….
END raise_sal;
PL/SQL raises NO_DATA_FOUND when there are no rows returned upon a select operation, and if more than one row is returned using a select statement, TOO_MANY_ROWS error is generated. Some more examples :COLLECTION_IS_NULL : when a collection is nullCURSOR_ALREADY_OPEN : When a cursor is already openLOGIN_DENIED : When login is incorrect or permission is not thereMERGE INTO orders o
USING customer c
ON (o.cust_id = c.cust_id)
WHEN MATCHED THEN
UPDATE SET o.del_address = c.address
WHEN NOT MATCHED THEN
INSERT (cust_id, address)
VALUES (c.emp_id, c.address);SELECT SYSDATE FROM dual;SELECT id, TO_CHAR(SYSDATE, 'yyyy/mm/dd') from InterviewBitEmployeeTable where customer_id < 200;SELECT USER FROM dual;SYS.ALL_DEPENDENCIES is used for describing all the dependencies between procedures, packages, triggers, functions that are accessible to the current user. It returns the columns like name, dependency_type, type, referenced_owner etc. DBMS_OUTPUT prints the output to the standard console.DBMS_DEBUG prints the output to the log file.SQL%FOUND : This returns TRUE if at least one row has been processed.SQL%NOTFOUND : This returns TRUE if no rows were processed.SQL%ISOPEN : This checks whether the cursor is open or not and returns TRUE if open.SQL%ROWCOUNT : This returns the number of rows processed by the DML statement.PL/SQL :DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE. PL/SQL packages have the following two parts :EXECUTE or [EXEC] procedure_name;procedure_name; PL/SQL to manipulate, compare, and calculate data. An expression is made up of two parts : operators and operands.+’, ‘*’, etc.SELECT is parsed.SET SERVER OUTPUT ON
DECLARE
BEGIN
FOR i IN REVERSE 1..99
LOOP
IF Mod(i,3) = 0 THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
/ – .DECLARE
num NUMBER(2); — it is a local variable.
BEGIN/* and ends with */.BEGIN
num := &p_num; /* This is a host variable used in program body */
……….
END
DECLARE
message VARCHAR2(255):= 'Welcome to PL/SQL';
byzero NUMBER;
BEGIN
DBMS_OUTPUT.put_line (message);
byzero := 1/0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;SQLCODE and SQLERRM are used to trace exceptions that are not explicitly handled in the program. These are globally defined variables. SQLCODE returns the error code while SQLERRM returns the corresponding error message. ACCEPT keyword to take inputs from the user. Example –ACCEPT age number prompt ‘Enter your age :’CREATE PROCEDURE get_customer_details @age nvarchar(30), @city nvarchar(10)
AS
BEGIN
SELECT * FROM customers WHERE age = @age AND city = @city;
END;NVL (exp1, exp2) .If exp1 is null then the NVL function returns the value of exp2.nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)NULLIF (exp1, exp2)COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternative values.IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function. COUNT (*) : Returns a number of rows in a table including duplicates rows and rows containing null values in any of the columns.COUNT (EXP) : Returns the number of non-null values in the column identified by expression.COUNT (DISTINCT EXP) : Returns the number of unique, non-null values in the column identified by expression.SELECT statement that is embedded in a clause of another SELECT statement. A subquery can be placed in WHERE HAVING and FROM clause.DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from smp where smpno in (10,12,14,16);CASE is directly used in PL SQL, but DECODE is used in PL/SQL through SQL only.OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package..Unambiguous logic must be avoided whilst overloading is being done. pragma_exception_init command in PL/SQL instructs the compiler to associate an exception name with an Oracle error number. This enables you to refer to any internal exception by name and create a custom handler for it. DMS_OUTPUT package. get_line, put_Line, new_line, and many more are found in DBMS_OUTPUT. The put_line procedure, which is a part of the DBMS_OUPUT package, is used to display the information in the line. PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another modulefcopy procedure”, you can copy the complete contents of one file into another file. While to copy the contents of a file directly into a PL/SQL table, you can use the program “file2pstab”. PL/SQL table are the closest to arrays in PL/SQL, and in order to access this table you have to first declare a table type, and then you have to declare PL/SQL table itself. But by using PLVtab, you can avoid defining your own PL/SQL table type and make PL/SQL data-table access easy. two packages that help you manage transaction processing in PL/SQL application. It is PLVcmt and PLVrb.‘1’ and Maximum size is ‘4000’1’and Maximum size is ‘2000’Scale” can range from -84 to 127.ON DELETE CASCADE Indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted. ON DELETE SET NULL Covert foreign key values to null when the parent value is removed. Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.