PLSQL Notes & OCI 2 PDF

Oracle Apex - SQL Workshop - SQL Commands - Part 2 Language change. Copy and Run ---- if else code Copy and Run ---- while code Oracle PLSQL - Introduction PL/SQL Course Link - https://mylearn.oracle.com/ou/exam/oracle-database-program-with-plsql-1z0- 149/38560/110643/170351 DML, Transaction C...

Oracle Apex - SQL Workshop - SQL Commands - Part 2 Language change. Copy and Run ---- if else code Copy and Run ---- while code Oracle PLSQL - Introduction PL/SQL Course Link - https://mylearn.oracle.com/ou/exam/oracle-database-program-with-plsql-1z0- 149/38560/110643/170351 DML, Transaction Control commands - Static SQL DDL commands - Dynamic SQL SET SERVEROUTPUT ON - FIRST EXECUTE this. DECLARE v_myName VARCHAR2(20):= 'John'; BEGIN v_myName := 'Steven'; DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName); END; Delimiters- DECLARE v_event VARCHAR2(15); BEGIN v_event := q'!Father's day!'; -----------------------------< here all types of special characters will work. !@#$[]---> DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is : '|| v_event ); v_event := q'[Mother's day]'; -----------------------------< here all types of special characters will work. !@#$[]---> DBMS_OUTPUT.PUT_LINE('2nd Sunday in May is : '|| v_event ); END;  TRUE represents a Boolean value. 15-JAN-09 represents a DATE. The image represents a BLOB. The text in the callout can represent a VARCHAR2 data type or a CLOB. 256120.08 represents a NUMBER data type with precision and scale. The film reel represents a BFILE. The city name Atlanta represents a VARCHAR2 data type. ----------------------------------------------------------------------------------------------------------------------------- ------------- ------------------ Boolean- DECLARE flag BOOLEAN := FALSE; BEGIN flag := TRUE; END; / DECLARE v_outer_variable VARCHAR2(20):='GLOBAL VARIABLE'; BEGIN DECLARE v_inner_variable VARCHAR2(20):='LOCAL VARIABLE'; BEGIN DBMS_OUTPUT.PUT_LINE(v_inner_variable); DBMS_OUTPUT.PUT_LINE(v_outer_variable); END; DBMS_OUTPUT.PUT_LINE(v_outer_variable); END; DECLARE v_fname VARCHAR2(25); BEGIN SELECT first_name INTO v_fname FROM employees WHERE employee_id=4; DBMS_OUTPUT.PUT_LINE(' First Name is : '||v_fname); END; / INTO Clause- The INTO clause is mandatory and occurs between the SELECT and FROM clauses. It is used to specify the names of variables that hold the values that SQL returns from the SELECT clause. DECLARE v_emp_lastname employees.last_name%TYPE; v_emp_dept employees.department_id%TYPE; BEGIN SELECT last_name , department_id INTO v_emp_lastname, v_emp_dept FROM employees WHERE employee_id = 4; DBMS_OUTPUT.PUT_LINE ('Last name is :'||v_emp_lastname); DBMS_OUTPUT.PUT_LINE ('Dept Id is :'|| v_emp_dept); END; / select * from employees; %type- it will inherit the datatype/ take the same datatype as column datatype in the table. INSERT and INSERT ALL- BEGIN INSERT INTO employee (EMP_ID, EMP_NAME, EMP_SALARY) VALUES(4, 'Suraj', 500000); END; / select * from employee; BEGIN INSERT ALL INTO employees(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID )VALUES(88, 'FNAME1', 'LANME1' , 88) INTO employees(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID )VALUES(99, 'FNAME2', 'LANME2' , 85) SELECT * from dual; END; / select * from employees; UPDATE- DECLARE sal_increase employee.EMP_SALARY%TYPE := 80000; BEGIN UPDATE employee SET EMP_SALARY = EMP_SALARY + sal_increase WHERE EMP_ID = 3; END; / select * from employee; DELETE- DECLARE deptno employees.department_id%TYPE := 15; BEGIN DELETE FROM employees WHERE department_id = deptno; END; / SELECT * from employees; MERGE- BEGIN MERGE INTO employees a USING employee_updates b ON (a.employee_id = b.employee_id) WHEN MATCHED THEN UPDATE SET a.first_name = b.first_name, a.last_name = b.last_name, a.department_id = b.department_id WHEN NOT MATCHED THEN INSERT (employee_id, first_name, last_name, department_id) VALUES (b.employee_id, b.first_name, b.last_name, b.department_id); COMMIT; END; %ROWTYPE- In order to save entire row record use %rowtype. DECLARE v_employee employees%ROWTYPE; BEGIN SELECT * INTO v_employee FROM employees where EMPLOYEE_ID = 99; DBMS_OUTPUT.PUT_LINE(v_employee.EMPLOYEE_ID || ' ' || v_employee.first_name || ' ' || v_employee.last_name); END; select * from employees; Oracle PLSQL - IF-ELSE , ElseIf ELSE Part---- ELSE IF Part---- IF condition THEN -- Statements to execute if the condition is true ELSIF condition THEN -- Statements to execute if this condition is true... ELSIF condition THEN -- Statements to execute if this condition is true ELSE -- Statements to execute if none of the above conditions are true END IF; DECLARE x NUMBER := 10; BEGIN IF x > 10 THEN DBMS_OUTPUT.PUT_LINE('x is greater than 10'); ELSIF x = 10 THEN DBMS_OUTPUT.PUT_LINE('x is equal to 10'); ELSE DBMS_OUTPUT.PUT_LINE('x is less than 10'); END IF; END; Oracle PLSQL - Basic Loop SET SERVEROUTPUT ON; LOOP -- Statements to execute repeatedly -- You must include an EXIT statement to break out of the loop at some point EXIT WHEN condition; -- Exit the loop when the condition is true END LOOP; DECLARE x NUMBER := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('Value of x: ' || x); x := x + 1; EXIT WHEN x > 5; -- Exit the loop when x is greater than 5 END LOOP; END; This code will output the value of x from 1 to 5. -------------------------------------------------------------------------------------------------- DECLARE x NUMBER := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('Value of x: ' || x); x := x + 1; EXIT WHEN x >= 5; END LOOP; END; This code will output the value of x from 1 to 4. Oracle PLSQL - While Loop EXIT When ------ WHILE condition LOOP -- Statements to execute as long as the condition is true END LOOP; DECLARE x NUMBER := 1; BEGIN WHILE x Here's an example of using an implicit cursor to fetch data from a table: DECLARE -- Declare variables to hold fetched data v_employee_id employees.employee_id%TYPE; v_first_name employees.first_name%TYPE; v_last_name employees.last_name%TYPE; BEGIN -- Implicit cursor created by SELECT statement FOR emp_rec IN (SELECT employee_id, first_name, last_name FROM employees) LOOP -- Assign fetched data to variables v_employee_id := emp_rec.employee_id; v_first_name := emp_rec.first_name; v_last_name := emp_rec.last_name; -- Display the fetched data DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name); END LOOP; END; / In this example: We declare three variables v_employee_id, v_first_name, and v_last_name to hold the fetched data. We use a SELECT statement within a FOR loop to fetch data from the employees table. This automatically creates an implicit cursor. Inside the loop, we assign the fetched data to variables and then display it using DBMS_OUTPUT.PUT_LINE. Implicit cursors are convenient for simple queries where you don't need explicit control over cursor operations. However, for more complex scenarios or when you need to perform operations like opening, fetching, and closing explicitly, explicit cursors are more suitable. IMPLICIT CURSOR----------------another example. DECLARE -- Declare variables to hold fetched data v_rows VARCHAR2(30); v_employee_id employees.employee_id%TYPE; v_first_name employees.first_name%TYPE; v_last_name employees.last_name%TYPE; BEGIN -- Implicit cursor created by SELECT statement FOR emp_rec IN (SELECT employee_id, first_name, last_name FROM employees) LOOP -- Assign fetched data to variables v_employee_id := emp_rec.employee_id; v_first_name := emp_rec.first_name; v_last_name := emp_rec.last_name; v_rows := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE(v_rows || ' is row count'); -- Display the fetched data DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name ); END LOOP; END; / ------------------------------------------------------- ORDER BY----- DECLARE -- Declare variables to hold fetched data v_employee_id employees.employee_id%TYPE; v_first_name employees.first_name%TYPE; v_last_name employees.last_name%TYPE; -- Declare cursor CURSOR employee_cursor IS SELECT employee_id, first_name, last_name FROM employees ORDER BY employee_id asc ; BEGIN -- Open the cursor OPEN employee_cursor; -- Fetch data from the cursor into variables LOOP FETCH employee_cursor INTO v_employee_id, v_first_name, v_last_name; EXIT WHEN employee_cursor%NOTFOUND; -- Exit loop when no more rows found -- Display the fetched data DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name); END LOOP; -- Close the cursor CLOSE employee_cursor; END; / OCI - Oracle Cloud Infrastructure OCI Course Video Link - https://mylearn.oracle.com/ou/course/getting-started-with-oracle-cloud- infrastructure/116049/183024 Physical Machine VS Virtual Machine - to understand the concept of the cloud. Accessibility - Space (physical hardware)- Time, other efforts to maintain physical machines- Updates for MS Patches,.net patches, Security related, anti-virus related updates- Our complete work is with systems- Advantages of VM/ having IT Infrastructure on cloud- Lower Operational Costs: Reduces the costs associated with maintaining and upgrading hardware, managing data centers, and employing large IT teams. Global Reach: Cloud services are available globally, enabling organizations to deploy applications and services in multiple regions to reach users worldwide. On-Demand Resources: Easily scale resources up or down based on demand, allowing businesses to handle peak loads and scale back when demand decreases. High Availability: Cloud providers offer high availability through redundant infrastructure and data centers across different geographical locations. Disaster Recovery: Cloud services often include built-in disaster recovery solutions, ensuring data is backed up and can be quickly restored in case of failures. Fast Deployment: Rapidly deploy and configure resources without the delays associated with hardware procurement and setup. Optimized Performance: Cloud providers use the latest technologies and hardware to ensure optimal performance for applications and services. Advanced Security Measures: Cloud providers implement robust security measures, including data encryption, network security, and regular security updates. Compliance: Many cloud providers comply with industry standards and regulations, helping organizations meet their own compliance requirements. Remote Access: Cloud services enable remote access to applications and data from any location with internet connectivity, supporting remote work and mobile access. Reduced IT Management: By outsourcing infrastructure management to cloud providers, organizations can focus more on their core business activities and strategic initiatives rather than IT maintenance. Energy Efficiency: Cloud providers operate data centers that are optimized for energy efficiency, often using renewable energy sources and advanced cooling technologies. Resource Optimization: By sharing resources among multiple customers, cloud computing reduces the overall carbon footprint compared to traditional data centers. Remote Work Enablement Rapid Deployment Environmental Sustainability Key Layers of OCI Architecture- Regions and Availability Domains (ADs) Virtual Cloud Network (VCN) Compute Layer Storage Layer Database Layer Networking Layer Security Layer Management and Monitoring Layer In this module- the focus is on learning the- Concepts Understanding that what is cloud. Benefits of having cloud Oracle is now on cloud. Architecture Layers Purpose of each component in the architecture Hybrid Cloud Multi Cloud Examples- Sky Tap Google Cloud AWS Microsoft Azure And now Oracle Cloud. Comparison: Multi-Cloud vs. Hybrid Cloud Use Cases Multi-Cloud: Organizations looking to leverage best-of-breed services from different cloud providers. Companies aiming to enhance redundancy and resilience by distributing workloads across multiple clouds. Businesses seeking to avoid vendor lock-in and increase negotiating power with cloud providers. Hybrid Cloud: Enterprises needing to maintain on-premises infrastructure due to regulatory, security, or latency requirements. Organizations looking to burst into the public cloud during peak times while keeping baseline workloads on-premises. Companies aiming to gradually migrate to the cloud without disrupting existing on-premises systems. Strategic Goals Multi-Cloud: Flexibility in choosing services. Risk mitigation through diversification. Optimization of performance and cost. Hybrid Cloud: Seamless integration of legacy systems with modern cloud infrastructure. Flexibility to meet regulatory and compliance requirements. Efficient handling of variable workloads. Management and Tools Multi-Cloud: Requires tools that can manage, monitor, and secure multiple cloud environments. Emphasis on interoperability and consistency across different platforms. Hybrid Cloud: Focus on tools that enable integration, data synchronization, and workload portability between on- premises and cloud environments. Importance of unified management platforms to oversee both on-premises and cloud resources. SAAS---------- SOFTWARE AS A SERVICE--------- ready-to-use software applications. readily available over the internet. Accessible through a web browser. Anybody can use it. No need download/install/setup infrastructure or platforms. Vendors handle maintenance, upgrades, support, security and all other aspects of managing the software. EX- GOOGLE WORKSPACE…… Drive, Email and many more. Netflix PAAS--------- PLATFORM AS A SERVICE----------- provides a framework of resources and development tools. Access through web page. offers a platform allowing developers to build, deploy, and manage applications without worrying about the underlying infrastructure. EX- ORACLE APEX IAAS--------- INFRASTRUCTURE AS A SERVICE----------  IaaS provides virtualized computing resources over the internet. It offers the most control over IT resources, including servers, storage, and networking. EX- AWS CLOUD, GCP, MS AZURE, IBM CLOUD